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
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
InnoDB table from the
files, although the
.frm file for
the table exists. See
Section 14.19.3, “Troubleshooting InnoDB Data Dictionary Operations”.
InnoDB has 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
You can increase the value of the
configuration 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
on a busy system.
The total number of locks exceeds the amount of memory
InnoDB devotes to managing locks. To avoid
this error, increase the value of
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
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 14.2.12, “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
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,
InnoDB requires an index to exist on
foreign key columns,
DELETE operations 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
CREATE INDEX, and
ALTER TABLE statements.
When you drop an index,
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.
InnoDB prevents you from dropping the last
index that can enforce a particular referential constraint.