I'm trying to move a database from a Windows2003Server with mysql 5.0.27-community-nt to a new server with CenTOS. When using mysqldump on the database on the windows server, one table (i haven't started checking how widespread the problem is) is being dumped with quite some deleted records in it. After restoring the dump on the new server, I can easily spot records that exist on the new server but not on the old one. However, when I do a select count(*) both tables show the same record. The dump is created with --opt, and I also tried dropping the new server's table before a restore. Reading through the dump (I did a table exclusive dump) I can spot the inserts for the records that exist in one database but not the other one. I also tried doing a flush tables before dumping the table, and tried using SQLyog's export to SQL function, all with the same result.

What can be going on?

  • What you're describing sounds impossible. The mysqldump utility creates the INSERTs by executing essentially SELECT * FROM table_name and transforms the results back into INSERT statements. One possibility is that you might have an index-related problem that's keeping you from finding the records on the old server using WHERE, but they're really there. The fact that COUNT(*) returns the same value seems to suggest that this might be the case. – Michael - sqlbot Feb 18 '13 at 20:59
  • Yes I know it should be impossible. In fact what you say is exactly what happened. MySQL just decided to throw the index error a couple of hours down the road instead of right away. – nriveros Feb 20 '13 at 15:21
  • If my suggestion sent you in the right direction, I could post it as an answer that you could mark as the accepted answer. – Michael - sqlbot Feb 20 '13 at 16:51
  • It didn't, but it could for someone else, and it IS entirely correct. So post it and I'll remove my own and vote yours. – nriveros Feb 20 '13 at 20:30

In case anyone reaches this, and to tag it as finished, the problem was a corrupted index file. MySQL threw the error a couple of hours after and after a REPAIR TABLE it was clear the data was exactly the same.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.