include/master-slave.inc
[connection master]
call mtr.add_suppression("Unsafe statement written to the binary log using statement format");
connection slave;
call mtr.add_suppression("Unsafe statement written to the binary log using statement format");
include/stop_slave.inc
CHANGE MASTER TO MASTER_USE_GTID=NO;
include/start_slave.inc
connection master;
[on master]
CREATE TABLE t1 (a VARCHAR(100), b INT);
INSERT INTO t1 VALUES ("zero", 0);
==== Normal setup ====
[on slave]
connection slave;
include/stop_slave.inc
# CHANGE MASTER TO MASTER_DELAY = 2*T
include/start_slave.inc
# Asserted this: SHOW SLAVE STATUS should return the same delay that we set with CHANGE MASTER
[on master]
connection master;
INSERT INTO t1 VALUES ('normal setup', 1);
connection master;
[on slave]
include/sync_slave_io_with_master.inc
# sleep 1*T
# Asserted this: Query 1 should not be executed
# Asserted this: Status should be 'Waiting until MASTER_DELAY...'
# sleep 1*T
# sync with master (with timeout 1*T)
include/wait_for_slave_param.inc [Relay_Master_Log_File]
include/wait_for_slave_param.inc [Exec_Master_Log_Pos]
# Asserted this: Query 1 should be executed
# Asserted this: Status should be 'Has read all relay log...'
include/check_slave_is_running.inc
==== Slave lags "naturally" after master ====
[on master]
connection master;
# CREATE FUNCTION delay_on_slave(time_units INT) RETURNS INT BEGIN IF @@GLOBAL.server_id = 2 THEN RETURN SLEEP(time_units * T); ELSE RETURN 0; END IF; END
INSERT INTO t1 SELECT delay_on_slave(3), 2;
Warnings:
Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
INSERT INTO t1 VALUES ('slave is already lagging: this statement should execute immediately', 3);
INSERT INTO t1 SELECT delay_on_slave(2), 4;
Warnings:
Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
[on slave]
include/sync_slave_io_with_master.inc
# sleep 1*T
# Asserted this: No query executed
# Asserted this: Status should be 'Waiting until MASTER_DELAY...'
# wait for first query to execute
# sleep 1*T
# Asserted this: Second query executed
# Asserted this: Status should be executing third query (i.e., 'User sleep')
# sleep 2*T
# Asserted this: Third query executed
# Asserted this: Status should be 'Has read all relay log...'
==== Seconds_Behind_Master ====
# Bring slave to sync.
include/stop_slave.inc
CHANGE MASTER TO MASTER_DELAY = 0;
include/start_slave.inc
connection master;
INSERT INTO t1 VALUES ('Syncing slave', 5);
connection slave;
include/stop_slave.inc
# CHANGE MASTER TO MASTER_DELAY = 2*T
include/start_slave.inc
connection master;
INSERT INTO t1 VALUES ('Syncing slave', 5);
connection slave;
connection master;
INSERT INTO t1 VALUES (delay_on_slave(1), 6);
Warnings:
Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
connection slave;
# sleep 1*T
# Asserted this: Seconds_Behind_Master should be between 0 and the 2*T
# sleep 1*T
# Asserted this: Seconds_Behind_Master should be at least 2*T
==== STOP SLAVE / START SLAVE + DML ====
include/stop_slave.inc
# CHANGE MASTER TO MASTER_DELAY = 3*T
include/start_slave.inc
[on master]
connection master;
INSERT INTO t1 VALUES ('stop slave and start slave: DML', 7);
[on slave]
connection slave;
# sleep 1*T
include/stop_slave.inc
# Asserted this: STOP SLAVE should finish quickly, not wait for the ongoing sleep to finish
# Asserted this: SQL thread position should not increase after STOP SLAVE
# Asserted this: Query should not be executed after STOP SLAVE
# Asserted this: Status should be '' after STOP SLAVE
include/start_slave.inc
# Asserted this: START SLAVE should finish quickly
connection master;
[on slave]
include/sync_slave_io_with_master.inc
# sleep 1*T
# Asserted this: Query 7 should not be executed
# Asserted this: Status should be 'Waiting until MASTER_DELAY...'
# sleep 1*T
# sync with master (with timeout 1*T)
include/wait_for_slave_param.inc [Relay_Master_Log_File]
include/wait_for_slave_param.inc [Exec_Master_Log_Pos]
# Asserted this: Query 7 should be executed
# Asserted this: Status should be 'Has read all relay log...'
include/check_slave_is_running.inc
==== STOP SLAVE / START SLAVE + DDL ====
This verifies BUG#56442
[on master]
connection master;
CREATE TABLE t_check_dml_not_executed_prematurely (a INT);
include/save_master_pos.inc
[on slave]
connection slave;
# sleep 1*T
include/stop_slave.inc
# Asserted this: STOP SLAVE should finish quickly, not wait for the ongoing sleep to finish
# Asserted this: SQL thread position should not increase after STOP SLAVE
# Asserted this: Query should not be executed after STOP SLAVE
# Asserted this: Status should be '' after STOP SLAVE
include/start_slave.inc
# Asserted this: START SLAVE should finish quickly
# sleep 1*T
# Asserted this: DDL Query should not be executed after START SLAVE
# Asserted this: Status should be 'Waiting until MASTER_DELAY...'
# sleep 1*T
# sync with master (with timeout 1*T)
include/wait_for_slave_param.inc [Relay_Master_Log_File]
include/wait_for_slave_param.inc [Exec_Master_Log_Pos]
# Asserted this: DDL Query should be executed
# Asserted this: Status should be 'Has read all relay log...'
include/check_slave_is_running.inc
==== Change back to no delay ====
[on slave]
connection slave;
include/stop_slave.inc
CHANGE MASTER TO MASTER_DELAY = 0;
# Asserted this: Delay should be 0 when we set it to 0
include/start_slave.inc
[on master]
connection master;
INSERT INTO t1 VALUES ('change back to no delay', 8);
[on slave]
include/sync_slave_io_with_master.inc
# sleep 1*T
# Asserted this: Query should be executed
# Asserted this: Status should be 'Slave has read all relay log...'
==== Reset delay with RESET SLAVE ====
include/stop_slave.inc
CHANGE MASTER TO MASTER_DELAY = 71;
include/start_slave.inc
# Asserted this: Delay should be 71 when we set it to 71
include/stop_slave.inc
include/reset_slave.inc
Warnings:
Note	4190	RESET SLAVE is implicitly changing the value of 'Using_Gtid' from 'No' to 'Slave_Pos'
[on master]
connection master;
RESET MASTER;
[on slave]
connection slave;
include/start_slave.inc
# Asserted this: Delay should be 0 after RESET SLAVE
==== Set an invalid value for the delay ====
include/stop_slave.inc
# Expect error for setting negative delay
CHANGE MASTER TO MASTER_DELAY = -1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-1' at line 1
# Expect that it's ok to set delay of 2^31-1
CHANGE MASTER TO MASTER_DELAY = 2147483647;
# Expect error for setting delay between 2^31 and 2^32-1
CHANGE MASTER TO MASTER_DELAY = 2147483648;
ERROR HY000: The requested value 2147483648 for the master delay exceeds the maximum 2147483647
# Expect error for setting delay to nonsense
CHANGE MASTER TO MASTER_DELAY = blah;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'blah' at line 1
CHANGE MASTER TO MASTER_DELAY = 0;
include/start_slave.inc
==== Clean up ====
[on master]
connection master;
DROP TABLE t1, t_check_dml_not_executed_prematurely;
DROP FUNCTION delay_on_slave;
[on slave]
connection slave;
SELECT @@GLOBAL.slave_parallel_mode;
@@GLOBAL.slave_parallel_mode
optimistic
SELECT @@GLOBAL.slave_parallel_threads;
@@GLOBAL.slave_parallel_threads
0
include/rpl_end.inc