Fixing damaged MySQL tables - Error 1712 and Error 2013
Archive - Originally posted on "The Horse's Mouth" - 2015-01-25 16:44:16 - Graham EllisERROR 1712 (HY000): Index smf_messages is corrupted
ERROR 2013 (HY000): Lost connection to MySQL server during queryA
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `smf_messages` at row: 135457
Oops ... I don't know what to say ...
"Don't Panic" in the words of Corporal Jones (Dad's Army) [here]
"I don't BELIEVE it" in the words of Victor Meldrew (One foot in the grave) [here]
For readers who are to young to remember those shows, or aren't from Great Britain ... something seems to have gone awfully wrong with the database. If I had been using MyIsam tables, mysqlcheck would identify the problem and call repair table to (try to ) fix the issue. But I wasn't - I was using Innodb tables.
Don't panic - provided you have a reasonably recent backup, and the table in question is write-mostly (which mine is - it's forum messages)
Method
a) Create a new table structure the same as the damaged table:
mysql> create table newmessages like smf_messages;b) Copy messages from the end of the damaged table into the new table:
mysql> insert newmessages select * from smf_messages order by id_msg desc limit 20;c) Backup the newly created (short) table:
/usr/local/mysql/bin/mysqldump -h 127.0.0.1 -u fgw -p fgw newmessages > noloss.sqld) delete the damaged table (having double checked you have a GOOD, COMPLETE backup!):
mysql> drop table smf_messages;
e) Extract the data you want to restore out of your backups
(Edit copy of fgw.sql to contain only the inserts for the smf_messages)
mysql> source fgw.sql.copyf) Trim back the end of the update table so that there's no overlap, and install it too
(Edit noloss.sql to contain only the extra posts added)
mysql> source noloss.sql.copyAnd it should be "Robert's your parent's brother" - sorry - "Bob's your Uncle" [here].