Skip navigation links
13.2.12.2 Operating System Error Codes »
Section Navigation      [Toggle]

13.2.12.1. InnoDB Error Codes

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 problem.

  • 1005 (ER_CANT_CREATE_TABLE)

    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 InnoDB table.

  • 1016 (ER_CANT_OPEN_FILE)

    Cannot find the InnoDB table from the InnoDB data files, although the .frm file for the table exists. See Section 13.2.13.4, “Troubleshooting InnoDB Data Dictionary Operations”.

  • 1114 (ER_RECORD_FILE_FULL)

    InnoDB has run out of free space in the tablespace. You should reconfigure the tablespace to add a new data file.

  • 1205 (ER_LOCK_WAIT_TIMEOUT)

    Lock wait timeout expired. Transaction was rolled back.

  • 1213 (ER_LOCK_DEADLOCK)

    Transaction deadlock. You should rerun the transaction.

  • 1216 (ER_NO_REFERENCED_ROW)

    You are trying to add a row but there is no parent row, and a foreign key constraint fails. You should add the parent row first.

  • 1217 (ER_ROW_IS_REFERENCED)

    You are trying to delete a parent row that has children, and a foreign key constraint fails. You should delete the children first.


User Comments

Posted by Vijay Kaushik on December 4 2006 5:45pm[Delete] [Edit]

ERROR 1005 (HY000): Can't create table './<db_name>/#sql-32be_1b99b.frm' (errno: 150)

This has always come and bit me more than once - mostly because of my own stupidity. The error message is not all that helpful in guiding you to fix the problem. Hence I thought I would document it.

You will encounter this problem typically when the tables have been created with different Engines e.g one is of the type INNODB and the other is of the type ISAM.

Posted by Marc Champlain on February 25 2007 5:03pm[Delete] [Edit]

ERROR 1005 (HY000): Can't create table './<db_name>/#sql-32be_1b99b.frm' (errno: 150)

Other reason for that error is trying to set a forein key between two fields that are not exactly the same. The fields type, dimension and flags should be identical.

Posted by Mark Robbins on September 29 2007 10:19pm[Delete] [Edit]

ERROR 1005 : Can't create table './<db_name>/#sql-<text>.frm' (errno: 121)

I found that this when I used the create script like option in Toad (basically use the same SQL).
I had forgotten to rename a constraint


Posted by Jim Grill on February 29 2008 7:41pm[Delete] [Edit]

ERROR 1005 (HY000): Can't create table './MyDB/#sql-e4a_c715.frm' (errno: 121)

As mentioned above, you will get this message if you're trying to add a constraint with a name that's already used somewhere else.

There is really not much wrong with allowing innodb to choose the name for you. Simply omit the CONSTRAINT keyword when creating foreign keys.

likewise...

ERROR 1025 (HY000): Error on rename of './MyDB/MyTable' to './MyDB/#sql2-e4a-ca3e' (errno: 152)

To avoid getting this error while trying to drop a foreign key, use the constraint name rather than the column name of the foreign key.

Summary:

99.999% of the time errors like this have nothing to do with the ability to create a temporary file and much to do with foreign key issues.

Posted by Alesandro Guiterrez on June 21 2008 11:36pm[Delete] [Edit]

Regarding 1005:

Usually caused by an error related to a foreign key. To see a complete error description type:

Show innoDB status;

Read under the section: Latest Foreign Key Error, and there you go! Your problem explained! Just read it two or three times if you don’t get it. Trust me it helps a lot.

Posted by Burt Culver on August 18 2008 3:23am[Delete] [Edit]

I was blocked creating a InnoDB table in a import of a mysqldump and found a way to resolve the 1005 error with errno: -1 - delete the tablename.ibd file. Then the table create works fine.

Posted by Alex Blume on November 7 2008 5:09pm[Delete] [Edit]

"ERROR 1025 (HY000): Error on rename of './MyDB/MyTable' to './MyDB/#sql2-e4a-ca3e' (errno: 152)

To avoid getting this error while trying to drop a foreign key, use the constraint name rather than the column name of the foreign key."

I was getting this errno 152 when I was specifying the constraint name in my ALTER TABLE DROP FOREIGN KEY '<keyname>' statement, but I had misspelled the constraint name! (Note: They are case sensitive!)

Posted by Aaron Baxter on October 4 2009 6:17pm[Delete] [Edit]

From Alex's Post:
To avoid getting this error while trying to drop a foreign key, use the constraint name rather than the column name of the foreign key."

I was getting this errno 152 when I was specifying the constraint name in my ALTER TABLE DROP FOREIGN KEY '<keyname>' statement, but I had misspelled the constraint name! (Note: They are case sensitive!)

---END---

If you didn't specify your own keyname when you created the foreign key, the quickest way to find it is to issue the statment: "SHOW CREATE TABLE XXX". This will list all of the foreign key constraints and their name which can be used to drop the foreign key.

To specify your constraint name, use the following syntax:
CONSTRAINT name FOREIGN KEY (Column) REFERENCES parent(column)
where name is the name you want to set the foreign key constraint to. Be careful, they are case sensitive and you can't duplicate the name.

Add your own comment.