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
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