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: | |
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
[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.