Add referenced table to prelocking list during CREATE TABLE with FKfk_in_progress

14 files changed, 59 insertions, 48 deletions

diff --git a/mysql-test/main/func_regexp_pcre.result b/mysql-test/main/func_regexp_pcre.result
index 3b0688fc70e..771e22b3155 100644
--- a/mysql-test/main/func_regexp_pcre.result
+++ b/mysql-test/main/func_regexp_pcre.result
@@ -526,31 +526,35 @@ c
 b
 SELECT REGEXP_REPLACE('a\rb','(*LF)(?m)^a$','c');
 REGEXP_REPLACE('a\rb','(*LF)(?m)^a$','c')
-a
b
+a
+b
 SELECT REGEXP_REPLACE('a\rb','(*CR)(?m)^a$','c');
 REGEXP_REPLACE('a\rb','(*CR)(?m)^a$','c')
-c
b
+c
+b
 SELECT REGEXP_REPLACE('a\rb','(*CRLF)(?m)^a$','c');
 REGEXP_REPLACE('a\rb','(*CRLF)(?m)^a$','c')
-a
b
+a
+b
 SELECT REGEXP_REPLACE('a\rb','(*ANYCRLF)(?m)^a$','c');
 REGEXP_REPLACE('a\rb','(*ANYCRLF)(?m)^a$','c')
-c
b
+c
+b
 SELECT REGEXP_REPLACE('a\r\nb','(*LF)(?m)^a$','c');
 REGEXP_REPLACE('a\r\nb','(*LF)(?m)^a$','c')
-a

+a
 b
 SELECT REGEXP_REPLACE('a\r\nb','(*CR)(?m)^a$','c');
 REGEXP_REPLACE('a\r\nb','(*CR)(?m)^a$','c')
-c

+c
 b
 SELECT REGEXP_REPLACE('a\r\nb','(*CRLF)(?m)^a$','c');
 REGEXP_REPLACE('a\r\nb','(*CRLF)(?m)^a$','c')
-c

+c
 b
 SELECT REGEXP_REPLACE('a\r\nb','(*ANYCRLF)(?m)^a$','c');
 REGEXP_REPLACE('a\r\nb','(*ANYCRLF)(?m)^a$','c')
-c

+c
 b
 SELECT REGEXP_REPLACE('aa','(a)\\g1','b');
 REGEXP_REPLACE('aa','(a)\\g1','b')
diff --git a/mysql-test/main/partition.result b/mysql-test/main/partition.result
index 373522ded40..a3c88fed373 100644
--- a/mysql-test/main/partition.result
+++ b/mysql-test/main/partition.result
@@ -303,10 +303,12 @@ create index i2 on t1 (a);
 Warnings:
 Note	1831	Duplicate index `i2`. This is deprecated and will be disallowed in a future release
 drop table t1;
+CREATE TABLE t0(a int);
 CREATE TABLE t1 (a INT, FOREIGN KEY (a) REFERENCES t0 (a))
 ENGINE=MyISAM
 PARTITION BY HASH (a);
 ERROR HY000: Partitioned tables do not support FOREIGN KEY
+DROP TABLE t0;
 CREATE TABLE t1 (
 pk INT NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (pk)
diff --git a/mysql-test/main/partition.test b/mysql-test/main/partition.test
index 81752982959..c171c71c0ed 100644
--- a/mysql-test/main/partition.test
+++ b/mysql-test/main/partition.test
@@ -290,10 +290,12 @@ drop table t1;
 #
 # Bug#36001: Partitions: spelling and using some error messages
 #
---error ER_FEATURE_NOT_SUPPORTED_WITH_PARTITIONING
+CREATE TABLE t0(a int);
+--error ER_FOREIGN_KEY_ON_PARTITIONED
 CREATE TABLE t1 (a INT, FOREIGN KEY (a) REFERENCES t0 (a))
 ENGINE=MyISAM
 PARTITION BY HASH (a);
+DROP TABLE t0;
 
 #
 # Bug#40954: Crash if range search and order by.
diff --git a/mysql-test/main/union.result b/mysql-test/main/union.result
index 293c9f54651..25c2834c513 100644
--- a/mysql-test/main/union.result
+++ b/mysql-test/main/union.result
@@ -581,7 +581,7 @@ insert into users values (1);
 insert into groups values (1);
 create table t1 (   id int not null auto_increment, primary key (id)   ,user_name text );
 create table t2 (    id int not null auto_increment, primary key (id)   ,group_name text );
-create table t3 (    id int not null auto_increment, primary key (id)   ,user_id int   ,index user_idx (user_id)   ,foreign key (user_id) references users(id)   ,group_id int   ,index group_idx (group_id)   ,foreign key (group_id) references groups(id) );
+create table t3 (    id int not null auto_increment, primary key (id)   ,user_id int   ,index user_idx (user_id)   ,foreign key (user_id) references t1(id)   ,group_id int   ,index group_idx (group_id)   ,foreign key (group_id) references t2(id) );
 insert into t1 (user_name) values ('Tester');
 insert into t2 (group_name) values ('Group A');
 insert into t2 (group_name) values ('Group B');
diff --git a/mysql-test/main/union.test b/mysql-test/main/union.test
index 62e5c978602..5a6f37394d6 100644
--- a/mysql-test/main/union.test
+++ b/mysql-test/main/union.test
@@ -353,7 +353,7 @@ insert into users values (1);
 insert into groups values (1);
 create table t1 (   id int not null auto_increment, primary key (id)   ,user_name text );
 create table t2 (    id int not null auto_increment, primary key (id)   ,group_name text );
-create table t3 (    id int not null auto_increment, primary key (id)   ,user_id int   ,index user_idx (user_id)   ,foreign key (user_id) references users(id)   ,group_id int   ,index group_idx (group_id)   ,foreign key (group_id) references groups(id) );
+create table t3 (    id int not null auto_increment, primary key (id)   ,user_id int   ,index user_idx (user_id)   ,foreign key (user_id) references t1(id)   ,group_id int   ,index group_idx (group_id)   ,foreign key (group_id) references t2(id) );
 insert into t1 (user_name) values ('Tester');
 insert into t2 (group_name) values ('Group A');
 insert into t2 (group_name) values ('Group B');
diff --git a/mysql-test/suite/gcol/inc/gcol_keys.inc b/mysql-test/suite/gcol/inc/gcol_keys.inc
index e59b368c261..f382dd8e1c4 100644
--- a/mysql-test/suite/gcol/inc/gcol_keys.inc
+++ b/mysql-test/suite/gcol/inc/gcol_keys.inc
@@ -130,22 +130,22 @@ if (!$skip_spatial_index_check)
 --echo # FOREIGN KEY
 
 --echo # Rejected FK options.
---error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
+--error ER_NO_SUCH_TABLE
 create table t1 (a int, b int generated always as (a+1) stored,
                  foreign key (b) references t2(a) on update set null);
---error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
+--error ER_NO_SUCH_TABLE
 create table t1 (a int, b int generated always as (a+1) stored,
                  foreign key (b) references t2(a) on update cascade);
---error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
+--error ER_NO_SUCH_TABLE
 create table t1 (a int, b int generated always as (a+1) stored,
                  foreign key (b) references t2(a) on delete set null);
 
 create table t1 (a int, b int generated always as (a+1) stored);
---error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
+--error ER_NO_SUCH_TABLE
 alter table t1 add foreign key (b) references t2(a) on update set null;
---error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
+--error ER_NO_SUCH_TABLE
 alter table t1 add foreign key (b) references t2(a) on update cascade;
---error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
+--error ER_NO_SUCH_TABLE
 alter table t1 add foreign key (b) references t2(a) on delete set null;
 drop table t1;
 
diff --git a/mysql-test/suite/gcol/r/gcol_keys_innodb.result b/mysql-test/suite/gcol/r/gcol_keys_innodb.result
index de3d82172dc..1912cea75e1 100644
--- a/mysql-test/suite/gcol/r/gcol_keys_innodb.result
+++ b/mysql-test/suite/gcol/r/gcol_keys_innodb.result
@@ -133,20 +133,20 @@ drop table t1;
 # Rejected FK options.
 create table t1 (a int, b int generated always as (a+1) stored,
 foreign key (b) references t2(a) on update set null);
-ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column
+ERROR 42S02: Table 'test.t2' doesn't exist
 create table t1 (a int, b int generated always as (a+1) stored,
 foreign key (b) references t2(a) on update cascade);
-ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column
+ERROR 42S02: Table 'test.t2' doesn't exist
 create table t1 (a int, b int generated always as (a+1) stored,
 foreign key (b) references t2(a) on delete set null);
-ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column
+ERROR 42S02: Table 'test.t2' doesn't exist
 create table t1 (a int, b int generated always as (a+1) stored);
 alter table t1 add foreign key (b) references t2(a) on update set null;
-ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column
+ERROR 42S02: Table 'test.t2' doesn't exist
 alter table t1 add foreign key (b) references t2(a) on update cascade;
-ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column
+ERROR 42S02: Table 'test.t2' doesn't exist
 alter table t1 add foreign key (b) references t2(a) on delete set null;
-ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column
+ERROR 42S02: Table 'test.t2' doesn't exist
 drop table t1;
 create table t1 (a int, b int generated always as (a+1) virtual,
 foreign key (b) references t2(a));
diff --git a/mysql-test/suite/gcol/r/gcol_keys_myisam.result b/mysql-test/suite/gcol/r/gcol_keys_myisam.result
index 3f00d344901..cf0281f5d1e 100644
--- a/mysql-test/suite/gcol/r/gcol_keys_myisam.result
+++ b/mysql-test/suite/gcol/r/gcol_keys_myisam.result
@@ -140,20 +140,20 @@ drop table t1;
 # Rejected FK options.
 create table t1 (a int, b int generated always as (a+1) stored,
 foreign key (b) references t2(a) on update set null);
-ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column
+ERROR 42S02: Table 'test.t2' doesn't exist
 create table t1 (a int, b int generated always as (a+1) stored,
 foreign key (b) references t2(a) on update cascade);
-ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column
+ERROR 42S02: Table 'test.t2' doesn't exist
 create table t1 (a int, b int generated always as (a+1) stored,
 foreign key (b) references t2(a) on delete set null);
-ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column
+ERROR 42S02: Table 'test.t2' doesn't exist
 create table t1 (a int, b int generated always as (a+1) stored);
 alter table t1 add foreign key (b) references t2(a) on update set null;
-ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column
+ERROR 42S02: Table 'test.t2' doesn't exist
 alter table t1 add foreign key (b) references t2(a) on update cascade;
-ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column
+ERROR 42S02: Table 'test.t2' doesn't exist
 alter table t1 add foreign key (b) references t2(a) on delete set null;
-ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column
+ERROR 42S02: Table 'test.t2' doesn't exist
 drop table t1;
 # Allowed FK options.
 create table t2 (a int primary key, b char(5));
diff --git a/mysql-test/suite/innodb/r/foreign_key.result b/mysql-test/suite/innodb/r/foreign_key.result
index 6c31d354e12..4ea8d5b9b82 100644
--- a/mysql-test/suite/innodb/r/foreign_key.result
+++ b/mysql-test/suite/innodb/r/foreign_key.result
@@ -256,6 +256,7 @@ ALTER IGNORE TABLE t1 ADD FOREIGN KEY (a) REFERENCES t2 (b);
 ERROR 42S02: Table 'test.t2' doesn't exist
 SHOW WARNINGS;
 Level	Code	Message
+Error	1146	Table 'test.t2' doesn't exist
 DROP TABLE t1;
 #
 # MDEV-18139 ALTER IGNORE ... ADD FOREIGN KEY causes bogus error
diff --git a/mysql-test/suite/innodb/r/innodb-fk-warnings.result b/mysql-test/suite/innodb/r/innodb-fk-warnings.result
index ab200522443..af718da33a7 100644
--- a/mysql-test/suite/innodb/r/innodb-fk-warnings.result
+++ b/mysql-test/suite/innodb/r/innodb-fk-warnings.result
@@ -35,6 +35,7 @@ alter table t1 add constraint c1 foreign key (f1) references t11(f1);
 ERROR 42S02: Table 'test.t11' doesn't exist
 show warnings;
 Level	Code	Message
+Error	1146	Table 'test.t11' doesn't exist
 drop table t1;
 create temporary table t1(a int not null primary key, b int, key(b)) engine=innodb;
 create temporary table t2(a int, foreign key(a) references t1(a)) engine=innodb;
diff --git a/mysql-test/suite/innodb/r/innodb-fk.result b/mysql-test/suite/innodb/r/innodb-fk.result
index 1a8bc42b408..a2b92aa7b78 100644
--- a/mysql-test/suite/innodb/r/innodb-fk.result
+++ b/mysql-test/suite/innodb/r/innodb-fk.result
@@ -52,7 +52,7 @@ CONSTRAINT fk3 FOREIGN KEY (f3) REFERENCES t3 (id) ON DELETE CASCADE
 ERROR 42S02: Table 'test.t3' doesn't exist
 show warnings;
 Level	Code	Message
-Error	1239	Incorrect foreign key definition for 't3': referenced table not found
+Error	1146	Table 'test.t3' doesn't exist
 CREATE TABLE t2 (
 id int(11) NOT NULL AUTO_INCREMENT,
 f2 int(11) NOT NULL,
@@ -64,6 +64,7 @@ ALTER TABLE t2 ADD CONSTRAINT fk3 FOREIGN KEY (f3) REFERENCES t3 (id) ON DELETE
 ERROR 42S02: Table 'test.t3' doesn't exist
 show warnings;
 Level	Code	Message
+Error	1146	Table 'test.t3' doesn't exist
 drop table t2;
 drop table t1;
 CREATE DATABASE kg_test1;
diff --git a/mysql-test/suite/vcol/inc/vcol_keys.inc b/mysql-test/suite/vcol/inc/vcol_keys.inc
index 7031f784506..6a78bc7dd3b 100644
--- a/mysql-test/suite/vcol/inc/vcol_keys.inc
+++ b/mysql-test/suite/vcol/inc/vcol_keys.inc
@@ -103,22 +103,22 @@ if (!$skip_spatial_index_check)
 --echo # FOREIGN KEY
 
 --echo # Rejected FK options.
---error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN 
+--error ER_NO_SUCH_TABLE
 create table t1 (a int, b int as (a+1) persistent,
                  foreign key (b) references t2(a) on update set null);
---error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN 
+--error ER_NO_SUCH_TABLE
 create table t1 (a int, b int as (a+1) persistent,
                  foreign key (b) references t2(a) on update cascade);
---error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN 
+--error ER_NO_SUCH_TABLE
 create table t1 (a int, b int as (a+1) persistent,
                  foreign key (b) references t2(a) on delete set null);
 
 create table t1 (a int, b int as (a+1) persistent);
---error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN 
+--error ER_NO_SUCH_TABLE
 alter table t1 add foreign key (b) references t2(a) on update set null;
---error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN 
+--error ER_NO_SUCH_TABLE
 alter table t1 add foreign key (b) references t2(a) on update cascade;
---error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN 
+--error ER_NO_SUCH_TABLE
 alter table t1 add foreign key (b) references t2(a) on delete set null;
 drop table t1;
 
diff --git a/mysql-test/suite/vcol/r/vcol_keys_innodb.result b/mysql-test/suite/vcol/r/vcol_keys_innodb.result
index 25c6ace9d0f..86b22965f5c 100644
--- a/mysql-test/suite/vcol/r/vcol_keys_innodb.result
+++ b/mysql-test/suite/vcol/r/vcol_keys_innodb.result
@@ -107,20 +107,20 @@ drop table t1;
 # Rejected FK options.
 create table t1 (a int, b int as (a+1) persistent,
 foreign key (b) references t2(a) on update set null);
-ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column
+ERROR 42S02: Table 'test.t2' doesn't exist
 create table t1 (a int, b int as (a+1) persistent,
 foreign key (b) references t2(a) on update cascade);
-ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column
+ERROR 42S02: Table 'test.t2' doesn't exist
 create table t1 (a int, b int as (a+1) persistent,
 foreign key (b) references t2(a) on delete set null);
-ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column
+ERROR 42S02: Table 'test.t2' doesn't exist
 create table t1 (a int, b int as (a+1) persistent);
 alter table t1 add foreign key (b) references t2(a) on update set null;
-ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column
+ERROR 42S02: Table 'test.t2' doesn't exist
 alter table t1 add foreign key (b) references t2(a) on update cascade;
-ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column
+ERROR 42S02: Table 'test.t2' doesn't exist
 alter table t1 add foreign key (b) references t2(a) on delete set null;
-ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column
+ERROR 42S02: Table 'test.t2' doesn't exist
 drop table t1;
 create table t1 (a int, b int as (a+1), foreign key (b) references t2(a));
 ERROR 42S02: Table 'test.t2' doesn't exist
diff --git a/mysql-test/suite/vcol/r/vcol_keys_myisam.result b/mysql-test/suite/vcol/r/vcol_keys_myisam.result
index 1e6c854e0d9..fdb8325e5af 100644
--- a/mysql-test/suite/vcol/r/vcol_keys_myisam.result
+++ b/mysql-test/suite/vcol/r/vcol_keys_myisam.result
@@ -107,20 +107,20 @@ drop table t1;
 # Rejected FK options.
 create table t1 (a int, b int as (a+1) persistent,
 foreign key (b) references t2(a) on update set null);
-ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column
+ERROR 42S02: Table 'test.t2' doesn't exist
 create table t1 (a int, b int as (a+1) persistent,
 foreign key (b) references t2(a) on update cascade);
-ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column
+ERROR 42S02: Table 'test.t2' doesn't exist
 create table t1 (a int, b int as (a+1) persistent,
 foreign key (b) references t2(a) on delete set null);
-ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column
+ERROR 42S02: Table 'test.t2' doesn't exist
 create table t1 (a int, b int as (a+1) persistent);
 alter table t1 add foreign key (b) references t2(a) on update set null;
-ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column
+ERROR 42S02: Table 'test.t2' doesn't exist
 alter table t1 add foreign key (b) references t2(a) on update cascade;
-ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column
+ERROR 42S02: Table 'test.t2' doesn't exist
 alter table t1 add foreign key (b) references t2(a) on delete set null;
-ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column
+ERROR 42S02: Table 'test.t2' doesn't exist
 drop table t1;
 # Allowed FK options.
 create table t2 (a int primary key, b char(5));