Solving MySQL InnoDB Corruption

Today I got a scary error message when running some database migration scripts:

mysqli::query(): (HY000/1712): Index countermeasure_costs is corrupted

My first reaction was to just restore an earlier snapshot and run the scripts again. Unfortunately this happened again, so I ran the restoration process again and checked the table with the following command:

check table [table to check];

This returned:

+---------------------------+-------+----------+-----------------------------------------------------+
| Table                     | Op    | Msg_type | Msg_text                                            |
+---------------------------+-------+----------+-----------------------------------------------------+
| vida.countermeasure_costs | check | Warning  | InnoDB: Index "variables_id" is marked as corrupted |
| vida.countermeasure_costs | check | error    | Corrupt                                             |
+---------------------------+-------+----------+-----------------------------------------------------+

Rebooting the mysql service didn't resolve this.

Scouring the net came up with various solutions that were along the lines of converting the table to a MyISAM table for recovery and then back again and using innodb_force_recovery which didn't sound good. Luckily there is an easy solution posted on Stack Overflow that you can perform without any downtime and is quite quick. Simply create a new table and put the data into it before destroying the original and recreating it as so:

CREATE TABLE [new table] LIKE [old table];
INSERT [new table] SELECT * FROM [old table];
DROP TABLE [old table];
CREATE TABLE [old table] like [new table];
INSERT [old table] SELECT * FROM [new table];
DROP TABLE [new table];

References

• Stack Overflow - How do I repair an InnoDB table?
• Percona - Recovering Innodb table Corruption