Recover a corrupt Innodb table

Lost connection error

The Simplton Sphinx indexer kept bombing with the dreaded "Lost connection to MySQL server during query". Here is how I found the bad table and recovered it. Most of the info comes from this article but this post gives step by step instructions with the corresponding commands. 10 steps to breath easier.

1. Find out which is the bad table by doing.

OPTIMIZE table ... 
# on each table until you find the bad one. Should get a "error -1 from storage engine" on the bad table
# OR DO 
./bin/mysqlcheck -ao -u root -p1passwd --socket=/tmp/mysql51.sock simpy_db 
# see which table the connection is lost

2. On the table that is bad dump the schema.

./bin/mysqldump -uroot -p1passwd --no-data simpy_db items --socket=/tmp/mysql51.sock > ~/items-schema.sql

3. Edit ~/items-schema.sql

First, delete the DROP table line, next change the CREATE table name to "items_new". Lastly, make the STORAGE ENGINE MYISAM

CREATE TABLE `items_new` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `comment` text NOT NULL,
  `status` int(4) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `issue_id` int(11) DEFAULT NULL,
  `display_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_3` (`status`),
  KEY `issue_id` (`issue_id`)
) ENGINE=MYISAM AUTO_INCREMENT=74899 DEFAULT CHARSET=utf8;

4. Save the ~/items-schema.sql and import it.

./bin/mysql -uroot -p1passwd issues --socket=/tmp/mysql51.sock < ~/items-schema.sql

5. Edit my.cnf and add the line innodb_force_recovery=1

6. Restart MySQL

7. Log into mysql via command line and copy items table to items_new.

insert into items_new select * from items; # If you don't set innodb_force_recovery this command will bomb.

8. Edit my.cnf and remove or comment out the line innodb_force_recovery=1

9. Restart MySQL

10. Log into mysql via command line. Rename tables and convert items_new to INNODB.

RENAME TABLE items TO  items_old;
RENAME TABLE items_new TO  items;
ALTER TABLE items ENGINE = INNODB;

Everything should be fixed again, for more detailed info see this article.