Bug #55361 ERROR 1005 (HY000): Can't create table 'db1.#sql-132c_26' (errno: 150)

• Submitted: 19 Jul 2010 11:54
• Modified: 19 Jul 2010 12:01
• Reporter: Roel Van de Paar
• Status: Duplicate
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.1.47
• OS: Any
• CPU Architecture: Any

[19 Jul 2010 11:54] Roel Van de Paar

Description:
mysql> ALTER TABLE `db1`.`t1` ADD CONSTRAINT `i5` FOREIGN KEY (`i3`) REFERENCES `db2`.`t2` (`i4`) ON DELETE CASCADE;
ERROR 1005 (HY000): Can't create table 'db1.#sql-132c_26' (errno: 150)

------------------------
LATEST FOREIGN KEY ERROR
------------------------
100719 21:52:26 Error in foreign key constraint of table db1/#sql-132c_26:
 FOREIGN KEY (`i3`) REFERENCES `db2`.`t2` (`i4`) ON DELETE CASCADE:
Cannot resolve table name close to:
 (`i4`) ON DELETE CASCADE

MySQL error code 150: Foreign key constraint is incorrectly formed

How to repeat:
DROP DATABASE IF EXISTS db1;
DROP DATABASE IF EXISTS db2;
CREATE DATABASE db1;
CREATE DATABASE db2;
CREATE TABLE `db1`.`t1` (`i3` int(11) NOT NULL,PRIMARY KEY (`i3`)) ENGINE=InnoDB;
CREATE TABLE `db2`.`t2` (`i4` int(11) NOT NULL,PRIMARY KEY (`i4`)) ENGINE=MyISAM;
ALTER TABLE `db1`.`t1` ADD CONSTRAINT `i5` FOREIGN KEY (`i3`) REFERENCES `db2`.`t2` (`i4`) ON DELETE CASCADE;

[19 Jul 2010 12:01] Roel Van de Paar

Marking as a duplicate of bug #15324