1include/master-slave.inc 2[connection master] 3 4*********************************************************** 5*********************************************************** 6***************** Start of Testing ************************ 7*********************************************************** 8*********************************************************** 9* This test format == binlog_format ROW and engine == 'MyISAM' 10*********************************************************** 11*********************************************************** 12 13***** Testing more columns on the Master ***** 14 15connection master; 16CREATE TABLE t1 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20), 17/* extra */ 18f5 FLOAT DEFAULT '2.00', 19f6 CHAR(4) DEFAULT 'TEST', 20f7 INT DEFAULT '0', 21f8 TEXT, 22f9 LONGBLOB, 23f10 BIT(63), 24f11 VARBINARY(64))ENGINE='MyISAM'; 25 26* Alter Table on Slave and drop columns f5 through f11 * 27 28connection slave; 29alter table t1 drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11; 30 31* Insert data in Master then update and delete some rows* 32 33connection master; 34* Select count and 20 rows from Master * 35 36SELECT COUNT(*) FROM t1; 37COUNT(*) 3840 39 40SELECT f1,f2,f3,f4,f5,f6,f7,f8,f9, 41hex(f10),hex(f11) FROM t1 ORDER BY f3 LIMIT 20; 42f1 f2 f3 f4 f5 f6 f7 f8 f9 hex(f10) hex(f11) 432 2 2 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456 443 3 3 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456 455 5 5 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456 466 6 6 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456 478 8 8 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456 489 9 9 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456 4911 11 11 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456 5012 12 12 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456 5114 14 14 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456 5215 15 15 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456 5317 17 17 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456 5418 18 18 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456 5520 20 20 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456 5621 21 21 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456 5723 23 23 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456 5824 24 24 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456 5926 26 26 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456 6027 27 27 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456 6129 29 29 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456 6230 30 30 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456 63connection slave; 64 65* Select count and 20 rows from Slave * 66 67SELECT COUNT(*) FROM t1; 68COUNT(*) 6940 70 71SELECT * FROM t1 ORDER BY f3 LIMIT 20; 72f1 f2 f3 f4 732 2 2 second 743 3 3 next 755 5 5 second 766 6 6 next 778 8 8 second 789 9 9 next 7911 11 11 second 8012 12 12 next 8114 14 14 second 8215 15 15 next 8317 17 17 second 8418 18 18 next 8520 20 20 second 8621 21 21 next 8723 23 23 second 8824 24 24 next 8926 26 26 second 9027 27 27 next 9129 29 29 second 9230 30 30 next 93include/check_slave_is_running.inc 94 95***** Testing Altering table def scenario ***** 96 97connection master; 98CREATE TABLE t2 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20), 99/* extra */ 100f5 DOUBLE DEFAULT '2.00', 101f6 ENUM('a', 'b', 'c') default 'a', 102f7 DECIMAL(17,9) default '1000.00', 103f8 MEDIUMBLOB, 104f9 NUMERIC(6,2) default '2000.00', 105f10 VARCHAR(1024), 106f11 BINARY(20) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0', 107f12 SET('a', 'b', 'c') default 'b') 108ENGINE='MyISAM'; 109 110CREATE TABLE t3 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20), 111/* extra */ 112f5 DOUBLE DEFAULT '2.00', 113f6 ENUM('a', 'b', 'c') default 'a', 114f8 MEDIUMBLOB, 115f10 VARCHAR(1024), 116f11 BINARY(20) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0', 117f12 SET('a', 'b', 'c') default 'b') 118ENGINE='MyISAM'; 119 120CREATE TABLE t4 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20), 121/* extra */ 122f5 DOUBLE DEFAULT '2.00', 123f6 DECIMAL(17,9) default '1000.00', 124f7 MEDIUMBLOB, 125f8 NUMERIC(6,2) default '2000.00', 126f9 VARCHAR(1024), 127f10 BINARY(20) not null default '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0', 128f11 CHAR(255)) 129ENGINE='MyISAM'; 130 131CREATE TABLE t31 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20), 132/* extra */ 133f5 BIGINT, 134f6 BLOB, 135f7 DATE, 136f8 DATETIME, 137f9 FLOAT, 138f10 INT, 139f11 LONGBLOB, 140f12 LONGTEXT, 141f13 MEDIUMBLOB, 142f14 MEDIUMINT, 143f15 MEDIUMTEXT, 144f16 REAL, 145f17 SMALLINT, 146f18 TEXT, 147f19 TIME, 148f20 TIMESTAMP, 149f21 TINYBLOB, 150f22 TINYINT, 151f23 TINYTEXT, 152f24 YEAR, 153f25 BINARY(255), 154f26 BIT(64), 155f27 CHAR(255), 156f28 DECIMAL(30,7), 157f29 DOUBLE, 158f30 ENUM ('a','b', 'c') default 'a', 159f31 FLOAT, 160f32 NUMERIC(17,9), 161f33 SET ('a', 'b', 'c') default 'b', 162f34 VARBINARY(1025), 163f35 VARCHAR(257) 164) ENGINE='MyISAM'; 165 166** Alter tables on slave and drop columns ** 167 168connection slave; 169alter table t2 drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11, drop 170f12; 171alter table t3 drop f5, drop f6, drop f8, drop f10, drop f11, drop f12; 172alter table t4 drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11; 173alter table t31 174drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11, 175drop f12, drop f13, drop f14, drop f15, drop f16, drop f17, drop f18, 176drop f19, drop f20, drop f21, drop f22, drop f23, drop f24, drop f25, 177drop f26, drop f27, drop f28, drop f29, drop f30, drop f31, drop f32, 178drop f33, drop f34, drop f35; 179 180** Insert Data into Master ** 181connection master; 182INSERT into t2 set f1=1, f2=1, f3=1, f4='first', f8='f8: medium size blob', f10='f10: 183some var char'; 184INSERT into t2 values (2, 2, 2, 'second', 1852.0, 'b', 2000.0002, 'f8: medium size blob', 2000, 'f10: some var char', 186'01234567', 'c'), 187(3, 3, 3, 'third', 1883.0, 'b', 3000.0003, 'f8: medium size blob', 3000, 'f10: some var char', 189'01234567', 'c'); 190INSERT into t3 set f1=1, f2=1, f3=1, f4='first', f10='f10: some var char'; 191INSERT into t4 set f1=1, f2=1, f3=1, f4='first', f7='f7: medium size blob', f10='f10: 192binary data'; 193INSERT into t31 set f1=1, f2=1, f3=1, f4='first'; 194insert ignore into t31 set f1=1, f2=1, f3=2, f4='second', 195f9=2.2, f10='seven samurai', f28=222.222, f35='222'; 196Warnings: 197Warning 1366 Incorrect integer value: 'seven samurai' for column `test`.`t31`.`f10` at row 1 198insert ignore into t31 values (1, 1, 3, 'third', 199/* f5 BIGINT, */ 333333333333333333333333, 200/* f6 BLOB, */ '3333333333333333333333', 201/* f7 DATE, */ '2007-07-18', 202/* f8 DATETIME, */ "2007-07-18", 203/* f9 FLOAT, */ 3.33333333, 204/* f10 INT, */ 333333333, 205/* f11 LONGBLOB, */ '3333333333333333333', 206/* f12 LONGTEXT, */ '3333333333333333333', 207/* f13 MEDIUMBLOB, */ '3333333333333333333', 208/* f14 MEDIUMINT, */ 33, 209/* f15 MEDIUMTEXT, */ 3.3, 210/* f16 REAL, */ 3.3, 211/* f17 SMALLINT, */ 3, 212/* f18 TEXT, */ '33', 213/* f19 TIME, */ '2:59:58.999', 214/* f20 TIMESTAMP, */ 20000303000000, 215/* f21 TINYBLOB, */ '3333', 216/* f22 TINYINT, */ 3, 217/* f23 TINYTEXT, */ '3', 218/* f24 YEAR, */ 3000, 219/* f25 BINARY(255), */ 'three_33333', 220/* f26 BIT(64), */ b'011', 221/* f27 CHAR(255), */ 'three', 222/* f28 DECIMAL(30,7), */ 3.333, 223/* f29 DOUBLE, */ 3.333333333333333333333333333, 224/* f30 ENUM ('a','b','c')*/ 'c', 225/* f31 FLOAT, */ 3.0, 226/* f32 NUMERIC(17,9), */ 3.3333, 227/* f33 SET ('a','b','c'),*/ 'c', 228/*f34 VARBINARY(1025),*/ '3333 minus 3', 229/*f35 VARCHAR(257),*/ 'three times three' 230 ); 231Warnings: 232Warning 1264 Out of range value for column 'f5' at row 1 233Warning 1264 Out of range value for column 'f24' at row 1 234insert ignore into t31 values (1, 1, 4, 'fourth', 235/* f5 BIGINT, */ 333333333333333333333333, 236/* f6 BLOB, */ '3333333333333333333333', 237/* f7 DATE, */ '2007-07-18', 238/* f8 DATETIME, */ "2007-07-18", 239/* f9 FLOAT, */ 3.33333333, 240/* f10 INT, */ 333333333, 241/* f11 LONGBLOB, */ '3333333333333333333', 242/* f12 LONGTEXT, */ '3333333333333333333', 243/* f13 MEDIUMBLOB, */ '3333333333333333333', 244/* f14 MEDIUMINT, */ 33, 245/* f15 MEDIUMTEXT, */ 3.3, 246/* f16 REAL, */ 3.3, 247/* f17 SMALLINT, */ 3, 248/* f18 TEXT, */ '33', 249/* f19 TIME, */ '2:59:58.999', 250/* f20 TIMESTAMP, */ 20000303000000, 251/* f21 TINYBLOB, */ '3333', 252/* f22 TINYINT, */ 3, 253/* f23 TINYTEXT, */ '3', 254/* f24 YEAR, */ 3000, 255/* f25 BINARY(255), */ 'three_33333', 256/* f26 BIT(64), */ b'011', 257/* f27 CHAR(255), */ 'three', 258/* f28 DECIMAL(30,7), */ 3.333, 259/* f29 DOUBLE, */ 3.333333333333333333333333333, 260/* f30 ENUM ('a','b','c')*/ 'c', 261/* f31 FLOAT, */ 3.0, 262/* f32 NUMERIC(17,9), */ 3.3333, 263/* f33 SET ('a','b','c'),*/ 'c', 264/*f34 VARBINARY(1025),*/ '3333 minus 3', 265/*f35 VARCHAR(257),*/ 'three times three' 266 ), 267(1, 1, 5, 'fifth', 268/* f5 BIGINT, */ 333333333333333333333333, 269/* f6 BLOB, */ '3333333333333333333333', 270/* f7 DATE, */ '2007-07-18', 271/* f8 DATETIME, */ "2007-07-18", 272/* f9 FLOAT, */ 3.33333333, 273/* f10 INT, */ 333333333, 274/* f11 LONGBLOB, */ '3333333333333333333', 275/* f12 LONGTEXT, */ '3333333333333333333', 276/* f13 MEDIUMBLOB, */ '3333333333333333333', 277/* f14 MEDIUMINT, */ 33, 278/* f15 MEDIUMTEXT, */ 3.3, 279/* f16 REAL, */ 3.3, 280/* f17 SMALLINT, */ 3, 281/* f18 TEXT, */ '33', 282/* f19 TIME, */ '2:59:58.999', 283/* f20 TIMESTAMP, */ 20000303000000, 284/* f21 TINYBLOB, */ '3333', 285/* f22 TINYINT, */ 3, 286/* f23 TINYTEXT, */ '3', 287/* f24 YEAR, */ 3000, 288/* f25 BINARY(255), */ 'three_33333', 289/* f26 BIT(64), */ b'011', 290/* f27 CHAR(255), */ 'three', 291/* f28 DECIMAL(30,7), */ 3.333, 292/* f29 DOUBLE, */ 3.333333333333333333333333333, 293/* f30 ENUM ('a','b','c')*/ 'c', 294/* f31 FLOAT, */ 3.0, 295/* f32 NUMERIC(17,9), */ 3.3333, 296/* f33 SET ('a','b','c'),*/ 'c', 297/*f34 VARBINARY(1025),*/ '3333 minus 3', 298/*f35 VARCHAR(257),*/ 'three times three' 299 ), 300(1, 1, 6, 'sixth', 301/* f5 BIGINT, */ NULL, 302/* f6 BLOB, */ '3333333333333333333333', 303/* f7 DATE, */ '2007-07-18', 304/* f8 DATETIME, */ "2007-07-18", 305/* f9 FLOAT, */ 3.33333333, 306/* f10 INT, */ 333333333, 307/* f11 LONGBLOB, */ '3333333333333333333', 308/* f12 LONGTEXT, */ '3333333333333333333', 309/* f13 MEDIUMBLOB, */ '3333333333333333333', 310/* f14 MEDIUMINT, */ 33, 311/* f15 MEDIUMTEXT, */ 3.3, 312/* f16 REAL, */ 3.3, 313/* f17 SMALLINT, */ 3, 314/* f18 TEXT, */ '33', 315/* f19 TIME, */ '2:59:58.999', 316/* f20 TIMESTAMP, */ 20000303000000, 317/* f21 TINYBLOB, */ '3333', 318/* f22 TINYINT, */ 3, 319/* f23 TINYTEXT, */ '3', 320/* f24 YEAR, */ 3000, 321/* f25 BINARY(255), */ 'three_33333', 322/* f26 BIT(64), */ b'011', 323/* f27 CHAR(255), */ 'three', 324/* f28 DECIMAL(30,7), */ 3.333, 325/* f29 DOUBLE, */ 3.333333333333333333333333333, 326/* f30 ENUM ('a','b','c')*/ 'c', 327/* f31 FLOAT, */ 3.0, 328/* f32 NUMERIC(17,9), */ 3.3333, 329/* f33 SET ('a','b','c'),*/ 'c', 330/*f34 VARBINARY(1025),*/ '3333 minus 3', 331/*f35 VARCHAR(257),*/ NULL 332); 333Warnings: 334Warning 1264 Out of range value for column 'f5' at row 1 335Warning 1264 Out of range value for column 'f24' at row 1 336Warning 1264 Out of range value for column 'f5' at row 2 337Warning 1264 Out of range value for column 'f24' at row 2 338Warning 1264 Out of range value for column 'f24' at row 3 339 340** Sync slave with master ** 341** Do selects from tables ** 342 343connection slave; 344select * from t1 order by f3; 345f1 f2 f3 f4 3462 2 2 second 3473 3 3 next 3485 5 5 second 3496 6 6 next 3508 8 8 second 3519 9 9 next 35211 11 11 second 35312 12 12 next 35414 14 14 second 35515 15 15 next 35617 17 17 second 35718 18 18 next 35820 20 20 second 35921 21 21 next 36023 23 23 second 36124 24 24 next 36226 26 26 second 36327 27 27 next 36429 29 29 second 36530 30 30 next 36631 31 31 second 36732 32 32 second 36833 33 33 second 36934 34 34 second 37035 35 35 second 37136 36 36 second 37237 37 37 second 37338 38 38 second 37439 39 39 second 37540 40 40 second 37641 41 41 second 37742 42 42 second 37843 43 43 second 37944 44 44 second 38045 45 45 second 38146 46 46 second 38247 47 47 second 38348 48 48 second 38449 49 49 second 38550 50 50 second 386select * from t2 order by f1; 387f1 f2 f3 f4 3881 1 1 first 3892 2 2 second 3903 3 3 third 391select * from t3 order by f1; 392f1 f2 f3 f4 3931 1 1 first 394select * from t4 order by f1; 395f1 f2 f3 f4 3961 1 1 first 397select * from t31 order by f3; 398f1 f2 f3 f4 3991 1 1 first 4001 1 2 second 4011 1 3 third 4021 1 4 fourth 4031 1 5 fifth 4041 1 6 sixth 405connection master; 406 407** Do updates master ** 408 409update t31 set f5=555555555555555 where f3=6; 410update t31 set f2=2 where f3=2; 411update t31 set f1=NULL where f3=1; 412update ignore t31 set f3=NULL, f27=NULL, f35='f35 new value' where f3=3; 413Warnings: 414Warning 1048 Column 'f3' cannot be null 415 416** Delete from Master ** 417 418delete from t1; 419delete from t2; 420delete from t3; 421delete from t4; 422delete from t31; 423connection slave; 424select * from t31; 425f1 f2 f3 f4 426 427** Check slave status ** 428 429include/check_slave_is_running.inc 430connection master; 431 432**************************************** 433* columns in master at middle of table * 434* Expect: Proper error message * 435**************************************** 436 437** Stop and Reset Slave ** 438 439STOP SLAVE; 440RESET SLAVE; 441 442** create table slave side ** 443CREATE TABLE t10 (a INT PRIMARY KEY, b BLOB, c CHAR(5) 444) ENGINE='MyISAM'; 445 446** Connect to master and create table ** 447 448connection master; 449CREATE TABLE t10 (a INT KEY, b BLOB, f DOUBLE DEFAULT '233', 450c CHAR(5), e INT DEFAULT '1')ENGINE='MyISAM'; 451RESET MASTER; 452 453*** Start Slave *** 454connection slave; 455START SLAVE; 456 457*** Master Data Insert *** 458connection master; 459set @b1 = 'b1b1b1b1'; 460set @b1 = concat(@b1,@b1); 461INSERT INTO t10 () VALUES(1,@b1,DEFAULT,'Kyle',DEFAULT), 462(2,@b1,DEFAULT,'JOE',DEFAULT), 463(3,@b1,DEFAULT,'QA',DEFAULT); 464 465******************************************** 466*** Expect slave to fail with Error 1677 *** 467******************************************** 468 469connection slave; 470include/wait_for_slave_sql_error_and_skip.inc [errno=1677] 471Last_SQL_Error = 'Column 2 of table 'test.t10' cannot be converted from type 'double' to type 'char(5 octets) character set latin1'' 472 473*** Drop t10 *** 474connection master; 475DROP TABLE t10; 476connection slave; 477 478********************************************* 479* More columns in master at middle of table * 480* Expect: Proper error message * 481********************************************* 482 483*** Create t11 on slave *** 484STOP SLAVE; 485RESET SLAVE; 486CREATE TABLE t11 (a INT PRIMARY KEY, b BLOB, c VARCHAR(254) 487) ENGINE='MyISAM'; 488 489*** Create t11 on Master *** 490connection master; 491CREATE TABLE t11 (a INT KEY, b BLOB, f TEXT, 492c CHAR(5) DEFAULT 'test', e INT DEFAULT '1')ENGINE='MyISAM'; 493RESET MASTER; 494 495*** Start Slave *** 496connection slave; 497START SLAVE; 498 499*** Master Data Insert *** 500connection master; 501set @b1 = 'b1b1b1b1'; 502set @b1 = concat(@b1,@b1); 503INSERT INTO t11 () VALUES(1,@b1,'Testing is fun','Kyle',DEFAULT), 504(2,@b1,'Testing is cool','JOE',DEFAULT), 505(3,@b1,DEFAULT,'QA',DEFAULT); 506 507******************************************** 508*** Expect slave to fail with Error 1677 *** 509******************************************** 510 511connection slave; 512include/wait_for_slave_sql_error_and_skip.inc [errno=1677] 513Last_SQL_Error = 'Column 2 of table 'test.t11' cannot be converted from type 'blob' to type 'varchar(254 octets) character set latin1'' 514 515*** Drop t11 *** 516connection master; 517DROP TABLE t11; 518connection slave; 519 520********************************************* 521* More columns in master at middle of table * 522* Expect: This one should pass blob-text * 523********************************************* 524 525*** Create t12 on slave *** 526STOP SLAVE; 527RESET SLAVE; 528CREATE TABLE t12 (a INT PRIMARY KEY, b BLOB, c BLOB 529) ENGINE='MyISAM'; 530 531*** Create t12 on Master *** 532connection master; 533CREATE TABLE t12 (a INT KEY, b BLOB, f TEXT, 534c CHAR(5) DEFAULT 'test', e INT DEFAULT '1')ENGINE='MyISAM'; 535RESET MASTER; 536 537*** Start Slave *** 538connection slave; 539START SLAVE; 540 541*** Master Data Insert *** 542connection master; 543set @b1 = 'b1b1b1b1'; 544set @b1 = concat(@b1,@b1); 545INSERT INTO t12 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT), 546(2,@b1,'JOE',DEFAULT,DEFAULT), 547(3,@b1,'QA',DEFAULT,DEFAULT); 548 549SELECT a,hex(b),f,c,e FROM t12 ORDER BY a; 550a hex(b) f c e 5511 62316231623162316231623162316231 Kyle test 1 5522 62316231623162316231623162316231 JOE test 1 5533 62316231623162316231623162316231 QA test 1 554 555*** Select on Slave *** 556connection slave; 557SELECT a,hex(b),c FROM t12 ORDER BY a; 558a hex(b) c 5591 62316231623162316231623162316231 Kyle 5602 62316231623162316231623162316231 JOE 5613 62316231623162316231623162316231 QA 562 563*** Drop t12 *** 564connection master; 565DROP TABLE t12; 566connection slave; 567 568**************************************************** 569* - Alter Master adding columns at middle of table * 570* Expect: columns added * 571**************************************************** 572 573 574*** Create t14 on slave *** 575STOP SLAVE; 576RESET SLAVE; 577CREATE TABLE t14 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5) 578) ENGINE='MyISAM'; 579 580*** Create t14 on Master *** 581connection master; 582CREATE TABLE t14 (c1 INT KEY, c4 BLOB, c5 CHAR(5), 583c6 INT DEFAULT '1', 584c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP 585)ENGINE='MyISAM'; 586RESET MASTER; 587 588*** Start Slave *** 589connection slave; 590START SLAVE; 591 592*** Master Data Insert *** 593connection master; 594ALTER TABLE t14 ADD COLUMN c2 DECIMAL(8,2) AFTER c1; 595ALTER TABLE t14 ADD COLUMN c3 TEXT AFTER c2; 596 597set @b1 = 'b1b1b1b1'; 598set @b1 = concat(@b1,@b1); 599INSERT INTO t14 () VALUES(1,1.00,'Replication Testing Extra Col',@b1,'Kyle',DEFAULT,DEFAULT), 600(2,2.00,'This Test Should work',@b1,'JOE',DEFAULT,DEFAULT), 601(3,3.00,'If is does not, I will open a bug',@b1,'QA',DEFAULT,DEFAULT); 602 603SELECT c1,c2,c3,hex(c4),c5,c6,c7 FROM t14 ORDER BY c1; 604c1 c2 c3 hex(c4) c5 c6 c7 6051 1.00 Replication Testing Extra Col 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP 6062 2.00 This Test Should work 62316231623162316231623162316231 JOE 1 CURRENT_TIMESTAMP 6073 3.00 If is does not, I will open a bug 62316231623162316231623162316231 QA 1 CURRENT_TIMESTAMP 608 609*** Select on Slave **** 610connection slave; 611SELECT c1,c2,c3,hex(c4),c5 FROM t14 ORDER BY c1; 612c1 c2 c3 hex(c4) c5 6131 1.00 Replication Testing Extra Col 62316231623162316231623162316231 Kyle 6142 2.00 This Test Should work 62316231623162316231623162316231 JOE 6153 3.00 If is does not, I will open a bug 62316231623162316231623162316231 QA 616 617**************************************************** 618* - Alter Master Dropping columns from the middle. * 619* Expect: columns dropped * 620**************************************************** 621 622*** connect to master and drop columns *** 623connection master; 624ALTER TABLE t14 DROP COLUMN c2; 625ALTER TABLE t14 DROP COLUMN c7; 626 627*** Select from Master *** 628SELECT c1,c3,hex(c4),c5,c6 FROM t14 ORDER BY c1; 629c1 c3 hex(c4) c5 c6 6301 Replication Testing Extra Col 62316231623162316231623162316231 Kyle 1 6312 This Test Should work 62316231623162316231623162316231 JOE 1 6323 If is does not, I will open a bug 62316231623162316231623162316231 QA 1 633 634************ 635* Bug30415 * 636************ 637connection slave; 638include/wait_for_slave_sql_error.inc [errno=1091] 639Last_SQL_Error = 'Error 'Can't DROP COLUMN `c7`; check that it exists' on query. Default database: 'test'. Query: 'ALTER TABLE t14 DROP COLUMN c7'' 640STOP SLAVE; 641RESET SLAVE; 642 643*** Drop t14 *** 644DROP TABLE t14; 645connection master; 646DROP TABLE t14; 647RESET MASTER; 648connection slave; 649START SLAVE; 650 651************************************************* 652* - Alter Master adding columns at end of table * 653* Expect: Error 1054 * 654************************************************* 655 656*** Create t15 on slave *** 657STOP SLAVE; 658RESET SLAVE; 659CREATE TABLE t15 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5) 660) ENGINE='MyISAM'; 661 662*** Create t15 on Master *** 663connection master; 664CREATE TABLE t15 (c1 INT KEY, c4 BLOB, c5 CHAR(5), 665c6 INT DEFAULT '1', 666c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP 667)ENGINE='MyISAM'; 668RESET MASTER; 669 670*** Start Slave *** 671connection slave; 672START SLAVE; 673 674*** Master Data Insert *** 675connection master; 676ALTER TABLE t15 ADD COLUMN c2 DECIMAL(8,2) AFTER c7; 677set @b1 = 'b1b1b1b1'; 678set @b1 = concat(@b1,@b1); 679INSERT INTO t15 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT,3.00), 680(2,@b1,'JOE',DEFAULT,DEFAULT,3.00), 681(3,@b1,'QA',DEFAULT,DEFAULT,3.00); 682SELECT c1,hex(c4),c5,c6,c7,c2 FROM t15 ORDER BY c1; 683c1 hex(c4) c5 c6 c7 c2 6841 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP 3.00 6852 62316231623162316231623162316231 JOE 1 CURRENT_TIMESTAMP 3.00 6863 62316231623162316231623162316231 QA 1 CURRENT_TIMESTAMP 3.00 687 688******************************************** 689*** Expect slave to fail with Error 1054 *** 690******************************************** 691 692connection slave; 693include/wait_for_slave_sql_error.inc [errno=1054] 694Last_SQL_Error = 'Error 'Unknown column 'c7' in 't15'' on query. Default database: 'test'. Query: 'ALTER TABLE t15 ADD COLUMN c2 DECIMAL(8,2) AFTER c7'' 695STOP SLAVE; 696RESET SLAVE; 697 698*** Drop t15 *** 699DROP TABLE t15; 700connection master; 701DROP TABLE t15; 702RESET MASTER; 703connection slave; 704START SLAVE; 705 706************************************************ 707* - Create index on Master column not on slave * 708* Expect:Warning * 709************************************************ 710 711*** Create t16 on slave *** 712STOP SLAVE; 713RESET SLAVE; 714CREATE TABLE t16 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5) 715) ENGINE='MyISAM'; 716 717*** Create t16 on Master *** 718connection master; 719CREATE TABLE t16 (c1 INT KEY, c4 BLOB, c5 CHAR(5), 720c6 INT DEFAULT '1', 721c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP 722)ENGINE='MyISAM'; 723RESET MASTER; 724 725*** Start Slave *** 726connection slave; 727START SLAVE; 728 729*** Master Create Index and Data Insert *** 730connection master; 731CREATE INDEX part_of_c6 ON t16 (c6); 732set @b1 = 'b1b1b1b1'; 733set @b1 = concat(@b1,@b1); 734INSERT INTO t16 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT), 735(2,@b1,'JOE',2,DEFAULT), 736(3,@b1,'QA',3,DEFAULT); 737SELECT c1,hex(c4),c5,c6,c7 FROM t16 ORDER BY c1; 738c1 hex(c4) c5 c6 c7 7391 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP 7402 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP 7413 62316231623162316231623162316231 QA 3 CURRENT_TIMESTAMP 742 743***************** 744*** BUG 30434 *** 745***************** 746 747connection slave; 748include/wait_for_slave_sql_error.inc [errno=1072] 749Last_SQL_Error = 'Error 'Key column 'c6' doesn't exist in table' on query. Default database: 'test'. Query: 'CREATE INDEX part_of_c6 ON t16 (c6)'' 750STOP SLAVE; 751RESET SLAVE; 752 753*** Drop t16 *** 754DROP TABLE t16; 755connection master; 756DROP TABLE t16; 757RESET MASTER; 758connection slave; 759START SLAVE; 760 761***************************************************** 762* - Delete rows using column on Master not on slave * 763* Expect: Rows Deleted * 764***************************************************** 765 766*** Create t17 on slave *** 767STOP SLAVE; 768RESET SLAVE; 769CREATE TABLE t17 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5) 770) ENGINE='MyISAM'; 771 772*** Create t17 on Master *** 773connection master; 774CREATE TABLE t17 (c1 INT KEY, c4 BLOB, c5 CHAR(5), 775c6 INT DEFAULT '1', 776c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP 777)ENGINE='MyISAM'; 778RESET MASTER; 779 780*** Start Slave *** 781connection slave; 782START SLAVE; 783 784*** Master Data Insert *** 785connection master; 786set @b1 = 'b1b1b1b1'; 787set @b1 = concat(@b1,@b1); 788INSERT INTO t17 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT), 789(2,@b1,'JOE',2,DEFAULT), 790(3,@b1,'QA',3,DEFAULT); 791SELECT c1,hex(c4),c5,c6,c7 FROM t17 ORDER BY c1; 792c1 hex(c4) c5 c6 c7 7931 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP 7942 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP 7953 62316231623162316231623162316231 QA 3 CURRENT_TIMESTAMP 796 797** Select * from Slave ** 798connection slave; 799SELECT c1,hex(c4),c5 FROM t17 ORDER BY c1; 800c1 hex(c4) c5 8011 62316231623162316231623162316231 Kyle 8022 62316231623162316231623162316231 JOE 8033 62316231623162316231623162316231 QA 804 805** Delete from master ** 806connection master; 807DELETE FROM t17 WHERE c6 = 3; 808SELECT c1,hex(c4),c5,c6,c7 FROM t17 ORDER BY c1; 809c1 hex(c4) c5 c6 c7 8101 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP 8112 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP 812 813** Check slave ** 814connection slave; 815SELECT c1,hex(c4),c5 FROM t17 ORDER BY c1; 816c1 hex(c4) c5 8171 62316231623162316231623162316231 Kyle 8182 62316231623162316231623162316231 JOE 819connection master; 820DROP TABLE t17; 821connection slave; 822 823 824***************************************************** 825* - Update row using column on Master not on slave * 826* Expect: Rows updated * 827***************************************************** 828 829** Bug30674 ** 830 831*** Create t18 on slave *** 832 833STOP SLAVE; 834RESET SLAVE; 835CREATE TABLE t18 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5) 836) ENGINE='MyISAM'; 837 838*** Create t18 on Master *** 839connection master; 840CREATE TABLE t18 (c1 INT KEY, c4 BLOB, c5 CHAR(5), 841c6 INT DEFAULT '1', 842c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP 843)ENGINE='MyISAM'; 844RESET MASTER; 845 846*** Start Slave *** 847connection slave; 848START SLAVE; 849 850*** Master Data Insert *** 851connection master; 852set @b1 = 'b1b1b1b1'; 853set @b1 = concat(@b1,@b1); 854INSERT INTO t18 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT), 855(2,@b1,'JOE',2,DEFAULT), 856(3,@b1,'QA',3,DEFAULT); 857SELECT c1,hex(c4),c5,c6,c7 FROM t18 ORDER BY c1; 858c1 hex(c4) c5 c6 c7 8591 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP 8602 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP 8613 62316231623162316231623162316231 QA 3 CURRENT_TIMESTAMP 862 863** Select * from Slave ** 864connection slave; 865SELECT c1,hex(c4),c5 FROM t18 ORDER BY c1; 866c1 hex(c4) c5 8671 62316231623162316231623162316231 Kyle 8682 62316231623162316231623162316231 JOE 8693 62316231623162316231623162316231 QA 870 871** update from master ** 872connection master; 873UPDATE t18 SET c5 = 'TEST' WHERE c6 = 3; 874SELECT c1,hex(c4),c5,c6,c7 FROM t18 ORDER BY c1; 875c1 hex(c4) c5 c6 c7 8761 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP 8772 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP 8783 62316231623162316231623162316231 TEST 3 CURRENT_TIMESTAMP 879 880** Check slave ** 881connection slave; 882SELECT c1,hex(c4),c5 FROM t18 ORDER BY c1; 883c1 hex(c4) c5 8841 62316231623162316231623162316231 Kyle 8852 62316231623162316231623162316231 JOE 8863 62316231623162316231623162316231 TEST 887connection master; 888DROP TABLE t18; 889connection slave; 890 891 892***************************************************** 893* - Insert UUID column on Master not on slave * 894* Expect: Rows inserted * 895***************************************************** 896 897*** Create t5 on slave *** 898STOP SLAVE; 899RESET SLAVE; 900CREATE TABLE t5 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5) 901) ENGINE='MyISAM'; 902 903*** Create t5 on Master *** 904connection master; 905CREATE TABLE t5 (c1 INT KEY, c4 BLOB, c5 CHAR(5), 906c6 LONG, 907c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP 908)ENGINE='MyISAM'; 909RESET MASTER; 910 911*** Start Slave *** 912connection slave; 913START SLAVE; 914 915*** Master Data Insert *** 916connection master; 917set @b1 = 'b1b1b1b1'; 918INSERT INTO t5 () VALUES(1,@b1,'Kyle',UUID(),DEFAULT), 919(2,@b1,'JOE',UUID(),DEFAULT), 920(3,@b1,'QA',UUID(),DEFAULT); 921SELECT c1,hex(c4),c5,c6,c7 FROM t5 ORDER BY c1; 922c1 hex(c4) c5 c6 c7 9231 6231623162316231 Kyle UUID TIME 9242 6231623162316231 JOE UUID TIME 9253 6231623162316231 QA UUID TIME 926 927** Select * from Slave ** 928connection slave; 929SELECT c1,hex(c4),c5 FROM t5 ORDER BY c1; 930c1 hex(c4) c5 9311 6231623162316231 Kyle 9322 6231623162316231 JOE 9333 6231623162316231 QA 934connection master; 935DROP TABLE t5; 936connection slave; 937connection master; 938include/rpl_end.inc 939