Unknown storage engine InnoDB when using LOCK TABLES

Have you ever noticed the error Unknown storage engine InnoDB when using LOCK TABLES when creating a MySQL backup or tables or executing commands that involve InnoDB? Sometimes this error shows while starting the MySQL server or on the website too!

root@server [~]# mysqldump grepitout_wp > grepitout_wp.sql
mysqldump: Got error: 1286: Unknown storage engine 'InnoDB' when using LOCK TABLES
root@server [~]#

Cause

There are multiple reasons for this issue. They are:

InnoDB engine may be disabled.
Corrupted InnoDB log files.
Incorrect /tmp permission

How to Fix?

You have to find the root cause of this issue to apply the fix.

FIX: If the InnoDB engine is disabled

You need to first verify the InnoDB engine is disabled on your server. You can verify this using any one of the following two methods:

Method 1

Run Command:

mysqladmin var | grep have_innodb

Sample Output:

root@server [~]# mysqladmin var | grep have_innodb
| have_innodb | NO |
root@server [~]#

Method 2:

Also, you can verify this by logging into MySQL server like below:

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

mysql>

If InnoDB engine is disabled just open the MySQL configuration file and check for the parameter skip-innodb. If it is there, just comment it and restart MySQL.

vim /etc/my.cnf

Search for skip-innodb and comment it.

#skip-innodb

Then restart MySQL service.

service mysql restart

Fix: Corrupted InnoDB log files

If you can’t find the parameter skip-innodb in MySQL configuration file (my.cnf), the issue may due to the corrupted InnoDB log files. Hence, just stop the mysql and restart it after renaming the InnoDB log files.

service mysql stop
mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0_bk
mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1_bk
service mysql start

The log files will be created as soon as you start the MySQL server.

FIX: Incorrect /tmp permission

Please make sure that the /tmp directory have 1777 permission. If the permission is correct check the disk space as the MySQL server needs enough space to create the socket file.

That’t it!

Also Read:

If you like the post Understanding top Command and wish to receive more articles from us, please like our FB page: GrepItOut

Your suggestions and feedbacks will encourage us and help to improve further, please feel free to write your comments.

For more details on our services, please drop us an E-mail at info@grepitout.com

Tagsmysql Unknown storage engine InnoDB when using LOCK TABLES

Unknown storage engine InnoDB when using LOCK TABLES