Upgrades failing on migration with unique constraint violation

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?

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 #<Process::Status: pid 10707 exit 1>
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.

Maybe try hard nuking the badge from the badges table?

3 Likes

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:

3 Likes