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

Details

Type: Bug
Status: Closed (View Workflow)
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`);

Attachments

Activity

Ascending order - Click to sort in descending order

Jan Lindström (Inactive) 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.

Jan Lindström (Inactive) 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.

Mikhail Gavrilov added a comment - 2015-02-26 10:20

> I strongly suggest that you try to avoid names starting with `#` and names ending with ibfk.
But why? How it broken FK name creation?
For example for table `#someibfk` I can create unlimited number of names FK for example `#someibfk_ibfk_1` `#someibfk_ibfk_2` `#someibfk_ibfk_3` `#someibfk_ibfk_4` etc

Mikhail Gavrilov added a comment - 2015-02-26 10:20 > I strongly suggest that you try to avoid names starting with `#` and names ending with ibfk. But why? How it broken FK name creation? For example for table `#someibfk` I can create unlimited number of names FK for example `#someibfk_ibfk_1` `#someibfk_ibfk_2` `#someibfk_ibfk_3` `#someibfk_ibfk_4` etc

Jan Lindström (Inactive) added a comment - 2015-02-26 10:28

If you have create table like:

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

Then alter table like

ALTER TABLE `#departaments_tree`

  ADD FOREIGN KEY (`id_depart_in`) REFERENCES `#departaments`(`id_depart`)

WIll fail because it will try to create foreign key constraint with name `#departaments_tree_ibfk_1` in system charset, but that fails because table already has foreign key constraint with name `#departaments_tree_ibfk_1` in system charset. I personally would use:

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_fk1` FOREIGN KEY (`id_depart`) REFERENCES `departaments` (`id_depart`)

) ENGINE=INNODB DEFAULT CHARSET=utf8

Jan Lindström (Inactive) added a comment - 2015-02-26 10:28 If you have create table like: 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 Then alter table like ALTER TABLE `#departaments_tree` ADD FOREIGN KEY (`id_depart_in`) REFERENCES `#departaments`(`id_depart`) WIll fail because it will try to create foreign key constraint with name `#departaments_tree_ibfk_1` in system charset, but that fails because table already has foreign key constraint with name `#departaments_tree_ibfk_1` in system charset. I personally would use: 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_fk1` FOREIGN KEY (`id_depart`) REFERENCES `departaments` (`id_depart`) ) ENGINE=INNODB DEFAULT CHARSET=utf8

Mikhail Gavrilov added a comment - 2015-02-26 10:42

I am understand this.
But why innodb was generated `#departaments_tree_ibfk_1` name not `#departaments_tree_ibfk_2` as expected?

Mikhail Gavrilov added a comment - 2015-02-26 10:42 I am understand this. But why innodb was generated `#departaments_tree_ibfk_1` name not `#departaments_tree_ibfk_2` as expected?

Jan Lindström (Inactive) added a comment - 2015-02-26 11:12

Because numbering always starts from 1 and names are generated only for unnamed foreign key constraints. User given names are used exactly as they are given (on system charset naturally).

Jan Lindström (Inactive) added a comment - 2015-02-26 11:12 Because numbering always starts from 1 and names are generated only for unnamed foreign key constraints. User given names are used exactly as they are given (on system charset naturally).

Jan Lindström (Inactive) added a comment - 2015-02-26 14:08

I will improve the show warnings to show;

show warnings;

Level	Code	Message

Warning	1478	InnoDB: Duplicate foreign key name test/#departments_pos_ibfk_1

Error	1050	Table './test/@0023departments_pos' already exists

Error	1025	Error on rename of './test/#sql-5376_3' to './test/@0023departments_pos' (errno: -1 "Internal error < 0 (Not system error)")

Jan Lindström (Inactive) added a comment - 2015-02-26 14:08 I will improve the show warnings to show; show warnings; Level Code Message Warning 1478 InnoDB: Duplicate foreign key name test/#departments_pos_ibfk_1 Error 1050 Table './test/@0023departments_pos' already exists Error 1025 Error on rename of './test/#sql-5376_3' to './test/@0023departments_pos' (errno: -1 "Internal error < 0 (Not system error)")

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

> I will improve the show warnings to show;
Thanks, this is much better than 'Error 1050'
But I still not understand why innodb can't resolve this situation. Why it offer name which already used.
When I add unnamed foreign key constraints:

ALTER TABLE `#departaments_tree`

  ADD FOREIGN KEY (`id_depart_in`) REFERENCES `#departaments`(`id_depart`)

innodb generate name `#departaments_tree_ibfk_1` why innodb don't see that `#departaments_tree_ibfk_1` already reserved by another constraint and not use next number, and this occurs only in cases which I described above.

Mikhail Gavrilov added a comment - 2015-02-26 14:24 - edited > I will improve the show warnings to show; Thanks, this is much better than 'Error 1050' But I still not understand why innodb can't resolve this situation. Why it offer name which already used. When I add unnamed foreign key constraints: ALTER TABLE `#departaments_tree` ADD FOREIGN KEY (`id_depart_in`) REFERENCES `#departaments`(`id_depart`) innodb generate name `#departaments_tree_ibfk_1` why innodb don't see that `#departaments_tree_ibfk_1` already reserved by another constraint and not use next number, and this occurs only in cases which I described above.

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?

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?

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

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

Jan Lindström (Inactive) 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

Jan Lindström (Inactive) 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

Jan Lindström (Inactive) 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.

Jan Lindström (Inactive) 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.

Jan Lindström (Inactive) 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.

Jan Lindström (Inactive) 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 (Inactive)

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

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server