Rebuilding app fails at db:migrate with unique constraint 'badges_pkey' error

(etewiah) #1

I just now tried to rebuild my docker installation with the following command:

./launcher rebuild app

When it gets to running db:migrate, the following error regarding the unique constraint “badges_pkey” occurs:

I, [2014-09-23T02:09:50.362876 #47]  INFO -- : > cd /var/www/discourse && su discourse -c 'bundle exec rake db:migrate'
2014-09-23 02:10:05 UTC ERROR:  duplicate key value violates unique constraint "badges_pkey"
2014-09-23 02:10:05 UTC DETAIL:  Key (id)=(1) already exists.
2014-09-23 02:10:05 UTC STATEMENT:  INSERT INTO "badges" ("badge_type_id", "created_at", "id", "name", "query", "system", "trigger", "updated_at") VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING "id"
rake aborted!
ActiveRecord::RecordNotUnique: PG::Error: ERROR:  duplicate key value violates unique constraint "badges_pkey"
DETAIL:  Key (id)=(1) already exists.
: INSERT INTO "badges" ("badge_type_id", "created_at", "id", "name", "query", "system", "trigger", "updated_at") VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING "id"

Can anyone give me some pointers as to how I can go about resolving this please?


Upgrades failing on migration with unique constraint violation
(Sam Saffron) #2

Something is fishy about your badges table. What is in it?

(etewiah) #3

Thanks for the quick response Sam, much appreciated :wink:

This is the first item ( ‘Badge.first’ from rails console):

<Badge id: 2, name: "Member", description: nil, badge_type_id: 3, grant_count: 6, created_at: "2014-08-12 16:02:34", updated_at: "2014-09-21 15:41:29", allow_title: false, multiple_grant: false, icon: "fa-certificate", listable: true, target_posts: false, query: "\n    SELECT user_id, current_timestamp grante...", enabled: true, auto_revoke: true, badge_grouping_id: 4, trigger: 4, show_posts: false, system: true>

and this is badge.last

<Badge id: 23, name: "Great Share", description: nil, badge_type_id: 1, grant_count: 0, created_at: "2014-09-15 10:28:46", updated_at: "2014-09-15 11:24:47", allow_title: false, multiple_grant: true, icon: "fa-certificate", listable: true, target_posts: true, query: "    SELECT views.user_id, i2.post_id, i2.created_a...", enabled: true, auto_revoke: true, badge_grouping_id: 2, trigger: 0, show_posts: true, system: true>

(Sam Saffron) #4

There seems to be indicating you have two badge id 1s in the db … how many dupes do you have in that table? can you nuke them ?

(etewiah) #5

After googling a bit I followed a suggestion to run this from the postgres console:

discourse=# SELECT MAX(id) FROM badges;

result was 23

and for

discourse=# SELECT nextval('badges_id_seq');

result was 60 so according to that article , that isn’t the problem

(etewiah) #6

Sorry, how do I find dupes?

(Sam Saffron) #7
select id, count(*) from badges 
group by id 

(etewiah) #8

Just tried that and there is only 1 of each

After the error I have reported, the script goes on to try to load various fixtures which I don’t quite understand. This is the last entry from the log before the console hangs:

``== 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, :default_name=>“Basic User”, :badge_type_id=>3, :query=>"\n SELECT user_id, current_timestamp granted_at FROM users u\n WHERE trust_level >= 1 AND (\n :backfill OR IN (:user_ids)\n )\n", :default_badge_grouping_id=>4, :trigger=>4, :default_allow_title=>false, :system=>true}``

(etewiah) #9

Here is the full extent of the console output as appears on my screen:

The top part is missing as it has scrolled past my screen buffer. Perhaps there is somewhere I can get the full output.

What would you say is the safest way of nuking the badges table?

(Sam Saffron) #10

Are there any manual badges assigned ?

(Sam Saffron) #11

Really confused why this line is not finding the badge seed-fu/seeder.rb at master · mbleigh/seed-fu · GitHub

(etewiah) #12

No manual badges ( I don’t even know how to do that)

This is the result of

select name, count(*) from badges group by name

  name       | count

Great Post | 1
Read Guidelines | 1
Autobiographer | 1
Reader | 1
Regular | 1
Good Post | 1
First Share | 1
Welcome | 1
Nice Topic | 1
First Flag | 1
Nice Post | 1
Great Topic | 1
Great Share | 1
Good Topic | 1
Leader | 1
Good Share | 1
Editor | 1
First Quote | 1
Member | 1
First Like | 1
Nice Share | 1

(Sam Saffron) #13

You can try to just empty the badges table and run the migration, it should rebuild it. I still don’t understand the error.

(etewiah) #14

Could it be the result of my db being corrupt? When I try this from the console


I get this:

ActiveRecord::RecordNotFound: Couldn't find Badge with 'id'=1

I will go ahead and run Badge.destroy_all from the console and see if the rebuild works.

(Sam Saffron) #15

how is that even possible? very confusing, is there a missing index somehow

(etewiah) #16

This is probably unrelated but might as well mention it.

When I run Badge.destroy_all I get the ff error:

NoMethodError: undefined method `publish_notifications_state' for nil:NilClass
from /var/www/discourse/app/models/notification.rb:132:in `refresh_notification_count'

delete_all appeared to work but said 18 items had been deleted. A Badge.count immediately after showed 1 record remaining.
Subsequent calls to delete_all won’t get rid of that last one!!! Something been confuuusing my poor database.

I can’t even delete it from postgres console. I’m at my wits end. Going to sleep on this and hope I have some inspiration when I wake up.

Thanks for your help on this Sam - I really appreciate it. It seems like some sort of wierd db corruption.

(Dave McClure) #17

What error do you get when you try to do that?

(etewiah) #18

Sorry, I went to get some sleep.

I don’t get an error message. It just doesn’t delete anything - so I run Delete from badges;
and the output is

I’m going mad - I really don’t want to lose data as I don’t have a backup for the last few days but I don’t know any way of clearing the badges table without droping and rebuilding the db.

(Dave McClure) #19

Hmmm… I was thinking (long shot) maybe you got some referential constraint in there somehow (like a record in another table with a foreign key constraint on badge with an ON DELETE RESTRICT).

So after the delete from badges; if you do a select id, name from badges; do you get any output there?

It wouldn’t hurt to manually dump your database before monkeying around too much in that case.

(etewiah) #20

Thanks for getting back to me David.

Yes, I’d say there is something fundamentally screwed with my db. Its just taking me forever to take the simple steps needed to fix that. After a bit of digging, I found a post somewhere suggesting this to do a pg_dump:

sudo -u postgres pg_dump -xOf /shared/backups/discourse-backup-no2.sql -d discourse -n public

That just gives me a permission denied error for the output file. Eventually, this seemed to work:

sudo -u postgres pg_dump discourse > shared/backups/discourse_bup_23_spt_2014

I’m now trying to reset my db but even something as simple as that is taking me a while. I’m pretty sure this should work:

RAILS_ENV=production su discourse -c 'bundle exec rake db:reset'

but I get an error “PG::Error: ERROR: must be owner of database discourse”

I can mess around and try different user but it feels very much like shooting in the dark. If I use the wrong user I’ll probably cause more problems down the line :frowning: