180 lines
6.3 KiB
Text
180 lines
6.3 KiB
Text
#########################################
|
|
# ==== Purpose ====
|
|
#
|
|
# Test various scenarios involved in creation of foreign key (FK)
|
|
# when executing CREATE TABLE ... SELECT (DDL_CTAS).
|
|
#
|
|
# ==== Requirements ====
|
|
#
|
|
# R1 The behavior of non-atomic DDL_CTAS remains the same
|
|
# with request to create FK.
|
|
#
|
|
# R2 The behavior of atomic CTAS and request to create FK is rejected if
|
|
# following conditions are true,
|
|
# - SE supports foreign keys.
|
|
# - SE supports atomic DDL.
|
|
# - The binlogging is enabled.
|
|
# - The binlog format is 'row'.
|
|
#
|
|
# ==== Implementation ====
|
|
#
|
|
# TC1 The behavior of non-atomic CTAS remains the same
|
|
# with request to create FK.
|
|
# 1) Allow creation of FK on table using MyISAM engine. Using rows which
|
|
# violate FK constraint. Make sure that the FK constraint is ignored.
|
|
# 2) Allow creation of FK on table using MyISAM engine. Using rows which
|
|
# do not violate FK constraint.
|
|
#
|
|
# TC2 The behavior of atomic CTAS, with sql_log_bin OFF.
|
|
# 1) Attempt to create FK on table using InnoDB engine. Using rows which
|
|
# violate FK constraint. Make sure we get ER_NO_REFERENCED_ROW_2.
|
|
# 2) Allow creation of FK on table using InnoDB engine. Using rows which
|
|
# do not violate FK constraint.
|
|
#
|
|
# TC3 The behavior of CTAS, with sql_log_bin ON and format STATEMENT.
|
|
# 1) Attempt to create FK on table using InnoDB engine. Using rows which
|
|
# violate FK constraint. Make sure we get ER_NO_REFERENCED_ROW_2.
|
|
# 2) Allow creation of FK on table using InnoDB engine. Using rows which
|
|
# do not violate FK constraint.
|
|
#
|
|
# TC4 The behavior of CTAS, with sql_log_bin ON and format MIXED.
|
|
# 1) Steps are same as TC3 and the behavior too would be same.
|
|
#
|
|
# TC5 The behavior of CTAS, with sql_log_bin ON and format ROW.
|
|
# 1) Attempt to create FK on table using InnoDB engine and using rows which
|
|
# violate FK constraint results in
|
|
# ER_FOREIGN_KEY_WITH_ATOMIC_CREATE_SELECT.
|
|
# 2) Attempt to create FK on table using InnoDB engine and using rows which
|
|
# do not violate FK constraint results in
|
|
# ER_FOREIGN_KEY_WITH_ATOMIC_CREATE_SELECT.
|
|
# 3) Attempt to create FK on table using InnoDB engine with CREATE TABLE ...
|
|
# START TRANSACTION is rejected with
|
|
# ER_FOREIGN_KEY_WITH_ATOMIC_CREATE_SELECT.
|
|
#
|
|
# ==== References ====
|
|
#
|
|
# WL#13355 Make CREATE TABLE...SELECT atomic and crash-safe
|
|
#
|
|
|
|
# Skip ps protocol because CREATE TABLE ... START TRANSACTION is not
|
|
# allowed to be run with ps protocol.
|
|
--source include/no_ps_protocol.inc
|
|
--source include/have_log_bin.inc
|
|
|
|
SET @saved_sql_log_bin = @@SESSION.sql_log_bin;
|
|
|
|
--echo #
|
|
--echo # CREATE TABLE AS SELECT (CTAS) and foreign key (FK).
|
|
--echo #
|
|
|
|
CREATE TABLE t0 (f1 INT PRIMARY KEY);
|
|
INSERT INTO t0 VALUES (1),(2),(3),(4);
|
|
|
|
--echo #
|
|
--echo # CASE 1 The behavior of non-atomic CTAS remains the same
|
|
--echo # with request to create FK. There is no engine which do
|
|
--echo # not support atomic DDL, but supports foreign keys.
|
|
|
|
--echo # MyISAM does not support foreign keys, so there is no error
|
|
--echo # as it ignore FK constraints silently.
|
|
CREATE TABLE myisam_table1 (m INT, n INT, FOREIGN KEY (n) REFERENCES t0(f1))
|
|
ENGINE=MyISAM AS SELECT 101 as m, 5 as n;
|
|
SHOW CREATE TABLE myisam_table1;
|
|
CREATE TABLE myisam_table2 (m INT, n INT, FOREIGN KEY (n) REFERENCES t0(f1))
|
|
ENGINE=MyISAM AS SELECT 101 as m, 2 as n;
|
|
SHOW CREATE TABLE myisam_table2;
|
|
|
|
DROP TABLE myisam_table1;
|
|
DROP TABLE myisam_table2;
|
|
|
|
--echo #
|
|
--echo # CASE 2 The behavior of atomic CTAS, with sql_log_bin OFF.
|
|
--echo #
|
|
|
|
SET sql_log_bin = OFF;
|
|
--error ER_NO_REFERENCED_ROW_2
|
|
CREATE TABLE innodb_table1 (m INT, n INT, FOREIGN KEY (n) REFERENCES t0(f1))
|
|
AS SELECT 101 as m, 5 as n;
|
|
CREATE TABLE innodb_table1 (m INT, n INT, FOREIGN KEY (n) REFERENCES t0(f1))
|
|
AS SELECT 101 as m, 2 as n;
|
|
CREATE TABLE innodb_table2 as SELECT m, 4 FROM innodb_table1;
|
|
SHOW CREATE TABLE innodb_table1;
|
|
SHOW CREATE TABLE innodb_table2;
|
|
DROP TABLE innodb_table1;
|
|
DROP TABLE innodb_table2;
|
|
|
|
--echo #
|
|
--echo # CASE 3 The behavior of CTAS, with sql_log_bin ON and format STATEMENT.
|
|
--echo #
|
|
|
|
SET sql_log_bin = ON;
|
|
SET @@SESSION.binlog_format=STATEMENT;
|
|
--error ER_NO_REFERENCED_ROW_2
|
|
CREATE TABLE innodb_table1 (m INT, n INT, FOREIGN KEY (n) REFERENCES t0(f1))
|
|
AS SELECT 101 as m, 5 as n;
|
|
|
|
CREATE TABLE innodb_table1 (m INT, n INT, FOREIGN KEY (n) REFERENCES t0(f1))
|
|
AS SELECT 101 as m, 2 as n;
|
|
DROP TABLE innodb_table1;
|
|
|
|
--echo #
|
|
--echo # CASE 4 The behavior of CTAS, with sql_log_bin ON and format MIXED.
|
|
--echo # The behavior would be same as case 3 above.
|
|
|
|
SET @@SESSION.binlog_format=MIXED;
|
|
--error ER_NO_REFERENCED_ROW_2
|
|
CREATE TABLE innodb_table1 (m INT, n INT, FOREIGN KEY (n) REFERENCES t0(f1))
|
|
AS SELECT 101 as m, 5 as n;
|
|
|
|
CREATE TABLE innodb_table1 (m INT, n INT, FOREIGN KEY (n) REFERENCES t0(f1))
|
|
AS SELECT 101 as m, 2 as n;
|
|
DROP TABLE innodb_table1;
|
|
|
|
--echo #
|
|
--echo # CASE 5 The behavior of CTAS, with sql_log_bin ON and format ROW.
|
|
--echo #
|
|
|
|
SET @@SESSION.binlog_format=ROW;
|
|
--error ER_FOREIGN_KEY_WITH_ATOMIC_CREATE_SELECT
|
|
CREATE TABLE innodb_table1 (m INT, n INT, FOREIGN KEY (n) REFERENCES t0(f1))
|
|
AS SELECT 101 as m, 5 as n;
|
|
|
|
--error ER_FOREIGN_KEY_WITH_ATOMIC_CREATE_SELECT
|
|
CREATE TABLE innodb_table1 (m INT, n INT, FOREIGN KEY (n) REFERENCES t0(f1))
|
|
AS SELECT 101 as m, 2 as n;
|
|
|
|
--error ER_FOREIGN_KEY_WITH_ATOMIC_CREATE_SELECT
|
|
CREATE TABLE innodb_table1 (m INT, n INT,
|
|
FOREIGN KEY (n) REFERENCES t0(f1)) START TRANSACTION;
|
|
|
|
--echo #
|
|
--echo # Bug#35553557 Table creation with foreign key assertion error
|
|
--echo #
|
|
|
|
SET sql_log_bin = OFF;
|
|
CREATE TABLE innodb_table1(m INT, FOREIGN KEY (m) REFERENCES t0(f1))
|
|
AS SELECT f1 AS m FROM t0;
|
|
--error ER_ROW_IS_REFERENCED_2
|
|
DELETE FROM t0 WHERE f1 = 1;
|
|
|
|
DROP TABLE innodb_table1;
|
|
|
|
CREATE TABLE self_referencing_table(pk INT PRIMARY KEY, fk INT,
|
|
FOREIGN KEY (fk) REFERENCES self_referencing_table(pk))
|
|
AS SELECT 1 AS pk, 1 as fk;
|
|
|
|
INSERT INTO self_referencing_table VALUES (2,1), (3, NULL), (4,3);
|
|
|
|
--error ER_ROW_IS_REFERENCED_2
|
|
DELETE FROM self_referencing_table WHERE pk=1;
|
|
SELECT pk, fk FROM self_referencing_table ORDER BY pk;
|
|
--error ER_ROW_IS_REFERENCED_2
|
|
DELETE FROM self_referencing_table WHERE pk=3;
|
|
DELETE FROM self_referencing_table WHERE pk=4;
|
|
SELECT pk, fk FROM self_referencing_table ORDER BY pk;
|
|
DELETE FROM self_referencing_table WHERE pk=3;
|
|
SELECT pk, fk FROM self_referencing_table ORDER BY pk;
|
|
|
|
DROP TABLE self_referencing_table;
|
|
SET sql_log_bin = @saved_sql_log_bin;
|
|
DROP TABLE t0;
|