Cry MySQL How to...

Troubleshooting: Default storage engine (InnoDB) is not available

---

Symptoms

MySQL is not running and will not start. In the Windows Application event log the following error is logged:

Source:	MySQL
EventID:	100
Level:	Error

Default storage engine (InnoDB) is not available.

Identify the underlying cause

In the first instance location the file "computer.err", where "computer" is the name of the computer on which MySQL is (or should be) running. This is the error log file appended to by MySQL which it uses to record any start up errors.

On a Windows PC this is typically located in C:\Program Files\MySQL MySQL Server 5.0\data (you will need to replace 5.0 with your MySQL version number).

Open "computer.err" with notepad, scroll down to the bottom of this file and look at the line (or lines) immediately preceding the one which states "[ERROR] Default storage engine (InnoDB) is not available. That will indicate the cause of the error and what you need to address.

Possible Causes and Resolution

There are a number of possible causes to this problem (but I have only recorded here the ones that I have encountered).

• InnoDB: Error: unable to create temporary file; errno: 9
[ERROR] Default storage engine (InnoDB) is not available
[ERROR] Aborting

  MySQL was unable to create the temporary files it uses. Check that the temporary directory MySQL uses still exists (I had accidentally deleted mine whilst housekeeping). You can identify the directory MySQL is trying to use either by opening my.ini or by using the GUI tools to configure the instance (you can configure the instance without connecting to MySQL). Check that you are not out of storage space and that no permissions have been placed on the folder that would prevent files from being created in it.

• InnoDB: No valid checkpoint found.
InnoDB: If this error appears when you are creating an InnoDB database,
InnoDB: the problem may be that during an earlier attempt you managed
InnoDB: to create the InnoDB data files, but log file creation failed.
InnoDB: If that is the case, please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/error-creating-innodb.html
[ERROR] Default storage engine (InnoDB) is not available
[ERROR] Aborting

  The log files and the InnoDB data file (ibdata1) must be in step with each other.

  In my case I had tried to wipe the database by deleting the ibdata1 file knowing that it would be recreated when MySQL next started, but I had neglected to delete the log files. (Note: Unless you have good reason to want to wipe your MySQL database deleting either the ibdata1 or the log files is a bad idea.)

---

These notes have been tested against MySQL 5.0