1include/master-slave.inc 2[connection master] 3call mtr.add_suppression("Slave: Unknown table 't6' error.* 1051"); 4call mtr.add_suppression("Slave SQL.*Column [0-9] of table .test.t[0-9]*. cannot be converted from type.* error.* 1677"); 5**** Diff Table Def Start **** 6connection slave; 7STOP SLAVE; 8RESET SLAVE; 9SET @saved_slave_type_conversions = @@slave_type_conversions; 10SET GLOBAL SLAVE_TYPE_CONVERSIONS = 'ALL_NON_LOSSY'; 11CREATE TABLE t1 (a INT, b INT PRIMARY KEY, c CHAR(20), 12d FLOAT DEFAULT '2.00', 13e CHAR(4) DEFAULT 'TEST') 14ENGINE='MyISAM'; 15*** Create t1 on Master *** 16connection master; 17CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c CHAR(10) 18) ENGINE='MyISAM'; 19RESET MASTER; 20*** Start Slave *** 21connection slave; 22START SLAVE; 23*** Master Data Insert *** 24connection master; 25INSERT INTO t1 () VALUES(1,2,'TEXAS'),(2,1,'AUSTIN'),(3,4,'QA'); 26SELECT * FROM t1 ORDER BY a; 27a b c 281 2 TEXAS 292 1 AUSTIN 303 4 QA 31*** Select from slave *** 32connection slave; 33SELECT * FROM t1 ORDER BY a; 34a b c d e 351 2 TEXAS 2 TEST 362 1 AUSTIN 2 TEST 373 4 QA 2 TEST 38SET GLOBAL SLAVE_TYPE_CONVERSIONS = @saved_slave_type_conversions; 39*** Drop t1 *** 40connection master; 41DROP TABLE t1; 42connection slave; 43*** Create t2 on slave *** 44STOP SLAVE; 45RESET SLAVE; 46CREATE TABLE t2 (a INT, b INT PRIMARY KEY, c CHAR(5), 47d FLOAT DEFAULT '2.00', 48e CHAR(5) DEFAULT 'TEST2') 49ENGINE='MyISAM'; 50*** Create t2 on Master *** 51connection master; 52CREATE TABLE t2 (a INT PRIMARY KEY, b INT, c CHAR(10) 53) ENGINE='MyISAM'; 54RESET MASTER; 55*** Master Data Insert *** 56connection master; 57INSERT INTO t2 () VALUES(1,2,'Kyle, TEX'),(2,1,'JOE AUSTIN'),(3,4,'QA TESTING'); 58SELECT * FROM t2 ORDER BY a; 59a b c 601 2 Kyle, TEX 612 1 JOE AUSTIN 623 4 QA TESTING 63*** Start Slave *** 64connection slave; 65START SLAVE; 66include/wait_for_slave_sql_error.inc [errno=1677] 67Last_SQL_Error = 'Column 2 of table 'test.t2' cannot be converted from type 'char(10 octets)' to type 'char(5 octets) character set latin1'' 68STOP SLAVE; 69RESET SLAVE; 70SELECT * FROM t2 ORDER BY a; 71a b c d e 72connection master; 73RESET MASTER; 74connection slave; 75START SLAVE; 76*** Drop t2 *** 77connection master; 78DROP TABLE t2; 79connection slave; 80*** Create t3 on slave *** 81STOP SLAVE; 82RESET SLAVE; 83CREATE TABLE t3 (a INT, b INT PRIMARY KEY, c CHAR(20), 84d FLOAT DEFAULT '2.00', 85e CHAR(5) DEFAULT 'TEST2') 86ENGINE='MyISAM'; 87*** Create t3 on Master *** 88connection master; 89CREATE TABLE t3 (a BLOB, b INT PRIMARY KEY, c CHAR(20) 90) ENGINE='MyISAM'; 91RESET MASTER; 92*** Start Slave *** 93connection slave; 94START SLAVE; 95*** Master Data Insert *** 96connection master; 97set @b1 = 'b1'; 98set @b1 = concat(@b1,@b1); 99INSERT INTO t3 () VALUES(@b1,2,'Kyle, TEX'),(@b1,1,'JOE AUSTIN'),(@b1,4,'QA TESTING'); 100******************************************** 101*** Expect slave to fail with Error 1677 *** 102******************************************** 103connection slave; 104include/wait_for_slave_sql_error_and_skip.inc [errno=1677] 105Last_SQL_Error = 'Column 0 of table 'test.t3' cannot be converted from type 'blob' to type 'int(11)'' 106*** Drop t3 *** 107connection master; 108DROP TABLE t3; 109connection slave; 110*** Create t4 on slave *** 111STOP SLAVE; 112RESET SLAVE; 113CREATE TABLE t4 (a INT, b INT PRIMARY KEY, c CHAR(20), 114d FLOAT DEFAULT '2.00', 115e CHAR(5) DEFAULT 'TEST2') 116ENGINE='MyISAM'; 117*** Create t4 on Master *** 118connection master; 119CREATE TABLE t4 (a DECIMAL(8,2), b INT PRIMARY KEY, c CHAR(20) 120) ENGINE='MyISAM'; 121RESET MASTER; 122*** Start Slave *** 123connection slave; 124START SLAVE; 125*** Master Data Insert *** 126connection master; 127INSERT INTO t4 () VALUES(100.22,2,'Kyle, TEX'),(200.26,1,'JOE AUSTIN'), 128(30000.22,4,'QA TESTING'); 129******************************************** 130*** Expect slave to fail with Error 1677 *** 131******************************************** 132connection slave; 133include/wait_for_slave_sql_error_and_skip.inc [errno=1677] 134Last_SQL_Error = 'Column 0 of table 'test.t4' cannot be converted from type 'decimal(8,2)' to type 'int(11)'' 135*** Drop t4 *** 136connection master; 137DROP TABLE t4; 138connection slave; 139*** Create t5 on slave *** 140STOP SLAVE; 141RESET SLAVE; 142CREATE TABLE t5 (a INT PRIMARY KEY, b CHAR(5), 143c FLOAT, d INT, e DOUBLE, 144f DECIMAL(8,2))ENGINE='MyISAM'; 145*** Create t5 on Master *** 146connection master; 147CREATE TABLE t5 (a INT PRIMARY KEY, b VARCHAR(6), 148c DECIMAL(8,2), d BIT, e BLOB, 149f FLOAT) ENGINE='MyISAM'; 150RESET MASTER; 151*** Start Slave *** 152connection slave; 153START SLAVE; 154*** Master Data Insert *** 155connection master; 156INSERT INTO t5 () VALUES(1,'Kyle',200.23,1,'b1b1',23.00098), 157(2,'JOE',300.01,0,'b2b2',1.0000009); 158******************************************** 159*** Expect slave to fail with Error 1677 *** 160******************************************** 161connection slave; 162include/wait_for_slave_sql_error_and_skip.inc [errno=1677] 163Last_SQL_Error = 'Column 1 of table 'test.t5' cannot be converted from type 'varchar(6 octets)' to type 'char(5 octets) character set latin1'' 164*** Drop t5 *** 165connection master; 166DROP TABLE t5; 167connection slave; 168*** Create t6 on slave *** 169STOP SLAVE; 170RESET SLAVE; 171CREATE TABLE t6 (a INT PRIMARY KEY, b CHAR(5), 172c FLOAT, d INT)ENGINE='MyISAM'; 173*** Create t6 on Master *** 174connection master; 175CREATE TABLE t6 (a INT PRIMARY KEY, b VARCHAR(6), 176c DECIMAL(8,2), d BIT 177) ENGINE='MyISAM'; 178RESET MASTER; 179*** Start Slave *** 180connection slave; 181START SLAVE; 182*** Master Data Insert *** 183connection master; 184INSERT INTO t6 () VALUES(1,'Kyle',200.23,1), 185(2,'JOE',300.01,0); 186******************************************** 187*** Expect slave to fail with Error 1677 *** 188******************************************** 189connection slave; 190include/wait_for_slave_sql_error.inc [errno=1677] 191Last_SQL_Error = 'Column 1 of table 'test.t6' cannot be converted from type 'varchar(6 octets)' to type 'char(5 octets) character set latin1'' 192*** Drop t6 *** 193include/rpl_reset.inc 194connection master; 195DROP TABLE t6; 196connection slave; 197**** Diff Table Def End **** 198**** Extra Colums Start **** 199*** Create t7 on slave *** 200STOP SLAVE; 201RESET SLAVE; 202CREATE TABLE t7 (a INT KEY, b BLOB, c CHAR(5), 203d TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00', 204e CHAR(20) DEFAULT 'Extra Column Testing') 205ENGINE='MyISAM'; 206*** Create t7 on Master *** 207connection master; 208CREATE TABLE t7 (a INT PRIMARY KEY, b BLOB, c CHAR(5) 209) ENGINE='MyISAM'; 210RESET MASTER; 211*** Start Slave *** 212connection slave; 213START SLAVE; 214*** Master Data Insert *** 215connection master; 216set @b1 = 'b1'; 217set @b1 = concat(@b1,@b1); 218INSERT INTO t7 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); 219SELECT * FROM t7 ORDER BY a; 220a b c 2211 b1b1 Kyle 2222 b1b1 JOE 2233 b1b1 QA 224*** Select from slave *** 225connection slave; 226SELECT * FROM t7 ORDER BY a; 227a b c d e 2281 b1b1 Kyle 0000-00-00 00:00:00 Extra Column Testing 2292 b1b1 JOE 0000-00-00 00:00:00 Extra Column Testing 2303 b1b1 QA 0000-00-00 00:00:00 Extra Column Testing 231*** Drop t7 *** 232connection master; 233DROP TABLE t7; 234connection slave; 235*** Create t8 on slave *** 236STOP SLAVE; 237RESET SLAVE; 238CREATE TABLE t8 (a INT KEY, b BLOB, c CHAR(5), 239d TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00', 240e INT)ENGINE='MyISAM'; 241*** Create t8 on Master *** 242connection master; 243CREATE TABLE t8 (a INT PRIMARY KEY, b BLOB, c CHAR(5) 244) ENGINE='MyISAM'; 245RESET MASTER; 246*** Start Slave *** 247connection slave; 248START SLAVE; 249*** Master Data Insert *** 250connection master; 251set @b1 = 'b1b1b1b1'; 252set @b1 = concat(@b1,@b1); 253INSERT INTO t8 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); 254*** Drop t8 *** 255connection master; 256DROP TABLE t8; 257connection slave; 258STOP SLAVE; 259RESET SLAVE; 260CREATE TABLE t9 (a INT KEY, b BLOB, c CHAR(5), 261d TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 262ON UPDATE CURRENT_TIMESTAMP, 263e INT NOT NULL, 264f text not null, 265g text, 266h blob not null, 267i blob) ENGINE='MyISAM'; 268*** Create t9 on Master *** 269connection master; 270CREATE TABLE t9 (a INT PRIMARY KEY, b BLOB, c CHAR(5) 271) ENGINE='MyISAM'; 272RESET MASTER; 273*** Start Slave *** 274connection slave; 275START SLAVE; 276*** Master Data Insert *** 277connection master; 278set @b1 = 'b1b1b1b1'; 279set @b1 = concat(@b1,@b1); 280INSERT INTO t9 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); 281connection slave; 282select * from t9; 283a b c d e f g h i 2841 b1b1b1b1b1b1b1b1 Kyle 0000-00-00 00:00:00 0 NULL NULL 2852 b1b1b1b1b1b1b1b1 JOE 0000-00-00 00:00:00 0 NULL NULL 2863 b1b1b1b1b1b1b1b1 QA 0000-00-00 00:00:00 0 NULL NULL 287connection master; 288DROP TABLE t9; 289connection slave; 290*** Create t10 on slave *** 291STOP SLAVE; 292RESET SLAVE; 293CREATE TABLE t10 (a INT KEY, b BLOB, f DOUBLE DEFAULT '233', 294c CHAR(5), e INT DEFAULT '1')ENGINE='MyISAM'; 295*** Create t10 on Master *** 296connection master; 297CREATE TABLE t10 (a INT PRIMARY KEY, b BLOB, c CHAR(5) 298) ENGINE='MyISAM'; 299RESET MASTER; 300*** Start Slave *** 301connection slave; 302START SLAVE; 303*** Master Data Insert *** 304connection master; 305set @b1 = 'b1b1b1b1'; 306set @b1 = concat(@b1,@b1); 307INSERT INTO t10 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); 308******************************************** 309*** Expect slave to fail with Error 1677 *** 310******************************************** 311connection slave; 312include/wait_for_slave_sql_error_and_skip.inc [errno=1677] 313Last_SQL_Error = 'Column 2 of table 'test.t10' cannot be converted from type 'char(5 octets)' to type 'double'' 314*** Drop t10 *** 315connection master; 316DROP TABLE t10; 317connection slave; 318*** Create t11 on slave *** 319STOP SLAVE; 320RESET SLAVE; 321CREATE TABLE t11 (a INT KEY, b BLOB, f INT, 322c CHAR(5) DEFAULT 'test', e INT DEFAULT '1')ENGINE='MyISAM'; 323*** Create t11 on Master *** 324connection master; 325CREATE TABLE t11 (a INT PRIMARY KEY, b BLOB, c VARCHAR(254) 326) ENGINE='MyISAM'; 327RESET MASTER; 328*** Start Slave *** 329connection slave; 330START SLAVE; 331*** Master Data Insert *** 332connection master; 333set @b1 = 'b1b1b1b1'; 334set @b1 = concat(@b1,@b1); 335INSERT INTO t11 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); 336******************************************** 337*** Expect slave to fail with Error 1677 *** 338******************************************** 339connection slave; 340include/wait_for_slave_sql_error_and_skip.inc [errno=1677] 341Last_SQL_Error = 'Column 2 of table 'test.t11' cannot be converted from type 'varchar(254 octets)' to type 'int(11)'' 342*** Drop t11 *** 343connection master; 344DROP TABLE t11; 345connection slave; 346*** Create t12 on slave *** 347STOP SLAVE; 348RESET SLAVE; 349CREATE TABLE t12 (a INT KEY, b BLOB, f TEXT, 350c CHAR(5) DEFAULT 'test', e INT DEFAULT '1')ENGINE='MyISAM'; 351*** Create t12 on Master *** 352connection master; 353CREATE TABLE t12 (a INT PRIMARY KEY, b BLOB, c BLOB 354) ENGINE='MyISAM'; 355RESET MASTER; 356*** Start Slave *** 357connection slave; 358START SLAVE; 359*** Master Data Insert *** 360connection master; 361set @b1 = 'b1b1b1b1'; 362set @b1 = concat(@b1,@b1); 363INSERT INTO t12 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); 364SELECT * FROM t12 ORDER BY a; 365a b c 3661 b1b1b1b1b1b1b1b1 Kyle 3672 b1b1b1b1b1b1b1b1 JOE 3683 b1b1b1b1b1b1b1b1 QA 369*** Select on Slave *** 370connection slave; 371SELECT * FROM t12 ORDER BY a; 372a b f c e 3731 b1b1b1b1b1b1b1b1 Kyle test 1 3742 b1b1b1b1b1b1b1b1 JOE test 1 3753 b1b1b1b1b1b1b1b1 QA test 1 376*** Drop t12 *** 377connection master; 378DROP TABLE t12; 379connection slave; 380**** Extra Colums End **** 381*** BUG 22177 Start *** 382*** Create t13 on slave *** 383STOP SLAVE; 384RESET SLAVE; 385CREATE TABLE t13 (a INT KEY, b BLOB, c CHAR(5), 386d INT DEFAULT '1', 387e TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP 388)ENGINE='MyISAM'; 389*** Create t13 on Master *** 390connection master; 391CREATE TABLE t13 (a INT PRIMARY KEY, b BLOB, c CHAR(5) 392) ENGINE='MyISAM'; 393RESET MASTER; 394*** Start Slave *** 395connection slave; 396START SLAVE; 397*** Master Data Insert *** 398connection master; 399set @b1 = 'b1b1b1b1'; 400set @b1 = concat(@b1,@b1); 401INSERT INTO t13 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); 402SELECT * FROM t13 ORDER BY a; 403a b c 4041 b1b1b1b1b1b1b1b1 Kyle 4052 b1b1b1b1b1b1b1b1 JOE 4063 b1b1b1b1b1b1b1b1 QA 407*** Select on Slave **** 408connection slave; 409SELECT * FROM t13 ORDER BY a; 410a b c d e 4111 b1b1b1b1b1b1b1b1 Kyle 1 CURRENT_TIMESTAMP 4122 b1b1b1b1b1b1b1b1 JOE 1 CURRENT_TIMESTAMP 4133 b1b1b1b1b1b1b1b1 QA 1 CURRENT_TIMESTAMP 414*** Drop t13 *** 415connection master; 416DROP TABLE t13; 417connection slave; 418*** 22117 END *** 419*** Alter Master Table Testing Start *** 420*** Create t14 on slave *** 421STOP SLAVE; 422RESET SLAVE; 423CREATE TABLE t14 (c1 INT KEY, c4 BLOB, c5 CHAR(5), 424c6 INT DEFAULT '1', 425c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP 426)ENGINE='MyISAM'; 427*** Create t14 on Master *** 428connection master; 429CREATE TABLE t14 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5) 430) ENGINE='MyISAM'; 431RESET MASTER; 432*** Start Slave *** 433connection slave; 434START SLAVE; 435*** Master Data Insert *** 436connection master; 437ALTER TABLE t14 ADD COLUMN c2 DECIMAL(8,2) AFTER c1; 438ALTER TABLE t14 ADD COLUMN c3 TEXT AFTER c2; 439set @b1 = 'b1b1b1b1'; 440set @b1 = concat(@b1,@b1); 441INSERT INTO t14 () VALUES(1,1.00,'Replication Testing Extra Col',@b1,'Kyle'), 442(2,2.00,'This Test Should work',@b1,'JOE'), 443(3,3.00,'If is does not, I will open a bug',@b1,'QA'); 444SELECT * FROM t14 ORDER BY c1; 445c1 c2 c3 c4 c5 4461 1.00 Replication Testing Extra Col b1b1b1b1b1b1b1b1 Kyle 4472 2.00 This Test Should work b1b1b1b1b1b1b1b1 JOE 4483 3.00 If is does not, I will open a bug b1b1b1b1b1b1b1b1 QA 449*** Select on Slave **** 450connection slave; 451SELECT * FROM t14 ORDER BY c1; 452c1 c2 c3 c4 c5 c6 c7 4531 1.00 Replication Testing Extra Col b1b1b1b1b1b1b1b1 Kyle 1 CURRENT_TIMESTAMP 4542 2.00 This Test Should work b1b1b1b1b1b1b1b1 JOE 1 CURRENT_TIMESTAMP 4553 3.00 If is does not, I will open a bug b1b1b1b1b1b1b1b1 QA 1 CURRENT_TIMESTAMP 456*** Create t14a on slave *** 457STOP SLAVE; 458RESET SLAVE; 459CREATE TABLE t14a (c1 INT KEY, c4 BLOB, c5 CHAR(5), 460c6 INT DEFAULT '1', 461c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP 462)ENGINE='MyISAM'; 463*** Create t14a on Master *** 464connection master; 465CREATE TABLE t14a (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5) 466) ENGINE='MyISAM'; 467RESET MASTER; 468*** Start Slave *** 469connection slave; 470START SLAVE; 471*** Master Data Insert *** 472connection master; 473set @b1 = 'b1b1b1b1'; 474set @b1 = concat(@b1,@b1); 475INSERT INTO t14a () VALUES(1,@b1,'Kyle'), 476(2,@b1,'JOE'), 477(3,@b1,'QA'); 478SELECT * FROM t14a ORDER BY c1; 479c1 c4 c5 4801 b1b1b1b1b1b1b1b1 Kyle 4812 b1b1b1b1b1b1b1b1 JOE 4823 b1b1b1b1b1b1b1b1 QA 483*** Select on Slave **** 484connection slave; 485SELECT * FROM t14a ORDER BY c1; 486c1 c4 c5 c6 c7 4871 b1b1b1b1b1b1b1b1 Kyle 1 CURRENT_TIMESTAMP 4882 b1b1b1b1b1b1b1b1 JOE 1 CURRENT_TIMESTAMP 4893 b1b1b1b1b1b1b1b1 QA 1 CURRENT_TIMESTAMP 490STOP SLAVE; 491RESET SLAVE; 492*** Master Drop c5 *** 493connection master; 494ALTER TABLE t14a DROP COLUMN c5; 495RESET MASTER; 496*** Start Slave *** 497connection slave; 498START SLAVE; 499*** Master Data Insert *** 500connection master; 501set @b1 = 'b1b1b1b1'; 502set @b1 = concat(@b1,@b1); 503INSERT INTO t14a () VALUES(4,@b1), 504(5,@b1), 505(6,@b1); 506SELECT * FROM t14a ORDER BY c1; 507c1 c4 5081 b1b1b1b1b1b1b1b1 5092 b1b1b1b1b1b1b1b1 5103 b1b1b1b1b1b1b1b1 5114 b1b1b1b1b1b1b1b1 5125 b1b1b1b1b1b1b1b1 5136 b1b1b1b1b1b1b1b1 514*** Select on Slave **** 515connection slave; 516SELECT * FROM t14a ORDER BY c1; 517c1 c4 c5 c6 c7 5181 b1b1b1b1b1b1b1b1 Kyle 1 CURRENT_TIMESTAMP 5192 b1b1b1b1b1b1b1b1 JOE 1 CURRENT_TIMESTAMP 5203 b1b1b1b1b1b1b1b1 QA 1 CURRENT_TIMESTAMP 5214 b1b1b1b1b1b1b1b1 NULL 1 CURRENT_TIMESTAMP 5225 b1b1b1b1b1b1b1b1 NULL 1 CURRENT_TIMESTAMP 5236 b1b1b1b1b1b1b1b1 NULL 1 CURRENT_TIMESTAMP 524connection master; 525DROP TABLE t14a; 526connection slave; 527*** connect to master and drop columns *** 528connection master; 529ALTER TABLE t14 DROP COLUMN c2; 530ALTER TABLE t14 DROP COLUMN c4; 531*** Select from Master *** 532SELECT * FROM t14 ORDER BY c1; 533c1 c3 c5 5341 Replication Testing Extra Col Kyle 5352 This Test Should work JOE 5363 If is does not, I will open a bug QA 537*** Select from Slave *** 538connection slave; 539SELECT * FROM t14 ORDER BY c1; 540c1 c3 c5 c6 c7 5411 Replication Testing Extra Col Kyle 1 CURRENT_TIMESTAMP 5422 This Test Should work JOE 1 CURRENT_TIMESTAMP 5433 If is does not, I will open a bug QA 1 CURRENT_TIMESTAMP 544*** Drop t14 *** 545connection master; 546DROP TABLE t14; 547connection slave; 548*** Create t15 on slave *** 549STOP SLAVE; 550RESET SLAVE; 551CREATE TABLE t15 (c1 INT KEY, c2 DECIMAL(8,2), c3 TEXT, 552c4 BLOB, c5 CHAR(5), 553c6 INT DEFAULT '1', 554c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP 555)ENGINE='MyISAM'; 556*** Create t15 on Master *** 557connection master; 558CREATE TABLE t15 (c1 INT PRIMARY KEY, c2 DECIMAL(8,2), c3 TEXT, 559c4 BLOB, c5 CHAR(5)) ENGINE='MyISAM'; 560RESET MASTER; 561*** Start Slave *** 562connection slave; 563START SLAVE; 564call mtr.add_suppression("Error .Unknown table .t6.. on query.* error.* 1051"); 565call mtr.add_suppression("Error .Duplicate column name .c6.. on query.* error.* 1060"); 566call mtr.add_suppression("Table definition on master and slave does not match: Column . ...e mismatch.* error.* 1535"); 567*** Master Data Insert *** 568connection master; 569set @b1 = 'b1b1b1b1'; 570set @b1 = concat(@b1,@b1); 571INSERT INTO t15 () VALUES(1,1.00,'Replication Testing Extra Col',@b1,'Kyle'), 572(2,2.00,'This Test Should work',@b1,'JOE'), 573(3,3.00,'If is does not, I will open a bug',@b1,'QA'); 574SELECT * FROM t15 ORDER BY c1; 575c1 c2 c3 c4 c5 5761 1.00 Replication Testing Extra Col b1b1b1b1b1b1b1b1 Kyle 5772 2.00 This Test Should work b1b1b1b1b1b1b1b1 JOE 5783 3.00 If is does not, I will open a bug b1b1b1b1b1b1b1b1 QA 579*** Select on Slave **** 580connection slave; 581SELECT * FROM t15 ORDER BY c1; 582c1 c2 c3 c4 c5 c6 c7 5831 1.00 Replication Testing Extra Col b1b1b1b1b1b1b1b1 Kyle 1 CURRENT_TIMESTAMP 5842 2.00 This Test Should work b1b1b1b1b1b1b1b1 JOE 1 CURRENT_TIMESTAMP 5853 3.00 If is does not, I will open a bug b1b1b1b1b1b1b1b1 QA 1 CURRENT_TIMESTAMP 586*** Add column on master that is a Extra on Slave *** 587connection master; 588ALTER TABLE t15 ADD COLUMN c6 INT AFTER c5; 589******************************************** 590*** Expect slave to fail with Error 1060 *** 591******************************************** 592connection slave; 593include/wait_for_slave_sql_error_and_skip.inc [errno=1060] 594Last_SQL_Error = 'Error 'Duplicate column name 'c6'' on query. Default database: 'test'. Query: 'ALTER TABLE t15 ADD COLUMN c6 INT AFTER c5'' 595*** Try to insert in master **** 596connection master; 597INSERT INTO t15 () VALUES(5,2.00,'Replication Testing',@b1,'Buda',2); 598SELECT * FROM t15 ORDER BY c1; 599c1 c2 c3 c4 c5 c6 6001 1.00 Replication Testing Extra Col b1b1b1b1b1b1b1b1 Kyle NULL 6012 2.00 This Test Should work b1b1b1b1b1b1b1b1 JOE NULL 6023 3.00 If is does not, I will open a bug b1b1b1b1b1b1b1b1 QA NULL 6035 2.00 Replication Testing b1b1b1b1b1b1b1b1 Buda 2 604*** Try to select from slave **** 605connection slave; 606SELECT * FROM t15 ORDER BY c1; 607c1 c2 c3 c4 c5 c6 c7 6081 1.00 Replication Testing Extra Col b1b1b1b1b1b1b1b1 Kyle 1 CURRENT_TIMESTAMP 6092 2.00 This Test Should work b1b1b1b1b1b1b1b1 JOE 1 CURRENT_TIMESTAMP 6103 3.00 If is does not, I will open a bug b1b1b1b1b1b1b1b1 QA 1 CURRENT_TIMESTAMP 6115 2.00 Replication Testing b1b1b1b1b1b1b1b1 Buda 2 CURRENT_TIMESTAMP 612*** DROP TABLE t15 *** 613connection master; 614DROP TABLE t15; 615connection slave; 616*** Create t16 on slave *** 617STOP SLAVE; 618RESET SLAVE; 619CREATE TABLE t16 (c1 INT KEY, c2 DECIMAL(8,2), c3 TEXT, 620c4 BLOB, c5 CHAR(5), 621c6 INT DEFAULT '1', 622c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP 623)ENGINE='MyISAM'; 624*** Create t16 on Master *** 625connection master; 626CREATE TABLE t16 (c1 INT PRIMARY KEY, c2 DECIMAL(8,2), c3 TEXT, 627c4 BLOB, c5 CHAR(5))ENGINE='MyISAM'; 628RESET MASTER; 629*** Start Slave *** 630connection slave; 631START SLAVE; 632*** Master Data Insert *** 633connection master; 634set @b1 = 'b1b1b1b1'; 635set @b1 = concat(@b1,@b1); 636INSERT INTO t16 () VALUES(1,1.00,'Replication Testing Extra Col',@b1,'Kyle'), 637(2,2.00,'This Test Should work',@b1,'JOE'), 638(3,3.00,'If is does not, I will open a bug',@b1,'QA'); 639SELECT * FROM t16 ORDER BY c1; 640c1 c2 c3 c4 c5 6411 1.00 Replication Testing Extra Col b1b1b1b1b1b1b1b1 Kyle 6422 2.00 This Test Should work b1b1b1b1b1b1b1b1 JOE 6433 3.00 If is does not, I will open a bug b1b1b1b1b1b1b1b1 QA 644*** Select on Slave **** 645connection slave; 646SELECT * FROM t16 ORDER BY c1; 647c1 c2 c3 c4 c5 c6 c7 6481 1.00 Replication Testing Extra Col b1b1b1b1b1b1b1b1 Kyle 1 CURRENT_TIMESTAMP 6492 2.00 This Test Should work b1b1b1b1b1b1b1b1 JOE 1 CURRENT_TIMESTAMP 6503 3.00 If is does not, I will open a bug b1b1b1b1b1b1b1b1 QA 1 CURRENT_TIMESTAMP 651*** Add Partition on master *** 652connection master; 653ALTER TABLE t16 PARTITION BY KEY(c1) PARTITIONS 4; 654INSERT INTO t16 () VALUES(4,1.00,'Replication Rocks',@b1,'Omer'); 655SHOW CREATE TABLE t16; 656Table Create Table 657t16 CREATE TABLE `t16` ( 658 `c1` int(11) NOT NULL, 659 `c2` decimal(8,2) DEFAULT NULL, 660 `c3` text DEFAULT NULL, 661 `c4` blob DEFAULT NULL, 662 `c5` char(5) DEFAULT NULL, 663 PRIMARY KEY (`c1`) 664) ENGINE=MyISAM DEFAULT CHARSET=latin1 665 PARTITION BY KEY (`c1`) 666PARTITIONS 4 667*** Show table on Slave **** 668connection slave; 669SHOW CREATE TABLE t16; 670Table Create Table 671t16 CREATE TABLE `t16` ( 672 `c1` int(11) NOT NULL, 673 `c2` decimal(8,2) DEFAULT NULL, 674 `c3` text DEFAULT NULL, 675 `c4` blob DEFAULT NULL, 676 `c5` char(5) DEFAULT NULL, 677 `c6` int(11) DEFAULT 1, 678 `c7` timestamp NULL DEFAULT current_timestamp(), 679 PRIMARY KEY (`c1`) 680) ENGINE=MyISAM DEFAULT CHARSET=latin1 681 PARTITION BY KEY (`c1`) 682PARTITIONS 4 683*** DROP TABLE t16 *** 684connection master; 685DROP TABLE t16; 686connection slave; 687*** Alter Master End *** 688*** Create t17 on slave *** 689STOP SLAVE; 690RESET SLAVE; 691CREATE TABLE t17 (a SMALLINT, b INT PRIMARY KEY, c CHAR(5), 692d FLOAT DEFAULT '2.00', 693e CHAR(5) DEFAULT 'TEST2') 694ENGINE='MyISAM'; 695*** Create t17 on Master *** 696connection master; 697CREATE TABLE t17 (a BIGINT PRIMARY KEY, b INT, c CHAR(10) 698) ENGINE='MyISAM'; 699RESET MASTER; 700*** Start Slave *** 701connection slave; 702START SLAVE; 703*** Master Data Insert *** 704connection master; 705INSERT INTO t17 () VALUES(9223372036854775807,2,'Kyle, TEX'); 706******************************************** 707*** Expect slave to fail with Error 1677 *** 708******************************************** 709connection slave; 710include/wait_for_slave_sql_error_and_skip.inc [errno=1677] 711Last_SQL_Error = 'Column 0 of table 'test.t17' cannot be converted from type 'bigint' to type 'smallint(6)'' 712** DROP table t17 *** 713connection master; 714DROP TABLE t17; 715connection slave; 716include/rpl_end.inc 717