1
#############################################################
3
#############################################################
4
# Purpose: To test having extra columns on the master WL#3915
5
# engine inspecific sourced part
6
#############################################################
8
# Change: Cleanup and extend testing
9
#############################################################
10
# TODO: partition specific
11
# -- source include/have_partition.inc
12
# Note: Will be done in different test due to NDB using this
14
############################################################
16
########### Clean up ################
19
DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t10,t11,t12,t13,t14,t15,t16,t17,t18,t31;
24
# Setup differently defined tables on master and slave
27
# Def on master: t (f_1 type_m_1,... f_s type_m_s, f_s1, f_m)
28
# Def on slave: t (f_1 type_s_1,... f_s type_s_s)
29
# where type_mi,type_si (0 < i-1 <s1) pairs are compatible types (WL#3228)
30
# Arbitrary paramaters of the test are:
32
# 2. the types of the extra master's column f_s1,..., f_m
33
# 3. the numbers of common columns `s'
34
# 4. and extra columns `m' are par
38
# 5. vary the common columns type within compatible ranges.
41
# constant size column type:
64
# variable size column types:
79
let $binformat = `SHOW VARIABLES LIKE '%binlog_format%'`;
81
--echo ***********************************************************
82
--echo ***********************************************************
83
--echo ***************** Start of Testing ************************
84
--echo ***********************************************************
85
--echo ***********************************************************
86
--echo * This test format == $binformat and engine == $engine_type
87
--echo ***********************************************************
88
--echo ***********************************************************
90
--echo ***** Testing more columns on the Master *****
93
eval CREATE TABLE t1 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
95
f5 FLOAT DEFAULT '2.00',
96
f6 CHAR(4) DEFAULT 'TEST',
101
f11 VARBINARY(64))ENGINE=$engine_type;
103
--echo * Alter Table on Slave and drop columns f5 through f11 *
105
sync_slave_with_master;
106
alter table t1 drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11;
109
--echo * Insert data in Master then update and delete some rows*
116
eval INSERT INTO t1 VALUES ($j, $j, $j, 'second', 2.0, 'kaks', 2,
117
'got stolen from the paradise',
118
'very fat blob', b'01010101010101',
125
eval update t1 set f4= 'next' where f1=$j;
128
eval delete from t1 where f1=$j;
133
--echo * Select count and 20 rows from Master *
135
SELECT COUNT(*) FROM t1;
137
SELECT f1,f2,f3,f4,f5,f6,f7,f8,f9,
138
hex(f10),hex(f11) FROM t1 ORDER BY f3 LIMIT 20;
141
sync_slave_with_master;
143
--echo * Select count and 20 rows from Slave *
145
SELECT COUNT(*) FROM t1;
147
SELECT * FROM t1 ORDER BY f3 LIMIT 20;
150
--echo * Show Slave Status *
152
--replace_column 1 # 4 # 7 # 8 # 9 # 22 # 23 # 33 # 35 # 36 #
153
--query_vertical show slave status;
156
### Altering table def scenario
158
--echo ***** Testing Altering table def scenario *****
163
eval CREATE TABLE t2 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
165
f5 DOUBLE DEFAULT '2.00',
166
f6 ENUM('a', 'b', 'c') default 'a',
167
f7 DECIMAL(17,9) default '1000.00',
169
f9 NUMERIC(6,4) default '2000.00',
171
f11 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',
172
f12 SET('a', 'b', 'c') default 'b')
175
eval CREATE TABLE t3 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
177
f5 DOUBLE DEFAULT '2.00',
178
f6 ENUM('a', 'b', 'c') default 'a',
181
f11 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',
182
f12 SET('a', 'b', 'c') default 'b')
187
eval CREATE TABLE t4 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
189
f5 DOUBLE DEFAULT '2.00',
190
f6 DECIMAL(17,9) default '1000.00',
192
f8 NUMERIC(6,4) default '2000.00',
194
f10 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',
199
eval CREATE TABLE t31 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
228
f30 ENUM ('a','b', 'c') default 'a',
231
f33 SET ('a', 'b', 'c') default 'b',
234
) ENGINE=$engine_type;
236
--echo ** Alter tables on slave and drop columns **
239
sync_slave_with_master;
240
alter table t2 drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11, drop
242
alter table t3 drop f5, drop f6, drop f8, drop f10, drop f11, drop f12;
243
alter table t4 drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11;
246
drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11,
247
drop f12, drop f13, drop f14, drop f15, drop f16, drop f17, drop f18,
248
drop f19, drop f20, drop f21, drop f22, drop f23, drop f24, drop f25,
249
drop f26, drop f27, drop f28, drop f29, drop f30, drop f31, drop f32,
250
drop f33, drop f34, drop f35;
253
--echo ** Insert Data into Master **
255
INSERT into t2 set f1=1, f2=1, f3=1, f4='first', f8='f8: medium size blob', f10='f10:
257
INSERT into t2 values (2, 2, 2, 'second',
258
2.0, 'b', 2000.0002, 'f8: medium size blob', 2000, 'f10: some var char',
261
3.0, 'b', 3000.0003, 'f8: medium size blob', 3000, 'f10: some var char',
263
INSERT into t3 set f1=1, f2=1, f3=1, f4='first', f10='f10: some var char';
264
INSERT into t4 set f1=1, f2=1, f3=1, f4='first', f7='f7: medium size blob', f10='f10:
266
INSERT into t31 set f1=1, f2=1, f3=1, f4='first';
267
INSERT into t31 set f1=1, f2=1, f3=2, f4='second',
268
f9=2.2, f10='seven samurai', f28=222.222, f35='222';
269
INSERT into t31 values (1, 1, 3, 'third',
270
/* f5 BIGINT, */ 333333333333333333333333,
271
/* f6 BLOB, */ '3333333333333333333333',
272
/* f7 DATE, */ '2007-07-18',
273
/* f8 DATETIME, */ "2007-07-18",
274
/* f9 FLOAT, */ 3.33333333,
275
/* f10 INT, */ 333333333,
276
/* f11 LONGBLOB, */ '3333333333333333333',
277
/* f12 LONGTEXT, */ '3333333333333333333',
278
/* f13 MEDIUMBLOB, */ '3333333333333333333',
279
/* f14 MEDIUMINT, */ 33,
280
/* f15 MEDIUMTEXT, */ 3.3,
282
/* f17 SMALLINT, */ 3,
283
/* f18 TEXT, */ '33',
284
/* f19 TIME, */ '2:59:58.999',
285
/* f20 TIMESTAMP, */ 20000303000000,
286
/* f21 TINYBLOB, */ '3333',
287
/* f22 TINYINT, */ 3,
288
/* f23 TINYTEXT, */ '3',
289
/* f24 YEAR, */ 3000,
290
/* f25 BINARY(255), */ 'three_33333',
291
/* f26 BIT(64), */ b'011',
292
/* f27 CHAR(255), */ 'three',
293
/* f28 DECIMAL(30,7), */ 3.333,
294
/* f29 DOUBLE, */ 3.333333333333333333333333333,
295
/* f30 ENUM ('a','b','c')*/ 'c',
296
/* f31 FLOAT, */ 3.0,
297
/* f32 NUMERIC(17,9), */ 3.3333,
298
/* f33 SET ('a','b','c'),*/ 'c',
299
/*f34 VARBINARY(1025),*/ '3333 minus 3',
300
/*f35 VARCHAR(257),*/ 'three times three'
303
INSERT into t31 values (1, 1, 4, 'fourth',
304
/* f5 BIGINT, */ 333333333333333333333333,
305
/* f6 BLOB, */ '3333333333333333333333',
306
/* f7 DATE, */ '2007-07-18',
307
/* f8 DATETIME, */ "2007-07-18",
308
/* f9 FLOAT, */ 3.33333333,
309
/* f10 INT, */ 333333333,
310
/* f11 LONGBLOB, */ '3333333333333333333',
311
/* f12 LONGTEXT, */ '3333333333333333333',
312
/* f13 MEDIUMBLOB, */ '3333333333333333333',
313
/* f14 MEDIUMINT, */ 33,
314
/* f15 MEDIUMTEXT, */ 3.3,
316
/* f17 SMALLINT, */ 3,
317
/* f18 TEXT, */ '33',
318
/* f19 TIME, */ '2:59:58.999',
319
/* f20 TIMESTAMP, */ 20000303000000,
320
/* f21 TINYBLOB, */ '3333',
321
/* f22 TINYINT, */ 3,
322
/* f23 TINYTEXT, */ '3',
323
/* f24 YEAR, */ 3000,
324
/* f25 BINARY(255), */ 'three_33333',
325
/* f26 BIT(64), */ b'011',
326
/* f27 CHAR(255), */ 'three',
327
/* f28 DECIMAL(30,7), */ 3.333,
328
/* f29 DOUBLE, */ 3.333333333333333333333333333,
329
/* f30 ENUM ('a','b','c')*/ 'c',
330
/* f31 FLOAT, */ 3.0,
331
/* f32 NUMERIC(17,9), */ 3.3333,
332
/* f33 SET ('a','b','c'),*/ 'c',
333
/*f34 VARBINARY(1025),*/ '3333 minus 3',
334
/*f35 VARCHAR(257),*/ 'three times three'
337
/* f5 BIGINT, */ 333333333333333333333333,
338
/* f6 BLOB, */ '3333333333333333333333',
339
/* f7 DATE, */ '2007-07-18',
340
/* f8 DATETIME, */ "2007-07-18",
341
/* f9 FLOAT, */ 3.33333333,
342
/* f10 INT, */ 333333333,
343
/* f11 LONGBLOB, */ '3333333333333333333',
344
/* f12 LONGTEXT, */ '3333333333333333333',
345
/* f13 MEDIUMBLOB, */ '3333333333333333333',
346
/* f14 MEDIUMINT, */ 33,
347
/* f15 MEDIUMTEXT, */ 3.3,
349
/* f17 SMALLINT, */ 3,
350
/* f18 TEXT, */ '33',
351
/* f19 TIME, */ '2:59:58.999',
352
/* f20 TIMESTAMP, */ 20000303000000,
353
/* f21 TINYBLOB, */ '3333',
354
/* f22 TINYINT, */ 3,
355
/* f23 TINYTEXT, */ '3',
356
/* f24 YEAR, */ 3000,
357
/* f25 BINARY(255), */ 'three_33333',
358
/* f26 BIT(64), */ b'011',
359
/* f27 CHAR(255), */ 'three',
360
/* f28 DECIMAL(30,7), */ 3.333,
361
/* f29 DOUBLE, */ 3.333333333333333333333333333,
362
/* f30 ENUM ('a','b','c')*/ 'c',
363
/* f31 FLOAT, */ 3.0,
364
/* f32 NUMERIC(17,9), */ 3.3333,
365
/* f33 SET ('a','b','c'),*/ 'c',
366
/*f34 VARBINARY(1025),*/ '3333 minus 3',
367
/*f35 VARCHAR(257),*/ 'three times three'
370
/* f5 BIGINT, */ NULL,
371
/* f6 BLOB, */ '3333333333333333333333',
372
/* f7 DATE, */ '2007-07-18',
373
/* f8 DATETIME, */ "2007-07-18",
374
/* f9 FLOAT, */ 3.33333333,
375
/* f10 INT, */ 333333333,
376
/* f11 LONGBLOB, */ '3333333333333333333',
377
/* f12 LONGTEXT, */ '3333333333333333333',
378
/* f13 MEDIUMBLOB, */ '3333333333333333333',
379
/* f14 MEDIUMINT, */ 33,
380
/* f15 MEDIUMTEXT, */ 3.3,
382
/* f17 SMALLINT, */ 3,
383
/* f18 TEXT, */ '33',
384
/* f19 TIME, */ '2:59:58.999',
385
/* f20 TIMESTAMP, */ 20000303000000,
386
/* f21 TINYBLOB, */ '3333',
387
/* f22 TINYINT, */ 3,
388
/* f23 TINYTEXT, */ '3',
389
/* f24 YEAR, */ 3000,
390
/* f25 BINARY(255), */ 'three_33333',
391
/* f26 BIT(64), */ b'011',
392
/* f27 CHAR(255), */ 'three',
393
/* f28 DECIMAL(30,7), */ 3.333,
394
/* f29 DOUBLE, */ 3.333333333333333333333333333,
395
/* f30 ENUM ('a','b','c')*/ 'c',
396
/* f31 FLOAT, */ 3.0,
397
/* f32 NUMERIC(17,9), */ 3.3333,
398
/* f33 SET ('a','b','c'),*/ 'c',
399
/*f34 VARBINARY(1025),*/ '3333 minus 3',
400
/*f35 VARCHAR(257),*/ NULL
403
--echo ** Sync slave with master **
404
--echo ** Do selects from tables **
407
sync_slave_with_master;
409
select * from t1 order by f3;
410
select * from t2 order by f1;
411
select * from t3 order by f1;
412
select * from t4 order by f1;
413
select * from t31 order by f3;
417
--echo ** Do updates master **
419
update t31 set f5=555555555555555 where f3=6;
420
update t31 set f2=2 where f3=2;
421
update t31 set f1=NULL where f3=1;
422
update t31 set f3=NULL, f27=NULL, f35='f35 new value' where f3=3;
425
--echo ** Delete from Master **
435
--echo ** Check slave status **
438
sync_slave_with_master;
441
--replace_result $MASTER_MYPORT MASTER_PORT
442
--replace_column 1 # 4 # 7 # 8 # 9 # 22 # 23 # 33 # 35 # 36 #
443
--query_vertical show slave status;
450
DROP TABLE t1,t2,t3,t4,t31;
452
######################################################
454
sync_slave_with_master;
458
--echo ****************************************
459
--echo * columns in master at middle of table *
460
--echo * Expect: Proper error message *
461
--echo ****************************************
463
--echo ** Stop and Reset Slave **
468
--echo ** create table slave side **
469
eval CREATE TABLE t10 (a INT PRIMARY KEY, b BLOB, c CHAR(5)
470
) ENGINE=$engine_type;
473
--echo ** Connect to master and create table **
476
eval CREATE TABLE t10 (a INT KEY, b BLOB, f DOUBLE DEFAULT '233',
477
c CHAR(5), e INT DEFAULT '1')ENGINE=$engine_type;
481
--echo *** Start Slave ***
486
--echo *** Master Data Insert ***
488
set @b1 = 'b1b1b1b1';
489
set @b1 = concat(@b1,@b1);
490
INSERT INTO t10 () VALUES(1,@b1,DEFAULT,'Kyle',DEFAULT),
491
(2,@b1,DEFAULT,'JOE',DEFAULT),
492
(3,@b1,DEFAULT,'QA',DEFAULT);
495
--echo ********************************************
496
--echo *** Expect slave to fail with Error 1523 ***
497
--echo ********************************************
500
source include/wait_for_slave_sql_to_stop.inc;
501
--replace_result $MASTER_MYPORT MASTER_PORT
502
--replace_column 1 # 4 # 7 # 8 # 9 # 22 # 23 # 33 # 35 # 36 #
503
--query_vertical SHOW SLAVE STATUS
504
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
508
--echo *** Drop t10 ***
511
sync_slave_with_master;
513
############################################
514
############## Continued ###################
515
############################################
517
--echo *********************************************
518
--echo * More columns in master at middle of table *
519
--echo * Expect: Proper error message *
520
--echo *********************************************
522
--echo *** Create t11 on slave ***
526
eval CREATE TABLE t11 (a INT PRIMARY KEY, b BLOB, c VARCHAR(254)
527
) ENGINE=$engine_type;
530
--echo *** Create t11 on Master ***
532
eval CREATE TABLE t11 (a INT KEY, b BLOB, f TEXT,
533
c CHAR(5) DEFAULT 'test', e INT DEFAULT '1')ENGINE=$engine_type;
538
--echo *** Start Slave ***
543
--echo *** Master Data Insert ***
545
set @b1 = 'b1b1b1b1';
546
set @b1 = concat(@b1,@b1);
547
INSERT INTO t11 () VALUES(1,@b1,'Testing is fun','Kyle',DEFAULT),
548
(2,@b1,'Testing is cool','JOE',DEFAULT),
549
(3,@b1,DEFAULT,'QA',DEFAULT);
552
--echo ********************************************
553
--echo *** Expect slave to fail with Error 1523 ***
554
--echo ********************************************
557
source include/wait_for_slave_sql_to_stop.inc;
558
--replace_result $MASTER_MYPORT MASTER_PORT
559
--replace_column 1 # 4 # 7 # 8 # 9 # 22 # 23 # 33 # 35 # 36 #
560
--query_vertical SHOW SLAVE STATUS
561
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
565
--echo *** Drop t11 ***
568
sync_slave_with_master;
570
############################################
571
############## Continued ###################
572
############################################
574
--echo *********************************************
575
--echo * More columns in master at middle of table *
576
--echo * Expect: This one should pass blob-text *
577
--echo *********************************************
579
--echo *** Create t12 on slave ***
582
eval CREATE TABLE t12 (a INT PRIMARY KEY, b BLOB, c BLOB
583
) ENGINE=$engine_type;
586
--echo *** Create t12 on Master ***
588
eval CREATE TABLE t12 (a INT KEY, b BLOB, f TEXT,
589
c CHAR(5) DEFAULT 'test', e INT DEFAULT '1')ENGINE=$engine_type;
594
--echo *** Start Slave ***
599
--echo *** Master Data Insert ***
601
set @b1 = 'b1b1b1b1';
602
set @b1 = concat(@b1,@b1);
603
INSERT INTO t12 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),
604
(2,@b1,'JOE',DEFAULT,DEFAULT),
605
(3,@b1,'QA',DEFAULT,DEFAULT);
607
SELECT a,hex(b),f,c,e FROM t12 ORDER BY a;
610
--echo *** Select on Slave ***
611
sync_slave_with_master;
612
SELECT a,hex(b),c FROM t12 ORDER BY a;
615
--echo *** Drop t12 ***
618
sync_slave_with_master;
620
############################################
621
############## Continued ###################
622
############################################
624
--echo ****************************************************
625
--echo * - Alter Master adding columns at middle of table *
626
--echo * Expect: columns added *
627
--echo ****************************************************
630
--echo *** Create t14 on slave ***
633
eval CREATE TABLE t14 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
634
) ENGINE=$engine_type;
637
--echo *** Create t14 on Master ***
639
eval CREATE TABLE t14 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
641
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
642
)ENGINE=$engine_type;
647
--echo *** Start Slave ***
652
--echo *** Master Data Insert ***
654
ALTER TABLE t14 ADD COLUMN c2 DECIMAL(8,2) AFTER c1;
655
ALTER TABLE t14 ADD COLUMN c3 TEXT AFTER c2;
657
set @b1 = 'b1b1b1b1';
658
set @b1 = concat(@b1,@b1);
659
INSERT INTO t14 () VALUES(1,1.00,'Replication Testing Extra Col',@b1,'Kyle',DEFAULT,DEFAULT),
660
(2,2.00,'This Test Should work',@b1,'JOE',DEFAULT,DEFAULT),
661
(3,3.00,'If is does not, I will open a bug',@b1,'QA',DEFAULT,DEFAULT);
663
--replace_column 7 CURRENT_TIMESTAMP
664
SELECT c1,c2,c3,hex(c4),c5,c6,c7 FROM t14 ORDER BY c1;
667
--echo *** Select on Slave ****
668
sync_slave_with_master;
669
SELECT c1,c2,c3,hex(c4),c5 FROM t14 ORDER BY c1;
671
####################################################
673
--echo ****************************************************
674
--echo * - Alter Master Dropping columns from the middle. *
675
--echo * Expect: columns dropped *
676
--echo ****************************************************
678
--echo *** connect to master and drop columns ***
680
ALTER TABLE t14 DROP COLUMN c2;
681
ALTER TABLE t14 DROP COLUMN c7;
683
--echo *** Select from Master ***
684
SELECT c1,c3,hex(c4),c5,c6 FROM t14 ORDER BY c1;
690
# Uncomment below once fixed
692
#--echo *** Select from Slave ***
693
#sync_slave_with_master;
694
#SELECT c1,c2,c3,hex(c4),c5 FROM t14 ORDER BY c1;
697
# Remove below once fixed
698
#***************************
700
source include/wait_for_slave_sql_to_stop.inc;
701
--replace_result $MASTER_MYPORT MASTER_PORT
702
--replace_column 1 # 4 # 7 # 8 # 9 # 22 # 23 # 33 # 35 # 36 #
703
--query_vertical SHOW SLAVE STATUS
704
#***************************
710
--echo *** Drop t14 ***
720
#################################################
722
--echo *************************************************
723
--echo * - Alter Master adding columns at end of table *
724
--echo * Expect: Error 1054 *
725
--echo *************************************************
727
--echo *** Create t15 on slave ***
730
eval CREATE TABLE t15 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
731
) ENGINE=$engine_type;
734
--echo *** Create t15 on Master ***
736
eval CREATE TABLE t15 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
738
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
739
)ENGINE=$engine_type;
744
--echo *** Start Slave ***
749
--echo *** Master Data Insert ***
751
ALTER TABLE t15 ADD COLUMN c2 DECIMAL(8,2) AFTER c7;
752
set @b1 = 'b1b1b1b1';
753
set @b1 = concat(@b1,@b1);
754
INSERT INTO t15 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT,3.00),
755
(2,@b1,'JOE',DEFAULT,DEFAULT,3.00),
756
(3,@b1,'QA',DEFAULT,DEFAULT,3.00);
757
--replace_column 5 CURRENT_TIMESTAMP
758
SELECT c1,hex(c4),c5,c6,c7,c2 FROM t15 ORDER BY c1;
761
--echo ********************************************
762
--echo *** Expect slave to fail with Error 1054 ***
763
--echo ********************************************
766
source include/wait_for_slave_sql_to_stop.inc;
767
--replace_result $MASTER_MYPORT MASTER_PORT
768
--replace_column 1 # 4 # 7 # 8 # 9 # 22 # 23 # 33 # 35 # 36 #
769
--query_vertical SHOW SLAVE STATUS
774
--echo *** Drop t15 ***
784
####################################################
786
--echo ************************************************
787
--echo * - Create index on Master column not on slave *
788
--echo * Expect:Warning *
789
--echo ************************************************
791
--echo *** Create t16 on slave ***
794
eval CREATE TABLE t16 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
795
) ENGINE=$engine_type;
798
--echo *** Create t16 on Master ***
800
eval CREATE TABLE t16 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
802
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
803
)ENGINE=$engine_type;
808
--echo *** Start Slave ***
813
--echo *** Master Create Index and Data Insert ***
815
CREATE INDEX part_of_c6 ON t16 (c6);
816
set @b1 = 'b1b1b1b1';
817
set @b1 = concat(@b1,@b1);
818
INSERT INTO t16 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),
819
(2,@b1,'JOE',2,DEFAULT),
820
(3,@b1,'QA',3,DEFAULT);
821
--replace_column 5 CURRENT_TIMESTAMP
822
SELECT c1,hex(c4),c5,c6,c7 FROM t16 ORDER BY c1;
824
# Uncomment the below when bug 30434 is patched
826
#--echo *** Select on Slave ****
827
#sync_slave_with_master;
828
#SELECT c1,hex(c4),c5 FROM t16 ORDER BY c1;
830
#--echo *** Drop t16 ***
833
#sync_slave_with_master;
835
# Remove the below when bug 30434 is patched
836
#*******************************************
838
--echo *****************
839
--echo *** BUG 30434 ***
840
--echo *****************
843
source include/wait_for_slave_sql_to_stop.inc;
844
--replace_result $MASTER_MYPORT MASTER_PORT
845
--replace_column 1 # 4 # 7 # 8 # 9 # 22 # 23 # 33 # 35 # 36 #
846
--query_vertical SHOW SLAVE STATUS
851
--echo *** Drop t16 ***
860
#*******************************************
862
####################################################
864
--echo *****************************************************
865
--echo * - Delete rows using column on Master not on slave *
866
--echo * Expect: Rows Deleted *
867
--echo *****************************************************
869
--echo *** Create t17 on slave ***
872
eval CREATE TABLE t17 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
873
) ENGINE=$engine_type;
876
--echo *** Create t17 on Master ***
878
eval CREATE TABLE t17 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
880
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
881
)ENGINE=$engine_type;
886
--echo *** Start Slave ***
891
--echo *** Master Data Insert ***
893
set @b1 = 'b1b1b1b1';
894
set @b1 = concat(@b1,@b1);
895
INSERT INTO t17 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),
896
(2,@b1,'JOE',2,DEFAULT),
897
(3,@b1,'QA',3,DEFAULT);
898
--replace_column 5 CURRENT_TIMESTAMP
899
SELECT c1,hex(c4),c5,c6,c7 FROM t17 ORDER BY c1;
902
--echo ** Select * from Slave **
903
sync_slave_with_master;
904
SELECT c1,hex(c4),c5 FROM t17 ORDER BY c1;
907
--echo ** Delete from master **
909
DELETE FROM t17 WHERE c6 = 3;
910
--replace_column 5 CURRENT_TIMESTAMP
911
SELECT c1,hex(c4),c5,c6,c7 FROM t17 ORDER BY c1;
914
--echo ** Check slave **
915
sync_slave_with_master;
916
SELECT c1,hex(c4),c5 FROM t17 ORDER BY c1;
921
sync_slave_with_master;
924
####################################################
926
--echo *****************************************************
927
--echo * - Update row using column on Master not on slave *
928
--echo * Expect: Rows updated *
929
--echo *****************************************************
931
--echo ** Bug30674 **
933
--echo *** Create t18 on slave ***
938
eval CREATE TABLE t18 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
939
) ENGINE=$engine_type;
942
--echo *** Create t18 on Master ***
944
eval CREATE TABLE t18 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
946
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
947
)ENGINE=$engine_type;
952
--echo *** Start Slave ***
957
--echo *** Master Data Insert ***
959
set @b1 = 'b1b1b1b1';
960
set @b1 = concat(@b1,@b1);
962
INSERT INTO t18 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),
963
(2,@b1,'JOE',2,DEFAULT),
964
(3,@b1,'QA',3,DEFAULT);
965
--replace_column 5 CURRENT_TIMESTAMP
966
SELECT c1,hex(c4),c5,c6,c7 FROM t18 ORDER BY c1;
969
--echo ** Select * from Slave **
970
sync_slave_with_master;
971
SELECT c1,hex(c4),c5 FROM t18 ORDER BY c1;
974
--echo ** update from master **
976
UPDATE t18 SET c5 = 'TEST' WHERE c6 = 3;
978
--replace_column 5 CURRENT_TIMESTAMP
979
SELECT c1,hex(c4),c5,c6,c7 FROM t18 ORDER BY c1;
982
--echo ** Check slave **
983
sync_slave_with_master;
984
SELECT c1,hex(c4),c5 FROM t18 ORDER BY c1;
988
sync_slave_with_master;
991
####################################################
993
--echo *****************************************************
994
--echo * - Insert UUID column on Master not on slave *
995
--echo * Expect: Rows inserted *
996
--echo *****************************************************
998
--echo *** Create t5 on slave ***
1001
eval CREATE TABLE t5 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
1002
) ENGINE=$engine_type;
1005
--echo *** Create t5 on Master ***
1007
eval CREATE TABLE t5 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
1009
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
1010
)ENGINE=$engine_type;
1015
--echo *** Start Slave ***
1020
--echo *** Master Data Insert ***
1022
set @b1 = 'b1b1b1b1';
1023
INSERT INTO t5 () VALUES(1,@b1,'Kyle',UUID(),DEFAULT),
1024
(2,@b1,'JOE',UUID(),DEFAULT),
1025
(3,@b1,'QA',UUID(),DEFAULT);
1026
--replace_column 4 UUID 5 TIME
1027
SELECT c1,hex(c4),c5,c6,c7 FROM t5 ORDER BY c1;
1030
--echo ** Select * from Slave **
1031
sync_slave_with_master;
1032
SELECT c1,hex(c4),c5 FROM t5 ORDER BY c1;
1036
sync_slave_with_master;
1039
# END of 5.1 tests case