mariadb/mysql-test/suite/rpl/t/rpl_checksum_cache.test

#
# This include file is used by more than one test suite
# (currently rpl and binlog_encryption).
# Please check all dependent tests after modifying it
#

-- source include/have_innodb.inc
-- source include/master-slave.inc

--disable_warnings
call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. .*Statement: insert into t2 set data=repeat.*'a', @act_size.*");
call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. .*Statement: insert into t1 values.* NAME_CONST.*'n',.*, @data .*");
--enable_warnings

connection master;
set @save_binlog_cache_size = @@global.binlog_cache_size;
set @save_binlog_checksum = @@global.binlog_checksum;
set @save_master_verify_checksum = @@global.master_verify_checksum;
set @@global.binlog_cache_size = 4096;
set @@global.binlog_checksum = CRC32;
set @@global.master_verify_checksum = 1;

# restart slave to force the dump thread to verify events (on master side)
connection slave;
source include/stop_slave.inc;
source include/start_slave.inc;

connection master;

#
# Testing a critical part of checksum handling dealing with transaction cache.
# The cache's buffer size is set to be less than the transaction's footprint
# in binlog.
#
# To verify combined buffer-by-buffer read out of the file and fixing crc per event
# there are the following parts:
#
# 1. the event size is much less than the cache's buffer
# 2. the event size is bigger than the cache's buffer
# 3. the event size if approximately the same as the cache's buffer
# 4. all in above

#
# 1. the event size is much less than the cache's buffer
#

flush status;
show status like "binlog_cache_use";
show status like "binlog_cache_disk_use";
--disable_warnings
drop table if exists t1;
--enable_warnings

#
# parameter to ensure the test slightly varies binlog content
# between different invocations
#
let $deviation_size=32;
eval create table t1 (a int PRIMARY KEY, b CHAR($deviation_size)) engine=innodb;

# Now we are going to create transaction which is long enough so its 
# transaction binlog will be flushed to disk...

delimiter |;
create procedure test.p_init (n int, size int) 
begin
  while n > 0 do
    select round(RAND() * size) into @act_size;
    set @data = repeat('a', @act_size);
    insert into t1 values(n, @data );
    set n= n-1;
  end while;
end|

delimiter ;|

let $1 = 4000; # PB2 can run it slow to time out on following sync_slave_with_master:s

begin;
--disable_warnings
# todo: check if it is really so.
#+Note   1592    Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Statement uses a system function whose value may differ on slave.
eval call test.p_init($1, $deviation_size);
--enable_warnings
commit;

show status like "binlog_cache_use";
--echo *** binlog_cache_disk_use must be non-zero ***
show status like "binlog_cache_disk_use";

sync_slave_with_master;

let $diff_tables=master:test.t1, slave:test.t1;
source include/diff_tables.inc;

# undoing changes with verifying the above once again
connection master;

begin;
delete from t1;
commit;

sync_slave_with_master;


#
# 2. the event size is bigger than the cache's buffer
#
connection master;

flush status;
let $t2_data_size= `select 3 * @@global.binlog_cache_size`;
let $t2_aver_size= `select 2 * @@global.binlog_cache_size`;
let $t2_max_rand=  `select 1 * @@global.binlog_cache_size`;

eval create table t2(a int auto_increment primary key, data VARCHAR($t2_data_size)) ENGINE=Innodb;
let $1=100;
--disable_query_log
begin;
while ($1)
{
 --disable_cursor_protocol
 eval select round($t2_aver_size + RAND() * $t2_max_rand) into @act_size;
 --enable_cursor_protocol
 set @data = repeat('a', @act_size);
 insert into t2 set data = @data;
 dec $1;
}
commit;
--enable_query_log
show status like "binlog_cache_use";
--echo *** binlog_cache_disk_use must be non-zero ***
show status like "binlog_cache_disk_use";

sync_slave_with_master;

let $diff_tables=master:test.t2, slave:test.t2;
source include/diff_tables.inc;

# undoing changes with verifying the above once again
connection master;

begin;
delete from t2;
commit;

sync_slave_with_master;

#
# 3. the event size if approximately the same as the cache's buffer
#

connection master;

flush status;
let $t3_data_size= `select 2 * @@global.binlog_cache_size`;
let $t3_aver_size= `select (9 * @@global.binlog_cache_size) / 10`;
let $t3_max_rand=  `select (2 * @@global.binlog_cache_size) / 10`;

eval create table t3(a int auto_increment primary key, data VARCHAR($t3_data_size)) engine=innodb;

let $1= 300;
--disable_query_log
begin;
while ($1)
{
 --disable_cursor_protocol
 eval select round($t3_aver_size + RAND() * $t3_max_rand) into @act_size;
 --enable_cursor_protocol
 insert into t3 set data= repeat('a', @act_size);
 dec $1;
}
commit;
--enable_query_log
show status like "binlog_cache_use";
--echo *** binlog_cache_disk_use must be non-zero ***
show status like "binlog_cache_disk_use";

sync_slave_with_master;

let $diff_tables=master:test.t3, slave:test.t3;
source include/diff_tables.inc;

# undoing changes with verifying the above once again
connection master;

begin;
delete from t3;
commit;

sync_slave_with_master;


#
# 4. all in above
#

connection master;
flush status;

delimiter |;
eval create procedure test.p1 (n int) 
begin
  while n > 0 do
    case (select (round(rand()*100) % 3) + 1)
    when 1 then
      select round(RAND() * $deviation_size) into @act_size;
      set @data = repeat('a', @act_size);
      insert into t1 values(n, @data);
    when 2 then
      begin
        select round($t2_aver_size + RAND() * $t2_max_rand) into @act_size;
	insert into t2 set data=repeat('a', @act_size);
      end;
    when 3 then
      begin
        select round($t3_aver_size + RAND() * $t3_max_rand) into @act_size;
	insert into t3 set data= repeat('a', @act_size);
      end;
    end case;
    set n= n-1;
  end while;
end|
delimiter ;|

let $1= 1000;
set autocommit= 0;
begin;
--disable_warnings
eval call test.p1($1);
--enable_warnings
commit;

show status like "binlog_cache_use";
--echo *** binlog_cache_disk_use must be non-zero ***
show status like "binlog_cache_disk_use";

sync_slave_with_master;

let $diff_tables=master:test.t1, slave:test.t1;
source include/diff_tables.inc;

let $diff_tables=master:test.t2, slave:test.t2;
source include/diff_tables.inc;

let $diff_tables=master:test.t3, slave:test.t3;
source include/diff_tables.inc;


--echo *** Test switching checksum algorithm while ongoing transactions have pre-computed checksum in their binlog cache ***

--connection master
CREATE TABLE t4 (a INT, b INT, c VARCHAR(1024), PRIMARY KEY (a,b)) ENGINE=InnoDB;

# Create a couple transactions that will precompute checksums but commit
# without them.

BEGIN;
INSERT INTO t4 VALUES (1, 1, "small, pre-computed checksums");

--connection server_1
BEGIN;
INSERT INTO t4 VALUES (2, 1, "big, pre-computed checksums");
--let $i= 20
--disable_query_log
while ($i) {
  eval INSERT INTO t4 VALUES (2, 22-$i, REPEAT("x", FLOOR(RAND()*100) + 831));
  dec $i;
}
--enable_query_log

# Disable checksums dynamically, so MYSQL_BIN_LOG::write_cache() will have
# to drop the pre-computed checksums.
set @@global.binlog_checksum = NONE;

--connection master
INSERT INTO t4 VALUES (1, 2, "done");
COMMIT;
--connection server_1
INSERT INTO t4 VALUES (2, 22, "done");
COMMIT;

# Create a couple transactions that will not precompute checksums but commit
# with them.

--connection master
BEGIN;
INSERT INTO t4 VALUES (3, 1, "small, no pre-computed checksums");

--connection server_1
BEGIN;
INSERT INTO t4 VALUES (4, 1, "big, no pre-computed checksums");
--let $i= 20
--disable_query_log
while ($i) {
  eval INSERT INTO t4 VALUES (4, 22-$i, REPEAT("x", FLOOR(RAND()*100) + 853));
  dec $i;
}
--enable_query_log

# Ebable checksums dynamically, so MYSQL_BIN_LOG::write_cache() will have
# to recompute the checksums.
set @@global.binlog_checksum = CRC32;

--connection master
INSERT INTO t4 VALUES (3, 2, "done");
COMMIT;
--connection server_1
INSERT INTO t4 VALUES (4, 22, "done");
COMMIT;

sync_slave_with_master;


--echo *** Test the --binlog-legacy-event-pos option.
--connection master
FLUSH BINARY LOGS;
--source include/wait_for_binlog_checkpoint.inc

--let $query_file= query_get_value(SHOW MASTER STATUS, File, 1)
--let $query_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
BEGIN;
INSERT INTO t4 VALUES (5, 1, "Zero end_log_pos");
COMMIT;
--let $end_log_pos= query_get_value(SHOW BINLOG EVENTS IN "$query_file" FROM $query_pos LIMIT 3, End_log_pos, 2)
if ($end_log_pos!=0) {
  eval SHOW BINLOG EVENTS IN "$query_file";
  --die Wrong End_log_pos=$end_log_pos, expected zero.
}

set @@global.binlog_legacy_event_pos= 1;
--let $query_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
BEGIN;
INSERT INTO t4 VALUES (6, 1, "Non-zero end_log_pos");
COMMIT;
--let $end_log_pos= query_get_value(SHOW BINLOG EVENTS IN "$query_file" FROM $query_pos LIMIT 3, End_log_pos, 2)
if ($end_log_pos==0) {
  eval SHOW BINLOG EVENTS IN "$query_file";
  --die Wrong End_log_pos=$end_log_pos, expected non-zero.
}
set @@global.binlog_legacy_event_pos= 0;

sync_slave_with_master;


connection master;

begin;
delete from t1;
delete from t2;
delete from t3;
commit;

drop table t1, t2, t3, t4;
set @@global.binlog_cache_size = @save_binlog_cache_size;
set @@global.binlog_checksum = @save_binlog_checksum;
set @@global.master_verify_checksum = @save_master_verify_checksum;
drop procedure test.p_init;
drop procedure test.p1;

--source include/rpl_end.inc