Debian Codegraph @

mysql-test/suite/rpl/t/rpl_row_img_sanity.test

raw
1 #
2 # Description
3 # ===========
4 #
5 # This test case checks whether binlog files contain Before and After
6 # image values as expected. Configuration is done using the
7 # --binlog-row-image variable.
8 #
9 # How it works
10 # ============
11 #
12 # The test case is implemented such that master and slave basically
13 # hold the same table but sometimes differ in indexes, number of
14 # columns and data types constraints (autoinc or NOT NULL). Then some
15 # statements are executed and the output of mysqlbinlog is parsed for
16 # the given event to check if the columns in the before and after
17 # image match expectations according to the binlog-row-image value on
18 # master and on slave.
19 #
20 # See also WL#5096.
21 #
22 --source include/not_group_replication_plugin.inc
23 -- source include/have_binlog_format_row.inc
24 -- source include/no_valgrind_without_big.inc
25 -- source include/master-slave.inc
26
27 -- connection slave
28 call mtr.add_suppression("Replica: Can\'t find record in \'t\' Error_code: MY-001032");
29 call mtr.add_suppression("Replica SQL for channel '': .*Could not execute Update_rows event on table test.t; Can.t find record in .t.* Error_code: MY-001032");
30 call mtr.add_suppression("The replica coordinator and worker threads are stopped, possibly leaving data in inconsistent state");
31
32 -- connection master
33
34 ## assertion: check that default value for binlog-row-image == 'FULL'
35 SHOW VARIABLES LIKE 'binlog_row_image';
36
37 ## save original
38 -- connection master
39 SET @old_binlog_row_image= @@binlog_row_image;
40 -- connection slave
41 SET @old_binlog_row_image= @@binlog_row_image;
42 -- connection master
43
44 -- echo #####################################################
45 -- echo # basic assertion that binlog_row_image='FULL' is the
46 -- echo # default
47 -- echo #####################################################
48
49 -- connection master
50 -- let $row_img_set=master:FULL:N,slave:FULL:Y
51 -- source include/rpl_row_img_set.inc
52
53 CREATE TABLE t (c1 int, c2 int, c3 blob, primary key(c1));
54
55 -- let $row_img_query= INSERT INTO t(c1,c3) VALUES (1, 'a')
56 -- let $row_img_expected_master= | 1:1 2:NULL 3:'a'
57 -- let $row_img_expected_slave = | 1:1 2:NULL 3:'a'
58 -- source include/rpl_row_img_parts_master_slave.inc
59
60 -- let $row_img_query= UPDATE t SET c1=2 WHERE c1=1;
61 -- let $row_img_expected_master= 1:1 2:NULL 3:'a' | 1:2 2:NULL 3:'a'
62 -- let $row_img_expected_slave = 1:1 2:NULL 3:'a' | 1:2 2:NULL 3:'a'
63 -- source include/rpl_row_img_parts_master_slave.inc
64
65 -- let $row_img_query= DELETE FROM t;
66 -- let $row_img_expected_master= 1:2 2:NULL 3:'a' |
67 -- let $row_img_expected_slave = 1:2 2:NULL 3:'a' |
68 -- source include/rpl_row_img_parts_master_slave.inc
69
70 DROP TABLE t;
71 --source include/sync_slave_sql_with_master.inc
72
73 #
74 # Assertions: combines img_types with different index types.
75 # The checks that rows are logged with expected
76 # image contents, depending on the img_type.
77 #
78
79 -- connection master
80 SET @img_types= 'MINIMAL NOBLOB FULL';
81 while (`SELECT HEX(@img_types) != HEX('')`)
82 {
83 -- disable_query_log
84 let $img_type= `SELECT SUBSTRING_INDEX(@img_types, ' ', 1)`;
85 -- let $row_img_set=master:$img_type:N,slave:$img_type:Y
86 -- source include/rpl_row_img_set.inc
87
88 SET @index_types= 'UK-NOT-NULL PK UK K NONE';
89 while (`SELECT HEX(@index_types) != HEX('')`)
90 {
91 -- disable_query_log
92 let $index_type= `SELECT SUBSTRING_INDEX(@index_types, ' ', 1)`;
93 -- enable_query_log
94
95 -- echo ITERATIONS: row_img: $img_type, indexes: $index_type
96
97 -- source include/rpl_reset.inc
98 -- connection master
99
100 # create the table
101 -- echo CREATING TABLE IN $CURRENT_CONNECTION WITH INDEX '$index_type'
102
103 if (`SELECT HEX('$index_type') = HEX('NONE')`)
104 {
105 CREATE TABLE t (c1 int, c2 int, c3 blob);
106 }
107
108 if (`SELECT HEX('$index_type') = HEX('K')`)
109 {
110 CREATE TABLE t (c1 int, c2 int, c3 blob, key(c1));
111 }
112
113 if (`SELECT HEX('$index_type') = HEX('UK')`)
114 {
115 CREATE TABLE t (c1 int, c2 int, c3 blob, unique key(c1));
116 }
117
118 if (`SELECT HEX('$index_type') = HEX('PK')`)
119 {
120 CREATE TABLE t (c1 int, c2 int, c3 blob, primary key(c1));
121 }
122
123 if (`SELECT HEX('$index_type') = HEX('UK-NOT-NULL')`)
124 {
125 CREATE TABLE t (c1 int NOT NULL, c2 int, c3 blob, unique key(c1));
126 }
127
128 --source include/sync_slave_sql_with_master.inc
129 -- connection master
130
131 # Issue some statements
132
133 -- let $row_img_query= INSERT INTO t VALUES (1,2,"a")
134 -- let $row_img_expected_master= | 1:1 2:2 3:'a'
135 -- let $row_img_expected_slave = | 1:1 2:2 3:'a'
136 -- source include/rpl_row_img_parts_master_slave.inc
137
138 -- let $row_img_query= INSERT INTO t(c1,c3) VALUES (10,"a")
139 if (`SELECT @@binlog_row_image = "FULL" or @@binlog_row_image = "NOBLOB"`)
140 {
141 -- let $row_img_expected_master= | 1:10 2:NULL 3:'a'
142 }
143 if (`SELECT @@binlog_row_image = "MINIMAL"`)
144 {
145 -- let $row_img_expected_master= | 1:10 3:'a'
146 }
147 -- let $row_img_expected_slave= $row_img_expected_master
148 -- source include/rpl_row_img_parts_master_slave.inc
149
150 -- let $row_img_query= INSERT INTO t(c1) VALUES (1000)
151 if (`SELECT @@binlog_row_image = "FULL"`)
152 {
153 -- let $row_img_expected_master= | 1:1000 2:NULL 3:NULL
154 }
155 if (`SELECT @@binlog_row_image = "NOBLOB"`)
156 {
157 -- let $row_img_expected_master= | 1:1000 2:NULL
158 }
159 if (`SELECT @@binlog_row_image = "MINIMAL"`)
160 {
161 -- let $row_img_expected_master= | 1:1000
162 }
163 -- let $row_img_expected_slave= $row_img_expected_master
164 -- source include/rpl_row_img_parts_master_slave.inc
165
166 -- let $row_img_query= UPDATE t SET c1=2 WHERE c1=1
167 if (`SELECT @@binlog_row_image = "FULL" OR (SELECT '$index_type' IN ('NONE', 'K', 'UK'))`)
168 {
169 -- let $bi= 1:1 2:2 3:'a'
170 }
171
172 # noblob with pk + uk not nullable
173 if (`SELECT @@binlog_row_image = "NOBLOB" AND (SELECT '$index_type' IN ('PK', 'UK-NOT-NULL'))`)
174 {
175 -- let $bi= 1:1 2:2
176 }
177
178 if (`SELECT @@binlog_row_image = "MINIMAL" AND (SELECT '$index_type' IN ('PK', 'UK-NOT-NULL'))`)
179 {
180 -- let $bi= 1:1
181 }
182
183 if (`SELECT @@binlog_row_image = "FULL"`)
184 {
185 -- let $ai= 1:2 2:2 3:'a'
186 }
187
188 if (`SELECT @@binlog_row_image = "NOBLOB"`)
189 {
190 -- let $ai= 1:2 2:2
191 }
192
193 if (`SELECT @@binlog_row_image = "MINIMAL"`)
194 {
195 -- let $ai= 1:2
196 }
197 -- let $row_img_expected_master= $bi | $ai
198 -- let $row_img_expected_slave = $bi | $ai
199 -- let $ai=
200 -- let $bi=
201 -- source include/rpl_row_img_parts_master_slave.inc
202
203 -- let $row_img_query= DELETE FROM t WHERE c2=2
204 # no key, simple key or unique key nullable
205 if (`SELECT @@binlog_row_image = "FULL" OR (SELECT '$index_type' IN ('NONE', 'K', 'UK'))`)
206 {
207 -- let $row_img_expected_master= 1:2 2:2 3:'a' |
208 }
209
210 # noblob with pk + uk not nullable
211 if (`SELECT @@binlog_row_image = "NOBLOB" AND (SELECT '$index_type' IN ('PK', 'UK-NOT-NULL'))`)
212 {
213 -- let $row_img_expected_master= 1:2 2:2 |
214 }
215
216 if (`SELECT @@binlog_row_image = "MINIMAL" AND (SELECT '$index_type' IN ('PK', 'UK-NOT-NULL'))`)
217 {
218 -- let $row_img_expected_master= 1:2 |
219 }
220 -- let $row_img_expected_slave= $row_img_expected_master
221 -- source include/rpl_row_img_parts_master_slave.inc
222
223 DROP TABLE t;
224
225 --source include/sync_slave_sql_with_master.inc
226
227 -- disable_query_log
228 -- connection master
229 -- eval SET @index_types= LTRIM(SUBSTRING(@index_types, LENGTH('$index_type') + 1))
230 -- enable_query_log
231
232 }
233
234 -- disable_query_log
235 -- connection master
236 -- eval SET @img_types= LTRIM(SUBSTRING(@img_types, LENGTH('$img_type') + 1))
237 -- enable_query_log
238 }
239
240 ### Some more scenarios
241 ### These relate to different constraints on PKE and its impact
242 ### on logged images. Further description is inline.
243
244 -- connection master
245 SET @img_types= 'MINIMAL NOBLOB FULL';
246 while (`SELECT HEX(@img_types) != HEX('')`)
247 {
248 -- disable_query_log
249 let $img_type= `SELECT SUBSTRING_INDEX(@img_types, ' ', 1)`;
250 -- let $row_img_set=master:$img_type:N,slave:$img_type:Y
251 -- source include/rpl_row_img_set.inc
252
253 -- echo ITERATIONS: row_img: $img_type
254
255 -- source include/rpl_reset.inc
256 -- connection master
257
258 # ASSERTIONS:
259 # UPDATE (MINIMAL,NOBLOB,FULL)
260 # - on slave, columns that are not in master's BI but are in slave's
261 # PKE are in slave's BI
262
263 -- connection master
264 SET SQL_LOG_BIN=0;
265 CREATE TABLE t (a INT);
266 SET SQL_LOG_BIN=1;
267
268 -- connection slave
269 SET SQL_LOG_BIN=0;
270 CREATE TABLE t (a INT, b INT DEFAULT 1000);
271 SET SQL_LOG_BIN=1;
272
273 -- connection master
274 INSERT INTO t VALUES (1);
275 --source include/sync_slave_sql_with_master.inc
276 -- connection master
277
278 -- let $row_img_query= UPDATE t SET a=2 WHERE a=1;
279 # 1. columns that are set to default value are in AI
280 -- let $row_img_expected_master= 1:1 | 1:2
281 if (`SELECT @@binlog_row_image = "NOBLOB" OR (SELECT @@binlog_row_image = "FULL")`)
282 {
283 -- let $row_img_expected_slave = 1:1 2:1000 | 1:2 2:1000
284 }
285 if (`SELECT @@binlog_row_image = "MINIMAL"`)
286 {
287 -- let $row_img_expected_slave = 1:1 2:1000 | 1:2
288 }
289 -- source include/rpl_row_img_parts_master_slave.inc
290
291 -- connection master
292 DROP TABLE IF EXISTS t;
293 --source include/sync_slave_sql_with_master.inc
294
295 -- source include/rpl_reset.inc
296
297 -- connection master
298 if (`SELECT @@binlog_row_image = "MINIMAL"`)
299 {
300 -- echo ####### MINIMAL PARTICULAR SCENARIO ######
301
302 # ASSERTIONS:
303 # INSERT/MINIMAL
304 # 1. columns that are set to default value are in AI
305 #
306 # UPDATE/MINIMAL:
307 # 2. columns that are set to the same value are in AI
308 # 3. columns that are not in WHERE clause but in PKE are in BI
309 # 4. on slave, columns that are not in master's BI but are in slave's
310 # PKE are in slave's BI
311 #
312 # DELETE/MINIMAL:
313 # 5. on slave, columns that are in master's BI but not in slave's PKE are
314 # not in slave's BI
315 # 6. columns that are NOT NULL UK but not in PK are not in BI
316 #
317 -- connection master
318 SET SQL_LOG_BIN=0;
319 CREATE TABLE t (c1 INT PRIMARY KEY, c2 INT DEFAULT 100, c3 INT);
320 SET SQL_LOG_BIN=1;
321
322 -- connection slave
323 SET SQL_LOG_BIN=0;
324 CREATE TABLE t (c1 INT NOT NULL UNIQUE KEY, c2 INT DEFAULT 100, c3 INT, c4 INT, PRIMARY KEY(c2,c3));
325
326 -- let $row_img_query= INSERT INTO t VALUES (1, 100, 1);
327 # 1. columns that are set to default value are in AI
328 -- let $row_img_expected_master= | 1:1 2:100 3:1
329 -- let $row_img_expected_slave = | 1:1 2:100 3:1
330 -- source include/rpl_row_img_parts_master_slave.inc
331
332 -- let $row_img_query= UPDATE t SET c2=100, c3=1000 WHERE c2=100;
333 # 2. columns that are set to the same value are in AI
334 # 3. columns that are not in WHERE clause but in PKE are in BI
335 # 4. on slave, columns that are not in master's BI but are in slave's PKE are in slave's BI
336 -- let $row_img_expected_master= 1:1 | 2:100 3:1000
337 -- let $row_img_expected_slave = 2:100 3:1 | 2:100 3:1000
338 -- source include/rpl_row_img_parts_master_slave.inc
339
340 -- let $row_img_query= DELETE FROM t WHERE c1=1
341 # asserts:
342 # 5. on slave, columns that are not in master's BI but are in slave's PKE are in slave's BI
343 # 6. columns that are NOT NULL UK but not in PK are not in BI
344 -- let $row_img_expected_master= 1:1 |
345 -- let $row_img_expected_slave= 2:100 3:1000 |
346 -- source include/rpl_row_img_parts_master_slave.inc
347
348 -- connection master
349 DROP TABLE t;
350 --source include/sync_slave_sql_with_master.inc
351
352 -- echo ####### MINIMAL OTHER PARTICULAR SCENARIO ######
353
354 # ASSERTIONS:
355 # UPDATE/MINIMAL:
356 # 1. all columns needed to identify a row are in BI; as the
357 # index is not unique, this means all columns of the table
358 # 2. columns that are in SET changed are in AI
359 # 3. columns that are not in SET are not in AI
360 # 4. table on slave has the same definition as table on
361 # master, so points 1-2-3 apply to images in the slave's
362 # binlog too.
363 #
364 -- connection master
365 CREATE TABLE t (c1 INT NOT NULL, c2 INT NOT NULL, KEY (c1));
366 INSERT INTO t VALUES (30,40);
367
368 -- connection slave
369
370 -- let $row_img_query= UPDATE t SET c2=100 ORDER BY c1;
371 # 1. all columns needed to identify a row are in BI
372 # 2. columns that are in SET changed are in AI
373 # 3. columns that are not in SET are not in AI
374 # 4. slave is as master as tables' definitions are identical
375 -- let $row_img_expected_master= 1:30 2:40 | 2:100
376 -- let $row_img_expected_slave = 1:30 2:40 | 2:100
377 -- source include/rpl_row_img_parts_master_slave.inc
378
379 -- connection master
380 DROP TABLE t;
381 --source include/sync_slave_sql_with_master.inc
382 }
383
384 if (`SELECT @@binlog_row_image = "NOBLOB"`)
385 {
386 -- echo ####### NOBLOB PARTICULAR SCENARIO ######
387
388 # ASSERTIONS:
389 # INSERT/NOBLOB:
390 # 1 non-blob columns that are set to default value are in AI
391 #
392 # UPDATE/NOBLOB:
393 # 2 non-blob columns that are set to default value are in AI
394 # 3 blob columns that are set to default value are in AI
395 # 4 blob columns that are in WHERE clause but not in PKE are not in BI
396 # 5 blob columns that are in NON-NULL UK but not in PK are not in BI
397 # 6 on slave, blob columns that are in master's BI clause but not in
398 # slave's PKE are not in BI
399 #
400 # DELETE
401 # 7 non-blob columns that are not in WHERE clause but in PKE are in BI
402 # 8 blob columns that are used in WHERE clause but not in PKE are not
403 # in BI
404 # 9 blob columns that are NOT NULL UK but not in PK are not in BI
405 # 10 on slave, blob columns that are in master's BI but not in slave's PKE
406 # are not in slave's BI
407
408 -- connection master
409 SET SQL_LOG_BIN=0;
410 CREATE TABLE t (c1 INT, c2 BLOB, c3 INT DEFAULT 1000, c4 BLOB NOT NULL, c5 INT, UNIQUE KEY(c4(512)), PRIMARY KEy(c1, c2(512)));
411 SET SQL_LOG_BIN=1;
412
413 -- connection slave
414 SET SQL_LOG_BIN=0;
415 CREATE TABLE t (c1 INT PRIMARY KEY, c2 BLOB, c3 INT DEFAULT 1000, c4 BLOB, c5 INT);
416 SET SQL_LOG_BIN=1;
417
418 -- connection master
419 -- let $row_img_query= INSERT INTO t VALUES (1,'aaa', 1000, 'bbb', 1)
420 # 1. non-blob columns that are set to default value are in AI
421 -- let $row_img_expected_master= | 1:1 2:'aaa' 3:1000 4:'bbb' 5:1
422 -- let $row_img_expected_slave = | 1:1 2:'aaa' 3:1000 4:'bbb' 5:1
423 -- source include/rpl_row_img_parts_master_slave.inc
424
425 -- connection master
426 -- let $row_img_query= UPDATE t SET c2='aaa', c3=1000, c5=10000 WHERE c1=1 AND c4='bbb'
427 # asserts that:
428 # 2. non-blob columns that are set to default value are in AI
429 # 3. blob columns that are set to default value are in AI
430 # 4. blob columns that are in WHERE clause but not in PKE are not in BI
431 # 5. blob columns that are in NON-NULL UK but not in PK are not in BI
432 # 6. on slave, blob columns that are in master's BI clause but not in
433 # slave's PKE are not in BI
434 -- let $row_img_expected_master= 1:1 2:'aaa' 3:1000 5:1 | 1:1 2:'aaa' 3:1000 5:10000
435 -- let $row_img_expected_slave = 1:1 3:1000 5:1 | 1:1 2:'aaa' 3:1000 5:10000
436 -- source include/rpl_row_img_parts_master_slave.inc
437
438 -- connection master
439 -- let $row_img_query= DELETE FROM t WHERE c1=1 AND c4='bbb'
440 # asserts that:
441 # 7. non-blob columns that are not in WHERE clause but in PKE are in BI
442 # 8. blob columns that are used in WHERE clause but not in PKE are not n BI
443 # 9. blob columns that are NOT NULL UK but not in PK are not in BI
444 # 10. on slave, blob columns that are in master's BI but not in slave's PKE are not in slave's BI
445 -- let $row_img_expected_master= 1:1 2:'aaa' 3:1000 5:10000 |
446 -- let $row_img_expected_slave = 1:1 3:1000 5:10000 |
447 -- source include/rpl_row_img_parts_master_slave.inc
448
449 -- connection master
450 DROP TABLE t;
451 --source include/sync_slave_sql_with_master.inc
452
453 }
454
455 -- disable_query_log
456 -- connection master
457 -- eval SET @img_types= LTRIM(SUBSTRING(@img_types, LENGTH('$img_type') + 1))
458 -- enable_query_log
459 }
460
461 -- echo ################## SPECIAL CASES #########################
462
463 -- source include/rpl_reset.inc
464 -- connection master
465 -- let $row_img_set=master:NOBLOB:N,slave:NOBLOB:Y
466 -- source include/rpl_row_img_set.inc
467
468 -- echo ###################################
469 -- echo # PK (contains blob)
470 -- echo ###################################
471
472 -- connection master
473
474 CREATE TABLE t (c1 int, c2 int, c3 blob, primary key(c1,c3(512)));
475
476 -- let $row_img_query= INSERT INTO t VALUES (1,2,"a")
477 -- let $row_img_expected_master= | 1:1 2:2 3:'a'
478 -- let $row_img_expected_slave = | 1:1 2:2 3:'a'
479 -- source include/rpl_row_img_parts_master_slave.inc
480
481 -- let $row_img_query= INSERT INTO t(c1,c3) VALUES (10,"a")
482 -- let $row_img_expected_master= | 1:10 2:NULL 3:'a'
483 -- let $row_img_expected_slave = | 1:10 2:NULL 3:'a'
484 -- source include/rpl_row_img_parts_master_slave.inc
485
486 -- let $row_img_query= INSERT INTO t(c1) VALUES (1000)
487 -- let $row_img_expected_master= | 1:1000 2:NULL
488 -- let $row_img_expected_slave = | 1:1000 2:NULL
489 -- source include/rpl_row_img_parts_master_slave.inc
490
491 -- let $row_img_query= UPDATE t SET c1=2 WHERE c1=1
492 -- let $row_img_expected_master= 1:1 2:2 3:'a' | 1:2 2:2
493 -- let $row_img_expected_slave = 1:1 2:2 3:'a' | 1:2 2:2
494 -- source include/rpl_row_img_parts_master_slave.inc
495
496 -- let $row_img_query= DELETE FROM t WHERE c2=2
497 -- let $row_img_expected_master= 1:2 2:2 3:'a' |
498 -- let $row_img_expected_slave = 1:2 2:2 3:'a' |
499 -- source include/rpl_row_img_parts_master_slave.inc
500
501 DROP TABLE t;
502
503 --source include/sync_slave_sql_with_master.inc
504
505 -- echo ###################################
506 -- echo # PK (does not contain blob, but blob is updated)
507 -- echo ###################################
508
509 -- source include/rpl_reset.inc
510 -- connection master
511
512 CREATE TABLE t (c1 int, c2 int, c3 blob, primary key(c1,c2));
513
514 -- let $row_img_query= INSERT INTO t VALUES (1,2,"a")
515 -- let $row_img_expected_master= | 1:1 2:2 3:'a'
516 -- let $row_img_expected_slave = | 1:1 2:2 3:'a'
517 -- source include/rpl_row_img_parts_master_slave.inc
518
519 -- let $row_img_query= INSERT INTO t(c1,c3) VALUES (10,"a")
520 -- let $row_img_expected_master= | 1:10 2:0 3:'a'
521 -- let $row_img_expected_slave = | 1:10 2:0 3:'a'
522 -- source include/rpl_row_img_parts_master_slave.inc
523
524 -- let $row_img_query= INSERT INTO t(c1) VALUES (1000)
525 -- let $row_img_expected_master= | 1:1000 2:0
526 -- let $row_img_expected_slave = | 1:1000 2:0
527 -- source include/rpl_row_img_parts_master_slave.inc
528
529 -- let $row_img_query= UPDATE t SET c3='b' WHERE c1=1
530 -- let $row_img_expected_master= 1:1 2:2 | 1:1 2:2 3:'b'
531 -- let $row_img_expected_slave = 1:1 2:2 | 1:1 2:2 3:'b'
532 -- source include/rpl_row_img_parts_master_slave.inc
533
534 -- let $row_img_query= DELETE FROM t WHERE c2=2
535 -- let $row_img_expected_master= 1:1 2:2 |
536 -- let $row_img_expected_slave = 1:1 2:2 |
537 -- source include/rpl_row_img_parts_master_slave.inc
538
539 DROP TABLE t;
540 --source include/sync_slave_sql_with_master.inc
541
542 -- echo ###################################
543 -- echo # AUTOINC columns
544 -- echo ###################################
545
546 -- source include/rpl_reset.inc
547 -- connection master
548 -- let $row_img_set=master:MINIMAL:N,slave:MINIMAL:Y
549 -- source include/rpl_row_img_set.inc
550
551 CREATE TABLE t (c1 int NOT NULL AUTO_INCREMENT, c2 int, c3 blob, primary key(c1,c2));
552
553 -- let $row_img_query= INSERT INTO t(c2) VALUES (2)
554 -- let $row_img_expected_master= | 1:1 2:2
555 -- let $row_img_expected_slave = | 1:1 2:2
556 -- source include/rpl_row_img_parts_master_slave.inc
557
558 DROP TABLE t;
559 --source include/sync_slave_sql_with_master.inc
560
561 -- echo ##################################################################
562 -- echo # Test that slave does not write more columns than the ones it has
563 -- echo ##################################################################
564
565 -- source include/rpl_reset.inc
566 -- connection master
567 -- let $row_img_set=master:MINIMAL:N,slave:MINIMAL:Y
568 -- source include/rpl_row_img_set.inc
569
570 SET SQL_LOG_BIN=0;
571 CREATE TABLE t (c1 int NOT NULL AUTO_INCREMENT, c2 int, c3 blob, primary key(c1,c2));
572 SET SQL_LOG_BIN=1;
573
574 -- connection slave
575 CREATE TABLE t (c1 int, c2 int, primary key(c1));
576
577 -- connection master
578
579 -- let $row_img_query= INSERT INTO t(c2,c3) VALUES (2,'aaaaa')
580 -- let $row_img_expected_master= | 1:1 2:2 3:'aaaaa'
581 -- let $row_img_expected_slave = | 1:1 2:2
582 -- source include/rpl_row_img_parts_master_slave.inc
583
584 -- let $row_img_query= UPDATE t SET c2=3, c3='bbbbb' WHERE c2=2
585 -- let $row_img_expected_master= 1:1 2:2 | 2:3 3:'bbbbb'
586 -- let $row_img_expected_slave = 1:1 | 2:3
587 -- source include/rpl_row_img_parts_master_slave.inc
588
589 DROP TABLE t;
590 --source include/sync_slave_sql_with_master.inc
591
592 -- echo ##################################################################
593 -- echo # Test that slave fills default columns in its own columns
594 -- echo ##################################################################
595
596 -- source include/rpl_reset.inc
597 -- connection master
598 -- let $row_img_set=master:FULL:N,slave:FULL:Y
599 -- source include/rpl_row_img_set.inc
600
601 SET SQL_LOG_BIN=0;
602 CREATE TABLE t (c1 int, c2 int);
603 SET SQL_LOG_BIN=1;
604
605 -- connection slave
606 CREATE TABLE t (c1 int, c2 int, c3 int DEFAULT 2005);
607
608 -- connection master
609
610 -- let $row_img_query= INSERT INTO t(c1) VALUES (1)
611 -- let $row_img_expected_master= | 1:1 2:NULL
612 -- let $row_img_expected_slave = | 1:1 2:NULL 3:2005
613 -- source include/rpl_row_img_parts_master_slave.inc
614
615 -- let $row_img_query= INSERT INTO t(c1) VALUES (2)
616 -- let $row_img_expected_master= | 1:2 2:NULL
617 -- let $row_img_expected_slave = | 1:2 2:NULL 3:2005
618 -- source include/rpl_row_img_parts_master_slave.inc
619
620 -- connection slave
621 SELECT * FROM t;
622 -- connection master
623 SELECT * FROM t;
624
625 DROP TABLE t;
626 --source include/sync_slave_sql_with_master.inc
627
628 -- echo ##################################################################
629 -- echo # Test that slave uses partial BI when master contains more columns
630 -- echo ##################################################################
631
632 -- source include/rpl_reset.inc
633 -- connection master
634 -- let $row_img_set=master:MINIMAL:N,slave:MINIMAL:Y
635 -- source include/rpl_row_img_set.inc
636
637
638 SET SQL_LOG_BIN=0;
639 CREATE TABLE t (c1 int NOT NULL, c2 int, c3 int, primary key(c1, c3), unique key(c1));
640 SET SQL_LOG_BIN=1;
641
642 -- connection slave
643 CREATE TABLE t (c1 int NOT NULL, c2 int, unique key(c1));
644
645 -- connection master
646
647 -- let $row_img_query= INSERT INTO t VALUES (1, 2, 3)
648 -- let $row_img_expected_master= | 1:1 2:2 3:3
649 -- let $row_img_expected_slave = | 1:1 2:2
650 -- source include/rpl_row_img_parts_master_slave.inc
651
652 -- let $row_img_query= UPDATE t SET c2= 4 WHERE c1=1
653 -- let $row_img_expected_master= 1:1 3:3 | 2:4
654 -- let $row_img_expected_slave = 1:1 | 2:4
655 -- source include/rpl_row_img_parts_master_slave.inc
656
657 -- connection slave
658 SELECT * FROM t;
659 -- connection master
660 SELECT * FROM t;
661
662 DROP TABLE t;
663 --source include/sync_slave_sql_with_master.inc
664
665 -- echo ##################################################################
666 -- echo # Test that if master has binlog_row_image=MINIMAL and slave has
667 -- echo # NOBLOB or FULL, it will log the expected columns
668 -- echo ##################################################################
669
670 -- source include/rpl_reset.inc
671 -- connection master
672 -- let $row_img_set=master:MINIMAL:N,slave:FULL:Y
673 -- source include/rpl_row_img_set.inc
674
675 SET SQL_LOG_BIN=0;
676 CREATE TABLE t (c1 int NOT NULL, c2 int, c3 int, primary key(c1));
677 SET SQL_LOG_BIN=1;
678
679 -- connection slave
680 CREATE TABLE t (c1 int NOT NULL, c2 int, c3 int, c4 blob, unique key(c1));
681
682 -- connection master
683 -- let $row_img_query= INSERT INTO t VALUES (1, 2, 3)
684 -- let $row_img_expected_master= | 1:1 2:2 3:3
685 -- let $row_img_expected_slave = | 1:1 2:2 3:3 4:NULL
686 -- source include/rpl_row_img_parts_master_slave.inc
687
688 -- let $row_img_query= UPDATE t SET c2= 4 WHERE c1=1
689 -- let $row_img_expected_master= 1:1 | 2:4
690 -- let $row_img_expected_slave = 1:1 2:2 3:3 4:NULL | 1:1 2:4 3:3 4:NULL
691 -- source include/rpl_row_img_parts_master_slave.inc
692
693 -- let $row_img_query= DELETE FROM t WHERE c2=4
694 -- let $row_img_expected_master= 1:1 |
695 -- let $row_img_expected_slave = 1:1 2:4 3:3 4:NULL |
696 -- source include/rpl_row_img_parts_master_slave.inc
697
698 DROP TABLE t;
699 --source include/sync_slave_sql_with_master.inc
700
701 -- source include/rpl_reset.inc
702 -- connection master
703 -- let $row_img_set=master:MINIMAL:N,slave:NOBLOB:Y
704 -- source include/rpl_row_img_set.inc
705
706 SET SQL_LOG_BIN=0;
707 CREATE TABLE t (c1 int NOT NULL, c2 int, c3 int, primary key(c1));
708 SET SQL_LOG_BIN=1;
709
710 -- connection slave
711 CREATE TABLE t (c1 int NOT NULL, c2 int, c3 int, c4 blob, unique key(c1));
712
713 -- connection master
714 -- let $row_img_query= INSERT INTO t VALUES (1, 2, 3)
715 -- let $row_img_expected_master= | 1:1 2:2 3:3
716 -- let $row_img_expected_slave = | 1:1 2:2 3:3
717 -- source include/rpl_row_img_parts_master_slave.inc
718
719 -- let $row_img_query= UPDATE t SET c2= 4 WHERE c1=1
720 -- let $row_img_expected_master= 1:1 | 2:4
721 -- let $row_img_expected_slave = 1:1 2:2 3:3 | 1:1 2:4 3:3
722 -- source include/rpl_row_img_parts_master_slave.inc
723
724 -- let $row_img_query= DELETE FROM t WHERE c2=4
725 -- let $row_img_expected_master= 1:1 |
726 -- let $row_img_expected_slave = 1:1 2:4 3:3 |
727 -- source include/rpl_row_img_parts_master_slave.inc
728
729 DROP TABLE t;
730 --source include/sync_slave_sql_with_master.inc
731
732 -- echo ################################################################
733 -- echo # Test that the slave stop with error if no usable data is on BI
734 -- echo ################################################################
735
736 -- source include/rpl_reset.inc
737 -- connection master
738 -- let $row_img_set=master:MINIMAL:N,slave:NOBLOB:Y
739 -- source include/rpl_row_img_set.inc
740
741 SET SQL_LOG_BIN=0;
742 CREATE TABLE t (c1 int NOT NULL, c2 int, c3 int, primary key(c3));
743 SET SQL_LOG_BIN=1;
744
745 -- connection slave
746 CREATE TABLE t (c1 int NOT NULL, c2 int, primary key(c1));
747
748 -- connection master
749
750 INSERT INTO t VALUES (1,2,3);
751 UPDATE t SET c2=4 WHERE c2=2;
752 DROP TABLE t;
753
754 -- connection slave
755 # 1032==ER_KEY_NOT_FOUND (handler returns HA_ERR_END_OF_FILE)
756 let $slave_sql_errno= 1032;
757 source include/wait_for_slave_sql_error.inc;
758 DROP TABLE t;
759 --let $rpl_only_running_threads= 1
760
761 # ==== Purpose ====
762 #
763 # Check that when binlog_row_image= FULL 'UPDATE' query should not using
764 # temporary if the PRIMARY KEY not being modified as part of the query.
765 #
766 # ==== Implementation ====
767 #
768 # Set binlog_row_image= FULL. Create a table which has both a primary key and
769 # a regular int field which is not a key. Execute an UPDATE statement in such
770 # a way that it doesn't update the primary key field. See the 'EXPLAIN' output
771 # it should not use a temporary table. Repeat the same test in case of
772 # binlog_row_image= NOBLOB as well. No temporary table should be used in this
773 # case as well.
774 #
775 # ==== References ====
776 #
777 # Bug#22510353: UNNECESSARY USING TEMPORARY FOR UPDATE
778 #
779 ###############################################################################
780 # Execute RESET REPLICA before calling rpl_reset.inc
781 # To avoid failure in MTS and rpl-priv_checks_user runs
782 --source include/stop_slave_io.inc
783 RESET REPLICA;
784
785 -- source include/rpl_reset.inc
786 -- connection master
787 -- let $row_img_set=master:FULL:N,slave:FULL:Y
788 -- source include/rpl_row_img_set.inc
789 CREATE TABLE t1(id INT PRIMARY KEY, a INT) ENGINE = INNODB;
790
791 --source include/sync_slave_sql_with_master.inc
792
793 -- connection master
794 -- let $row_img_query= INSERT INTO t1 (id, a) VALUES (1, 1)
795 -- let $row_img_expected_master= | 1:1 2:1
796 -- let $row_img_expected_slave = | 1:1 2:1
797 -- source include/rpl_row_img_parts_master_slave.inc
798
799 -- echo "Case: FULL - EXPLAIN output should not display Using temporary"
800 EXPLAIN UPDATE t1 SET a=a+1 WHERE id < 2;
801
802 -- let $row_img_query= UPDATE t1 SET a=a+1 WHERE id < 2
803 -- let $row_img_expected_master= 1:1 2:1 | 1:1 2:2
804 -- let $row_img_expected_slave = 1:1 2:1 | 1:1 2:2
805 -- source include/rpl_row_img_parts_master_slave.inc
806
807 -- let $row_img_set=master:NOBLOB:N,slave:NOBLOB:Y
808 -- source include/rpl_row_img_set.inc
809
810 -- echo "Case: NOBLOB - EXPLAIN output should not display Using temporary"
811 EXPLAIN UPDATE t1 SET a=a+1 WHERE id < 2;
812
813 -- let $row_img_query= UPDATE t1 SET a=a+1 WHERE id < 2
814 -- let $row_img_expected_master= 1:1 2:2 | 1:1 2:3
815 -- let $row_img_expected_slave = 1:1 2:2 | 1:1 2:3
816 -- source include/rpl_row_img_parts_master_slave.inc
817
818 -- let $row_img_set=master:MINIMAL:N,slave:MINIMAL:Y
819 -- source include/rpl_row_img_set.inc
820
821 EXPLAIN UPDATE t1 SET a=a+1 WHERE id < 2;
822
823 -- let $row_img_query= UPDATE t1 SET a=a+1 WHERE id < 2
824 -- let $row_img_expected_master= 1:1 | 2:4
825 -- let $row_img_expected_slave = 1:1 | 2:4
826 -- source include/rpl_row_img_parts_master_slave.inc
827
828 DROP TABLE t1;
829 --source include/sync_slave_sql_with_master.inc
830
831 ## CLEAN UP
832
833 -- connection master
834 SET GLOBAL binlog_row_image= @old_binlog_row_image;
835 SET SESSION binlog_row_image= @old_binlog_row_image;
836 -- connection slave
837 SET GLOBAL binlog_row_image= @old_binlog_row_image;
838 SET SESSION binlog_row_image= @old_binlog_row_image;
839
840 --source include/rpl_end.inc