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 connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information. [connection master] [connection slave] CALL mtr.add_suppression("You need to use --log-bin to make --binlog-format work"); CALL mtr.add_suppression("The transaction owned GTID is already in the gtid_executed table"); SET @save_replica_parallel_workers= @@global.replica_parallel_workers; SET @save_replica_parallel_type= @@global.replica_parallel_type; Warnings: Warning 1287 '@@replica_parallel_type' is deprecated and will be removed in a future release. SET @save_replica_preserve_commit_order= @@global.replica_preserve_commit_order; SET GLOBAL replica_parallel_type = 'LOGICAL_CLOCK'; Warnings: Warning 1287 '@@replica_parallel_type' is deprecated and will be removed in a future release. SET GLOBAL replica_parallel_workers= 10; SET GLOBAL replica_preserve_commit_order= ON; include/start_slave.inc [connection master1] SET @save_binlog_transaction_dependency_tracking= @@global.binlog_transaction_dependency_tracking; Warnings: Warning 1287 '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release. SET @@global.binlog_transaction_dependency_tracking = COMMIT_ORDER; Warnings: Warning 1287 '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release. ################################################################# # 1. Check for ANALYZE TABLE ################################################################# # Setup [connection master] CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE = InnoDB; CREATE TABLE t2 (c1 INT PRIMARY KEY) ENGINE = InnoDB; include/sync_slave_sql_with_master.inc # Block slave sql applier threads [connection slave] BEGIN; INSERT INTO t2 VALUES (1); [connection slave] [connection slave] # Generate the transactions which can be applied in parallel on slave [connection master] # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug BEGIN; INSERT INTO t2 VALUES (1); COMMIT; BEGIN; INSERT INTO t2 VALUES (2); COMMIT; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status OK [connection server_1] BEGIN; INSERT INTO t2 VALUES (3); COMMIT; BEGIN; INSERT INTO t2 VALUES (4); COMMIT; BEGIN; INSERT INTO t2 VALUES (5); COMMIT; # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug # Verify the transactions are ordered correctly on slave [connection server_2] include/assert.inc [Verify table t2 has 0 rows] [connection slave] include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation] # Rollback the first insert so that slave applier threads can # unblock and proceed. Verify the transactions are applied. [connection slave] ROLLBACK; TRUNCATE TABLE t1; TRUNCATE TABLE t2; include/sync_slave_sql_with_master.inc include/rpl_connect.inc [creating master] ################################################################# # 2. Check for REPAIR TABLE ################################################################# # Setup [connection master] CREATE TABLE t3(a INT PRIMARY KEY)Engine=MyISAM; INSERT INTO t3 VALUES(1),(2),(3); include/sync_slave_sql_with_master.inc # Block slave sql applier threads [connection slave] BEGIN; INSERT INTO t2 VALUES (1); [connection slave] [connection slave] # Generate the transactions which can be applied in parallel on slave [connection master] # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug BEGIN; INSERT INTO t2 VALUES (1); COMMIT; BEGIN; INSERT INTO t2 VALUES (2); COMMIT; SET TIMESTAMP = 100; REPAIR TABLE t3; Table Op Msg_type Msg_text test.t3 repair status OK [connection server_1] BEGIN; INSERT INTO t2 VALUES (3); COMMIT; BEGIN; INSERT INTO t2 VALUES (4); COMMIT; BEGIN; INSERT INTO t2 VALUES (5); COMMIT; # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug # Verify the transactions are ordered correctly on slave [connection server_2] include/assert.inc [Verify table t2 has 0 rows] [connection slave] include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation] # Rollback the first insert so that slave applier threads can # unblock and proceed. Verify the transactions are applied. [connection slave] ROLLBACK; TRUNCATE TABLE t1; TRUNCATE TABLE t2; DROP TABLE t3; include/sync_slave_sql_with_master.inc include/rpl_connect.inc [creating master] ################################################################# # 3. Check for OPTIMIZE TABLE ################################################################# # Setup [connection master] CREATE TABLE t3 (c1 INT PRIMARY KEY) ENGINE = InnoDB; CREATE TABLE t4 (c1 INT PRIMARY KEY) ENGINE = InnoDB; include/sync_slave_sql_with_master.inc # Block slave sql applier threads [connection slave] BEGIN; INSERT INTO t4 VALUES (1); [connection slave] [connection slave] # Generate the transactions which can be applied in parallel on slave [connection master] # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug BEGIN; INSERT INTO t4 VALUES (1); COMMIT; BEGIN; INSERT INTO t4 VALUES (2); COMMIT; OPTIMIZE TABLE t3; Table Op Msg_type Msg_text test.t3 optimize note Table does not support optimize, doing recreate + analyze instead test.t3 optimize status OK [connection server_1] BEGIN; INSERT INTO t4 VALUES (3); COMMIT; BEGIN; INSERT INTO t4 VALUES (4); COMMIT; BEGIN; INSERT INTO t4 VALUES (5); COMMIT; # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug # Verify the transactions are ordered correctly on slave [connection server_2] include/assert.inc [Verify table t4 is empty] [connection slave] include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation] # Rollback the first insert so that slave applier threads can # unblock and proceed. Verify the transactions are applied. [connection slave] ROLLBACK; DROP TABLE t3; DROP TABLE t4; include/sync_slave_sql_with_master.inc include/rpl_connect.inc [creating master] ################################################################# # 4. Check for RENAME TABLE ################################################################# # 4.1 Check for RENAME single table # Setup [connection master] INSERT INTO t1 VALUES (10); include/sync_slave_sql_with_master.inc # Block slave sql applier threads [connection slave] BEGIN; INSERT INTO t2 VALUES (1); [connection slave] [connection slave] # Generate the transactions which can be applied in parallel on slave [connection master] # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug BEGIN; INSERT INTO t2 VALUES (1); COMMIT; BEGIN; INSERT INTO t2 VALUES (2); COMMIT; RENAME TABLE t1 to t10; [connection server_1] BEGIN; INSERT INTO t2 VALUES (3); COMMIT; BEGIN; INSERT INTO t2 VALUES (4); COMMIT; BEGIN; INSERT INTO t2 VALUES (5); COMMIT; # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug # Verify the transactions are ordered correctly on slave [connection server_2] include/assert.inc [Verify table t2 is empty] include/assert.inc [Verify tables t1 and t2 exist] include/assert.inc [Verify table t10 does not exist] [connection slave] include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation] # Rollback the first insert so that slave applier threads can # unblock and proceed. Verify the transactions are applied. [connection slave] ROLLBACK; RENAME TABLE t10 to t1; TRUNCATE TABLE t1; TRUNCATE TABLE t2; include/sync_slave_sql_with_master.inc # 4.2 Check for RENAME multiple tables # Setup [connection master] CREATE TABLE t3 (c1 INT PRIMARY KEY) ENGINE = InnoDB; include/sync_slave_sql_with_master.inc # Block slave sql applier threads [connection slave] BEGIN; INSERT INTO t2 VALUES (1); [connection slave] [connection slave] # Generate the transactions which can be applied in parallel on slave [connection master] # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug BEGIN; INSERT INTO t2 VALUES (1); COMMIT; BEGIN; INSERT INTO t2 VALUES (2); COMMIT; RENAME TABLE t1 to t10, t3 to t30; [connection server_1] BEGIN; INSERT INTO t2 VALUES (3); COMMIT; BEGIN; INSERT INTO t2 VALUES (4); COMMIT; BEGIN; INSERT INTO t2 VALUES (5); COMMIT; # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug # Verify the transactions are ordered correctly on slave [connection server_2] include/assert.inc [Verify table t2 is empty] include/assert.inc [Verify tables t1, t2 and t3 exist] include/assert.inc [Verify tables t10 and t30 do not exist] [connection slave] include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation] # Rollback the first insert so that slave applier threads can # unblock and proceed. Verify the transactions are applied. [connection slave] ROLLBACK; DROP TABLE t10, t2, t30; include/sync_slave_sql_with_master.inc #################################################################### # 5. CREATE TABLE and CREATE TABLE IF NOT EXISTS #################################################################### # Setup [connection master] CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE = InnoDB; CREATE TABLE t2 (c1 INT PRIMARY KEY) ENGINE = InnoDB; include/sync_slave_sql_with_master.inc # Block slave sql applier threads [connection slave] BEGIN; INSERT INTO t1 VALUES (1); [connection slave] [connection slave] # Generate the transactions which can be applied in parallel on slave [connection master] # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug BEGIN; INSERT INTO t1 VALUES (1); COMMIT; BEGIN; INSERT INTO t1 VALUES (2); COMMIT; CREATE TABLE t10 (c1 INT PRIMARY KEY) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS t20 (c1 INT PRIMARY KEY) ENGINE = InnoDB; [connection server_1] BEGIN; INSERT INTO t1 VALUES (5); COMMIT; BEGIN; INSERT INTO t1 VALUES (6); COMMIT; BEGIN; INSERT INTO t1 VALUES (7); COMMIT; # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug # Verify the transactions are ordered correctly on slave [connection server_2] include/assert.inc [Verify table t1 is empty] include/assert.inc [Verify tables t1 and t2 exist] include/assert.inc [Verify tables t10 and t20 do not exist] [connection slave] include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation] # Rollback the first insert so that slave applier threads can # unblock and proceed. Verify the transactions are applied. [connection slave] ROLLBACK; DROP TABLE t10, t20; TRUNCATE TABLE t1; TRUNCATE TABLE t2; include/sync_slave_sql_with_master.inc #################################################################### # 6. DROP TABLE and DROP TABLE IF EXISTS #################################################################### # Setup [connection master] CREATE TABLE t3 LIKE t1; CREATE TABLE t4 LIKE t1; CREATE TABLE t5 LIKE t1; include/sync_slave_sql_with_master.inc # Block slave sql applier threads [connection slave] BEGIN; INSERT INTO t1 VALUES (1); [connection slave] [connection slave] # Generate the transactions which can be applied in parallel on slave [connection master] # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug BEGIN; INSERT INTO t1 VALUES (1); COMMIT; BEGIN; INSERT INTO t1 VALUES (2); COMMIT; DROP TABLE t2; DROP TABLE t3, t4; DROP TABLE IF EXISTS t5; DROP TABLE IF EXISTS t10; Warnings: Note 1051 Unknown table 'test.t10' [connection server_1] BEGIN; INSERT INTO t1 VALUES (5); COMMIT; BEGIN; INSERT INTO t1 VALUES (6); COMMIT; BEGIN; INSERT INTO t1 VALUES (7); COMMIT; # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug # Verify the transactions are ordered correctly on slave [connection server_2] include/assert.inc [Verify table t1 is empty] include/assert.inc [Verify tables t1, t2, t3, t4, and t5 exist] include/assert.inc [Verify table t10 does not exist] [connection slave] include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation] # Rollback the first insert so that slave applier threads can # unblock and proceed. Verify the transactions are applied. [connection slave] ROLLBACK; TRUNCATE TABLE t1; CREATE TABLE t2 (c1 INT PRIMARY KEY) ENGINE = InnoDB; include/sync_slave_sql_with_master.inc #################################################################### # 7. Check for CREATE DATABASE and CREATE DATABASE IF NOT EXISTS #################################################################### # Setup [connection master] CREATE DATABASE db1; include/sync_slave_sql_with_master.inc # Block slave sql applier threads [connection slave] BEGIN; INSERT INTO t1 VALUES (1); [connection slave] [connection slave] # Generate the transactions which can be applied in parallel on slave [connection master] # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug BEGIN; INSERT INTO t1 VALUES (1); COMMIT; BEGIN; INSERT INTO t1 VALUES (2); COMMIT; CREATE DATABASE db10; CREATE DATABASE IF NOT EXISTS db20; [connection server_1] BEGIN; INSERT INTO t2 VALUES (1); COMMIT; BEGIN; INSERT INTO t2 VALUES (2); COMMIT; BEGIN; INSERT INTO t2 VALUES (3); COMMIT; # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug # Verify the transactions are ordered correctly on slave [connection server_2] include/assert.inc [Verify table t1 is empty] include/assert.inc [Verify table t2 is empty] include/assert.inc [Verify DATABASE db1 exists] include/assert.inc [Verify DATABASES db10 and db20 do not exist] [connection slave] include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation] # Rollback the first insert so that slave applier threads can # unblock and proceed. Verify the transactions are applied. [connection slave] ROLLBACK; TRUNCATE TABLE t1; TRUNCATE TABLE t2; DROP DATABASE db1; DROP DATABASE db10; DROP DATABASE db20; include/sync_slave_sql_with_master.inc ################################################################# # 8. Check for DROP DATABASE and DROP DATABASE IF EXISTS ################################################################# # Setup [connection master] CREATE DATABASE db1; CREATE DATABASE db2; include/sync_slave_sql_with_master.inc # Block slave sql applier threads [connection slave] BEGIN; INSERT INTO t1 VALUES (1); [connection slave] [connection slave] # Generate the transactions which can be applied in parallel on slave [connection master] # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug BEGIN; INSERT INTO t1 VALUES (1); COMMIT; BEGIN; INSERT INTO t1 VALUES (2); COMMIT; DROP DATABASE db1; DROP DATABASE IF EXISTS db2; DROP DATABASE IF EXISTS db10; Warnings: Note 1008 Can't drop database 'db10'; database doesn't exist [connection server_1] BEGIN; INSERT INTO t1 VALUES (5); COMMIT; BEGIN; INSERT INTO t1 VALUES (6); COMMIT; BEGIN; INSERT INTO t1 VALUES (7); COMMIT; # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug # Verify the transactions are ordered correctly on slave [connection server_2] include/assert.inc [Verify table t1 is empty] include/assert.inc [Verify table t2 is empty] include/assert.inc [Verify DATABASES db1 and db2 exist] include/assert.inc [Verify DATABASE db10 does not exist] [connection slave] include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation] # Rollback the first insert so that slave applier threads can # unblock and proceed. Verify the transactions are applied. [connection slave] ROLLBACK; TRUNCATE TABLE t1; TRUNCATE TABLE t2; include/sync_slave_sql_with_master.inc ################################################################# # 9. Check for CREATE TABLESPACE ################################################################# # Setup [connection master] INSERT INTO t1 VALUES (10); include/sync_slave_sql_with_master.inc # Block slave sql applier threads [connection slave] BEGIN; INSERT INTO t1 VALUES (1); [connection slave] [connection slave] # Generate the transactions which can be applied in parallel on slave [connection master] # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug BEGIN; INSERT INTO t1 VALUES (1); COMMIT; BEGIN; INSERT INTO t1 VALUES (2); COMMIT; CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' Engine=InnoDB; [connection server_1] BEGIN; INSERT INTO t2 VALUES (1); COMMIT; BEGIN; INSERT INTO t2 VALUES (2); COMMIT; BEGIN; INSERT INTO t2 VALUES (3); COMMIT; # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug # Verify the transactions are ordered correctly on slave [connection server_2] include/assert.inc [Verify table t1 has one value] include/assert.inc [Verify table t2 is empty] include/assert.inc [Verify TABLESPACE does not exist] [connection slave] include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation] # Rollback the first insert so that slave applier threads can # unblock and proceed. Verify the transactions are applied. [connection slave] ROLLBACK; TRUNCATE TABLE t1; TRUNCATE TABLE t2; DROP TABLESPACE ts1; include/sync_slave_sql_with_master.inc ################################################################# # 10. Check for ALTER TABLESPACE ################################################################# # Setup [connection master] INSERT INTO t1 VALUES (10); CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' Engine=InnoDB; CREATE TABLE t11 (a INT, b INT) ENGINE = InnoDB TABLESPACE=ts1; include/sync_slave_sql_with_master.inc # Block slave sql applier threads [connection slave] BEGIN; INSERT INTO t1 VALUES (1); [connection slave] [connection slave] # Generate the transactions which can be applied in parallel on slave [connection master] # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug BEGIN; INSERT INTO t1 VALUES (1); COMMIT; BEGIN; INSERT INTO t1 VALUES (2); COMMIT; ALTER TABLESPACE ts1 RENAME TO ts11; [connection server_1] BEGIN; INSERT INTO t2 VALUES (1); COMMIT; BEGIN; INSERT INTO t2 VALUES (2); COMMIT; BEGIN; INSERT INTO t2 VALUES (3); COMMIT; # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug # Verify the transactions are ordered correctly on slave [connection server_2] include/assert.inc [Verify table t1 has one value] include/assert.inc [Verify TABLESPACE ts1 exists] include/assert.inc [Verify TABLESPACE ts11 does not exists] [connection slave] include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation] # Rollback the first insert so that slave applier threads can # unblock and proceed. Verify the transactions are applied. [connection slave] ROLLBACK; TRUNCATE TABLE t1; TRUNCATE TABLE t2; DROP TABLE t11; DROP TABLESPACE ts11; include/sync_slave_sql_with_master.inc ################################################################# # 11. Check for DROP TABLESPACE ################################################################# # Setup [connection master] INSERT INTO t1 VALUES (10); CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' Engine=InnoDB; include/sync_slave_sql_with_master.inc # Block slave sql applier threads [connection slave] BEGIN; INSERT INTO t1 VALUES (1); [connection slave] [connection slave] # Generate the transactions which can be applied in parallel on slave [connection master] # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug BEGIN; INSERT INTO t1 VALUES (1); COMMIT; BEGIN; INSERT INTO t1 VALUES (2); COMMIT; DROP TABLESPACE ts1; [connection server_1] BEGIN; INSERT INTO t2 VALUES (1); COMMIT; BEGIN; INSERT INTO t2 VALUES (2); COMMIT; BEGIN; INSERT INTO t2 VALUES (3); COMMIT; # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug # Verify the transactions are ordered correctly on slave [connection server_2] include/assert.inc [Verify table t1 has one value] include/assert.inc [Verify table t2 is empty] include/assert.inc [Verify TABLESPACE exists] [connection slave] include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation] # Rollback the first insert so that slave applier threads can # unblock and proceed. Verify the transactions are applied. [connection slave] ROLLBACK; TRUNCATE TABLE t1; TRUNCATE TABLE t2; include/sync_slave_sql_with_master.inc ################################################################# # 12. Check for CREATE INDEX ################################################################# # Setup [connection master] INSERT INTO t1 VALUES (10); CREATE TABLE t3 (col1 INT, col2 INT, INDEX func_index ((ABS(col1)))); INSERT INTO t3 VALUES(1,1); include/sync_slave_sql_with_master.inc # Block slave sql applier threads [connection slave] BEGIN; INSERT INTO t1 VALUES (1); [connection slave] [connection slave] # Generate the transactions which can be applied in parallel on slave [connection master] # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug BEGIN; INSERT INTO t1 VALUES (1); COMMIT; BEGIN; INSERT INTO t1 VALUES (2); COMMIT; CREATE INDEX idx1 ON t3 ((col1 + col2)); [connection server_1] BEGIN; INSERT INTO t2 VALUES (1); COMMIT; BEGIN; INSERT INTO t2 VALUES (2); COMMIT; BEGIN; INSERT INTO t2 VALUES (3); COMMIT; # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug # Verify the transactions are ordered correctly on slave [connection server_2] include/assert.inc [Verify table t1 has one value] include/assert.inc [Verify table t2 is empty] include/assert.inc [Verify INDEX does not exist] [connection slave] include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation] # Rollback the first insert so that slave applier threads can # unblock and proceed. Verify the transactions are applied. [connection slave] ROLLBACK; TRUNCATE TABLE t1; TRUNCATE TABLE t2; DROP INDEX idx1 ON t3; DROP TABLE t3; include/sync_slave_sql_with_master.inc ################################################################# # 13. Check for DROP INDEX ################################################################# # Setup [connection master] INSERT INTO t1 VALUES (10); CREATE TABLE t3 (col1 INT, col2 INT, INDEX func_index ((ABS(col1)))); INSERT INTO t3 VALUES(1,1); CREATE INDEX idx1 ON t3 ((col1 + col2)); include/sync_slave_sql_with_master.inc # Block slave sql applier threads [connection slave] BEGIN; INSERT INTO t1 VALUES (1); [connection slave] [connection slave] # Generate the transactions which can be applied in parallel on slave [connection master] # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug BEGIN; INSERT INTO t1 VALUES (1); COMMIT; BEGIN; INSERT INTO t1 VALUES (2); COMMIT; DROP INDEX idx1 ON t3; [connection server_1] BEGIN; INSERT INTO t2 VALUES (1); COMMIT; BEGIN; INSERT INTO t2 VALUES (2); COMMIT; BEGIN; INSERT INTO t2 VALUES (3); COMMIT; # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug # Verify the transactions are ordered correctly on slave [connection server_2] include/assert.inc [Verify table t1 has one value] include/assert.inc [Verify table t2 is empty] include/assert.inc [Verify INDEX exists] [connection slave] include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation] # Rollback the first insert so that slave applier threads can # unblock and proceed. Verify the transactions are applied. [connection slave] ROLLBACK; TRUNCATE TABLE t1; DROP TABLE t2, t3; include/sync_slave_sql_with_master.inc #################################################################### # 13. DROP FUNCTION and DROP FUNCTION IF EXISTS #################################################################### # Setup [connection master] CREATE FUNCTION func1 (param1 INT) returns CHAR(50) return 'test'; CREATE FUNCTION func2 (param1 INT) returns CHAR(50) return 'test'; include/sync_slave_sql_with_master.inc # Block slave sql applier threads [connection slave] BEGIN; INSERT INTO t1 VALUES (1); [connection slave] [connection slave] # Generate the transactions which can be applied in parallel on slave [connection master] # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug BEGIN; INSERT INTO t1 VALUES (1); COMMIT; BEGIN; INSERT INTO t1 VALUES (2); COMMIT; DROP FUNCTION func1; DROP FUNCTION IF EXISTS func2; DROP FUNCTION IF EXISTS func3; Warnings: Note 1305 FUNCTION test.func3 does not exist [connection server_1] BEGIN; INSERT INTO t1 VALUES (5); COMMIT; BEGIN; INSERT INTO t1 VALUES (6); COMMIT; BEGIN; INSERT INTO t1 VALUES (7); COMMIT; # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug # Verify the transactions are ordered correctly on slave [connection server_2] include/assert.inc [Verify table t1 is empty] include/assert.inc [Verify function func1 and func2 exists] [connection slave] include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation] # Rollback the first insert so that slave applier threads can # unblock and proceed. Verify the transactions are applied. [connection slave] ROLLBACK; DROP TABLE t1; include/sync_slave_sql_with_master.inc #################################################################### # 14. DROP PROCEDURE and DROP PROCEDURE IF EXISTS #################################################################### # Setup [connection master] CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE = InnoDB;; CREATE PROCEDURE proc_t1 (OUT param1 INT) BEGIN SELECT 'test'; END; CREATE PROCEDURE proc_t2 (OUT param1 INT) BEGIN SELECT 'test'; END// include/sync_slave_sql_with_master.inc # Block slave sql applier threads [connection slave] BEGIN; INSERT INTO t1 VALUES (1);; [connection slave] [connection slave] # Generate the transactions which can be applied in parallel on slave [connection master] # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug BEGIN; INSERT INTO t1 VALUES (1); COMMIT; BEGIN; INSERT INTO t1 VALUES (2); COMMIT; DROP PROCEDURE proc_t1; DROP PROCEDURE IF EXISTS proc_t2; DROP PROCEDURE IF EXISTS proc_t3; Warnings: Note 1305 PROCEDURE test.proc_t3 does not exist [connection server_1] BEGIN; INSERT INTO t1 VALUES (5); COMMIT; BEGIN; INSERT INTO t1 VALUES (6); COMMIT; BEGIN; INSERT INTO t1 VALUES (7); COMMIT; # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug # Verify the transactions are ordered correctly on slave [connection server_2] include/assert.inc [Verify table t1 is empty] include/assert.inc [Verify procedure proc_t1 and proc_t2 exists] [connection slave] include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation] # Rollback the first insert so that slave applier threads can # unblock and proceed. Verify the transactions are applied. [connection slave] ROLLBACK; TRUNCATE TABLE t1; include/sync_slave_sql_with_master.inc #################################################################### # 15. DROP VIEW and DROP VIEW IF EXISTS #################################################################### # Setup [connection master] CREATE VIEW view_t1 AS SELECT * FROM t1; CREATE VIEW view_t2 AS SELECT * FROM t1; include/sync_slave_sql_with_master.inc # Block slave sql applier threads [connection slave] BEGIN; INSERT INTO t1 VALUES (1);; [connection slave] [connection slave] # Generate the transactions which can be applied in parallel on slave [connection master] # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug BEGIN; INSERT INTO t1 VALUES (1); COMMIT; BEGIN; INSERT INTO t1 VALUES (2); COMMIT; DROP VIEW view_t1; DROP VIEW IF EXISTS view_t2; DROP VIEW IF EXISTS view_t3; Warnings: Note 1051 Unknown table 'test.view_t3' [connection server_1] BEGIN; INSERT INTO t1 VALUES (5); COMMIT; BEGIN; INSERT INTO t1 VALUES (6); COMMIT; BEGIN; INSERT INTO t1 VALUES (7); COMMIT; # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug # Verify the transactions are ordered correctly on slave [connection server_2] include/assert.inc [Verify table t1 is empty] include/assert.inc [Verify views view_t1 and view_t2 exists] [connection slave] include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation] # Rollback the first insert so that slave applier threads can # unblock and proceed. Verify the transactions are applied. [connection slave] ROLLBACK; DROP TABLE t1; include/sync_slave_sql_with_master.inc #################################################################### # 16. DROP EVENT and DROP EVENT IF EXISTS #################################################################### # Setup [connection master] CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE = InnoDB;; CREATE EVENT event_t1 ON SCHEDULE EVERY 1 DAY DO BEGIN select 'test'; END; CREATE EVENT event_t2 ON SCHEDULE EVERY 1 DAY DO BEGIN select 'test'; END// include/sync_slave_sql_with_master.inc # Block slave sql applier threads [connection slave] BEGIN; INSERT INTO t1 VALUES (1);; [connection slave] [connection slave] # Generate the transactions which can be applied in parallel on slave [connection master] # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug BEGIN; INSERT INTO t1 VALUES (1); COMMIT; BEGIN; INSERT INTO t1 VALUES (2); COMMIT; DROP EVENT event_t1; DROP EVENT IF EXISTS event_t2; DROP EVENT IF EXISTS event_t3; Warnings: Note 1305 Event event_t3 does not exist [connection server_1] BEGIN; INSERT INTO t1 VALUES (5); COMMIT; BEGIN; INSERT INTO t1 VALUES (6); COMMIT; BEGIN; INSERT INTO t1 VALUES (7); COMMIT; # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug # Verify the transactions are ordered correctly on slave [connection server_2] include/assert.inc [Verify table t1 is empty] include/assert.inc [Verify events event_t1 and event_t2 exists] [connection slave] include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation] # Rollback the first insert so that slave applier threads can # unblock and proceed. Verify the transactions are applied. [connection slave] ROLLBACK; TRUNCATE TABLE t1; include/sync_slave_sql_with_master.inc #################################################################### # 17. DROP TRIGGER and DROP TRIGGER IF EXISTS #################################################################### # Setup [connection master] CREATE TABLE t2 (c1 INT PRIMARY KEY) ENGINE = InnoDB; CREATE TABLE t3 (c1 INT PRIMARY KEY) ENGINE = InnoDB; CREATE TRIGGER trig_t1 BEFORE INSERT ON t2 FOR EACH ROW SET @sum = @sum + NEW.c1; CREATE TRIGGER trig_t2 BEFORE INSERT ON t3 FOR EACH ROW SET @sum = @sum + NEW.c1; include/sync_slave_sql_with_master.inc # Block slave sql applier threads [connection slave] BEGIN; INSERT INTO t1 VALUES (1);; [connection slave] [connection slave] # Generate the transactions which can be applied in parallel on slave [connection master] # Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug BEGIN; INSERT INTO t1 VALUES (1); COMMIT; BEGIN; INSERT INTO t1 VALUES (2); COMMIT; DROP TRIGGER trig_t1; DROP TRIGGER IF EXISTS trig_t2; DROP TRIGGER IF EXISTS trig_t3; Warnings: Note 1360 Trigger does not exist [connection server_1] BEGIN; INSERT INTO t1 VALUES (5); COMMIT; BEGIN; INSERT INTO t1 VALUES (6); COMMIT; BEGIN; INSERT INTO t1 VALUES (7); COMMIT; # Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug # Verify the transactions are ordered correctly on slave [connection server_2] include/assert.inc [Verify table t1 is empty] include/assert.inc [Verify trigger trig_t1 and trig_t2 exists] [connection slave] include/assert.inc [Exactly 0 GTIDs should have been committed since last invocation] # Rollback the first insert so that slave applier threads can # unblock and proceed. Verify the transactions are applied. [connection slave] ROLLBACK; DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; include/sync_slave_sql_with_master.inc ################################################################# # Cleanup ################################################################# [connection master1] SET GLOBAL binlog_transaction_dependency_tracking=@save_binlog_transaction_dependency_tracking; Warnings: Warning 1287 '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release. include/sync_slave_sql_with_master.inc include/stop_slave.inc SET GLOBAL replica_parallel_type=@save_replica_parallel_type; Warnings: Warning 1287 '@@replica_parallel_type' is deprecated and will be removed in a future release. SET GLOBAL replica_parallel_workers=@save_replica_parallel_workers; SET GLOBAL replica_preserve_commit_order=@save_replica_preserve_commit_order; include/start_slave.inc include/rpl_end.inc