MySQL Bugs: #70171: ALTER TABLE add foreign key causes failure to rename temporary table on 5.6.12+

Bug #70171	ALTER TABLE add foreign key causes failure to rename temporary table on 5.6.12+
Submitted:	28 Aug 2013 3:59	Modified:	12 Nov 2013 8:43
Reporter:	Andrew Mann	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.5.32,5.5.33, 5.6.12, 5.6.13	OS:	Linux (Ubuntu 12.04 LTS)
Assigned to:		CPU Architecture:	Any
Tags:	ALTER TABLE, foreign key, innodb, regression, renaming table

Description:
ALTER TABLE ADD CONSTRAINT FOREIGN KEY fails in some situations with "Error 1050 (42S01) : Table '' already exists". Innodb engine status shows "InnoDB: Renaming table `bug`.`#sql-52d4_2` to `bug`.`OrganizationBucketResources` failed!"

This does not seem to happen on 5.6.11, but happens on 5.6.12 and 5.6.13.  I'd imagine there are more criteria to cause this to happen or it would be widely reported (repro included below)

How to repeat:
1) Use MySQL community server 5.6.12 or 5.6.13 on Linux.  (Using Ubuntu 12.04LTS with the Debian package in this case)
2) Issue the following statements to create test schema:
CREATE DATABASE bug;

CREATE TABLE `intCloudServiceOrganization` ( 
  `organization_service_id` int(4) NOT NULL AUTO_INCREMENT, 
  `org_service_name` varchar(64) DEFAULT NULL, 
  `organization_account_number` varchar(16) DEFAULT NULL, 
  `organization_service_key` varchar(32) DEFAULT NULL, 
  `organization_id` int(4) DEFAULT NULL, 
  `service_id` int(2) DEFAULT NULL, 
  `organization_secret` varchar(64) DEFAULT NULL, 
  `organization_service_enckeys` varchar(255) DEFAULT NULL, 
  `auth_fail_count` int(4) DEFAULT '0', 
  `inactive` tinyint(1) DEFAULT '0', 
  PRIMARY KEY (`organization_service_id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `OrganizationBuckets` ( 
  `bucket_id` int(6) NOT NULL AUTO_INCREMENT, 
  `bucket_name` varchar(32) NOT NULL, 
  `organization_id` int(4) NOT NULL,
  PRIMARY KEY (`bucket_id`),
  UNIQUE KEY `bucket_name` (`bucket_name`,`organization_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `OrganizationBucketResources` ( 
  `bucket_id` int(6) NOT NULL, 
  `resource_id` char(40) NOT NULL DEFAULT '', 
  `organization_service_id` int(4) NOT NULL, 
  PRIMARY KEY (`bucket_id`,`resource_id`), 
  KEY `OrganizationBucketResources_ibfk_1` (`bucket_id`), 
  CONSTRAINT `OrganizationBucketResources_ibfk_1` FOREIGN KEY (`bucket_id`) REFERENCES `OrganizationBuckets` (`bucket_id`) ON DELETE CASCADE ON UPDATE CASCADE 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3) Issue the following ALTER TABLE statement to demonstrate error:

ALTER TABLE OrganizationBucketResources ADD CONSTRAINT OrganizationBuckets_ibfk_1 FOREIGN KEY (organization_service_id) REFERENCES intCloudServiceOrganization (organization_service_id) ON DELETE CASCADE ON UPDATE CASCADE;

Expected result is an OK response and table modified to add specified foreign key.

Actual result is:
ERROR 1050 (42S01): Table './bug/OrganizationBucketResources' already exists

Hello Andrew,

Thank you for your bug report and test case.
Verified as described.

Thanks,
Umesh

// 5.5.32  - affected

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.32-log |
+------------+
1 row in set (0.00 sec)

mysql> CREATE DATABASE bug;
Query OK, 1 row affected (0.00 sec)

mysql> use bug
Database changed
mysql> CREATE TABLE `intCloudServiceOrganization` (
    ->   `organization_service_id` int(4) NOT NULL AUTO_INCREMENT,
    ->   `org_service_name` varchar(64) DEFAULT NULL,
    ->   `organization_account_number` varchar(16) DEFAULT NULL,
    ->   `organization_service_key` varchar(32) DEFAULT NULL,
    ->   `organization_id` int(4) DEFAULT NULL,
    ->   `service_id` int(2) DEFAULT NULL,
    ->   `organization_secret` varchar(64) DEFAULT NULL,
    ->   `organization_service_enckeys` varchar(255) DEFAULT NULL,
    ->   `auth_fail_count` int(4) DEFAULT '0',
    ->   `inactive` tinyint(1) DEFAULT '0',
    ->   PRIMARY KEY (`organization_service_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `OrganizationBuckets` (
    ->   `bucket_id` int(6) NOT NULL AUTO_INCREMENT,
    ->   `bucket_name` varchar(32) NOT NULL,
    ->   `organization_id` int(4) NOT NULL,
    ->   PRIMARY KEY (`bucket_id`),
    ->   UNIQUE KEY `bucket_name` (`bucket_name`,`organization_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `OrganizationBucketResources` (
    ->   `bucket_id` int(6) NOT NULL,
    ->   `resource_id` char(40) NOT NULL DEFAULT '',
    ->   `organization_service_id` int(4) NOT NULL,
    ->   PRIMARY KEY (`bucket_id`,`resource_id`),
    ->   KEY `OrganizationBucketResources_ibfk_1` (`bucket_id`),
    ->   CONSTRAINT `OrganizationBucketResources_ibfk_1` FOREIGN KEY (`bucket_id`) REFERENCES `OrganizationBuckets` (`bucket_id`) ON DELETE CASCADE ON UPDATE CASCADE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> ALTER TABLE OrganizationBucketResources ADD CONSTRAINT OrganizationBuckets_ibfk_1 FOREIGN KEY (organization_service_id) REFERENCES intCloudServiceOrganization (organization_service_id) ON DELETE CASCADE ON UPDATE CASCADE;
ERROR 1050 (42S01): Table './bug/OrganizationBucketResources' already exists
mysql>

// 5.5.33  - affected

mysql> select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.5.33-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE DATABASE bug;
Query OK, 1 row affected (0.00 sec)

mysql> use bug
Database changed
mysql>
mysql> CREATE TABLE `intCloudServiceOrganization` (
    ->   `organization_service_id` int(4) NOT NULL AUTO_INCREMENT,
    ->   `org_service_name` varchar(64) DEFAULT NULL,
    ->   `organization_account_number` varchar(16) DEFAULT NULL,
    ->   `organization_service_key` varchar(32) DEFAULT NULL,
    ->   `organization_id` int(4) DEFAULT NULL,
    ->   `service_id` int(2) DEFAULT NULL,
    ->   `organization_secret` varchar(64) DEFAULT NULL,
    ->   `organization_service_enckeys` varchar(255) DEFAULT NULL,
    ->   `auth_fail_count` int(4) DEFAULT '0',
    ->   `inactive` tinyint(1) DEFAULT '0',
    ->   PRIMARY KEY (`organization_service_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `OrganizationBuckets` (
    ->   `bucket_id` int(6) NOT NULL AUTO_INCREMENT,
    ->   `bucket_name` varchar(32) NOT NULL,
    ->   `organization_id` int(4) NOT NULL,
    ->   PRIMARY KEY (`bucket_id`),
    ->   UNIQUE KEY `bucket_name` (`bucket_name`,`organization_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `OrganizationBucketResources` (
    ->   `bucket_id` int(6) NOT NULL,
    ->   `resource_id` char(40) NOT NULL DEFAULT '',
    ->   `organization_service_id` int(4) NOT NULL,
    ->   PRIMARY KEY (`bucket_id`,`resource_id`),
    ->   KEY `OrganizationBucketResources_ibfk_1` (`bucket_id`),
    ->   CONSTRAINT `OrganizationBucketResources_ibfk_1` FOREIGN KEY (`bucket_id`) REFERENCES `OrganizationBuckets` (`bucket_id`) ON DELETE CASCADE ON UPDATE CASCADE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE OrganizationBucketResources ADD CONSTRAINT OrganizationBuckets_ibfk_1 FOREIGN KEY (organization_service_id) REFERENCES intCloudServiceOrganization (organization_service_id) ON DELETE CASCADE ON UPDATE CASCADE;
ERROR 1050 (42S01): Table './bug/OrganizationBucketResources' already exists
mysql>
mysql> show errors;
+-------+------+-------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                   |
+-------+------+-------------------------------------------------------------------------------------------+
| Error | 1050 | Table './bug/OrganizationBucketResources' already exists                                  |
| Error | 1025 | Error on rename of './bug/#sql-23f0_1' to './bug/OrganizationBucketResources' (errno: -1) |
+-------+------+-------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

// 5.6.11 - Works

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.11    |
+-----------+
1 row in set (0.00 sec)

mysql> CREATE DATABASE bug;
Query OK, 1 row affected (0.00 sec)

mysql> use bug;
Database changed
mysql> CREATE TABLE `intCloudServiceOrganization` (
    ->   `organization_service_id` int(4) NOT NULL AUTO_INCREMENT,
    ->   `org_service_name` varchar(64) DEFAULT NULL,
    ->   `organization_account_number` varchar(16) DEFAULT NULL,
    ->   `organization_service_key` varchar(32) DEFAULT NULL,
    ->   `organization_id` int(4) DEFAULT NULL,
    ->   `service_id` int(2) DEFAULT NULL,
    ->   `organization_secret` varchar(64) DEFAULT NULL,
    ->   `organization_service_enckeys` varchar(255) DEFAULT NULL,
    ->   `auth_fail_count` int(4) DEFAULT '0',
    ->   `inactive` tinyint(1) DEFAULT '0',
    ->   PRIMARY KEY (`organization_service_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE `OrganizationBuckets` (
    ->   `bucket_id` int(6) NOT NULL AUTO_INCREMENT,
    ->   `bucket_name` varchar(32) NOT NULL,
    ->   `organization_id` int(4) NOT NULL,
    ->   PRIMARY KEY (`bucket_id`),
    ->   UNIQUE KEY `bucket_name` (`bucket_name`,`organization_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE `OrganizationBucketResources` (
    ->   `bucket_id` int(6) NOT NULL,
    ->   `resource_id` char(40) NOT NULL DEFAULT '',
    ->   `organization_service_id` int(4) NOT NULL,
    ->   PRIMARY KEY (`bucket_id`,`resource_id`),
    ->   KEY `OrganizationBucketResources_ibfk_1` (`bucket_id`),
    ->   CONSTRAINT `OrganizationBucketResources_ibfk_1` FOREIGN KEY (`bucket_id`) REFERENCES `OrganizationBuckets` (`bucket_id`) ON DELETE CASCADE ON UPDATE CASCADE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.07 sec)

mysql> ALTER TABLE OrganizationBucketResources ADD CONSTRAINT OrganizationBuckets_ibfk_1 FOREIGN KEY (organization_service_id) REFERENCES intCloudServiceOrganization (organization_service_id) ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table OrganizationBucketResources\G
*************************** 1. row ***************************
       Table: OrganizationBucketResources
Create Table: CREATE TABLE `OrganizationBucketResources` (
  `bucket_id` int(6) NOT NULL,
  `resource_id` char(40) NOT NULL DEFAULT '',
  `organization_service_id` int(4) NOT NULL,
  PRIMARY KEY (`bucket_id`,`resource_id`),
  KEY `OrganizationBucketResources_ibfk_1` (`bucket_id`),
  KEY `OrganizationBuckets_ibfk_1` (`organization_service_id`),
  CONSTRAINT `OrganizationBuckets_ibfk_1` FOREIGN KEY (`organization_service_id`) REFERENCES `intCloudServiceOrganization` (`organization_service_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `OrganizationBucketResources_ibfk_1` FOREIGN KEY (`bucket_id`) REFERENCES `OrganizationBuckets` (`bucket_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

// 5.6.13 - affected

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.13-log |
+------------+
1 row in set (0.00 sec)

mysql> CREATE DATABASE bug;
Query OK, 1 row affected (0.00 sec)

mysql> use bug
Database changed
mysql> CREATE TABLE `intCloudServiceOrganization` (
    ->   `organization_service_id` int(4) NOT NULL AUTO_INCREMENT,
    ->   `org_service_name` varchar(64) DEFAULT NULL,
    ->   `organization_account_number` varchar(16) DEFAULT NULL,
    ->   `organization_service_key` varchar(32) DEFAULT NULL,
    ->   `organization_id` int(4) DEFAULT NULL,
    ->   `service_id` int(2) DEFAULT NULL,
    ->   `organization_secret` varchar(64) DEFAULT NULL,
    ->   `organization_service_enckeys` varchar(255) DEFAULT NULL,
    ->   `auth_fail_count` int(4) DEFAULT '0',
    ->   `inactive` tinyint(1) DEFAULT '0',
    ->   PRIMARY KEY (`organization_service_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE `OrganizationBuckets` (
    ->   `bucket_id` int(6) NOT NULL AUTO_INCREMENT,
    ->   `bucket_name` varchar(32) NOT NULL,
    ->   `organization_id` int(4) NOT NULL,
    ->   PRIMARY KEY (`bucket_id`),
    ->   UNIQUE KEY `bucket_name` (`bucket_name`,`organization_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE `OrganizationBucketResources` (
    ->   `bucket_id` int(6) NOT NULL,
    ->   `resource_id` char(40) NOT NULL DEFAULT '',
    ->   `organization_service_id` int(4) NOT NULL,
    ->   PRIMARY KEY (`bucket_id`,`resource_id`),
    ->   KEY `OrganizationBucketResources_ibfk_1` (`bucket_id`),
    ->   CONSTRAINT `OrganizationBucketResources_ibfk_1` FOREIGN KEY (`bucket_id`) REFERENCES `OrganizationBuckets` (`bucket_id`) ON DELETE CASCADE ON UPDATE CASCADE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.04 sec)

mysql> ALTER TABLE OrganizationBucketResources ADD CONSTRAINT OrganizationBuckets_ibfk_1 FOREIGN KEY (organization_service_id) REFERENCES intCloudServiceOrganization (organization_service_id) ON DELETE CASCADE ON UPDATE CASCADE;
ERROR 1050 (42S01): Table './bug/OrganizationBucketResources' already exists
mysql>
mysql> show errors;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                        |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------+
| Error | 1050 | Table './bug/OrganizationBucketResources' already exists                                                                       |
| Error | 1025 | Error on rename of './bug/#sql-22da_1' to './bug/OrganizationBucketResources' (errno: -1 - Unknown error 18446744073709551615) |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Constraint name is the culprit, if you change it to OrganizationBuckets_ibfk_2 it will work.

Probably a regression from:
Bug #16722314 FOREIGN KEY ID MODIFIED DURING EXPORT

Which is in 5.5.33 changelog, but looks like it's since 5.5.32.

Fixed in 5.5.34, 5.6.14, 5.7.2. See bug#69707, bug#69693.