MySQL Bugs: #15324: ERROR 1005 (HY000): Can't create table ... (errno: 150)

Bug #15324	ERROR 1005 (HY000): Can't create table ... (errno: 150)
Submitted:	29 Nov 2005 18:58	Modified:	19 Dec 2012 1:50
Reporter:	Eric Goff	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.0, 5.1, 5.1.47	OS:	Linux (Linux)
Assigned to:	Kevin Lewis	CPU Architecture:	Any

Description:
If I try to alter a table to add a foreign key on an INNODB table, I get  an error.
ERROR 1005 (HY000): Can't create table './mndb/#sql-992_406.frm' (errno: 150)

How to repeat:
create table foo(id integer) ;
create table bar(id integer) ;
alter table foo add foreign key(id) references bar(id) ;

returns:
ERROR 1005 (HY000): Can't create table './mndb/#sql-992_406.frm' (errno: 150)

It appears that MySQL is generating a temporary table with '#' in it.
I see that in general, there is a bug that innodb can not deal with # in table names.
I would argue that that is a different bug since it was talking about tables
that the user generates.

I have no control over these temporary tables, and therefore no work-around at all.

Suggested fix:
Make it work.  :)
Or at least change the temporary table to not use #.

Hi!

I was unable to reproduce this problem under FC4 / 5.0.16:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.16

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table foo(id integer) ;
create table bar(id integer) ;
Query OK, 0 rows affected (0.02 sec)

mysql> create table bar(id integer) ;
alter table foo add foreign key(id) references bar(id) ;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table foo add foreign key(id) references bar(id) ;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

Are you sure you used INNODB engine ?
I should have been more explicit with my SQL, please try:

create table foo(id integer) engine INNODB ;
create table bar(id integer) engine  INNODB ;
alter table foo add foreign key(id) references bar(id) ;

Is this still an issue? We keep coming across this bug off and on.Eric's last example doesnt work on our mysql db.

Engine: INNODB
version: 5.0.33

We have found a definite cause of this problem (even if it isn't the only one.)

If you try and create a foreign key constraint with the same name as an existing one it will fail with a 'constraint already exists' error.

After this you can no longer create constraints to the same parent table and receive the errno:150 error.

If you then drop the parent table and recreate it, mySQL will then allow you to add the FK constaints successfully.

This appears to be a reference left in the mySQL reference data which should be cleaned up when the constraint creation fails.

drop table if exists foo;
drop table if exists bar;
create table foo(id integer) engine=innodb;
create table bar(id integer) engine=myisam;
alter table foo add foreign key(id) references bar(id);

Marked bug #55361 as a duplicate of this one, see that bug for more info.

mysql> alter table foo add foreign key(id) references bar(id) ;
ERROR 1005 (HY000): Can't create table 'roelt.#sql-132c_26' (errno: 150)

5.0.22 gives different message:

mysql> alter table foo add foreign key(id) references bar(id);
ERROR 1005 (HY000): Can't create table './roelt/#sql-421d_1.frm' (errno: 150)

See bug #5670, bug #21704

Added to changelog for 5.6.9: 

This fix improves the error message when a foreign key constraint
cannot be created. Instead of referring to an inability to create a
table with an auto-generated name, the message clearly states the
error:

ERROR 1215 (HY000): Cannot add foreign key constraint

Issuing a subsequent SHOW WARNINGS statement provides additional detail about
any secondary indexes that are required.

may be the character set of these two tables are different

You can use:
show create table foo\G
show create table bar\G
to see the character set of each field;
If you want to add a foreign key between two table, the character set of each field must be same.