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 on this post.
  1. grange:

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

  2. yves:

    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:

    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. alphabytes (Nitesh Chauhan):

    @rhyscampbell u saved my life again… with this. http://www.youdidwhatwithtsql.com/unknown-table-engine-innodb/760
    thanx

  5. Batou:

    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

  6. CobaltBlueDW:

    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!

  7. CobaltBlueDW:

    *mysql_upgrade –password

  8. Unnikrishnan B.:

    Thanks very much. It solved my problem.

    rm /var/lib/mysql/ib_log*

    Thanks,
    Unnikrishnan B.

  9. kg:

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

  10. kevin:

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

  11. Rhys:

    Hi Kevin,

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

    RHys

  12. Earl:

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

    simply deleting the log files solved my issue.

  13. miklr:

    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.

  14. Rob Maurer:

    Thanks! This solved my problem!

Leave a comment





Current day month ye@r *