HostOnNet Blog

InnoDB gets disabled

InnoDB is a MySQL engine which stores all of the data for it’s tables within the ibdata files located at /var/lib/mysql on the server. Unfortunately, data can be corrupted within these files and recovery methods must be taken to restore the corrupt data.

InnoDB corruption can cause all of the databases running on that server to be inaccessible.

Use the ‘show engine’ command to view the active default engine


mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | DEFAULT | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.02 sec)

As you can see, InnoDB isn’t even an option! You may also get the following error:

mysql> show engine innodb status;
ERROR 1286 (42000): Unknown table engine ‘innodb’

Stop MySQL completely and rename the ib* files in /var/lib/mysql .

# /etc/init.d/mysql stop
Shutting down MySQL.                                       [  OK  ]
mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0.bak
mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1.bak
# /etc/init.d/mysql start
Starting MySQL...............................    

Now run the show engine command again, you can see InnoDB entry.

mysql_show_engine

Finally restart MySQL restart and following the logs to be thorough, but at this point the data should be recovered and issues resolved.


About Annie

I’ve been working in Technical Section for over 10 years in a wide range of tech jobs from Tech Support to Software Testing. I started writing blog for my future reference and useful for all.

Posted in MySQL