diff options
Diffstat (limited to 'mysql-test/suite/binlog/t')
19 files changed, 601 insertions, 211 deletions
diff --git a/mysql-test/suite/binlog/t/binlog_base64_flag.test b/mysql-test/suite/binlog/t/binlog_base64_flag.test index 5311da54..6935f69b 100644 --- a/mysql-test/suite/binlog/t/binlog_base64_flag.test +++ b/mysql-test/suite/binlog/t/binlog_base64_flag.test @@ -2,9 +2,6 @@ # work as expected, and that BINLOG statements with row events fail if # they are not preceded by BINLOG statements with Format description # events. -# -# See also BUG#32407. - # BINLOG statement does not work in embedded mode. source include/not_embedded.inc; @@ -12,23 +9,10 @@ source include/not_embedded.inc; call mtr.add_suppression("BINLOG_BASE64_EVENT: According to the master's version"); call mtr.add_suppression("BINLOG_BASE64_EVENT: Column 1 of table 'test.char128_utf8' cannot be converted"); -disable_warnings; -DROP TABLE IF EXISTS t1; -enable_warnings; -# Test to show BUG#32407. This reads a binlog created with the -# mysql-5.1-telco-6.1 tree, specifically at the tag -# mysql-5.1.15-ndb-6.1.23, and applies it to the database. The test -# should fail before BUG#32407 was fixed and succeed afterwards. ---echo ==== Test BUG#32407 ==== - -# The binlog contains row events equivalent to: -# CREATE TABLE t1 (a int) engine = myisam -# INSERT INTO t1 VALUES (1), (1) -exec $MYSQL_BINLOG suite/binlog/std_data/bug32407.001 | $MYSQL; -# The above line should succeed and t1 should contain two ones +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (1), (1); select * from t1; - # Test that a BINLOG statement encoding a row event fails unless a # Format_description_event as been supplied with an earlier BINLOG # statement. @@ -92,14 +76,6 @@ select * from t1; # show "one-shot" feature of binlog_fragment variables SELECT @binlog_fragment_0, @binlog_fragment_1 as 'NULL','NULL'; -# New mysqlbinlog supports --base64-output=never ---echo ==== Test --base64-output=never on a binlog with row events ==== - -# mysqlbinlog should fail ---replace_regex /#[0-9][0-9][0-9][0-9][0-9][0-9] \N*/<#>/ /SET \@\@session.pseudo_thread_id.*/<#>/ -exec $MYSQL_BINLOG --base64-output=never --print-row-count=0 --print-row-event-positions=0 suite/binlog/std_data/bug32407.001; - - # Test that the following fails cleanly: "First, read a # Format_description event which has N event types. Then, read an # event of type M>N" diff --git a/mysql-test/suite/binlog/t/binlog_expire_warnings.opt b/mysql-test/suite/binlog/t/binlog_expire_warnings.opt new file mode 100644 index 00000000..c85ef7d3 --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_expire_warnings.opt @@ -0,0 +1 @@ +--log-warnings=4 diff --git a/mysql-test/suite/binlog/t/binlog_grant.test b/mysql-test/suite/binlog/t/binlog_grant.test index d573281f..5ece793e 100644 --- a/mysql-test/suite/binlog/t/binlog_grant.test +++ b/mysql-test/suite/binlog/t/binlog_grant.test @@ -30,7 +30,6 @@ connection plain; --error ER_SPECIFIC_ACCESS_DENIED_ERROR set session sql_log_bin = 1; - # Testing setting both session and global BINLOG_FORMAT variable both # as root and as plain user. @@ -56,7 +55,6 @@ connection default; set global binlog_format = @saved_binlog_format; drop user mysqltest_1@localhost; - # Testing if REPLICATION CLIENT privilege is enough to execute # SHOW MASTER LOGS and SHOW BINARY. CREATE USER 'mysqltest_1'@'localhost'; @@ -77,9 +75,12 @@ SHOW BINLOG STATUS; connection default; DROP USER 'mysqltest_1'@'localhost'; +--echo # +--echo # End of 10.4 tests +--echo # --echo # ---echo # Start of 10.5 test +--echo # Start of 10.5 tests --echo # --echo # @@ -95,11 +96,10 @@ REVOKE REPLICATION CLIENT ON *.* FROM user1@localhost; SHOW GRANTS FOR user1@localhost; DROP USER user1@localhost; - ---echo # Test if SHOW BINARY LOGS and SHOW BINGLOG STATUS are not allowed without REPLICATION CLIENT or SUPER +--echo # Test if SHOW BINARY LOGS and SHOW BINGLOG STATUS are not allowed without REPLICATION CLIENT CREATE USER user1@localhost; GRANT ALL PRIVILEGES ON *.* TO user1@localhost; -REVOKE REPLICATION CLIENT, SUPER ON *.* FROM user1@localhost; +REVOKE REPLICATION CLIENT ON *.* FROM user1@localhost; --connect(user1,localhost,user1,,) --connection user1 --error ER_SPECIFIC_ACCESS_DENIED_ERROR @@ -112,11 +112,10 @@ SHOW BINLOG STATUS; --connection default DROP USER user1@localhost; - ---echo # Test if PURGE BINARY LOGS is not allowed without BINLOG ADMIN or SUPER +--echo # Test if PURGE BINARY LOGS is not allowed without BINLOG ADMIN CREATE USER user1@localhost; GRANT ALL PRIVILEGES ON *.* TO user1@localhost; -REVOKE BINLOG ADMIN, SUPER ON *.* FROM user1@localhost; +REVOKE BINLOG ADMIN ON *.* FROM user1@localhost; --connect(user1,localhost,user1,,) --connection user1 --error ER_SPECIFIC_ACCESS_DENIED_ERROR @@ -125,7 +124,6 @@ PURGE BINARY LOGS BEFORE '2001-01-01 00:00:00'; --connection default DROP USER user1@localhost; - --echo # Test if PURGE BINLOG is allowed with BINLOG ADMIN CREATE USER user1@localhost; GRANT BINLOG ADMIN ON *.* TO user1@localhost; @@ -136,18 +134,6 @@ PURGE BINARY LOGS BEFORE '2001-01-01 00:00:00'; connection default; DROP USER user1@localhost; - ---echo # Test if PURGE BINLOG is allowed with SUPER -CREATE USER user1@localhost; -GRANT SUPER ON *.* TO user1@localhost; ---connect(user1,localhost,user1,,"*NO-ONE*") ---connection user1 -PURGE BINARY LOGS BEFORE '2001-01-01 00:00:00'; ---disconnect user1 -connection default; -DROP USER user1@localhost; - - --echo # Test if SHOW BINLOG EVENTS is not allowed without BINLOG MONITOR CREATE USER user1@localhost; GRANT ALL PRIVILEGES ON *.* TO user1@localhost; @@ -160,7 +146,6 @@ SHOW BINLOG EVENTS; --connection default DROP USER user1@localhost; - --echo # Test if SHOW BINLOG EVENTS is allowed with BINLOG MONITOR CREATE USER user1@localhost; GRANT BINLOG MONITOR ON *.* TO user1@localhost; @@ -196,7 +181,7 @@ RENAME TABLE t1 to t2; --connection default REVOKE BINLOG REPLAY ON *.* FROM user1@localhost; -call mtr.add_suppression("Access denied; you need (at least one of) the SUPER, BINLOG REPLAY privilege(s) for this operation"); +call mtr.add_suppression("Access denied; you need (at least one of) the BINLOG REPLAY privilege(s) for this operation"); --echo # Privilege errors are expected now: --connection user1 --error 1 diff --git a/mysql-test/suite/binlog/t/binlog_gtid_index.test b/mysql-test/suite/binlog/t/binlog_gtid_index.test new file mode 100644 index 00000000..458b77ec --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_gtid_index.test @@ -0,0 +1,229 @@ +--source include/have_binlog_format_mixed.inc + +SET GLOBAL binlog_gtid_index= 0; +SET GLOBAL binlog_gtid_index= 1; + +--let $file= query_get_value(SHOW MASTER STATUS, File, 1) +--let $pos1= query_get_value(SHOW MASTER STATUS, Position, 1) +SET @gtid1= @@gtid_binlog_pos; +CREATE TABLE t1 (a INT PRIMARY KEY); +--let $pos2= query_get_value(SHOW MASTER STATUS, Position, 1) +SET @gtid2= @@gtid_binlog_pos; +INSERT INTO t1 VALUES (1); +--let $pos3= query_get_value(SHOW MASTER STATUS, Position, 1) +SET @gtid3= @@gtid_binlog_pos; +INSERT INTO t1 VALUES (2); +INSERT INTO t1 VALUES (3); +INSERT INTO t1 VALUES (4); +--let $pos4= query_get_value(SHOW MASTER STATUS, Position, 1) +SET @gtid4= @@gtid_binlog_pos; +INSERT INTO t1 VALUES (5); +--let $pos5= query_get_value(SHOW MASTER STATUS, Position, 1) +SET @gtid5= @@gtid_binlog_pos; + +--disable_query_log +--let $i=0 +while ($i < 100) { + eval INSERT INTO t1 VALUES (6 + $i); + inc $i; +} +--enable_query_log +--let $pos6= query_get_value(SHOW MASTER STATUS, Position, 1) +SET @gtid6= @@gtid_binlog_pos; + +INSERT INTO t1 VALUES (106); +INSERT INTO t1 VALUES (107); + +# Test first the hot and then the cold index. +--let $i= 0 +while ($i < 2) { + --disable_query_log + eval SELECT BINLOG_GTID_POS('$file', $pos1) = @gtid1 AS Ok; + eval SELECT BINLOG_GTID_POS('$file', $pos2) = @gtid2 AS Ok; + eval SELECT BINLOG_GTID_POS('$file', $pos3) = @gtid3 AS Ok; + eval SELECT BINLOG_GTID_POS('$file', $pos4) = @gtid4 AS Ok; + eval SELECT BINLOG_GTID_POS('$file', $pos5) = @gtid5 AS Ok; + eval SELECT BINLOG_GTID_POS('$file', $pos6) = @gtid6 AS Ok; + --enable_query_log + + inc $i; + if ($i == 1) { + FLUSH BINARY LOGS; + } +} + +--echo *** Test that purge deletes the gtid index files. *** +FLUSH BINARY LOGS; +INSERT INTO t1 VALUES (200); +--let $file2= query_get_value(SHOW MASTER STATUS, File, 1) +FLUSH BINARY LOGS; +INSERT INTO t1 VALUES (201); +--let $file3= query_get_value(SHOW MASTER STATUS, File, 1) +FLUSH BINARY LOGS; +INSERT INTO t1 VALUES (202); +--let $file4= query_get_value(SHOW MASTER STATUS, File, 1) + +--replace_result $file3 FILE +eval PURGE BINARY LOGS TO '$file3'; + +--let $MYSQLD_DATADIR= `select @@datadir` +--error 1 +--file_exists $MYSQLD_DATADIR/$file.idx +--error 1 +--file_exists $MYSQLD_DATADIR/$file2.idx +--file_exists $MYSQLD_DATADIR/$file3.idx +--file_exists $MYSQLD_DATADIR/$file4.idx + +--echo *** Test missed index lookup due to missing or corrupt index file. +FLUSH NO_WRITE_TO_BINLOG BINARY LOGS; +--let $file= query_get_value(SHOW MASTER STATUS, File, 1) +INSERT INTO t1 VALUES (301); +INSERT INTO t1 VALUES (302); +INSERT INTO t1 VALUES (303); +--let $pos= query_get_value(SHOW MASTER STATUS, Position, 1) +SET @gtid_pos= @@GLOBAL.gtid_binlog_pos; +INSERT INTO t1 VALUES (304); +INSERT INTO t1 VALUES (305); + +# BINLOG_GTID_POS() has a side effect: it increments binlog_gtid_index_hit +--disable_ps2_protocol +FLUSH NO_WRITE_TO_BINLOG STATUS; +--echo +++ Initial status: +SHOW STATUS LIKE 'binlog_gtid_index_%'; +--echo +++ GTID Lookup in good index. +--disable_query_log +eval SELECT BINLOG_GTID_POS('$file', $pos) = @gtid_pos AS Gtid_Lookup_Ok; +--enable_query_log +SHOW STATUS LIKE 'binlog_gtid_index_%'; +--remove_file $MYSQLD_DATADIR/$file.idx +--echo +++ GTID Lookup, index file is missing. +--disable_query_log +eval SELECT BINLOG_GTID_POS('$file', $pos) = @gtid_pos AS Gtid_Lookup_Ok; +--enable_query_log +SHOW STATUS LIKE 'binlog_gtid_index_%'; + +FLUSH NO_WRITE_TO_BINLOG BINARY LOGS; +--let $file= query_get_value(SHOW MASTER STATUS, File, 1) +INSERT INTO t1 VALUES (306); +--let $pos= query_get_value(SHOW MASTER STATUS, Position, 1) +SET @gtid_pos= @@GLOBAL.gtid_binlog_pos; +INSERT INTO t1 VALUES (307); +INSERT INTO t1 VALUES (308); +# Rotate again so we hit an on-disk index file, not the "hot" index. +FLUSH NO_WRITE_TO_BINLOG BINARY LOGS; + +# Corrupt the flag byte of the first page with an unused bit. +--let FILE_TO_CORRUPT= $MYSQLD_DATADIR/$file.idx +--perl +use strict; +use warnings; +use Fcntl qw(:DEFAULT :seek); +sysopen F, $ENV{FILE_TO_CORRUPT}, O_RDWR + or die "Cannot open file $ENV{FILE_TO_CORRUPT}: $!\n"; +# Corrupt the flag byte with an unused flag. +sysseek(F, 16, SEEK_SET) + or die "Cannot seek file: $!\n"; +my $buf; +sysread(F, $buf, 1) + or die "Cannot read file: $!\n"; +$buf= chr(ord($buf) | 0x80); +sysseek(F, 16, SEEK_SET) + or die "Cannot seek file: $!\n"; +syswrite(F, $buf, 1) == 1 + or die "Cannot write file: $!\n"; +close F; +EOF + +--echo +++ GTID Lookup, first page of index is corrupt. +--disable_query_log +eval SELECT BINLOG_GTID_POS('$file', $pos) = @gtid_pos AS Gtid_Lookup_Ok; +--enable_query_log +SHOW STATUS LIKE 'binlog_gtid_index_%'; + +# Corrupt the last byte of the root page. +# Set a small page-size so we test corruption in something not the header page. +SET @old_page_size= @@GLOBAL.binlog_gtid_index_page_size; +SET @old_span_min= @@GLOBAL.binlog_gtid_index_span_min; +SET GLOBAL binlog_gtid_index_page_size= 64; +SET GLOBAL binlog_gtid_index_span_min= 1; +FLUSH NO_WRITE_TO_BINLOG BINARY LOGS; +--let $file= query_get_value(SHOW MASTER STATUS, File, 1) +INSERT INTO t1 VALUES (310); +INSERT INTO t1 VALUES (311); +INSERT INTO t1 VALUES (312); +--let $pos= query_get_value(SHOW MASTER STATUS, Position, 1) +SET @gtid_pos= @@GLOBAL.gtid_binlog_pos; +INSERT INTO t1 VALUES (313); +INSERT INTO t1 VALUES (314); +INSERT INTO t1 VALUES (315); +INSERT INTO t1 VALUES (316); +FLUSH NO_WRITE_TO_BINLOG BINARY LOGS; +SET GLOBAL binlog_gtid_index_page_size= @old_page_size; +SET GLOBAL binlog_gtid_index_span_min= @old_span_min; + +--let FILE_TO_CORRUPT= $MYSQLD_DATADIR/$file.idx +--perl +use strict; +use warnings; +use Fcntl qw(:DEFAULT :seek); +sysopen F, $ENV{FILE_TO_CORRUPT}, O_RDWR + or die "Cannot open file $ENV{FILE_TO_CORRUPT}: $!\n"; +# Tricky: The index is written asynchroneously, it may still be incomplete. +# So wait for the file to be written completely with a root node at the end. +my $count= 0; +for (;;) { + my $end= sysseek(F, 0, SEEK_END); + if ($end > 0 && ($end % 64) == 0) { + # The index file is non-empty with a full page at the end, test if the + # root page has been fully written. This is seen as bit 2 (PAGE_FLAG_LAST) + # and bit 3 (PAGE_FLAG_ROOT) being set (0xc). + my $flag; + if (sysseek(F, -64, SEEK_CUR) && + sysread(F, $flag, 1) && + (ord($flag) & 0xc) == 0xc) { + last; + } + } + die "Timeout waiting for GTID index to be non-empty\n" + if ++$count >= 500; + # Simple way to do sub-second sleep. + select(undef, undef, undef, 0.050); +} +# Corrupt the flag byte with an unused flag. +sysseek(F, -2, SEEK_END) + or die "Cannot seek file: $!\n"; +my $buf; +sysread(F, $buf, 1) + or die "Cannot read file: $!\n"; +$buf= chr(ord($buf) ^ 0x4); +sysseek(F, -2, SEEK_END) + or die "Cannot seek file: $!\n"; +syswrite(F, $buf, 1) == 1 + or die "Cannot write file: $!\n"; +close F; +EOF + +--echo +++ GTID Lookup, root page of index is corrupt. +--disable_query_log +eval SELECT BINLOG_GTID_POS('$file', $pos) = @gtid_pos AS Gtid_Lookup_Ok; +--enable_query_log +SHOW STATUS LIKE 'binlog_gtid_index_%'; + +--echo *** Test BINLOG_GTID_POS() with too-large offset. +# New binlog to skip the now corrupted one. +FLUSH NO_WRITE_TO_BINLOG BINARY LOGS; +--let $file= query_get_value(SHOW MASTER STATUS, File, 1) +INSERT INTO t1 VALUES (401); +INSERT INTO t1 VALUES (402); +--echo +++ Test the hot index. +--replace_result $file FILE +eval SELECT BINLOG_GTID_POS('$file', 100000000); +SHOW STATUS LIKE 'binlog_gtid_index_%'; +FLUSH NO_WRITE_TO_BINLOG BINARY LOGS; +--echo +++ Test the cold index. +--replace_result $file FILE +eval SELECT BINLOG_GTID_POS('$file', 100000000); +SHOW STATUS LIKE 'binlog_gtid_index_%'; +--enable_ps2_protocol + +DROP TABLE t1; diff --git a/mysql-test/suite/binlog/t/binlog_gtid_index_crash.opt b/mysql-test/suite/binlog/t/binlog_gtid_index_crash.opt new file mode 100644 index 00000000..993e6613 --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_gtid_index_crash.opt @@ -0,0 +1 @@ +--binlog-gtid-index-page-size=128 --binlog-gtid-index-span-min=1 diff --git a/mysql-test/suite/binlog/t/binlog_gtid_index_crash.test b/mysql-test/suite/binlog/t/binlog_gtid_index_crash.test new file mode 100644 index 00000000..965e08a4 --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_gtid_index_crash.test @@ -0,0 +1,66 @@ +--source include/have_innodb.inc +# Don't test this under valgrind, memory leaks will occur +--source include/not_valgrind.inc +# Avoid CrashReporter popup on Mac +--source include/not_crashrep.inc +# Binary must be compiled with debug for crash to occur +--source include/have_debug.inc +--source include/have_binlog_format_row.inc + +# We have an .opt file that sets a small page size and disables sparseness, +# so we get something non-trivial in the GTID index even with a small amount +# of binlogged events. + +--echo *** Test that binlog GTID index is recovered after a crash. +CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB; + +--disable_query_log +INSERT INTO t1 VALUES (0, 0); +INSERT INTO t1 VALUES (1, 0); +INSERT INTO t1 VALUES (2, 0); +--let $i= 10 +while ($i < 20) { + eval INSERT INTO t1 VALUES ($i, 0); + inc $i; +} +--let $file= query_get_value(SHOW MASTER STATUS, File, 1) +--let $pos1= query_get_value(SHOW MASTER STATUS, Position, 1) +--let $gtid1= `SELECT @@gtid_binlog_pos` +while ($i < 30) { + eval INSERT INTO t1 VALUES ($i, 0); + inc $i; +} +--let $pos2= query_get_value(SHOW MASTER STATUS, Position, 1) +--let $gtid2= `SELECT @@gtid_binlog_pos` +while ($i < 40) { + eval INSERT INTO t1 VALUES ($i, 0); + inc $i; +} +--let $pos3= query_get_value(SHOW MASTER STATUS, Position, 1) +--let $gtid3= `SELECT @@gtid_binlog_pos` +INSERT INTO t1 VALUES (50, 0); +INSERT INTO t1 VALUES (51, 0); + +--disable_ps2_protocol +FLUSH NO_WRITE_TO_BINLOG STATUS; +eval SELECT BINLOG_GTID_POS('$file', $pos1) = "$gtid1" AS Ok; +eval SELECT BINLOG_GTID_POS('$file', $pos2) = "$gtid2" AS Ok; +eval SELECT BINLOG_GTID_POS('$file', $pos3) = "$gtid3" AS Ok; +--enable_query_log +SHOW STATUS LIKE 'binlog_gtid_index_%'; +--enable_ps2_protocol + +--echo *** Crash the server, check that GTID index can be used after restart. +--source include/crash_mysqld.inc + +--disable_ps2_protocol +FLUSH NO_WRITE_TO_BINLOG STATUS; +--disable_query_log +eval SELECT BINLOG_GTID_POS('$file', $pos1) = "$gtid1" AS Ok; +eval SELECT BINLOG_GTID_POS('$file', $pos2) = "$gtid2" AS Ok; +eval SELECT BINLOG_GTID_POS('$file', $pos3) = "$gtid3" AS Ok; +--enable_query_log +SHOW STATUS LIKE 'binlog_gtid_index_%'; +--enable_ps2_protocol + +DROP TABLE t1; diff --git a/mysql-test/suite/binlog/t/binlog_killed.test b/mysql-test/suite/binlog/t/binlog_killed.test index 7c3a262d..271da705 100644 --- a/mysql-test/suite/binlog/t/binlog_killed.test +++ b/mysql-test/suite/binlog/t/binlog_killed.test @@ -67,7 +67,7 @@ let $rows= `select count(*) from t2 /* must be 2 or 0 */`; let $MYSQLD_DATADIR= `select @@datadir`; --let $binlog_killed_pos=query_get_value(SHOW BINLOG EVENTS, Pos, 6) ---let $binlog_killed_end_log_pos=query_get_value(SHOW BINLOG EVENTS, End_log_pos, 6) +--let $binlog_killed_end_log_pos=query_get_value(SHOW BINLOG EVENTS, Pos, 7) --exec $MYSQL_BINLOG --force-if-open --start-position=$binlog_killed_pos --stop-position=$binlog_killed_end_log_pos $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/kill_query_calling_sp.binlog --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR --disable_result_log diff --git a/mysql-test/suite/binlog/t/binlog_killed_simulate-master.opt b/mysql-test/suite/binlog/t/binlog_killed_simulate-master.opt index 90c70ece..d6f14950 100644 --- a/mysql-test/suite/binlog/t/binlog_killed_simulate-master.opt +++ b/mysql-test/suite/binlog/t/binlog_killed_simulate-master.opt @@ -1 +1 @@ ---loose-debug=d,simulate_kill_bug27571 +--loose-debug-dbug=d,simulate_kill_bug27571 diff --git a/mysql-test/suite/binlog/t/binlog_mysqlbinlog_raw_flush.test b/mysql-test/suite/binlog/t/binlog_mysqlbinlog_raw_flush.test index 539a8fe5..e32d1e0e 100644 --- a/mysql-test/suite/binlog/t/binlog_mysqlbinlog_raw_flush.test +++ b/mysql-test/suite/binlog/t/binlog_mysqlbinlog_raw_flush.test @@ -23,6 +23,8 @@ # Test needs to reset the binlog as it is checking specific GTID. RESET MASTER; +RESET MASTER; + --echo # --echo # MDEV-30698 Cover missing test cases for mariadb-binlog options --echo # --raw [and] --flashback diff --git a/mysql-test/suite/binlog/t/binlog_old_versions.test b/mysql-test/suite/binlog/t/binlog_old_versions.test deleted file mode 100644 index 13010154..00000000 --- a/mysql-test/suite/binlog/t/binlog_old_versions.test +++ /dev/null @@ -1,153 +0,0 @@ -# Test that old binlog formats can be read. - -# Some previous versions of MySQL use their own binlog format, -# especially in row-based replication. This test uses saved binlogs -# from those old versions to test that we can replicate from old -# versions to the present version. - -# Replicating from old versions to new versions is necessary in an -# online upgrade scenario, where the . - -# The previous versions we currently test are: -# - version 5.1.17 and earlier trees -# - mysql-5.1-wl2325-xxx trees (AKA alcatel trees) -# - mysql-5.1-telco-6.1 trees -# For completeness, we also test mysql-5.1-new_rpl, which is supposed -# to be the "correct" version. - -# All binlogs were generated with the same commands (listed at the end -# of this test for reference). The binlogs contain the following -# events: Table_map, Write_rows, Update_rows, Delete_rows Query, Xid, -# User_var, Int_var, Rand, Begin_load, Append_file, Execute_load. - -# Related bugs: BUG#27779, BUG#31581, BUG#31582, BUG#31583, BUG#32407 - -source include/not_embedded.inc; - ---echo ==== Read binlog with v2 row events ==== - -# Read binlog. ---exec $MYSQL_BINLOG --local-load=$MYSQLTEST_VARDIR/tmp/ suite/binlog/std_data/ver_trunk_row_v2.001 | $MYSQL --local-infile=1 -# Show result. -SELECT * FROM t1 ORDER BY a; -SELECT * FROM t2 ORDER BY a; -SELECT COUNT(*) FROM t3; -# Reset. -DROP TABLE t1, t2, t3; - - ---echo ==== Read modern binlog (version 5.1.23) ==== - -# Read binlog. ---exec $MYSQL_BINLOG --local-load=$MYSQLTEST_VARDIR/tmp/ suite/binlog/std_data/ver_5_1_23.001 | $MYSQL --local-infile=1 -# Show result. -SELECT * FROM t1 ORDER BY a; -SELECT * FROM t2 ORDER BY a; -SELECT COUNT(*) FROM t3; -# Reset. -DROP TABLE t1, t2, t3; - - ---echo ==== Read binlog from version 5.1.17 ==== - -# Read binlog. ---exec $MYSQL_BINLOG --local-load=$MYSQLTEST_VARDIR/tmp/ suite/binlog/std_data/ver_5_1_17.001 | $MYSQL --local-infile=1 -# Show result. -SELECT * FROM t1 ORDER BY a; -SELECT * FROM t2 ORDER BY a; -SELECT COUNT(*) FROM t3; -# Reset. -DROP TABLE t1, t2, t3; - - ---echo ==== Read binlog from version 4.1 ==== - -# In this version, neither row-based binlogging nor Xid events -# existed, so the binlog was generated without the "row-based tests" -# part and the "get xid event" part, and it does not create table t2. - -# Read binlog. ---exec $MYSQL_BINLOG --local-load=$MYSQLTEST_VARDIR/tmp/ suite/binlog/std_data/binlog_old_version_4_1.000001 | $MYSQL --local-infile=1 -# Show result. -SELECT * FROM t1 ORDER BY a; -SELECT COUNT(*) FROM t3; -# Reset. -DROP TABLE t1, t3; - - ---echo ==== Read binlog from telco tree (mysql-5.1-telco-6.1) ==== - -# Read binlog. ---exec $MYSQL_BINLOG --local-load=$MYSQLTEST_VARDIR/tmp/ suite/binlog/std_data/ver_5_1-telco.001 | $MYSQL --local-infile=1 -# Show resulting tablea. -SELECT * FROM t1 ORDER BY a; -SELECT * FROM t2 ORDER BY a; -SELECT COUNT(*) FROM t3; -# Reset. -DROP TABLE t1, t2, t3; - - -#### The following commands were used to generate the binlogs #### -# -#source include/master-slave.inc; -# -## ==== initialize ==== -#USE test; -#CREATE TABLE t1 (a int, b char(50)) ENGINE = MyISAM; -#CREATE TABLE t2 (a int, b char(50)) ENGINE = InnoDB; -#CREATE TABLE t3 (a char(20)); -# -# -## ==== row based tests ==== -#SET BINLOG_FORMAT='row'; -# -## ---- get write, update, and delete rows events ---- -#INSERT INTO t1 VALUES (0, 'one'), (1, 'two'); -#UPDATE t1 SET a=a+1; -#DELETE FROM t1 WHERE a=2; -# -# -## ==== statement based tests ==== -#SET BINLOG_FORMAT = 'statement'; -# -## ---- get xid events ---- -#BEGIN; -#INSERT INTO t2 VALUES (3, 'first stm in trx'); -#INSERT INTO t1 VALUES (3, 'last stm in trx: next event should be xid'); -#COMMIT; -# -## ---- get user var events ---- -#SET @x = 4; -#INSERT INTO t1 VALUES (@x, 'four'); -# -## ---- get rand event ---- -#INSERT INTO t1 VALUES (RAND() * 1000000, 'random'); -# -## ---- get intvar event ---- -#INSERT INTO t1 VALUES (LAST_INSERT_ID(), 'last_insert_id'); -# -## ---- get begin, append and execute load events ---- -## double the file until we have more than 2^17 bytes, so that the -## event has to be split and we can use Append_file_log_event. -# -#SET SQL_LOG_BIN=0; -#CREATE TABLE temp (a char(20)); -#LOAD DATA INFILE '../std_data_ln/words.dat' INTO TABLE temp; -#INSERT INTO temp SELECT * FROM temp; -#INSERT INTO temp SELECT * FROM temp; -#INSERT INTO temp SELECT * FROM temp; -#INSERT INTO temp SELECT * FROM temp; -#INSERT INTO temp SELECT * FROM temp; -#INSERT INTO temp SELECT * FROM temp; -#INSERT INTO temp SELECT * FROM temp; -#INSERT INTO temp SELECT * FROM temp; -#SELECT a FROM temp INTO OUTFILE 'big_file.dat'; -#DROP TABLE temp; -#SET SQL_LOG_BIN=1; -# -#LOAD DATA INFILE 'big_file.dat' INTO TABLE t3; -# -#SELECT * FROM t1 ORDER BY a; -#SELECT * FROM t2 ORDER BY a; -#SELECT COUNT(*) FROM t3; -#--source include/rpl_end.inc diff --git a/mysql-test/suite/binlog/t/binlog_row_img.test b/mysql-test/suite/binlog/t/binlog_row_img.test new file mode 100644 index 00000000..a6624e7e --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_row_img.test @@ -0,0 +1,35 @@ +################################################################################ +# Support binlog_row_image FULL_NODUP mode +# +# In FULL_NODUP: +# - before image includes all columns. +# - after image includes only changed columns. +################################################################################ + +--source include/have_binlog_format_row.inc +--echo # +--echo # FULL_NODUP mode works as expected +--echo # +SET binlog_row_image = "FULL_NODUP"; +--source suite/binlog/include/row_img.test + +--echo # +--echo # MINIMAL mode works as expected +--echo # +SET binlog_row_image = "MINIMAL"; +--source suite/binlog/include/row_img.test + +--echo # +--echo # NOBLOB mode works as expected +--echo # +SET binlog_row_image = "NOBLOB"; +--source suite/binlog/include/row_img.test + +--echo # +--echo # FULL mode works as expected +--echo # +SET binlog_row_image = "FULL"; +--source suite/binlog/include/row_img.test + +RESET MASTER; + diff --git a/mysql-test/suite/binlog/t/binlog_stm_do_db.test b/mysql-test/suite/binlog/t/binlog_stm_do_db.test index 3ed1734f..c83753eb 100644 --- a/mysql-test/suite/binlog/t/binlog_stm_do_db.test +++ b/mysql-test/suite/binlog/t/binlog_stm_do_db.test @@ -19,7 +19,7 @@ # # The test is implemented as follows: # -# i) set tx_isolation to read-committed. +# i) set transaction_isolation to read-committed. # # ii) create two databases (one filtered other not - using # binlog-do-db) @@ -38,8 +38,8 @@ -- source include/have_innodb.inc -- source include/have_binlog_format_statement.inc RESET MASTER; # clear up binlogs -SET @old_isolation_level= @@session.tx_isolation; -SET @@session.tx_isolation= 'READ-COMMITTED'; +SET @old_isolation_level= @@session.transaction_isolation; +SET @@session.transaction_isolation= 'READ-COMMITTED'; -- let $engine= InnoDB -- let $filtered= b42829_filtered @@ -87,4 +87,4 @@ source include/show_binlog_events.inc; -- eval DROP DATABASE $not_filtered -- eval DROP DATABASE $filtered -SET @@session.tx_isolation= @old_isolation_level; +SET @@session.transaction_isolation= @old_isolation_level; diff --git a/mysql-test/suite/binlog/t/binlog_stm_mysqlbinlog_collate_implicit.test b/mysql-test/suite/binlog/t/binlog_stm_mysqlbinlog_collate_implicit.test new file mode 100644 index 00000000..77dad9de --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_stm_mysqlbinlog_collate_implicit.test @@ -0,0 +1,77 @@ +-- source include/have_utf8.inc +-- source include/have_utf8mb4.inc +-- source include/have_ucs2.inc +-- source include/have_binlog_format_statement.inc +-- source include/have_log_bin.inc + +--disable_query_log +CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); +--enable_query_log + +RESET MASTER; +SET timestamp=1000000000; + +--echo # +--echo # MDEV-30164 System variable for default collations +--echo # + +SET character_set_collations='utf8mb3=utf8mb3_bin,ucs2=ucs2_bin'; + +CREATE TABLE t1 (a VARCHAR(20)); +DROP TABLE t1; + +CREATE TABLE t1 (a VARCHAR(20) CHARACTER SET utf8mb4); +DROP TABLE t1; + +CREATE TABLE t1 (a VARCHAR(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin); +DROP TABLE t1; + +CREATE TABLE t1 (a VARCHAR(20) CHARACTER SET utf8mb3); + +INSERT INTO t1 VALUES ('a00'); +INSERT INTO t1 VALUES (_utf8mb3'a01-utf8mb3'); +INSERT INTO t1 VALUES (_utf8mb4'a01-utf8mb4'); + +PREPARE stmt FROM 'INSERT INTO t1 VALUES (?)'; +EXECUTE stmt USING _utf8mb3'a02-utf8mb3'; +EXECUTE stmt USING _utf8mb4'a02-utf8mb4'; + +EXECUTE stmt USING CONVERT('a03-utf8mb3' USING utf8mb3); +EXECUTE stmt USING CONVERT('a03-utf8mb4' USING utf8mb4); + +EXECUTE stmt USING IF(0,CONVERT('a04-utf8mb3' USING utf8mb3),CONVERT('a03-utf8mb4' USING utf8mb4)); +EXECUTE stmt USING IF(1,CONVERT('a04-utf8mb3' USING utf8mb3),CONVERT('a03-utf8mb4' USING utf8mb4)); + +DEALLOCATE PREPARE stmt; + +DROP TABLE t1; + +PREPARE stmt FROM 'CREATE TABLE t1 (a VARCHAR(20) CHARACTER SET utf8mb4)'; +EXECUTE stmt; +DROP TABLE t1; +DEALLOCATE PREPARE stmt; + +PREPARE stmt FROM 'CREATE TABLE t1 (a VARCHAR(20) CHARACTER SET utf8mb3)'; +EXECUTE stmt; +DROP TABLE t1; +DEALLOCATE PREPARE stmt; + + +EXECUTE IMMEDIATE 'CREATE TABLE t1 (a VARCHAR(20) CHARACTER SET utf8mb4)'; +DROP TABLE t1; + +EXECUTE IMMEDIATE 'CREATE TABLE t1 (a VARCHAR(20) CHARACTER SET utf8mb3)'; +DROP TABLE t1; + + +### Starting master-bin.000002 +FLUSH LOGS; + +--disable_query_log +SELECT "--- ---- ---" as ""; +--enable_query_log + +let $MYSQLD_DATADIR= `select @@datadir`; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--replace_regex /SQL_LOAD_MB-[0-9a-f]+-[0-9a-f]+/SQL_LOAD_MB-#-#/ /@@session.sql_mode=\d+/@@session.sql_mode=#/ /collation_server=\d+/collation_server=#/ +--exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ $MYSQLD_DATADIR/master-bin.000001 diff --git a/mysql-test/suite/binlog/t/binlog_table_map_optional_metadata.test b/mysql-test/suite/binlog/t/binlog_table_map_optional_metadata.test index 4577c6c1..ea439730 100644 --- a/mysql-test/suite/binlog/t/binlog_table_map_optional_metadata.test +++ b/mysql-test/suite/binlog/t/binlog_table_map_optional_metadata.test @@ -275,7 +275,7 @@ INSERT INTO t1 VALUES(2, "b"); # The invalid metadata will case assertion failure on Write_rows_log_event # So we need to stop mysqlbinlog before reading Write_rows_log_event. ---let $stop_position= query_get_value(SHOW BINLOG EVENTS FROM $start_pos LIMIT 3, End_log_pos, 3) +--let $stop_position= query_get_value(SHOW BINLOG EVENTS FROM $start_pos LIMIT 4, Pos, 4) --source include/print_optional_metadata.inc --echo # @@ -291,7 +291,7 @@ INSERT INTO t1(c_point) VALUES(ST_PointFromText('POINT(10 10)')); # The invalid metadata will case assertion failure on Write_rows_log_event # So we need to stop mysqlbinlog before reading Write_rows_log_event. ---let $stop_position= query_get_value(SHOW BINLOG EVENTS FROM $start_pos LIMIT 3, End_log_pos, 3) +--let $stop_position= query_get_value(SHOW BINLOG EVENTS FROM $start_pos LIMIT 4, Pos, 4) --source include/print_optional_metadata.inc DROP TABLE t1; diff --git a/mysql-test/suite/binlog/t/binlog_truncate_multi_engine.test b/mysql-test/suite/binlog/t/binlog_truncate_multi_engine.test index 12b0a743..61d097a8 100644 --- a/mysql-test/suite/binlog/t/binlog_truncate_multi_engine.test +++ b/mysql-test/suite/binlog/t/binlog_truncate_multi_engine.test @@ -12,6 +12,7 @@ --source include/have_debug.inc --source include/have_debug_sync.inc --source include/have_binlog_format_row.inc +--source include/not_valgrind.inc --let $old_max_binlog_size= `select @@global.max_binlog_size` call mtr.add_suppression("Can.t init tc log"); diff --git a/mysql-test/suite/binlog/t/flashback.combinations b/mysql-test/suite/binlog/t/flashback.combinations new file mode 100644 index 00000000..51716cbb --- /dev/null +++ b/mysql-test/suite/binlog/t/flashback.combinations @@ -0,0 +1,4 @@ +[use_full_mode] +binlog_row_image=FULL +[use_full_nodup_mode] +binlog_row_image=FULL_NODUP diff --git a/mysql-test/suite/binlog/t/flashback.test b/mysql-test/suite/binlog/t/flashback.test index 8daf3f43..fa20744b 100644 --- a/mysql-test/suite/binlog/t/flashback.test +++ b/mysql-test/suite/binlog/t/flashback.test @@ -372,6 +372,73 @@ DROP TABLE t1; --error 1 # --raw mode and --flashback mode are not allowed --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 +--echo # +--echo # < CASE 8 > +--echo # Verify flashback works well for binlog_row_image full_nodup mode +--echo # +CREATE TABLE t1 ( + c01 TINYINT PRIMARY KEY, + c02 SMALLINT, + c03 MEDIUMINT, + c04 INT, + c05 BIGINT, + c06 CHAR(10), + c07 VARCHAR(20), + c08 TEXT, + c09 ENUM('one', 'two', 'three', 'four', 'five', 'six', 'seven', 'eight'), + c10 SET('black', 'white', 'red', 'yellow'), + c11 TIMESTAMP(3), + c12 DATETIME(3) +) ENGINE = InnoDB; + +INSERT INTO t1 VALUES (1, 1, 1, 1, 1, 'A', 'A', 'A', 'one', 'black', + '2023-11-26 10:00:00.123', '2023-11-26 10:00:00'); +INSERT INTO t1 VALUES (2, 1, 1, 1, 1, 'A', 'A', 'A', 'one', 'black', + '2023-11-26 10:00:00.123', '2023-11-26 10:00:00'); +INSERT INTO t1 VALUES (3, 1, NULL, 1, 1, 'A', 'A', 'A', 'one', 'black', + '2023-11-26 10:00:00.123', NULL); +INSERT INTO t1 VALUES (4, 1, NULL, 1, 1, 'A', 'A', 'A', 'one', 'black', + '2023-11-26 10:00:00.123', NULL); + +--let $checksum_old= `CHECKSUM TABLE t1` + +FLUSH BINARY LOGS; +--echo # The update includes the cases that +--echo # Value -> Value +--echo # Value -> NULL +--echo # NULL -> value +--echo # and the changed null bits in both first and second null byte + +UPDATE t1 SET c02 = NULL, c03 = 2, c09 = 'two', + c10 = NULL, c12 = '2023-11-26 11:00:00'; +--let $master_file= query_get_value("SHOW MASTER STATUS", File, 1) +--let $MYSQLD_DATADIR= `select @@datadir` +FLUSH BINARY LOGS; + +--echo # +--echo # Data before flashback +--echo # +SELECT * FROM t1; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--exec $MYSQL_BINLOG -B -vv $MYSQLD_DATADIR/$master_file > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback.sql +--exec $MYSQL -e "SET binlog_format= ROW; source $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback.sql;" + +--echo # +--echo # Data after flashback +--echo # +SELECT * FROM t1; + +# After flashback, t1's checksum should be same to original checksum +--let $checksum_new = `CHECKSUM TABLE t1` +if ($checksum_new != $checksum_old) +{ + --die "After flashback, t1's checksum is different from the original checksum" +} + +DROP TABLE t1; +--remove_file $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback.sql + ## Clear SET binlog_format=statement; --error ER_FLASHBACK_NOT_SUPPORTED diff --git a/mysql-test/suite/binlog/t/max_binlog_total_size-master.opt b/mysql-test/suite/binlog/t/max_binlog_total_size-master.opt new file mode 100644 index 00000000..9ba64a44 --- /dev/null +++ b/mysql-test/suite/binlog/t/max_binlog_total_size-master.opt @@ -0,0 +1,5 @@ +--log-bin=binary +--max-binlog-total-size=1500 +--max-binlog-size=4096 +--binlog-format=row +--slave_connections_needed_for_purge=1 diff --git a/mysql-test/suite/binlog/t/max_binlog_total_size.test b/mysql-test/suite/binlog/t/max_binlog_total_size.test new file mode 100644 index 00000000..19e6131b --- /dev/null +++ b/mysql-test/suite/binlog/t/max_binlog_total_size.test @@ -0,0 +1,94 @@ +--source include/have_log_bin.inc +select @@global.max_binlog_total_size; +select @@global.max_binlog_size; + +# Note that this test is only using MyISAM tables. +# The reason for this is that we do not want to have engine +# checkpoints in the binary log as it would change the number of +# bytes in the log, which could cause random rotations. + +--echo # +--echo # MDEV-31404 Implement binlog_space_limit +--echo # + +FLUSH LOGS; +FLUSH LOGS; +FLUSH LOGS; +source include/show_binary_logs.inc; +show status like "binlog_disk_use"; +set @@global.slave_connections_needed_for_purge= 0; +--echo # binary.000001 should be deleted now +source include/show_binary_logs.inc; +show status like "binlog_disk_use"; + +CREATE TABLE `t1` ( + `v1` int(11) DEFAULT NULL, + `v2` varchar(8000) DEFAULT NULL, + KEY `v1` (`v1`) +) engine=myisam; +INSERT INTO t1 VALUES (0,repeat("a",3000)); +show status like "binlog_disk_use"; +--echo # First binary should be binary.000004 +source include/show_binary_logs.inc; + +INSERT INTO t1 VALUES (2,repeat("b",10)); +--echo # First binary should be binary.000004 +# The reson why we have logs 00004 and 00005 at this point is that we first +# do a rotate and then try to purge. However as there are still existing +# xid's pointing to the 00004, we cannot yet purge 000004. +# After rotate a checkpoint record will be written to 00005 which +# will release pointers to 00004. On next binary log write or flush +# the purge will be retried and succeed. + +source include/show_binary_logs.inc; + +FLUSH LOGS; +--echo # First binary should be binary.000005 +source include/show_binary_logs.inc; + +FLUSH LOGS; +FLUSH LOGS; +FLUSH LOGS; +FLUSH LOGS; +source include/show_binary_logs.inc; +show status like "binlog_disk_use"; + +PURGE BINARY LOGS TO 'binary.000009'; +--echo # First binary should be binary.000009 +source include/show_binary_logs.inc; + +INSERT INTO t1 VALUES (3,repeat("c",4000)); +--echo # First binary should be binary.000010 +source include/show_binary_logs.inc; + +INSERT INTO t1 VALUES (4,repeat("d",3000)); +--echo # First binary should be binary.000011 +source include/show_binary_logs.inc; + +RESET MASTER; +source include/show_binary_logs.inc; +show status like "binlog_disk_use"; + +INSERT INTO t1 VALUES (5,"e"); +FLUSH LOGS; +INSERT INTO t1 VALUES (6,repeat("f",3000)); +source include/show_binary_logs.inc; +show status like "binlog_disk_use"; + +INSERT INTO t1 VALUES (7,repeat("g",3000)); +--echo # binary.000001 should be deleted now +source include/show_binary_logs.inc; +show status like "binlog_disk_use"; + +FLUSH LOGS; +FLUSH LOGS; +--echo # binary.000002 should be deleted now +show binary logs; +show status like "binlog_disk_use"; + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +set @@global.slave_connections_needed_for_purge= default; +# End of 11.4 tests |