InnoDB is a storage engine for MySQL. Due to its speedy crash recovery, InnoDB is the preferred engine for high available databases.
But there are times when InnoDB throws error, such as ‘Unknown table engine ‘innodb”. Today, we’ll see when and why this error happens and how to fix it.
What is ‘Unknown table engine ‘innodb” error?
The error message itself clearly says that MySQL service is unable to detect the InnoDB storage engine.
InnoDB engine error manifests itself in different situations:
1. Failure in creating InnoDB tables
Suppose you try to create a new table using the command (“CREATE TABLE t1 (a INT, b CHAR (20), PRIMARY KEY (a)) ENGINE=InnoDB;”).
The table creation fails giving the error – ERROR 1286 (42000) at line 1: Unknown table engine 'innodb'.
Table creation or listing using PhpMyAdmin may also not work. It may not even list the InnoDB storage engine option but instead throw up this error.
2. Using MySQL commands that involve InnoDB
When InnoDB engine is disabled, using certain commands would show this error.
For instance, listing the storage engines using ‘show engines’ command, backing up the databases using mysqldump, etc. are situations where you may end up seeing this error:
mysqldump: Got error: 1286: Unknown table engine 'InnoDB' when using LOCK TABLES
3. MySQL server can fail to start
MySQL may not start with InnoDB engine support. The error shown in the log file would be:
4. Error in the websites
All of a sudden, all your database-driven websites can start giving the error along with the database queries:
What causes the error ‘Unknown table engine ‘innodb”
Unknown table engine error happens either because InnoDB storage engine is not enabled in the server or because it got corrupt.
The main reasons that cause the error are:
1. InnoDB not enabled
If the InnoDB engine is disabled in the MySQL database server configuration, it will give error ‘Unknown table engine ‘innodb” whenever you try to create a table with InnoDB support.
It can happen if the MySQL server is not compiled with InnoDB support or if the InnoDB support is disabled via configuration settings.
If the MySQL server was built from source and the built-in InnoDB wasn’t compiled in it, InnoDB engine will not be recognized.
You can check if InnoDB is supported in the server, using the MySQL command:
Check if InnoDB is enabled in server
The value ‘DISABLED’ for ‘have_innodb’ parameter shows that InnoDB is not enabled in that server.
InnoDB engine can also get disabled as a result of a MySQL server upgrade, server migration or manual configuration changes.
If the parameter ‘skip-innodb’ is uncommented in /etc/my.cnf, it will skip loading the InnoDB database engine.
2. Memory shortage
Server not having enough memory to allocate to MySQL, can cause InnoDB to fail. Memory allocation happens based on the parameters such as ‘innodb_buffer_pool_size'.
If the value alloted for this pool size is greater than the available memory limit, InnoDB error occurs.
3. Huge or corrupt log files
In the long run, it so happens that the InnoDB log files grow in size of around GBs or they get corrupt due to some reason. The log files are available at ‘ib_logfile'.
These log files contain the changes to InnoDB data. If they get corrupt, InnoDB will fail to load and ends up giving the error ‘Unknown table engine ‘innodb”.
4. Incorrect permissions for /tmp or socket file
By default, the MySQL server creates its socket file in /tmp or other tmp folder as configured. This folder requires 1777 permissions, for the service to work.
If the /tmp doesn’t have enough permissions to create the socket file, it may end up showing the InnoDB error. InnoDB also fails to run if this folder is full and have no space left.
How to fix the error ‘Unknown table engine ‘innodb”
After identifying the reason for the InnoDB engine error from the error logs, we can apply any of the fixes here, as suited for the situation.
1. Enable InnoDB in the configuration file
If InnoDB support is disabled in the configuration file, here is how to fix it.
Check the ‘/etc/my.cnf’ file. It is the configuration file for MySQL server.
Find the ‘skip-innodb' parameter and comment it by adding # at the beginning of the line:
Enable InnoDB support in my.cnf
Restart the MySQL server and now InnoDB support will be enabled and you would be able to execute commands without the error.
InnoDB engine support enabled in server
2. Recreate the InnoDB log files
InnoDB usually recovers its log files when the MySQL service is started.
So, if the service does not start due to huge or corrupt log files, then rename them and restart MySQL with these steps:
a. Stop MySQL service:
b. Rename the log files to some other name
c. Start MySQL service:
Now the MySQL server would start fine. It will create the log files from the scratch and the InnoDB tables would be accessible without errors.
3. Edit the configuration settings
Edit the following parameters in ‘/etc/my.cnf’ and set optimal values based on the server settings, such as available memory and CPU.
These are the parameters that allocate memory and CPU for the InnoDB storage engine. There are no default values for these parameters, but the values can change from server to server.
Always verify the permissions of the ‘tmp’ folder and confirm that enough memory and disk space are available for the MySQL server.
At Bobcares, we also examine the server logs and monitor the MySQL performance and fine-tune the values set for each parameter.
4. Setup MySQL server with InnoDB support
All the above fixes are relevant on servers that already have InnoDB engine compiled with it. MySQL 5.5 versions and above have InnoDB engine enabled by default.
If your MySQL server doesn’t have InnoDB support compiled in, you need to recompile it with InnoDB. If you are running older versions of MySQL, it is best to upgrade your server.
To install the latest version of MySQL server in Ubuntu, use the command:
This will install the MySQL server 5.6 with InnoDB support, provided all dependencies are installed.
Points to note..
The file ‘ibdata1’ contains the data of the InnoDB databases and should never be deleted.
So, before doing any changes to MySQL server, we always make a backup of these:
a. MySQL data directory (usually at /var/lib/mysql)
b. MySQL configuration file (default at /etc/my.cnf )
Today we saw the major causes for the error ‘Unknown table engine ‘innodb” and how to fix them. In addition to these, some configuration errors can also lead to errors.
Examining the MySQL error logs, permissions, resources, etc. may be vital to resolve InnoDB related errors. In critical situations, restoring from the backups would also be required.
At Bobcares, we perform crash rescue services with minimal downtime, even for servers with fully corrupt data and no backups.
Read: Database crash rescue – How we re-built an InnoDB MySQL database when ibdata1 file was corrupted
The post How to fix ‘Unknown table engine ‘innodb” error appeared first on Bobcares.