Debian Codegraph @

mysql-test/suite/rpl/include/rpl_row_basic.test

raw
1 #
2 # Basic tests of row-level logging
3 #
4
5 #
6 # First we test tables with only an index.
7 #
8
9 #BUG#12662190 - COM_COMMIT IS NOT INCREMENTED FROM THE BINARY LOGS ON SLAVE, COM_BEGIN IS
10 #Testing command counters -BEFORE
11 #Storing the before counts of Slave
12 connection slave;
13 let $slave_com_commit_before= query_get_value(SHOW GLOBAL STATUS LIKE 'com_commit', Value, 1);
14 let $slave_com_insert_before= query_get_value(SHOW GLOBAL STATUS LIKE 'com_insert', Value, 1);
15 let $slave_com_delete_before= query_get_value(SHOW GLOBAL STATUS LIKE 'com_delete', Value, 1);
16 let $slave_com_update_before= query_get_value(SHOW GLOBAL STATUS LIKE 'com_update', Value, 1);
17
18 connection master;
19 eval CREATE TABLE t1 (C1 CHAR(1), C2 CHAR(1), INDEX (C1)$extra_index_t1) ENGINE = $type ;
20 SELECT * FROM t1;
21 sync_slave_with_master;
22 SELECT * FROM t1;
23
24 # Testing insert
25 connection master;
26 INSERT INTO t1 VALUES ('A','B'), ('X','Y'), ('X','X');
27 INSERT INTO t1 VALUES ('A','C'), ('X','Z'), ('A','A');
28 SELECT * FROM t1 ORDER BY C1,C2;
29 sync_slave_with_master;
30 SELECT * FROM t1 ORDER BY C1,C2;
31
32 # Testing delete
33 # Observe that are several rows having the value for the index but only one
34 # should be deleted.
35 connection master;
36 DELETE FROM t1 WHERE C1 = C2;
37 SELECT * FROM t1 ORDER BY C1,C2;
38 sync_slave_with_master;
39 SELECT * FROM t1 ORDER BY C1,C2;
40
41 #
42 # Testing update.
43 # Note that we have a condition on a column that is not part of the index for
44 # the table. The right row should be updated nevertheless.
45 #
46 connection master;
47 UPDATE t1 SET C2 = 'I' WHERE C1 = 'A' AND C2 = 'C';
48 SELECT * FROM t1 ORDER BY C1,C2;
49 sync_slave_with_master;
50 SELECT * FROM t1 ORDER BY C1,C2;
51
52 #BUG#12662190 - COM_COMMIT IS NOT INCREMENTED FROM THE BINARY LOGS ON SLAVE, COM_BEGIN IS
53 #Testing command counters -AFTER
54 #Storing the after counts of Slave
55 connection slave;
56 let $slave_com_commit_after= query_get_value(SHOW GLOBAL STATUS LIKE 'com_commit', Value, 1);
57 let $slave_com_insert_after= query_get_value(SHOW GLOBAL STATUS LIKE 'com_insert', Value, 1);
58 let $slave_com_delete_after= query_get_value(SHOW GLOBAL STATUS LIKE 'com_delete', Value, 1);
59 let $slave_com_update_after= query_get_value(SHOW GLOBAL STATUS LIKE 'com_update', Value, 1);
60
61 #Commit count check
62 --let $assert_text= Counter for COM_COMMIT is consistent with the number of actual commits
63 --let $assert_cond= $slave_com_commit_after - $slave_com_commit_before = 4
64 --source include/assert.inc
65 #Insert count check
66 --let $assert_text= Counter for COM_INSERT is consistent with the number of actual inserts
67 --let $assert_cond= $slave_com_insert_after - $slave_com_insert_before = 2
68 --source include/assert.inc
69 #Delete count check
70 --let $assert_text= Counter for COM_DELETE is consistent with the number of actual deletes
71 --let $assert_cond= $slave_com_delete_after - $slave_com_delete_before = 1
72 --source include/assert.inc
73 #Update count check
74 --let $assert_text= Counter for COM_UPDATE is consistent with the number of actual updates
75 --let $assert_cond= $slave_com_update_after - $slave_com_update_before = 1
76 --source include/assert.inc
77
78 # Testing update with a condition that does not match any rows, but
79 # which has a match for the index.
80 connection master;
81 UPDATE t1 SET c2 = 'Q' WHERE c1 = 'A' AND c2 = 'N';
82 SELECT * FROM t1 ORDER BY c1,c2;
83 sync_slave_with_master;
84 SELECT * FROM t1 ORDER BY c1,c2;
85
86 #
87 # Testing table with primary key
88 #
89 connection master;
90 eval CREATE TABLE t2 (c1 INT, c12 char(1), c2 INT, PRIMARY KEY (c1)) ENGINE = $type ;
91 INSERT INTO t2
92 VALUES (1,'A',2), (2,'A',4), (3,'A',9), (4,'A',15), (5,'A',25),
93 (6,'A',35), (7,'A',50), (8,'A',64), (9,'A',81);
94 SELECT * FROM t2 ORDER BY c1,c2;
95 SELECT * FROM t2 WHERE c2 = c1 * c1 ORDER BY c1,c2;
96 sync_slave_with_master;
97 SELECT * FROM t2 ORDER BY c1,c2;
98 SELECT * FROM t2 WHERE c2 = c1 * c1 ORDER BY c1,c2;
99
100 connection master;
101 UPDATE t2 SET c2 = c1*c1 WHERE c2 != c1*c1;
102 SELECT * FROM t2 WHERE c2 = c1 * c1 ORDER BY c1,c2;
103 sync_slave_with_master;
104 SELECT * FROM t2 WHERE c2 = c1 * c1 ORDER BY c1,c2;
105
106 # Testing update with a condition that does not match any rows, but
107 # which has a match for the primary key.
108 connection master;
109 UPDATE t2 SET c12 = 'Q' WHERE c1 = 1 AND c2 = 999;
110 SELECT * FROM t2 ORDER BY c1,c2;
111 sync_slave_with_master;
112 SELECT * FROM t2 ORDER BY c1,c2;
113
114 connection master;
115 DELETE FROM t2 WHERE c1 % 4 = 0;
116 SELECT * FROM t2 ORDER BY c1,c2;
117 sync_slave_with_master;
118 SELECT * FROM t2 ORDER BY c1,c2;
119
120 connection master;
121 UPDATE t2 SET c12='X';
122
123 #
124 # Testing table with a multi-column primary key.
125 #
126 connection master;
127 eval CREATE TABLE t3 (C1 CHAR(1), C2 CHAR(1), pk1 INT, C3 CHAR(1), pk2 INT, PRIMARY KEY (pk1,pk2)) ENGINE = $type ;
128
129 INSERT INTO t3 VALUES ('A','B',1,'B',1), ('X','Y',2,'B',1), ('X','X',3,'B',1);
130 INSERT INTO t3 VALUES ('A','C',1,'B',2), ('X','Z',2,'B',2), ('A','A',3,'B',2);
131 SELECT * FROM t3 ORDER BY C1,C2;
132 sync_slave_with_master;
133 SELECT * FROM t3 ORDER BY C1,C2;
134
135 connection master;
136 DELETE FROM t3 WHERE C1 = C2;
137 SELECT * FROM t3 ORDER BY C1,C2;
138 sync_slave_with_master;
139 SELECT * FROM t3 ORDER BY C1,C2;
140
141 connection master;
142 UPDATE t3 SET C2 = 'I' WHERE C1 = 'A' AND C2 = 'C';
143 SELECT * FROM t3 ORDER BY C1,C2;
144 sync_slave_with_master;
145 SELECT * FROM t3 ORDER BY C1,C2;
146
147 #
148 # Testing table without index or primary key
149 #
150 connection master;
151 eval CREATE TABLE t6 (C1 CHAR(1), C2 CHAR(1), C3 INT$extra_index_t6) ENGINE = $type;
152
153 # Testing insert
154 INSERT INTO t6 VALUES ('A','B',1), ('X','Y',2), ('X','X',3);
155 INSERT INTO t6 VALUES ('A','C',4), ('X','Z',5), ('A','A',6);
156 SELECT * FROM t6 ORDER BY C3;
157 sync_slave_with_master;
158 SELECT * FROM t6 ORDER BY C3;
159
160 # Testing delete
161 # Observe that are several rows having the value for the index but only one
162 # should be deleted.
163 connection master;
164 DELETE FROM t6 WHERE C1 = C2;
165 SELECT * FROM t6 ORDER BY C3;
166 sync_slave_with_master;
167 SELECT * FROM t6 ORDER BY C3;
168
169 #
170 # Testing update.
171 # Note that we have a condition on a column that is not part of the index for
172 # the table. The right row should be updated nevertheless.
173 #
174 connection master;
175 UPDATE t6 SET C2 = 'I' WHERE C1 = 'A' AND C2 = 'C';
176 SELECT * FROM t6 ORDER BY C3;
177 sync_slave_with_master;
178 SELECT * FROM t6 ORDER BY C3;
179
180 # now mixing the 3 tables without begin/commit
181 connection master;
182 eval CREATE TABLE t5 (C1 CHAR(1), C2 CHAR(1), C3 INT PRIMARY KEY) ENGINE = $type ;
183 INSERT INTO t5 VALUES ('A','B',1), ('X','Y',2), ('X','X',3);
184 INSERT INTO t5 VALUES ('A','C',4), ('X','Z',5), ('A','A',6);
185
186 UPDATE t5,t2,t3 SET t5.C2='Q', t2.c12='R', t3.C3 ='S' WHERE t5.C1 = t2.c12 AND t5.C1 = t3.C1;
187 SELECT * FROM t5,t2,t3 WHERE t5.C2='Q' AND t2.c12='R' AND t3.C3 ='S' ORDER BY t5.C3,t2.c1,t3.pk1,t3.pk2;
188 sync_slave_with_master;
189 SELECT * FROM t5,t2,t3 WHERE t5.C2='Q' AND t2.c12='R' AND t3.C3 ='S' ORDER BY t5.C3,t2.c1,t3.pk1,t3.pk2;
190
191 #
192 # Testing special column types
193 #
194
195 if (`select char_length('$bit_field_special') > 0`) {
196 connection slave;
197 SET @saved_slave_type_conversions = @@SLAVE_TYPE_CONVERSIONS;
198 eval SET GLOBAL SLAVE_TYPE_CONVERSIONS = '$bit_field_special';
199 }
200
201 connection master;
202 eval CREATE TABLE t4 (C1 CHAR(1) PRIMARY KEY, B1 BIT(1), B2 BIT(1) NOT NULL DEFAULT 0, C2 CHAR(1) NOT NULL DEFAULT 'A') ENGINE = $type ;
203
204 INSERT INTO t4 SET C1 = 1;
205 SELECT C1,HEX(B1),HEX(B2) FROM t4 ORDER BY C1;
206 sync_slave_with_master;
207 SELECT C1,HEX(B1),HEX(B2) FROM t4 ORDER BY C1;
208
209 if (`select char_length('$bit_field_special') > 0`) {
210 SET GLOBAL SLAVE_TYPE_CONVERSIONS = @saved_slave_type_conversions;
211 }
212
213 #
214 # Testing conflicting operations
215 #
216 connection master;
217 eval CREATE TABLE t7 (C1 INT PRIMARY KEY, C2 INT) ENGINE = $type ;
218 sync_slave_with_master;
219 --echo --- original values ---
220 INSERT INTO t7 VALUES (1,3), (2,6), (3,9);
221 SELECT * FROM t7 ORDER BY C1;
222
223 # since bug#31552/31609 idempotency is not default any longer. In order
224 # the preceding test INSERT INTO t7 to pass the mode is switched
225 # temprorarily
226 set @@global.slave_exec_mode= 'IDEMPOTENT';
227
228 connection master;
229 --echo --- new values inserted ---
230 INSERT INTO t7 VALUES (1,2), (2,4), (3,6);
231 SELECT * FROM t7 ORDER BY C1;
232 sync_slave_with_master;
233
234 set @@global.slave_exec_mode= default;
235 --echo --- old values should be overwritten by replicated values ---
236 SELECT * FROM t7 ORDER BY C1;
237
238 #
239 # A more complicated test where the table has several keys and we are
240 # causing a conflict for a key that is not "last".
241 #
242 connection master;
243 eval CREATE TABLE t8 (a INT PRIMARY KEY, b INT UNIQUE, c INT UNIQUE) ENGINE = $type ;
244
245 # First we make sure that the constraints are correctly set.
246 INSERT INTO t8 VALUES (99,99,99);
247 --error ER_DUP_ENTRY
248 INSERT INTO t8 VALUES (99,22,33);
249 --error ER_DUP_ENTRY
250 INSERT INTO t8 VALUES (11,99,33);
251 --error ER_DUP_ENTRY
252 INSERT INTO t8 VALUES (11,22,99);
253 SELECT * FROM t8 ORDER BY a;
254
255 sync_slave_with_master;
256 SELECT * FROM t8 ORDER BY a;
257 INSERT INTO t8 VALUES (1,2,3), (2,4,6), (3,6,9);
258 SELECT * FROM t8 ORDER BY a;
259
260 # since bug#31552/31609 idempotency is not default any longer. In order
261 # the preceding test INSERT INTO t8 to pass the mode is switched
262 # temprorarily
263 set @@global.slave_exec_mode= 'IDEMPOTENT';
264
265 connection master;
266 # We insert a row that will cause conflict on the primary key but not
267 # on the other keys.
268 INSERT INTO t8 VALUES (2,4,8);
269 sync_slave_with_master;
270 set @@global.slave_exec_mode= default;
271
272 SELECT * FROM t8 ORDER BY a;
273
274 # BUG#31552: Replication breaks when deleting rows from out-of-sync
275 # table without PK
276
277 --echo **** Test for BUG#31552 ****
278
279 # Clean up t1 so that we can use it.
280 connection master;
281 DELETE FROM t1;
282 sync_slave_with_master;
283
284 # Just to get a clean binary log
285 --source include/rpl_reset.inc
286
287 connection master;
288 INSERT INTO t1 VALUES ('K','K'), ('L','L'), ('M','M');
289 sync_slave_with_master;
290 # since bug#31552/31609 idempotency is not default any longer. In order
291 # the following test DELETE FROM t1 to pass the mode is switched
292 # temprorarily
293 set @@global.slave_exec_mode= 'IDEMPOTENT';
294 DELETE FROM t1 WHERE C1 = 'L';
295
296 connection master;
297 DELETE FROM t1;
298 query_vertical SELECT COUNT(*) FROM t1 ORDER BY c1,c2;
299 sync_slave_with_master;
300 set @@global.slave_exec_mode= default;
301 source include/check_slave_is_running.inc;
302 query_vertical SELECT COUNT(*) FROM t1 ORDER BY c1,c2;
303
304 # BUG#37076: TIMESTAMP/DATETIME values are not replicated correctly
305 # between machines with mixed endiannes
306 # (regression test)
307
308 --echo **** Test for BUG#37076 ****
309 connection master;
310 DROP TABLE IF EXISTS t1;
311 CREATE TABLE t1 (a TIMESTAMP, b DATETIME, c DATE);
312 INSERT INTO t1 VALUES(
313 '2005-11-14 01:01:01', '2005-11-14 01:01:02', '2005-11-14');
314
315 sync_slave_with_master slave;
316 SELECT * FROM t1;
317
318 #
319 # cleanup
320 #
321
322 connection master;
323 DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8;
324 sync_slave_with_master;
325
326 #
327 # BUG#37426: RBR breaks for CHAR() UTF8 fields > 85 chars
328 #
329
330 # We have 4 combinations to test with respect to the field length
331 # (i.e., the number of bytes) of the CHAR fields:
332 #
333 # 1. Replicating from CHAR<256 to CHAR<256
334 # 2. Replicating from CHAR<256 to CHAR>255
335 # 3. Replicating from CHAR>255 to CHAR<256
336 # 4. Replicating from CHAR>255 to CHAR>255
337
338 # We also make a special case of using the max size of a field on the
339 # master, i.e. CHAR(255) in UTF-8, giving another three cases.
340 #
341 # 5. Replicating UTF-8 CHAR(255) to CHAR(<256)
342 # 6. Replicating UTF-8 CHAR(255) to CHAR(>255)
343 # 7. Replicating UTF-8 CHAR(255) to CHAR(255) UTF-8
344
345 connection master;
346 eval CREATE TABLE t1 (i INT NOT NULL,
347 c CHAR(16) CHARACTER SET utf8 NOT NULL,
348 j INT NOT NULL) ENGINE = $type ;
349
350 eval CREATE TABLE t2 (i INT NOT NULL,
351 c CHAR(16) CHARACTER SET utf8 NOT NULL,
352 j INT NOT NULL) ENGINE = $type ;
353
354 sync_slave_with_master;
355 ALTER TABLE t2 MODIFY c CHAR(128) CHARACTER SET utf8 NOT NULL;
356
357 connection master;
358 eval CREATE TABLE t3 (i INT NOT NULL,
359 c CHAR(128) CHARACTER SET utf8 NOT NULL,
360 j INT NOT NULL) ENGINE = $type ;
361 sync_slave_with_master;
362 ALTER TABLE t3 MODIFY c CHAR(16) CHARACTER SET utf8 NOT NULL;
363
364 connection master;
365 eval CREATE TABLE t4 (i INT NOT NULL,
366 c CHAR(128) CHARACTER SET utf8 NOT NULL,
367 j INT NOT NULL) ENGINE = $type ;
368
369 eval CREATE TABLE t5 (i INT NOT NULL,
370 c CHAR(255) CHARACTER SET utf8 NOT NULL,
371 j INT NOT NULL) ENGINE = $type ;
372 sync_slave_with_master;
373 ALTER TABLE t5 MODIFY c CHAR(16) CHARACTER SET utf8 NOT NULL;
374
375 connection master;
376 eval CREATE TABLE t6 (i INT NOT NULL,
377 c CHAR(255) CHARACTER SET utf8 NOT NULL,
378 j INT NOT NULL) ENGINE = $type ;
379 sync_slave_with_master;
380 ALTER TABLE t6 MODIFY c CHAR(128) CHARACTER SET utf8 NOT NULL;
381
382 connection master;
383 eval CREATE TABLE t7 (i INT NOT NULL,
384 c CHAR(255) CHARACTER SET utf8 NOT NULL,
385 j INT NOT NULL) ENGINE = $type ;
386
387 connection slave;
388 SET @saved_slave_type_conversions = @@slave_type_conversions;
389 SET GLOBAL SLAVE_TYPE_CONVERSIONS = 'ALL_NON_LOSSY';
390
391 --echo [expecting slave to replicate correctly]
392 connection master;
393 INSERT INTO t1 VALUES (1, "", 1);
394 INSERT INTO t1 VALUES (2, repeat(_utf8'a', 16), 2);
395 sync_slave_with_master;
396
397 let $diff_tables= master:t1, slave:t1;
398 source include/diff_tables.inc;
399
400 --echo [expecting slave to replicate correctly]
401 connection master;
402 INSERT INTO t2 VALUES (1, "", 1);
403 INSERT INTO t2 VALUES (2, repeat(_utf8'a', 16), 2);
404 sync_slave_with_master;
405
406 let $diff_tables= master:t2, slave:t2;
407 source include/diff_tables.inc;
408
409 connection slave;
410 SET GLOBAL SLAVE_TYPE_CONVERSIONS = @saved_slave_type_conversions;
411 call mtr.add_suppression("Slave SQL.*Table definition on master and slave does not match: Column 1 size mismatch.* error.* 1535");
412 call mtr.add_suppression("Slave SQL.*Could not execute Delete_rows event on table test.t1.* error.* 1032");
413 call mtr.add_suppression("Slave SQL.*Column 1 of table .test.t.. cannot be converted from type.*, error.* 1677");
414 call mtr.add_suppression("Can't find record in 't1'");
415
416 --let $rpl_only_running_threads= 1
417 --source include/rpl_reset.inc
418
419 --echo [expecting slave to replicate correctly]
420 connection master;
421 INSERT INTO t4 VALUES (1, "", 1);
422 INSERT INTO t4 VALUES (2, repeat(_utf8'a', 128), 2);
423 sync_slave_with_master;
424
425 let $diff_tables= master:t4, slave:t4;
426 source include/diff_tables.inc;
427
428 --echo [expecting slave to stop]
429 connection master;
430 INSERT INTO t5 VALUES (1, "", 1);
431 INSERT INTO t5 VALUES (2, repeat(_utf8'a', 255), 2);
432
433 connection slave;
434 # 1677 = ER_SLAVE_CONVERSION_FAILED
435 --let $slave_sql_errno= 1677
436 --let $show_slave_sql_error= 1
437 --source include/wait_for_slave_sql_error.inc
438
439 --source include/rpl_reset.inc
440
441 --echo [expecting slave to stop]
442 connection master;
443 INSERT INTO t6 VALUES (1, "", 1);
444 INSERT INTO t6 VALUES (2, repeat(_utf8'a', 255), 2);
445
446 connection slave;
447 # 1677 = ER_SLAVE_CONVERSION_FAILED
448 --let $slave_sql_errno= 1677
449 --let $show_slave_sql_error= 1
450 --source include/wait_for_slave_sql_error.inc
451
452 --source include/rpl_reset.inc
453
454 --echo [expecting slave to replicate correctly]
455 connection master;
456 INSERT INTO t7 VALUES (1, "", 1);
457 INSERT INTO t7 VALUES (2, repeat(_utf8'a', 255), 2);
458 sync_slave_with_master;
459
460 let $diff_tables= master:t7, slave:t7;
461 source include/diff_tables.inc;
462
463 connection master;
464 drop table t1, t2, t3, t4, t5, t6, t7;
465 sync_slave_with_master;
466
467 #
468 # BUG#32709: Assertion failed: trx_data->empty(), file .\log.cc, line 1293
469 #
470
471 connection master;
472 eval CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=$type;
473
474 INSERT INTO t1 VALUES (1), (2), (3);
475 --error ER_DUP_ENTRY
476 UPDATE t1 SET a = 10;
477 INSERT INTO t1 VALUES (4);
478 sync_slave_with_master;
479
480 let $diff_tables= master:t1, slave:t1;
481 source include/diff_tables.inc;
482
483 connection master;
484 drop table t1;
485 sync_slave_with_master;
486
487 #
488 # Bug #38230 Differences between master and slave after
489 # UPDATE or DELETE with LIMIT with pk
490 #
491 # the regression test verifies consistency via selecting
492
493 --disable_abort_on_error
494
495 --connection master
496
497 --disable_warnings
498 DROP TABLE IF EXISTS t1, t2;
499 --enable_warnings
500
501 eval CREATE TABLE t1 (
502 `pk` int(11) NOT NULL AUTO_INCREMENT,
503 `int_nokey` int(11) NOT NULL,
504 `int_key` int(11) NOT NULL,
505 `date_key` date NOT NULL,
506 `date_nokey` date NOT NULL,
507 `time_key` time NOT NULL,
508 `time_nokey` time NOT NULL,
509 `datetime_key` datetime NOT NULL,
510 `datetime_nokey` datetime NOT NULL,
511 `varchar_key` varchar(1) NOT NULL,
512 `varchar_nokey` varchar(1) NOT NULL,
513 PRIMARY KEY (`pk`),
514 KEY `int_key` (`int_key`),
515 KEY `date_key` (`date_key`),
516 KEY `time_key` (`time_key`),
517 KEY `datetime_key` (`datetime_key`),
518 KEY `varchar_key` (`varchar_key`)
519 ) ENGINE=$type;
520
521 INSERT INTO t1 VALUES (1,8,5,'0000-00-00','0000-00-00','10:37:38','10:37:38','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'),(2,0,9,'0000-00-00','0000-00-00','00:00:00','00:00:00','2007-10-14 00:00:00','2007-10-14 00:00:00','d','d');
522
523 eval CREATE TABLE t2 (
524 `pk` int(11) NOT NULL AUTO_INCREMENT,
525 `int_nokey` int(11) NOT NULL,
526 `int_key` int(11) NOT NULL,
527 `date_key` date NOT NULL,
528 `date_nokey` date NOT NULL,
529 `time_key` time NOT NULL,
530 `time_nokey` time NOT NULL,
531 `datetime_key` datetime NOT NULL,
532 `datetime_nokey` datetime NOT NULL,
533 `varchar_key` varchar(1) NOT NULL,
534 `varchar_nokey` varchar(1) NOT NULL,
535 PRIMARY KEY (`pk`),
536 KEY `int_key` (`int_key`),
537 KEY `date_key` (`date_key`),
538 KEY `time_key` (`time_key`),
539 KEY `datetime_key` (`datetime_key`),
540 KEY `varchar_key` (`varchar_key`)
541 ) ENGINE=$type;
542
543 INSERT INTO t2 VALUES (1,1,6,'2005-12-23','2005-12-23','02:24:28','02:24:28','0000-00-00 00:00:00','0000-00-00 00:00:00','g','g'),(2,0,3,'2009-09-14','2009-09-14','00:00:00','00:00:00','2000-01-30 16:39:40','2000-01-30 16:39:40','q','q'),(3,0,3,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','c','c'),(4,1,6,'2007-03-29','2007-03-29','15:49:00','15:49:00','0000-00-00 00:00:00','0000-00-00 00:00:00','m','m'),(5,4,0,'2002-12-04','2002-12-04','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','o','o'),(6,9,0,'2005-01-28','2005-01-28','00:00:00','00:00:00','2001-05-18 00:00:00','2001-05-18 00:00:00','w','w'),(7,6,0,'0000-00-00','0000-00-00','06:57:25','06:57:25','0000-00-00 00:00:00','0000-00-00 00:00:00','m','m'),(8,0,0,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','z','z'),(9,4,6,'2006-08-15','2006-08-15','00:00:00','00:00:00','2002-04-12 14:44:25','2002-04-12 14:44:25','j','j'),(10,0,5,'2006-12-20','2006-12-20','10:13:53','10:13:53','2008-07-22 00:00:00','2008-07-22 00:00:00','y','y'),(11,9,7,'0000-00-00','0000-00-00','00:00:00','00:00:00','2004-07-05 00:00:00','2004-07-05 00:00:00','{','{'),(12,4,3,'2007-01-26','2007-01-26','23:00:51','23:00:51','2001-05-16 00:00:00','2001-05-16 00:00:00','f','f'),(13,7,0,'2004-03-27','2004-03-27','00:00:00','00:00:00','2005-01-24 03:30:37','2005-01-24 03:30:37','',''),(14,6,0,'2006-07-26','2006-07-26','18:43:57','18:43:57','0000-00-00 00:00:00','0000-00-00 00:00:00','{','{'),(15,0,6,'2000-01-14','2000-01-14','00:00:00','00:00:00','2000-09-21 00:00:00','2000-09-21 00:00:00','o','o'),(16,9,8,'0000-00-00','0000-00-00','21:15:08','21:15:08','0000-00-00 00:00:00','0000-00-00 00:00:00','a','a'),(17,2,0,'2004-10-27','2004-10-27','00:00:00','00:00:00','2004-03-24 22:13:43','2004-03-24 22:13:43','',''),(18,7,4,'0000-00-00','0000-00-00','08:38:27','08:38:27','2002-03-18 19:51:44','2002-03-18 19:51:44','t','t'),(19,5,3,'2008-03-07','2008-03-07','03:29:07','03:29:07','2007-12-01 18:44:44','2007-12-01 18:44:44','t','t'),(20,0,0,'2002-04-09','2002-04-09','16:06:03','16:06:03','2009-04-22 00:00:00','2009-04-22 00:00:00','n','n');
544
545 DELETE FROM t2 WHERE `int_key` < 3 LIMIT 1;
546 UPDATE t1 SET `int_key` = 3 ORDER BY `pk` LIMIT 4;
547 DELETE FROM t2 WHERE `int_key` < 3 LIMIT 1;
548 DELETE FROM t2 WHERE `pk` < 6 LIMIT 1;
549 UPDATE t1 SET `int_key` = 6 ORDER BY `pk` LIMIT 3;
550 DELETE FROM t2 WHERE `pk` < 6 LIMIT 1;
551 UPDATE t1 SET `pk` = 6 ORDER BY `int_key` LIMIT 6;
552 DELETE FROM t2 WHERE `pk` < 7 LIMIT 1;
553 UPDATE t1 SET `int_key` = 4 ORDER BY `pk` LIMIT 6;
554
555 --sync_slave_with_master
556 --echo *** results: t2 must be consistent ****
557
558 let $diff_tables= master:t2, slave:t2;
559 source include/diff_tables.inc;
560
561 --connection master
562 DROP TABLE t1, t2;
563
564 --enable_abort_on_error
565
566 --echo EOF OF TESTS
567
568 #
569 # BUG#40004: Replication failure with no PK + no indexes
570 #
571
572 # The test cases are taken from the bug report. It is difficult to
573 # produce a test case that generates a HA_ERR_RECORD_DELETED, so we go
574 # with the test cases we have.
575
576 connection master;
577
578 eval CREATE TABLE t1 (a int) ENGINE=$type;
579
580 INSERT IGNORE INTO t1 VALUES (NULL);
581 INSERT INTO t1 ( a ) VALUES ( 0 );
582 INSERT INTO t1 ( a ) VALUES ( 9 );
583 INSERT INTO t1 ( a ) VALUES ( 2 );
584 INSERT INTO t1 ( a ) VALUES ( 9 );
585 INSERT INTO t1 ( a ) VALUES ( 5 );
586
587 UPDATE t1 SET a = 5 WHERE a = 9;
588 DELETE FROM t1 WHERE a < 6;
589 UPDATE t1 SET a = 9 WHERE a < 3;
590 INSERT INTO t1 ( a ) VALUES ( 3 );
591 UPDATE t1 SET a = 0 WHERE a < 4;
592 UPDATE t1 SET a = 8 WHERE a < 5;
593
594 sync_slave_with_master;
595
596 let $diff_tables= master:t1, slave:t1;
597 source include/diff_tables.inc;
598
599 connection master;
600 drop table t1;
601 sync_slave_with_master;
602
603 #
604 # Bug #39752: Replication failure on RBR + MyISAM + no PK
605 #
606
607 # The test cases are taken from the bug report. It is difficult to
608 # produce a test case that generates a HA_ERR_RECORD_DELETED, so we go
609 # with the test cases we have.
610
611 connection master;
612
613 # Since t1 contain a bit field, we have to do this trick to handle InnoDB
614 if (`select char_length('$bit_field_special') > 0`) {
615 connection slave;
616 SET @saved_slave_type_conversions = @@SLAVE_TYPE_CONVERSIONS;
617 eval SET GLOBAL SLAVE_TYPE_CONVERSIONS = '$bit_field_special';
618 }
619
620 --disable_warnings
621 connection master;
622 eval CREATE TABLE t1 (a bit) ENGINE=$type;
623 INSERT IGNORE INTO t1 VALUES (NULL);
624 INSERT INTO t1 ( a ) VALUES ( 0 );
625 UPDATE t1 SET a = 0 WHERE a = 1 LIMIT 3;
626 INSERT IGNORE INTO t1 ( a ) VALUES ( 5 );
627 DELETE FROM t1 WHERE a < 2 LIMIT 4;
628 DELETE FROM t1 WHERE a < 9 LIMIT 4;
629 INSERT IGNORE INTO t1 ( a ) VALUES ( 9 );
630 UPDATE t1 SET a = 8 WHERE a = 0 LIMIT 6;
631 INSERT IGNORE INTO t1 ( a ) VALUES ( 8 );
632 UPDATE t1 SET a = 0 WHERE a < 6 LIMIT 0;
633 INSERT IGNORE INTO t1 ( a ) VALUES ( 4 );
634 INSERT IGNORE INTO t1 ( a ) VALUES ( 3 );
635 UPDATE t1 SET a = 0 WHERE a = 7 LIMIT 6;
636 DELETE FROM t1 WHERE a = 4 LIMIT 7;
637 UPDATE IGNORE t1 SET a = 9 WHERE a < 2 LIMIT 9;
638 UPDATE t1 SET a = 0 WHERE a < 9 LIMIT 2;
639 DELETE FROM t1 WHERE a < 0 LIMIT 5;
640 INSERT IGNORE INTO t1 ( a ) VALUES ( 5 );
641 UPDATE IGNORE t1 SET a = 4 WHERE a < 6 LIMIT 4;
642 INSERT IGNORE INTO t1 ( a ) VALUES ( 5 );
643 UPDATE IGNORE t1 SET a = 9 WHERE a < 5 LIMIT 8;
644 DELETE FROM t1 WHERE a < 8 LIMIT 8;
645 INSERT IGNORE INTO t1 ( a ) VALUES ( 6 );
646 DELETE FROM t1 WHERE a < 6 LIMIT 7;
647 UPDATE t1 SET a = 7 WHERE a = 3 LIMIT 7;
648 UPDATE t1 SET a = 8 WHERE a = 0 LIMIT 6;
649 INSERT IGNORE INTO t1 ( a ) VALUES ( 7 );
650 DELETE FROM t1 WHERE a < 9 LIMIT 4;
651 INSERT IGNORE INTO t1 ( a ) VALUES ( 7 );
652 INSERT IGNORE INTO t1 ( a ) VALUES ( 6 );
653 UPDATE t1 SET a = 8 WHERE a = 3 LIMIT 4;
654 DELETE FROM t1 WHERE a = 2 LIMIT 9;
655 DELETE FROM t1 WHERE a = 1 LIMIT 4;
656 UPDATE t1 SET a = 4 WHERE a = 2 LIMIT 7;
657 INSERT INTO t1 ( a ) VALUES ( 0 );
658 DELETE FROM t1 WHERE a < 3 LIMIT 0;
659 UPDATE t1 SET a = 8 WHERE a = 5 LIMIT 2;
660 INSERT INTO t1 ( a ) VALUES ( 1 );
661 UPDATE IGNORE t1 SET a = 9 WHERE a < 5 LIMIT 3;
662 --enable_warnings
663
664 sync_slave_with_master;
665
666 if (`select char_length('$bit_field_special') > 0`) {
667 SET GLOBAL SLAVE_TYPE_CONVERSIONS = @saved_slave_type_conversions;
668 }
669
670 let $diff_tables= master:t1, slave:t1;
671 source include/diff_tables.inc;
672
673 connection master;
674 drop table t1;
675 sync_slave_with_master;