include/master-slave.inc
Warnings:
Note	####	Sending passwords in plain text without SSL/TLS is extremely insecure.
Note	####	Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.
[connection master]
call mtr.add_suppression("Unsafe statement written to the binary log using statement format");
call mtr.add_suppression("Unsafe statement written to the binary log using statement format");
[on master]
CREATE TABLE t1 (a VARCHAR(100), b INT);
INSERT INTO t1 VALUES ("zero", 0);
==== Normal setup ====
[on slave]
include/stop_slave.inc
# CHANGE MASTER TO MASTER_DELAY = 2*T
include/start_slave.inc
include/assert.inc [SHOW SLAVE STATUS should return the same delay that we set with CHANGE MASTER]
[on master]
INSERT INTO t1 VALUES ('normal setup', 1);
[on slave]
include/sync_slave_io_with_master.inc
# sleep 1*T
include/assert.inc [Query 1 should not be executed]
include/assert.inc [Status should be 'Waiting until MASTER_DELAY...']
# sleep 1*T
# sync with master (with timeout 1*T)
include/sync_slave_sql.inc
include/assert.inc [Query 1 should be executed]
include/assert.inc [Status should be 'Has read all relay log...']
include/check_slave_is_running.inc
==== Slave lags "naturally" after master ====
[on master]
# CREATE FUNCTION delay_on_slave(time_units INT) RETURNS INT BEGIN IF @@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
include/assert.inc [No query executed]
include/assert.inc [Status should be 'Waiting until MASTER_DELAY...']
# wait for first query to execute
# sleep 1*T
include/assert.inc [Second query executed]
include/assert.inc [Status should be executing third query (i.e., 'User sleep')]
# sleep 2*T
include/assert.inc [Third query executed]
include/assert.inc [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
INSERT INTO t1 VALUES ('Syncing slave', 5);
include/stop_slave.inc
# CHANGE MASTER TO MASTER_DELAY = 2*T
include/start_slave.inc
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.
# sleep 1*T
include/assert.inc [Seconds_Behind_Master should be between 0 and the 2*T]
# sleep 1*T
include/assert.inc [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]
INSERT INTO t1 VALUES ('stop slave and start slave: DML', 7);
[on slave]
# sleep 1*T
include/stop_slave.inc
include/assert.inc [STOP SLAVE should finish quickly, not wait for the ongoing sleep to finish]
include/assert.inc [SQL thread position should not increase after STOP SLAVE]
include/assert.inc [Query should not be executed after STOP SLAVE]
include/assert.inc [Status should be '' after STOP SLAVE]
include/start_slave.inc
include/assert.inc [START SLAVE should finish quickly]
[on slave]
include/sync_slave_io_with_master.inc
# sleep 1*T
include/assert.inc [Query 7 should not be executed]
include/assert.inc [Status should be 'Waiting until MASTER_DELAY...']
# sleep 1*T
# sync with master (with timeout 1*T)
include/sync_slave_sql.inc
include/assert.inc [Query 7 should be executed]
include/assert.inc [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]
CREATE TABLE t_check_dml_not_executed_prematurely (a INT);
include/save_master_pos.inc
[on slave]
# sleep 1*T
include/stop_slave.inc
include/assert.inc [STOP SLAVE should finish quickly, not wait for the ongoing sleep to finish]
include/assert.inc [SQL thread position should not increase after STOP SLAVE]
include/assert.inc [Query should not be executed after STOP SLAVE]
include/assert.inc [Status should be '' after STOP SLAVE]
include/start_slave.inc
include/assert.inc [START SLAVE should finish quickly]
# sleep 1*T
include/assert.inc [DDL Query should not be executed after START SLAVE]
include/assert.inc [Status should be 'Waiting until MASTER_DELAY...']
# sleep 1*T
# sync with master (with timeout 1*T)
include/sync_slave_sql.inc
include/assert.inc [DDL Query should be executed]
include/assert.inc [Status should be 'Has read all relay log...']
include/check_slave_is_running.inc
==== Change back to no delay ====
[on slave]
include/stop_slave.inc
CHANGE MASTER TO MASTER_DELAY = 0;
include/assert.inc [Delay should be 0 when we set it to 0]
include/start_slave.inc
[on master]
INSERT INTO t1 VALUES ('change back to no delay', 8);
[on slave]
include/sync_slave_io_with_master.inc
# sleep 1*T
include/assert.inc [Query should be executed]
include/assert.inc [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
include/assert.inc [Delay should be 71 when we set it to 71]
include/stop_slave.inc
RESET SLAVE;
[on master]
RESET MASTER;
[on slave]
include/start_slave.inc
include/assert.inc [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 MySQL 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 MySQL 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]
DROP TABLE t1, t_check_dml_not_executed_prematurely;
DROP FUNCTION delay_on_slave;
[on slave]
include/rpl_end.inc
