How to fix index corruption in InnoDB table?
A: Use OPTIMIZE TABLE to rebuid the InnoDB table that have corrupt indexes. This causes a complete table rebuild which fixes the corruption. You may have the easiest solution there is. However, I would love to clarify some things:
How to fix an InnoDB table in MySQL?
However, if an InnoDB table has problems, you cannot fix it by using REPAIR TABLE because that statement applies only to MyISAM. If a table check indicates that an InnoDB table has problems, you should be able to restore the table to a consistent state by dumping it with mysqldump, dropping it, and recreating it from that dump.
Why does alter table my _ table engine = InnoDB?
In regards to your original question, when you ran ALTER TABLE my_table ENGINE=InnoDB; you simply rebuilt index pages reading from data pages of the table, most likely bypassing the ibdata1 Insert Buffer. This is why that worked for you.
What to do if mysql table has corruption?
If a table check indicates that an InnoDB table has problems, you should be able to restore the table to a consistent state by dumping it with mysqldump, dropping it, and recreating it from that dump. In the event of a crash of a MySQL Server or on the host on which it runs, some InnoDB tables might need repairs.
What causes data corruption in a MySQL database?
Data corruption can also occur if the MySQL server daemon restarts suddenly, or your server reboots due to a crash of other OS components. If the database instance was in the middle of writing data to disk, it could write the data partially which may end up with a page checksum that is different than expected.
Is there a way to fix corrupted MySQL?
We have developed software that uses MySQL. After some months of usage, some of the database tables had been corrupted and need to be repaired. Because of the corruption,we are not able to create a backup using Navicat, and we can not generate a dump too.
What happens when a MyISAM table is corrupted?
MyISAM tables can easily get corrupted when lots of writes happen and a lot of locks happen on that table. The storage engine “writes” data to the filesystem cache, which may take some time before it is flushed to disk. Therefore if your server restarts suddenly, some unknown amount of data in the cache is lost.