[MDEV-7627] Some symbols in table name can cause to Error Code: 1050 when created FK Created: 2015-02-25  Updated: 2015-03-09  Resolved: 2015-03-09

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB, Storage Engine - XtraDB
Affects Version/s: 10.0.16
Fix Version/s: 10.0.18

Type: Bug Priority: Minor
Reporter: Mikhail Gavrilov Assignee: Jan Lindström (Inactive)
Resolution: Fixed Votes: 0
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`);



 Comments   
Comment by Jan Lindström (Inactive) [ 2015-02-26 ]

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.

Comment by Mikhail Gavrilov [ 2015-02-26 ]

> 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

Comment by Jan Lindström (Inactive) [ 2015-02-26 ]

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

Comment by Mikhail Gavrilov [ 2015-02-26 ]

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

Comment by Jan Lindström (Inactive) [ 2015-02-26 ]

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

Comment by Jan Lindström (Inactive) [ 2015-02-26 ]

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

Comment by Mikhail Gavrilov [ 2015-02-26 ]

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

Comment by Mikhail Gavrilov [ 2015-02-26 ] 

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?

Comment by Mikhail Gavrilov [ 2015-02-26 ]

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

Comment by Jan Lindström (Inactive) [ 2015-02-26 ]

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

Comment by Jan Lindström (Inactive) [ 2015-03-09 ]

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.

Comment by Jan Lindström (Inactive) [ 2015-03-09 ]

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.

Generated at Thu Feb 08 07:21:03 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.