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
Triage: Triaged: D4 (Minor)

[29 Nov 2005 18:58] Eric Goff
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 #.
[29 Nov 2005 20:36] Jorge del Conde
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>
[29 Nov 2005 20:44] Eric Goff
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) ;
[8 Mar 2007 17:21] anoop kulkarni
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
[25 Mar 2009 8:10] Mike Cook
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.
[19 Jul 2010 12:03] Roel Van de Paar
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.
[19 Jul 2010 12:04] Roel Van de Paar
mysql> alter table foo add foreign key(id) references bar(id) ;
ERROR 1005 (HY000): Can't create table 'roelt.#sql-132c_26' (errno: 150)
[19 Jul 2010 12:07] Roel Van de Paar
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)
[19 Jul 2010 13:23] Roel Van de Paar
See bug #5670, bug #21704
[19 Dec 2012 1:50] John Russell
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.
[4 Jul 2013 6:00] zhang william
may be the character set of these two tables are different
[4 Jul 2013 6:12] zhang william
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.