Adding upstream version 1:10.5.12.upstream/1%10.5.12upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 370 insertions, 0 deletions

diff --git a/mysql-test/suite/binlog/t/flashback.test b/mysql-test/suite/binlog/t/flashback.test
new file mode 100644
index 00000000..abe054e4
--- /dev/null
+++ b/mysql-test/suite/binlog/t/flashback.test
@@ -0,0 +1,370 @@
+--source include/have_log_bin.inc
+--source include/have_innodb.inc
+
+--echo #
+--echo # Preparatory cleanup.
+--echo #
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+--echo #
+--echo # We need a fixed timestamp to avoid varying results.
+--echo #
+SET timestamp=1000000000;
+
+--echo # < CASE 1 >
+--echo # Delete all existing binary logs.
+--echo #
+RESET MASTER;
+
+CREATE TABLE t1 (
+  c01 tinyint,
+  c02 smallint,
+  c03 mediumint,
+  c04 int,
+  c05 bigint,
+  c06 char(10),
+  c07 varchar(20),
+  c08 TEXT
+) ENGINE=InnoDB;
+
+--echo # < CASE 1 >
+--echo # Insert data to t1
+--echo #
+INSERT INTO t1 VALUES(0,0,0,0,0,'','','');
+INSERT INTO t1 VALUES(1,2,3,4,5, "abc", "abcdefg", "abcedfghijklmnopqrstuvwxyz");
+INSERT INTO t1 VALUES(127, 32767, 8388607, 2147483647, 9223372036854775807, repeat('a', 10), repeat('a', 20), repeat('a', 255));
+
+
+--echo # < CASE 1 >
+--echo # Update t1
+--echo #
+UPDATE t1 SET c01=100 WHERE c02=0 OR c03=3;
+
+--echo # < CASE 1 >
+--echo # Clear t1
+--echo #
+DELETE FROM t1;
+
+FLUSH LOGS;
+
+--echo # < CASE 1 >
+--echo # Show mysqlbinlog result without -B
+--echo #
+
+let $MYSQLD_DATADIR= `select @@datadir`;
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+--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/
+--exec $MYSQL_BINLOG --base64-output=decode-rows -v -v $MYSQLD_DATADIR/master-bin.000001
+
+--echo # < CASE 1 >
+--echo # Show mysqlbinlog result with -B
+--echo #
+
+let $MYSQLD_DATADIR= `select @@datadir`;
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+--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/
+--exec $MYSQL_BINLOG -B --base64-output=decode-rows -v -v $MYSQLD_DATADIR/master-bin.000001
+
+--echo # < CASE 1 >
+--echo # Insert data to t1
+--echo #
+TRUNCATE TABLE t1;
+INSERT INTO t1 VALUES(0,0,0,0,0,'','','');
+INSERT INTO t1 VALUES(1,2,3,4,5, "abc", "abcdefg", "abcedfghijklmnopqrstuvwxyz");
+INSERT INTO t1 VALUES(127, 32767, 8388607, 2147483647, 9223372036854775807, repeat('a', 10), repeat('a', 20), repeat('a', 60));
+
+--echo # < CASE 1 >
+--echo # Delete all existing binary logs.
+--echo #
+RESET MASTER;
+SELECT * FROM t1;
+
+--echo # < CASE 1 >
+--echo # Operate some data
+--echo #
+
+UPDATE t1 SET c01=20;
+UPDATE t1 SET c02=200;
+UPDATE t1 SET c03=2000;
+
+DELETE FROM t1;
+
+FLUSH LOGS;
+
+--echo # < CASE 1 >
+--echo # Flashback & Check the result
+--echo #
+
+let $MYSQLD_DATADIR= `select @@datadir`;
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+--exec $MYSQL_BINLOG -vv $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_original_1.sql
+--exec $MYSQL_BINLOG -B -vv $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_1.sql
+--exec $MYSQL -e "SET binlog_format= ROW; source $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_1.sql;"
+
+SELECT * FROM t1;
+
+RESET MASTER;
+
+--echo # < CASE 2 >
+--echo # UPDATE multi-rows in one event
+--echo #
+
+BEGIN;
+UPDATE t1 SET c01=10 WHERE c01=0;
+UPDATE t1 SET c01=20 WHERE c01=10;
+COMMIT;
+
+FLUSH LOGS;
+
+--echo # < CASE 2 >
+--echo # Flashback & Check the result
+--echo #
+
+let $MYSQLD_DATADIR= `select @@datadir`;
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+--exec $MYSQL_BINLOG -vv $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_original_2.sql
+--exec $MYSQL_BINLOG -B -vv $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_2.sql
+--exec $MYSQL -e "SET binlog_format= ROW; source $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_2.sql;"
+
+SELECT * FROM t1;
+
+DROP TABLE t1;
+
+--echo # < CASE 3 >
+--echo # Self-referencing foreign keys
+--echo #
+
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, FOREIGN KEY my_fk(b) REFERENCES t1(a)) ENGINE=InnoDB;
+
+BEGIN;
+INSERT INTO t1 VALUES (1, NULL);
+INSERT INTO t1 VALUES (2, 1), (3, 2), (4, 3);
+COMMIT;
+
+SELECT * FROM t1;
+
+# New binlog
+RESET MASTER;
+
+DELETE FROM t1 ORDER BY a DESC;
+
+FLUSH LOGS;
+
+--echo # < CASE 3 >
+--echo # Flashback & Check the result
+--echo #
+
+let $MYSQLD_DATADIR= `select @@datadir`;
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+--exec $MYSQL_BINLOG -vv $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_original_3.sql
+--exec $MYSQL_BINLOG -B -vv $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_3.sql
+--exec $MYSQL -e "SET binlog_format= ROW; source $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_3.sql;"
+
+SELECT * FROM t1;
+
+DROP TABLE t1;
+
+--echo # < CASE 4 >
+--echo # Trigger
+--echo #
+
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
+CREATE TABLE t2 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
+
+BEGIN;
+INSERT INTO t1 VALUES (1, NULL);
+INSERT INTO t1 VALUES (2, 1), (3, 2), (4, 3);
+INSERT INTO t2 VALUES (6, 7), (7, 8), (8, 9);
+COMMIT;
+
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW DELETE FROM t2 WHERE a = NEW.b;
+
+# New binlog
+RESET MASTER;
+
+INSERT INTO t1 VALUES (5, 6), (7, 8);
+
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+FLUSH LOGS;
+
+--echo # < CASE 4 >
+--echo # Flashback & Check the result
+--echo #
+
+let $MYSQLD_DATADIR= `select @@datadir`;
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+--exec $MYSQL_BINLOG -vv $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_original_4.sql
+--exec $MYSQL_BINLOG -B $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_4.sql
+--exec $MYSQL -e "SET binlog_format= ROW; source $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_4.sql;"
+
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+DROP TRIGGER trg1;
+DROP TABLE t1;
+DROP TABLE t2;
+
+--echo # < CASE 5 >
+--echo # REPLCAE Queries
+--echo #
+
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, UNIQUE uk(b)) ENGINE=InnoDB;
+
+BEGIN;
+INSERT INTO t1 VALUES (1, NULL);
+INSERT INTO t1 VALUES (2, 1), (3, 2), (4, 3);
+INSERT INTO t1 VALUES (5, 4), (6, 5), (7, 6);
+COMMIT;
+
+SELECT * FROM t1;
+
+# New binlog
+RESET MASTER;
+
+REPLACE INTO t1 VALUES (3, 100);
+REPLACE INTO t1 SET a=4, b=200;
+
+SELECT * FROM t1;
+
+REPLACE INTO t1 VALUES (5,5);
+
+SELECT * FROM t1;
+
+FLUSH LOGS;
+
+--echo # < CASE 5 >
+--echo # Flashback & Check the result
+--echo #
+
+let $MYSQLD_DATADIR= `select @@datadir`;
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+--exec $MYSQL_BINLOG -vv $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_original_5.sql
+--exec $MYSQL_BINLOG -B $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_5.sql
+--exec $MYSQL -e "SET binlog_format= ROW; source $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_5.sql;"
+
+SELECT * FROM t1;
+
+DROP TABLE t1;
+
+
+--echo # < CASE 6 >
+--echo # Test Case from MDEV-21067
+--echo #
+
+# Init Structure
+CREATE DATABASE world;
+CREATE TABLE world.city (
+     ID INT AUTO_INCREMENT PRIMARY KEY,
+     Name VARCHAR(64),
+     CountryCode VARCHAR(64),
+     District VARCHAR(64),
+     Population INT
+) ENGINE=InnoDB;
+CREATE TABLE test.test (
+     ID INT AUTO_INCREMENT PRIMARY KEY,
+     REC VARCHAR(64),
+     ts TIMESTAMP
+) ENGINE=InnoDB;
+
+INSERT INTO world.city VALUES (NULL, 'Davenport', 'USA', 'Iowa', 100);
+INSERT INTO world.city VALUES (NULL, 'Boulder', 'USA', 'Colorado', 1000);
+INSERT INTO world.city VALUES (NULL, 'Gweru', 'ZWE', 'Midlands', 10000);
+
+RESET MASTER;
+
+CHECKSUM TABLE world.city;
+
+# Insert test data
+INSERT INTO test.test VALUES (NULL, 'Good record 1', CURRENT_TIMESTAMP());
+
+INSERT INTO world.city VALUES (NULL, 'Wrong value 1', '000', 'Wrong', 0);
+INSERT INTO world.city VALUES (NULL, 'Wrong value 2', '000', 'Wrong', 0) , (NULL, 'Wrong value 3', '000', 'Wrong', 0);
+
+INSERT INTO test.test VALUES (NULL, 'Good record 2', CURRENT_TIMESTAMP());
+
+UPDATE world.city SET Population = 99999999 WHERE ID IN (1, 2, 3);
+
+INSERT INTO test.test VALUES (NULL, 'Good record 3', CURRENT_TIMESTAMP());
+
+DELETE FROM world.city WHERE ID BETWEEN 1 AND 2;
+
+INSERT INTO test.test VALUES (NULL, 'Good record 5', CURRENT_TIMESTAMP());
+
+REPLACE INTO world.city VALUES (4074, 'Wrong value 4', '000', 'Wrong', 0);
+REPLACE INTO world.city VALUES (4078, 'Wrong value 5', '000', 'Wrong', 0), (NULL, 'Wrong value 6', '000', 'Wrong', 0);
+
+INSERT INTO test.test VALUES (NULL, 'Good record 6', CURRENT_TIMESTAMP());
+
+INSERT INTO world.city
+SELECT NULL, Name, CountryCode, District, Population FROM world.city WHERE ID BETWEEN 2 AND 10;
+
+INSERT INTO test.test VALUES (NULL, 'Good record 7', CURRENT_TIMESTAMP());
+
+INSERT INTO test.test VALUES (NULL, 'Good record 8', CURRENT_TIMESTAMP());
+
+DELETE FROM world.city;
+
+INSERT INTO test.test VALUES (NULL, 'Good record 9', CURRENT_TIMESTAMP());
+
+FLUSH LOGS;
+
+--echo # < CASE 6 >
+--echo # Flashback & Check the result
+--echo #
+
+let $MYSQLD_DATADIR= `select @@datadir`;
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+--exec $MYSQL_BINLOG --database=world --table=city -vv $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_original_6.sql
+--exec $MYSQL_BINLOG --database=world --table=city -B $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_6.sql
+--exec $MYSQL -e "SET binlog_format= ROW; source $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_6.sql;"
+
+SELECT * FROM world.city;
+
+SELECT * FROM test.test;
+
+CHECKSUM TABLE world.city;
+
+DROP TABLE test.test;
+DROP TABLE world.city;
+DROP DATABASE world;
+
+--echo # < CASE 7 >
+--echo # Test Case for MDEV-17260
+--echo #
+
+RESET MASTER;
+
+CREATE TABLE t1 ( f INT PRIMARY KEY ) ENGINE=innodb;
+INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6);
+--echo # 6- Rows must be present
+SELECT COUNT(*) FROM t1;
+FLUSH LOGS;
+DELETE  FROM t1;
+FLUSH LOGS;
+
+--echo # 0- Rows must be present
+--let $assert_cond= COUNT(*) = 0 FROM t1
+--let $assert_text= Table t1 should have 0 rows.
+--source include/assert.inc
+
+--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
+--exec $MYSQL -e "SET binlog_format= ROW; source $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_7.sql;"
+
+--echo # 6- Rows must be present upon restoring from flashback
+--let $assert_cond= COUNT(*) = 6 FROM t1
+--let $assert_text= Table t1 should have six rows.
+--source include/assert.inc
+
+DROP TABLE t1;
+
+## Clear
+SET binlog_format=statement;
+--error ER_FLASHBACK_NOT_SUPPORTED
+SET GLOBAL binlog_format=statement;