REINDEX not working: Table contains duplicated values.

[HowdyDoodee]

ERROR: could not create unique index "handhistories_pkey"
DETAIL: Table contains duplicated values.

Hi,
I believe I double imported some hand histories into my main computer (from my laptop).

I am trying to VACUUM and REINDEX my database, but it is not working anymore.

What is the safest way for me to clean this up? Thanks.

SOLUTION FOUND: see summary steps a few posts down in thread

[morny]

Hi, Its indicating a corruption with your database, try this FAQ - Hold'em Manager Poker Tracking Software :: Fix Indexes (http://faq.holdemmanager.com/questions/86/Fix+Indexes)

[HowdyDoodee]

Hi, Its indicating a corruption with your database, try this FAQ - Hold'em Manager Poker Tracking Software :: Fix Indexes (http://faq.holdemmanager.com/questions/86/Fix+Indexes)
Okay, I have done this, but the fix/rebuild still comes back with an error

Index rebuilding has failed: ERROR: 23505: could not create unique index "handhistories_pkey"

I am sure that this is because I imported some hand histories twice from my laptop. Everything in HEM seems to being working, but I cannot vacuum or reindex without an error.

[netsrak]

In a normal working database it is not possible to import a hand twice. This will be detected and reported as duplicate hand during the import process.

So i think your database is damaged. You should create a new empty database and reimport your hands.

[HowdyDoodee]

Isn't there a sql-query that I can run to find out what the "duplicate values" are? That would let us know if there actually are handhistory duplicates or not.

Many of the hands I had were on a USB and I deleted them after importing them. That means a "new database" would not have all hands I have done this last 6 months (300,000+). I really prefer to keep this database if at all possible.
Thanks.

========
ERROR: could not create unique index "handhistories_pkey"
DETAIL: Table contains duplicated values.
========

[netsrak]

Does the export of hands work?
Then i suggest you do this, create a new empty database and reimport the hands.

[HowdyDoodee]

Does the export of hands work?
Then i suggest you do this, create a new empty database and reimport the hands.

DONE with bad side-effect. It seems that I have lost ALL of my player notes and ALL marked cards.

The export/import revealed that there were "38 duplicate hands" out of 350K hands. I suspect that this occurred during one of the betas coming up to 1.11

QUESTION: wouldn't it be better for me to remove the duplicate 38 hands from the original database? I have a mountain of notes (during 350K hands). It seems silly to lose all of these notes because of a beta import problem.

Thanks for your help with this. I appreciate it.


verification update:
I have now verified this by switching between the two databases (new and original). All player notes are lost in the new database (where the hands were reimported). I will need to use the original database.

Will probably need a sql-script to remove the 38 duplicate hands imported during one of the betas.

[morny]

The 38 duplicates is the duplicate files it encountered but it wouldnt have imported them its just a notification.

Load the new database again via Options > Database Management

Export Your Notes
http://faq.holdemmanager.com/images/notes-02.jpg

Export Your Bonuses and Rakeback - Options > Rakeback & Bonuses http://faq.holdemmanager.com/images/rakeback-bonuses-01.jpg

Export any tourney summaries by selecting all your tournaments in the Data View Sub-Tab of the Tourney > Results Menu and choose Export Tourney Results and summaries

http://faq.holdemmanager.com/images/tourney-data-view-01.jpg

Then reload the new database and import those into the new database and all should be fine then as the problem was a corrupt database which is solved with the new one

[HowdyDoodee]

Issue/Problem:
========
ERROR: could not create unique index "handhistories_pkey"
DETAIL: Table contains duplicated values.
========
Would not completely reindex or vacuum database (otherwise database was working okay).


Summary of the steps taken to resolve the problem (with success):

Export data from OLD database

Export Handhistory: from Database Management......Export Hands
Export Player Notes: from Options>>Notes.......Export
Export Tournament Results: (see above post by Morny/support)
Export Rake/Bonuses: (see above post by Morny/support)

Create a NEW database

Create new DB.....from Database Management (in HEM)....click Create New Database
Assure that the NEW database is the default......click Set as Default
click Start Holdem Manager
Import data into NEW database

Import Handhistory: on IMPORT......Import File(s) (exported from OLD DB)
Import Player Notes: from IMPORT......Import File(s) (exported from OLD DB)
Import Tournament Results: from IMPORT......Import File(s) (exported from OLD DB)
Import Rake/Bonuses: (see above post by Morny/support)

Check all data-points in HEM

e.g. Are NOTES still on players? ALL Tournaments imported?
OPTIONAL: after completing the migration from OLD to NEW DB

**REINDEX, VACUUM, and BACKUP to insure that repairs have been successful.
**Delete/Remove old database in pgAdmin (after making sure NEW DB is okay)



Thanks to Morny/Tech-Support for helping me with this.