[MDEV-7627] Some symbols in table name can cause to Error Code: 1050 when created FK - JIRA

Details

Type: Bug
Status: Closed
Priority: Minor
Resolution: Fixed
Affects Version/s: 10.0.16
Fix Version/s: 10.0.18
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB, Storage Engine - XtraDB
Labels:
- CONSTRAINT
- alter
Environment:
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`);

Gliffy Diagrams

Attachments

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Jan Lindström added a comment - 2015-02-26 08:16

Lets start from the facts, this is not a bug. InnoDB internally uses foreign keys named with <table_name>ibfk<n> if no other name is provided. I strongly suggest that you try to avoid names starting with `#` and names ending with ibfk. Now the error message is really meaningless but you should see on error log something like this:

InnoDB: 1) Table rename would cause two FOREIGN KEY constraints
InnoDB: to have the same internal name in case-insensitive comparison.
InnoDB: 2) table `test`.`#departments_pos` exists in the InnoDB internal data
InnoDB: dictionary though MySQL is trying to rename table `test`.`#sql-4389_3` to it.
InnoDB: Have you deleted the .frm file and not used DROP TABLE?
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html
InnoDB: If table `test`.`#departments_pos` is a temporary table #sql..., then it can be that
InnoDB: there are still queries running on the table, and it will be
InnoDB: dropped automatically when the queries end.
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and copying the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.

In above examples you have named your foreign key constraint so that alter will fail because there would be two identical foreign key names in InnoDB data dictionary.

I will try to add more clearer error message so that show warnings after failed alter table would return more information why alter really failed.

Show

Jan Lindström added a comment - 2015-02-26 08:16 Lets start from the facts, this is not a bug. InnoDB internally uses foreign keys named with <table_name> ibfk <n> if no other name is provided. I strongly suggest that you try to avoid names starting with `#` and names ending with ibfk . Now the error message is really meaningless but you should see on error log something like this: InnoDB: 1) Table rename would cause two FOREIGN KEY constraints InnoDB: to have the same internal name in case-insensitive comparison. InnoDB: 2) table `test`.`#departments_pos` exists in the InnoDB internal data InnoDB: dictionary though MySQL is trying to rename table `test`.`#sql-4389_3` to it. InnoDB: Have you deleted the .frm file and not used DROP TABLE? InnoDB: You can look for further help from InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html InnoDB: If table `test`.`#departments_pos` is a temporary table #sql..., then it can be that InnoDB: there are still queries running on the table, and it will be InnoDB: dropped automatically when the queries end. InnoDB: You can drop the orphaned table inside InnoDB by InnoDB: creating an InnoDB table with the same name in another InnoDB: database and copying the .frm file to the current database. InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed. In above examples you have named your foreign key constraint so that alter will fail because there would be two identical foreign key names in InnoDB data dictionary. I will try to add more clearer error message so that show warnings after failed alter table would return more information why alter really failed.

6 older comments

Hide

Permalink

Mikhail Gavrilov added a comment - 2015-02-26 14:50 - edited

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


ALTER TABLE `boroda`  
  ADD FOREIGN KEY (`b`) REFERENCES `boroda`(`id`);

-- 1 queries executed, 1 success, 0 errors, 0 warnings


ALTER TABLE `boroda` DROP FOREIGN KEY `boroda_ibfk_2`; 

RENAME TABLE `boroda` TO `#boroda`; 

ALTER TABLE `#boroda`  
ADD FOREIGN KEY (`b`) REFERENCES `#boroda`(`id`);

-- 1 queries executed, 0 success, 1 errors, 0 warnings
-- Error Code: 1050
-- Table './test5/@0023boroda' already exists

Why using '#' is broken resolving logic?

Show

Mikhail Gavrilov added a comment - 2015-02-26 14:50 - edited 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 ALTER TABLE `boroda` ADD FOREIGN KEY (`b`) REFERENCES `boroda`(`id`); -- 1 queries executed, 1 success, 0 errors, 0 warnings ALTER TABLE `boroda` DROP FOREIGN KEY `boroda_ibfk_2`; RENAME TABLE `boroda` TO `#boroda`; ALTER TABLE `#boroda` ADD FOREIGN KEY (`b`) REFERENCES `#boroda`(`id`); -- 1 queries executed, 0 success, 1 errors, 0 warnings -- Error Code: 1050 -- Table './test5/@0023boroda' already exists Why using '#' is broken resolving logic?

Hide

Permalink

Mikhail Gavrilov added a comment - 2015-02-26 14:57

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

Show

Mikhail Gavrilov added a comment - 2015-02-26 14:57 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

Hide

Permalink

Jan Lindström added a comment - 2015-02-26 15:38 - edited

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

Show

Jan Lindström added a comment - 2015-02-26 15:38 - edited 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

Hide

Permalink

Jan Lindström added a comment - 2015-03-09 09:15

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.

Show

Jan Lindström added a comment - 2015-03-09 09:15 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.

Hide

Permalink

Jan Lindström added a comment - 2015-03-09 09:55

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.

Show

Jan Lindström added a comment - 2015-03-09 09:55 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.

People

Assignee:

Jan Lindström

Reporter:

Mikhail Gavrilov

Votes:

0 Vote for this issue

Watchers:

2 Start watching this issue

Dates

Created:

2015-02-25 09:13

Updated:

2015-03-09 09:55

Resolved:

2015-03-09 09:55

Agile

View on Board