| 1 |
include/master-slave.inc |
| 2 |
Warnings: |
| 3 |
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. |
| 4 |
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. |
| 5 |
[connection master] |
| 6 |
#### Initialize #### |
| 7 |
CREATE TABLE t1 (a INT); |
| 8 |
include/sync_slave_sql_with_master.inc |
| 9 |
CALL mtr.add_suppression('QUERY.*COMMIT or ROLLBACK.* or XID_LOG_EVENT is not expected in an event stream outside a transaction'); |
| 10 |
CALL mtr.add_suppression('An unexpected event sequence was detected by the IO thread while queuing the event received from source'); |
| 11 |
#### 1. Check basic properties of GTID_NEXT = NOT_YET_DETERMINED #### |
| 12 |
---- 1.1. Format_description_log_event sets NOT_YET_DETERMINED ---- |
| 13 |
BINLOG 'C9BAVQ8BAAAAdwAAAHsAAAABAAQANS43LjgtcmMtZGVidWctbG9nAAAAAAAAAAAAAAAAAAAAAAAA |
| 14 |
AAAAAAAAAAAAAAAAAAAL0EBVEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjUA |
| 15 |
AdVXOVA='; |
| 16 |
SELECT @@SESSION.GTID_NEXT; |
| 17 |
@@SESSION.GTID_NEXT |
| 18 |
NOT_YET_DETERMINED |
| 19 |
---- 1.2. DO, SELECT, SHOW, and SET don't change GTID_NEXT ---- |
| 20 |
DO 1; |
| 21 |
SELECT 1; |
| 22 |
1 |
| 23 |
1 |
| 24 |
SHOW VARIABLES LIKE 'whatever'; |
| 25 |
Variable_name Value |
| 26 |
SET @foo = 1; |
| 27 |
SELECT @@SESSION.GTID_NEXT; |
| 28 |
@@SESSION.GTID_NEXT |
| 29 |
NOT_YET_DETERMINED |
| 30 |
---- 1.3. Other stm changes NOT_YET_DETERMINED to ANONYMOUS if GTID_MODE=OFF ---- |
| 31 |
BEGIN; |
| 32 |
SELECT @@SESSION.GTID_NEXT; |
| 33 |
@@SESSION.GTID_NEXT |
| 34 |
ANONYMOUS |
| 35 |
ROLLBACK; |
| 36 |
SET @@SESSION.GTID_NEXT = 'AUTOMATIC'; |
| 37 |
---- 1.4. Row injection changes NOT_YET_DETERMINED to ANONYMOUS if GTID_MODE=OFF ---- |
| 38 |
BINLOG 'C9BAVQ8BAAAAdwAAAHsAAAABAAQANS43LjgtcmMtZGVidWctbG9nAAAAAAAAAAAAAAAAAAAAAAAA |
| 39 |
AAAAAAAAAAAAAAAAAAAL0EBVEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjUA |
| 40 |
AdVXOVA='; |
| 41 |
SELECT @@SESSION.GTID_NEXT; |
| 42 |
@@SESSION.GTID_NEXT |
| 43 |
NOT_YET_DETERMINED |
| 44 |
BINLOG ' |
| 45 |
qyhTVRMBAAAALQAAAPIBAAAAAGwAAAAAAAEABHRlc3QAAnQxAAEDAAGFeQAI |
| 46 |
qyhTVR4BAAAAKAAAABoCAAAAAGwAAAAAAAEAAgAB//4BAAAAcd2jbw== |
| 47 |
'/*!*/; |
| 48 |
SELECT @@SESSION.GTID_NEXT; |
| 49 |
@@SESSION.GTID_NEXT |
| 50 |
ANONYMOUS |
| 51 |
ROLLBACK; |
| 52 |
SET @@SESSION.GTID_NEXT = 'AUTOMATIC'; |
| 53 |
---- 1.5. Other stm generates error for NOT_YET_DETERMINED if GTID_MODE=ON ---- |
| 54 |
include/rpl_set_gtid_mode.inc [ON on servers 1,2] |
| 55 |
BINLOG 'C9BAVQ8BAAAAdwAAAHsAAAABAAQANS43LjgtcmMtZGVidWctbG9nAAAAAAAAAAAAAAAAAAAAAAAA |
| 56 |
AAAAAAAAAAAAAAAAAAAL0EBVEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjUA |
| 57 |
AdVXOVA='; |
| 58 |
SELECT @@SESSION.GTID_NEXT; |
| 59 |
@@SESSION.GTID_NEXT |
| 60 |
NOT_YET_DETERMINED |
| 61 |
BEGIN; |
| 62 |
ERROR HY000: @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON. |
| 63 |
SET @@SESSION.GTID_NEXT = 'AUTOMATIC'; |
| 64 |
---- 1.6. Row injection generates error for NOT_YET_DETERMINED if GTID_MODE=ON ---- |
| 65 |
BINLOG 'C9BAVQ8BAAAAdwAAAHsAAAABAAQANS43LjgtcmMtZGVidWctbG9nAAAAAAAAAAAAAAAAAAAAAAAA |
| 66 |
AAAAAAAAAAAAAAAAAAAL0EBVEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjUA |
| 67 |
AdVXOVA='; |
| 68 |
SELECT @@SESSION.GTID_NEXT; |
| 69 |
@@SESSION.GTID_NEXT |
| 70 |
NOT_YET_DETERMINED |
| 71 |
BINLOG ' |
| 72 |
qyhTVRMBAAAALQAAAPIBAAAAAGwAAAAAAAEABHRlc3QAAnQxAAEDAAGFeQAI |
| 73 |
qyhTVR4BAAAAKAAAABoCAAAAAGwAAAAAAAEAAgAB//4BAAAAcd2jbw== |
| 74 |
'/*!*/; |
| 75 |
ERROR HY000: @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON. |
| 76 |
SET @@SESSION.GTID_NEXT = 'AUTOMATIC'; |
| 77 |
include/rpl_set_gtid_mode.inc [OFF on servers 1,2] |
| 78 |
---- 1.7.1 Can set AUTOMATIC after NOT_YET_DETERMINED ---- |
| 79 |
BINLOG 'C9BAVQ8BAAAAdwAAAHsAAAABAAQANS43LjgtcmMtZGVidWctbG9nAAAAAAAAAAAAAAAAAAAAAAAA |
| 80 |
AAAAAAAAAAAAAAAAAAAL0EBVEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjUA |
| 81 |
AdVXOVA='; |
| 82 |
SELECT @@SESSION.GTID_NEXT; |
| 83 |
@@SESSION.GTID_NEXT |
| 84 |
NOT_YET_DETERMINED |
| 85 |
SET @@SESSION.GTID_NEXT = 'AUTOMATIC'; |
| 86 |
SELECT @@SESSION.GTID_NEXT; |
| 87 |
@@SESSION.GTID_NEXT |
| 88 |
AUTOMATIC |
| 89 |
---- 1.7.2 Can set DEFAULT after NOT_YET_DETERMINED ---- |
| 90 |
BINLOG 'C9BAVQ8BAAAAdwAAAHsAAAABAAQANS43LjgtcmMtZGVidWctbG9nAAAAAAAAAAAAAAAAAAAAAAAA |
| 91 |
AAAAAAAAAAAAAAAAAAAL0EBVEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjUA |
| 92 |
AdVXOVA='; |
| 93 |
SELECT @@SESSION.GTID_NEXT; |
| 94 |
@@SESSION.GTID_NEXT |
| 95 |
NOT_YET_DETERMINED |
| 96 |
SET @@SESSION.GTID_NEXT = DEFAULT; |
| 97 |
SELECT @@SESSION.GTID_NEXT; |
| 98 |
@@SESSION.GTID_NEXT |
| 99 |
AUTOMATIC |
| 100 |
---- 1.7.3 Can set ANONYMOUS after NOT_YET_DETERMINED ---- |
| 101 |
BINLOG 'C9BAVQ8BAAAAdwAAAHsAAAABAAQANS43LjgtcmMtZGVidWctbG9nAAAAAAAAAAAAAAAAAAAAAAAA |
| 102 |
AAAAAAAAAAAAAAAAAAAL0EBVEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjUA |
| 103 |
AdVXOVA='; |
| 104 |
SELECT @@SESSION.GTID_NEXT; |
| 105 |
@@SESSION.GTID_NEXT |
| 106 |
NOT_YET_DETERMINED |
| 107 |
SET @@SESSION.GTID_NEXT = 'ANONYMOUS'; |
| 108 |
SELECT @@SESSION.GTID_NEXT; |
| 109 |
@@SESSION.GTID_NEXT |
| 110 |
ANONYMOUS |
| 111 |
ROLLBACK; |
| 112 |
SET @@SESSION.GTID_NEXT = 'AUTOMATIC'; |
| 113 |
---- 1.7.4 Can set GTID after NOT_YET_DETERMINED ---- |
| 114 |
SET GLOBAL GTID_MODE = OFF_PERMISSIVE; |
| 115 |
BINLOG 'C9BAVQ8BAAAAdwAAAHsAAAABAAQANS43LjgtcmMtZGVidWctbG9nAAAAAAAAAAAAAAAAAAAAAAAA |
| 116 |
AAAAAAAAAAAAAAAAAAAL0EBVEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjUA |
| 117 |
AdVXOVA='; |
| 118 |
SELECT @@SESSION.GTID_NEXT; |
| 119 |
@@SESSION.GTID_NEXT |
| 120 |
NOT_YET_DETERMINED |
| 121 |
SET GTID_NEXT = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1'; |
| 122 |
SELECT @@SESSION.GTID_NEXT; |
| 123 |
@@SESSION.GTID_NEXT |
| 124 |
aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1 |
| 125 |
ROLLBACK; |
| 126 |
SET @@SESSION.GTID_NEXT = 'AUTOMATIC'; |
| 127 |
SET GLOBAL GTID_MODE = OFF; |
| 128 |
#### 2. CHANGE MASTER TO MASTER_LOG_POS #### |
| 129 |
include/stop_slave_io.inc |
| 130 |
INSERT INTO t1 VALUES (1); |
| 131 |
CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE = 'FILE', SOURCE_LOG_POS = POSITION; |
| 132 |
include/start_slave_io.inc |
| 133 |
include/sync_slave_sql_with_master.inc |
| 134 |
#### 3. CHANGE MASTER TO RELAY_LOG_POS #### |
| 135 |
include/stop_slave_sql.inc |
| 136 |
INSERT INTO t1 VALUES (2); |
| 137 |
include/sync_slave_io_with_master.inc |
| 138 |
CHANGE REPLICATION SOURCE TO RELAY_LOG_FILE = 'FILE', RELAY_LOG_POS = POSITION; |
| 139 |
include/start_slave.inc |
| 140 |
include/sync_slave_sql_with_master.inc |
| 141 |
#### Clean up #### |
| 142 |
DROP TABLE t1; |
| 143 |
include/rpl_end.inc |