Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

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 significant amount of work. I have an almost-current database design however no backup of the data that had been generated to-date)

update 1
Adding innodb_force_recovery = 6 into my.cnf allows SHOW TABLE STATUS to execute successfully, values of <= 5 still result in the error as shown above. With this flag SELECT * FROM baskets operates, however one table specifically has the error returning still indicating it is one table that perhaps is causing the problem?

Ultimately following more hours of scanning through recovery files I can only conclude that my data was lost (as RolandoMySQLDBA had said). To get as far as I did I followed DTest's suggestion on the Percona Recovery Tools, however the data corruption meant that the tools were unable to extract the data from my ibdata1 file for the specific table.

Of course this meant total data loss from the effected table which is a concern, however I can only hope that adding innodb_file_per_table will help with data recovery should this recur - I intend to kill the power at some point to attempt to try and reproduce this on test database.

Below is some more verbose information from the logs.

my.cnf

This is just a dev system so the my.cnf is VERY basic, in fact unchanged from default

3 Answers
3

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.

innodb_file_per_table seems like a wise option to add in at this point then - is this viable without rebuilding or would it require reimporting all data? Regarding the specific question above, will go through the above soon and see how it goes however my concern is that the corrupt table is the one I care most about!
–
Simon at mso.netMar 2 '12 at 15:56

It does not help with the metadata in a corrupt state.
–
RolandoMySQLDBAMar 2 '12 at 16:03

inndob_file_per_table will be in use from now on. Have restored structure from the workbench file along with stored procs. etc. Just need to look at this answer shortly to try to recover the data from that last table.
–
Simon at mso.netMar 2 '12 at 16:21

Right, I'm getting somewhere with this. So far I get as far as 3- Extract the rows however am getting an error returned, Segmentation fault (core dumped) along with a partial (unfortunately 1.5 rows) dump of the data
–
Simon at mso.netMar 6 '12 at 9:27

-1 on this for 4 reasons : 1) Your answer makes ABSOLUTELY NO ATTEMPT to recover data. 2) Killing mysql_safe (or is that mysqld_safe) does not kill mysqld. They are separate processes. 3) You did not include safety measures to recover working tables in the conflicting database. A little effort would spare you even the slightest data loss. 4) Dropping the offending database may wreak havoc on the InnoDB crash recovery phase of mysql startup. You should have executed set global innodb_fast_shutdown = 0; to completely flush transaction logs and double write buffer during normal shutdown.
–
RolandoMySQLDBAJul 17 '13 at 14:15