Debian Codegraph @

mysql-test/suite/binlog/t/flashback.test

raw
1 --source include/have_binlog_format_row.inc
2 --source include/have_innodb.inc
3 --source include/test_db_charset_latin1.inc
4
5 --echo #
6 --echo # Preparatory cleanup.
7 --echo #
8 --disable_warnings
9 DROP TABLE IF EXISTS t1;
10 --enable_warnings
11
12 --echo #
13 --echo # We need a fixed timestamp to avoid varying results.
14 --echo #
15 SET timestamp=1000000000;
16
17 --echo # < CASE 1 >
18 --echo # Delete all existing binary logs.
19 --echo #
20 RESET MASTER;
21
22 CREATE TABLE t1 (
23 c01 tinyint,
24 c02 smallint,
25 c03 mediumint,
26 c04 int,
27 c05 bigint,
28 c06 char(10),
29 c07 varchar(20),
30 c08 TEXT
31 ) ENGINE=InnoDB;
32
33 --echo # < CASE 1 >
34 --echo # Insert data to t1
35 --echo #
36 INSERT INTO t1 VALUES(0,0,0,0,0,'','','');
37 INSERT INTO t1 VALUES(1,2,3,4,5, "abc", "abcdefg", "abcedfghijklmnopqrstuvwxyz");
38 INSERT INTO t1 VALUES(127, 32767, 8388607, 2147483647, 9223372036854775807, repeat('a', 10), repeat('a', 20), repeat('a', 255));
39
40
41 --echo # < CASE 1 >
42 --echo # Update t1
43 --echo #
44 UPDATE t1 SET c01=100 WHERE c02=0 OR c03=3;
45
46 --echo # < CASE 1 >
47 --echo # Clear t1
48 --echo #
49 DELETE FROM t1;
50
51 FLUSH LOGS;
52
53 --echo # < CASE 1 >
54 --echo # Show mysqlbinlog result without -B
55 --echo #
56
57 let $MYSQLD_DATADIR= `select @@datadir`;
58 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
59 --replace_regex /SQL_LOAD_MB-[0-9]-[0-9]/SQL_LOAD_MB-#-#/ /exec_time=[0-9]*/exec_time=#/ /end_log_pos [0-9]*/end_log_pos #/ /# at [0-9]*/# at #/ /Xid = [0-9]*/Xid = #/ /thread_id=[0-9]*/thread_id=#/ /table id [0-9]*/table id #/ /mapped to number [0-9]*/mapped to number #/ /server v [^ ]*/server v #.##.##/ /CRC32 0x[0-9a-f]*/CRC32 XXX/ /collation_server=[0-9]+/collation_server=X/ /character_set_client=[a-zA-Z0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ /xid=\d*/xid=<xid>/
60 --exec $MYSQL_BINLOG --base64-output=decode-rows -v -v $MYSQLD_DATADIR/master-bin.000001
61
62 --echo # < CASE 1 >
63 --echo # Show mysqlbinlog result with -B
64 --echo #
65
66 let $MYSQLD_DATADIR= `select @@datadir`;
67 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
68 --replace_regex /SQL_LOAD_MB-[0-9]-[0-9]/SQL_LOAD_MB-#-#/ /exec_time=[0-9]*/exec_time=#/ /end_log_pos [0-9]*/end_log_pos #/ /# at [0-9]*/# at #/ /Xid = [0-9]*/Xid = #/ /thread_id=[0-9]*/thread_id=#/ /table id [0-9]*/table id #/ /mapped to number [0-9]*/mapped to number #/ /server v [^ ]*/server v #.##.##/ /CRC32 0x[0-9a-f]*/CRC32 XXX/ /collation_server=[0-9]+/collation_server=X/ /character_set_client=[a-zA-Z0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ /xid=\d*/xid=<xid>/
69 --exec $MYSQL_BINLOG -B --base64-output=decode-rows -v -v $MYSQLD_DATADIR/master-bin.000001
70
71 --echo # < CASE 1 >
72 --echo # Insert data to t1
73 --echo #
74 TRUNCATE TABLE t1;
75 INSERT INTO t1 VALUES(0,0,0,0,0,'','','');
76 INSERT INTO t1 VALUES(1,2,3,4,5, "abc", "abcdefg", "abcedfghijklmnopqrstuvwxyz");
77 INSERT INTO t1 VALUES(127, 32767, 8388607, 2147483647, 9223372036854775807, repeat('a', 10), repeat('a', 20), repeat('a', 60));
78
79 --echo # < CASE 1 >
80 --echo # Delete all existing binary logs.
81 --echo #
82 RESET MASTER;
83 SELECT * FROM t1;
84
85 --echo # < CASE 1 >
86 --echo # Operate some data
87 --echo #
88
89 UPDATE t1 SET c01=20;
90 UPDATE t1 SET c02=200;
91 UPDATE t1 SET c03=2000;
92
93 DELETE FROM t1;
94
95 FLUSH LOGS;
96
97 --echo # < CASE 1 >
98 --echo # Flashback & Check the result
99 --echo #
100
101 let $MYSQLD_DATADIR= `select @@datadir`;
102 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
103 --exec $MYSQL_BINLOG -vv $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_original_1.sql
104 --exec $MYSQL_BINLOG -B -vv $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_1.sql
105 --exec $MYSQL -e "source $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_1.sql;"
106
107 SELECT * FROM t1;
108
109 RESET MASTER;
110
111 --echo # < CASE 2 >
112 --echo # UPDATE multi-rows in one event
113 --echo #
114
115 BEGIN;
116 UPDATE t1 SET c01=10 WHERE c01=0;
117 UPDATE t1 SET c01=20 WHERE c01=10;
118 COMMIT;
119
120 FLUSH LOGS;
121
122 --echo # < CASE 2 >
123 --echo # Flashback & Check the result
124 --echo #
125
126 let $MYSQLD_DATADIR= `select @@datadir`;
127 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
128 --exec $MYSQL_BINLOG -vv $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_original_2.sql
129 --exec $MYSQL_BINLOG -B -vv $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_2.sql
130 --exec $MYSQL -e "source $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_2.sql;"
131
132 SELECT * FROM t1;
133
134 DROP TABLE t1;
135
136 --echo # < CASE 3 >
137 --echo # Self-referencing foreign keys
138 --echo #
139
140 CREATE TABLE t1 (a INT PRIMARY KEY, b INT, FOREIGN KEY my_fk(b) REFERENCES t1(a)) ENGINE=InnoDB;
141
142 BEGIN;
143 INSERT INTO t1 VALUES (1, NULL);
144 INSERT INTO t1 VALUES (2, 1), (3, 2), (4, 3);
145 COMMIT;
146
147 SELECT * FROM t1;
148
149 # New binlog
150 RESET MASTER;
151
152 DELETE FROM t1 ORDER BY a DESC;
153
154 FLUSH LOGS;
155
156 --echo # < CASE 3 >
157 --echo # Flashback & Check the result
158 --echo #
159
160 let $MYSQLD_DATADIR= `select @@datadir`;
161 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
162 --exec $MYSQL_BINLOG -vv $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_original_3.sql
163 --exec $MYSQL_BINLOG -B -vv $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_3.sql
164 --exec $MYSQL -e "source $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_3.sql;"
165
166 SELECT * FROM t1;
167
168 DROP TABLE t1;
169
170 --echo # < CASE 4 >
171 --echo # Trigger
172 --echo #
173
174 CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
175 CREATE TABLE t2 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
176
177 BEGIN;
178 INSERT INTO t1 VALUES (1, NULL);
179 INSERT INTO t1 VALUES (2, 1), (3, 2), (4, 3);
180 INSERT INTO t2 VALUES (6, 7), (7, 8), (8, 9);
181 COMMIT;
182
183 SELECT * FROM t1;
184 SELECT * FROM t2;
185
186 CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW DELETE FROM t2 WHERE a = NEW.b;
187
188 # New binlog
189 RESET MASTER;
190
191 INSERT INTO t1 VALUES (5, 6), (7, 8);
192
193 SELECT * FROM t1;
194 SELECT * FROM t2;
195
196 FLUSH LOGS;
197
198 --echo # < CASE 4 >
199 --echo # Flashback & Check the result
200 --echo #
201
202 let $MYSQLD_DATADIR= `select @@datadir`;
203 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
204 --exec $MYSQL_BINLOG -vv $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_original_4.sql
205 --exec $MYSQL_BINLOG -B $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_4.sql
206 --exec $MYSQL -e "source $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_4.sql;"
207
208 SELECT * FROM t1;
209 SELECT * FROM t2;
210
211 DROP TRIGGER trg1;
212 DROP TABLE t1;
213 DROP TABLE t2;
214
215 --echo # < CASE 5 >
216 --echo # REPLCAE Queries
217 --echo #
218
219 CREATE TABLE t1 (a INT PRIMARY KEY, b INT, UNIQUE uk(b)) ENGINE=InnoDB;
220
221 BEGIN;
222 INSERT INTO t1 VALUES (1, NULL);
223 INSERT INTO t1 VALUES (2, 1), (3, 2), (4, 3);
224 INSERT INTO t1 VALUES (5, 4), (6, 5), (7, 6);
225 COMMIT;
226
227 SELECT * FROM t1;
228
229 # New binlog
230 RESET MASTER;
231
232 REPLACE INTO t1 VALUES (3, 100);
233 REPLACE INTO t1 SET a=4, b=200;
234
235 SELECT * FROM t1;
236
237 REPLACE INTO t1 VALUES (5,5);
238
239 SELECT * FROM t1;
240
241 FLUSH LOGS;
242
243 --echo # < CASE 5 >
244 --echo # Flashback & Check the result
245 --echo #
246
247 let $MYSQLD_DATADIR= `select @@datadir`;
248 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
249 --exec $MYSQL_BINLOG -vv $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_original_5.sql
250 --exec $MYSQL_BINLOG -B $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_5.sql
251 --exec $MYSQL -e "source $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_5.sql;"
252
253 SELECT * FROM t1;
254
255 DROP TABLE t1;
256
257
258 --echo # < CASE 6 >
259 --echo # Test Case from MDEV-21067
260 --echo #
261
262 # Init Structure
263 CREATE DATABASE world;
264 CREATE TABLE world.city (
265 ID INT AUTO_INCREMENT PRIMARY KEY,
266 Name VARCHAR(64),
267 CountryCode VARCHAR(64),
268 District VARCHAR(64),
269 Population INT
270 ) ENGINE=InnoDB;
271 CREATE TABLE test.test (
272 ID INT AUTO_INCREMENT PRIMARY KEY,
273 REC VARCHAR(64),
274 ts TIMESTAMP
275 ) ENGINE=InnoDB;
276
277 INSERT INTO world.city VALUES (NULL, 'Davenport', 'USA', 'Iowa', 100);
278 INSERT INTO world.city VALUES (NULL, 'Boulder', 'USA', 'Colorado', 1000);
279 INSERT INTO world.city VALUES (NULL, 'Gweru', 'ZWE', 'Midlands', 10000);
280
281 RESET MASTER;
282
283 CHECKSUM TABLE world.city;
284
285 # Insert test data
286 INSERT INTO test.test VALUES (NULL, 'Good record 1', CURRENT_TIMESTAMP());
287
288 INSERT INTO world.city VALUES (NULL, 'Wrong value 1', '000', 'Wrong', 0);
289 INSERT INTO world.city VALUES (NULL, 'Wrong value 2', '000', 'Wrong', 0) , (NULL, 'Wrong value 3', '000', 'Wrong', 0);
290
291 INSERT INTO test.test VALUES (NULL, 'Good record 2', CURRENT_TIMESTAMP());
292
293 UPDATE world.city SET Population = 99999999 WHERE ID IN (1, 2, 3);
294
295 INSERT INTO test.test VALUES (NULL, 'Good record 3', CURRENT_TIMESTAMP());
296
297 DELETE FROM world.city WHERE ID BETWEEN 1 AND 2;
298
299 INSERT INTO test.test VALUES (NULL, 'Good record 5', CURRENT_TIMESTAMP());
300
301 REPLACE INTO world.city VALUES (4074, 'Wrong value 4', '000', 'Wrong', 0);
302 REPLACE INTO world.city VALUES (4078, 'Wrong value 5', '000', 'Wrong', 0), (NULL, 'Wrong value 6', '000', 'Wrong', 0);
303
304 INSERT INTO test.test VALUES (NULL, 'Good record 6', CURRENT_TIMESTAMP());
305
306 INSERT INTO world.city
307 SELECT NULL, Name, CountryCode, District, Population FROM world.city WHERE ID BETWEEN 2 AND 10;
308
309 INSERT INTO test.test VALUES (NULL, 'Good record 7', CURRENT_TIMESTAMP());
310
311 INSERT INTO test.test VALUES (NULL, 'Good record 8', CURRENT_TIMESTAMP());
312
313 DELETE FROM world.city;
314
315 INSERT INTO test.test VALUES (NULL, 'Good record 9', CURRENT_TIMESTAMP());
316
317 FLUSH LOGS;
318
319 --echo # < CASE 6 >
320 --echo # Flashback & Check the result
321 --echo #
322
323 let $MYSQLD_DATADIR= `select @@datadir`;
324 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
325 --exec $MYSQL_BINLOG --database=world --table=city -vv $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_original_6.sql
326 --exec $MYSQL_BINLOG --database=world --table=city -B $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_6.sql
327 --exec $MYSQL -e "source $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_6.sql;"
328
329 SELECT * FROM world.city;
330
331 SELECT * FROM test.test;
332
333 CHECKSUM TABLE world.city;
334
335 DROP TABLE test.test;
336 DROP TABLE world.city;
337 DROP DATABASE world;
338
339 --echo # < CASE 7 >
340 --echo # Test Case for MDEV-17260
341 --echo #
342
343 RESET MASTER;
344
345 CREATE TABLE t1 ( f INT PRIMARY KEY ) ENGINE=innodb;
346 INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6);
347 --echo # 6- Rows must be present
348 SELECT COUNT(*) FROM t1;
349 FLUSH LOGS;
350 DELETE FROM t1;
351 FLUSH LOGS;
352
353 --echo # 0- Rows must be present
354 --let $assert_cond= COUNT(*) = 0 FROM t1
355 --let $assert_text= Table t1 should have 0 rows.
356 --source include/assert.inc
357
358 --exec $MYSQL_BINLOG -vv -B --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000002> $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_7.sql
359 --exec $MYSQL -e "source $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_7.sql;"
360
361 --echo # 6- Rows must be present upon restoring from flashback
362 --let $assert_cond= COUNT(*) = 6 FROM t1
363 --let $assert_text= Table t1 should have six rows.
364 --source include/assert.inc
365
366 DROP TABLE t1;
367
368 --echo #
369 --echo # MDEV-30698 Cover missing test cases for mariadb-binlog options
370 --echo # --raw [and] --flashback
371 --echo #
372
373 --error 1 # --raw mode and --flashback mode are not allowed
374 --exec $MYSQL_BINLOG -vv -B --raw --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000003> $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_8.sql
375
376 --echo #
377 --echo # < CASE 8 >
378 --echo # Verify flashback works well for binlog_row_image full_nodup mode
379 --echo #
380 CREATE TABLE t1 (
381 c01 TINYINT PRIMARY KEY,
382 c02 SMALLINT,
383 c03 MEDIUMINT,
384 c04 INT,
385 c05 BIGINT,
386 c06 CHAR(10),
387 c07 VARCHAR(20),
388 c08 TEXT,
389 c09 ENUM('one', 'two', 'three', 'four', 'five', 'six', 'seven', 'eight'),
390 c10 SET('black', 'white', 'red', 'yellow'),
391 c11 TIMESTAMP(3),
392 c12 DATETIME(3)
393 ) ENGINE = InnoDB;
394
395 INSERT INTO t1 VALUES (1, 1, 1, 1, 1, 'A', 'A', 'A', 'one', 'black',
396 '2023-11-26 10:00:00.123', '2023-11-26 10:00:00');
397 INSERT INTO t1 VALUES (2, 1, 1, 1, 1, 'A', 'A', 'A', 'one', 'black',
398 '2023-11-26 10:00:00.123', '2023-11-26 10:00:00');
399 INSERT INTO t1 VALUES (3, 1, NULL, 1, 1, 'A', 'A', 'A', 'one', 'black',
400 '2023-11-26 10:00:00.123', NULL);
401 INSERT INTO t1 VALUES (4, 1, NULL, 1, 1, 'A', 'A', 'A', 'one', 'black',
402 '2023-11-26 10:00:00.123', NULL);
403
404 --let $checksum_old= `CHECKSUM TABLE t1`
405
406 FLUSH BINARY LOGS;
407 --echo # The update includes the cases that
408 --echo # Value -> Value
409 --echo # Value -> NULL
410 --echo # NULL -> value
411 --echo # and the changed null bits in both first and second null byte
412
413 UPDATE t1 SET c02 = NULL, c03 = 2, c09 = 'two',
414 c10 = NULL, c12 = '2023-11-26 11:00:00';
415 --let $master_file= query_get_value("SHOW MASTER STATUS", File, 1)
416 --let $MYSQLD_DATADIR= `select @@datadir`
417 FLUSH BINARY LOGS;
418
419 --echo #
420 --echo # Data before flashback
421 --echo #
422 SELECT * FROM t1;
423
424 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
425 --exec $MYSQL_BINLOG -B -vv $MYSQLD_DATADIR/$master_file > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback.sql
426 --exec $MYSQL -e "SET binlog_format= ROW; source $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback.sql;"
427
428 --echo #
429 --echo # Data after flashback
430 --echo #
431 SELECT * FROM t1;
432
433 # After flashback, t1's checksum should be same to original checksum
434 --let $checksum_new = `CHECKSUM TABLE t1`
435 if ($checksum_new != $checksum_old)
436 {
437 --die "After flashback, t1's checksum is different from the original checksum"
438 }
439
440 DROP TABLE t1;
441 --remove_file $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback.sql
442
443 ## Clear
444 SET binlog_format=statement;
445 --error ER_FLASHBACK_NOT_SUPPORTED
446 SET GLOBAL binlog_format=statement;
447
448 --source include/test_db_charset_restore.inc