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 consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
[connection master]
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, INDEX(c2)) ENGINE = InnoDB;
INSERT INTO t1 VALUES(1, NULL),(2, 2), (3, NULL), (4, 4), (5, NULL), (6, 6);
include/sync_slave_sql_with_master.inc
include/stop_slave_sql.inc
SET @saved_slave_parallel_type = @@GLOBAL.slave_parallel_type;
SET @saved_slave_parallel_workers = @@GLOBAL.slave_parallel_workers;
SET @saved_slave_preserve_commit_order = @@GLOBAL.slave_preserve_commit_order;
SET @saved_innodb_lock_wait_timeout = @@GLOBAL.innodb_lock_wait_timeout;
SET @saved_slave_transaction_retries = @@GLOBAL.slave_transaction_retries;
SET GLOBAL slave_transaction_retries = 2;
SET GLOBAL slave_parallel_type = "LOGICAL_CLOCK";
SET GLOBAL slave_parallel_workers = 3;
SET GLOBAL slave_preserve_commit_order = ON;
SET GLOBAL innodb_lock_wait_timeout = 1000;
#
# Case 1: Verify slave can find the deadlock when DELETE is waiting
# for its turn to commit
#
[connection master]
INSERT INTO t1 VALUES(10, 10);
SET debug = "d,set_commit_parent_100";
INSERT INTO t1 VALUES(11, NULL);
DELETE FROM t1 WHERE c2 <= 3;
[connection slave]
BEGIN;
INSERT INTO t1 VALUES(11, 11);
[connection slave1]
include/start_slave_sql.inc
[connection slave]
ROLLBACK;
include/rpl_diff.inc
[connection master]
SET debug = "";
TRUNCATE t1;
INSERT INTO t1 VALUES(1, NULL),(2, 2), (3, NULL), (4, 4), (5, NULL), (6, 6);
include/sync_slave_sql_with_master.inc
include/stop_slave_sql.inc
#
# Case 2: Verify slave can find the deadlock when it begins to applying
# second DELETE statement.
#
[connection master]
INSERT INTO t1 VALUES(20, NULL);
SET debug = "d,set_commit_parent_100";
INSERT INTO t1 VALUES(21, NULL);
BEGIN;
INSERT INTO t1 VALUES(22, 22);
DELETE FROM t1 WHERE c2 <= 3;
INSERT INTO t1 VALUES(23, 23);
INSERT INTO t1 VALUES(24, 24);
INSERT INTO t1 VALUES(25, 25);
COMMIT;
[connection slave]
BEGIN;
INSERT INTO t1 VALUES(21, 21);
[connection server_2_1]
BEGIN;
INSERT INTO t1 VALUES(23, 23);
[connection slave1]
include/start_slave_sql.inc
[connection slave]
ROLLBACK;
[connection server_2_1]
ROLLBACK;
include/rpl_diff.inc
[connection master]
SET debug = "";
TRUNCATE t1;
INSERT INTO t1 VALUES(1, NULL),(2, 2), (3, NULL), (4, 4), (5, NULL), (32, 32);
include/sync_slave_sql_with_master.inc
include/stop_slave_sql.inc
#
# Test case 3: Verify the worker can handle it correctly when it is
# retrying a transaction.
#
[connection master]
INSERT INTO t1 VALUES(30, NULL);
SET debug = "d,set_commit_parent_100";
INSERT INTO t1 VALUES(31, NULL);
INSERT INTO t1 VALUES(33, NULL);
DELETE FROM t1 WHERE c2 <= 3;
[connection slave]
BEGIN;
INSERT INTO t1 VALUES(31, 31);
[connection server_2_1]
BEGIN;
INSERT INTO t1 VALUES(33, 33);
[connection slave1]
include/start_slave_sql.inc
[connection slave]
ROLLBACK;
[connection server_2_1]
ROLLBACK;
include/rpl_diff.inc
SET debug = "";
#
# Test Case 4: Innodb internal transaction deadlock
#
[connection master]
CREATE TABLE t2 LIKE mysql.innodb_table_stats;
include/sync_slave_sql_with_master.inc
include/stop_slave_sql.inc
CALL mtr.add_suppression(".*InnoDB: Cannot save table statistics for table.*");
LOCK TABLE t1 WRITE;
[connection master]
TRUNCATE t2;
SET debug = "d,set_commit_parent_100";
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
INSERT INTO t2 SELECT * FROM mysql.innodb_table_stats;
[connection slave]
include/start_slave_sql.inc
UNLOCK TABLES;
#
# Test Case 5: It won't cause transaction rollback if
#              slave_preserve_commit_order is OFF
#
[connection master]
SET debug = "";
TRUNCATE t1;
INSERT INTO t1 VALUES(1, NULL),(2, 2), (3, NULL), (4, 4), (5, NULL), (6, 6);
include/sync_slave_sql_with_master.inc
include/stop_slave_sql.inc
[connection master]
INSERT INTO t1 VALUES(50, 50);
SET debug = "d,set_commit_parent_100";
INSERT INTO t1 VALUES(51, NULL);
BEGIN;
INSERT INTO t1 VALUES(52, 52);
DELETE FROM t1 WHERE c2 <= 3;
INSERT INTO t1 VALUES(53, 53);
INSERT INTO t1 VALUES(54, 54);
INSERT INTO t1 VALUES(55, 55);
COMMIT;
[connection slave]
BEGIN;
INSERT INTO t1 VALUES(51, 51);
[connection server_2_1]
BEGIN;
INSERT INTO t1 VALUES(53, 53);
[connection slave1]
SET GLOBAL slave_preserve_commit_order = OFF;
SET GLOBAL slave_transaction_retries = 0;
include/start_slave_sql.inc
[connection slave]
ROLLBACK;
[connection server_2_1]
ROLLBACK;
include/rpl_diff.inc
#
# Deinitialize
#
[connection master]
SET debug = "";
DROP TABLE t1, t2;
include/sync_slave_sql_with_master.inc
include/stop_slave.inc
SET GLOBAL slave_transaction_retries = @saved_slave_transaction_retries;
SET GLOBAL slave_parallel_type = @saved_slave_parallel_type;
SET GLOBAL slave_parallel_workers = @saved_slave_parallel_workers;
SET GLOBAL slave_preserve_commit_order = @saved_slave_preserve_commit_order;
SET GLOBAL innodb_lock_wait_timeout = @saved_innodb_lock_wait_timeout;
include/rpl_end.inc