Bug #69699 add foreign key error
Submitted: 9 Jul 2013 15:31 Modified: 10 Jul 2013 5:21
Reporter: Cyril Scetbon Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.6.11 OS:Linux
Assigned to: CPU Architecture:Any
Tags: alter, error, fk

[9 Jul 2013 15:31] Cyril Scetbon
Description:
On one of our servers (only one) we can't add a foreign key using ALTER TABLE table_name ADD FOREIGN KEY, whereas it works using ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY !

mysql> CREATE TABLE `ProcessStats` (
      `processId` int(11) unsigned DEFAULT NULL,
      `processName` varchar(100) COLLATE ascii_bin NOT NULL,
      `startDate` datetime NOT NULL,
      `name` text CHARACTER SET utf8 NOT NULL,
      `valueNum` mediumtext COLLATE ascii_bin,
      `valueStr` text CHARACTER SET utf8,
      `componentName` varchar(70) COLLATE ascii_bin NOT NULL,
      PRIMARY KEY (`processName`,`startDate`,`componentName`,`name`(100)),
      KEY `ProcessStats_ibfk_processId1` (`processId`),
      CONSTRAINT `ProcessStats_ibfk_processId1` FOREIGN KEY (`processId`) REFERENCES `ProcessHistory` (`processId`),
      CONSTRAINT `processstats_ibfk_1` FOREIGN KEY (`processName`, `startDate`) REFERENCES `ProcessHistory` (`processName`, `startDate`) ON DELETE CASCADE ON UPDATE CASCADE
     ) ENGINE=InnoDB DEFAULT CHARSET=ascii COLLATE=ascii_bin COMMENT='Store the related ID for each process\n            that has been run to link the processhistory table';
Query OK, 0 rows affected (0.03 sec)
 
mysql> ALTER TABLE ProcessStats DROP FOREIGN KEY ProcessStats_ibfk_processId1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> ALTER TABLE ProcessStats ADD FOREIGN KEY fk_name (processId) REFERENCES ProcessHistory(processId) ON DELETE CASCADE ON UPDATE CASCADE;
ERROR 1050 (42S01): Table './icanic_tst/ProcessStats' already exists

mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                           |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------+
| Error | 1050 | Table './icanic_tst/ProcessStats' already exists                                                                                  |
| Error | 1025 | Error on rename of './icanic_tst/#sql-3481_104a1' to './icanic_tst/ProcessStats' (errno: -1 - Unknown error 18446744073709551615) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
 
mysql> ALTER TABLE ProcessStats ADD CONSTRAINT constraint_name FOREIGN KEY (processId) REFERENCES ProcessHistory(processId) ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

We tried to remove fk_name as it's ignored but it does not help. So we understand that those commands which should do the same, actually don't !

How to repeat:
Unfortunately we can't reproduce it on another server with the same version.

Suggested fix:
none
[10 Jul 2013 5:21] Umesh Shastry
Thank you for the bug report, duplicate of Bug #69707