Bug #62521 Attempts at Creating Foreign Keys Causes ERROR 1005 (HY000): Can't create table

• Submitted: 23 Sep 2011 19:02
• Modified: 24 Oct 2011 5:04
• Reporter: Carl KEMP
• Status: No Feedback
• Category: MySQL Server: DDL
• Severity: S2 (Serious)
• Version: 5.5
• OS: Windows (Windows 7 x64)
• CPU Architecture: Any
• Tags: errno: 150, ERROR 1005, foreign key

[23 Sep 2011 19:02] Carl KEMP

Description:
I am having trouble creating foreign keys on multiple tables in my database. The create fails while trying to create a temporary table with an invalid name. The error message is:

ERROR 1005 (HY000): Can't create table 'openjcsdb.#sql-5c4_a' (errno: 150)

I have tried dropping and rebuilding the tables, checking for orphaned indexes, all to no avail. See Attachment for examples.

Regards,
Carl Kemp

How to repeat:
This has happened in several tables in my database, as shown:

mysql> alter table propertyperms add foreign key (InstanceName,ObjectName,PropertyName,Scope) references instanceproperties (InstanceName,ObjectName,PropertyName,Scope);
ERROR 1005 (HY000): Can't create table 'openjcsdb.#sql-5c4_a' (errno: 150)
mysql>
mysql> alter table virtualworkload add foreign key (HostID,MemberType,Scope,VirtualHost) references virtualmembers (HostID,MemberType,Scope,VirtualHost);
ERROR 1005 (HY000): Can't create table 'openjcsdb.#sql-5c4_a' (errno: 150)
mysql>
mysql> alter table vmemberlimits add foreign key (HostID,MemberType,Scope,VirtualHost) references virtualmembers (HostID,MemberType,Scope,VirtualHost);
ERROR 1005 (HY000): Can't create table 'openjcsdb.#sql-5c4_a' (errno: 150)
mysql>
mysql> alter table limitquotas add foreign key (JobQueue,LimitNum,Scope) references jobqueuelimits (JobQueue,LimitNum,Scope);
ERROR 1005 (HY000): Can't create table 'openjcsdb.#sql-5c4_a' (errno: 150)
mysql>
mysql> alter table vmemberlimits add foreign key (idx,Scope,VirtualHost) references virtuallimits (idx,Scope,VirtualHost);
ERROR 1005 (HY000): Can't create table 'openjcsdb.#sql-5c4_a' (errno: 150)
mysql>
mysql> alter table jobhistory add foreign key jobhistory_osgroups_fk2 (GID,HostID) references osgroups (GID,HostID);
ERROR 1005 (HY000): Can't create table 'openjcsdb.#sql-5c4_a' (errno: 150)
mysql>
mysql> alter table jobs add foreign key (GID,HostID) references osgroups (GID,HostID);
ERROR 1005 (HY000): Can't create table 'openjcsdb.#sql-5c4_a' (errno: 150)
mysql>
mysql> alter table logins add foreign key (GID,HostID) references osgroups (GID,HostID);
ERROR 1005 (HY000): Can't create table 'openjcsdb.#sql-5c4_a' (errno: 150)
mysql>
mysql> alter table hostusers add foreign key (HostID,OSUserID) references osusers (HostID,OSUserID);
ERROR 1005 (HY000): Can't create table 'openjcsdb.#sql-5c4_a' (errno: 150)
mysql>
mysql> alter table jobhistory add foreign key (HostID,OSUserID) references osusers (HostID,OSUserID);
ERROR 1005 (HY000): Can't create table 'openjcsdb.#sql-5c4_a' (errno: 150)
mysql>
mysql> alter table jobs add foreign key (HostID,OSUserID) references osusers (HostID,OSUserID);
ERROR 1005 (HY000): Can't create table 'openjcsdb.#sql-5c4_a' (errno: 150)
mysql>
mysql> alter table logins add foreign key (HostID,OSUserID) references osusers (HostID,OSUserID);
ERROR 1005 (HY000): Can't create table 'openjcsdb.#sql-5c4_a' (errno: 150)
mysql>
mysql> alter table osuserattributes add foreign key (AttributeName,UserID) references userattributes (AttributeName,UserID);
ERROR 1005 (HY000): Can't create table 'openjcsdb.#sql-5c4_a' (errno: 150)
mysql>
mysql> alter table groupattributes add foreign key (HostID) references hosts (HostID);
ERROR 1005 (HY000): Can't create table 'openjcsdb.#sql-5c4_a' (errno: 150)
mysql>
mysql> alter table osgroups add foreign key (HostID) references hosts (HostID);
ERROR 1005 (HY000): Can't create table 'openjcsdb.#sql-5c4_a' (errno: 150)
mysql>
mysql> alter table osusers add foreign key (HostID) references hosts (HostID);
ERROR 1005 (HY000): Can't create table 'openjcsdb.#sql-5c4_a' (errno: 150)
mysql>
mysql> alter table osuserattributes add foreign key (UserID) references users (UserID);
ERROR 1005 (HY000): Can't create table 'openjcsdb.#sql-5c4_a' (errno: 150)
mysql>

[23 Sep 2011 19:03] Carl KEMP

Foreign Key Failures

Attachment: FK Failure.txt (text/plain), 19.46 KiB.

[24 Sep 2011 5:05] Valeriy Kravchuk

I am not sure about all the cases, but at least in one the problem is obvious. You try to add foreign key like this:

mysql> alter table propertyperms add foreign key (InstanceName,ObjectName,PropertyName,Scope) references instanceproperties (InstanceName,ObjectName,PropertyName,Scope);
ERROR 1005 (HY000): Can't create table 'openjcsdb.#sql-5c4_5' (errno: 150)

while table you refer to is defined as follows:

| instanceproperties | CREATE TABLE `instanceproperties` (
  `ObjectName` varchar(40) NOT NULL DEFAULT '',
  `InstanceName` varchar(40) NOT NULL DEFAULT '',
  `Scope` varchar(80) NOT NULL DEFAULT '',
  `PropertyName` varchar(40) NOT NULL DEFAULT '',
  `ValueType` varchar(255) DEFAULT NULL,
  `Value` varchar(255) DEFAULT NULL,
  `Locked` tinyint(1) DEFAULT NULL,
  `LockingPID` int(11) DEFAULT NULL,
  `Permissions` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`ObjectName`,`InstanceName`,`Scope`,`PropertyName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

Note different order of columns in the primary key!

Please, make sure both tables are InnoDB, data types in key columns are the same and foreign key refers to columns of other table's primary key exactly in the same order they are listed in the primary key.

[24 Oct 2011 23:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".