Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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?

share|improve this question
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 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 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 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 at 20:30

closed as too localized by Mark Storey-Smith, RolandoMySQLDBA, dezso, JNK♦ Feb 22 at 13:49

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, see the FAQ.

1 Answer

up vote 0 down vote accepted

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.

share|improve this answer

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