| 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 |
*** Test 1 ******************************************************** |
| 7 |
* test native NDB$MAX_INS() conflict resolution |
| 8 |
* test with both allowed "conflict column" types, uint32 and uint64 |
| 9 |
******************************************************************* |
| 10 |
set @save_luaw = @@global.ndb_log_update_as_write; |
| 11 |
set @save_luo = @@global.ndb_log_updated_only; |
| 12 |
set global ndb_log_update_as_write=0; |
| 13 |
set global ndb_log_updated_only=0; |
| 14 |
insert into mysql.ndb_replication values ("test", "t1", 0, 0, "NDB$MAX_INS(X)"); |
| 15 |
create table t1 (a int, b varchar(32), X int unsigned, d varchar(9), primary key (a, d)) engine ndb; |
| 16 |
create table t2 (a int, b varchar(32), c int unsigned, d varchar(9), primary key (a, d)) engine ndb; |
| 17 |
************************************************ |
| 18 |
* some basic positive testing, i.e. no conflicts |
| 19 |
************************************************ |
| 20 |
*** insert some data |
| 21 |
begin; |
| 22 |
insert into t1 values (1, "Master t1 a=1 at X=1", 1, '1'); |
| 23 |
insert into t1 values (2, "Master t1 a=2 at X=1", 1, '12'); |
| 24 |
insert into t1 values (3, "Master t1 a=3 at X=1", 1, '123'); |
| 25 |
insert into t2 values (1, "Master t2 a=1 at c=1", 1, '1'); |
| 26 |
insert into t2 values (2, "Master t2 a=2 at c=1", 1, '12'); |
| 27 |
insert into t2 values (3, "Master t2 a=3 at c=1", 1, '123'); |
| 28 |
commit; |
| 29 |
*** master - update all |
| 30 |
begin; |
| 31 |
update t1 set b="Master t1 X=2", X=X+1; |
| 32 |
update t2 set b="Master t2 c=2", c=c+1; |
| 33 |
commit; |
| 34 |
*** slave - check update all |
| 35 |
select * from t1 order by a, d; |
| 36 |
a b X d |
| 37 |
1 Master t1 X=2 2 1 |
| 38 |
2 Master t1 X=2 2 12 |
| 39 |
3 Master t1 X=2 2 123 |
| 40 |
select * from t2 order by a, d; |
| 41 |
a b c d |
| 42 |
1 Master t2 c=2 2 1 |
| 43 |
2 Master t2 c=2 2 12 |
| 44 |
3 Master t2 c=2 2 123 |
| 45 |
Conflict algorithm counter diff : |
| 46 |
Exception table contents |
| 47 |
SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; |
| 48 |
server_id master_server_id master_epoch count a d |
| 49 |
SELECT * FROM `t1$EX` ORDER BY a, d; |
| 50 |
server_id master_server_id master_epoch count a d |
| 51 |
*** master - update 2 rows in different tables |
| 52 |
update t1 set b="Master t1 a=2 at X=3", X=X+1 where a=2; |
| 53 |
update t2 set b="Master t2 a=3 at c=3", c=c+1 where a=3; |
| 54 |
*** slave - check update 2 rows in different tables |
| 55 |
select * from t1 order by a, d; |
| 56 |
a b X d |
| 57 |
1 Master t1 X=2 2 1 |
| 58 |
2 Master t1 a=2 at X=3 3 12 |
| 59 |
3 Master t1 X=2 2 123 |
| 60 |
select * from t2 order by a, d; |
| 61 |
a b c d |
| 62 |
1 Master t2 c=2 2 1 |
| 63 |
2 Master t2 c=2 2 12 |
| 64 |
3 Master t2 a=3 at c=3 3 123 |
| 65 |
Conflict algorithm counter diff : |
| 66 |
Exception table contents |
| 67 |
SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; |
| 68 |
server_id master_server_id master_epoch count a d |
| 69 |
SELECT * FROM `t1$EX` ORDER BY a, d; |
| 70 |
server_id master_server_id master_epoch count a d |
| 71 |
*** master - delete all |
| 72 |
delete from t1; |
| 73 |
delete from t2; |
| 74 |
*** slave - check delete all |
| 75 |
select * from t1 order by a, d; |
| 76 |
a b X d |
| 77 |
select * from t2 order by a, d; |
| 78 |
a b c d |
| 79 |
Conflict algorithm counter diff : |
| 80 |
Exception table contents |
| 81 |
SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; |
| 82 |
server_id master_server_id master_epoch count a d |
| 83 |
SELECT * FROM `t1$EX` ORDER BY a, d; |
| 84 |
server_id master_server_id master_epoch count a d |
| 85 |
************************ |
| 86 |
* start conflict testing |
| 87 |
************************ |
| 88 |
*** master - insert some data |
| 89 |
begin; |
| 90 |
insert into t1 values (1, "Master t1 a=1 at X=10", 10, '111'); |
| 91 |
insert into t1 values (2, "Master t1 a=2 at X=10", 10, '111222'); |
| 92 |
insert into t1 values (3, "Master t1 a=3 at X=10", 10, '111222333'); |
| 93 |
insert into t1 values (4, "Master t1 a=4 at X=10", 10, '111222333'); |
| 94 |
insert into t2 values (1, "Master t2 a=1 at c=10", 10, '111'); |
| 95 |
insert into t2 values (2, "Master t2 a=2 at c=10", 10, '111222'); |
| 96 |
insert into t2 values (3, "Master t2 a=3 at c=10", 10, '111222333'); |
| 97 |
insert into t2 values (4, "Master t2 a=4 at c=10", 10, '111222333'); |
| 98 |
commit; |
| 99 |
*** slave - check conflict info, no conflicts yet |
| 100 |
Conflict algorithm counter diff : |
| 101 |
Exception table contents |
| 102 |
SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; |
| 103 |
server_id master_server_id master_epoch count a d |
| 104 |
SELECT * FROM `t1$EX` ORDER BY a, d; |
| 105 |
server_id master_server_id master_epoch count a d |
| 106 |
*** slave - check insert some data |
| 107 |
select * from t1 order by a, d; |
| 108 |
a b X d |
| 109 |
1 Master t1 a=1 at X=10 10 111 |
| 110 |
2 Master t1 a=2 at X=10 10 111222 |
| 111 |
3 Master t1 a=3 at X=10 10 111222333 |
| 112 |
4 Master t1 a=4 at X=10 10 111222333 |
| 113 |
select * from t2 order by a, d; |
| 114 |
a b c d |
| 115 |
1 Master t2 a=1 at c=10 10 111 |
| 116 |
2 Master t2 a=2 at c=10 10 111222 |
| 117 |
3 Master t2 a=3 at c=10 10 111222333 |
| 118 |
4 Master t2 a=4 at c=10 10 111222333 |
| 119 |
*** slave - Insert some data to cause conflicts |
| 120 |
begin; |
| 121 |
insert into t1 values (5, "Slave t1 a=5 at X=12", 12, '111'); |
| 122 |
insert into t1 values (6, "Slave t1 a=6 at X=12", 12, '111222'); |
| 123 |
insert into t1 values (7, "Slave t1 a=7 at X=12", 12, '111222333'); |
| 124 |
insert into t1 values (8, "Slave t1 a=8 at X=12", 12, '111222333'); |
| 125 |
insert into t2 values (5, "Slave t2 a=5 at c=12", 12, '111'); |
| 126 |
insert into t2 values (6, "Slave t2 a=6 at c=12", 12, '111222'); |
| 127 |
insert into t2 values (7, "Slave t2 a=7 at c=12", 12, '111222333'); |
| 128 |
insert into t2 values (8, "Slave t2 a=8 at c=12", 12, '111222333'); |
| 129 |
commit; |
| 130 |
*** master - Insert some data to cause conflicts |
| 131 |
begin; |
| 132 |
insert into t1 values (5, "Master t1 a=5 at X=11", 11, '111'); |
| 133 |
insert into t1 values (6, "Master t1 a=6 at X=11", 11, '111222'); |
| 134 |
insert into t2 values (5, "Master t2 a=5 at c=11", 11, '111'); |
| 135 |
insert into t2 values (6, "Master t2 a=6 at c=11", 11, '111222'); |
| 136 |
commit; |
| 137 |
*** slave - check conflict info, there should be some |
| 138 |
Conflict algorithm counter diff : |
| 139 |
ndb_conflict_fn_max_ins |
| 140 |
2 |
| 141 |
Exception table contents |
| 142 |
SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; |
| 143 |
server_id master_server_id master_epoch count a d |
| 144 |
2 1 # 1 # # |
| 145 |
2 1 # 2 # # |
| 146 |
SELECT * FROM `t1$EX` ORDER BY a, d; |
| 147 |
server_id master_server_id master_epoch count a d |
| 148 |
2 1 # # 5 111 |
| 149 |
2 1 # # 6 111222 |
| 150 |
*** slave - check insert some data that causes conflicts |
| 151 |
select * from t1 order by a, d; |
| 152 |
a b X d |
| 153 |
1 Master t1 a=1 at X=10 10 111 |
| 154 |
2 Master t1 a=2 at X=10 10 111222 |
| 155 |
3 Master t1 a=3 at X=10 10 111222333 |
| 156 |
4 Master t1 a=4 at X=10 10 111222333 |
| 157 |
5 Slave t1 a=5 at X=12 12 111 |
| 158 |
6 Slave t1 a=6 at X=12 12 111222 |
| 159 |
7 Slave t1 a=7 at X=12 12 111222333 |
| 160 |
8 Slave t1 a=8 at X=12 12 111222333 |
| 161 |
select * from t2 order by a, d; |
| 162 |
a b c d |
| 163 |
1 Master t2 a=1 at c=10 10 111 |
| 164 |
2 Master t2 a=2 at c=10 10 111222 |
| 165 |
3 Master t2 a=3 at c=10 10 111222333 |
| 166 |
4 Master t2 a=4 at c=10 10 111222333 |
| 167 |
5 Master t2 a=5 at c=11 11 111 |
| 168 |
6 Master t2 a=6 at c=11 11 111222 |
| 169 |
7 Slave t2 a=7 at c=12 12 111222333 |
| 170 |
8 Slave t2 a=8 at c=12 12 111222333 |
| 171 |
*** master - insert some data, use higher timestamp |
| 172 |
begin; |
| 173 |
insert into t1 values (7, "Master t1 a=7 at X=13", 13, '111222333'); |
| 174 |
insert into t1 values (8, "Master t1 a=8 at X=13", 13, '111222333'); |
| 175 |
insert into t2 values (7, "Master t2 a=7 at c=13", 13, '111222333'); |
| 176 |
insert into t2 values (8, "Master t2 a=8 at c=13", 13, '111222333'); |
| 177 |
commit; |
| 178 |
*** slave - check conflict info, change depends on calling test |
| 179 |
Conflict algorithm counter diff : |
| 180 |
ndb_conflict_fn_max_ins |
| 181 |
2 |
| 182 |
Exception table contents |
| 183 |
SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; |
| 184 |
server_id master_server_id master_epoch count a d |
| 185 |
2 1 # 1 # # |
| 186 |
2 1 # 2 # # |
| 187 |
SELECT * FROM `t1$EX` ORDER BY a, d; |
| 188 |
server_id master_server_id master_epoch count a d |
| 189 |
2 1 # # 5 111 |
| 190 |
2 1 # # 6 111222 |
| 191 |
*** slave - check higer timestamp |
| 192 |
select * from t1 order by a, d; |
| 193 |
a b X d |
| 194 |
1 Master t1 a=1 at X=10 10 111 |
| 195 |
2 Master t1 a=2 at X=10 10 111222 |
| 196 |
3 Master t1 a=3 at X=10 10 111222333 |
| 197 |
4 Master t1 a=4 at X=10 10 111222333 |
| 198 |
5 Slave t1 a=5 at X=12 12 111 |
| 199 |
6 Slave t1 a=6 at X=12 12 111222 |
| 200 |
7 Master t1 a=7 at X=13 13 111222333 |
| 201 |
8 Master t1 a=8 at X=13 13 111222333 |
| 202 |
select * from t2 order by a, d; |
| 203 |
a b c d |
| 204 |
1 Master t2 a=1 at c=10 10 111 |
| 205 |
2 Master t2 a=2 at c=10 10 111222 |
| 206 |
3 Master t2 a=3 at c=10 10 111222333 |
| 207 |
4 Master t2 a=4 at c=10 10 111222333 |
| 208 |
5 Master t2 a=5 at c=11 11 111 |
| 209 |
6 Master t2 a=6 at c=11 11 111222 |
| 210 |
7 Master t2 a=7 at c=13 13 111222333 |
| 211 |
8 Master t2 a=8 at c=13 13 111222333 |
| 212 |
drop table t1, t2; |
| 213 |
*** Test 2 ******************************************************** |
| 214 |
* test native NDB$MAX_INS() conflict resolution |
| 215 |
* test with both allowed "conflict column" types, uint32 and uint64 |
| 216 |
******************************************************************* |
| 217 |
create table t1 (a int, b varchar(32), X bigint unsigned, d varchar(9), primary key (a, d)) engine ndb; |
| 218 |
create table t2 (a int, b varchar(32), c int unsigned, d varchar(9), primary key (a, d)) engine ndb; |
| 219 |
************************************************ |
| 220 |
* some basic positive testing, i.e. no conflicts |
| 221 |
************************************************ |
| 222 |
*** insert some data |
| 223 |
begin; |
| 224 |
insert into t1 values (1, "Master t1 a=1 at X=1", 1, '1'); |
| 225 |
insert into t1 values (2, "Master t1 a=2 at X=1", 1, '12'); |
| 226 |
insert into t1 values (3, "Master t1 a=3 at X=1", 1, '123'); |
| 227 |
insert into t2 values (1, "Master t2 a=1 at c=1", 1, '1'); |
| 228 |
insert into t2 values (2, "Master t2 a=2 at c=1", 1, '12'); |
| 229 |
insert into t2 values (3, "Master t2 a=3 at c=1", 1, '123'); |
| 230 |
commit; |
| 231 |
*** master - update all |
| 232 |
begin; |
| 233 |
update t1 set b="Master t1 X=2", X=X+1; |
| 234 |
update t2 set b="Master t2 c=2", c=c+1; |
| 235 |
commit; |
| 236 |
*** slave - check update all |
| 237 |
select * from t1 order by a, d; |
| 238 |
a b X d |
| 239 |
1 Master t1 X=2 2 1 |
| 240 |
2 Master t1 X=2 2 12 |
| 241 |
3 Master t1 X=2 2 123 |
| 242 |
select * from t2 order by a, d; |
| 243 |
a b c d |
| 244 |
1 Master t2 c=2 2 1 |
| 245 |
2 Master t2 c=2 2 12 |
| 246 |
3 Master t2 c=2 2 123 |
| 247 |
Conflict algorithm counter diff : |
| 248 |
Exception table contents |
| 249 |
SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; |
| 250 |
server_id master_server_id master_epoch count a d |
| 251 |
SELECT * FROM `t1$EX` ORDER BY a, d; |
| 252 |
server_id master_server_id master_epoch count a d |
| 253 |
*** master - update 2 rows in different tables |
| 254 |
update t1 set b="Master t1 a=2 at X=3", X=X+1 where a=2; |
| 255 |
update t2 set b="Master t2 a=3 at c=3", c=c+1 where a=3; |
| 256 |
*** slave - check update 2 rows in different tables |
| 257 |
select * from t1 order by a, d; |
| 258 |
a b X d |
| 259 |
1 Master t1 X=2 2 1 |
| 260 |
2 Master t1 a=2 at X=3 3 12 |
| 261 |
3 Master t1 X=2 2 123 |
| 262 |
select * from t2 order by a, d; |
| 263 |
a b c d |
| 264 |
1 Master t2 c=2 2 1 |
| 265 |
2 Master t2 c=2 2 12 |
| 266 |
3 Master t2 a=3 at c=3 3 123 |
| 267 |
Conflict algorithm counter diff : |
| 268 |
Exception table contents |
| 269 |
SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; |
| 270 |
server_id master_server_id master_epoch count a d |
| 271 |
SELECT * FROM `t1$EX` ORDER BY a, d; |
| 272 |
server_id master_server_id master_epoch count a d |
| 273 |
*** master - delete all |
| 274 |
delete from t1; |
| 275 |
delete from t2; |
| 276 |
*** slave - check delete all |
| 277 |
select * from t1 order by a, d; |
| 278 |
a b X d |
| 279 |
select * from t2 order by a, d; |
| 280 |
a b c d |
| 281 |
Conflict algorithm counter diff : |
| 282 |
Exception table contents |
| 283 |
SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; |
| 284 |
server_id master_server_id master_epoch count a d |
| 285 |
SELECT * FROM `t1$EX` ORDER BY a, d; |
| 286 |
server_id master_server_id master_epoch count a d |
| 287 |
************************ |
| 288 |
* start conflict testing |
| 289 |
************************ |
| 290 |
*** master - insert some data |
| 291 |
begin; |
| 292 |
insert into t1 values (1, "Master t1 a=1 at X=10", 10, '111'); |
| 293 |
insert into t1 values (2, "Master t1 a=2 at X=10", 10, '111222'); |
| 294 |
insert into t1 values (3, "Master t1 a=3 at X=10", 10, '111222333'); |
| 295 |
insert into t1 values (4, "Master t1 a=4 at X=10", 10, '111222333'); |
| 296 |
insert into t2 values (1, "Master t2 a=1 at c=10", 10, '111'); |
| 297 |
insert into t2 values (2, "Master t2 a=2 at c=10", 10, '111222'); |
| 298 |
insert into t2 values (3, "Master t2 a=3 at c=10", 10, '111222333'); |
| 299 |
insert into t2 values (4, "Master t2 a=4 at c=10", 10, '111222333'); |
| 300 |
commit; |
| 301 |
*** slave - check conflict info, no conflicts yet |
| 302 |
Conflict algorithm counter diff : |
| 303 |
Exception table contents |
| 304 |
SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; |
| 305 |
server_id master_server_id master_epoch count a d |
| 306 |
SELECT * FROM `t1$EX` ORDER BY a, d; |
| 307 |
server_id master_server_id master_epoch count a d |
| 308 |
*** slave - check insert some data |
| 309 |
select * from t1 order by a, d; |
| 310 |
a b X d |
| 311 |
1 Master t1 a=1 at X=10 10 111 |
| 312 |
2 Master t1 a=2 at X=10 10 111222 |
| 313 |
3 Master t1 a=3 at X=10 10 111222333 |
| 314 |
4 Master t1 a=4 at X=10 10 111222333 |
| 315 |
select * from t2 order by a, d; |
| 316 |
a b c d |
| 317 |
1 Master t2 a=1 at c=10 10 111 |
| 318 |
2 Master t2 a=2 at c=10 10 111222 |
| 319 |
3 Master t2 a=3 at c=10 10 111222333 |
| 320 |
4 Master t2 a=4 at c=10 10 111222333 |
| 321 |
*** slave - Insert some data to cause conflicts |
| 322 |
begin; |
| 323 |
insert into t1 values (5, "Slave t1 a=5 at X=12", 12, '111'); |
| 324 |
insert into t1 values (6, "Slave t1 a=6 at X=12", 12, '111222'); |
| 325 |
insert into t1 values (7, "Slave t1 a=7 at X=12", 12, '111222333'); |
| 326 |
insert into t1 values (8, "Slave t1 a=8 at X=12", 12, '111222333'); |
| 327 |
insert into t2 values (5, "Slave t2 a=5 at c=12", 12, '111'); |
| 328 |
insert into t2 values (6, "Slave t2 a=6 at c=12", 12, '111222'); |
| 329 |
insert into t2 values (7, "Slave t2 a=7 at c=12", 12, '111222333'); |
| 330 |
insert into t2 values (8, "Slave t2 a=8 at c=12", 12, '111222333'); |
| 331 |
commit; |
| 332 |
*** master - Insert some data to cause conflicts |
| 333 |
begin; |
| 334 |
insert into t1 values (5, "Master t1 a=5 at X=11", 11, '111'); |
| 335 |
insert into t1 values (6, "Master t1 a=6 at X=11", 11, '111222'); |
| 336 |
insert into t2 values (5, "Master t2 a=5 at c=11", 11, '111'); |
| 337 |
insert into t2 values (6, "Master t2 a=6 at c=11", 11, '111222'); |
| 338 |
commit; |
| 339 |
*** slave - check conflict info, there should be some |
| 340 |
Conflict algorithm counter diff : |
| 341 |
ndb_conflict_fn_max_ins |
| 342 |
2 |
| 343 |
Exception table contents |
| 344 |
SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; |
| 345 |
server_id master_server_id master_epoch count a d |
| 346 |
2 1 # 1 # # |
| 347 |
2 1 # 2 # # |
| 348 |
SELECT * FROM `t1$EX` ORDER BY a, d; |
| 349 |
server_id master_server_id master_epoch count a d |
| 350 |
2 1 # # 5 111 |
| 351 |
2 1 # # 6 111222 |
| 352 |
*** slave - check insert some data that causes conflicts |
| 353 |
select * from t1 order by a, d; |
| 354 |
a b X d |
| 355 |
1 Master t1 a=1 at X=10 10 111 |
| 356 |
2 Master t1 a=2 at X=10 10 111222 |
| 357 |
3 Master t1 a=3 at X=10 10 111222333 |
| 358 |
4 Master t1 a=4 at X=10 10 111222333 |
| 359 |
5 Slave t1 a=5 at X=12 12 111 |
| 360 |
6 Slave t1 a=6 at X=12 12 111222 |
| 361 |
7 Slave t1 a=7 at X=12 12 111222333 |
| 362 |
8 Slave t1 a=8 at X=12 12 111222333 |
| 363 |
select * from t2 order by a, d; |
| 364 |
a b c d |
| 365 |
1 Master t2 a=1 at c=10 10 111 |
| 366 |
2 Master t2 a=2 at c=10 10 111222 |
| 367 |
3 Master t2 a=3 at c=10 10 111222333 |
| 368 |
4 Master t2 a=4 at c=10 10 111222333 |
| 369 |
5 Master t2 a=5 at c=11 11 111 |
| 370 |
6 Master t2 a=6 at c=11 11 111222 |
| 371 |
7 Slave t2 a=7 at c=12 12 111222333 |
| 372 |
8 Slave t2 a=8 at c=12 12 111222333 |
| 373 |
*** master - insert some data, use higher timestamp |
| 374 |
begin; |
| 375 |
insert into t1 values (7, "Master t1 a=7 at X=13", 13, '111222333'); |
| 376 |
insert into t1 values (8, "Master t1 a=8 at X=13", 13, '111222333'); |
| 377 |
insert into t2 values (7, "Master t2 a=7 at c=13", 13, '111222333'); |
| 378 |
insert into t2 values (8, "Master t2 a=8 at c=13", 13, '111222333'); |
| 379 |
commit; |
| 380 |
*** slave - check conflict info, change depends on calling test |
| 381 |
Conflict algorithm counter diff : |
| 382 |
ndb_conflict_fn_max_ins |
| 383 |
2 |
| 384 |
Exception table contents |
| 385 |
SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; |
| 386 |
server_id master_server_id master_epoch count a d |
| 387 |
2 1 # 1 # # |
| 388 |
2 1 # 2 # # |
| 389 |
SELECT * FROM `t1$EX` ORDER BY a, d; |
| 390 |
server_id master_server_id master_epoch count a d |
| 391 |
2 1 # # 5 111 |
| 392 |
2 1 # # 6 111222 |
| 393 |
*** slave - check higer timestamp |
| 394 |
select * from t1 order by a, d; |
| 395 |
a b X d |
| 396 |
1 Master t1 a=1 at X=10 10 111 |
| 397 |
2 Master t1 a=2 at X=10 10 111222 |
| 398 |
3 Master t1 a=3 at X=10 10 111222333 |
| 399 |
4 Master t1 a=4 at X=10 10 111222333 |
| 400 |
5 Slave t1 a=5 at X=12 12 111 |
| 401 |
6 Slave t1 a=6 at X=12 12 111222 |
| 402 |
7 Master t1 a=7 at X=13 13 111222333 |
| 403 |
8 Master t1 a=8 at X=13 13 111222333 |
| 404 |
select * from t2 order by a, d; |
| 405 |
a b c d |
| 406 |
1 Master t2 a=1 at c=10 10 111 |
| 407 |
2 Master t2 a=2 at c=10 10 111222 |
| 408 |
3 Master t2 a=3 at c=10 10 111222333 |
| 409 |
4 Master t2 a=4 at c=10 10 111222333 |
| 410 |
5 Master t2 a=5 at c=11 11 111 |
| 411 |
6 Master t2 a=6 at c=11 11 111222 |
| 412 |
7 Master t2 a=7 at c=13 13 111222333 |
| 413 |
8 Master t2 a=8 at c=13 13 111222333 |
| 414 |
drop table t1, t2; |
| 415 |
*** Test 3 ******************************************************** |
| 416 |
* test native NDB$MAX_INS() conflict resolution |
| 417 |
* test that setting binlog update option via table works equally well |
| 418 |
******************************************************************* |
| 419 |
set global ndb_log_update_as_write=1; |
| 420 |
set global ndb_log_updated_only=1; |
| 421 |
update mysql.ndb_replication set binlog_type=7; |
| 422 |
create table t1 (a int, b varchar(32), X bigint unsigned, d varchar(9), primary key (a, d)) engine ndb; |
| 423 |
create table t2 (a int, b varchar(32), c int unsigned, d varchar(9), primary key (a, d)) engine ndb; |
| 424 |
************************************************ |
| 425 |
* some basic positive testing, i.e. no conflicts |
| 426 |
************************************************ |
| 427 |
*** insert some data |
| 428 |
begin; |
| 429 |
insert into t1 values (1, "Master t1 a=1 at X=1", 1, '1'); |
| 430 |
insert into t1 values (2, "Master t1 a=2 at X=1", 1, '12'); |
| 431 |
insert into t1 values (3, "Master t1 a=3 at X=1", 1, '123'); |
| 432 |
insert into t2 values (1, "Master t2 a=1 at c=1", 1, '1'); |
| 433 |
insert into t2 values (2, "Master t2 a=2 at c=1", 1, '12'); |
| 434 |
insert into t2 values (3, "Master t2 a=3 at c=1", 1, '123'); |
| 435 |
commit; |
| 436 |
*** master - update all |
| 437 |
begin; |
| 438 |
update t1 set b="Master t1 X=2", X=X+1; |
| 439 |
update t2 set b="Master t2 c=2", c=c+1; |
| 440 |
commit; |
| 441 |
*** slave - check update all |
| 442 |
select * from t1 order by a, d; |
| 443 |
a b X d |
| 444 |
1 Master t1 X=2 2 1 |
| 445 |
2 Master t1 X=2 2 12 |
| 446 |
3 Master t1 X=2 2 123 |
| 447 |
select * from t2 order by a, d; |
| 448 |
a b c d |
| 449 |
1 Master t2 c=2 2 1 |
| 450 |
2 Master t2 c=2 2 12 |
| 451 |
3 Master t2 c=2 2 123 |
| 452 |
Conflict algorithm counter diff : |
| 453 |
Exception table contents |
| 454 |
SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; |
| 455 |
server_id master_server_id master_epoch count a d |
| 456 |
SELECT * FROM `t1$EX` ORDER BY a, d; |
| 457 |
server_id master_server_id master_epoch count a d |
| 458 |
*** master - update 2 rows in different tables |
| 459 |
update t1 set b="Master t1 a=2 at X=3", X=X+1 where a=2; |
| 460 |
update t2 set b="Master t2 a=3 at c=3", c=c+1 where a=3; |
| 461 |
*** slave - check update 2 rows in different tables |
| 462 |
select * from t1 order by a, d; |
| 463 |
a b X d |
| 464 |
1 Master t1 X=2 2 1 |
| 465 |
2 Master t1 a=2 at X=3 3 12 |
| 466 |
3 Master t1 X=2 2 123 |
| 467 |
select * from t2 order by a, d; |
| 468 |
a b c d |
| 469 |
1 Master t2 c=2 2 1 |
| 470 |
2 Master t2 c=2 2 12 |
| 471 |
3 Master t2 a=3 at c=3 3 123 |
| 472 |
Conflict algorithm counter diff : |
| 473 |
Exception table contents |
| 474 |
SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; |
| 475 |
server_id master_server_id master_epoch count a d |
| 476 |
SELECT * FROM `t1$EX` ORDER BY a, d; |
| 477 |
server_id master_server_id master_epoch count a d |
| 478 |
*** master - delete all |
| 479 |
delete from t1; |
| 480 |
delete from t2; |
| 481 |
*** slave - check delete all |
| 482 |
select * from t1 order by a, d; |
| 483 |
a b X d |
| 484 |
select * from t2 order by a, d; |
| 485 |
a b c d |
| 486 |
Conflict algorithm counter diff : |
| 487 |
Exception table contents |
| 488 |
SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; |
| 489 |
server_id master_server_id master_epoch count a d |
| 490 |
SELECT * FROM `t1$EX` ORDER BY a, d; |
| 491 |
server_id master_server_id master_epoch count a d |
| 492 |
************************ |
| 493 |
* start conflict testing |
| 494 |
************************ |
| 495 |
*** master - insert some data |
| 496 |
begin; |
| 497 |
insert into t1 values (1, "Master t1 a=1 at X=10", 10, '111'); |
| 498 |
insert into t1 values (2, "Master t1 a=2 at X=10", 10, '111222'); |
| 499 |
insert into t1 values (3, "Master t1 a=3 at X=10", 10, '111222333'); |
| 500 |
insert into t1 values (4, "Master t1 a=4 at X=10", 10, '111222333'); |
| 501 |
insert into t2 values (1, "Master t2 a=1 at c=10", 10, '111'); |
| 502 |
insert into t2 values (2, "Master t2 a=2 at c=10", 10, '111222'); |
| 503 |
insert into t2 values (3, "Master t2 a=3 at c=10", 10, '111222333'); |
| 504 |
insert into t2 values (4, "Master t2 a=4 at c=10", 10, '111222333'); |
| 505 |
commit; |
| 506 |
*** slave - check conflict info, no conflicts yet |
| 507 |
Conflict algorithm counter diff : |
| 508 |
Exception table contents |
| 509 |
SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; |
| 510 |
server_id master_server_id master_epoch count a d |
| 511 |
SELECT * FROM `t1$EX` ORDER BY a, d; |
| 512 |
server_id master_server_id master_epoch count a d |
| 513 |
*** slave - check insert some data |
| 514 |
select * from t1 order by a, d; |
| 515 |
a b X d |
| 516 |
1 Master t1 a=1 at X=10 10 111 |
| 517 |
2 Master t1 a=2 at X=10 10 111222 |
| 518 |
3 Master t1 a=3 at X=10 10 111222333 |
| 519 |
4 Master t1 a=4 at X=10 10 111222333 |
| 520 |
select * from t2 order by a, d; |
| 521 |
a b c d |
| 522 |
1 Master t2 a=1 at c=10 10 111 |
| 523 |
2 Master t2 a=2 at c=10 10 111222 |
| 524 |
3 Master t2 a=3 at c=10 10 111222333 |
| 525 |
4 Master t2 a=4 at c=10 10 111222333 |
| 526 |
*** slave - Insert some data to cause conflicts |
| 527 |
begin; |
| 528 |
insert into t1 values (5, "Slave t1 a=5 at X=12", 12, '111'); |
| 529 |
insert into t1 values (6, "Slave t1 a=6 at X=12", 12, '111222'); |
| 530 |
insert into t1 values (7, "Slave t1 a=7 at X=12", 12, '111222333'); |
| 531 |
insert into t1 values (8, "Slave t1 a=8 at X=12", 12, '111222333'); |
| 532 |
insert into t2 values (5, "Slave t2 a=5 at c=12", 12, '111'); |
| 533 |
insert into t2 values (6, "Slave t2 a=6 at c=12", 12, '111222'); |
| 534 |
insert into t2 values (7, "Slave t2 a=7 at c=12", 12, '111222333'); |
| 535 |
insert into t2 values (8, "Slave t2 a=8 at c=12", 12, '111222333'); |
| 536 |
commit; |
| 537 |
*** master - Insert some data to cause conflicts |
| 538 |
begin; |
| 539 |
insert into t1 values (5, "Master t1 a=5 at X=11", 11, '111'); |
| 540 |
insert into t1 values (6, "Master t1 a=6 at X=11", 11, '111222'); |
| 541 |
insert into t2 values (5, "Master t2 a=5 at c=11", 11, '111'); |
| 542 |
insert into t2 values (6, "Master t2 a=6 at c=11", 11, '111222'); |
| 543 |
commit; |
| 544 |
*** slave - check conflict info, there should be some |
| 545 |
Conflict algorithm counter diff : |
| 546 |
ndb_conflict_fn_max_ins |
| 547 |
2 |
| 548 |
Exception table contents |
| 549 |
SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; |
| 550 |
server_id master_server_id master_epoch count a d |
| 551 |
2 1 # 1 # # |
| 552 |
2 1 # 2 # # |
| 553 |
SELECT * FROM `t1$EX` ORDER BY a, d; |
| 554 |
server_id master_server_id master_epoch count a d |
| 555 |
2 1 # # 5 111 |
| 556 |
2 1 # # 6 111222 |
| 557 |
*** slave - check insert some data that causes conflicts |
| 558 |
select * from t1 order by a, d; |
| 559 |
a b X d |
| 560 |
1 Master t1 a=1 at X=10 10 111 |
| 561 |
2 Master t1 a=2 at X=10 10 111222 |
| 562 |
3 Master t1 a=3 at X=10 10 111222333 |
| 563 |
4 Master t1 a=4 at X=10 10 111222333 |
| 564 |
5 Slave t1 a=5 at X=12 12 111 |
| 565 |
6 Slave t1 a=6 at X=12 12 111222 |
| 566 |
7 Slave t1 a=7 at X=12 12 111222333 |
| 567 |
8 Slave t1 a=8 at X=12 12 111222333 |
| 568 |
select * from t2 order by a, d; |
| 569 |
a b c d |
| 570 |
1 Master t2 a=1 at c=10 10 111 |
| 571 |
2 Master t2 a=2 at c=10 10 111222 |
| 572 |
3 Master t2 a=3 at c=10 10 111222333 |
| 573 |
4 Master t2 a=4 at c=10 10 111222333 |
| 574 |
5 Master t2 a=5 at c=11 11 111 |
| 575 |
6 Master t2 a=6 at c=11 11 111222 |
| 576 |
7 Slave t2 a=7 at c=12 12 111222333 |
| 577 |
8 Slave t2 a=8 at c=12 12 111222333 |
| 578 |
*** master - insert some data, use higher timestamp |
| 579 |
begin; |
| 580 |
insert into t1 values (7, "Master t1 a=7 at X=13", 13, '111222333'); |
| 581 |
insert into t1 values (8, "Master t1 a=8 at X=13", 13, '111222333'); |
| 582 |
insert into t2 values (7, "Master t2 a=7 at c=13", 13, '111222333'); |
| 583 |
insert into t2 values (8, "Master t2 a=8 at c=13", 13, '111222333'); |
| 584 |
commit; |
| 585 |
*** slave - check conflict info, change depends on calling test |
| 586 |
Conflict algorithm counter diff : |
| 587 |
ndb_conflict_fn_max_ins |
| 588 |
2 |
| 589 |
Exception table contents |
| 590 |
SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; |
| 591 |
server_id master_server_id master_epoch count a d |
| 592 |
2 1 # 1 # # |
| 593 |
2 1 # 2 # # |
| 594 |
SELECT * FROM `t1$EX` ORDER BY a, d; |
| 595 |
server_id master_server_id master_epoch count a d |
| 596 |
2 1 # # 5 111 |
| 597 |
2 1 # # 6 111222 |
| 598 |
*** slave - check higer timestamp |
| 599 |
select * from t1 order by a, d; |
| 600 |
a b X d |
| 601 |
1 Master t1 a=1 at X=10 10 111 |
| 602 |
2 Master t1 a=2 at X=10 10 111222 |
| 603 |
3 Master t1 a=3 at X=10 10 111222333 |
| 604 |
4 Master t1 a=4 at X=10 10 111222333 |
| 605 |
5 Slave t1 a=5 at X=12 12 111 |
| 606 |
6 Slave t1 a=6 at X=12 12 111222 |
| 607 |
7 Master t1 a=7 at X=13 13 111222333 |
| 608 |
8 Master t1 a=8 at X=13 13 111222333 |
| 609 |
select * from t2 order by a, d; |
| 610 |
a b c d |
| 611 |
1 Master t2 a=1 at c=10 10 111 |
| 612 |
2 Master t2 a=2 at c=10 10 111222 |
| 613 |
3 Master t2 a=3 at c=10 10 111222333 |
| 614 |
4 Master t2 a=4 at c=10 10 111222333 |
| 615 |
5 Master t2 a=5 at c=11 11 111 |
| 616 |
6 Master t2 a=6 at c=11 11 111222 |
| 617 |
7 Master t2 a=7 at c=13 13 111222333 |
| 618 |
8 Master t2 a=8 at c=13 13 111222333 |
| 619 |
drop table t1, t2; |
| 620 |
*** Test 4 *********************************************************** |
| 621 |
* test behaviour with updates logged as writes, but all columns present |
| 622 |
* Should succeed since the NDB$MAX_INS() supports conflict detection |
| 623 |
* and resolution for INSERTS. |
| 624 |
* ******************************************************************** |
| 625 |
update mysql.ndb_replication set binlog_type=3; |
| 626 |
create table t1 (a int, b varchar(32), X int unsigned, d varchar(9), primary key (a, d)) engine ndb; |
| 627 |
create table t2 (a int, b varchar(32), c int unsigned, d varchar(9), primary key (a, d)) engine ndb; |
| 628 |
************************************************ |
| 629 |
* some basic positive testing, i.e. no conflicts |
| 630 |
************************************************ |
| 631 |
*** insert some data |
| 632 |
begin; |
| 633 |
insert into t1 values (1, "Master t1 a=1 at X=1", 1, '1'); |
| 634 |
insert into t1 values (2, "Master t1 a=2 at X=1", 1, '12'); |
| 635 |
insert into t1 values (3, "Master t1 a=3 at X=1", 1, '123'); |
| 636 |
insert into t2 values (1, "Master t2 a=1 at c=1", 1, '1'); |
| 637 |
insert into t2 values (2, "Master t2 a=2 at c=1", 1, '12'); |
| 638 |
insert into t2 values (3, "Master t2 a=3 at c=1", 1, '123'); |
| 639 |
commit; |
| 640 |
*** master - update all |
| 641 |
begin; |
| 642 |
update t1 set b="Master t1 X=2", X=X+1; |
| 643 |
update t2 set b="Master t2 c=2", c=c+1; |
| 644 |
commit; |
| 645 |
*** slave - check update all |
| 646 |
select * from t1 order by a, d; |
| 647 |
a b X d |
| 648 |
1 Master t1 X=2 2 1 |
| 649 |
2 Master t1 X=2 2 12 |
| 650 |
3 Master t1 X=2 2 123 |
| 651 |
select * from t2 order by a, d; |
| 652 |
a b c d |
| 653 |
1 Master t2 c=2 2 1 |
| 654 |
2 Master t2 c=2 2 12 |
| 655 |
3 Master t2 c=2 2 123 |
| 656 |
Conflict algorithm counter diff : |
| 657 |
Exception table contents |
| 658 |
SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; |
| 659 |
server_id master_server_id master_epoch count a d |
| 660 |
SELECT * FROM `t1$EX` ORDER BY a, d; |
| 661 |
server_id master_server_id master_epoch count a d |
| 662 |
*** master - update 2 rows in different tables |
| 663 |
update t1 set b="Master t1 a=2 at X=3", X=X+1 where a=2; |
| 664 |
update t2 set b="Master t2 a=3 at c=3", c=c+1 where a=3; |
| 665 |
*** slave - check update 2 rows in different tables |
| 666 |
select * from t1 order by a, d; |
| 667 |
a b X d |
| 668 |
1 Master t1 X=2 2 1 |
| 669 |
2 Master t1 a=2 at X=3 3 12 |
| 670 |
3 Master t1 X=2 2 123 |
| 671 |
select * from t2 order by a, d; |
| 672 |
a b c d |
| 673 |
1 Master t2 c=2 2 1 |
| 674 |
2 Master t2 c=2 2 12 |
| 675 |
3 Master t2 a=3 at c=3 3 123 |
| 676 |
Conflict algorithm counter diff : |
| 677 |
Exception table contents |
| 678 |
SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; |
| 679 |
server_id master_server_id master_epoch count a d |
| 680 |
SELECT * FROM `t1$EX` ORDER BY a, d; |
| 681 |
server_id master_server_id master_epoch count a d |
| 682 |
*** master - delete all |
| 683 |
delete from t1; |
| 684 |
delete from t2; |
| 685 |
*** slave - check delete all |
| 686 |
select * from t1 order by a, d; |
| 687 |
a b X d |
| 688 |
select * from t2 order by a, d; |
| 689 |
a b c d |
| 690 |
Conflict algorithm counter diff : |
| 691 |
Exception table contents |
| 692 |
SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; |
| 693 |
server_id master_server_id master_epoch count a d |
| 694 |
SELECT * FROM `t1$EX` ORDER BY a, d; |
| 695 |
server_id master_server_id master_epoch count a d |
| 696 |
************************ |
| 697 |
* start conflict testing |
| 698 |
************************ |
| 699 |
*** master - insert some data |
| 700 |
begin; |
| 701 |
insert into t1 values (1, "Master t1 a=1 at X=10", 10, '111'); |
| 702 |
insert into t1 values (2, "Master t1 a=2 at X=10", 10, '111222'); |
| 703 |
insert into t1 values (3, "Master t1 a=3 at X=10", 10, '111222333'); |
| 704 |
insert into t1 values (4, "Master t1 a=4 at X=10", 10, '111222333'); |
| 705 |
insert into t2 values (1, "Master t2 a=1 at c=10", 10, '111'); |
| 706 |
insert into t2 values (2, "Master t2 a=2 at c=10", 10, '111222'); |
| 707 |
insert into t2 values (3, "Master t2 a=3 at c=10", 10, '111222333'); |
| 708 |
insert into t2 values (4, "Master t2 a=4 at c=10", 10, '111222333'); |
| 709 |
commit; |
| 710 |
*** slave - check conflict info, no conflicts yet |
| 711 |
Conflict algorithm counter diff : |
| 712 |
Exception table contents |
| 713 |
SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; |
| 714 |
server_id master_server_id master_epoch count a d |
| 715 |
SELECT * FROM `t1$EX` ORDER BY a, d; |
| 716 |
server_id master_server_id master_epoch count a d |
| 717 |
*** slave - check insert some data |
| 718 |
select * from t1 order by a, d; |
| 719 |
a b X d |
| 720 |
1 Master t1 a=1 at X=10 10 111 |
| 721 |
2 Master t1 a=2 at X=10 10 111222 |
| 722 |
3 Master t1 a=3 at X=10 10 111222333 |
| 723 |
4 Master t1 a=4 at X=10 10 111222333 |
| 724 |
select * from t2 order by a, d; |
| 725 |
a b c d |
| 726 |
1 Master t2 a=1 at c=10 10 111 |
| 727 |
2 Master t2 a=2 at c=10 10 111222 |
| 728 |
3 Master t2 a=3 at c=10 10 111222333 |
| 729 |
4 Master t2 a=4 at c=10 10 111222333 |
| 730 |
*** slave - Insert some data to cause conflicts |
| 731 |
begin; |
| 732 |
insert into t1 values (5, "Slave t1 a=5 at X=12", 12, '111'); |
| 733 |
insert into t1 values (6, "Slave t1 a=6 at X=12", 12, '111222'); |
| 734 |
insert into t1 values (7, "Slave t1 a=7 at X=12", 12, '111222333'); |
| 735 |
insert into t1 values (8, "Slave t1 a=8 at X=12", 12, '111222333'); |
| 736 |
insert into t2 values (5, "Slave t2 a=5 at c=12", 12, '111'); |
| 737 |
insert into t2 values (6, "Slave t2 a=6 at c=12", 12, '111222'); |
| 738 |
insert into t2 values (7, "Slave t2 a=7 at c=12", 12, '111222333'); |
| 739 |
insert into t2 values (8, "Slave t2 a=8 at c=12", 12, '111222333'); |
| 740 |
commit; |
| 741 |
*** master - Insert some data to cause conflicts |
| 742 |
begin; |
| 743 |
insert into t1 values (5, "Master t1 a=5 at X=11", 11, '111'); |
| 744 |
insert into t1 values (6, "Master t1 a=6 at X=11", 11, '111222'); |
| 745 |
insert into t2 values (5, "Master t2 a=5 at c=11", 11, '111'); |
| 746 |
insert into t2 values (6, "Master t2 a=6 at c=11", 11, '111222'); |
| 747 |
commit; |
| 748 |
*** slave - check conflict info, there should be some |
| 749 |
Conflict algorithm counter diff : |
| 750 |
ndb_conflict_fn_max_ins |
| 751 |
2 |
| 752 |
Exception table contents |
| 753 |
SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; |
| 754 |
server_id master_server_id master_epoch count a d |
| 755 |
2 1 # 1 # # |
| 756 |
2 1 # 2 # # |
| 757 |
SELECT * FROM `t1$EX` ORDER BY a, d; |
| 758 |
server_id master_server_id master_epoch count a d |
| 759 |
2 1 # # 5 111 |
| 760 |
2 1 # # 6 111222 |
| 761 |
*** slave - check insert some data that causes conflicts |
| 762 |
select * from t1 order by a, d; |
| 763 |
a b X d |
| 764 |
1 Master t1 a=1 at X=10 10 111 |
| 765 |
2 Master t1 a=2 at X=10 10 111222 |
| 766 |
3 Master t1 a=3 at X=10 10 111222333 |
| 767 |
4 Master t1 a=4 at X=10 10 111222333 |
| 768 |
5 Slave t1 a=5 at X=12 12 111 |
| 769 |
6 Slave t1 a=6 at X=12 12 111222 |
| 770 |
7 Slave t1 a=7 at X=12 12 111222333 |
| 771 |
8 Slave t1 a=8 at X=12 12 111222333 |
| 772 |
select * from t2 order by a, d; |
| 773 |
a b c d |
| 774 |
1 Master t2 a=1 at c=10 10 111 |
| 775 |
2 Master t2 a=2 at c=10 10 111222 |
| 776 |
3 Master t2 a=3 at c=10 10 111222333 |
| 777 |
4 Master t2 a=4 at c=10 10 111222333 |
| 778 |
5 Master t2 a=5 at c=11 11 111 |
| 779 |
6 Master t2 a=6 at c=11 11 111222 |
| 780 |
7 Slave t2 a=7 at c=12 12 111222333 |
| 781 |
8 Slave t2 a=8 at c=12 12 111222333 |
| 782 |
*** master - insert some data, use higher timestamp |
| 783 |
begin; |
| 784 |
insert into t1 values (7, "Master t1 a=7 at X=13", 13, '111222333'); |
| 785 |
insert into t1 values (8, "Master t1 a=8 at X=13", 13, '111222333'); |
| 786 |
insert into t2 values (7, "Master t2 a=7 at c=13", 13, '111222333'); |
| 787 |
insert into t2 values (8, "Master t2 a=8 at c=13", 13, '111222333'); |
| 788 |
commit; |
| 789 |
*** slave - check conflict info, change depends on calling test |
| 790 |
Conflict algorithm counter diff : |
| 791 |
ndb_conflict_fn_max_ins |
| 792 |
2 |
| 793 |
Exception table contents |
| 794 |
SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; |
| 795 |
server_id master_server_id master_epoch count a d |
| 796 |
2 1 # 1 # # |
| 797 |
2 1 # 2 # # |
| 798 |
SELECT * FROM `t1$EX` ORDER BY a, d; |
| 799 |
server_id master_server_id master_epoch count a d |
| 800 |
2 1 # # 5 111 |
| 801 |
2 1 # # 6 111222 |
| 802 |
*** slave - check higer timestamp |
| 803 |
select * from t1 order by a, d; |
| 804 |
a b X d |
| 805 |
1 Master t1 a=1 at X=10 10 111 |
| 806 |
2 Master t1 a=2 at X=10 10 111222 |
| 807 |
3 Master t1 a=3 at X=10 10 111222333 |
| 808 |
4 Master t1 a=4 at X=10 10 111222333 |
| 809 |
5 Slave t1 a=5 at X=12 12 111 |
| 810 |
6 Slave t1 a=6 at X=12 12 111222 |
| 811 |
7 Master t1 a=7 at X=13 13 111222333 |
| 812 |
8 Master t1 a=8 at X=13 13 111222333 |
| 813 |
select * from t2 order by a, d; |
| 814 |
a b c d |
| 815 |
1 Master t2 a=1 at c=10 10 111 |
| 816 |
2 Master t2 a=2 at c=10 10 111222 |
| 817 |
3 Master t2 a=3 at c=10 10 111222333 |
| 818 |
4 Master t2 a=4 at c=10 10 111222333 |
| 819 |
5 Master t2 a=5 at c=11 11 111 |
| 820 |
6 Master t2 a=6 at c=11 11 111222 |
| 821 |
7 Master t2 a=7 at c=13 13 111222333 |
| 822 |
8 Master t2 a=8 at c=13 13 111222333 |
| 823 |
drop table t1, t2; |
| 824 |
set @save_lum = @@global.ndb_log_update_minimal; |
| 825 |
*** Test 5 **************************************************************************** |
| 826 |
* Test with ndb_log_update_minimal binlogging option and NBT_UPDATED_FULLY_MINIMAL type |
| 827 |
*************************************************************************************** |
| 828 |
set global ndb_log_update_minimal=1; |
| 829 |
update mysql.ndb_replication set binlog_type=9; |
| 830 |
create table t1 (a int, b varchar(32), X int unsigned, d varchar(9), primary key (a, d)) engine ndb; |
| 831 |
create table t2 (a int, b varchar(32), c int unsigned, d varchar(9), primary key (a, d)) engine ndb; |
| 832 |
************************************************ |
| 833 |
* some basic positive testing, i.e. no conflicts |
| 834 |
************************************************ |
| 835 |
*** insert some data |
| 836 |
begin; |
| 837 |
insert into t1 values (1, "Master t1 a=1 at X=1", 1, '1'); |
| 838 |
insert into t1 values (2, "Master t1 a=2 at X=1", 1, '12'); |
| 839 |
insert into t1 values (3, "Master t1 a=3 at X=1", 1, '123'); |
| 840 |
insert into t2 values (1, "Master t2 a=1 at c=1", 1, '1'); |
| 841 |
insert into t2 values (2, "Master t2 a=2 at c=1", 1, '12'); |
| 842 |
insert into t2 values (3, "Master t2 a=3 at c=1", 1, '123'); |
| 843 |
commit; |
| 844 |
*** master - update all |
| 845 |
begin; |
| 846 |
update t1 set b="Master t1 X=2", X=X+1; |
| 847 |
update t2 set b="Master t2 c=2", c=c+1; |
| 848 |
commit; |
| 849 |
*** slave - check update all |
| 850 |
select * from t1 order by a, d; |
| 851 |
a b X d |
| 852 |
1 Master t1 X=2 2 1 |
| 853 |
2 Master t1 X=2 2 12 |
| 854 |
3 Master t1 X=2 2 123 |
| 855 |
select * from t2 order by a, d; |
| 856 |
a b c d |
| 857 |
1 Master t2 c=2 2 1 |
| 858 |
2 Master t2 c=2 2 12 |
| 859 |
3 Master t2 c=2 2 123 |
| 860 |
Conflict algorithm counter diff : |
| 861 |
Exception table contents |
| 862 |
SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; |
| 863 |
server_id master_server_id master_epoch count a d |
| 864 |
SELECT * FROM `t1$EX` ORDER BY a, d; |
| 865 |
server_id master_server_id master_epoch count a d |
| 866 |
*** master - update 2 rows in different tables |
| 867 |
update t1 set b="Master t1 a=2 at X=3", X=X+1 where a=2; |
| 868 |
update t2 set b="Master t2 a=3 at c=3", c=c+1 where a=3; |
| 869 |
*** slave - check update 2 rows in different tables |
| 870 |
select * from t1 order by a, d; |
| 871 |
a b X d |
| 872 |
1 Master t1 X=2 2 1 |
| 873 |
2 Master t1 a=2 at X=3 3 12 |
| 874 |
3 Master t1 X=2 2 123 |
| 875 |
select * from t2 order by a, d; |
| 876 |
a b c d |
| 877 |
1 Master t2 c=2 2 1 |
| 878 |
2 Master t2 c=2 2 12 |
| 879 |
3 Master t2 a=3 at c=3 3 123 |
| 880 |
Conflict algorithm counter diff : |
| 881 |
Exception table contents |
| 882 |
SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; |
| 883 |
server_id master_server_id master_epoch count a d |
| 884 |
SELECT * FROM `t1$EX` ORDER BY a, d; |
| 885 |
server_id master_server_id master_epoch count a d |
| 886 |
*** master - delete all |
| 887 |
delete from t1; |
| 888 |
delete from t2; |
| 889 |
*** slave - check delete all |
| 890 |
select * from t1 order by a, d; |
| 891 |
a b X d |
| 892 |
select * from t2 order by a, d; |
| 893 |
a b c d |
| 894 |
Conflict algorithm counter diff : |
| 895 |
Exception table contents |
| 896 |
SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; |
| 897 |
server_id master_server_id master_epoch count a d |
| 898 |
SELECT * FROM `t1$EX` ORDER BY a, d; |
| 899 |
server_id master_server_id master_epoch count a d |
| 900 |
************************ |
| 901 |
* start conflict testing |
| 902 |
************************ |
| 903 |
*** master - insert some data |
| 904 |
begin; |
| 905 |
insert into t1 values (1, "Master t1 a=1 at X=10", 10, '111'); |
| 906 |
insert into t1 values (2, "Master t1 a=2 at X=10", 10, '111222'); |
| 907 |
insert into t1 values (3, "Master t1 a=3 at X=10", 10, '111222333'); |
| 908 |
insert into t1 values (4, "Master t1 a=4 at X=10", 10, '111222333'); |
| 909 |
insert into t2 values (1, "Master t2 a=1 at c=10", 10, '111'); |
| 910 |
insert into t2 values (2, "Master t2 a=2 at c=10", 10, '111222'); |
| 911 |
insert into t2 values (3, "Master t2 a=3 at c=10", 10, '111222333'); |
| 912 |
insert into t2 values (4, "Master t2 a=4 at c=10", 10, '111222333'); |
| 913 |
commit; |
| 914 |
*** slave - check conflict info, no conflicts yet |
| 915 |
Conflict algorithm counter diff : |
| 916 |
Exception table contents |
| 917 |
SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; |
| 918 |
server_id master_server_id master_epoch count a d |
| 919 |
SELECT * FROM `t1$EX` ORDER BY a, d; |
| 920 |
server_id master_server_id master_epoch count a d |
| 921 |
*** slave - check insert some data |
| 922 |
select * from t1 order by a, d; |
| 923 |
a b X d |
| 924 |
1 Master t1 a=1 at X=10 10 111 |
| 925 |
2 Master t1 a=2 at X=10 10 111222 |
| 926 |
3 Master t1 a=3 at X=10 10 111222333 |
| 927 |
4 Master t1 a=4 at X=10 10 111222333 |
| 928 |
select * from t2 order by a, d; |
| 929 |
a b c d |
| 930 |
1 Master t2 a=1 at c=10 10 111 |
| 931 |
2 Master t2 a=2 at c=10 10 111222 |
| 932 |
3 Master t2 a=3 at c=10 10 111222333 |
| 933 |
4 Master t2 a=4 at c=10 10 111222333 |
| 934 |
*** slave - Insert some data to cause conflicts |
| 935 |
begin; |
| 936 |
insert into t1 values (5, "Slave t1 a=5 at X=12", 12, '111'); |
| 937 |
insert into t1 values (6, "Slave t1 a=6 at X=12", 12, '111222'); |
| 938 |
insert into t1 values (7, "Slave t1 a=7 at X=12", 12, '111222333'); |
| 939 |
insert into t1 values (8, "Slave t1 a=8 at X=12", 12, '111222333'); |
| 940 |
insert into t2 values (5, "Slave t2 a=5 at c=12", 12, '111'); |
| 941 |
insert into t2 values (6, "Slave t2 a=6 at c=12", 12, '111222'); |
| 942 |
insert into t2 values (7, "Slave t2 a=7 at c=12", 12, '111222333'); |
| 943 |
insert into t2 values (8, "Slave t2 a=8 at c=12", 12, '111222333'); |
| 944 |
commit; |
| 945 |
*** master - Insert some data to cause conflicts |
| 946 |
begin; |
| 947 |
insert into t1 values (5, "Master t1 a=5 at X=11", 11, '111'); |
| 948 |
insert into t1 values (6, "Master t1 a=6 at X=11", 11, '111222'); |
| 949 |
insert into t2 values (5, "Master t2 a=5 at c=11", 11, '111'); |
| 950 |
insert into t2 values (6, "Master t2 a=6 at c=11", 11, '111222'); |
| 951 |
commit; |
| 952 |
*** slave - check conflict info, there should be some |
| 953 |
Conflict algorithm counter diff : |
| 954 |
ndb_conflict_fn_max_ins |
| 955 |
2 |
| 956 |
Exception table contents |
| 957 |
SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; |
| 958 |
server_id master_server_id master_epoch count a d |
| 959 |
2 1 # 1 # # |
| 960 |
2 1 # 2 # # |
| 961 |
SELECT * FROM `t1$EX` ORDER BY a, d; |
| 962 |
server_id master_server_id master_epoch count a d |
| 963 |
2 1 # # 5 111 |
| 964 |
2 1 # # 6 111222 |
| 965 |
*** slave - check insert some data that causes conflicts |
| 966 |
select * from t1 order by a, d; |
| 967 |
a b X d |
| 968 |
1 Master t1 a=1 at X=10 10 111 |
| 969 |
2 Master t1 a=2 at X=10 10 111222 |
| 970 |
3 Master t1 a=3 at X=10 10 111222333 |
| 971 |
4 Master t1 a=4 at X=10 10 111222333 |
| 972 |
5 Slave t1 a=5 at X=12 12 111 |
| 973 |
6 Slave t1 a=6 at X=12 12 111222 |
| 974 |
7 Slave t1 a=7 at X=12 12 111222333 |
| 975 |
8 Slave t1 a=8 at X=12 12 111222333 |
| 976 |
select * from t2 order by a, d; |
| 977 |
a b c d |
| 978 |
1 Master t2 a=1 at c=10 10 111 |
| 979 |
2 Master t2 a=2 at c=10 10 111222 |
| 980 |
3 Master t2 a=3 at c=10 10 111222333 |
| 981 |
4 Master t2 a=4 at c=10 10 111222333 |
| 982 |
5 Master t2 a=5 at c=11 11 111 |
| 983 |
6 Master t2 a=6 at c=11 11 111222 |
| 984 |
7 Slave t2 a=7 at c=12 12 111222333 |
| 985 |
8 Slave t2 a=8 at c=12 12 111222333 |
| 986 |
*** master - insert some data, use higher timestamp |
| 987 |
begin; |
| 988 |
insert into t1 values (7, "Master t1 a=7 at X=13", 13, '111222333'); |
| 989 |
insert into t1 values (8, "Master t1 a=8 at X=13", 13, '111222333'); |
| 990 |
insert into t2 values (7, "Master t2 a=7 at c=13", 13, '111222333'); |
| 991 |
insert into t2 values (8, "Master t2 a=8 at c=13", 13, '111222333'); |
| 992 |
commit; |
| 993 |
*** slave - check conflict info, change depends on calling test |
| 994 |
Conflict algorithm counter diff : |
| 995 |
ndb_conflict_fn_max_ins |
| 996 |
2 |
| 997 |
Exception table contents |
| 998 |
SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; |
| 999 |
server_id master_server_id master_epoch count a d |
| 1000 |
2 1 # 1 # # |
| 1001 |
2 1 # 2 # # |
| 1002 |
SELECT * FROM `t1$EX` ORDER BY a, d; |
| 1003 |
server_id master_server_id master_epoch count a d |
| 1004 |
2 1 # # 5 111 |
| 1005 |
2 1 # # 6 111222 |
| 1006 |
*** slave - check higer timestamp |
| 1007 |
select * from t1 order by a, d; |
| 1008 |
a b X d |
| 1009 |
1 Master t1 a=1 at X=10 10 111 |
| 1010 |
2 Master t1 a=2 at X=10 10 111222 |
| 1011 |
3 Master t1 a=3 at X=10 10 111222333 |
| 1012 |
4 Master t1 a=4 at X=10 10 111222333 |
| 1013 |
5 Slave t1 a=5 at X=12 12 111 |
| 1014 |
6 Slave t1 a=6 at X=12 12 111222 |
| 1015 |
7 Master t1 a=7 at X=13 13 111222333 |
| 1016 |
8 Master t1 a=8 at X=13 13 111222333 |
| 1017 |
select * from t2 order by a, d; |
| 1018 |
a b c d |
| 1019 |
1 Master t2 a=1 at c=10 10 111 |
| 1020 |
2 Master t2 a=2 at c=10 10 111222 |
| 1021 |
3 Master t2 a=3 at c=10 10 111222333 |
| 1022 |
4 Master t2 a=4 at c=10 10 111222333 |
| 1023 |
5 Master t2 a=5 at c=11 11 111 |
| 1024 |
6 Master t2 a=6 at c=11 11 111222 |
| 1025 |
7 Master t2 a=7 at c=13 13 111222333 |
| 1026 |
8 Master t2 a=8 at c=13 13 111222333 |
| 1027 |
drop table t1, t2; |
| 1028 |
set global ndb_log_update_minimal = @save_lum; |
| 1029 |
*** test cleanup |
| 1030 |
set global ndb_log_update_as_write= @save_luaw; |
| 1031 |
set global ndb_log_updated_only = @save_luo; |
| 1032 |
include/rpl_end.inc |