From 19e3597e0c718a4cfdfe8789c7b4b11a4e0ba0c6 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Jan=20Lindstr=C3=B6m?= Date: Thu, 7 Apr 2016 10:47:46 +0300 Subject: MDEV-9142 :Adding Constraint with no database reference results in ERROR 1046 (3D000) at line 13: No database selected. Use database from create table to foreign key database if nothing else is given. --- mysql-test/suite/innodb/r/innodb-fk.result | 44 ++++++++++++++++++++++++++++++ mysql-test/suite/innodb/t/innodb-fk.test | 41 ++++++++++++++++++++++++++++ sql/sql_parse.cc | 30 ++++++++++++++++---- sql/sql_parse.h | 3 +- sql/sql_table.cc | 2 +- 5 files changed, 113 insertions(+), 7 deletions(-) diff --git a/mysql-test/suite/innodb/r/innodb-fk.result b/mysql-test/suite/innodb/r/innodb-fk.result index 6c3306f5c15..7ba300b245e 100644 --- a/mysql-test/suite/innodb/r/innodb-fk.result +++ b/mysql-test/suite/innodb/r/innodb-fk.result @@ -68,3 +68,47 @@ Warning 150 Alter table `test`.`t2` with foreign key constraint failed. Referen Error 1005 Can't create table '#sql-temporary' (errno: 150) drop table t2; drop table t1; +CREATE DATABASE kg_test1; +CREATE DATABASE kg_test2; +CREATE TABLE `kg_test1`.`group` ( +Id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE TABLE `kg_test1`.`person` ( +`Id` INT(11) NOT NULL AUTO_INCREMENT, +`Name` VARCHAR(50) NOT NULL, +PRIMARY KEY (`Id`), +CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `group` (`Id`) +) ENGINE=INNODB DEFAULT CHARSET=utf8; +show create table `kg_test1`.`person`; +Table Create Table +person CREATE TABLE `person` ( + `Id` int(11) NOT NULL AUTO_INCREMENT, + `Name` varchar(50) NOT NULL, + PRIMARY KEY (`Id`), + CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `group` (`Id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 +CREATE TABLE `kg_test2`.`person2` ( +`Id` INT(11) NOT NULL AUTO_INCREMENT, +`Name` VARCHAR(50) NOT NULL, +PRIMARY KEY (`Id`), +CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `group` (`Id`) +) ENGINE=INNODB DEFAULT CHARSET=utf8; +ERROR HY000: Can't create table 'kg_test2.person2' (errno: 150) +CREATE TABLE `kg_test2`.`person2` ( +`Id` INT(11) NOT NULL AUTO_INCREMENT, +`Name` VARCHAR(50) NOT NULL, +PRIMARY KEY (`Id`), +CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `kg_test1`.`group` (`Id`) +) ENGINE=INNODB DEFAULT CHARSET=utf8; +show create table `kg_test2`.`person2`; +Table Create Table +person2 CREATE TABLE `person2` ( + `Id` int(11) NOT NULL AUTO_INCREMENT, + `Name` varchar(50) NOT NULL, + PRIMARY KEY (`Id`), + CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `kg_test1`.`group` (`Id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 +SHOW WARNINGS; +Level Code Message +DROP DATABASE kg_test2; +DROP DATABASE kg_test1; diff --git a/mysql-test/suite/innodb/t/innodb-fk.test b/mysql-test/suite/innodb/t/innodb-fk.test index 9bfd16b88e9..fd8dce7594c 100644 --- a/mysql-test/suite/innodb/t/innodb-fk.test +++ b/mysql-test/suite/innodb/t/innodb-fk.test @@ -124,3 +124,44 @@ show warnings; drop table t2; drop table t1; + +# +# MDEV-9142 :Adding Constraint with no database reference +# results in ERROR 1046 (3D000) at line 13: No database selected +# +CREATE DATABASE kg_test1; +CREATE DATABASE kg_test2; + +CREATE TABLE `kg_test1`.`group` ( + Id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `kg_test1`.`person` ( +`Id` INT(11) NOT NULL AUTO_INCREMENT, +`Name` VARCHAR(50) NOT NULL, +PRIMARY KEY (`Id`), +CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `group` (`Id`) +) ENGINE=INNODB DEFAULT CHARSET=utf8; + +show create table `kg_test1`.`person`; + +--error 1005 +CREATE TABLE `kg_test2`.`person2` ( +`Id` INT(11) NOT NULL AUTO_INCREMENT, +`Name` VARCHAR(50) NOT NULL, +PRIMARY KEY (`Id`), +CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `group` (`Id`) +) ENGINE=INNODB DEFAULT CHARSET=utf8; + +CREATE TABLE `kg_test2`.`person2` ( +`Id` INT(11) NOT NULL AUTO_INCREMENT, +`Name` VARCHAR(50) NOT NULL, +PRIMARY KEY (`Id`), +CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `kg_test1`.`group` (`Id`) +) ENGINE=INNODB DEFAULT CHARSET=utf8; + +show create table `kg_test2`.`person2`; + +SHOW WARNINGS; +DROP DATABASE kg_test2; +DROP DATABASE kg_test1; diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index a52d55b47e9..a3114aba7d3 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -5395,6 +5395,7 @@ bool check_global_access(THD *thd, ulong want_access, bool no_errors) temporary table flag) @param alter_info [in] Initial list of columns and indexes for the table to be created + @param create_db [in] Database of the created table @retval false ok. @@ -5403,7 +5404,8 @@ bool check_global_access(THD *thd, ulong want_access, bool no_errors) */ bool check_fk_parent_table_access(THD *thd, HA_CREATE_INFO *create_info, - Alter_info *alter_info) + Alter_info *alter_info, + const char* create_db) { Key *key; List_iterator key_iterator(alter_info->key_list); @@ -5443,10 +5445,28 @@ bool check_fk_parent_table_access(THD *thd, return true; } } - else if (thd->lex->copy_db_to(&db_name.str, &db_name.length)) - return true; else - is_qualified_table_name= false; + { + if (!thd->db) + { + db_name.str= (char *) thd->memdup(create_db, strlen(create_db)+1); + db_name.length= strlen(create_db); + is_qualified_table_name= true; + + if(create_db && check_db_name(&db_name)) + { + my_error(ER_WRONG_DB_NAME, MYF(0), db_name.str); + return true; + } + } + else + { + if (thd->lex->copy_db_to(&db_name.str, &db_name.length)) + return true; + else + is_qualified_table_name= false; + } + } // if lower_case_table_names is set then convert tablename to lower case. if (lower_case_table_names) @@ -7462,7 +7482,7 @@ bool create_table_precheck(THD *thd, TABLE_LIST *tables, goto err; } - if (check_fk_parent_table_access(thd, &lex->create_info, &lex->alter_info)) + if (check_fk_parent_table_access(thd, &lex->create_info, &lex->alter_info, create_table->db)) goto err; error= FALSE; diff --git a/sql/sql_parse.h b/sql/sql_parse.h index 60d5925c573..4c3070d197d 100644 --- a/sql/sql_parse.h +++ b/sql/sql_parse.h @@ -47,7 +47,8 @@ bool create_table_precheck(THD *thd, TABLE_LIST *tables, TABLE_LIST *create_table); bool check_fk_parent_table_access(THD *thd, HA_CREATE_INFO *create_info, - Alter_info *alter_info); + Alter_info *alter_info, + const char* create_db); bool parse_sql(THD *thd, Parser_state *parser_state, diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 024ca9b58e1..2cd36ceb4de 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -6305,7 +6305,7 @@ bool mysql_alter_table(THD *thd,char *new_db, char *new_name, till this point for the alter operation. */ if ((alter_info->flags & ALTER_FOREIGN_KEY) && - check_fk_parent_table_access(thd, create_info, alter_info)) + check_fk_parent_table_access(thd, create_info, alter_info, new_db)) goto err; /* -- cgit v1.2.1