1--source include/have_log_bin.inc 2--source include/have_innodb.inc 3 4--echo # 5--echo # Preparatory cleanup. 6--echo # 7--disable_warnings 8DROP TABLE IF EXISTS t1; 9--enable_warnings 10 11--echo # 12--echo # We need a fixed timestamp to avoid varying results. 13--echo # 14SET timestamp=1000000000; 15 16--echo # < CASE 1 > 17--echo # Delete all existing binary logs. 18--echo # 19RESET MASTER; 20 21CREATE TABLE t1 ( 22 c01 tinyint, 23 c02 smallint, 24 c03 mediumint, 25 c04 int, 26 c05 bigint, 27 c06 char(10), 28 c07 varchar(20), 29 c08 TEXT 30) ENGINE=InnoDB; 31 32--echo # < CASE 1 > 33--echo # Insert data to t1 34--echo # 35INSERT INTO t1 VALUES(0,0,0,0,0,'','',''); 36INSERT INTO t1 VALUES(1,2,3,4,5, "abc", "abcdefg", "abcedfghijklmnopqrstuvwxyz"); 37INSERT INTO t1 VALUES(127, 32767, 8388607, 2147483647, 9223372036854775807, repeat('a', 10), repeat('a', 20), repeat('a', 255)); 38 39 40--echo # < CASE 1 > 41--echo # Update t1 42--echo # 43UPDATE t1 SET c01=100 WHERE c02=0 OR c03=3; 44 45--echo # < CASE 1 > 46--echo # Clear t1 47--echo # 48DELETE FROM t1; 49 50FLUSH LOGS; 51 52--echo # < CASE 1 > 53--echo # Show mysqlbinlog result without -B 54--echo # 55 56let $MYSQLD_DATADIR= `select @@datadir`; 57--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 58--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=[0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ 59--exec $MYSQL_BINLOG --base64-output=decode-rows -v -v $MYSQLD_DATADIR/master-bin.000001 60 61--echo # < CASE 1 > 62--echo # Show mysqlbinlog result with -B 63--echo # 64 65let $MYSQLD_DATADIR= `select @@datadir`; 66--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 67--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=[0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ 68--exec $MYSQL_BINLOG -B --base64-output=decode-rows -v -v $MYSQLD_DATADIR/master-bin.000001 69 70--echo # < CASE 1 > 71--echo # Insert data to t1 72--echo # 73TRUNCATE TABLE t1; 74INSERT INTO t1 VALUES(0,0,0,0,0,'','',''); 75INSERT INTO t1 VALUES(1,2,3,4,5, "abc", "abcdefg", "abcedfghijklmnopqrstuvwxyz"); 76INSERT INTO t1 VALUES(127, 32767, 8388607, 2147483647, 9223372036854775807, repeat('a', 10), repeat('a', 20), repeat('a', 60)); 77 78--echo # < CASE 1 > 79--echo # Delete all existing binary logs. 80--echo # 81RESET MASTER; 82SELECT * FROM t1; 83 84--echo # < CASE 1 > 85--echo # Operate some data 86--echo # 87 88UPDATE t1 SET c01=20; 89UPDATE t1 SET c02=200; 90UPDATE t1 SET c03=2000; 91 92DELETE FROM t1; 93 94FLUSH LOGS; 95 96--echo # < CASE 1 > 97--echo # Flashback & Check the result 98--echo # 99 100let $MYSQLD_DATADIR= `select @@datadir`; 101--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 102--exec $MYSQL_BINLOG -vv $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_original_1.sql 103--exec $MYSQL_BINLOG -B -vv $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_1.sql 104--exec $MYSQL -e "SET binlog_format= ROW; source $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_1.sql;" 105 106SELECT * FROM t1; 107 108RESET MASTER; 109 110--echo # < CASE 2 > 111--echo # UPDATE multi-rows in one event 112--echo # 113 114BEGIN; 115UPDATE t1 SET c01=10 WHERE c01=0; 116UPDATE t1 SET c01=20 WHERE c01=10; 117COMMIT; 118 119FLUSH LOGS; 120 121--echo # < CASE 2 > 122--echo # Flashback & Check the result 123--echo # 124 125let $MYSQLD_DATADIR= `select @@datadir`; 126--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 127--exec $MYSQL_BINLOG -vv $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_original_2.sql 128--exec $MYSQL_BINLOG -B -vv $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_2.sql 129--exec $MYSQL -e "SET binlog_format= ROW; source $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_2.sql;" 130 131SELECT * FROM t1; 132 133DROP TABLE t1; 134 135--echo # < CASE 3 > 136--echo # Self-referencing foreign keys 137--echo # 138 139CREATE TABLE t1 (a INT PRIMARY KEY, b INT, FOREIGN KEY my_fk(b) REFERENCES t1(a)) ENGINE=InnoDB; 140 141BEGIN; 142INSERT INTO t1 VALUES (1, NULL); 143INSERT INTO t1 VALUES (2, 1), (3, 2), (4, 3); 144COMMIT; 145 146SELECT * FROM t1; 147 148# New binlog 149RESET MASTER; 150 151DELETE FROM t1 ORDER BY a DESC; 152 153FLUSH LOGS; 154 155--echo # < CASE 3 > 156--echo # Flashback & Check the result 157--echo # 158 159let $MYSQLD_DATADIR= `select @@datadir`; 160--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 161--exec $MYSQL_BINLOG -vv $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_original_3.sql 162--exec $MYSQL_BINLOG -B -vv $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_3.sql 163--exec $MYSQL -e "SET binlog_format= ROW; source $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_3.sql;" 164 165SELECT * FROM t1; 166 167DROP TABLE t1; 168 169--echo # < CASE 4 > 170--echo # Trigger 171--echo # 172 173CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB; 174CREATE TABLE t2 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB; 175 176BEGIN; 177INSERT INTO t1 VALUES (1, NULL); 178INSERT INTO t1 VALUES (2, 1), (3, 2), (4, 3); 179INSERT INTO t2 VALUES (6, 7), (7, 8), (8, 9); 180COMMIT; 181 182SELECT * FROM t1; 183SELECT * FROM t2; 184 185CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW DELETE FROM t2 WHERE a = NEW.b; 186 187# New binlog 188RESET MASTER; 189 190INSERT INTO t1 VALUES (5, 6), (7, 8); 191 192SELECT * FROM t1; 193SELECT * FROM t2; 194 195FLUSH LOGS; 196 197--echo # < CASE 4 > 198--echo # Flashback & Check the result 199--echo # 200 201let $MYSQLD_DATADIR= `select @@datadir`; 202--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 203--exec $MYSQL_BINLOG -vv $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_original_4.sql 204--exec $MYSQL_BINLOG -B $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_4.sql 205--exec $MYSQL -e "SET binlog_format= ROW; source $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_4.sql;" 206 207SELECT * FROM t1; 208SELECT * FROM t2; 209 210DROP TRIGGER trg1; 211DROP TABLE t1; 212DROP TABLE t2; 213 214--echo # < CASE 5 > 215--echo # REPLCAE Queries 216--echo # 217 218CREATE TABLE t1 (a INT PRIMARY KEY, b INT, UNIQUE uk(b)) ENGINE=InnoDB; 219 220BEGIN; 221INSERT INTO t1 VALUES (1, NULL); 222INSERT INTO t1 VALUES (2, 1), (3, 2), (4, 3); 223INSERT INTO t1 VALUES (5, 4), (6, 5), (7, 6); 224COMMIT; 225 226SELECT * FROM t1; 227 228# New binlog 229RESET MASTER; 230 231REPLACE INTO t1 VALUES (3, 100); 232REPLACE INTO t1 SET a=4, b=200; 233 234SELECT * FROM t1; 235 236REPLACE INTO t1 VALUES (5,5); 237 238SELECT * FROM t1; 239 240FLUSH LOGS; 241 242--echo # < CASE 5 > 243--echo # Flashback & Check the result 244--echo # 245 246let $MYSQLD_DATADIR= `select @@datadir`; 247--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 248--exec $MYSQL_BINLOG -vv $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_original_5.sql 249--exec $MYSQL_BINLOG -B $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_5.sql 250--exec $MYSQL -e "SET binlog_format= ROW; source $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_5.sql;" 251 252SELECT * FROM t1; 253 254DROP TABLE t1; 255 256 257--echo # < CASE 6 > 258--echo # Test Case from MDEV-21067 259--echo # 260 261# Init Structure 262CREATE DATABASE world; 263CREATE TABLE world.city ( 264 ID INT AUTO_INCREMENT PRIMARY KEY, 265 Name VARCHAR(64), 266 CountryCode VARCHAR(64), 267 District VARCHAR(64), 268 Population INT 269) ENGINE=InnoDB; 270CREATE TABLE test.test ( 271 ID INT AUTO_INCREMENT PRIMARY KEY, 272 REC VARCHAR(64), 273 ts TIMESTAMP 274) ENGINE=InnoDB; 275 276INSERT INTO world.city VALUES (NULL, 'Davenport', 'USA', 'Iowa', 100); 277INSERT INTO world.city VALUES (NULL, 'Boulder', 'USA', 'Colorado', 1000); 278INSERT INTO world.city VALUES (NULL, 'Gweru', 'ZWE', 'Midlands', 10000); 279 280RESET MASTER; 281 282CHECKSUM TABLE world.city; 283 284# Insert test data 285INSERT INTO test.test VALUES (NULL, 'Good record 1', CURRENT_TIMESTAMP()); 286 287INSERT INTO world.city VALUES (NULL, 'Wrong value 1', '000', 'Wrong', 0); 288INSERT INTO world.city VALUES (NULL, 'Wrong value 2', '000', 'Wrong', 0) , (NULL, 'Wrong value 3', '000', 'Wrong', 0); 289 290INSERT INTO test.test VALUES (NULL, 'Good record 2', CURRENT_TIMESTAMP()); 291 292UPDATE world.city SET Population = 99999999 WHERE ID IN (1, 2, 3); 293 294INSERT INTO test.test VALUES (NULL, 'Good record 3', CURRENT_TIMESTAMP()); 295 296DELETE FROM world.city WHERE ID BETWEEN 1 AND 2; 297 298INSERT INTO test.test VALUES (NULL, 'Good record 5', CURRENT_TIMESTAMP()); 299 300REPLACE INTO world.city VALUES (4074, 'Wrong value 4', '000', 'Wrong', 0); 301REPLACE INTO world.city VALUES (4078, 'Wrong value 5', '000', 'Wrong', 0), (NULL, 'Wrong value 6', '000', 'Wrong', 0); 302 303INSERT INTO test.test VALUES (NULL, 'Good record 6', CURRENT_TIMESTAMP()); 304 305INSERT INTO world.city 306SELECT NULL, Name, CountryCode, District, Population FROM world.city WHERE ID BETWEEN 2 AND 10; 307 308INSERT INTO test.test VALUES (NULL, 'Good record 7', CURRENT_TIMESTAMP()); 309 310INSERT INTO test.test VALUES (NULL, 'Good record 8', CURRENT_TIMESTAMP()); 311 312DELETE FROM world.city; 313 314INSERT INTO test.test VALUES (NULL, 'Good record 9', CURRENT_TIMESTAMP()); 315 316FLUSH LOGS; 317 318--echo # < CASE 6 > 319--echo # Flashback & Check the result 320--echo # 321 322let $MYSQLD_DATADIR= `select @@datadir`; 323--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 324--exec $MYSQL_BINLOG --database=world --table=city -vv $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_original_6.sql 325--exec $MYSQL_BINLOG --database=world --table=city -B $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_6.sql 326--exec $MYSQL -e "SET binlog_format= ROW; source $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_6.sql;" 327 328SELECT * FROM world.city; 329 330SELECT * FROM test.test; 331 332CHECKSUM TABLE world.city; 333 334DROP TABLE test.test; 335DROP TABLE world.city; 336DROP DATABASE world; 337 338--echo # < CASE 7 > 339--echo # Test Case for MDEV-17260 340--echo # 341 342RESET MASTER; 343 344CREATE TABLE t1 ( f INT PRIMARY KEY ) ENGINE=innodb; 345INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6); 346--echo # 6- Rows must be present 347SELECT COUNT(*) FROM t1; 348FLUSH LOGS; 349DELETE FROM t1; 350FLUSH LOGS; 351 352--echo # 0- Rows must be present 353--let $assert_cond= COUNT(*) = 0 FROM t1 354--let $assert_text= Table t1 should have 0 rows. 355--source include/assert.inc 356 357--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 358--exec $MYSQL -e "SET binlog_format= ROW; source $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_7.sql;" 359 360--echo # 6- Rows must be present upon restoring from flashback 361--let $assert_cond= COUNT(*) = 6 FROM t1 362--let $assert_text= Table t1 should have six rows. 363--source include/assert.inc 364 365DROP TABLE t1; 366 367## Clear 368SET binlog_format=statement; 369--error ER_FLASHBACK_NOT_SUPPORTED 370SET GLOBAL binlog_format=statement; 371