MySQL: Failed Registration of InnoDB as a Storage Engine

It happens countless times for many reasons. You attempt to start or restart your “MySQL server”:http://dev.mysql.com/ after a small configuration change and MySQL stubbornly refuses to start. Or, if it does start, some important functionality, such as “InnoDB”:http://dev.mysql.com/doc/refman/5.0/en/innodb-storage-engine.html support, is missing.

Most Linux users will be familiar with the following pleasant response:

MySQL Fails to Start

Starting MySQL . . . . . . . [FAILED]

In the case of a configuration change, MySQL clearly fails to start because of the change, but why? Some changes, such as the size of a log file, seem trivial.

Sometimes the cause can be difficult to track down, especially if you don’t know where to look for clues.

h2. MySQL Logging

MySQL has “many types of logs…”:http://dev.mysql.com/doc/refman/5.0/en/server-logs.html a general query log, a connection log, an error log, a binary log, a slow query log, etc. And then there are the Linux system logs.

In the case of a failed MySQL start due to a configuration error or MySQL problem, details are usually output to the “error log”:http://dev.mysql.com/doc/refman/5.0/en/error-log.html. The default location of the error log is the “data directory”:http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html (usually /var/lib/mysql). The log is normally named based on the hostname of the server. For example, database.example.com.err.

If this file doesn’t seem to exist in your MySQL data directory, MySQL has probably been configured to log elsewhere. Check your my.cnf file, which is normally at /etc/my.cnf. The my.cnf file can contain a log-error option in the [mysqld] section which specifies a different location of the log. If no such option exists and you can’t find the log, try specifying a location for the log yourself, such as log-error = /var/lib/mysq/mysql_error.log

h2. InnoDB Log Files

A common configuration change which can prevent MySQL from starting involves the InnoDB log file size. The “InnoDB log file”:http://www.pythian.com/news/1242/innodb-logfiles/ is the ‘redo’ or recovery log for the InnoDB storage engine. It contains transactions which have been committed to a MySQL table, but which have not yet been written to disk. Should MySQL crash and lose the contents of its buffer pool, the log file can be used to recover any data changes which were in the buffer pool. These recovered data changes can then be written to disk.

The my.cnf file contains a few options which affect the InnoDB log files. In this instance, the key one is innodb_log_file_size. This option sets the size of the InnoDB log file.

h2. Failed Registration of InnoDB as a Storage Engine

Frequently individuals decide to make changes to the innodb_log_file_size option. Generally, they attempt to increase the value, and restart the MySQL server. Unless the “proper procedure”:http://dev.mysql.com/doc/refman/5.0/en/innodb-data-log-reconfiguration.html is followed, MySQL will fail to start (or start without support for InnoDB). The individual may be confused about why MySQL suddenly decided not to start. A quick check of the MySQL error log will usually reveal the cause.

In the case of changing the innodb_log_file_size, one will often find an error similar to the following:

110509 12:04:27  InnoDB: Initializing buffer pool, size = 384.0M
110509 12:04:27  InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 157286400 bytes!
110509 12:04:27 [ERROR] Plugin 'InnoDB' init function returned error.
110509 12:04:27 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
110509 12:04:27 [ERROR] Unknown/unsupported table type: innodb
110509 12:04:27 [ERROR] Aborting

110509 12:04:27 [Note] /usr/sbin/mysqld: Shutdown complete

While the cause of MySQL’s failure to start is obvious from the log file, this is often overlooked.

There are two solutions to this particular problem:

* Restore the my.cnf file to its original state, with an innodb_log_file_size equal to the actual size of the existing InnoDB log files.

* Rename or move both the ./ib_logfile0 and ./ib_logfile1 files, and then start the MySQL server.

The ./ib_logfile0 and ./ib_logfile1 files are located in the InnoDB data directory (usually /var/lib/mysql). Both files must be moved or renamed for the above procedure to work. When starting MySQL, new InnoDB log files of the appropriate size will be created.

The original InnoDB log files only need to be kept as long as they may be needed for data recovery. If the MySQL server successfully starts after the above procedure, and all data is intact, the original InnoDB log files can be discarded.

Conversation
  • Tony says:

    THANK YOU.

    My web server wouldn’t boot but I could access the file system. I was able to get my sites back up and running by simply copying the data from the old server’s mySQL database directories to the new server and renaming the ./ib_logfile0 and ./ib_logfile1 files.

  • Blayo says:

    Thanks a lot for this great explanation. It works fine.

  • Wow! Thank you very MUCH! I edit my.cnf then MaridDB refuse to start! You save me! Thanks for the clear explaination!

  • Luz Marina Carvajal G says:

    Gracias, muy buena explicación

  • Diego says:

    It was very useful for us . Thanks for your article!!! :)

  • Srinivas Madiwala says:

    Thank you very much, I was facing similar issue and this information helped me.

  • Thank you, this article very help for my case.

  • George Millo says:

    Thanks! My error was slightly different from yours, but I’ll post it here to help a future Googler find this page:

    [ERROR] Plugin ‘InnoDB’ init function returned error.
    [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
    [ERROR] Unknown/unsupported storage engine: InnoDB

    Note that it’s complaining on the last line about the “storage engine”, not the “table type”.

    I found the above error in the logfile like you mentioned, but the error that originally confused me was that I was getting the following failure when trying to run ‘mysql.server start’:

    > ERROR! The server quit without updating PID file (/usr/local/var/mysql/name-of-my-computer.home.pid).

    Removing the ./ib_logfile0 and ./ib_logfile1 files fixed the problem. Since this is my development machine and the data isn’t important, I could safely just delete these files and not care about recovering any lost data.

  • hanafi bin majid says:

    Thanks for the solution…!

  • […] References:- JUSTIN KULESZA (2011). MySQL: Failed Registration of InnoDB as a Storage Engine. Available at: https://spin.atomicobject.com/2011/05/09/mysql-failed-registration-of-innodb-as-a-storage-engine/. […]

  • ghazal says:

    So relieved I found your solution. I got my SQL server back finally.
    Thanks a lot.

  • Oleksandr says:

    Worked fine, thanks!

  • […] MySQL: Failed Registration of InnoDB as a Storage Engine […]

  • Atul says:

    thank you.

  • Jade says:

    Thanks for the article!

  • zomars says:

    Thank you so much!

  • thank you somuch, it work (y)

  • Azdin says:

    For me was this the Solution:
    false: innodb_log_file_size=4096M
    true: innodb_log_file_size=1024M

  • A Raw says:

    Thats greate and straight resolution of problem.
    Thanks.

  • Renee says:

    New to innodb + mariadb, this restored my sanity! The innodb_log_file_size change was my issue. Thank you for a simple, straightforward explanation and solution.

  • mike says:

    Thanks for Your help!!:) I deleted files (ib_log..), and server/mysql service has started:)
    With regards
    mike

  • Thanks, this one had me for a minute on one of our ancillary servers that has a lot of activity. The log size limitation were all the words I needed to read.

    • To clear: My issues ended up being a bit more severe, though analysis of the error log revealed other issues. Eventually I forced an innodb recovery value of 6 to repair the database and continue on while I watch for possible faults again. This is basically an overloaded database running on inadequate cloud hardware (again, ancillary optional server).

  • Saiful Islam Rokon says:

    Thanks

  • Comments are closed.