I'm trying to repair a table in my database. Currently, my database is empty (no tables). I found a backup of the database and grabbed three files.

  • comments.frm
  • comments.MYI
  • comments.MYD

I then put these files in the database folder: /var/lib/mysql/shf_main2

When I run a repair, I get this error a lot.

| shf_main2.comments | repair | warning  | Duplicate key for record at 36960720 against record at 152   |
| shf_main2.comments | repair | warning  | Number of rows changed from 182427 to 52 

There are 123355 records in the backup. When I check the table, there are only 52 records. How can I recover all of them?

I have tried repairing by these methods which all give the same results:

SSH Access commands

  • myisamchk -r *.MYI

  • myisamchk -o *.MYI

  • myisamchk --safe-recover *.MYI

SQL Commands

  • REPAIR TABLE comments;

I do not understand why its getting a dulicate key for all by 52 records. Thank you for any help.

  • Reconstruct table structure removing unique constraints and replace old table structure file (and index file too) with edited one. I.e. create an empty copy of this table in separate (temporary) database without unique constraints, copy old data file (.MYD) instead of new empty one, then try to repair this table.
    – Akina
    Aug 31, 2018 at 7:10
  • After you clean up the data (as much as possible), change to InnoDB so that the problem won't happen again.
    – Rick James
    Sep 30, 2018 at 17:54


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.