Continuing the discussion from Rebuilding app fails at db:migrate with unique constraint 'badges_pkey' error: Our upgrades have started failing with a unique constraint violation, specifically ActiveRecord::RecordNotUnique: PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "index_badges_on_name". Here’s a full dump of the upgrade output. There don’t appear to be any duplicates badges in our database (i.e., no counts > 1 of id or name in badges). From the output, it appears to be trying to create a “First Like” badge with ID 11; however, in our system the “First Like” badge has ID 5 and there’s an “Encourager” badge with ID 5. :confused: We haven’t yet tried rebuilding, but, from similar posts in the past, it looks like an attempt to rebuild will likely fail during data migration. Any suggestions on how to get past this?

[image] sam: Maybe try hard nuking the badge from the badges table? delete from badges where id=5 ftw! Looks like this followed by rebuilding is going to work. Thanks @sam! I think I just needed a nudge to be brave enough to try the nuclear option. :wink:

I got a chance to look into this a bit further. In our database, the First Like badge has id=5: discourse=# select id, name from badges order by id limit 15; id | name ----+---------------- 1 | Basic User 2 | Member 3 | Regular 4 | Leader 5 | First Like 6 | Nice Post 7 | Good Post 8 | Great Post 9 | Autobiographer 10 | Editor 11 | Encourager 12 | First Share 13 | First Flag 14 | First Link 15 | First Quote (15 rows) When I try to perform an upgrade, start, or rebuild our Discourse data, it’s failing with something like this (output from ./launcher start data): == Seed from /var/www/discourse/db/fixtures/006_badges.rb - BadgeGrouping {:id=>1, :name=>"Getting Started", :default_position=>10} - BadgeGrouping {:id=>2, :name=>"Community", :default_position=>11} - BadgeGrouping {:id=>3, :name=>"Posting", :default_position=>12} - BadgeGrouping {:id=>4, :name=>"Trust Level", :default_position=>13} - BadgeGrouping {:id=>5, :name=>"Other", :default_position=>14} - Badge {:id=>1, :name=>"Basic User", :badge_type_id=>3, :query=>"\n SELECT u.id user_id, current_timestamp granted_at FROM users u\n WHERE trust_level >= 1 AND (\n :backfill OR u.id IN (:user_ids)\n )\n ", :default_badge_grouping_id=>4, :trigger=>4, :default_allow_title=>false, :default_icon=>"fa-user", :system=>true} - Badge {:id=>2, :name=>"Member", :badge_type_id=>3, :query=>"\n SELECT u.id user_id, current_timestamp granted_at FROM users u\n WHERE trust_level >= 2 AND (\n :backfill OR u.id IN (:user_ids)\n )\n ", :default_badge_grouping_id=>4, :trigger=>4, :default_allow_title=>false, :default_icon=>"fa-user", :system=>true} - Badge {:id=>3, :name=>"Regular", :badge_type_id=>2, :query=>"\n SELECT u.id user_id, current_timestamp granted_at FROM users u\n WHERE trust_level >= 3 AND (\n :backfill OR u.id IN (:user_ids)\n )\n ", :default_badge_grouping_id=>4, :trigger=>4, :default_allow_title=>true, :default_icon=>"fa-user", :system=>true} - Badge {:id=>4, :name=>"Leader", :badge_type_id=>1, :query=>"\n SELECT u.id user_id, current_timestamp granted_at FROM users u\n WHERE trust_level >= 4 AND (\n :backfill OR u.id IN (:user_ids)\n )\n ", :default_badge_grouping_id=>4, :trigger=>4, :default_allow_title=>true, :default_icon=>"fa-user", :system=>true} - Badge {:id=>17, :name=>"Reader", :badge_type_id=>3, :multiple_grant=>false, :target_posts=>false, :show_posts=>false, :query=>" SELECT id user_id, current_timestamp granted_at\n FROM users\n WHERE id IN\n (\n SELECT pt.user_id\n FROM post_timings pt\n JOIN badge_posts b ON b.post_number = pt.post_number AND\n b.topic_id = pt.topic_id\n JOIN topics t ON t.id = pt.topic_id\n LEFT JOIN user_badges ub ON ub.badge_id = 17 AND ub.user_id = pt.user_id\n WHERE ub.id IS NULL AND t.posts_count > 100\n GROUP BY pt.user_id, pt.topic_id, t.posts_count\n HAVING count(*) >= t.posts_count\n )\n", :default_badge_grouping_id=>1, :auto_revoke=>false, :system=>true} - Badge {:id=>16, :name=>"Read Guidelines", :badge_type_id=>3, :multiple_grant=>false, :target_posts=>false, :show_posts=>false, :query=>" SELECT user_id, read_faq granted_at\n FROM user_stats\n WHERE read_faq IS NOT NULL AND (user_id IN (:user_ids) OR :backfill)\n", :default_badge_grouping_id=>1, :trigger=>8, :system=>true} - Badge {:id=>14, :name=>"First Link", :badge_type_id=>3, :multiple_grant=>false, :target_posts=>true, :show_posts=>true, :query=>" SELECT l.user_id, l.post_id, l.created_at granted_at\n FROM\n (\n SELECT MIN(l1.id) id\n FROM topic_links l1\n JOIN badge_posts p1 ON p1.id = l1.post_id\n JOIN badge_posts p2 ON p2.id = l1.link_post_id\n WHERE NOT reflection AND p1.topic_id <> p2.topic_id AND not quote AND\n (:backfill OR ( p1.id in (:post_ids) ))\n GROUP BY l1.user_id\n ) ids\n JOIN topic_links l ON l.id = ids.id\n", :default_badge_grouping_id=>1, :trigger=>2, :system=>true} - Badge {:id=>15, :name=>"First Quote", :badge_type_id=>3, :multiple_grant=>false, :target_posts=>true, :show_posts=>true, :query=>" SELECT ids.user_id, q.post_id, q.created_at granted_at\n FROM\n (\n SELECT p1.user_id, MIN(q1.id) id\n FROM quoted_posts q1\n JOIN badge_posts p1 ON p1.id = q1.post_id\n JOIN badge_posts p2 ON p2.id = q1.quoted_post_id\n WHERE (:backfill OR ( p1.id IN (:post_ids) ))\n GROUP BY p1.user_id\n ) ids\n JOIN quoted_posts q ON q.id = ids.id\n", :default_badge_grouping_id=>1, :trigger=>2, :system=>true} - Badge {:id=>11, :name=>"First Like", :badge_type_id=>3, :multiple_grant=>false, :target_posts=>true, :show_posts=>true, :query=>" SELECT pa1.user_id, pa1.created_at granted_at, pa1.post_id\n FROM (\n SELECT pa.user_id, min(pa.id) id\n FROM post_actions pa\n JOIN badge_posts p on p.id = pa.post_id\n WHERE post_action_type_id = 2 AND\n (:backfill OR pa.post_id IN (:post_ids) )\n GROUP BY pa.user_id\n ) x\n JOIN post_actions pa1 on pa1.id = x.id\n", :default_badge_grouping_id=>1, :trigger=>1, :system=>true} FAILED -------------------- Pups::ExecError: cd /var/www/discourse && su discourse -c 'bundle exec rake db:migrate' failed with return # Location of failure: /pups/lib/pups/exec_command.rb:108:in `spawn' exec failed with the params {"cd"=>"$home", "hook"=>"bundle_exec", "cmd"=>["su discourse -c 'bundle install --deployment --verbose --without test --without development'", "su discourse -c 'bundle exec rake db:migrate'", "su discourse -c 'bundle exec rake assets:precompile'"]} e1234564e2ecee1e26a6cc8cb030295d39a2641ca72a906e06d1bc87e56b0832 ** FAILED TO BOOTSTRAP ** please scroll up and look for earlier error messages, there may be more than one It again looks like Discourse is assuming the First Like badge has id=11, but our has id=5. I’ve looked 006_badges.rb, but don’t see any assumption about an id=11 for First Like badge. Any suggestions on how to get past error would be greatly appreciated.

Upgrades failing on migration with unique constraint violation

Product Support

sam (Sam Saffron) October 9, 2017, 8:57pm 3

Maybe try hard nuking the badge from the badges table?

Topic		Replies	Views
Upgrade fails while migrating Self-hosting	4	974	July 11, 2019
Upgrade failed with "duplicate key value violates unique constraint" error Self-hosting	17	2729	February 29, 2020
Error during upgrading from Beta 3.1.x to latest Self-hosting	2	945	January 20, 2023
Failed upgrade: UniqueViolation: duplicate key value violates unique constraint "data_explorer_queries_pkey" Self-hosting	11	2298	September 10, 2020
Duplicate key value violates unique constraint on a table during upgrade Self-hosting	5	2033	June 13, 2020

Upgrades failing on migration with unique constraint violation

Related topics