アップグレードが「duplicate key value violates unique constraint」エラーで失敗しました

サポート インストール
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

はい、データベースの内容を確認したかったので、以下の手順を実行しました：

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

エラーが発生しました：

psql: サーバーに接続できませんでした: そのようなファイルまたはディレクトリがありません
	サーバーがローカルで実行されており、Unix ドメインソケット
	"/var/run/postgresql/.s.PGSQL.5432" で接続を受け付けていますか？
3

インデックスが破損しています。破損したインデックスへの対処法に関するヒントについては、Can't restore due to corrupt indexes (with some clues on how to deal with corrupt indexes) を参照してください。

別のデータベースコンテナをお持ちでしょうか？おそらくお持ちで、そのコンテナにアクセスする必要があると思われます。

4

ヒントをありがとうございます！

いいえ、スタンドアロンのコンテナインストールです（おそらくそう呼ばれるのでしょう）。

postgresql サービス（docker コンテナから）を起動しようとしましたが、以下のような結果になりました：

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

はい、PostgreSQL サービスの起動に成功し、データベースのバックアップを取得してデータベースに接続しました：

# 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=#

さて、インストールを復元するにはどうすればよいでしょうか？

6

それはよろしくありませんね。

再構築を行った際、PostgreSQL のアップグレードに関するメッセージは表示されましたか？複数回実行されましたか？

最近のバックアップはありますか？そこから復元を試みるのが最も簡単な方法かもしれませんが、それも難しいかもしれません。

7

覚えていません。Web UI を介してアップグレードしていた際、メッセージが多すぎました。:frowning:

はい、更新を複数回実行しました。最初は Web UI から（失敗しました）、その後コンソールから数回実行しました。

はい、24 時間前に作成したバックアップがあります。復元できますが、一部のメッセージ（バックアップ後に投稿されたもの）は失われます。

8

それは破損したインデックスではありません。

2.1 から 2.5 の間で、プーリングを PluginStore から適切なテーブルへ移行しました。この移行の理由の一つはデータの整合性を保つことであり、まさにあなたが直面している問題です。

poll_options テーブルに重複する値が存在します。該当テーブルへアクセスし、重複するエントリを削除してください。

「いいね！」 9
9

ご説明いただき、ありがとうございます！
PostgreSQL の専門家ではないのですが、実行に必要なコマンドをご教示いただけますでしょうか？（すでに psql にログインしていることを前提としています）:slight_smile:

10

はい、以下を実行しました：

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

どうしてこのテーブルが空になっているのでしょうか？

他のテーブルは空ではありません：

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

重複したエントリは Plugin Store に由来していると思われます。しかし、それはどこにあり、どのように表示または編集できるのでしょうか？

13

単なる別のテーブル（plugin_store_rows）です。

14

投票は実際には post_custom_fields テーブルにあり、name カラムの値は ‘polls’ です。

「いいね！」 1
15

これは このコミット によって変更され、対応するマイグレーションが poll_options テーブルを埋めます。そのため、重複レコードの挿入を試みている可能性があります。

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

「いいね！」 1
16

さて、私には2つの選択肢があります。

  1. データベースからすべてのアンケートデータを削除し、更新プロセスを再度実行します。これですべてのアンケートが失われますが、それは問題ありません。
  2. migrate_polls_data.rb のコードを修正して、重複エントリの挿入をスキップするようにします。より良い解決策ですが、その方法がわかりません。
17

この部分を失敗してもエラーを出さずに済むようにコードを修正するにはどうすればよいですか？

18

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

はい、SQL ステートメントに ON CONFLICT DO NOTHING を追加して修正すれば十分だと思います - https://stackoverflow.com/a/31742830
それでうまくいきますか？

19

はい、pgsql から以下のコマンドを実行しました：

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

結果として 1 行が表示されました。そこで、その行を削除しました：

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

注：本来は行 ID で削除するのが正しい方法ですが、1 行しか返ってこなかったため、上記のように実行しました :slight_smile:

その後、アップグレードを再開したところ、正常に完了しました。ヒントを投稿してくださった皆様、ありがとうございます！:slight_smile:

「いいね！」 3
20

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