| 1 |
include/rpl_init.inc [topology=1->2->3] |
| 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 |
Warnings: |
| 6 |
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. |
| 7 |
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. |
| 8 |
### Testing with InnoDB storage engine |
| 9 |
CON: 'server_1', IMG: 'MINIMAL', RESTART SLAVE: 'N' |
| 10 |
SET SESSION binlog_row_image= 'MINIMAL'; |
| 11 |
SET GLOBAL binlog_row_image= 'MINIMAL'; |
| 12 |
FLUSH TABLES; |
| 13 |
SHOW VARIABLES LIKE 'binlog_row_image'; |
| 14 |
Variable_name Value |
| 15 |
binlog_row_image MINIMAL |
| 16 |
CON: 'server_2', IMG: 'MINIMAL', RESTART SLAVE: 'Y' |
| 17 |
SET SESSION binlog_row_image= 'MINIMAL'; |
| 18 |
SET GLOBAL binlog_row_image= 'MINIMAL'; |
| 19 |
include/stop_slave.inc |
| 20 |
include/start_slave.inc |
| 21 |
FLUSH TABLES; |
| 22 |
SHOW VARIABLES LIKE 'binlog_row_image'; |
| 23 |
Variable_name Value |
| 24 |
binlog_row_image MINIMAL |
| 25 |
CON: 'server_3', IMG: 'MINIMAL', RESTART SLAVE: 'Y' |
| 26 |
SET SESSION binlog_row_image= 'MINIMAL'; |
| 27 |
SET GLOBAL binlog_row_image= 'MINIMAL'; |
| 28 |
include/stop_slave.inc |
| 29 |
include/start_slave.inc |
| 30 |
FLUSH TABLES; |
| 31 |
SHOW VARIABLES LIKE 'binlog_row_image'; |
| 32 |
Variable_name Value |
| 33 |
binlog_row_image MINIMAL |
| 34 |
#### case #1: AI: no values logged |
| 35 |
CREATE TABLE t1 (c1 int DEFAULT 100) Engine=ENGINE; |
| 36 |
INSERT INTO t1 VALUES (); |
| 37 |
SELECT * FROM t1; |
| 38 |
c1 |
| 39 |
100 |
| 40 |
include/rpl_sync.inc |
| 41 |
SELECT * FROM t1; |
| 42 |
c1 |
| 43 |
100 |
| 44 |
SELECT * FROM t1; |
| 45 |
c1 |
| 46 |
100 |
| 47 |
DROP TABLE t1; |
| 48 |
include/rpl_sync.inc |
| 49 |
#### case #2: AI: not empty but slave does not have usable data for its columns (INSERT) |
| 50 |
CREATE TABLE t1 (c1 int DEFAULT 100) Engine=ENGINE; |
| 51 |
SET SQL_LOG_BIN=0; |
| 52 |
CREATE TABLE t1 (c1 int DEFAULT 100) Engine=ENGINE; |
| 53 |
SET SQL_LOG_BIN=1; |
| 54 |
SET SQL_LOG_BIN=0; |
| 55 |
CREATE TABLE t1 (c1 int DEFAULT 100, c2 int, primary key(c2)) Engine=ENGINE; |
| 56 |
SET SQL_LOG_BIN=1; |
| 57 |
INSERT INTO t1(c2) VALUES (1); |
| 58 |
SELECT * FROM t1; |
| 59 |
c1 c2 |
| 60 |
100 1 |
| 61 |
include/rpl_sync.inc |
| 62 |
SELECT * FROM t1; |
| 63 |
c1 |
| 64 |
100 |
| 65 |
SELECT * FROM t1; |
| 66 |
c1 |
| 67 |
100 |
| 68 |
DROP TABLE t1; |
| 69 |
include/rpl_sync.inc |
| 70 |
#### case #3: BI: usable columns on the slave, AI: no usable columns on the slave |
| 71 |
CREATE TABLE t1 (c1 int DEFAULT 100) Engine=ENGINE; |
| 72 |
SET SQL_LOG_BIN=0; |
| 73 |
CREATE TABLE t1 (c1 int DEFAULT 100) Engine=ENGINE; |
| 74 |
SET SQL_LOG_BIN=1; |
| 75 |
SET SQL_LOG_BIN=0; |
| 76 |
CREATE TABLE t1 (c1 int DEFAULT 100, c2 int) Engine=ENGINE; |
| 77 |
SET SQL_LOG_BIN=1; |
| 78 |
INSERT INTO t1 VALUES (1,1); |
| 79 |
SELECT * FROM t1; |
| 80 |
c1 c2 |
| 81 |
1 1 |
| 82 |
include/rpl_sync.inc |
| 83 |
SELECT * FROM t1; |
| 84 |
c1 |
| 85 |
1 |
| 86 |
SELECT * FROM t1; |
| 87 |
c1 |
| 88 |
1 |
| 89 |
UPDATE t1 SET c2=2 WHERE c1=1 AND c2=1; |
| 90 |
SELECT * FROM t1; |
| 91 |
c1 c2 |
| 92 |
1 2 |
| 93 |
include/rpl_sync.inc |
| 94 |
SELECT * FROM t1; |
| 95 |
c1 |
| 96 |
1 |
| 97 |
SELECT * FROM t1; |
| 98 |
c1 |
| 99 |
1 |
| 100 |
DROP TABLE t1; |
| 101 |
include/rpl_sync.inc |
| 102 |
#### case #4: AI, BI: no usable columns on the slave (NOOP UPDATE). |
| 103 |
#### |
| 104 |
CREATE TABLE t1 (c1 int DEFAULT 100) Engine=ENGINE; |
| 105 |
SET SQL_LOG_BIN=0; |
| 106 |
CREATE TABLE t1 (c1 int DEFAULT 100) Engine=ENGINE; |
| 107 |
SET SQL_LOG_BIN=1; |
| 108 |
SET SQL_LOG_BIN=0; |
| 109 |
CREATE TABLE t1 (c1 int DEFAULT 100, c2 int, c3 int, primary key(c2)) Engine=ENGINE; |
| 110 |
SET SQL_LOG_BIN=1; |
| 111 |
INSERT INTO t1 VALUES (1,1,1); |
| 112 |
SELECT * FROM t1; |
| 113 |
c1 c2 c3 |
| 114 |
1 1 1 |
| 115 |
include/rpl_sync.inc |
| 116 |
SELECT * FROM t1; |
| 117 |
c1 |
| 118 |
1 |
| 119 |
SELECT * FROM t1; |
| 120 |
c1 |
| 121 |
1 |
| 122 |
UPDATE t1 SET c3=300 WHERE c2=1; |
| 123 |
SELECT * FROM t1; |
| 124 |
c1 c2 c3 |
| 125 |
1 1 300 |
| 126 |
include/rpl_sync.inc |
| 127 |
SELECT * FROM t1; |
| 128 |
c1 |
| 129 |
1 |
| 130 |
SELECT * FROM t1; |
| 131 |
c1 |
| 132 |
1 |
| 133 |
DROP TABLE t1; |
| 134 |
include/rpl_sync.inc |
| 135 |
#### case #5: BI: no usable columns on the slave, AI: usable columns on the slave (slave must stop). |
| 136 |
#### |
| 137 |
CREATE TABLE t1 (c1 INT DEFAULT 100) Engine=ENGINE; |
| 138 |
SET SQL_LOG_BIN=0; |
| 139 |
CREATE TABLE t1 (c1 INT DEFAULT 100) Engine=ENGINE; |
| 140 |
SET SQL_LOG_BIN=1; |
| 141 |
SET SQL_LOG_BIN=0; |
| 142 |
CREATE TABLE t1 (c1 INT DEFAULT 100, c2 INT PRIMARY KEY) Engine=ENGINE; |
| 143 |
SET SQL_LOG_BIN=1; |
| 144 |
INSERT INTO t1 VALUES (1,1); |
| 145 |
SELECT * FROM t1; |
| 146 |
c1 c2 |
| 147 |
1 1 |
| 148 |
include/rpl_sync.inc |
| 149 |
SELECT * FROM t1; |
| 150 |
c1 |
| 151 |
1 |
| 152 |
SELECT * FROM t1; |
| 153 |
c1 |
| 154 |
1 |
| 155 |
UPDATE t1 SET c1=300 WHERE c2=1; |
| 156 |
SELECT * FROM t1; |
| 157 |
c1 c2 |
| 158 |
300 1 |
| 159 |
SET SQL_LOG_BIN=0; |
| 160 |
call mtr.add_suppression("Replica: Can\'t find record in \'t1\' Error_code: MY-001032"); |
| 161 |
call mtr.add_suppression("Replica SQL for channel '': .*Could not execute Update_rows event on table test.t1; Can.t find record in .t1.* Error_code: MY-001032"); |
| 162 |
call mtr.add_suppression("The replica coordinator and worker threads are stopped, possibly leaving data in inconsistent state"); |
| 163 |
SET SQL_LOG_BIN=1; |
| 164 |
include/wait_for_slave_sql_error_and_skip.inc [errno=1032] |
| 165 |
DROP TABLE t1; |
| 166 |
include/rpl_sync.inc |
| 167 |
CON: 'server_1', IMG: 'FULL', RESTART SLAVE: 'N' |
| 168 |
SET SESSION binlog_row_image= 'FULL'; |
| 169 |
SET GLOBAL binlog_row_image= 'FULL'; |
| 170 |
FLUSH TABLES; |
| 171 |
SHOW VARIABLES LIKE 'binlog_row_image'; |
| 172 |
Variable_name Value |
| 173 |
binlog_row_image FULL |
| 174 |
CON: 'server_2', IMG: 'FULL', RESTART SLAVE: 'Y' |
| 175 |
SET SESSION binlog_row_image= 'FULL'; |
| 176 |
SET GLOBAL binlog_row_image= 'FULL'; |
| 177 |
include/stop_slave.inc |
| 178 |
include/start_slave.inc |
| 179 |
FLUSH TABLES; |
| 180 |
SHOW VARIABLES LIKE 'binlog_row_image'; |
| 181 |
Variable_name Value |
| 182 |
binlog_row_image FULL |
| 183 |
CON: 'server_3', IMG: 'FULL', RESTART SLAVE: 'Y' |
| 184 |
SET SESSION binlog_row_image= 'FULL'; |
| 185 |
SET GLOBAL binlog_row_image= 'FULL'; |
| 186 |
include/stop_slave.inc |
| 187 |
include/start_slave.inc |
| 188 |
FLUSH TABLES; |
| 189 |
SHOW VARIABLES LIKE 'binlog_row_image'; |
| 190 |
Variable_name Value |
| 191 |
binlog_row_image FULL |
| 192 |
include/rpl_end.inc |