rpl_row_basic.test 21.2 KB
Newer Older
1 2 3 4 5 6 7 8
#
# Basic tests of row-level logging
#

#
# First we test tables with only an index.
#

9
connection master;
10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159
eval CREATE TABLE t1 (C1 CHAR(1), C2 CHAR(1), INDEX (C1)$extra_index_t1) ENGINE = $type ;
SELECT * FROM t1;
sync_slave_with_master;
SELECT * FROM t1;

# Testing insert
connection master;
INSERT INTO t1 VALUES ('A','B'), ('X','Y'), ('X','X');
INSERT INTO t1 VALUES ('A','C'), ('X','Z'), ('A','A');
SELECT * FROM t1 ORDER BY C1,C2;
sync_slave_with_master;
SELECT * FROM t1 ORDER BY C1,C2;

# Testing delete
# Observe that are several rows having the value for the index but only one
# should be deleted.
connection master;
DELETE FROM t1 WHERE C1 = C2;
SELECT * FROM t1 ORDER BY C1,C2;
sync_slave_with_master;
SELECT * FROM t1 ORDER BY C1,C2;

#
# Testing update.
# Note that we have a condition on a column that is not part of the index for
# the table. The right row should be updated nevertheless.
#
connection master;
UPDATE t1 SET C2 = 'I' WHERE C1 = 'A' AND C2 = 'C';
SELECT * FROM t1 ORDER BY C1,C2;
sync_slave_with_master;
SELECT * FROM t1 ORDER BY C1,C2;

# Testing update with a condition that does not match any rows, but
# which has a match for the index.
connection master;
UPDATE t1 SET c2 = 'Q' WHERE c1 = 'A' AND c2 = 'N';
SELECT * FROM t1 ORDER BY c1,c2;
sync_slave_with_master;
SELECT * FROM t1 ORDER BY c1,c2;

#
# Testing table with primary key
#
connection master;
eval CREATE TABLE t2 (c1 INT, c12 char(1), c2 INT, PRIMARY KEY (c1)) ENGINE = $type ;
INSERT INTO t2
  VALUES (1,'A',2),  (2,'A',4),  (3,'A',9),  (4,'A',15), (5,'A',25),
         (6,'A',35), (7,'A',50), (8,'A',64), (9,'A',81);
SELECT * FROM t2 ORDER BY c1,c2;
SELECT * FROM t2 WHERE c2 = c1 * c1 ORDER BY c1,c2;
sync_slave_with_master;
SELECT * FROM t2 ORDER BY c1,c2;
SELECT * FROM t2 WHERE c2 = c1 * c1 ORDER BY c1,c2;

connection master;
UPDATE t2 SET c2 = c1*c1 WHERE c2 != c1*c1;
SELECT * FROM t2 WHERE c2 = c1 * c1 ORDER BY c1,c2;
sync_slave_with_master;
SELECT * FROM t2 WHERE c2 = c1 * c1 ORDER BY c1,c2;

# Testing update with a condition that does not match any rows, but
# which has a match for the primary key.
connection master;
UPDATE t2 SET c12 = 'Q' WHERE c1 = 1 AND c2 = 999;
SELECT * FROM t2 ORDER BY c1,c2;
sync_slave_with_master;
SELECT * FROM t2 ORDER BY c1,c2;

connection master;
DELETE FROM t2 WHERE c1 % 4 = 0;
SELECT * FROM t2 ORDER BY c1,c2;
sync_slave_with_master;
SELECT * FROM t2 ORDER BY c1,c2;

connection master;
UPDATE t2 SET c12='X';

#
# Testing table with a multi-column primary key.
#
connection master;
eval CREATE TABLE t3 (C1 CHAR(1), C2 CHAR(1), pk1 INT, C3 CHAR(1), pk2 INT, PRIMARY KEY (pk1,pk2)) ENGINE = $type ;

INSERT INTO t3 VALUES ('A','B',1,'B',1), ('X','Y',2,'B',1), ('X','X',3,'B',1);
INSERT INTO t3 VALUES ('A','C',1,'B',2), ('X','Z',2,'B',2), ('A','A',3,'B',2);
SELECT * FROM t3 ORDER BY C1,C2;
sync_slave_with_master;
SELECT * FROM t3 ORDER BY C1,C2;

connection master;
DELETE FROM t3 WHERE C1 = C2;
SELECT * FROM t3 ORDER BY C1,C2;
sync_slave_with_master;
SELECT * FROM t3 ORDER BY C1,C2;

connection master;
UPDATE t3 SET C2 = 'I' WHERE C1 = 'A' AND C2 = 'C';
SELECT * FROM t3 ORDER BY C1,C2;
sync_slave_with_master;
SELECT * FROM t3 ORDER BY C1,C2;

#
# Testing table without index or primary key
#
connection master;
eval CREATE TABLE t6 (C1 CHAR(1), C2 CHAR(1), C3 INT$extra_index_t6) ENGINE = $type;

# Testing insert
INSERT INTO t6 VALUES ('A','B',1), ('X','Y',2), ('X','X',3);
INSERT INTO t6 VALUES ('A','C',4), ('X','Z',5), ('A','A',6);
SELECT * FROM t6 ORDER BY C3;
sync_slave_with_master;
SELECT * FROM t6 ORDER BY C3;

# Testing delete
# Observe that are several rows having the value for the index but only one
# should be deleted.
connection master;
DELETE FROM t6 WHERE C1 = C2;
SELECT * FROM t6 ORDER BY C3;
sync_slave_with_master;
SELECT * FROM t6 ORDER BY C3;

#
# Testing update.
# Note that we have a condition on a column that is not part of the index for
# the table. The right row should be updated nevertheless.
#
connection master;
UPDATE t6 SET C2 = 'I' WHERE C1 = 'A' AND C2 = 'C';
SELECT * FROM t6 ORDER BY C3;
sync_slave_with_master;
SELECT * FROM t6 ORDER BY C3;

# now mixing the 3 tables without begin/commit
connection master;
eval CREATE TABLE t5 (C1 CHAR(1), C2 CHAR(1), C3 INT PRIMARY KEY) ENGINE = $type ;
INSERT INTO t5 VALUES ('A','B',1), ('X','Y',2), ('X','X',3);
INSERT INTO t5 VALUES ('A','C',4), ('X','Z',5), ('A','A',6);

UPDATE t5,t2,t3 SET t5.C2='Q', t2.c12='R', t3.C3 ='S' WHERE t5.C1 = t2.c12 AND t5.C1 = t3.C1;
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;
sync_slave_with_master;
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;

#
# Testing special column types
#

160 161
if (`select char_length('$bit_field_special') > 0`) {
  connection slave;
162
  SET @saved_slave_type_conversions = @@SLAVE_TYPE_CONVERSIONS;
163 164 165
  eval SET GLOBAL SLAVE_TYPE_CONVERSIONS = '$bit_field_special';
}

166 167 168 169 170 171 172 173
connection master;
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 ;

INSERT INTO t4 SET C1 = 1;
SELECT C1,HEX(B1),HEX(B2) FROM t4 ORDER BY C1;
sync_slave_with_master;
SELECT C1,HEX(B1),HEX(B2) FROM t4 ORDER BY C1;

174 175 176 177
if (`select char_length('$bit_field_special') > 0`) {
  SET GLOBAL SLAVE_TYPE_CONVERSIONS = @saved_slave_type_conversions;
}

178 179 180 181 182 183
#
# Testing conflicting operations
#
connection master;
eval CREATE TABLE t7 (C1 INT PRIMARY KEY, C2 INT) ENGINE = $type ;
sync_slave_with_master;
184
--echo --- on slave: original values ---
185 186 187
INSERT INTO t7 VALUES (1,3), (2,6), (3,9);
SELECT * FROM t7 ORDER BY C1;

188 189 190 191 192
# since bug#31552/31609 idempotency is not default any longer. In order
# the preceeding test INSERT INTO t7 to pass the mode is switched
# temprorarily
set @@global.slave_exec_mode= 'IDEMPOTENT';

193
connection master;
194
--echo --- on master: new values inserted ---
195 196 197
INSERT INTO t7 VALUES (1,2), (2,4), (3,6);
SELECT * FROM t7 ORDER BY C1;
sync_slave_with_master;
198 199

set @@global.slave_exec_mode= default;
200
--echo --- on slave: old values should be overwritten by replicated values ---
201 202 203 204 205 206 207
SELECT * FROM t7 ORDER BY C1;

#
# A more complicated test where the table has several keys and we are
# causing a conflict for a key that is not "last".
#
connection master;
208 209
--echo --- on master ---
eval CREATE TABLE t8 (a INT PRIMARY KEY, b INT UNIQUE, c INT UNIQUE) ENGINE = $type ;
210 211

# First we make sure that the constraints are correctly set.
212
INSERT INTO t8 VALUES (99,99,99);
213
--error ER_DUP_ENTRY
214
INSERT INTO t8 VALUES (99,22,33);
215
--error ER_DUP_ENTRY
216
INSERT INTO t8 VALUES (11,99,33);
217
--error ER_DUP_ENTRY
218 219
INSERT INTO t8 VALUES (11,22,99);
SELECT * FROM t8 ORDER BY a;
220 221

sync_slave_with_master;
222 223 224 225
--echo --- on slave ---
SELECT * FROM t8 ORDER BY a;
INSERT INTO t8 VALUES (1,2,3), (2,4,6), (3,6,9);
SELECT * FROM t8 ORDER BY a;
226

227 228 229 230 231
# since bug#31552/31609 idempotency is not default any longer. In order
# the preceeding test INSERT INTO t8 to pass the mode is switched
# temprorarily
set @@global.slave_exec_mode= 'IDEMPOTENT';

232
connection master;
233
--echo --- on master ---
234 235
# We insert a row that will cause conflict on the primary key but not
# on the other keys.
236
INSERT INTO t8 VALUES (2,4,8);
237
sync_slave_with_master;
238 239
set @@global.slave_exec_mode= default;

240 241
--echo --- on slave ---
SELECT * FROM t8 ORDER BY a;
242

243 244 245 246 247 248 249 250 251 252 253 254
# BUG#31552: Replication breaks when deleting rows from out-of-sync
#            table without PK

--echo **** Test for BUG#31552 ****

--echo **** On Master ****
# Clean up t1 so that we can use it.
connection master;
DELETE FROM t1;
sync_slave_with_master;

# Just to get a clean binary log
255
--source include/rpl_reset.inc
256 257 258 259 260 261

--echo **** On Master ****
connection master;
INSERT INTO t1 VALUES ('K','K'), ('L','L'), ('M','M');
--echo **** On Master ****
sync_slave_with_master;
262 263 264 265
# since bug#31552/31609 idempotency is not default any longer. In order
# the following test DELETE FROM t1 to pass the mode is switched
# temprorarily
set @@global.slave_exec_mode= 'IDEMPOTENT';
266 267 268 269 270 271
DELETE FROM t1 WHERE C1 = 'L';

connection master;
DELETE FROM t1;
query_vertical SELECT COUNT(*) FROM t1 ORDER BY c1,c2;
sync_slave_with_master;
272
set @@global.slave_exec_mode= default;
273
source include/check_slave_is_running.inc;
274
query_vertical SELECT COUNT(*) FROM t1 ORDER BY c1,c2;
275

276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291
# BUG#37076: TIMESTAMP/DATETIME values are not replicated correctly
#            between machines with mixed endiannes
#            (regression test)

--echo **** Test for BUG#37076 ****
--echo **** On Master ****
connection master;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a TIMESTAMP, b DATETIME, c DATE);
INSERT INTO t1 VALUES(
  '2005-11-14 01:01:01', '2005-11-14 01:01:02', '2005-11-14');

--echo **** On Slave ****
sync_slave_with_master slave;
SELECT * FROM t1;

292 293 294 295 296
#
# cleanup
#

connection master;
297
DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8;
298
sync_slave_with_master;
299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319

#
# BUG#37426: RBR breaks for CHAR() UTF8 fields > 85 chars
#

# We have 4 combinations to test with respect to the field length
# (i.e., the number of bytes) of the CHAR fields:
#
# 1. Replicating from CHAR<256 to CHAR<256 
# 2. Replicating from CHAR<256 to CHAR>255
# 3. Replicating from CHAR>255 to CHAR<256
# 4. Replicating from CHAR>255 to CHAR>255

# We also make a special case of using the max size of a field on the
# master, i.e. CHAR(255) in UTF-8, giving another three cases.
#
# 5. Replicating UTF-8 CHAR(255) to CHAR(<256)
# 6. Replicating UTF-8 CHAR(255) to CHAR(>255)
# 7. Replicating UTF-8 CHAR(255) to CHAR(255) UTF-8

connection master;
320 321 322
eval CREATE TABLE t1 (i INT NOT NULL,
                      c CHAR(16) CHARACTER SET utf8 NOT NULL,
                      j INT NOT NULL) ENGINE = $type ;
323

324 325 326
eval CREATE TABLE t2 (i INT NOT NULL,
                      c CHAR(16) CHARACTER SET utf8 NOT NULL,
                      j INT NOT NULL) ENGINE = $type ;
327 328 329 330 331

sync_slave_with_master;
ALTER TABLE t2 MODIFY c CHAR(128) CHARACTER SET utf8 NOT NULL;

connection master;
332 333 334
eval CREATE TABLE t3 (i INT NOT NULL,
                      c CHAR(128) CHARACTER SET utf8 NOT NULL,
                      j INT NOT NULL) ENGINE = $type ;
335 336 337 338
sync_slave_with_master;
ALTER TABLE t3 MODIFY c CHAR(16) CHARACTER SET utf8 NOT NULL;

connection master;
339 340 341
eval CREATE TABLE t4 (i INT NOT NULL,
                      c CHAR(128) CHARACTER SET utf8 NOT NULL,
                      j INT NOT NULL) ENGINE = $type ;
342

343 344 345
eval CREATE TABLE t5 (i INT NOT NULL,
                      c CHAR(255) CHARACTER SET utf8 NOT NULL,
                      j INT NOT NULL) ENGINE = $type ;
346 347 348 349
sync_slave_with_master;
ALTER TABLE t5 MODIFY c CHAR(16) CHARACTER SET utf8 NOT NULL;

connection master;
350 351 352
eval CREATE TABLE t6 (i INT NOT NULL,
                      c CHAR(255) CHARACTER SET utf8 NOT NULL,
                      j INT NOT NULL) ENGINE = $type ;
353 354 355 356
sync_slave_with_master;
ALTER TABLE t6 MODIFY c CHAR(128) CHARACTER SET utf8 NOT NULL;

connection master;
357 358 359
eval CREATE TABLE t7 (i INT NOT NULL,
                      c CHAR(255) CHARACTER SET utf8 NOT NULL,
                      j INT NOT NULL) ENGINE = $type ;
360

361 362 363 364
connection slave;
SET @saved_slave_type_conversions = @@slave_type_conversions;
SET GLOBAL SLAVE_TYPE_CONVERSIONS = 'ALL_NON_LOSSY';

365 366 367 368
--echo [expecting slave to replicate correctly]
connection master;
INSERT INTO t1 VALUES (1, "", 1);
INSERT INTO t1 VALUES (2, repeat(_utf8'a', 16), 2);
369
sync_slave_with_master;
370

371
let $diff_tables= master:t1, slave:t1;
372 373 374 375 376 377
source include/diff_tables.inc;

--echo [expecting slave to replicate correctly]
connection master;
INSERT INTO t2 VALUES (1, "", 1);
INSERT INTO t2 VALUES (2, repeat(_utf8'a', 16), 2);
378
sync_slave_with_master;
379

380
let $diff_tables= master:t2, slave:t2;
381 382 383
source include/diff_tables.inc;

connection slave;
384
SET GLOBAL SLAVE_TYPE_CONVERSIONS = @saved_slave_type_conversions;
385 386
call mtr.add_suppression("Slave SQL.*Table definition on master and slave does not match: Column 1 size mismatch.* Error_code: 1535");
call mtr.add_suppression("Slave SQL.*Could not execute Delete_rows event on table test.t1.* Error_code: 1032");
387
call mtr.add_suppression("Slave SQL.*Column 1 of table .test.t.. cannot be converted from type.*, Error_code: 1677");
388

389 390
--let $rpl_only_running_threads= 1
--source include/rpl_reset.inc
391 392 393 394 395

--echo [expecting slave to replicate correctly]
connection master;
INSERT INTO t4 VALUES (1, "", 1);
INSERT INTO t4 VALUES (2, repeat(_utf8'a', 128), 2);
396
sync_slave_with_master;
397

398
let $diff_tables= master:t4, slave:t4;
399 400 401 402 403 404 405 406
source include/diff_tables.inc;

--echo [expecting slave to stop]
connection master;
INSERT INTO t5 VALUES (1, "", 1);
INSERT INTO t5 VALUES (2, repeat(_utf8'a', 255), 2);

connection slave;
's avatar
committed
407 408
# 1677 = ER_SLAVE_CONVERSION_FAILED 
--let $slave_sql_errno= 1677 
409 410
--let $show_slave_sql_error= 1
--source include/wait_for_slave_sql_error.inc
411 412

--source include/rpl_reset.inc
413 414 415 416 417 418 419

--echo [expecting slave to stop]
connection master;
INSERT INTO t6 VALUES (1, "", 1);
INSERT INTO t6 VALUES (2, repeat(_utf8'a', 255), 2);

connection slave;
's avatar
committed
420 421
# 1677 = ER_SLAVE_CONVERSION_FAILED 
--let $slave_sql_errno= 1677 
422 423
--let $show_slave_sql_error= 1
--source include/wait_for_slave_sql_error.inc
424 425

--source include/rpl_reset.inc
426 427 428 429 430

--echo [expecting slave to replicate correctly]
connection master;
INSERT INTO t7 VALUES (1, "", 1);
INSERT INTO t7 VALUES (2, repeat(_utf8'a', 255), 2);
431
sync_slave_with_master;
432

433
let $diff_tables= master:t7, slave:t7;
434 435 436 437 438 439
source include/diff_tables.inc;

connection master;
drop table t1, t2, t3, t4, t5, t6, t7;
sync_slave_with_master;

440 441 442 443 444 445 446 447 448 449 450 451 452
#
# BUG#32709: Assertion failed: trx_data->empty(), file .\log.cc, line 1293
#

connection master;
eval CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=$type;

INSERT INTO t1 VALUES (1), (2), (3);
--error ER_DUP_ENTRY
UPDATE t1 SET a = 10;
INSERT INTO t1 VALUES (4);
sync_slave_with_master;

453
let $diff_tables= master:t1, slave:t1;
454 455 456 457 458
source include/diff_tables.inc;

connection master;
drop table t1;
sync_slave_with_master;
459 460 461 462 463 464 465 466 467 468 469 470 471 472 473

#
# Bug #38230  Differences between master and slave after 
#             UPDATE or DELETE with LIMIT with pk
#
# the regression test verifies consistency via selecting

--disable_abort_on_error

--connection master

--disable_warnings
DROP TABLE IF EXISTS t1, t2;
--enable_warnings

Andrei Elkin's avatar
Andrei Elkin committed
474
eval CREATE TABLE t1 (
475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) NOT NULL,
  `int_key` int(11) NOT NULL,
  `date_key` date NOT NULL,
  `date_nokey` date NOT NULL,
  `time_key` time NOT NULL,
  `time_nokey` time NOT NULL,
  `datetime_key` datetime NOT NULL,
  `datetime_nokey` datetime NOT NULL,
  `varchar_key` varchar(1) NOT NULL,
  `varchar_nokey` varchar(1) NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `date_key` (`date_key`),
  KEY `time_key` (`time_key`),
  KEY `datetime_key` (`datetime_key`),
  KEY `varchar_key` (`varchar_key`)
Andrei Elkin's avatar
Andrei Elkin committed
492
) ENGINE=$type;
493 494 495

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');

Andrei Elkin's avatar
Andrei Elkin committed
496
eval CREATE TABLE t2 (
497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) NOT NULL,
  `int_key` int(11) NOT NULL,
  `date_key` date NOT NULL,
  `date_nokey` date NOT NULL,
  `time_key` time NOT NULL,
  `time_nokey` time NOT NULL,
  `datetime_key` datetime NOT NULL,
  `datetime_nokey` datetime NOT NULL,
  `varchar_key` varchar(1) NOT NULL,
  `varchar_nokey` varchar(1) NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `date_key` (`date_key`),
  KEY `time_key` (`time_key`),
  KEY `datetime_key` (`datetime_key`),
  KEY `varchar_key` (`varchar_key`)
Andrei Elkin's avatar
Andrei Elkin committed
514
) ENGINE=$type;
515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530

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');

DELETE FROM t2 WHERE `int_key` < 3 LIMIT 1;
UPDATE t1 SET `int_key` = 3 ORDER BY `pk` LIMIT 4;
DELETE FROM t2 WHERE `int_key` < 3 LIMIT 1;
DELETE FROM t2 WHERE `pk` < 6 LIMIT 1;
UPDATE t1 SET `int_key` = 6 ORDER BY `pk` LIMIT 3;
DELETE FROM t2 WHERE `pk` < 6 LIMIT 1;
UPDATE t1 SET `pk` = 6 ORDER BY `int_key` LIMIT 6;
DELETE FROM t2 WHERE `pk` < 7 LIMIT 1;
UPDATE t1 SET `int_key` = 4 ORDER BY `pk` LIMIT 6;

--sync_slave_with_master
--echo *** results: t2 must be consistent ****

531
let $diff_tables= master:t2, slave:t2;
532 533 534 535 536 537 538 539
source include/diff_tables.inc;

--connection master
DROP TABLE t1, t2;

--enable_abort_on_error

--echo EOF OF TESTS
540

541 542 543 544
#
# BUG#40004: Replication failure with no PK + no indexes
#

545 546 547 548
# The test cases are taken from the bug report. It is difficult to
# produce a test case that generates a HA_ERR_RECORD_DELETED, so we go
# with the test cases we have.

549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568
connection master;

eval CREATE TABLE t1 (a int) ENGINE=$type;

INSERT IGNORE INTO t1 VALUES (NULL);
INSERT INTO t1 ( a ) VALUES ( 0 );
INSERT INTO t1 ( a ) VALUES ( 9 );
INSERT INTO t1 ( a ) VALUES ( 2 );
INSERT INTO t1 ( a ) VALUES ( 9 );
INSERT INTO t1 ( a ) VALUES ( 5 );

UPDATE t1 SET a = 5 WHERE a = 9;
DELETE FROM t1 WHERE a < 6;
UPDATE t1 SET a = 9 WHERE a < 3;
INSERT INTO t1 ( a ) VALUES ( 3 );
UPDATE t1 SET a = 0 WHERE a < 4;
UPDATE t1 SET a = 8 WHERE a < 5;

sync_slave_with_master;

569
let $diff_tables= master:t1, slave:t1;
570 571 572 573 574 575 576 577 578 579
source include/diff_tables.inc;

connection master;
drop table t1;
sync_slave_with_master;

#
# Bug #39752: Replication failure on RBR + MyISAM + no PK
#

580 581 582 583
# The test cases are taken from the bug report. It is difficult to
# produce a test case that generates a HA_ERR_RECORD_DELETED, so we go
# with the test cases we have.

584 585
connection master;

586 587 588
# Since t1 contain a bit field, we have to do this trick to handle InnoDB
if (`select char_length('$bit_field_special') > 0`) {
  connection slave;
589
  SET @saved_slave_type_conversions = @@SLAVE_TYPE_CONVERSIONS;
590 591 592
  eval SET GLOBAL SLAVE_TYPE_CONVERSIONS = '$bit_field_special';
}

593
--disable_warnings
594
connection master;
595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638
eval CREATE TABLE t1 (a bit) ENGINE=$type;
INSERT IGNORE INTO t1 VALUES (NULL);
INSERT INTO t1 ( a ) VALUES ( 0 );
UPDATE t1 SET a = 0 WHERE a = 1 LIMIT 3;
INSERT INTO t1 ( a ) VALUES ( 5 );
DELETE FROM t1 WHERE a < 2 LIMIT 4;
DELETE FROM t1 WHERE a < 9 LIMIT 4;
INSERT INTO t1 ( a ) VALUES ( 9 );
UPDATE t1 SET a = 8 WHERE a = 0 LIMIT 6;
INSERT INTO t1 ( a ) VALUES ( 8 );
UPDATE t1 SET a = 0 WHERE a < 6 LIMIT 0;
INSERT INTO t1 ( a ) VALUES ( 4 );
INSERT INTO t1 ( a ) VALUES ( 3 );
UPDATE t1 SET a = 0 WHERE a = 7 LIMIT 6;
DELETE FROM t1 WHERE a = 4 LIMIT 7;
UPDATE t1 SET a = 9 WHERE a < 2 LIMIT 9;
UPDATE t1 SET a = 0 WHERE a < 9 LIMIT 2;
DELETE FROM t1 WHERE a < 0 LIMIT 5;
INSERT INTO t1 ( a ) VALUES ( 5 );
UPDATE t1 SET a = 4 WHERE a < 6 LIMIT 4;
INSERT INTO t1 ( a ) VALUES ( 5 );
UPDATE t1 SET a = 9 WHERE a < 5 LIMIT 8;
DELETE FROM t1 WHERE a < 8 LIMIT 8;
INSERT INTO t1 ( a ) VALUES ( 6 );
DELETE FROM t1 WHERE a < 6 LIMIT 7;
UPDATE t1 SET a = 7 WHERE a = 3 LIMIT 7;
UPDATE t1 SET a = 8 WHERE a = 0 LIMIT 6;
INSERT INTO t1 ( a ) VALUES ( 7 );
DELETE FROM t1 WHERE a < 9 LIMIT 4;
INSERT INTO t1 ( a ) VALUES ( 7 );
INSERT INTO t1 ( a ) VALUES ( 6 );
UPDATE t1 SET a = 8 WHERE a = 3 LIMIT 4;
DELETE FROM t1 WHERE a = 2 LIMIT 9;
DELETE FROM t1 WHERE a = 1 LIMIT 4;
UPDATE t1 SET a = 4 WHERE a = 2 LIMIT 7;
INSERT INTO t1 ( a ) VALUES ( 0 );
DELETE FROM t1 WHERE a < 3 LIMIT 0;
UPDATE t1 SET a = 8 WHERE a = 5 LIMIT 2;
INSERT INTO t1 ( a ) VALUES ( 1 );
UPDATE t1 SET a = 9 WHERE a < 5 LIMIT 3;
--enable_warnings

sync_slave_with_master;

639 640 641 642
if (`select char_length('$bit_field_special') > 0`) {
  SET GLOBAL SLAVE_TYPE_CONVERSIONS = @saved_slave_type_conversions;
}

643
let $diff_tables= master:t1, slave:t1;
644 645 646 647 648
source include/diff_tables.inc;

connection master;
drop table t1;
sync_slave_with_master;