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 [~]#
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:
mysqladmin var | grep have_innodb
root@server [~]# mysqladmin var | grep have_innodb | have_innodb | NO | root@server [~]#
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.
Search for skip-innodb and comment it.
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.
- How to Change MySQL Data Directory in Linux
- How To upgrade MySQL in InterWorx Server
- How to disable MySQL strict mode on cPanel
- Recover mysql root password
- How to backup and restore Plesk Database
- How to Backup and Restore Database in Linux
- How to backup and restore MongoDB database
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 email@example.com