[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

Descending order - Click to sort in ascending order

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.

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

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

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?

View 7 older comments

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