Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
10.0.16
-
Linux
Description
Reproduce code:
CREATE TABLE `#departments` ( |
`id_depart` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, |
PRIMARY KEY (`id_depart`) |
) ENGINE=INNODB;
|
|
|
CREATE TABLE `crm_client` ( |
`id_client` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, |
PRIMARY KEY (`id_client`) |
) ENGINE=INNODB;
|
|
|
|
|
CREATE TABLE `#departments_pos` ( |
`id_pos` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, |
`id_depart` INT(10) UNSIGNED NOT NULL, |
`id_client_bank` INT(10) UNSIGNED NOT NULL, |
PRIMARY KEY (`id_pos`), |
KEY `id_depart` (`id_depart`), |
CONSTRAINT `#departments_pos_ibfk_1` FOREIGN KEY (`id_depart`) REFERENCES `#departments` (`id_depart`) |
) ENGINE=INNODB;
|
|
|
|
|
ALTER TABLE `#departments_pos` |
ADD FOREIGN KEY (`id_client_bank`) REFERENCES `crm_client`(`id_client`); |
Error Code: 1050
|
Table './test2/@0023departments_pos' already exists
|
Workaround is manual define CONSTRAINT name
ALTER TABLE `#departments_pos` |
ADD CONSTRAINT `#departments_pos_ibfk_2` FOREIGN KEY (`id_client_bank`) REFERENCES `crm_client`(`id_client`); |
Yet another example:
CREATE TABLE `#departaments` ( |
`id_depart` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, |
PRIMARY KEY (`id_depart`) |
) ENGINE=INNODB DEFAULT CHARSET=utf8 |
|
|
|
|
CREATE TABLE `#departaments_tree` ( |
`id_depart` INT(10) UNSIGNED NOT NULL, |
`id_depart_in` INT(10) UNSIGNED NOT NULL, |
PRIMARY KEY (`id_depart`,`id_depart_in`), |
CONSTRAINT `#departaments_tree_ibfk_1` FOREIGN KEY (`id_depart`) REFERENCES `#departaments` (`id_depart`) |
) ENGINE=INNODB DEFAULT CHARSET=utf8 |
|
|
|
|
ALTER TABLE `#departaments_tree` |
ADD FOREIGN KEY (`id_depart_in`) REFERENCES `#departaments`(`id_depart`) |
Error CODE: 1050
|
TABLE './test3/@0023departaments_tree' already EXISTS
|
Workaround is manual define CONSTRAINT name
ALTER TABLE `#departaments_tree` |
ADD CONSTRAINT `#departaments_tree_ibfk_2` FOREIGN KEY (`id_depart_in`) REFERENCES `#departaments`(`id_depart`); |
Attachments
Activity
I think even more interesting is another question why it is not considered a bug innodb?
CREATE TABLE `boroda` ( |
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, |
`a` INT(11) UNSIGNED DEFAULT NULL, |
`b` INT(11) UNSIGNED DEFAULT NULL, |
PRIMARY KEY (`id`), |
KEY `a` (`a`), |
CONSTRAINT `boroda_ibfk_1` FOREIGN KEY (`a`) REFERENCES `boroda` (`id`) |
) ENGINE=INNODB DEFAULT CHARSET=utf8; |
|
|
RENAME TABLE `boroda` TO `bor#oda`; |
|
|
ALTER TABLE `bor#oda` |
ADD FOREIGN KEY (`b`) REFERENCES `bor#oda`(`id`); |
-- Error Code: 1050
|
-- Table './test5/bor@0023oda' already exists
|
Last one still same
Warning 1478 InnoDB: Duplicate foreign key name test/bor#oda_ibfk_1
|
It will rename also existing fk name to test/bor#oda_ibfk_1
Ok, found the actual problem, at dict_table_get_highest_foreign_id() we should convert dictionary memory cache foreign key identifiers to filename charset before comparing. This is because table name is on that charset.
commit 040027c888f9b9e1a41c82fd793e0cde289e5eb1
Author: Jan Lindström <jan.lindstrom@mariadb.com>
Date: Mon Mar 9 09:47:25 2015 +0200
MDEV-7627 :Some symbols in table name can cause to Error Code: 1050
when created FK
Analysis: Table name is on filename charset but foreign key
identifiers are not. This lead incorrect foreign key
identifier number to be used.
Fix: Convert foreign key identifier to filename charset before
comparing it to table name when largest foreign key identifier
number is resolved.
-- 1 queries executed, 1 success, 0 errors, 0 warnings-- 1 queries executed, 0 success, 1 errors, 0 warnings-- Error Code: 1050-- Table './test5/@0023boroda' already existsWhy using '#' is broken resolving logic?