InnoDB gets disabled
Have you ever run into an issue where InnoDB gets disabled, or just doesn’t appear to exist? Your issue is likely corrupted log files. On a server I recently worked on, I saw the following output from ‘show engines’:
mysql> 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)
As you can see, InnoDB isn’t even an option! You may also get the following error:
mysql> show engine innodb status;
ERROR 1286 (42000): Unknown table engine 'innodb'Fortunately for you, the fix could be very simple. Sometimes InnoDB’s log files get corrupted. These log files track changes to InnoDB structures similar to how binlogs track changes to actual data. You can easily fix the problem like so:
/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
mysql> show engines;
+-----------------------+----------------+------------------------------------------------------------------------------------------------+--------------------+---------+-----------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+-----------------------+----------------+------------------------------------------------------------------------------------------------+--------------------+---------+-----------------+
| InnoDB | YES | Supports transactions, row-level locking, and foreign key | YES | YES | YES |
| 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)If you think you might need the InnoDB logs (there’s a good chance you don’t), then you can simply move them out of the way.
