1include/master-slave.inc 2[connection master] 3connection slave; 4connection slave; 5connection master; 6CREATE TABLE t1 (C1 CHAR(1), C2 CHAR(1), INDEX (C1)) ENGINE = 'INNODB' ; 7SELECT * FROM t1; 8C1 C2 9connection slave; 10SELECT * FROM t1; 11C1 C2 12connection master; 13INSERT INTO t1 VALUES ('A','B'), ('X','Y'), ('X','X'); 14INSERT INTO t1 VALUES ('A','C'), ('X','Z'), ('A','A'); 15SELECT * FROM t1 ORDER BY C1,C2; 16C1 C2 17A A 18A B 19A C 20X X 21X Y 22X Z 23connection slave; 24SELECT * FROM t1 ORDER BY C1,C2; 25C1 C2 26A A 27A B 28A C 29X X 30X Y 31X Z 32connection master; 33DELETE FROM t1 WHERE C1 = C2; 34SELECT * FROM t1 ORDER BY C1,C2; 35C1 C2 36A B 37A C 38X Y 39X Z 40connection slave; 41SELECT * FROM t1 ORDER BY C1,C2; 42C1 C2 43A B 44A C 45X Y 46X Z 47connection master; 48UPDATE t1 SET C2 = 'I' WHERE C1 = 'A' AND C2 = 'C'; 49SELECT * FROM t1 ORDER BY C1,C2; 50C1 C2 51A B 52A I 53X Y 54X Z 55connection slave; 56SELECT * FROM t1 ORDER BY C1,C2; 57C1 C2 58A B 59A I 60X Y 61X Z 62connection slave; 63include/assert.inc [Counter for COM_COMMIT is consistent with the number of actual commits] 64include/assert.inc [Counter for COM_INSERT is consistent with the number of actual inserts] 65include/assert.inc [Counter for COM_DELETE is consistent with the number of actual deletes] 66include/assert.inc [Counter for COM_UPDATE is consistent with the number of actual updates] 67connection master; 68UPDATE t1 SET c2 = 'Q' WHERE c1 = 'A' AND c2 = 'N'; 69SELECT * FROM t1 ORDER BY c1,c2; 70C1 C2 71A B 72A I 73X Y 74X Z 75connection slave; 76SELECT * FROM t1 ORDER BY c1,c2; 77C1 C2 78A B 79A I 80X Y 81X Z 82connection master; 83CREATE TABLE t2 (c1 INT, c12 char(1), c2 INT, PRIMARY KEY (c1)) ENGINE = 'INNODB' ; 84INSERT INTO t2 85VALUES (1,'A',2), (2,'A',4), (3,'A',9), (4,'A',15), (5,'A',25), 86(6,'A',35), (7,'A',50), (8,'A',64), (9,'A',81); 87SELECT * FROM t2 ORDER BY c1,c2; 88c1 c12 c2 891 A 2 902 A 4 913 A 9 924 A 15 935 A 25 946 A 35 957 A 50 968 A 64 979 A 81 98SELECT * FROM t2 WHERE c2 = c1 * c1 ORDER BY c1,c2; 99c1 c12 c2 1002 A 4 1013 A 9 1025 A 25 1038 A 64 1049 A 81 105connection slave; 106SELECT * FROM t2 ORDER BY c1,c2; 107c1 c12 c2 1081 A 2 1092 A 4 1103 A 9 1114 A 15 1125 A 25 1136 A 35 1147 A 50 1158 A 64 1169 A 81 117SELECT * FROM t2 WHERE c2 = c1 * c1 ORDER BY c1,c2; 118c1 c12 c2 1192 A 4 1203 A 9 1215 A 25 1228 A 64 1239 A 81 124connection master; 125UPDATE t2 SET c2 = c1*c1 WHERE c2 != c1*c1; 126SELECT * FROM t2 WHERE c2 = c1 * c1 ORDER BY c1,c2; 127c1 c12 c2 1281 A 1 1292 A 4 1303 A 9 1314 A 16 1325 A 25 1336 A 36 1347 A 49 1358 A 64 1369 A 81 137connection slave; 138SELECT * FROM t2 WHERE c2 = c1 * c1 ORDER BY c1,c2; 139c1 c12 c2 1401 A 1 1412 A 4 1423 A 9 1434 A 16 1445 A 25 1456 A 36 1467 A 49 1478 A 64 1489 A 81 149connection master; 150UPDATE t2 SET c12 = 'Q' WHERE c1 = 1 AND c2 = 999; 151SELECT * FROM t2 ORDER BY c1,c2; 152c1 c12 c2 1531 A 1 1542 A 4 1553 A 9 1564 A 16 1575 A 25 1586 A 36 1597 A 49 1608 A 64 1619 A 81 162connection slave; 163SELECT * FROM t2 ORDER BY c1,c2; 164c1 c12 c2 1651 A 1 1662 A 4 1673 A 9 1684 A 16 1695 A 25 1706 A 36 1717 A 49 1728 A 64 1739 A 81 174connection master; 175DELETE FROM t2 WHERE c1 % 4 = 0; 176SELECT * FROM t2 ORDER BY c1,c2; 177c1 c12 c2 1781 A 1 1792 A 4 1803 A 9 1815 A 25 1826 A 36 1837 A 49 1849 A 81 185connection slave; 186SELECT * FROM t2 ORDER BY c1,c2; 187c1 c12 c2 1881 A 1 1892 A 4 1903 A 9 1915 A 25 1926 A 36 1937 A 49 1949 A 81 195connection master; 196UPDATE t2 SET c12='X'; 197connection master; 198CREATE TABLE t3 (C1 CHAR(1), C2 CHAR(1), pk1 INT, C3 CHAR(1), pk2 INT, PRIMARY KEY (pk1,pk2)) ENGINE = 'INNODB' ; 199INSERT INTO t3 VALUES ('A','B',1,'B',1), ('X','Y',2,'B',1), ('X','X',3,'B',1); 200INSERT INTO t3 VALUES ('A','C',1,'B',2), ('X','Z',2,'B',2), ('A','A',3,'B',2); 201SELECT * FROM t3 ORDER BY C1,C2; 202C1 C2 pk1 C3 pk2 203A A 3 B 2 204A B 1 B 1 205A C 1 B 2 206X X 3 B 1 207X Y 2 B 1 208X Z 2 B 2 209connection slave; 210SELECT * FROM t3 ORDER BY C1,C2; 211C1 C2 pk1 C3 pk2 212A A 3 B 2 213A B 1 B 1 214A C 1 B 2 215X X 3 B 1 216X Y 2 B 1 217X Z 2 B 2 218connection master; 219DELETE FROM t3 WHERE C1 = C2; 220SELECT * FROM t3 ORDER BY C1,C2; 221C1 C2 pk1 C3 pk2 222A B 1 B 1 223A C 1 B 2 224X Y 2 B 1 225X Z 2 B 2 226connection slave; 227SELECT * FROM t3 ORDER BY C1,C2; 228C1 C2 pk1 C3 pk2 229A B 1 B 1 230A C 1 B 2 231X Y 2 B 1 232X Z 2 B 2 233connection master; 234UPDATE t3 SET C2 = 'I' WHERE C1 = 'A' AND C2 = 'C'; 235SELECT * FROM t3 ORDER BY C1,C2; 236C1 C2 pk1 C3 pk2 237A B 1 B 1 238A I 1 B 2 239X Y 2 B 1 240X Z 2 B 2 241connection slave; 242SELECT * FROM t3 ORDER BY C1,C2; 243C1 C2 pk1 C3 pk2 244A B 1 B 1 245A I 1 B 2 246X Y 2 B 1 247X Z 2 B 2 248connection master; 249CREATE TABLE t6 (C1 CHAR(1), C2 CHAR(1), C3 INT) ENGINE = 'INNODB' ; 250INSERT INTO t6 VALUES ('A','B',1), ('X','Y',2), ('X','X',3); 251INSERT INTO t6 VALUES ('A','C',4), ('X','Z',5), ('A','A',6); 252SELECT * FROM t6 ORDER BY C3; 253C1 C2 C3 254A B 1 255X Y 2 256X X 3 257A C 4 258X Z 5 259A A 6 260connection slave; 261SELECT * FROM t6 ORDER BY C3; 262C1 C2 C3 263A B 1 264X Y 2 265X X 3 266A C 4 267X Z 5 268A A 6 269connection master; 270DELETE FROM t6 WHERE C1 = C2; 271SELECT * FROM t6 ORDER BY C3; 272C1 C2 C3 273A B 1 274X Y 2 275A C 4 276X Z 5 277connection slave; 278SELECT * FROM t6 ORDER BY C3; 279C1 C2 C3 280A B 1 281X Y 2 282A C 4 283X Z 5 284connection master; 285UPDATE t6 SET C2 = 'I' WHERE C1 = 'A' AND C2 = 'C'; 286SELECT * FROM t6 ORDER BY C3; 287C1 C2 C3 288A B 1 289X Y 2 290A I 4 291X Z 5 292connection slave; 293SELECT * FROM t6 ORDER BY C3; 294C1 C2 C3 295A B 1 296X Y 2 297A I 4 298X Z 5 299connection master; 300CREATE TABLE t5 (C1 CHAR(1), C2 CHAR(1), C3 INT PRIMARY KEY) ENGINE = 'INNODB' ; 301INSERT INTO t5 VALUES ('A','B',1), ('X','Y',2), ('X','X',3); 302INSERT INTO t5 VALUES ('A','C',4), ('X','Z',5), ('A','A',6); 303UPDATE t5,t2,t3 SET t5.C2='Q', t2.c12='R', t3.C3 ='S' WHERE t5.C1 = t2.c12 AND t5.C1 = t3.C1; 304SELECT * 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; 305C1 C2 C3 c1 c12 c2 C1 C2 pk1 C3 pk2 306X Q 2 1 R 1 X Y 2 S 1 307X Q 2 1 R 1 X Z 2 S 2 308X Q 2 2 R 4 X Y 2 S 1 309X Q 2 2 R 4 X Z 2 S 2 310X Q 2 3 R 9 X Y 2 S 1 311X Q 2 3 R 9 X Z 2 S 2 312X Q 2 5 R 25 X Y 2 S 1 313X Q 2 5 R 25 X Z 2 S 2 314X Q 2 6 R 36 X Y 2 S 1 315X Q 2 6 R 36 X Z 2 S 2 316X Q 2 7 R 49 X Y 2 S 1 317X Q 2 7 R 49 X Z 2 S 2 318X Q 2 9 R 81 X Y 2 S 1 319X Q 2 9 R 81 X Z 2 S 2 320X Q 3 1 R 1 X Y 2 S 1 321X Q 3 1 R 1 X Z 2 S 2 322X Q 3 2 R 4 X Y 2 S 1 323X Q 3 2 R 4 X Z 2 S 2 324X Q 3 3 R 9 X Y 2 S 1 325X Q 3 3 R 9 X Z 2 S 2 326X Q 3 5 R 25 X Y 2 S 1 327X Q 3 5 R 25 X Z 2 S 2 328X Q 3 6 R 36 X Y 2 S 1 329X Q 3 6 R 36 X Z 2 S 2 330X Q 3 7 R 49 X Y 2 S 1 331X Q 3 7 R 49 X Z 2 S 2 332X Q 3 9 R 81 X Y 2 S 1 333X Q 3 9 R 81 X Z 2 S 2 334X Q 5 1 R 1 X Y 2 S 1 335X Q 5 1 R 1 X Z 2 S 2 336X Q 5 2 R 4 X Y 2 S 1 337X Q 5 2 R 4 X Z 2 S 2 338X Q 5 3 R 9 X Y 2 S 1 339X Q 5 3 R 9 X Z 2 S 2 340X Q 5 5 R 25 X Y 2 S 1 341X Q 5 5 R 25 X Z 2 S 2 342X Q 5 6 R 36 X Y 2 S 1 343X Q 5 6 R 36 X Z 2 S 2 344X Q 5 7 R 49 X Y 2 S 1 345X Q 5 7 R 49 X Z 2 S 2 346X Q 5 9 R 81 X Y 2 S 1 347X Q 5 9 R 81 X Z 2 S 2 348connection slave; 349SELECT * 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; 350C1 C2 C3 c1 c12 c2 C1 C2 pk1 C3 pk2 351X Q 2 1 R 1 X Y 2 S 1 352X Q 2 1 R 1 X Z 2 S 2 353X Q 2 2 R 4 X Y 2 S 1 354X Q 2 2 R 4 X Z 2 S 2 355X Q 2 3 R 9 X Y 2 S 1 356X Q 2 3 R 9 X Z 2 S 2 357X Q 2 5 R 25 X Y 2 S 1 358X Q 2 5 R 25 X Z 2 S 2 359X Q 2 6 R 36 X Y 2 S 1 360X Q 2 6 R 36 X Z 2 S 2 361X Q 2 7 R 49 X Y 2 S 1 362X Q 2 7 R 49 X Z 2 S 2 363X Q 2 9 R 81 X Y 2 S 1 364X Q 2 9 R 81 X Z 2 S 2 365X Q 3 1 R 1 X Y 2 S 1 366X Q 3 1 R 1 X Z 2 S 2 367X Q 3 2 R 4 X Y 2 S 1 368X Q 3 2 R 4 X Z 2 S 2 369X Q 3 3 R 9 X Y 2 S 1 370X Q 3 3 R 9 X Z 2 S 2 371X Q 3 5 R 25 X Y 2 S 1 372X Q 3 5 R 25 X Z 2 S 2 373X Q 3 6 R 36 X Y 2 S 1 374X Q 3 6 R 36 X Z 2 S 2 375X Q 3 7 R 49 X Y 2 S 1 376X Q 3 7 R 49 X Z 2 S 2 377X Q 3 9 R 81 X Y 2 S 1 378X Q 3 9 R 81 X Z 2 S 2 379X Q 5 1 R 1 X Y 2 S 1 380X Q 5 1 R 1 X Z 2 S 2 381X Q 5 2 R 4 X Y 2 S 1 382X Q 5 2 R 4 X Z 2 S 2 383X Q 5 3 R 9 X Y 2 S 1 384X Q 5 3 R 9 X Z 2 S 2 385X Q 5 5 R 25 X Y 2 S 1 386X Q 5 5 R 25 X Z 2 S 2 387X Q 5 6 R 36 X Y 2 S 1 388X Q 5 6 R 36 X Z 2 S 2 389X Q 5 7 R 49 X Y 2 S 1 390X Q 5 7 R 49 X Z 2 S 2 391X Q 5 9 R 81 X Y 2 S 1 392X Q 5 9 R 81 X Z 2 S 2 393connection slave; 394SET @saved_slave_type_conversions = @@SLAVE_TYPE_CONVERSIONS; 395SET GLOBAL SLAVE_TYPE_CONVERSIONS = 'ALL_LOSSY'; 396connection master; 397CREATE 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 = 'INNODB' ; 398INSERT INTO t4 SET C1 = 1; 399SELECT C1,HEX(B1),HEX(B2) FROM t4 ORDER BY C1; 400C1 HEX(B1) HEX(B2) 4011 NULL 0 402connection slave; 403SELECT C1,HEX(B1),HEX(B2) FROM t4 ORDER BY C1; 404C1 HEX(B1) HEX(B2) 4051 NULL 0 406SET GLOBAL SLAVE_TYPE_CONVERSIONS = @saved_slave_type_conversions; 407connection master; 408CREATE TABLE t7 (C1 INT PRIMARY KEY, C2 INT) ENGINE = 'INNODB' ; 409connection slave; 410--- original values --- 411INSERT INTO t7 VALUES (1,3), (2,6), (3,9); 412SELECT * FROM t7 ORDER BY C1; 413C1 C2 4141 3 4152 6 4163 9 417set @@global.slave_exec_mode= 'IDEMPOTENT'; 418connection master; 419--- new values inserted --- 420INSERT INTO t7 VALUES (1,2), (2,4), (3,6); 421SELECT * FROM t7 ORDER BY C1; 422C1 C2 4231 2 4242 4 4253 6 426connection slave; 427set @@global.slave_exec_mode= default; 428--- old values should be overwritten by replicated values --- 429SELECT * FROM t7 ORDER BY C1; 430C1 C2 4311 2 4322 4 4333 6 434connection master; 435CREATE TABLE t8 (a INT PRIMARY KEY, b INT UNIQUE, c INT UNIQUE) ENGINE = 'INNODB' ; 436INSERT INTO t8 VALUES (99,99,99); 437INSERT INTO t8 VALUES (99,22,33); 438ERROR 23000: Duplicate entry '99' for key 'PRIMARY' 439INSERT INTO t8 VALUES (11,99,33); 440ERROR 23000: Duplicate entry '99' for key 'b' 441INSERT INTO t8 VALUES (11,22,99); 442ERROR 23000: Duplicate entry '99' for key 'c' 443SELECT * FROM t8 ORDER BY a; 444a b c 44599 99 99 446connection slave; 447SELECT * FROM t8 ORDER BY a; 448a b c 44999 99 99 450INSERT INTO t8 VALUES (1,2,3), (2,4,6), (3,6,9); 451SELECT * FROM t8 ORDER BY a; 452a b c 4531 2 3 4542 4 6 4553 6 9 45699 99 99 457set @@global.slave_exec_mode= 'IDEMPOTENT'; 458connection master; 459INSERT INTO t8 VALUES (2,4,8); 460connection slave; 461set @@global.slave_exec_mode= default; 462SELECT * FROM t8 ORDER BY a; 463a b c 4641 2 3 4652 4 8 4663 6 9 46799 99 99 468**** Test for BUG#31552 **** 469connection master; 470DELETE FROM t1; 471connection slave; 472include/rpl_reset.inc 473connection master; 474INSERT INTO t1 VALUES ('K','K'), ('L','L'), ('M','M'); 475connection slave; 476set @@global.slave_exec_mode= 'IDEMPOTENT'; 477DELETE FROM t1 WHERE C1 = 'L'; 478connection master; 479DELETE FROM t1; 480SELECT COUNT(*) FROM t1 ORDER BY c1,c2; 481COUNT(*) 0 482connection slave; 483set @@global.slave_exec_mode= default; 484include/check_slave_is_running.inc 485SELECT COUNT(*) FROM t1 ORDER BY c1,c2; 486COUNT(*) 0 487**** Test for BUG#37076 **** 488connection master; 489DROP TABLE IF EXISTS t1; 490CREATE TABLE t1 (a TIMESTAMP, b DATETIME, c DATE); 491INSERT INTO t1 VALUES( 492'2005-11-14 01:01:01', '2005-11-14 01:01:02', '2005-11-14'); 493connection slave; 494SELECT * FROM t1; 495a b c 4962005-11-14 01:01:01 2005-11-14 01:01:02 2005-11-14 497connection master; 498DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8; 499connection slave; 500connection master; 501CREATE TABLE t1 (i INT NOT NULL, 502c CHAR(16) CHARACTER SET utf8 NOT NULL, 503j INT NOT NULL) ENGINE = 'INNODB' ; 504CREATE TABLE t2 (i INT NOT NULL, 505c CHAR(16) CHARACTER SET utf8 NOT NULL, 506j INT NOT NULL) ENGINE = 'INNODB' ; 507connection slave; 508ALTER TABLE t2 MODIFY c CHAR(128) CHARACTER SET utf8 NOT NULL; 509connection master; 510CREATE TABLE t3 (i INT NOT NULL, 511c CHAR(128) CHARACTER SET utf8 NOT NULL, 512j INT NOT NULL) ENGINE = 'INNODB' ; 513connection slave; 514ALTER TABLE t3 MODIFY c CHAR(16) CHARACTER SET utf8 NOT NULL; 515connection master; 516CREATE TABLE t4 (i INT NOT NULL, 517c CHAR(128) CHARACTER SET utf8 NOT NULL, 518j INT NOT NULL) ENGINE = 'INNODB' ; 519CREATE TABLE t5 (i INT NOT NULL, 520c CHAR(255) CHARACTER SET utf8 NOT NULL, 521j INT NOT NULL) ENGINE = 'INNODB' ; 522connection slave; 523ALTER TABLE t5 MODIFY c CHAR(16) CHARACTER SET utf8 NOT NULL; 524connection master; 525CREATE TABLE t6 (i INT NOT NULL, 526c CHAR(255) CHARACTER SET utf8 NOT NULL, 527j INT NOT NULL) ENGINE = 'INNODB' ; 528connection slave; 529ALTER TABLE t6 MODIFY c CHAR(128) CHARACTER SET utf8 NOT NULL; 530connection master; 531CREATE TABLE t7 (i INT NOT NULL, 532c CHAR(255) CHARACTER SET utf8 NOT NULL, 533j INT NOT NULL) ENGINE = 'INNODB' ; 534connection slave; 535SET @saved_slave_type_conversions = @@slave_type_conversions; 536SET GLOBAL SLAVE_TYPE_CONVERSIONS = 'ALL_NON_LOSSY'; 537[expecting slave to replicate correctly] 538connection master; 539INSERT INTO t1 VALUES (1, "", 1); 540INSERT INTO t1 VALUES (2, repeat(_utf8'a', 16), 2); 541connection slave; 542include/diff_tables.inc [master:t1, slave:t1] 543[expecting slave to replicate correctly] 544connection master; 545INSERT INTO t2 VALUES (1, "", 1); 546INSERT INTO t2 VALUES (2, repeat(_utf8'a', 16), 2); 547connection slave; 548include/diff_tables.inc [master:t2, slave:t2] 549connection slave; 550SET GLOBAL SLAVE_TYPE_CONVERSIONS = @saved_slave_type_conversions; 551call mtr.add_suppression("Slave SQL.*Table definition on master and slave does not match: Column 1 size mismatch.* error.* 1535"); 552call mtr.add_suppression("Slave SQL.*Could not execute Delete_rows event on table test.t1.* error.* 1032"); 553call mtr.add_suppression("Slave SQL.*Column 1 of table .test.t.. cannot be converted from type.*, error.* 1677"); 554call mtr.add_suppression("Can't find record in 't1'"); 555include/rpl_reset.inc 556[expecting slave to replicate correctly] 557connection master; 558INSERT INTO t4 VALUES (1, "", 1); 559INSERT INTO t4 VALUES (2, repeat(_utf8'a', 128), 2); 560connection slave; 561include/diff_tables.inc [master:t4, slave:t4] 562[expecting slave to stop] 563connection master; 564INSERT INTO t5 VALUES (1, "", 1); 565INSERT INTO t5 VALUES (2, repeat(_utf8'a', 255), 2); 566connection slave; 567include/wait_for_slave_sql_error.inc [errno=1677] 568Last_SQL_Error = 'Column 1 of table 'test.t5' cannot be converted from type 'char(765 octets)' to type 'char(48 octets) character set utf8'' 569include/rpl_reset.inc 570[expecting slave to stop] 571connection master; 572INSERT INTO t6 VALUES (1, "", 1); 573INSERT INTO t6 VALUES (2, repeat(_utf8'a', 255), 2); 574connection slave; 575include/wait_for_slave_sql_error.inc [errno=1677] 576Last_SQL_Error = 'Column 1 of table 'test.t6' cannot be converted from type 'char(765 octets)' to type 'char(384 octets) character set utf8'' 577include/rpl_reset.inc 578[expecting slave to replicate correctly] 579connection master; 580INSERT INTO t7 VALUES (1, "", 1); 581INSERT INTO t7 VALUES (2, repeat(_utf8'a', 255), 2); 582connection slave; 583include/diff_tables.inc [master:t7, slave:t7] 584connection master; 585drop table t1, t2, t3, t4, t5, t6, t7; 586connection slave; 587connection master; 588CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE='INNODB' ; 589INSERT INTO t1 VALUES (1), (2), (3); 590UPDATE t1 SET a = 10; 591ERROR 23000: Duplicate entry '10' for key 'PRIMARY' 592INSERT INTO t1 VALUES (4); 593connection slave; 594include/diff_tables.inc [master:t1, slave:t1] 595connection master; 596drop table t1; 597connection slave; 598connection master; 599DROP TABLE IF EXISTS t1, t2; 600CREATE TABLE t1 ( 601`pk` int(11) NOT NULL AUTO_INCREMENT, 602`int_nokey` int(11) NOT NULL, 603`int_key` int(11) NOT NULL, 604`date_key` date NOT NULL, 605`date_nokey` date NOT NULL, 606`time_key` time NOT NULL, 607`time_nokey` time NOT NULL, 608`datetime_key` datetime NOT NULL, 609`datetime_nokey` datetime NOT NULL, 610`varchar_key` varchar(1) NOT NULL, 611`varchar_nokey` varchar(1) NOT NULL, 612PRIMARY KEY (`pk`), 613KEY `int_key` (`int_key`), 614KEY `date_key` (`date_key`), 615KEY `time_key` (`time_key`), 616KEY `datetime_key` (`datetime_key`), 617KEY `varchar_key` (`varchar_key`) 618) ENGINE='INNODB' ; 619INSERT 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'); 620CREATE TABLE t2 ( 621`pk` int(11) NOT NULL AUTO_INCREMENT, 622`int_nokey` int(11) NOT NULL, 623`int_key` int(11) NOT NULL, 624`date_key` date NOT NULL, 625`date_nokey` date NOT NULL, 626`time_key` time NOT NULL, 627`time_nokey` time NOT NULL, 628`datetime_key` datetime NOT NULL, 629`datetime_nokey` datetime NOT NULL, 630`varchar_key` varchar(1) NOT NULL, 631`varchar_nokey` varchar(1) NOT NULL, 632PRIMARY KEY (`pk`), 633KEY `int_key` (`int_key`), 634KEY `date_key` (`date_key`), 635KEY `time_key` (`time_key`), 636KEY `datetime_key` (`datetime_key`), 637KEY `varchar_key` (`varchar_key`) 638) ENGINE='INNODB' ; 639INSERT 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'); 640DELETE FROM t2 WHERE `int_key` < 3 LIMIT 1; 641UPDATE t1 SET `int_key` = 3 ORDER BY `pk` LIMIT 4; 642DELETE FROM t2 WHERE `int_key` < 3 LIMIT 1; 643DELETE FROM t2 WHERE `pk` < 6 LIMIT 1; 644UPDATE t1 SET `int_key` = 6 ORDER BY `pk` LIMIT 3; 645DELETE FROM t2 WHERE `pk` < 6 LIMIT 1; 646UPDATE t1 SET `pk` = 6 ORDER BY `int_key` LIMIT 6; 647ERROR 23000: Duplicate entry '6' for key 'PRIMARY' 648DELETE FROM t2 WHERE `pk` < 7 LIMIT 1; 649UPDATE t1 SET `int_key` = 4 ORDER BY `pk` LIMIT 6; 650connection slave; 651*** results: t2 must be consistent **** 652include/diff_tables.inc [master:t2, slave:t2] 653connection master; 654DROP TABLE t1, t2; 655EOF OF TESTS 656connection master; 657CREATE TABLE t1 (a int) ENGINE='INNODB' ; 658INSERT IGNORE INTO t1 VALUES (NULL); 659INSERT INTO t1 ( a ) VALUES ( 0 ); 660INSERT INTO t1 ( a ) VALUES ( 9 ); 661INSERT INTO t1 ( a ) VALUES ( 2 ); 662INSERT INTO t1 ( a ) VALUES ( 9 ); 663INSERT INTO t1 ( a ) VALUES ( 5 ); 664UPDATE t1 SET a = 5 WHERE a = 9; 665DELETE FROM t1 WHERE a < 6; 666UPDATE t1 SET a = 9 WHERE a < 3; 667INSERT INTO t1 ( a ) VALUES ( 3 ); 668UPDATE t1 SET a = 0 WHERE a < 4; 669UPDATE t1 SET a = 8 WHERE a < 5; 670connection slave; 671include/diff_tables.inc [master:t1, slave:t1] 672connection master; 673drop table t1; 674connection slave; 675connection master; 676connection slave; 677SET @saved_slave_type_conversions = @@SLAVE_TYPE_CONVERSIONS; 678SET GLOBAL SLAVE_TYPE_CONVERSIONS = 'ALL_LOSSY'; 679connection master; 680CREATE TABLE t1 (a bit) ENGINE='INNODB' ; 681INSERT IGNORE INTO t1 VALUES (NULL); 682INSERT INTO t1 ( a ) VALUES ( 0 ); 683UPDATE t1 SET a = 0 WHERE a = 1 LIMIT 3; 684INSERT IGNORE INTO t1 ( a ) VALUES ( 5 ); 685DELETE FROM t1 WHERE a < 2 LIMIT 4; 686DELETE FROM t1 WHERE a < 9 LIMIT 4; 687INSERT IGNORE INTO t1 ( a ) VALUES ( 9 ); 688UPDATE t1 SET a = 8 WHERE a = 0 LIMIT 6; 689INSERT IGNORE INTO t1 ( a ) VALUES ( 8 ); 690UPDATE t1 SET a = 0 WHERE a < 6 LIMIT 0; 691INSERT IGNORE INTO t1 ( a ) VALUES ( 4 ); 692INSERT IGNORE INTO t1 ( a ) VALUES ( 3 ); 693UPDATE t1 SET a = 0 WHERE a = 7 LIMIT 6; 694DELETE FROM t1 WHERE a = 4 LIMIT 7; 695UPDATE IGNORE t1 SET a = 9 WHERE a < 2 LIMIT 9; 696UPDATE t1 SET a = 0 WHERE a < 9 LIMIT 2; 697DELETE FROM t1 WHERE a < 0 LIMIT 5; 698INSERT IGNORE INTO t1 ( a ) VALUES ( 5 ); 699UPDATE IGNORE t1 SET a = 4 WHERE a < 6 LIMIT 4; 700INSERT IGNORE INTO t1 ( a ) VALUES ( 5 ); 701UPDATE IGNORE t1 SET a = 9 WHERE a < 5 LIMIT 8; 702DELETE FROM t1 WHERE a < 8 LIMIT 8; 703INSERT IGNORE INTO t1 ( a ) VALUES ( 6 ); 704DELETE FROM t1 WHERE a < 6 LIMIT 7; 705UPDATE t1 SET a = 7 WHERE a = 3 LIMIT 7; 706UPDATE t1 SET a = 8 WHERE a = 0 LIMIT 6; 707INSERT IGNORE INTO t1 ( a ) VALUES ( 7 ); 708DELETE FROM t1 WHERE a < 9 LIMIT 4; 709INSERT IGNORE INTO t1 ( a ) VALUES ( 7 ); 710INSERT IGNORE INTO t1 ( a ) VALUES ( 6 ); 711UPDATE t1 SET a = 8 WHERE a = 3 LIMIT 4; 712DELETE FROM t1 WHERE a = 2 LIMIT 9; 713DELETE FROM t1 WHERE a = 1 LIMIT 4; 714UPDATE t1 SET a = 4 WHERE a = 2 LIMIT 7; 715INSERT INTO t1 ( a ) VALUES ( 0 ); 716DELETE FROM t1 WHERE a < 3 LIMIT 0; 717UPDATE t1 SET a = 8 WHERE a = 5 LIMIT 2; 718INSERT INTO t1 ( a ) VALUES ( 1 ); 719UPDATE IGNORE t1 SET a = 9 WHERE a < 5 LIMIT 3; 720connection slave; 721SET GLOBAL SLAVE_TYPE_CONVERSIONS = @saved_slave_type_conversions; 722include/diff_tables.inc [master:t1, slave:t1] 723connection master; 724drop table t1; 725connection slave; 726include/rpl_end.inc 727