[Commits] 040027c: MDEV-7627 :Some symbols in table name can cause to Error Code: 1050

Jan Lindström jan.lindstrom at mariadb.com
Mon Mar 9 09:49:22 EET 2015


revision-id: 040027c888f9b9e1a41c82fd793e0cde289e5eb1
parent(s): 6fc0a8af243befc8504676b9d7a6f6bbeafb85b1
committer: Jan Lindström
branch nick: 10.0-git
timestamp: 2015-03-09 09:47:25 +0200
message:

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.

---
 mysql-test/suite/innodb/r/innodb-fk.result | 75 ++++++++++++++++++++++++++++++
 mysql-test/suite/innodb/t/innodb-fk.test   | 63 +++++++++++++++++++++++++
 storage/innobase/dict/dict0dict.cc         | 19 ++++++--
 storage/xtradb/dict/dict0dict.cc           | 19 ++++++--
 4 files changed, 166 insertions(+), 10 deletions(-)

diff --git a/mysql-test/suite/innodb/r/innodb-fk.result b/mysql-test/suite/innodb/r/innodb-fk.result
index 82ac412..956dbce 100644
--- a/mysql-test/suite/innodb/r/innodb-fk.result
+++ b/mysql-test/suite/innodb/r/innodb-fk.result
@@ -67,3 +67,78 @@ Error	1005	Can't create table `test`.`#sql-temporary` (errno: 150 "Foreign key c
 Warning	1215	Cannot add foreign key constraint
 drop table t2;
 drop table t1;
+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`);
+SHOW CREATE TABLE `#departaments_tree`;
+Table	Create Table
+#departaments_tree	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`),
+  KEY `id_depart_in` (`id_depart_in`),
+  CONSTRAINT `#departaments_tree_ibfk_1` FOREIGN KEY (`id_depart`) REFERENCES `#departaments` (`id_depart`),
+  CONSTRAINT `#departaments_tree_ibfk_2` FOREIGN KEY (`id_depart_in`) REFERENCES `#departaments` (`id_depart`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8
+DROP TABLE `#departaments_tree`;
+DROP TABLE `#departaments`;
+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`);
+ALTER TABLE `boroda` DROP FOREIGN KEY `boroda_ibfk_2`;
+RENAME TABLE `boroda` TO `#boroda`;
+ALTER TABLE `#boroda`
+ADD FOREIGN KEY (`b`) REFERENCES `#boroda`(`id`);
+SHOW CREATE TABLE `#boroda`;
+Table	Create Table
+#boroda	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`),
+  KEY `b` (`b`),
+  CONSTRAINT `#boroda_ibfk_1` FOREIGN KEY (`a`) REFERENCES `#boroda` (`id`),
+  CONSTRAINT `#boroda_ibfk_2` FOREIGN KEY (`b`) REFERENCES `#boroda` (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8
+DROP TABLE `#boroda`;
+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`);
+SHOW CREATE TABLE `bor#oda`;
+Table	Create Table
+bor#oda	CREATE TABLE `bor#oda` (
+  `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`),
+  KEY `b` (`b`),
+  CONSTRAINT `bor#oda_ibfk_1` FOREIGN KEY (`a`) REFERENCES `bor#oda` (`id`),
+  CONSTRAINT `bor#oda_ibfk_2` FOREIGN KEY (`b`) REFERENCES `bor#oda` (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8
+DROP TABLE `bor#oda`;
diff --git a/mysql-test/suite/innodb/t/innodb-fk.test b/mysql-test/suite/innodb/t/innodb-fk.test
index 2c2a8c7..f7bcbe2 100644
--- a/mysql-test/suite/innodb/t/innodb-fk.test
+++ b/mysql-test/suite/innodb/t/innodb-fk.test
@@ -124,3 +124,66 @@ show warnings;
 
 drop table t2;
 drop table t1;
+
+#
+# MDEV-7627: Some symbols in table name can cause to Error Code: 1050 when created FK
+#
+
+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`);
+
+SHOW CREATE TABLE `#departaments_tree`;
+
+DROP TABLE `#departaments_tree`;
+DROP TABLE `#departaments`;
+
+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`);
+
+ALTER TABLE `boroda` DROP FOREIGN KEY `boroda_ibfk_2`;
+
+RENAME TABLE `boroda` TO `#boroda`;
+
+ALTER TABLE `#boroda`
+ADD FOREIGN KEY (`b`) REFERENCES `#boroda`(`id`);
+
+SHOW CREATE TABLE `#boroda`;
+DROP TABLE `#boroda`;
+
+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`);
+
+SHOW CREATE TABLE `bor#oda`;
+DROP TABLE `bor#oda`;
diff --git a/storage/innobase/dict/dict0dict.cc b/storage/innobase/dict/dict0dict.cc
index 3ae6fa0..7c3c4e8 100644
--- a/storage/innobase/dict/dict0dict.cc
+++ b/storage/innobase/dict/dict0dict.cc
@@ -4110,16 +4110,25 @@ dict_table_get_highest_foreign_id(
 	for (dict_foreign_set::iterator it = table->foreign_set.begin();
 	     it != table->foreign_set.end();
 	     ++it) {
+		char    fkid[MAX_TABLE_NAME_LEN+20];
 		foreign = *it;
 
-		if (ut_strlen(foreign->id) > ((sizeof dict_ibfk) - 1) + len
-		    && 0 == ut_memcmp(foreign->id, table->name, len)
-		    && 0 == ut_memcmp(foreign->id + len,
+		strcpy(fkid, foreign->id);
+		/* Convert foreign key identifier on dictionary memory
+		cache to filename charset. */
+		innobase_convert_to_filename_charset(
+				strchr(fkid, '/') + 1,
+				strchr(foreign->id, '/') + 1,
+				MAX_TABLE_NAME_LEN);
+
+		if (ut_strlen(fkid) > ((sizeof dict_ibfk) - 1) + len
+		    && 0 == ut_memcmp(fkid, table->name, len)
+		    && 0 == ut_memcmp(fkid + len,
 				      dict_ibfk, (sizeof dict_ibfk) - 1)
-		    && foreign->id[len + ((sizeof dict_ibfk) - 1)] != '0') {
+		    && fkid[len + ((sizeof dict_ibfk) - 1)] != '0') {
 			/* It is of the >= 4.0.18 format */
 
-			id = strtoul(foreign->id + len
+			id = strtoul(fkid + len
 				     + ((sizeof dict_ibfk) - 1),
 				     &endp, 10);
 			if (*endp == '\0') {
diff --git a/storage/xtradb/dict/dict0dict.cc b/storage/xtradb/dict/dict0dict.cc
index 12d1452..6b62819 100644
--- a/storage/xtradb/dict/dict0dict.cc
+++ b/storage/xtradb/dict/dict0dict.cc
@@ -4114,16 +4114,25 @@ dict_table_get_highest_foreign_id(
 	for (dict_foreign_set::iterator it = table->foreign_set.begin();
 	     it != table->foreign_set.end();
 	     ++it) {
+		char    fkid[MAX_TABLE_NAME_LEN+20];
 		foreign = *it;
 
-		if (ut_strlen(foreign->id) > ((sizeof dict_ibfk) - 1) + len
-		    && 0 == ut_memcmp(foreign->id, table->name, len)
-		    && 0 == ut_memcmp(foreign->id + len,
+		strcpy(fkid, foreign->id);
+		/* Convert foreign key identifier on dictionary memory
+		cache to filename charset. */
+		innobase_convert_to_filename_charset(
+				strchr(fkid, '/') + 1,
+				strchr(foreign->id, '/') + 1,
+				MAX_TABLE_NAME_LEN);
+
+		if (ut_strlen(fkid) > ((sizeof dict_ibfk) - 1) + len
+		    && 0 == ut_memcmp(fkid, table->name, len)
+		    && 0 == ut_memcmp(fkid + len,
 				      dict_ibfk, (sizeof dict_ibfk) - 1)
-		    && foreign->id[len + ((sizeof dict_ibfk) - 1)] != '0') {
+		    && fkid[len + ((sizeof dict_ibfk) - 1)] != '0') {
 			/* It is of the >= 4.0.18 format */
 
-			id = strtoul(foreign->id + len
+			id = strtoul(fkid + len
 				     + ((sizeof dict_ibfk) - 1),
 				     &endp, 10);
 			if (*endp == '\0') {


More information about the commits mailing list