1286 - Unknown storage engine 'InnoDB'

Question

I am trying to use roundcube and it recently just broke. I don't know if this is due to a MySQL update that happened recently or not but in phpMyAdmin I get the following error if I try and view a table:

1286 - Unknown storage engine 'InnoDB'

and

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MyISAM             | DEFAULT | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)

and

[mysqld]
default-storage-engine=MyISAM
local-infile=0
symbolic-links=0
skip-networking
max_connections = 500
max_user_connections = 20
key_buffer = 512M
myisam_sort_buffer_size = 64M
join_buffer_size = 64M
read_buffer_size = 12M
sort_buffer_size = 12M
read_rnd_buffer_size = 12M
table_cache = 2048
thread_cache_size = 16K
wait_timeout = 30
connect_timeout = 15
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 64M
query_cache_type = 1
low_priority_updates=1
concurrent_insert=ALWAYS
log-error=/var/log/mysql/error.log
tmpdir=/home/mysqltmp
myisam_repair_threads=4
[mysqld_safe]
open_files_limit = 8192
log-error=/var/log/mysql/error.log

[mysqldump]
quick
max_allowed_packet = 512M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

Ideas as to how to fix? It used to work just fine.

Solution 1

Looks like one or more InnoDB log files got corrupted.

In that case MySQL doesn't load the engine even if you don't specify skip-innodb in your my.cnf file.

A solution is to stop mysqld and delete those log files BUT be careful as you can loose your data:

Even if InnoDB were used, you could delete the ib_arch_log* files. InnoDB redo log archiving was disabled in MySQL 4.1, I think. The ib_logfile* files are needed for InnoDB crash recovery. If you shut down InnoDB cleanly, you can remove them and change their size in the configuration file. Of course, you should be careful when doing such changes. It is best to take backups first.

So the procedure should be something like:

/etc/init.d/mysql stop

mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0.bak # these are your
mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1.bak # log files

/etc/init.d/mysql start

Please, move those logs to backup, don't delete them ;)

Take a look at this answer on dba.stackexchange.com for some useful insights, too.

Solution 2

Add these lines to my.cnf

default-storage-engine=innodb
default-table-type=innodb

And then restart MySQL:

service mysql restart