1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
|
#########################################
# ==== 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;
|
