Unknown table engine ‘InnoDB’‏

I ran across this error today whilst upgrading to an instance of MySQL 5.4.

Unknown table engine 'InnoDB'‏

I executed the following command at the MySQL client to see the available storage engines.

SHOW ENGINES;

This only listed the following table types;

MRG_MYISAM
MyISAM
BLACKHOLE
CSV
MEMORY
FEDERATED
ARCHIVE

Sure enough, No Innodb engine was present. I googled for “Unknown table engine Innodb” and came across the following thread. While this made sense it had no effect for me as skip-innodb wasn’t present in the my.cnf file. I decided to take a peek into the MySQL error log;

InnoDB: Error: log file /var/lib/mysql/ib_logfile2 is of different size 0 0 bytes
InnoDB: than specified in the .cnf file 0 5242880 bytes!
100511 18:28:23 [ERROR] Plugin 'InnoDB' init function returned error.
100511 18:28:23 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed

I checked the file mentioned, ib_logfile2, and it was precisely the size the config file said it should be. Since this was a new installation, and no risk of data loss, I deleted the file and attempted to restart MySQL. Again it failed. A bit of further googling uncovered this StackOverflow thread.

InnoDB is insanely picky about it's config; if something's not right,
it'll just give up and go home. To get around this problem, just stop MySQL,
delete the old logfile, and start MySQL again. The log file will be
created with the correct size, and all will be well.

I’d already tried this but the original poster mentioned they deleted all log files in the /var/lib/mysql directory and then InnoDB decided it was happy to start up. This isn’t something I’d be keen to use on a production system but I gave it go and MySQL started running.

100511 18:30:23  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Last MySQL binlog file position 0 3350536, file name ./mysql-bin.000034
100511 18:30:23 InnoDB Plugin 1.0.4 started; log sequence number 52761612
100511 18:30:23 [Note] Event Scheduler: Loaded 0 events
100511 18:30:23 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.4.2-beta-community-log'  socket: '/var/run/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)

14 Comments

  1. grange says:

    Thank you for this article! It was the solution for my problem :)

  2. yves says:

    Thanks. In case it is still not clear, delete the files

    ib_logfile*

    usually in /var/lib/mysql (Ubuntu)

    Agreed that Innodb is “insanely picky” about those problems.

  3. shiny666 says:

    wonderful news. thanks for the pointer, as you may have saved a small fraction of what sanity i may retain in a few weeks or so.

  4. Batou says:

    Oh god THANKS!!
    I was about to commit suicide.
    BTW, its important to delete BOTH files at the same time y you delete one, restart, delete the other and restart again, it does not work.
    Just do a rm ib_logfile*

    Bye

  5. CobaltBlueDW says:

    I had this problem as a result of a MySQL upgrade/reinstall, so to fix my problem I first had to run:
    mysql_update –password

    THEN deleting those files in my DB directory fixed the problem.

    Thanks!

  6. CobaltBlueDW says:

    *mysql_upgrade –password

  7. Thanks very much. It solved my problem.

    rm /var/lib/mysql/ib_log*

    Thanks,
    Unnikrishnan B.

  8. kg says:

    Thank you, worked for me on a WP site – Phew!!

  9. kevin says:

    None of these things work for me. mysql_upgrade -password does complain about corrupt InnoDB databases.

  10. Rhys says:

    Hi Kevin,

    Difficult to say without more info. What happened to cause this issue?

    RHys

  11. Earl says:

    thank you! ! thank you! ! thank you! !

    simply deleting the log files solved my issue.

  12. miklr says:

    Thanks so much, you also saved me some headaches, as confirmed deleting the log files (ib_logfile0 and ib_logfile1 – in my case) worked like a charm.

  13. Rob Maurer says:

    Thanks! This solved my problem!

Leave a Reply

Current day month ye@r *