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] #### Initialize #### CREATE TABLE t1 (a INT); include/sync_slave_sql_with_master.inc CALL mtr.add_suppression('QUERY.*COMMIT or ROLLBACK.* or XID_LOG_EVENT is not expected in an event stream outside a transaction'); CALL mtr.add_suppression('An unexpected event sequence was detected by the IO thread while queuing the event received from source'); #### 1. Check basic properties of GTID_NEXT = NOT_YET_DETERMINED #### ---- 1.1. Format_description_log_event sets NOT_YET_DETERMINED ---- BINLOG 'C9BAVQ8BAAAAdwAAAHsAAAABAAQANS43LjgtcmMtZGVidWctbG9nAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAL0EBVEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjUA AdVXOVA='; SELECT @@SESSION.GTID_NEXT; @@SESSION.GTID_NEXT NOT_YET_DETERMINED ---- 1.2. DO, SELECT, SHOW, and SET don't change GTID_NEXT ---- DO 1; SELECT 1; 1 1 SHOW VARIABLES LIKE 'whatever'; Variable_name Value SET @foo = 1; SELECT @@SESSION.GTID_NEXT; @@SESSION.GTID_NEXT NOT_YET_DETERMINED ---- 1.3. Other stm changes NOT_YET_DETERMINED to ANONYMOUS if GTID_MODE=OFF ---- BEGIN; SELECT @@SESSION.GTID_NEXT; @@SESSION.GTID_NEXT ANONYMOUS ROLLBACK; SET @@SESSION.GTID_NEXT = 'AUTOMATIC'; ---- 1.4. Row injection changes NOT_YET_DETERMINED to ANONYMOUS if GTID_MODE=OFF ---- BINLOG 'C9BAVQ8BAAAAdwAAAHsAAAABAAQANS43LjgtcmMtZGVidWctbG9nAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAL0EBVEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjUA AdVXOVA='; SELECT @@SESSION.GTID_NEXT; @@SESSION.GTID_NEXT NOT_YET_DETERMINED BINLOG ' qyhTVRMBAAAALQAAAPIBAAAAAGwAAAAAAAEABHRlc3QAAnQxAAEDAAGFeQAI qyhTVR4BAAAAKAAAABoCAAAAAGwAAAAAAAEAAgAB//4BAAAAcd2jbw== '/*!*/; SELECT @@SESSION.GTID_NEXT; @@SESSION.GTID_NEXT ANONYMOUS ROLLBACK; SET @@SESSION.GTID_NEXT = 'AUTOMATIC'; ---- 1.5. Other stm generates error for NOT_YET_DETERMINED if GTID_MODE=ON ---- include/rpl_set_gtid_mode.inc [ON on servers 1,2] BINLOG 'C9BAVQ8BAAAAdwAAAHsAAAABAAQANS43LjgtcmMtZGVidWctbG9nAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAL0EBVEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjUA AdVXOVA='; SELECT @@SESSION.GTID_NEXT; @@SESSION.GTID_NEXT NOT_YET_DETERMINED BEGIN; ERROR HY000: @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON. SET @@SESSION.GTID_NEXT = 'AUTOMATIC'; ---- 1.6. Row injection generates error for NOT_YET_DETERMINED if GTID_MODE=ON ---- BINLOG 'C9BAVQ8BAAAAdwAAAHsAAAABAAQANS43LjgtcmMtZGVidWctbG9nAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAL0EBVEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjUA AdVXOVA='; SELECT @@SESSION.GTID_NEXT; @@SESSION.GTID_NEXT NOT_YET_DETERMINED BINLOG ' qyhTVRMBAAAALQAAAPIBAAAAAGwAAAAAAAEABHRlc3QAAnQxAAEDAAGFeQAI qyhTVR4BAAAAKAAAABoCAAAAAGwAAAAAAAEAAgAB//4BAAAAcd2jbw== '/*!*/; ERROR HY000: @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON. SET @@SESSION.GTID_NEXT = 'AUTOMATIC'; include/rpl_set_gtid_mode.inc [OFF on servers 1,2] ---- 1.7.1 Can set AUTOMATIC after NOT_YET_DETERMINED ---- BINLOG 'C9BAVQ8BAAAAdwAAAHsAAAABAAQANS43LjgtcmMtZGVidWctbG9nAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAL0EBVEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjUA AdVXOVA='; SELECT @@SESSION.GTID_NEXT; @@SESSION.GTID_NEXT NOT_YET_DETERMINED SET @@SESSION.GTID_NEXT = 'AUTOMATIC'; SELECT @@SESSION.GTID_NEXT; @@SESSION.GTID_NEXT AUTOMATIC ---- 1.7.2 Can set DEFAULT after NOT_YET_DETERMINED ---- BINLOG 'C9BAVQ8BAAAAdwAAAHsAAAABAAQANS43LjgtcmMtZGVidWctbG9nAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAL0EBVEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjUA AdVXOVA='; SELECT @@SESSION.GTID_NEXT; @@SESSION.GTID_NEXT NOT_YET_DETERMINED SET @@SESSION.GTID_NEXT = DEFAULT; SELECT @@SESSION.GTID_NEXT; @@SESSION.GTID_NEXT AUTOMATIC ---- 1.7.3 Can set ANONYMOUS after NOT_YET_DETERMINED ---- BINLOG 'C9BAVQ8BAAAAdwAAAHsAAAABAAQANS43LjgtcmMtZGVidWctbG9nAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAL0EBVEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjUA AdVXOVA='; SELECT @@SESSION.GTID_NEXT; @@SESSION.GTID_NEXT NOT_YET_DETERMINED SET @@SESSION.GTID_NEXT = 'ANONYMOUS'; SELECT @@SESSION.GTID_NEXT; @@SESSION.GTID_NEXT ANONYMOUS ROLLBACK; SET @@SESSION.GTID_NEXT = 'AUTOMATIC'; ---- 1.7.4 Can set GTID after NOT_YET_DETERMINED ---- SET GLOBAL GTID_MODE = OFF_PERMISSIVE; BINLOG 'C9BAVQ8BAAAAdwAAAHsAAAABAAQANS43LjgtcmMtZGVidWctbG9nAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAL0EBVEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjUA AdVXOVA='; SELECT @@SESSION.GTID_NEXT; @@SESSION.GTID_NEXT NOT_YET_DETERMINED SET GTID_NEXT = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1'; SELECT @@SESSION.GTID_NEXT; @@SESSION.GTID_NEXT aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1 ROLLBACK; SET @@SESSION.GTID_NEXT = 'AUTOMATIC'; SET GLOBAL GTID_MODE = OFF; #### 2. CHANGE MASTER TO MASTER_LOG_POS #### include/stop_slave_io.inc INSERT INTO t1 VALUES (1); CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE = 'FILE', SOURCE_LOG_POS = POSITION; include/start_slave_io.inc include/sync_slave_sql_with_master.inc #### 3. CHANGE MASTER TO RELAY_LOG_POS #### include/stop_slave_sql.inc INSERT INTO t1 VALUES (2); include/sync_slave_io_with_master.inc CHANGE REPLICATION SOURCE TO RELAY_LOG_FILE = 'FILE', RELAY_LOG_POS = POSITION; include/start_slave.inc include/sync_slave_sql_with_master.inc #### Clean up #### DROP TABLE t1; include/rpl_end.inc