Upgrade failed with "duplicate key value violates unique constraint" error

Installation
1

Hi!

Today I tried to upgrade my Discourse instance from “2.1.0.beta6” to latest one - “2.5.0.beta1”.

But web upgrade failed in the middle and after that my server displayed recommendation to update by executing ./launcher rebuild app.

I did that, but upgrade failed with error:

root@cloud:/var/discourse# ./launcher rebuild synfig-forums
Ensuring launcher is up to date
Fetching origin
Launcher is up-to-date
Stopping old container
+ /usr/bin/docker stop -t 10 synfig-forums
synfig-forums
cd /pups && git pull && /pups/bin/pups --stdin
Already up to date.
...
Bundle complete! 124 Gemfile dependencies, 157 gems now installed.
Gems in the groups test and development were not installed.
Bundled gems are installed into `./vendor/bundle`

I, [2020-02-27T10:37:49.176136 #1]  INFO -- : > cd /var/www/discourse && su discourse -c 'bundle exec rake db:migrate'
2020-02-27 10:38:06.784 UTC [414] discourse@discourse ERROR:  duplicate key value violates unique constraint "index_poll_options_on_poll_id_and_digest"
2020-02-27 10:38:06.784 UTC [414] discourse@discourse DETAIL:  Key (poll_id, digest)=(30, 399fc6670871474cd7ce0458401fd299) already exists.
2020-02-27 10:38:06.784 UTC [414] discourse@discourse STATEMENT:  INSERT INTO poll_options
	  (poll_id, digest, html, anonymous_votes, created_at, updated_at)
	VALUES
	  (30, '399fc6670871474cd7ce0458401fd299', '<ul>
	<li>
	</li>
	</ul>', 0, '2018-07-30 12:09:10 UTC', '2018-07-30 12:09:10 UTC'),(30, '59f25e0e204428418f22b441698f25dd', '**', 0, '2018-07-30 12:09:10 UTC', '2018-07-30 12:09:10 UTC'),(30, '399fc6670871474cd7ce0458401fd299', '<hr>', 0, '2018-07-30 12:09:10 UTC', '2018-07-30 12:09:10 UTC'),(30, '399fc6670871474cd7ce0458401fd299', '<hr>', 0, '2018-07-30 12:09:10 UTC', '2018-07-30 12:09:10 UTC'),(30, '399fc6670871474cd7ce0458401fd299', '<hr>', 0, '2018-07-30 12:09:10 UTC', '2018-07-30 12:09:10 UTC')
	RETURNING digest, id
	
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

ERROR:  duplicate key value violates unique constraint "index_poll_options_on_poll_id_and_digest"
DETAIL:  Key (poll_id, digest)=(30, 399fc6670871474cd7ce0458401fd299) already exists.
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/rack-mini-profiler-1.1.6/lib/patches/db/pg.rb:110:in `exec'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/rack-mini-profiler-1.1.6/lib/patches/db/pg.rb:110:in `async_exec'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/mini_sql-0.2.4/lib/mini_sql/postgres/connection.rb:118:in `run'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/mini_sql-0.2.4/lib/mini_sql/postgres/connection.rb:55:in `query_single'
/var/www/discourse/plugins/poll/db/post_migrate/20180820080623_migrate_polls_data.rb:131:in `block (2 levels) in up'
/var/www/discourse/plugins/poll/db/post_migrate/20180820080623_migrate_polls_data.rb:87:in `each'
/var/www/discourse/plugins/poll/db/post_migrate/20180820080623_migrate_polls_data.rb:87:in `block in up'
/var/www/discourse/plugins/poll/db/post_migrate/20180820080623_migrate_polls_data.rb:64:in `each'
/var/www/discourse/plugins/poll/db/post_migrate/20180820080623_migrate_polls_data.rb:64:in `up'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:831:in `exec_migration'
/var/www/discourse/lib/freedom_patches/schema_migration_details.rb:9:in `block in exec_migration'
/var/www/discourse/lib/freedom_patches/schema_migration_details.rb:8:in `exec_migration'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:812:in `block (2 levels) in migrate'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:811:in `block in migrate'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:473:in `with_connection'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:810:in `migrate'
/var/www/discourse/lib/migration/safe_migrate.rb:29:in `migrate'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:1001:in `migrate'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:1311:in `block in execute_migration_in_transaction'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:1362:in `block in ddl_transaction'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/connection_adapters/abstract/database_statements.rb:281:in `block in transaction'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/connection_adapters/abstract/transaction.rb:280:in `block in within_new_transaction'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/connection_adapters/abstract/transaction.rb:278:in `within_new_transaction'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/connection_adapters/abstract/database_statements.rb:281:in `transaction'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/transactions.rb:212:in `transaction'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:1362:in `ddl_transaction'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:1310:in `execute_migration_in_transaction'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:1282:in `block in migrate_without_lock'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:1281:in `each'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:1281:in `migrate_without_lock'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:1229:in `block in migrate'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:1382:in `with_advisory_lock'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:1229:in `migrate'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:1061:in `up'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:1036:in `migrate'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/tasks/database_tasks.rb:238:in `migrate'
/var/www/discourse/lib/migration/safe_migrate.rb:52:in `migrate'
/var/www/discourse/lib/tasks/db.rake:72:in `block in <top (required)>'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/rake-13.0.1/exe/rake:27:in `<top (required)>'
/usr/local/bin/bundle:23:in `load'
/usr/local/bin/bundle:23:in `<main>'

Caused by:
PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "index_poll_options_on_poll_id_and_digest"
DETAIL:  Key (poll_id, digest)=(30, 399fc6670871474cd7ce0458401fd299) already exists.
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/rack-mini-profiler-1.1.6/lib/patches/db/pg.rb:110:in `exec'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/rack-mini-profiler-1.1.6/lib/patches/db/pg.rb:110:in `async_exec'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/mini_sql-0.2.4/lib/mini_sql/postgres/connection.rb:118:in `run'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/mini_sql-0.2.4/lib/mini_sql/postgres/connection.rb:55:in `query_single'
/var/www/discourse/plugins/poll/db/post_migrate/20180820080623_migrate_polls_data.rb:131:in `block (2 levels) in up'
/var/www/discourse/plugins/poll/db/post_migrate/20180820080623_migrate_polls_data.rb:87:in `each'
/var/www/discourse/plugins/poll/db/post_migrate/20180820080623_migrate_polls_data.rb:87:in `block in up'
/var/www/discourse/plugins/poll/db/post_migrate/20180820080623_migrate_polls_data.rb:64:in `each'
/var/www/discourse/plugins/poll/db/post_migrate/20180820080623_migrate_polls_data.rb:64:in `up'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:831:in `exec_migration'
/var/www/discourse/lib/freedom_patches/schema_migration_details.rb:9:in `block in exec_migration'
/var/www/discourse/lib/freedom_patches/schema_migration_details.rb:8:in `exec_migration'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:812:in `block (2 levels) in migrate'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:811:in `block in migrate'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:473:in `with_connection'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:810:in `migrate'
/var/www/discourse/lib/migration/safe_migrate.rb:29:in `migrate'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:1001:in `migrate'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:1311:in `block in execute_migration_in_transaction'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:1362:in `block in ddl_transaction'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/connection_adapters/abstract/database_statements.rb:281:in `block in transaction'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/connection_adapters/abstract/transaction.rb:280:in `block in within_new_transaction'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/connection_adapters/abstract/transaction.rb:278:in `within_new_transaction'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/connection_adapters/abstract/database_statements.rb:281:in `transaction'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/transactions.rb:212:in `transaction'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:1362:in `ddl_transaction'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:1310:in `execute_migration_in_transaction'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:1282:in `block in migrate_without_lock'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:1281:in `each'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:1281:in `migrate_without_lock'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:1229:in `block in migrate'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:1382:in `with_advisory_lock'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:1229:in `migrate'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:1061:in `up'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:1036:in `migrate'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/tasks/database_tasks.rb:238:in `migrate'
/var/www/discourse/lib/migration/safe_migrate.rb:52:in `migrate'
/var/www/discourse/lib/tasks/db.rake:72:in `block in <top (required)>'
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/rake-13.0.1/exe/rake:27:in `<top (required)>'
/usr/local/bin/bundle:23:in `load'
/usr/local/bin/bundle:23:in `<main>'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)
I, [2020-02-27T10:38:06.821527 #1]  INFO -- : == 20180820080623 MigratePollsData: migrating =================================
...
I, [2020-02-27T10:38:06.822339 #1]  INFO -- : Terminating async processes
I, [2020-02-27T10:38:06.822638 #1]  INFO -- : Sending INT to HOME=/var/lib/postgresql USER=postgres exec chpst -u postgres:postgres:ssl-cert -U postgres:postgres:ssl-cert /usr/lib/postgresql/10/bin/postmaster -D /etc/postgresql/10/main pid: 50
I, [2020-02-27T10:38:06.822850 #1]  INFO -- : Sending TERM to exec chpst -u redis -U redis /usr/bin/redis-server /etc/redis/redis.conf pid: 167
2020-02-27 10:38:06.822 UTC [50] LOG:  received fast shutdown request
167:signal-handler (1582799886) Received SIGTERM scheduling shutdown...
2020-02-27 10:38:06.825 UTC [50] LOG:  aborting any active transactions
167:M 27 Feb 2020 10:38:06.828 # User requested shutdown...
2020-02-27 10:38:06.828 UTC [50] LOG:  worker process: logical replication launcher (PID 59) exited with exit code 1
167:M 27 Feb 2020 10:38:06.829 * Saving the final RDB snapshot before exiting.
2020-02-27 10:38:06.839 UTC [54] LOG:  shutting down
2020-02-27 10:38:06.941 UTC [50] LOG:  database system is shut down
167:M 27 Feb 2020 10:38:07.170 * DB saved on disk
167:M 27 Feb 2020 10:38:07.171 # Redis is now ready to exit, bye bye...


FAILED
--------------------
Pups::ExecError: cd /var/www/discourse && su discourse -c 'bundle exec rake db:migrate' failed with return #<Process::Status: pid 385 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'"]}
77bc12013304841e8082e888a7a8c86adade040fd97b44fef1c1e85355ba2ae5
** FAILED TO BOOTSTRAP ** please scroll up and look for earlier error messages, there may be more than one.
./discourse-doctor may help diagnose the problem.

Note: I am intentionally used ./launcher rebuild synfig-forums command, because my instance is described in synfig-forums.yml file, not app.yml.

As I understand, the problem described by this line:

ERROR:  duplicate key value violates unique constraint "index_poll_options_on_poll_id_and_digest"
DETAIL:  Key (poll_id, digest)=(30, 399fc6670871474cd7ce0458401fd299) already exists.

What can I do in this situation? Any assistance is much appreciated!

2

Okay, I wanted to examine database contents, so I did the following:

cd /var/discourse
./launcher start synfig-forums
./launcher enter synfig-forums
su - postgres
psql

got an error:

psql: could not connect to server: No such file or directory
	Is the server running locally and accepting
	connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
3

You have a corrupted index. See Can't restore due to corrupt indexes (with some clues on how to deal with corrupt indexes) for some hints.

Do you have a separate database container? My guess is that you do and need to enter that container instead.

4

Thanks for giving some hints!

No, I have a standalone container installation (I guess this is how it called).

I tried to start postgresql service (from docker container), got the following:

root@cloud-synfig-forums:/var/www/discourse# service postgresql start
 * Starting PostgreSQL 10 database server                                        * Error: Config owner (postgres:107) and data owner (syslog:106) do not match, and config owner is not root
                                                                         [fail]
5

Okay, I managed to start postgresql service, did database backup and entered to the database:

# chown -R postgres /shared/postgres_data
# service postgresql start
 * Starting PostgreSQL 10 database server                                                                                        [ OK ] 
# psql
psql: FATAL:  role "root" does not exist
# su - discourse
$ pg_dump -xOf ~/discourse-backup.sql -d discourse -n public
$ exit
# mv /home/discourse/discourse-backup.sql /shared/discourse-backup-20200227-1.sql
# su - postgres
$ psql discourse
psql (10.4 (Ubuntu 10.4-2.pgdg16.04+1))
Type "help" for help.

discourse=#

Now, what can I do to recover my installation?

6

That doesn’t sound good.

When you did the rebuild did it say something about upgrading postgres? Did you run it more than once?

Do you have a recent backup? It might be easiest to try to restore from that, but that will be hard too.

7

I do not remember. There were too many messages when I was upgrading via web UI. :frowning:

And yes, I run update more than once: first time from web UI (it failed), then from console (several times).

Yes, I have backup made 24 hours ago. I can restore it, but some messages will be lost (the ones that were posted after backup).

8

That is not a corrupt index.

Between 2.1 and 2.5 we moved pools from PluginStore to proper tables. One of the reasons for the move is to have data consistency, which is exactly the problem you have.

You have a duplicate value on the poll_options table. Can you go there and delete the duplicate entries?

9 Likes
9

Thank you for clarification!
I am not expert with postgresql… can you please help with commands required to execute? (considering, I am already entered psql). :slight_smile:

10

Okay, I have executed this:

discourse=# SELECT * FROM poll_options;
 id | poll_id | digest | html | anonymous_votes | created_at | updated_at 
----+---------+--------+------+-----------------+------------+------------
(0 rows)

How could it be I have this table empty?

Other tables are not empty:

discourse=# SELECT name FROM badges WHERE id = 13;
    name    
------------
 First Flag
(1 row)
11

I suspect the duplicated entry comes from Plugin Store. But where is it located and how do I view/edit it?

13

It’s just another table (plugin_store_rows).

14

It appears the poll votes are actually on the post_custom_fields table, where the name column is ‘polls’.

1 Like
15

That was changed by this commit and the corresponding migration fills the poll_options table. So that could be trying to insert a duplicate record.

https://github.com/discourse/discourse/blob/master/plugins/poll/db/post_migrate/20180820080623_migrate_polls_data.rb#L132-L138

1 Like
16

So, I see two options for myself:

  1. Remove all poll data from my database and run update process again. This way I will lost all polls, but I am okay with that.
  2. Modify code of migrate_polls_data.rb, so it will skip inserting duplicated entries. Better solution, but I do not know how to do that.
17

How can I modify code to allow this part to fail silently?

18

https://github.com/discourse/discourse/blob/master/plugins/poll/db/post_migrate/20180820080623_migrate_polls_data.rb#L132-L138

Okay, I think it is enough to modify SQL statement by adding ON CONFLICT DO NOTHING - https://stackoverflow.com/a/31742830
Would that work?

19

Okay, I did the following from pgsql:

SELECT *
	FROM post_custom_fields
	WHERE name = 'polls'
	AND value LIKE '%399fc6670871474cd7ce0458401fd299%';

It displayed me one row. So, I removed it:

DELETE FROM post_custom_fields
	WHERE name = 'polls'
	AND value LIKE '%399fc6670871474cd7ce0458401fd299%';

Note: The proper way would be to remove by row id, but since it returned just a single row I did just that. :slight_smile:

After that I re-started upgrade and it completed successfully. Thanks to everyone who posted their hints! :slight_smile:

3 Likes
20

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