InnoDB table SELECT returns ERROR 2006 (HY000): MySQL server has gone away (after power outage)

This server is a virtual machine running CentOS 6.0, MySQL 5.5.21

There is a database named devSystem. There are InnoDB tables within. Running the following commands causes error ERROR 2006 (HY000): MySQL server has gone away. I have not previously used InnoDB extensively and so have not yet experienced this type of issue. I can only assume it's specific to InnoDB as it has not surfaced using MyISAM. Anyway, the commands I attempt to run are as follows.

mysql -u root -p
mysql> USE `devSystem`;
-- database changed
mysql> SHOW TABLES;

Full error as returned by MySQL client

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: devSystem

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
ERROR:
Can't connect to the server

Now trying to SELECT from table baskets which exists in devSystem

mysql> select * from baskets\G
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: devSystem

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
ERROR:
Can't connect to the server

For comparison, I tried a second database which also has InnoDB tables on same server

mysql> USE `testSystem`;
-- database changed
mysql> SHOW TABLES;

This output the tables correctly. For this reason I surmise that it's an issue with devSystem specifically.

I have tried searching on here however other similar queries don't seem to help, does anyone have any suggestions/advice that will help me solve this? This has so far wasted my whole morning!

Current options seem to be to remove the database and start over (however will lose a signif

This looks oddly familiar.

I have seen this occur with one of my web hosting client's DB servers. There was a particular table that crashed mysqld every single time you accessed it, even with SHOW CREATE TABLE.

The problem stems from a corrupt data dictionary. There is really no way to correct it. You could attempt to alter the tablespace_id within the .ibd file but the headache stems from locating the tablespace_id list internal to ibdata1.

Even if you create a MyISAM table with the same name in the same database as the original InnoDB table, you cannot convert it to InnoDB because the tablespace_id is already associated with the table name. This, of course, is a corrupted state. It's like having a pidgeon hole in ibdata1 that you cannot patch up without some exploratory surgery.

You may have to mysqldump everything except the database that houses the corrupt table. You would then have to mysqldump every table in that database except the corrupt table. Remember, it is the data dictionary's view of the table that is screwed up, not necessarily the table's data.

The only sure way to clean everything up is to perform the mysqldumps as I just specified, shutdown mysql, rm -rf all DB folders except /var/lib/mytsql/mysql, delete ibdata1, delete ib_logfile0, delete ib_logfile1, startup mysql, reload all mysqldumps. See my StackOverflow post about cleaning up your InnoDB infrastructure.

Since you are not using innodb_file_per_table, any tables with this corrupt state of things within ibdata1 are lost as casualities of war. My condolences.

For future reference, click here to see an artistic conception of InnoDB and its Internals.

StackExchange Database Administrators Q#14259, answer score: 5