Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 33.9Mb
PDF (A4) - 34.0Mb
PDF (RPM) - 33.2Mb
EPUB - 8.5Mb
HTML Download (TGZ) - 8.2Mb
HTML Download (Zip) - 8.2Mb
HTML Download (RPM) - 7.1Mb
Eclipse Doc Plugin (TGZ) - 9.0Mb
Eclipse Doc Plugin (Zip) - 11.1Mb
Man Pages (TGZ) - 219.4Kb
Man Pages (Zip) - 322.3Kb
Info (Gzip) - 3.2Mb
Info (Zip) - 3.2Mb
Excerpts from this Manual

14.18.5 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 14.18.3, “Troubleshooting InnoDB Data Dictionary Operations”.

  • 1114 (ER_RECORD_FILE_FULL)

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

  • 1205 (ER_LOCK_WAIT_TIMEOUT)

    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_timeout 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 reducing concurrency on a busy system.

  • 1206 (ER_LOCK_TABLE_FULL)

    The total number of locks exceeds the amount of memory InnoDB devotes 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 INSERT operations.

  • 1213 (ER_LOCK_DEADLOCK)

    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 (LOCK TABLES, SELECT ... FOR UPDATE, and so on) consistent between the different transactions or applications that experience the issue. See Section 14.2.2.10, “How to Cope with Deadlocks” for details.

  • 1216 (ER_NO_REFERENCED_ROW)

    You are trying to add a row but there is no parent row, and a foreign key constraint fails. 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. 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, InnoDB requires an index to exist on foreign key columns, so that UPDATE and 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 side-effect of CREATE TABLE, CREATE INDEX, and ALTER TABLE statements.

    When you drop an index, InnoDB checks 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.


User Comments
  Posted by Vijay Kaushik on December 4, 2006
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
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
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
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
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
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
"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
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.
  Posted by Hector E. Delgadillo on January 21, 2011
Hello, I got this message when I tried to drop a Primary Key:

mysql> ALTER TABLE mytable DROP PRIMARY KEY;
ERROR 1025 (HY000): Error on rename of '.\database\#sql-454_3' to '.\database\mytable' (errno: 150).

I solved it using:
mysql> ALTER TABLE mytable DROP PRIMARY KEY, ADD PRIMARY KEY (column1,column2,column3);

I hope help you.
  Posted by Feargal Reilly on September 23, 2011
Marc Champlain's note solved my problem:

I'd gotten used to creating auto-incrementing indices using the SERIAL keyword.

CREATE TABLE parent (
id SERIAL PRIMARY KEY NOT NULL
) ENGINE=InnoDB;

In PostgreSQL id will be created as type integer so I was trying this:

CREATE TABLE child (
id SERIAL PRIMARY KEY NOT NULL,
parent_id integer,
FOREIGN KEY (parent_id) REFERENCES parent(id)
) ENGINE=InnoDB;

This failed as parent.id was in fact created as type bigint(20) unsigned.

CREATE TABLE child (
id SERIAL PRIMARY KEY NOT NULL,
parent_id bigint(20) UNSIGNED,
FOREIGN KEY (parent_id) REFERENCES parent(id)
) ENGINE=InnoDB;

  Posted by Jim Leek on October 13, 2011
I ran across the following error code when restoring all databases from a mysqldump file:

ERROR 1005 (HY000) at line 156: Can't create table 'db1.testtable' (errno: 121)

Of course as my SQL had been created from the mysqldump utility, I was confused how an error had appeared in the SQL code!

It turned out it was the presence of existing "ibdata1", "ib_logfile0" and "ib_logfile1" in /var/lib/mysql/ that was causing the problem.

The solution for me was to stop mysql, delete EVERYTHING in the /var/lib/mysql/ folder, then start mysql (this recreates fresh versions of the above files in addition to a fresh "mysql" database), and then re-run my import of the dump.sql file:

shell> mysql < dump.sql

(No username or password needed as the fresh "mysql" database has no root password.)

This worked perfectly and recreated all databases (including re-populating the "mysql" database with my previous data - including my old root password).

  Posted by Scott O'Connell on October 30, 2011
When exporting from one MySQL system to another (distb 5.1.44 to 5.1.58) the generated SQL gave this error:
ERROR 1005 (HY000): Can't create table ... (errno: 150)

The problem was that the table names are case sensitive in the statement, but for some reason the generated file from phyMyAdmin made them all lower case.

Changing:

ALTER TABLE `jobPostingJobCategories`
ADD CONSTRAINT `jobpostingjobcategories_ibfk_1` FOREIGN KEY (`jobID`) REFERENCES `jobpostings` (`jobID`) ON DELETE CASCADE,
ADD CONSTRAINT `jobpostingjobcategories_ibfk_2` FOREIGN KEY (`categoryID`) REFERENCES `jobcategories` (`categoryID`) ON DELETE CASCADE;

to:

ALTER TABLE `jobPostingJobCategories`
ADD CONSTRAINT `jobpostingjobcategories_ibfk_1` FOREIGN KEY (`jobID`) REFERENCES `jobPostings` (`jobID`) ON DELETE CASCADE,
ADD CONSTRAINT `jobpostingjobcategories_ibfk_2` FOREIGN KEY (`categoryID`) REFERENCES `jobCategories` (`categoryID`) ON DELETE CASCADE;

fixed the problem
  Posted by Ben Parish on January 6, 2012
SOLVED: I struggled for hours trying to add a simple innodb table with a particular name without any foreign keys. Thanks to Burt Culver's earler comment regarding removing .idb files, I realised that my InnoDB data directory file must be corrupted by having a reference to the table even though it no longer existed so I stopped MySQL and removed

mysql\data\ibdata1

I then restarted which bebuilt the data directory file and I was able to add the table with no problems.
  Posted by Philip Flammer on June 9, 2012
For cryptic foreign key errors such as errno 150 and errno 121, you can take a look at this resource that explains many of the causes:
http://eliacom.com/wpErrNo150.php
  Posted by Ramone Burrell on August 9, 2012
I was getting the error number 121 while trying to crate a table and the solution was to simply drop and recreate the database. The actual cause of the problem I am still not sure of. Thanks for the comments above, they helped.
  Posted by john barri on October 4, 2012
I tried to replace a database by deleting the folder and re-creating it. When I tried to rewrite the tables into the new db folder the system errored "ERROR 1005 (HY000) at line 156: Can't create table 'db1.testtable' (errno: 121)", or something similar (don't know about the line number).

I restored the original DB and content and then used DROP SCHEMA to remove the DB. I re-created the DB and the the tables in the db without further problems.
  Posted by David Khasikyan on November 26, 2012
These errors may be cause of the constraint names of foreign keys.The constraint name of parent table should not be the same as the constraint name of child table.Thanks.
  Posted by Fernando Cypriano Almeida fernandoacr on February 3, 2013
Eu estava recebendo este erro: MySql Error 1005: Can't create table (errno: 150), por causa de um descuido meu.

CREATE TABLE IF NOT EXISTS `cursos_crs` (
`crs_id` INT NOT NULL AUTO_INCREMENT ,
`crs_nome` VARCHAR(45) NULL ,
`crs_instituicao` VARCHAR(45) NULL ,
`crs_carga_horaria` VARCHAR(45) NULL ,
`crs_data_inicio` DATE NULL ,
`crs_data_fim` DATE NULL ,
`pro_id` INT NOT NULL ,
`stc_id` INT NOT NULL ,
PRIMARY KEY (`crs_id`) ,
CONSTRAINT `fk_cursos_crs_profissional_pro1`
FOREIGN KEY (`pro_id` )
REFERENCES `profissional_pro` (`pro_id` )
ON DELETE CASCADE
ON UPDATE NO ACTION,
CONSTRAINT `fk_cursos_crs_situacao_std1`
FOREIGN KEY (`stc_id` )
REFERENCES `situacao_stc` (`stc_id` )
ON DELETE SET NULL
ON UPDATE NO ACTION)
ENGINE = InnoDB;

A FK on delete set null no atributo stc_id, porém este atributo estava not null.

Fiz esta correção na tabela `stc_id` INT NULL e o problema foi solucionado.
Sign Up Login You must be logged in to post a comment.