فشل التحديث بسبب خطأ "قيمة مفتاح مكررة تنتهك قيدًا فريدًا"

الدعم تثبيت
1

مرحبًا!

اليوم حاولت ترقية مثيل Discourse الخاص بي من الإصدار “2.1.0.beta6” إلى أحدث إصدار وهو “2.5.0.beta1”.

لكن فشلت الترقية عبر الويب في المنتصف، وبعد ذلك عرضت الخادم توصية بالتحديث عن طريق تنفيذ الأمر ./launcher rebuild app.

نفذت الأمر، لكن فشلت الترقية مع ظهور خطأ:

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.

ملاحظة: لقد استخدمت عمدًا أمر ./launcher rebuild synfig-forums لأن مثيلي موصوف في ملف synfig-forums.yml وليس في ملف app.yml.

كما أفهم، فإن المشكلة موضحة في هذا السطر:

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

ما الذي يمكنني فعله في هذه الحالة؟ أي مساعدة ستكون موضع تقدير كبير!

2

حسناً، أردتُ فحص محتويات قاعدة البيانات، لذا قمتُ بما يلي:

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

تلقّيتُ خطأً:

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

لديك فهرس تالف. راجع 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

لا أتذكر. كانت هناك رسائل كثيرة جدًا عندما كنت أقوم بالترقية عبر واجهة الويب. :frowning:

ونعم، قمت بتشغيل التحديث أكثر من مرة: أول مرة من واجهة الويب (فشلت)، ثم من وحدة التحكم (عدة مرات).

نعم، لدي نسخة احتياطية تم إنشاؤها قبل 24 ساعة. يمكنني استعادتها، لكن بعض الرسائل ستفقد (تلك التي نُشرت بعد النسخ الاحتياطي).

8

هذا ليس فهرسًا تالفًا.

بين الإصدارين 2.1 و2.5، قمنا بنقل المجموعات من PluginStore إلى جداول مناسبة. أحد أسباب هذا الانتقال هو ضمان اتساق البيانات، وهو بالضبط المشكلة التي تواجهها.

لديك قيمة مكررة في جدول poll_options. هل يمكنك الذهاب إليه وحذف الإدخالات المكررة؟

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

أشك في أن الإدخال المكرر يأتي من متجر الإضافات. لكن أين يقع وكيف يمكنني عرضه/تعديله؟

13

إنه مجرد جدول آخر (plugin_store_rows).

14

يبدو أن أصوات الاستطلاع موجودة فعليًا في جدول post_custom_fields، حيث تكون قيمة عمود name هي ‘polls’.

15

تم تغيير ذلك بواسطة هذا الالتزام، ويقوم الترحيل المقابل بملء جدول poll_options. لذا قد يكون ذلك يحاول إدراج سجل مكرر.

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

16

إذن، أرى خيارين أمامي:

  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%';

أظهر لي سطرًا واحدًا. لذا، قمت بحذفه:

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

ملاحظة: الطريقة الصحيحة هي الحذف بواسطة معرف الصف (row id)، ولكن بما أنه عاد بسطر واحد فقط، فقد فعلت ذلك. :slight_smile:

بعد ذلك، أعدت تشغيل الترقية وأُكملت بنجاح. شكرًا للجميع على إشاراتهم! :slight_smile: