rpl_binlog_max_cache_size.test

########################################################################################
#    This test does two things. First it verifies if the binlog is not corrupted when
#    the cache buffer is not big enough to accommodate the changes and is divided in
#    five steps:
#
#    1 - Single Statements:
#      1.1 - Single statement on transactional table.
#      1.2 - Single statement on non-transactional table. 
#      1.3 - Single statement on both transactional and non-transactional tables.
#
#    In both 1.2 and 1.3, an incident event is logged to notify the user that the
#    master and slave are diverging.
#
#    2 - Transactions ended by an implicit commit.
#
#    3 - Transactions ended by a COMMIT.
#
#    4 - Transactions ended by a ROLLBACK.
#
#    5 - Transactions with a failing statement that updates a non-transactional
#    table. In this case, a failure means that the statement does not get into
#    the cache and an incident event is logged to notify the user that the master
#    and slave are diverging.
#
#    Then it checks what happens when we try to set BINLOG_CACHE_SIZE to a value that
#    is greater than MAX_BINLOG_CACHE_SIZE and vice-versa. In both cases, We expect
#    that BINLOG_CACHE_SIZE will be set to MAX_BINLOG_CACHE_SIZE. (BUG#55377)
########################################################################################
--source include/have_myisam.inc
call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
call mtr.add_suppression("Option binlog_cache_size ");
call mtr.add_suppression("Replica SQL for channel '': The incident LOST_EVENTS occurred on the source.");
call mtr.add_suppression("Non-transactional changes were not written to the binlog. An incident event has been written to the binary log which will stop the replicas.");

let $old_max_binlog_cache_size= query_get_value(SHOW VARIABLES LIKE "max_binlog_cache_size", Value, 1);
let $old_binlog_cache_size= query_get_value(SHOW VARIABLES LIKE "binlog_cache_size", Value, 1);
let $old_max_binlog_stmt_cache_size= query_get_value(SHOW VARIABLES LIKE "max_binlog_stmt_cache_size", Value, 1);
let $old_binlog_stmt_cache_size= query_get_value(SHOW VARIABLES LIKE "binlog_stmt_cache_size", Value, 1);

SET GLOBAL max_binlog_cache_size = 4096;
SET GLOBAL binlog_cache_size = 4096;
SET GLOBAL max_binlog_stmt_cache_size = 4096;
SET GLOBAL binlog_stmt_cache_size = 4096;
disconnect master;
connect (master,127.0.0.1,root,,test,$MASTER_MYPORT,);

CREATE TABLE t1(a INT PRIMARY KEY, data VARCHAR(30000)) ENGINE=Innodb CHARACTER SET latin1;
CREATE TABLE t2(a INT PRIMARY KEY, data VARCHAR(30000)) ENGINE=MyIsam CHARACTER SET latin1;
CREATE TABLE t3(a INT PRIMARY KEY, data VARCHAR(30000)) ENGINE=Innodb CHARACTER SET latin1;

let $data = `select concat('"', repeat('a',2000), '"')`;

--echo ###### 1 - SINGLE STATEMENT  ######

connection master;

--echo *** Single statement on transactional table ***
--disable_query_log
--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
eval INSERT INTO t1 (a, data) VALUES (1,
     CONCAT($data, $data, $data, $data, $data));
--enable_query_log

--echo *** Single statement on non-transactional table ***
--disable_query_log
--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
eval INSERT INTO t2 (a, data) VALUES (2,
       CONCAT($data, $data, $data, $data, $data, $data));
--enable_query_log

# Incident event
# 13119=ER_REPLICA_INCIDENT
--let $slave_sql_errno= 13119
--source include/rpl/wait_for_applier_error_and_skip.inc

--disable_query_log
eval INSERT INTO t1 (a, data) VALUES (3, $data);
eval INSERT INTO t1 (a, data) VALUES (4, $data);
eval INSERT INTO t1 (a, data) VALUES (5, $data);
eval INSERT INTO t2 (a, data) VALUES (3, $data);
eval INSERT INTO t2 (a, data) VALUES (4, $data);
eval INSERT INTO t2 (a, data) VALUES (5, $data);
--enable_query_log

--echo *** Single statement on both transactional and non-transactional tables. ***
--disable_query_log
--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
eval UPDATE t2, t1 SET t2.data = CONCAT($data, $data, $data, $data),
                       t1.data = CONCAT($data, $data, $data, $data);
--enable_query_log

# 13119=ER_REPLICA_INCIDENT
--let $slave_sql_errno= 13119
--let $slave_skip_counter= `SELECT IF(@@binlog_format = 'ROW', 2, 1)`
--source include/rpl/wait_for_applier_error_and_skip.inc

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

--echo #########  2 - BEGIN - IMPLICIT COMMIT by DDL #########

connection master;
TRUNCATE TABLE t1;
TRUNCATE TABLE t2;
TRUNCATE TABLE t3;

BEGIN;
--disable_query_log
--eval INSERT INTO t1 (a, data) VALUES (1, $data);
--eval INSERT INTO t1 (a, data) VALUES (2, $data);
--eval INSERT INTO t1 (a, data) VALUES (3, $data);
--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
--eval INSERT INTO t1 (a, data) VALUES (4, $data);
--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
--eval INSERT INTO t1 (a, data) VALUES (5, $data);
--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
--eval INSERT INTO t1 (a, data) VALUES (6, $data);
--eval INSERT INTO t1 (a, data) VALUES (7, 's');
--eval INSERT INTO t2 (a, data) VALUES (8, 's');
--eval INSERT INTO t1 (a, data) VALUES (9, 's');
--enable_query_log

--disable_query_log
ALTER TABLE t3 ADD COLUMN d int;
--enable_query_log

--disable_query_log
--eval INSERT INTO t2 (a, data) VALUES (10, $data);
--eval INSERT INTO t2 (a, data) VALUES (11, $data);
--eval INSERT INTO t2 (a, data) VALUES (12, $data);
--eval INSERT INTO t2 (a, data) VALUES (13, $data);
--enable_query_log

BEGIN;
--disable_query_log
--eval INSERT INTO t1 (a, data) VALUES (14, $data);
--eval INSERT INTO t1 (a, data) VALUES (15, $data);
--eval INSERT INTO t1 (a, data) VALUES (16, $data);
--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
--eval INSERT INTO t1 (a, data) VALUES (17, $data);
--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
--eval INSERT INTO t1 (a, data) VALUES (18, $data);
--eval INSERT INTO t1 (a, data) VALUES (19, 's');
--eval INSERT INTO t2 (a, data) VALUES (20, 's');
--eval INSERT INTO t1 (a, data) VALUES (21, 's');
--enable_query_log

if (`SELECT @@binlog_format = 'STATEMENT' || @@binlog_format = 'MIXED'`)
{
  --disable_query_log
  CREATE TABLE t4 SELECT * FROM t1;
  --enable_query_log
}
if (`SELECT @@binlog_format = 'ROW'`)
{
  --disable_query_log
  --error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
  CREATE TABLE t4 SELECT * FROM t1;
  --enable_query_log
}

--disable_query_log
--eval INSERT INTO t2 (a, data) VALUES (15, $data);
--enable_query_log

BEGIN;
--disable_query_log
--eval INSERT INTO t1 (a, data) VALUES (22, $data);
--eval INSERT INTO t1 (a, data) VALUES (23, $data);
--eval INSERT INTO t1 (a, data) VALUES (24, $data);
--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
--eval INSERT INTO t1 (a, data) VALUES (25, $data);
--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
--eval INSERT INTO t1 (a, data) VALUES (26, $data);
--eval INSERT INTO t1 (a, data) VALUES (27, 's');
--eval INSERT INTO t2 (a, data) VALUES (28, 's');
--eval INSERT INTO t1 (a, data) VALUES (29, 's');
--enable_query_log

--disable_query_log
CREATE TABLE t5 (a int);
--enable_query_log

--sync_slave_with_master
--let $diff_tables= master:t1,slave:t1
--source include/diff_tables.inc

--echo ###### 3 - BEGIN - COMMIT ######

connection master;
TRUNCATE TABLE t1;
TRUNCATE TABLE t2;
TRUNCATE TABLE t3;

BEGIN;
--disable_query_log
--eval INSERT INTO t1 (a, data) VALUES (1, $data);
--eval INSERT INTO t1 (a, data) VALUES (2, $data);
--eval INSERT INTO t1 (a, data) VALUES (3, $data);
--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
--eval INSERT INTO t1 (a, data) VALUES (4, $data);
--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
--eval INSERT INTO t1 (a, data) VALUES (5, $data);
--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
--eval INSERT INTO t1 (a, data) VALUES (6, $data);
--eval INSERT INTO t1 (a, data) VALUES (7, 's');
--eval INSERT INTO t2 (a, data) VALUES (8, 's');
--eval INSERT INTO t1 (a, data) VALUES (9, 's');
--enable_query_log
COMMIT;

--sync_slave_with_master
--let $diff_tables= master:t1,slave:t1
--source include/diff_tables.inc

--echo ####### 4 - BEGIN - ROLLBACK  #######

connection master;
TRUNCATE TABLE t1;
TRUNCATE TABLE t2;
TRUNCATE TABLE t3;

BEGIN;
--disable_query_log
--eval INSERT INTO t1 (a, data) VALUES (1, $data);
--eval INSERT INTO t1 (a, data) VALUES (2, $data);
--eval INSERT INTO t1 (a, data) VALUES (3, $data);
--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
--eval INSERT INTO t1 (a, data) VALUES (4, $data);
--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
--eval INSERT INTO t1 (a, data) VALUES (5, $data);
--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
--eval INSERT INTO t1 (a, data) VALUES (6, $data);
--eval INSERT INTO t1 (a, data) VALUES (7, 's');
--eval INSERT INTO t2 (a, data) VALUES (8, 's');
--eval INSERT INTO t1 (a, data) VALUES (9, 's');
--enable_query_log
ROLLBACK;

--sync_slave_with_master
--let $diff_tables= master:t1,slave:t1
--source include/diff_tables.inc

--echo ###### 5 - PROCEDURE  #######

connection master;
TRUNCATE TABLE t1;
TRUNCATE TABLE t2;
TRUNCATE TABLE t3;

DELIMITER //;

CREATE PROCEDURE p1(pd VARCHAR(30000) CHARACTER SET latin1)
BEGIN
  INSERT INTO t1 (a, data) VALUES (1, pd);
  INSERT INTO t1 (a, data) VALUES (2, pd);
  INSERT INTO t1 (a, data) VALUES (3, pd);
  INSERT INTO t1 (a, data) VALUES (4, pd);
  INSERT INTO t1 (a, data) VALUES (5, 's');
END//

DELIMITER ;//

TRUNCATE TABLE t1;

--disable_query_log
eval CALL p1($data);
--enable_query_log

TRUNCATE TABLE t1;

BEGIN;
--disable_query_log
--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
eval CALL p1($data);
--enable_query_log
COMMIT;

TRUNCATE TABLE t1;

BEGIN;
--disable_query_log
--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
eval CALL p1($data);
--enable_query_log
ROLLBACK;

--sync_slave_with_master
--let $diff_tables= master:t1,slave:t1
--source include/diff_tables.inc

--echo ####### 6 - XID ######

connection master;
TRUNCATE TABLE t1;
TRUNCATE TABLE t2;
TRUNCATE TABLE t3;

BEGIN;
--disable_query_log
--eval INSERT INTO t1 (a, data) VALUES (1, $data);
--eval INSERT INTO t1 (a, data) VALUES (2, $data);
--eval INSERT INTO t1 (a, data) VALUES (3, $data);
--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
--eval INSERT INTO t1 (a, data) VALUES (4, $data);
SAVEPOINT sv;
--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
--eval INSERT INTO t1 (a, data) VALUES (5, $data);
--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
--eval INSERT INTO t1 (a, data) VALUES (6, $data);
--eval INSERT INTO t1 (a, data) VALUES (7, 's');
--eval INSERT INTO t2 (a, data) VALUES (8, 's');
--eval INSERT INTO t1 (a, data) VALUES (9, 's');
--enable_query_log
ROLLBACK TO sv;
COMMIT;

--sync_slave_with_master
--let $diff_tables= master:t1,slave:t1
--source include/diff_tables.inc

--echo ####### 7 - NON-TRANS TABLE #######

connection master;
TRUNCATE TABLE t1;
TRUNCATE TABLE t2;
TRUNCATE TABLE t3;

BEGIN;
--disable_query_log
--eval INSERT INTO t1 (a, data) VALUES (1, $data);
--eval INSERT INTO t1 (a, data) VALUES (2, $data);
--eval INSERT INTO t2 (a, data) VALUES (3, $data);
--eval INSERT INTO t1 (a, data) VALUES (4, $data);
--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
--eval INSERT INTO t1 (a, data) VALUES (5, $data);
--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
--eval INSERT INTO t1 (a, data) VALUES (6, $data);
--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
--eval INSERT INTO t1 (a, data) VALUES (7, $data);
--eval UPDATE t2 SET data= CONCAT($data, $data);
--eval INSERT INTO t1 (a, data) VALUES (8, 's');
--eval INSERT INTO t1 (a, data) VALUES (9, 's');
--eval INSERT INTO t2 (a, data) VALUES (10, 's');
--eval INSERT INTO t1 (a, data) VALUES (11, 's');
--enable_query_log
COMMIT;

BEGIN;
--disable_query_log
--eval INSERT INTO t1 (a, data) VALUES (15, $data);
--eval INSERT INTO t1 (a, data) VALUES (16, $data);
--eval INSERT INTO t2 (a, data) VALUES (17, $data);
--eval INSERT INTO t1 (a, data) VALUES (18, $data);
--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
--eval INSERT INTO t1 (a, data) VALUES (19, $data);
--enable_query_log
COMMIT;

--sync_slave_with_master
--let $diff_tables= master:t1,slave:t1
--source include/diff_tables.inc

--echo ####### 8 - LOAD DATA INFILE INTO TABLE #######

#
# This scenario verifies that load data infile fails when
# binlog cache exceeds max_binlog_cache_size
#
--source include/rpl/connection_source.inc
CREATE TABLE t6 (a varchar(20)) ENGINE=Innodb;

# Save the current binary log position
--let $pos_before= query_get_value(show binary log status,Position,1)

--disable_query_log
--let $write_var =`SELECT REPEAT('Testing\n', 1000)`
--let $write_to_file = GENERATE
--source include/write_var_to_file.inc
--enable_query_log

# Below transaction generates transaction cache more than
# the max_binlog_cache_size i.e 4096. Hence results in error.
--replace_result $write_to_file temp_file
--error ER_TRANS_CACHE_FULL
eval LOAD DATA INFILE '$write_to_file' INTO TABLE t6;

--exec rm $write_to_file

# Check that the above transaction has not been logged in the binary log
--let $assert_text= assert that the above Event has not been added to binlog
--let $assert_cond= [SHOW BINARY LOG STATUS, Position,1] = $pos_before
--source include/assert.inc

# Check that the table is empty on master
--let $assert_text = Check that the LOAD DATA didn't add any data into the table
--let $assert_cond = [SELECT COUNT(*) FROM t6] = 0
--source include/assert.inc

--source include/rpl/sync_to_replica.inc

# Check that the table is empty on slave
--let $assert_text = Check that the LOAD DATA didn't add any data into the table
--let $assert_cond = [SELECT COUNT(*) FROM t6] = 0
--source include/assert.inc

--source include/rpl/connection_source.inc
DROP TABLE t6;
--source include/rpl/sync_to_replica.inc

--echo ######### 9 - Bug#55375(Regression Bug) Transaction bigger than  ##########
--echo #########     max_binlog_cache_size crashes slave                ##########

--echo # [ On Slave ]
SET GLOBAL max_binlog_cache_size = 4096;
SET GLOBAL binlog_cache_size = 4096;
SET GLOBAL max_binlog_stmt_cache_size = 4096;
SET GLOBAL binlog_stmt_cache_size = 4096;

source include/rpl/stop_replica.inc;
source include/rpl/start_replica.inc;
CALL mtr.add_suppression("Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage.*");
CALL mtr.add_suppression("Multi-statement transaction required more than 'max_binlog_stmt_cache_size' bytes of storage.*");
CALL mtr.add_suppression("Writing one row to the row-based binary log failed.*");
CALL mtr.add_suppression("Replica SQL.*The incident LOST_EVENTS occurred on the source. Message: error writing to the binary log");
call mtr.add_suppression("The replica coordinator and worker threads are stopped, possibly leaving data in inconsistent state");

connection master;
TRUNCATE t1;

sync_slave_with_master;
--let binlog_start= query_get_value(SHOW BINARY LOG STATUS, Position, 1)
--let binlog_file= query_get_value(SHOW BINARY LOG STATUS, File, 1)

connection master;
--replace_result $old_max_binlog_cache_size ORIGINAL_VALUE
--eval SET GLOBAL max_binlog_cache_size= $old_max_binlog_cache_size
--replace_result $old_binlog_cache_size ORIGINAL_VALUE
--eval SET GLOBAL binlog_cache_size= $old_binlog_cache_size
--replace_result $old_max_binlog_stmt_cache_size ORIGINAL_VALUE
--eval SET GLOBAL max_binlog_stmt_cache_size= $old_max_binlog_stmt_cache_size
--replace_result $old_binlog_stmt_cache_size ORIGINAL_VALUE
--eval SET GLOBAL binlog_stmt_cache_size= $old_binlog_stmt_cache_size
disconnect master;
connect (master,127.0.0.1,root,,test,$MASTER_MYPORT,);

--let $n=128
BEGIN;
--disable_query_log
--echo Repeat statement 'INSERT INTO t1 VALUES(\$n, repeat("a", 32))' $n times
while ($n)
{
  --eval INSERT INTO t1 VALUES ($n, repeat("a", 32))
  --dec $n
}
--enable_query_log
COMMIT;

--connection slave
--let $slave_sql_errno= 1534,1197
source include/rpl/wait_for_applier_error.inc;

SELECT count(*) FROM t1;
source include/rpl/deprecated/show_binlog_events.inc;

--replace_result $old_max_binlog_cache_size ORIGINAL_VALUE
--eval SET GLOBAL max_binlog_cache_size= $old_max_binlog_cache_size
--replace_result $old_binlog_cache_size ORIGINAL_VALUE
--eval SET GLOBAL binlog_cache_size= $old_binlog_cache_size
--replace_result $old_max_binlog_stmt_cache_size ORIGINAL_VALUE
--eval SET GLOBAL max_binlog_stmt_cache_size= $old_max_binlog_stmt_cache_size
--replace_result $old_binlog_stmt_cache_size ORIGINAL_VALUE
--eval SET GLOBAL binlog_stmt_cache_size= $old_binlog_stmt_cache_size

source include/rpl/stop_replica.inc;
source include/rpl/start_replica.inc;

connection master;
sync_slave_with_master;
SELECT count(*) FROM t1;

--echo ######## CHECK MAX_BINLOG_CACHE_SIZE and BINLOG_CACHE_SIZE #########
#
# Checking what happens when we try to set BINLOG_CACHE_SIZE to a value that is
# greater than MAX_BINLOG_CACHE_SIZE and vice-versa. In both cases, We expect
# that BINLOG_CACHE_SIZE will be set to MAX_BINLOG_CACHE_SIZE. (BUG#55377)
# 
connection master;

SET @@GLOBAL.MAX_BINLOG_CACHE_SIZE= 8192;
SHOW VARIABLES LIKE 'MAX_BINLOG_CACHE_SIZE';
SHOW VARIABLES LIKE 'BINLOG_CACHE_SIZE';
SET @@GLOBAL.BINLOG_CACHE_SIZE= 16384;
SHOW VARIABLES LIKE 'MAX_BINLOG_CACHE_SIZE';
SHOW VARIABLES LIKE 'BINLOG_CACHE_SIZE';
SET @@GLOBAL.MAX_BINLOG_CACHE_SIZE= 4096;
SHOW VARIABLES LIKE 'MAX_BINLOG_CACHE_SIZE';
SHOW VARIABLES LIKE 'BINLOG_CACHE_SIZE';

--echo ######### CLEAN #########

--disable_query_log
--eval SET GLOBAL max_binlog_cache_size= $old_max_binlog_cache_size
--eval SET GLOBAL binlog_cache_size= $old_binlog_cache_size
--enable_query_log

DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
# t4 exists only if binlog_format!='row', so so a warning is generated
# if binog_format='row'
--disable_warnings
DROP TABLE IF EXISTS t4;
--enable_warnings
DROP TABLE t5;
DROP PROCEDURE p1;