diff options
author | Jan Lindström <jan.lindstrom@mariadb.com> | 2015-03-09 09:47:25 +0200 |
---|---|---|
committer | Jan Lindström <jan.lindstrom@mariadb.com> | 2015-03-09 09:47:25 +0200 |
commit | 040027c888f9b9e1a41c82fd793e0cde289e5eb1 (patch) | |
tree | 6afd5b095054fb00aa38a173e24cec34f627538a | |
parent | 6fc0a8af243befc8504676b9d7a6f6bbeafb85b1 (diff) | |
download | mariadb-git-040027c888f9b9e1a41c82fd793e0cde289e5eb1.tar.gz |
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.
-rw-r--r-- | mysql-test/suite/innodb/r/innodb-fk.result | 75 | ||||
-rw-r--r-- | mysql-test/suite/innodb/t/innodb-fk.test | 63 | ||||
-rw-r--r-- | storage/innobase/dict/dict0dict.cc | 19 | ||||
-rw-r--r-- | 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 82ac412e339..956dbce58ed 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 2c2a8c76cf8..f7bcbe238dd 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 3ae6fa032b9..7c3c4e824d5 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 12d145220d1..6b628198275 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') { |