Changing storage engine to InnoDb from MyIsam is dropping records from table

Posted on

Question :

Currently our Database all tables are using MyIsam as a storage engine. We tried changing storage engine of our mysql tables from MyISAM to InnoDB from PHPMyAdmin operations tab. However during the change table records are dropped. So what can be the possible factor causing this problem.

This is what we tried:

We converted test_user table which was having storage engine MyIsam and was having total records 11k. When we converted it into InnoDB the records droped to 9k. This happened for many tables. Not sure what we are doing wrong.

Answer :

Are you are using SHOW TABLE STATUS to determine that there is a loss? Don’t trust it. The “Rows” is an estimate in the case of InnoDB. Use SELECT COUNT(*) FROM tbl to get the exact row count.

More tips on the conversion:

Leave a Reply

Your email address will not be published. Required fields are marked *