Debian Codegraph @

mysql-test/suite/rpl/t/rpl_create_select_replay.test

raw
1 #########################################
2 # ==== Purpose ====
3 #
4 # Test binlog replaying binlogs for CREATE TABLE ... AS SELECT (DDL_CTAS)
5 # from the output of mysqlbinlog tool.
6 #
7 # ==== Requirements ====
8 #
9 # R1 Test binlog replay for DDL_CTAS.
10 #
11 # R2 Test crash during binlog replay for DDL_CTAS causes transaction recovery.
12 #
13 # R3 Replay DDL_CTAS with rows and then force rollback.
14 #
15 # R4 Repeat R2 with DDL_CTAS executed from within procedure.
16 #
17 # R5 Repeat R2 with DDL_CTAS executed using prepared statement.
18 #
19 # ==== Implementation ====
20 #
21 # TC1: Replay DDL_CTAS without rows and also crash before commit.
22 # 1) Create table t0 and Execute DDL_CTAS.
23 # 2) Run mysqlbinlog tool and generate mysqlbinlog.sql.
24 # 3) Drop table t0 and t1;
25 # 4) Replay the binlogs in mysqlbinlog.sql.
26 # 5) Verify that we have both t0 and t1 tables created.
27 # 6) Drop table t0 and t1 again.
28 # 7) Enable debug point to induce crash before commit.
29 # 8) Replay the binlogs in mysqlbinlog.sql, which causes crash.
30 # 9) Wait until server stops completely and then restart the server.
31 # 10) Verify that table t0 exists and t1 does not exists.
32 # 11) Drop the table t0.
33 # 12) And reset debug flag.
34 #
35 # TC2: Replay DDL_CTAS with rows and also crash before commit.
36 # Repeat steps from TC1 with rows inserted in t0.
37 #
38 # TC3: Replay DDL_CTAS with rows and then force rollback.
39 # Repeat steps from TC1 with rows inserted in t0. Verify that we have
40 # only t0 after rollback.
41 #
42 # TC4: Replay DDL_CTAS without rows and also crash before commit.
43 # Repeat steps from TC1 with a stored procedure executing DDL_CTAS.
44 #
45 # TC5: Replay DDL_CTAS with rows and also crash before commit.
46 # Repeat steps from TC1 with few rows inserted in t0 and a stored procedure
47 # executing DDL_CTAS.
48 #
49 # TC6: Replay DDL_CTAS with rows and also crash before commit.
50 # Repeat steps from TC1 with few rows inserted in t0 and a stored procedure
51 # executing DDL_CTAS.
52 #
53 # ==== References ====
54 #
55 # WL#13355 Make CREATE TABLE...SELECT atomic and crash-safe
56 #
57
58 --source include/not_group_replication_plugin.inc
59 --source include/have_binlog_format_row.inc
60 --source include/have_debug.inc
61 --source include/not_valgrind.inc # Test takes too much time in Valgrind
62
63 # Note: DDL_CTAS -> CREATE TABLE ... SELECT;
64
65 # SETUP VARIABLES.
66
67 --let $MYSQLD_DATADIR= `select @@datadir`
68 --let $MYSQLBINLOG_OUTFILE=$MYSQLTEST_VARDIR/tmp/wl13355_mysqlbinlog.sql
69 --let $mysqlbinlog_parameters= --result-file=$MYSQLBINLOG_OUTFILE --local-load=$MYSQLTEST_VARDIR/tmp/ $MYSQLD_DATADIR/master-bin.000001
70 --let $WAIT_COUNT=6000
71 RESET MASTER;
72
73 --echo #
74 --echo # Case 1: Replay DDL_CTAS without rows, crash before commit;
75 --echo #
76
77 CREATE TABLE t0 (f1 INT);
78 CREATE TABLE t1 AS SELECT * FROM t0;
79
80 --echo # Dump the binlog events into a file.
81 --source include/mysqlbinlog.inc
82
83 --echo # Replay without crash.
84 DROP TABLE t0,t1;
85 RESET MASTER;
86 --exec $MYSQL test < $MYSQLBINLOG_OUTFILE
87
88 --let $assert_text= Verify two tables (t0 & t1) are present in test database
89 --let $assert_cond= "[SELECT count(table_name) COUNT FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = \'test\', COUNT, 1]" = "2"
90 --source include/assert.inc
91
92 --let $assert_text= Verify test.t1 is synced
93 --let $assert_cond= "[SELECT count(*) COUNT FROM test.t1, COUNT, 1]" = "0"
94 --source include/assert.inc
95
96 DROP TABLE t0,t1;
97 RESET MASTER;
98
99 --echo # Replay the binlog events and cause crash before commit;
100 SET GLOBAL DEBUG='+d, crash_on_transactional_ddl_commit';
101 --exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
102 --error 1
103 --exec $MYSQL test < $MYSQLBINLOG_OUTFILE
104
105 --echo # Recover the server.
106 --source include/wait_until_disconnected.inc
107 --exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
108 --enable_reconnect
109 --source include/wait_time_until_connected_again.inc
110
111 --let $assert_text= Verify that only table t0 is present in test database
112 --let $assert_cond= "[SELECT count(table_name) COUNT FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = \'test\', COUNT, 1]" = "1"
113 --source include/assert.inc
114
115 DROP TABLE t0;
116 RESET MASTER;
117 SET global DEBUG='-d, crash_on_transactional_ddl_commit';
118 --remove_file $MYSQLBINLOG_OUTFILE
119 RESET MASTER;
120
121 --echo #
122 --echo # Case 2: Replay DDL_CTAS with rows, crash before commit;
123 --echo #
124
125 CREATE TABLE t0 (f1 INT);
126 INSERT INTO t0 VALUES (1);
127 INSERT INTO t0 VALUES (2);
128 INSERT INTO t0 VALUES (3);
129 INSERT INTO t0 VALUES (4);
130 CREATE TABLE t1 AS SELECT * FROM t0;
131
132 --echo # Dump the binlog events into a file.
133 --source include/mysqlbinlog.inc
134
135 --echo # Replay without crash.
136 DROP TABLE t0,t1;
137 RESET MASTER;
138 --exec $MYSQL test < $MYSQLBINLOG_OUTFILE
139
140 --let $assert_text= Verify two tables (t0 & t1) are present in test database
141 --let $assert_cond= "[SELECT count(table_name) COUNT FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = \'test\', COUNT, 1]" = "2"
142 --source include/assert.inc
143
144 --let $assert_text= Verify test.t1 is synced
145 --let $assert_cond= "[SELECT count(*) COUNT FROM test.t1, COUNT, 1]" = "4"
146 --source include/assert.inc
147
148 DROP TABLE t0,t1;
149 RESET MASTER;
150
151 --echo # Replay the binlog events, cause crash before commit and restart;
152 SET GLOBAL DEBUG='+d, crash_on_transactional_ddl_commit';
153 --exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
154 --error 1
155 --exec $MYSQL test < $MYSQLBINLOG_OUTFILE
156
157 --echo # Recover the server.
158 --source include/wait_until_disconnected.inc
159 --exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
160 --enable_reconnect
161 --source include/wait_time_until_connected_again.inc
162 SET global DEBUG='-d, crash_on_transactional_ddl_commit';
163
164 --let $assert_text= Verify that only table t0 is present in test database
165 --let $assert_cond= "[SELECT count(table_name) COUNT FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = \'test\', COUNT, 1]" = "1"
166 --source include/assert.inc
167
168 DROP TABLE t0;
169
170 --echo #
171 --echo # Case 3: Replay DDL_CTAS with rows and then force rollback.
172 --echo #
173 RESET MASTER;
174
175 SET GLOBAL DEBUG='+d, force_rollback_in_replica_on_transactional_ddl_commit';
176 --exec $MYSQL test < $MYSQLBINLOG_OUTFILE
177 --remove_file $MYSQLBINLOG_OUTFILE
178 SET global DEBUG='-d, force_rollback_in_replica_on_transactional_ddl_commit';
179
180 --let $assert_text= Verify that only table t0 is present in test database
181 --let $assert_cond= "[SELECT count(table_name) COUNT FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = \'test\', COUNT, 1]" = "1"
182 --source include/assert.inc
183
184 DROP TABLE t0;
185 RESET MASTER;
186
187 --echo #
188 --echo # Case 4: Replay DDL_CTAS in a SP without rows, crash before commit;
189 --echo #
190
191 CREATE TABLE t0 (f1 INT);
192 DELIMITER |;
193 CREATE PROCEDURE proc1()
194 BEGIN
195 CREATE TABLE t1 AS SELECT * FROM t0;
196 END|
197 DELIMITER ;|
198 CALL proc1();
199
200 --echo # Dump the binlog events into a file.
201 --source include/mysqlbinlog.inc
202
203 --echo # Replay without crash.
204 DROP TABLE t0,t1;
205 DROP PROCEDURE proc1;
206 RESET MASTER;
207 --exec $MYSQL test < $MYSQLBINLOG_OUTFILE
208
209 --let $assert_text= Verify two tables (t0 & t1) are present in test database
210 --let $assert_cond= "[SELECT count(table_name) COUNT FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = \'test\', COUNT, 1]" = "2"
211 --source include/assert.inc
212
213 --echo # Drop existing objects.
214 DROP TABLE t0,t1;
215 DROP PROCEDURE proc1;
216 RESET MASTER;
217
218 --echo # Replay the binlog events and cause crash.
219 SET GLOBAL DEBUG='+d, crash_on_transactional_ddl_commit';
220 --exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
221 --error 1
222 --exec $MYSQL test < $MYSQLBINLOG_OUTFILE
223
224 --echo # Recover the server.
225 --source include/wait_until_disconnected.inc
226 --exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
227 --enable_reconnect
228 --source include/wait_time_until_connected_again.inc
229
230 --let $assert_text= Verify that only table t0 is present in test database
231 --let $assert_cond= "[SELECT count(table_name) COUNT FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = \'test\', COUNT, 1]" = "1"
232 --source include/assert.inc
233
234 DROP TABLE t0;
235 DROP PROCEDURE proc1;
236 RESET MASTER;
237
238 SET global DEBUG='-d, crash_on_transactional_ddl_commit';
239 --remove_file $MYSQLBINLOG_OUTFILE
240 RESET MASTER;
241
242 --echo #
243 --echo # Case 5: Replay DDL_CTAS in a SP with rows, crash before commit;
244 --echo #
245
246 CREATE TABLE t0 (f1 INT);
247 INSERT INTO t0 VALUES (1);
248 INSERT INTO t0 VALUES (2);
249 INSERT INTO t0 VALUES (3);
250 INSERT INTO t0 VALUES (4);
251 DELIMITER |;
252 CREATE PROCEDURE proc1()
253 BEGIN
254 CREATE TABLE t1 AS SELECT * FROM t0;
255 END|
256 DELIMITER ;|
257 CALL proc1();
258
259 --echo # Dump the binlog events into a file.
260 --source include/mysqlbinlog.inc
261
262 --echo # Replay without crash.
263 DROP TABLE t0,t1;
264 DROP PROCEDURE proc1;
265 RESET MASTER;
266 --exec $MYSQL test < $MYSQLBINLOG_OUTFILE
267
268 --let $assert_text= Verify two tables (t0 & t1) are present in test database
269 --let $assert_cond= "[SELECT count(table_name) COUNT FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = \'test\', COUNT, 1]" = "2"
270 --source include/assert.inc
271
272 --let $assert_text= Verify test.t1 is synced
273 --let $assert_cond= "[SELECT count(*) COUNT FROM test.t1, COUNT, 1]" = "4"
274 --source include/assert.inc
275
276 --echo # Drop existing objects.
277 DROP TABLE t0,t1;
278 DROP PROCEDURE proc1;
279 RESET MASTER;
280
281 --echo # Replay the binlog events and cause crash.
282 SET GLOBAL DEBUG='+d, crash_on_transactional_ddl_commit';
283 --exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
284 --error 1
285 --exec $MYSQL test < $MYSQLBINLOG_OUTFILE
286
287 --echo # Recover the server.
288 --source include/wait_until_disconnected.inc
289 --exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
290 --enable_reconnect
291 --source include/wait_time_until_connected_again.inc
292
293 --let $assert_text= Verify that only table t0 is present in test database
294 --let $assert_cond= "[SELECT count(table_name) COUNT FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = \'test\', COUNT, 1]" = "1"
295 --source include/assert.inc
296
297 DROP TABLE t0;
298 DROP PROCEDURE proc1;
299 RESET MASTER;
300
301 SET global DEBUG='-d, crash_on_transactional_ddl_commit';
302 --remove_file $MYSQLBINLOG_OUTFILE
303 RESET MASTER;
304
305 --echo #
306 --echo # Case 6: Replay DDL_CTAS using PS with rows, crash before commit;
307 --echo #
308
309 CREATE TABLE t0 (f1 INT);
310 INSERT INTO t0 VALUES (1);
311 INSERT INTO t0 VALUES (2);
312 INSERT INTO t0 VALUES (3);
313 INSERT INTO t0 VALUES (4);
314
315 PREPARE stmt FROM "CREATE TABLE t1 AS SELECT * FROM t0";
316 EXECUTE stmt;
317 DEALLOCATE PREPARE stmt;
318
319 --echo # Dump the binlog events into a file.
320 --source include/mysqlbinlog.inc
321
322 --echo # Replay without crash.
323 DROP TABLE t0,t1;
324 RESET MASTER;
325 --exec $MYSQL test < $MYSQLBINLOG_OUTFILE
326
327 --let $assert_text= Verify two tables (t0 & t1) are present in test database
328 --let $assert_cond= "[SELECT count(table_name) COUNT FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = \'test\', COUNT, 1]" = "2"
329 --source include/assert.inc
330
331 --let $assert_text= Verify test.t1 is synced
332 --let $assert_cond= "[SELECT count(*) COUNT FROM test.t1, COUNT, 1]" = "4"
333 --source include/assert.inc
334
335 --echo # Drop existing objects.
336 DROP TABLE t0,t1;
337 RESET MASTER;
338 SET GLOBAL DEBUG='+d, crash_on_transactional_ddl_commit';
339 --exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
340 --echo # Replay the binlog events and cause crash.
341 --error 1
342 --exec $MYSQL test < $MYSQLBINLOG_OUTFILE
343
344 --echo # Recover the server.
345 --source include/wait_until_disconnected.inc
346 --exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
347 --enable_reconnect
348 --source include/wait_time_until_connected_again.inc
349
350 --let $assert_text= Verify that only table t0 is present in test database
351 --let $assert_cond= "[SELECT count(table_name) COUNT FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = \'test\', COUNT, 1]" = "1"
352 --source include/assert.inc
353
354 DROP TABLE t0;
355 --remove_file $MYSQLBINLOG_OUTFILE
356 RESET MASTER;