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

[28 Aug 2013 3:59] Andrew Mann
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
[28 Aug 2013 6:13] MySQL Verification Team
Hello Andrew,

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

Thanks,
Umesh
[28 Aug 2013 6:15] MySQL Verification Team
// 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)
[28 Aug 2013 6:15] MySQL Verification Team
// 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)
[28 Aug 2013 11:04] Patryk Pomykalski
Constraint name is the culprit, if you change it to OrganizationBuckets_ibfk_2 it will work.
[28 Aug 2013 11:24] Patryk Pomykalski
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.
[12 Nov 2013 8:43] Erlend Dahl
Fixed in 5.5.34, 5.6.14, 5.7.2. See bug#69707, bug#69693.