MySQL table corruption recovery – how to

It happened to me that on a big database one or more tables got corrupted. Basically DB would hang when querying specific rows in specific table or the application log would show something like

OperationalError: (OperationalError) (2002, "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)") None None

Then investigate further in mysql.log and look for trails like this one

InnoDB: Error: tried to read 16384 bytes at offset 0 5914624.
InnoDB: Was only able to read 12288.

It show a likelihood that one of the tables is corrupted. First step to do now should be a backup/dump of everything. If mysqldum/mysqlcheck disconnect at the concrete table try using --ignore-table.

You can attempt fixing concrete table by copying all content to a temporary new table using SELECT INTO. If it fails on entire table you can do this part-by-part using LIMIT and OFFSET keywords. If this doesn’t work either try running mysql with innodb_force_recovery = 1 in [mysqld] section of config – this will make your mysql ignore corrupted rows.

If everything else fails, and you can’t recover any data you can turn innodb_force_recovery = 6, but keep in mind that it works destructiveli and will even deepen corruption of your database. When I last tried it after dumping everything with innodb_force_recovery = 6 the database didn’t run AT ALL without this option.

piotr

Leave a Reply

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