What Happens when InnoDB Fails to Start

Posted in: Technical Track

Today I’ve spent some time (more than this issue was worth, actually) on a client’s system trying to
find out why table was not accessible and failed with the following error:

mysql> SHOW CREATE TABLE test_table;
ERROR 1033 (HY000): Incorrect information in file: './test/test_table.frm'


The error message suggested something went very wrong with .frm file and I already started thinking about restoring the table from backup, when I noticed that accessing any InnoDB table was producing same error. A quick check of the error log showed that when MySQL server was restarted some time ago InnoDB failed to initialize due to a memory issue.

| Engine     | Support  | Comment                                                        |
| InnoDB     | DISABLED | Supports transactions, row-level locking, and foreign keys     | 
12 rows in set (0.02 sec)

Restarting MySQL fixed the problem right away. But I was very frustrated that I spent so much time on this, just because error message was misleading, so I decided to check if MySQL improved it’s messages in newer versions. I observed this error on 5.0.77 and I did a simple test with 5.1.52 and 5.5.8.

I caused InnoDB to fail at start up by changing innod_log_file_size variable to mismatch existing log files. Both 5.1 and 5.5 gave a reasonable error message this time:

mysql [localhost] {msandbox} (test) > SHOW CREATE TABLE test;
ERROR 1286 (42000): Unknown storage engine 'InnoDB'


And InnoDB was not in the list of available storage engines:

master [localhost] {msandbox} ((none)) > SHOW ENGINES;
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
7 rows in set (0.00 sec)

One difference in 5.1 and 5.5 behavior is that InnoDB is the default storage engine in 5.5 and MySQL
fails to start if it can’t initialize the default storage engine. When you change default-storage-engine to, say, MyISAM and run this test case you will get same behavior with 5.5 as with 5.1

What seems to have changed between 5.0 and 5.1 is a switch to pluggable storage engine architecture in 5.1, if one of the plugins fails MySQL is able to identify this and produce the correct error message.

Want to talk with an expert? Schedule a call with our team to get the conversation started.

No comments

Leave a Reply

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