September 2012
« Aug   Oct »

Mysql Unknown table engine ‘InnoDB’ when using LOCK TABLES

Today I got an error when I try to backup all the databases from mysql. Surprisingly this error does not contain any database name and so I could not identify which database causing this issue. So that I start backing up each database separately and identify the database causing the issue.

Then I start to googling, is one of my interesting regular jobs.. revieing many blog post.. most them saying that to “#skip-innodb” in /etc/my.cnf will solve the issue. But didn’t work for me 🙁

Later I came to know that, this mysql server does not having “INNODB” storage engine enabled.. Shocked.. that moment and did some deep drive to error logs at data directory(/var/lib/mysq/asd.err). It’s appears that mysql showing InnoDB warning when it’s started and this engine support has been turned off due to the error.

mysql> show variables like 'have_innodb';
| Variable_name | Value |
| have_innodb   | NO   |
1 row in set (0.00 sec)

Solution : just look at the mysql errors found in data directory. When I open the error file I saw that innodb log file (innodb_log_file_size) was modified recently at run-time and hence got error.

See the log below,

#vi /var/lib/mysql/

 120912 09:07:43 mysqld_safe mysqld from pid file /var/run/mysqld/ ended
120912 09:07:44 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
120912  9:07:44  InnoDB: Initializing buffer pool, size = 2.0G
120912  9:07:44  InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 20971520 bytes!
120912  9:07:44 [ERROR] Plugin 'InnoDB' init function returned error.
120912  9:07:44 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
120912  9:07:44 [ERROR] Unknown/unsupported table type: Innodb
120912  9:07:44 [ERROR] Aborting

Fix : delete or move the “ib_logfile0 and ib_logfile1” and restart the mysql service.

#rm -rf  /var/lib/mysql/ib_logfile*

5 comments to Mysql Unknown table engine ‘InnoDB’ when using LOCK TABLES

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>