Failed upgrade: UniqueViolation: duplicate key value violates unique constraint "data_explorer_queries_pkey"

Today’s upgrade to latest failed:

FAILED
--------------------
Pups::ExecError: cd /var/www/discourse && su discourse -c 'bundle exec rake db:migrate' failed with return #<Process::Status: pid 3194 exit 1>
Location of failure: /pups/lib/pups/exec_command.rb:112:in `spawn'
exec failed with the params {"cd"=>"$home", "hook"=>"db_migrate", "cmd"=>["su discourse -c 'bundle exec rake db:migrate'"]}
590cf0611c566ea6df5f70ffdd2cec2359e84eaea29b7abcde77d56288a46370
** FAILED TO BOOTSTRAP ** please scroll up and look for earlier error messages, there may be more than one.

Above in the log:

Caused by:
PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "data_explorer_queries_pkey"
DETAIL:  Key (id)=(-10) already exists.

and

rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

ERROR:  duplicate key value violates unique constraint "data_explorer_queries_pkey"
DETAIL:  Key (id)=(-10) already exists.

So, I suppose I need to get rid of the duplicate data_explorer_queries_pkey key.

How do I do that?

1 Like
3 Likes

Thanks, but I can’t get into the app:

./launcher enter app
Error response from daemon: Container 694b24a2a235e90456fb0ca770c86ac14bb914ad33ada8a18fc4777a1188d848 is not running
root@gaoa-discourse:/var/discourse#

And also:

root@gaoa-discourse:/var/discourse# su - postgres
No passwd entry for user 'postgres'
1 Like

So restart the container?

./launcher start app

Then you want something like:

./launcher enter app

then:

su postgres -c 'psql discourse'

4 Likes

Thanks! Not sure why it wasn’t starting the first time I tried.

It looks like I have a duplicate “10” id:

4374 | discourse-data-explorer | q:-10 | JSON      | {"id":-10,"name":"Inactive Users with no posts","description":"analyze pre-Discourse signups.","sql":"SELECT\
n    u.id,\n    u.username_lower AS \"username\",\n    u.created_at,\n    u.last_seen_at\nFROM users u\nWHERE u.active = false\nORDER BY u.id\n","created_by":"-1",
"created_at":null,"group_ids":[],"last_run_at":"2019-10-21T04:03:35.548+00:00"}

4114 | discourse-data-explorer | q:-10 | JSON      | {"id":-10,"name":"Inactive Users with no posts","description":"analyze pre-Discourse signups.","sql":"SELECT\
n    u.id,\n    u.username_lower AS \"username\",\n    u.created_at,\n    u.last_seen_at\nFROM users u\nWHERE u.active = false\nORDER BY u.id\n","created_by":"-1",
"created_at":null,"last_run_at":"2019-02-27T06:17:48.317+00:00"}

So I suppose I should run this:

DELETE from plugin_store_rows WHERE id = 10

or should it be:

DELETE from plugin_store_rows WHERE id = -10

I’m curious - which one will be deleted by the command above - the first one it finds or both?

1 Like

The numbers in the ID column are 4374 and 4114. Delete the highest numbered one.

Do NOT run DELETE from plugin_store_rows WHERE id = 10!! or you will delete the wrong entry!

3 Likes

Ah, so this?

DELETE from plugin_store_rows WHERE id = 4374;

1 Like

Yes. You should see the correct column header in the plsql query output no?

1 Like

Now that you mention it, yes. I’d scrolled down because the output is like this:

2 Likes

Yep, also, in general consider running commands to see their impact before committing, see:

Then if you find you have deleted 500 rows when you meant to hit just one, you have the chance to rollback without fear.

3 Likes

Success! :tada: Thanks for the guidance @merefield.

My DELETE command was missing a closing semicolon (updated above).

3 Likes

Oh yes, that gets me every time :wink:

3 Likes

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.