InnoDB disabled if ib_logfile files corrupted

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:

/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