Hi, we are trying to update a very old installation of Discourse (v1.2.4) to the latest v2.3.1. Our first test-migrations using v2.2.0 done surprisingly well and the migration-process finished without any issues. But now we try to run that in the final stages against v2.3.1 and get the following error: PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "idx_unique_rows" DETAIL: Key (action_type, user_id, target_topic_id, target_post_id, acting_user_id)=(15, 1140990, 9346002, 9431164, 419835) already exists. /var/www/discourse/vendor/bundle/ruby/2.6.0/gems/rack-mini-profiler-1.0.2/lib/patches/db/pg.rb:110:in `async_exec' /var/www/discourse/vendor/bundle/ruby/2.6.0/gems/rack-mini-profiler-1.0.2/lib/patches/db/pg.rb:110:in `async_exec' (eval):24:in `async_exec' /var/www/discourse/vendor/bundle/ruby/2.6.0/gems/mini_sql-0.2.2/lib/mini_sql/postgres/connection.rb:118:in `run' /var/www/discourse/vendor/bundle/ruby/2.6.0/gems/mini_sql-0.2.2/lib/mini_sql/postgres/connection.rb:90:in `exec' /var/www/discourse/plugins/discourse-solved/plugin.rb:64:in `block in activate!' /var/www/discourse/lib/plugin/instance.rb:328:in `block in notify_after_initialize' /var/www/discourse/lib/plugin/instance.rb:326:in `each' /var/www/discourse/lib/plugin/instance.rb:326:in `notify_after_initialize' /var/www/discourse/config/application.rb:292:in `each' /var/www/discourse/config/application.rb:292:in `block in ' /var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activesupport-5.2.3/lib/active_support/lazy_load_hooks.rb:69:in `block in execute_hook' /var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activesupport-5.2.3/lib/active_support/lazy_load_hooks.rb:62:in `with_execution_control' /var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activesupport-5.2.3/lib/active_support/lazy_load_hooks.rb:67:in `execute_hook' /var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activesupport-5.2.3/lib/active_support/lazy_load_hooks.rb:52:in `block in run_load_hooks' /var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activesupport-5.2.3/lib/active_support/lazy_load_hooks.rb:51:in `each' /var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activesupport-5.2.3/lib/active_support/lazy_load_hooks.rb:51:in `run_load_hooks' /var/www/discourse/vendor/bundle/ruby/2.6.0/gems/railties-5.2.3/lib/rails/application/finisher.rb:75:in `block in ' /var/www/discourse/vendor/bundle/ruby/2.6.0/gems/railties-5.2.3/lib/rails/initializable.rb:32:in `instance_exec' /var/www/discourse/vendor/bundle/ruby/2.6.0/gems/railties-5.2.3/lib/rails/initializable.rb:32:in `run' /var/www/discourse/vendor/bundle/ruby/2.6.0/gems/railties-5.2.3/lib/rails/initializable.rb:61:in `block in run_initializers' /var/www/discourse/vendor/bundle/ruby/2.6.0/gems/railties-5.2.3/lib/rails/initializable.rb:60:in `run_initializers' /var/www/discourse/vendor/bundle/ruby/2.6.0/gems/railties-5.2.3/lib/rails/application.rb:361:in `initialize!' /var/www/discourse/vendor/bundle/ruby/2.6.0/gems/railties-5.2.3/lib/rails/railtie.rb:190:in `public_send' /var/www/discourse/vendor/bundle/ruby/2.6.0/gems/railties-5.2.3/lib/rails/railtie.rb:190:in `method_missing' /var/www/discourse/config/environment.rb:7:in ` ' /var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activesupport-5.2.3/lib/active_support/dependencies.rb:291:in `require' /var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activesupport-5.2.3/lib/active_support/dependencies.rb:291:in `block in require' /var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activesupport-5.2.3/lib/active_support/dependencies.rb:257:in `load_dependency' /var/www/discourse/vendor/bundle/ruby/2.6.0/gems/activesupport-5.2.3/lib/active_support/dependencies.rb:291:in `require' /var/www/discourse/vendor/bundle/ruby/2.6.0/gems/railties-5.2.3/lib/rails/application.rb:337:in `require_environment!' /var/www/discourse/vendor/bundle/ruby/2.6.0/gems/railties-5.2.3/lib/rails/application.rb:520:in `block in run_tasks_blocks' /var/www/discourse/vendor/bundle/ruby/2.6.0/gems/rake-12.3.2/exe/rake:27:in ` ' /var/www/discourse/bin/bundle:5:in `load' /var/www/discourse/bin/bundle:5:in ` ' Tasks: TOP => db:migrate => db:load_config => environment (See full trace by running task with --trace) This seems to be caused by “20120816050526_add_unique_constraint_to_user_actions.rb” and if I take a look in the database-table “schema_migrations” it seems that this has already been applied. As this migration seems to be added in 2012. Do you have any ideas why “bundle exec rake db:migrate” is running already applied migrations or what we are doing wrong? I know the source - version is pretty old but as said we already got successful results. Kind Regards

I’ve misinterpreted this error. So sorry for the confusion. Obviously it has nothing to do with the mentioned migration-file. Anyway, there seems to be an existing index for an entry in user_actions. So I checked that with: SELECT * FROM user_actions WHERE action_type=15 AND user_id=1140990 AND target_topic_id=9346002 AND target_post_id=9431164 AND acting_user_id=419835; This query gives me 0 Results. So there must be an index without a corresponding dataset.

Or maybe make sure that you were in read-only when you make the backup? Yes maybe that’s the problem. We’ve created the dump while the database was in use. Worked at the first try. I’ll create another one and try again.

Hi. It seemed our user_actions table got corrupted. I could fix that by running VACUUM (VERBOSE,ANALYZE,DISABLE_PAGE_SKIPPING) user_actions. But thanks anyway for your advise! There where several entries missing from the visibility map which produced duplicated keys.

Upgrade fails while migrating

Product Support Self-hosting

pfaffman (Jay Pfaffman) July 2, 2019, 6:20pm 3

When I’ve had similar problems I’ve deleted the thing that it was trying to duplicate. That’s likely not very helpful.

Or maybe make sure that you were in read-only when you make the backup?

Topic		Replies	Views
Upgrade failed with "duplicate key value violates unique constraint" error Self-hosting	17	2763	February 29, 2020
Update failed - PG::UniqueViolation: ERROR Self-hosting	7	965	December 21, 2018
Migration failed upgrading to latest from ~3 weeks ago Self-hosting	4	812	December 8, 2021
2.7.0.beta2 upgrade failed with ERROR: duplicate key Self-hosting	24	2617	January 26, 2021
Failed upgrade: UniqueViolation: duplicate key value violates unique constraint "data_explorer_queries_pkey" Self-hosting	11	2309	September 10, 2020

Upgrade fails while migrating

Related topics