The following is a nonexhaustive list of common
InnoDB-specific errors that you may encounter,
with information about why each occurs and how to resolve the
Cannot create table. If the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed. If the error message refers to error −1, table creation probably failed because the table includes a column name that matched the name of an internal
Cannot find the
InnoDBtable from the
InnoDBdata files, although the
.frmfile for the table exists. See Section 14.18.3, “Troubleshooting InnoDB Data Dictionary Operations”.
InnoDBhas run out of free space in the tablespace. Reconfigure the tablespace to add a new data file.
Lock wait timeout expired. The statement that waited too long was rolled back (not the entire transaction). You can increase the value of the
innodb_lock_wait_timeoutconfiguration option if SQL statements should wait longer for other transactions to complete, or decrease it if too many long-running transactions are causing locking problems and reducing concurrency on a busy system.
The total number of locks exceeds the amount of memory
InnoDBdevotes to managing locks. To avoid this error, increase the value of
innodb_buffer_pool_size. Within an individual application, a workaround may be to break a large operation into smaller pieces. For example, if the error occurs for a large
INSERT, perform several smaller
The transaction encountered a deadlock and was automatically rolled back so that your application could take corrective action. To recover from this error, run all the operations in this transaction again. A deadlock occurs when requests for locks arrive in inconsistent order between transactions. The transaction that was rolled back released all its locks, and the other transaction can now get all the locks it requested. Thus when you re-run the transaction that was rolled back, it might have to wait for other transactions to complete, but typically the deadlock does not recur. If you encounter frequent deadlocks, make the sequence of locking operations (
SELECT ... FOR UPDATE, and so on) consistent between the different transactions or applications that experience the issue. See Section 188.8.131.52, “How to Cope with Deadlocks” for details.
You are trying to add a row but there is no parent row, and a foreign key constraint fails. Add the parent row first.
You are trying to delete a parent row that has children, and a foreign key constraint fails. Delete the children first.
ERROR 1553 (HY000): Cannot drop index '
fooIdx': needed in a foreign key constraint
This error message is reported when you attempt to drop the last index that can enforce a particular referential constraint.
For optimal performance with DML statements,
InnoDBrequires an index to exist on foreign key columns, so that
DELETEoperations on a parent table can easily check whether corresponding rows exist in the child table. MySQL creates or drops such indexes automatically when needed, as a side-effect of
CREATE INDEX, and
When you drop an index,
InnoDBchecks whether the index is not used for checking a foreign key constraint. It is still OK to drop the index if there is another index that can be used to enforce the same constraint.
InnoDBprevents you from dropping the last index that can enforce a particular referential constraint.