InnoDB disabled if ib_logfile files corrupted

InnoDB
I recently came across a dev VM running MySQL 5.0.77 (an old release, 28 January 2009) that didn’t have InnoDB available. skip-innodb wasn’t set, SHOW VARIABLES LIKE '%innodb%' looked as expected, but with one exception: the value of have-innodb was DISABLED.

I confirmed this with SHOW ENGINES:

(root@localhost) [(none)]> show engines;
+------------+----------+----------------------------------------------------------------+
| Engine     | Support  | Comment                                                        |
+------------+----------+----------------------------------------------------------------+
| MyISAM     | DEFAULT  | Default engine as of MySQL 3.23 with great performance         |
| MEMORY     | YES      | Hash based, stored in memory, useful for temporary tables      |
| InnoDB     | DISABLED | Supports transactions, row-level locking, and foreign keys     |
...

(and chuckled to myself over the comment about MyISAM’s performance)

/var/log/mysqld.log yielded the answer:

120127 17:17:51  mysqld started
120127 17:17:51 [Warning] /usr/libexec/mysqld: ignoring option '--engine-condition-pushdown' due to invalid value 'InnoDB'
InnoDB: Error: log file ./ib_logfile0 is of different size 0 20971520 bytes
InnoDB: than specified in the .cnf file 0 104857600 bytes!
120127 17:17:51 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.77-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution

innodb_file_per_table was not set on this VM, and when the InnoDB log files had become corrupted and were not recognised, the engine couldn’t start. These ib_logfile files are the crash recovery logs:

The unsung heroes of InnoDB are the logfiles. They are what makes InnoDB automatic crash recovery possible.

Database administrators of other DBMS may be familiar with the concept of a “redo” log. When data is changed, affected data pages are changed in the innodb_buffer_pool. Then, the change is written to the redo log, which in MySQL is the InnoDB logfile (ib_logfile0 and ib_logfile1). The pages are marked as “dirty”, and eventually get flushed and written to disk.

If MySQL crashes, there may be data that is changed that has not been written to disk. Those data pages were marked as “dirty” in the innodb_buffer_pool, but after a MySQL crash the innodb_buffer_pool no longer exists. However, they were written to the redo log. On crash recovery, MySQL can read the redo log (InnoDB log files) and apply any changes that were not written to disk.

edit: As pointed out by James Day in the comments, the original solution listed could cause further problems and so is only appropriate to a development/staging environment. When using production data he has an alternative suggestion:

The correct solution to that error is to edit my.cnf/my.ini and set the size specified there to the size and number of log files on disk. InnoDB will then start normally.

Thanks James!


Of note: when this problem occurs, MySQL 5.0.77 erroneously returns this:

(root@some_host) [some_db]> SHOW ENGINE INNODB STATUS;
ERROR 1235 (42000): Cannot call SHOW INNODB STATUS because skip-innodb is defined


old solution for posterity:

The solution is to move the logs and allow InnoDB to recreate them. Don’t delete them – you may need them if your server has crashed or in case of data loss.

/etc/init.d/mysql stop
mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0.bak
mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1.bak
/etc/init.d/mysql start

With the files absent, InnoDB recreates them and the engine is loaded successfully.

Liked this post? Follow this blog to get more. 

  • Pingback: InnoDB disabled if ib_logfile files corrupted | MySQL | Syngu

  • Pingback: Log Buffer #258, A Carnival of the Vanities for DBAs | The Pythian Blog

  • Céd

    Corrupted ib_logfile files is a real problem with MySQL.
    I’m looking forward to see multiplexing in MySQL !

  • James Day

    The correct solution to that error is to edit my.cnf/my.ini and set the size specified there to the size and number of log files on disk. InnoDB will then start normally.

    If you rename the log files, the next error you see with be warnings about pages in the future. InnoDB uses the log position at which a page was last changed to work out whether to fix it during crash recovery. It won’t fix a page if it sees an later position in the page than the log file position. Removing the log files reset the positions of the log files but leaves all of the positions still in the pages. That causes crash recovery to not update pages it should be updating.

    If you’ve already renamed the log files and recreated them the fix to that problem is to create a work table and load data into it until the log position has advanced beyond the position in all existing pages in the database. Say insert 10,000 big rows, delete them, repeat, continue until done. This can take so long that mysqldump and reload is faster. Depends just how much writing is required.

    Removing the InnoDB log file is sometimes appropriate in cases where innodb_force_recovery has failed due to corruption in the logs but this is uncommon.

    Opinions are my own. For an official Oracle position contact a PR person.

    James Day, MySQL Senior Principal Support Engineer, Oracle

  • http://www.binarysludge.com Andy

    Thank you for taking the time to explain this so thoroughly, I’ll amend the post appropriately.

  • Ronald

    This was a great article. There’s so much misinformation regarding how to fix this on google that I’m sure a bunch of people have probably FUBAR’d their InnoDB databases as a result.