1
#############################################################
2
# Purpose: To test having extra columns on the master WL#3915
3
# engine inspecific sourced part
4
#############################################################
7
# Setup differently defined tables on master and slave
10
# Def on master: t (f_1 type_m_1,... f_s type_m_s, f_s1, f_m)
11
# Def on slave: t (f_1 type_s_1,... f_s type_s_s)
12
# where type_mi,type_si (0 < i-1 <s1) pairs are compatible types (WL#3228)
13
# Arbitrary paramaters of the test are:
15
# 2. the types of the extra master's column f_s1,..., f_m
16
# 3. the numbers of common columns `s'
17
# 4. and extra columns `m' are par
21
# 5. vary the common columns type within compatible ranges.
24
# constant size column type:
47
# variable size column types:
62
let $binformat = `SHOW VARIABLES LIKE '%binlog_format%'`;
64
--echo ***********************************************************
65
--echo ***********************************************************
66
--echo ***************** Start of Testing ************************
67
--echo ***********************************************************
68
--echo ***********************************************************
69
--echo * This test format == $binformat and engine == $engine_type
70
--echo ***********************************************************
71
--echo ***********************************************************
73
--echo ***** Testing more columns on the Master *****
76
eval CREATE TABLE t1 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
78
f5 FLOAT DEFAULT '2.00',
79
f6 CHAR(4) DEFAULT 'TEST',
84
f11 VARBINARY(64))ENGINE=$engine_type;
86
--echo * Alter Table on Slave and drop columns f5 through f11 *
88
sync_slave_with_master;
89
alter table t1 drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11;
92
--echo * Insert data in Master then update and delete some rows*
99
eval INSERT INTO t1 VALUES ($j, $j, $j, 'second', 2.0, 'kaks', 2,
100
'got stolen from the paradise',
101
'very fat blob', b'01010101010101',
108
eval update t1 set f4= 'next' where f1=$j;
111
eval delete from t1 where f1=$j;
116
--echo * Select count and 20 rows from Master *
118
SELECT COUNT(*) FROM t1;
120
SELECT f1,f2,f3,f4,f5,f6,f7,f8,f9,
121
hex(f10),hex(f11) FROM t1 ORDER BY f3 LIMIT 20;
126
call mtr.add_suppression("Slave SQL.*Table definition on master and slave does not match: Column 2 type mismatch.* 1535");
127
call mtr.add_suppression("Slave.*Can.t DROP .c7.; check that column.key exists.* Error_code: 1091");
128
call mtr.add_suppression("Slave.*Unknown column .c7. in .t15.* Error_code: 1054");
129
call mtr.add_suppression("Slave.*Key column .c6. doesn.t exist in table.* Error_code: 1072");
130
call mtr.add_suppression("Slave I/O: Get master clock failed with error:.* Error_code: 2013");
131
call mtr.add_suppression("Slave I/O: Get master SERVER_ID failed with error:.* Error_code: 2013");
134
sync_slave_with_master;
136
--echo * Select count and 20 rows from Slave *
138
SELECT COUNT(*) FROM t1;
140
SELECT * FROM t1 ORDER BY f3 LIMIT 20;
142
--source include/check_slave_is_running.inc
144
### Altering table def scenario
146
--echo ***** Testing Altering table def scenario *****
151
eval CREATE TABLE t2 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
153
f5 DOUBLE DEFAULT '2.00',
154
f6 ENUM('a', 'b', 'c') default 'a',
155
f7 DECIMAL(17,9) default '1000.00',
157
f9 NUMERIC(6,4) default '2000.00',
159
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',
160
f12 SET('a', 'b', 'c') default 'b')
163
eval CREATE TABLE t3 (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',
169
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',
170
f12 SET('a', 'b', 'c') default 'b')
175
eval CREATE TABLE t4 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
177
f5 DOUBLE DEFAULT '2.00',
178
f6 DECIMAL(17,9) default '1000.00',
180
f8 NUMERIC(6,4) default '2000.00',
182
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',
187
eval CREATE TABLE t31 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
216
f30 ENUM ('a','b', 'c') default 'a',
219
f33 SET ('a', 'b', 'c') default 'b',
222
) ENGINE=$engine_type;
224
--echo ** Alter tables on slave and drop columns **
227
sync_slave_with_master;
228
alter table t2 drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11, drop
230
alter table t3 drop f5, drop f6, drop f8, drop f10, drop f11, drop f12;
231
alter table t4 drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11;
234
drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11,
235
drop f12, drop f13, drop f14, drop f15, drop f16, drop f17, drop f18,
236
drop f19, drop f20, drop f21, drop f22, drop f23, drop f24, drop f25,
237
drop f26, drop f27, drop f28, drop f29, drop f30, drop f31, drop f32,
238
drop f33, drop f34, drop f35;
241
--echo ** Insert Data into Master **
243
INSERT into t2 set f1=1, f2=1, f3=1, f4='first', f8='f8: medium size blob', f10='f10:
245
INSERT into t2 values (2, 2, 2, 'second',
246
2.0, 'b', 2000.0002, 'f8: medium size blob', 2000, 'f10: some var char',
249
3.0, 'b', 3000.0003, 'f8: medium size blob', 3000, 'f10: some var char',
251
INSERT into t3 set f1=1, f2=1, f3=1, f4='first', f10='f10: some var char';
252
INSERT into t4 set f1=1, f2=1, f3=1, f4='first', f7='f7: medium size blob', f10='f10:
254
INSERT into t31 set f1=1, f2=1, f3=1, f4='first';
255
INSERT into t31 set f1=1, f2=1, f3=2, f4='second',
256
f9=2.2, f10='seven samurai', f28=222.222, f35='222';
257
INSERT into t31 values (1, 1, 3, 'third',
258
/* f5 BIGINT, */ 333333333333333333333333,
259
/* f6 BLOB, */ '3333333333333333333333',
260
/* f7 DATE, */ '2007-07-18',
261
/* f8 DATETIME, */ "2007-07-18",
262
/* f9 FLOAT, */ 3.33333333,
263
/* f10 INT, */ 333333333,
264
/* f11 LONGBLOB, */ '3333333333333333333',
265
/* f12 LONGTEXT, */ '3333333333333333333',
266
/* f13 MEDIUMBLOB, */ '3333333333333333333',
267
/* f14 MEDIUMINT, */ 33,
268
/* f15 MEDIUMTEXT, */ 3.3,
270
/* f17 SMALLINT, */ 3,
271
/* f18 TEXT, */ '33',
272
/* f19 TIME, */ '2:59:58.999',
273
/* f20 TIMESTAMP, */ 20000303000000,
274
/* f21 TINYBLOB, */ '3333',
275
/* f22 TINYINT, */ 3,
276
/* f23 TINYTEXT, */ '3',
277
/* f24 YEAR, */ 3000,
278
/* f25 BINARY(255), */ 'three_33333',
279
/* f26 BIT(64), */ b'011',
280
/* f27 CHAR(255), */ 'three',
281
/* f28 DECIMAL(30,7), */ 3.333,
282
/* f29 DOUBLE, */ 3.333333333333333333333333333,
283
/* f30 ENUM ('a','b','c')*/ 'c',
284
/* f31 FLOAT, */ 3.0,
285
/* f32 NUMERIC(17,9), */ 3.3333,
286
/* f33 SET ('a','b','c'),*/ 'c',
287
/*f34 VARBINARY(1025),*/ '3333 minus 3',
288
/*f35 VARCHAR(257),*/ 'three times three'
291
INSERT into t31 values (1, 1, 4, 'fourth',
292
/* f5 BIGINT, */ 333333333333333333333333,
293
/* f6 BLOB, */ '3333333333333333333333',
294
/* f7 DATE, */ '2007-07-18',
295
/* f8 DATETIME, */ "2007-07-18",
296
/* f9 FLOAT, */ 3.33333333,
297
/* f10 INT, */ 333333333,
298
/* f11 LONGBLOB, */ '3333333333333333333',
299
/* f12 LONGTEXT, */ '3333333333333333333',
300
/* f13 MEDIUMBLOB, */ '3333333333333333333',
301
/* f14 MEDIUMINT, */ 33,
302
/* f15 MEDIUMTEXT, */ 3.3,
304
/* f17 SMALLINT, */ 3,
305
/* f18 TEXT, */ '33',
306
/* f19 TIME, */ '2:59:58.999',
307
/* f20 TIMESTAMP, */ 20000303000000,
308
/* f21 TINYBLOB, */ '3333',
309
/* f22 TINYINT, */ 3,
310
/* f23 TINYTEXT, */ '3',
311
/* f24 YEAR, */ 3000,
312
/* f25 BINARY(255), */ 'three_33333',
313
/* f26 BIT(64), */ b'011',
314
/* f27 CHAR(255), */ 'three',
315
/* f28 DECIMAL(30,7), */ 3.333,
316
/* f29 DOUBLE, */ 3.333333333333333333333333333,
317
/* f30 ENUM ('a','b','c')*/ 'c',
318
/* f31 FLOAT, */ 3.0,
319
/* f32 NUMERIC(17,9), */ 3.3333,
320
/* f33 SET ('a','b','c'),*/ 'c',
321
/*f34 VARBINARY(1025),*/ '3333 minus 3',
322
/*f35 VARCHAR(257),*/ 'three times three'
325
/* f5 BIGINT, */ 333333333333333333333333,
326
/* f6 BLOB, */ '3333333333333333333333',
327
/* f7 DATE, */ '2007-07-18',
328
/* f8 DATETIME, */ "2007-07-18",
329
/* f9 FLOAT, */ 3.33333333,
330
/* f10 INT, */ 333333333,
331
/* f11 LONGBLOB, */ '3333333333333333333',
332
/* f12 LONGTEXT, */ '3333333333333333333',
333
/* f13 MEDIUMBLOB, */ '3333333333333333333',
334
/* f14 MEDIUMINT, */ 33,
335
/* f15 MEDIUMTEXT, */ 3.3,
337
/* f17 SMALLINT, */ 3,
338
/* f18 TEXT, */ '33',
339
/* f19 TIME, */ '2:59:58.999',
340
/* f20 TIMESTAMP, */ 20000303000000,
341
/* f21 TINYBLOB, */ '3333',
342
/* f22 TINYINT, */ 3,
343
/* f23 TINYTEXT, */ '3',
344
/* f24 YEAR, */ 3000,
345
/* f25 BINARY(255), */ 'three_33333',
346
/* f26 BIT(64), */ b'011',
347
/* f27 CHAR(255), */ 'three',
348
/* f28 DECIMAL(30,7), */ 3.333,
349
/* f29 DOUBLE, */ 3.333333333333333333333333333,
350
/* f30 ENUM ('a','b','c')*/ 'c',
351
/* f31 FLOAT, */ 3.0,
352
/* f32 NUMERIC(17,9), */ 3.3333,
353
/* f33 SET ('a','b','c'),*/ 'c',
354
/*f34 VARBINARY(1025),*/ '3333 minus 3',
355
/*f35 VARCHAR(257),*/ 'three times three'
358
/* f5 BIGINT, */ NULL,
359
/* f6 BLOB, */ '3333333333333333333333',
360
/* f7 DATE, */ '2007-07-18',
361
/* f8 DATETIME, */ "2007-07-18",
362
/* f9 FLOAT, */ 3.33333333,
363
/* f10 INT, */ 333333333,
364
/* f11 LONGBLOB, */ '3333333333333333333',
365
/* f12 LONGTEXT, */ '3333333333333333333',
366
/* f13 MEDIUMBLOB, */ '3333333333333333333',
367
/* f14 MEDIUMINT, */ 33,
368
/* f15 MEDIUMTEXT, */ 3.3,
370
/* f17 SMALLINT, */ 3,
371
/* f18 TEXT, */ '33',
372
/* f19 TIME, */ '2:59:58.999',
373
/* f20 TIMESTAMP, */ 20000303000000,
374
/* f21 TINYBLOB, */ '3333',
375
/* f22 TINYINT, */ 3,
376
/* f23 TINYTEXT, */ '3',
377
/* f24 YEAR, */ 3000,
378
/* f25 BINARY(255), */ 'three_33333',
379
/* f26 BIT(64), */ b'011',
380
/* f27 CHAR(255), */ 'three',
381
/* f28 DECIMAL(30,7), */ 3.333,
382
/* f29 DOUBLE, */ 3.333333333333333333333333333,
383
/* f30 ENUM ('a','b','c')*/ 'c',
384
/* f31 FLOAT, */ 3.0,
385
/* f32 NUMERIC(17,9), */ 3.3333,
386
/* f33 SET ('a','b','c'),*/ 'c',
387
/*f34 VARBINARY(1025),*/ '3333 minus 3',
388
/*f35 VARCHAR(257),*/ NULL
391
--echo ** Sync slave with master **
392
--echo ** Do selects from tables **
395
sync_slave_with_master;
397
select * from t1 order by f3;
398
select * from t2 order by f1;
399
select * from t3 order by f1;
400
select * from t4 order by f1;
401
select * from t31 order by f3;
405
--echo ** Do updates master **
407
update t31 set f5=555555555555555 where f3=6;
408
update t31 set f2=2 where f3=2;
409
update t31 set f1=NULL where f3=1;
410
update t31 set f3=NULL, f27=NULL, f35='f35 new value' where f3=3;
413
--echo ** Delete from Master **
423
sync_slave_with_master;
427
--echo ** Check slave status **
429
--source include/check_slave_is_running.inc
436
DROP TABLE t1,t2,t3,t4,t31;
438
######################################################
440
sync_slave_with_master;
444
--echo ****************************************
445
--echo * columns in master at middle of table *
446
--echo * Expect: Proper error message *
447
--echo ****************************************
449
--echo ** Stop and Reset Slave **
454
--echo ** create table slave side **
455
eval CREATE TABLE t10 (a INT PRIMARY KEY, b BLOB, c CHAR(5)
456
) ENGINE=$engine_type;
459
--echo ** Connect to master and create table **
462
eval CREATE TABLE t10 (a INT KEY, b BLOB, f DOUBLE DEFAULT '233',
463
c CHAR(5), e INT DEFAULT '1')ENGINE=$engine_type;
467
--echo *** Start Slave ***
472
--echo *** Master Data Insert ***
474
set @b1 = 'b1b1b1b1';
475
set @b1 = concat(@b1,@b1);
476
INSERT INTO t10 () VALUES(1,@b1,DEFAULT,'Kyle',DEFAULT),
477
(2,@b1,DEFAULT,'JOE',DEFAULT),
478
(3,@b1,DEFAULT,'QA',DEFAULT);
481
--echo ********************************************
482
--echo *** Expect slave to fail with Error 1535 ***
483
--echo ********************************************
486
# 1535 = ER_BINLOG_ROW_WRONG_TABLE_DEF
487
--let $slave_sql_errno= 1535
488
--let $slave_skip_counter= 2
489
--let $show_slave_sql_error= 1
490
--source include/wait_for_slave_sql_error_and_skip.inc
493
--echo *** Drop t10 ***
496
sync_slave_with_master;
498
############################################
499
############## Continued ###################
500
############################################
502
--echo *********************************************
503
--echo * More columns in master at middle of table *
504
--echo * Expect: Proper error message *
505
--echo *********************************************
507
--echo *** Create t11 on slave ***
511
eval CREATE TABLE t11 (a INT PRIMARY KEY, b BLOB, c VARCHAR(254)
512
) ENGINE=$engine_type;
515
--echo *** Create t11 on Master ***
517
eval CREATE TABLE t11 (a INT KEY, b BLOB, f TEXT,
518
c CHAR(5) DEFAULT 'test', e INT DEFAULT '1')ENGINE=$engine_type;
523
--echo *** Start Slave ***
528
--echo *** Master Data Insert ***
530
set @b1 = 'b1b1b1b1';
531
set @b1 = concat(@b1,@b1);
532
INSERT INTO t11 () VALUES(1,@b1,'Testing is fun','Kyle',DEFAULT),
533
(2,@b1,'Testing is cool','JOE',DEFAULT),
534
(3,@b1,DEFAULT,'QA',DEFAULT);
537
--echo ********************************************
538
--echo *** Expect slave to fail with Error 1535 ***
539
--echo ********************************************
542
# 1535 = ER_BINLOG_ROW_WRONG_TABLE_DEF
543
--let $slave_sql_errno= 1535
544
--let $slave_skip_counter= 2
545
--let $show_slave_sql_error= 1
546
--source include/wait_for_slave_sql_error_and_skip.inc
549
--echo *** Drop t11 ***
552
sync_slave_with_master;
554
############################################
555
############## Continued ###################
556
############################################
558
--echo *********************************************
559
--echo * More columns in master at middle of table *
560
--echo * Expect: This one should pass blob-text *
561
--echo *********************************************
563
--echo *** Create t12 on slave ***
566
eval CREATE TABLE t12 (a INT PRIMARY KEY, b BLOB, c BLOB
567
) ENGINE=$engine_type;
570
--echo *** Create t12 on Master ***
572
eval CREATE TABLE t12 (a INT KEY, b BLOB, f TEXT,
573
c CHAR(5) DEFAULT 'test', e INT DEFAULT '1')ENGINE=$engine_type;
578
--echo *** Start Slave ***
583
--echo *** Master Data Insert ***
585
set @b1 = 'b1b1b1b1';
586
set @b1 = concat(@b1,@b1);
587
INSERT INTO t12 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),
588
(2,@b1,'JOE',DEFAULT,DEFAULT),
589
(3,@b1,'QA',DEFAULT,DEFAULT);
591
SELECT a,hex(b),f,c,e FROM t12 ORDER BY a;
594
--echo *** Select on Slave ***
595
sync_slave_with_master;
596
SELECT a,hex(b),c FROM t12 ORDER BY a;
599
--echo *** Drop t12 ***
602
sync_slave_with_master;
604
############################################
605
############## Continued ###################
606
############################################
608
--echo ****************************************************
609
--echo * - Alter Master adding columns at middle of table *
610
--echo * Expect: columns added *
611
--echo ****************************************************
614
--echo *** Create t14 on slave ***
617
eval CREATE TABLE t14 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
618
) ENGINE=$engine_type;
621
--echo *** Create t14 on Master ***
623
eval CREATE TABLE t14 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
625
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
626
)ENGINE=$engine_type;
631
--echo *** Start Slave ***
636
--echo *** Master Data Insert ***
638
ALTER TABLE t14 ADD COLUMN c2 DECIMAL(8,2) AFTER c1;
639
ALTER TABLE t14 ADD COLUMN c3 TEXT AFTER c2;
641
set @b1 = 'b1b1b1b1';
642
set @b1 = concat(@b1,@b1);
643
INSERT INTO t14 () VALUES(1,1.00,'Replication Testing Extra Col',@b1,'Kyle',DEFAULT,DEFAULT),
644
(2,2.00,'This Test Should work',@b1,'JOE',DEFAULT,DEFAULT),
645
(3,3.00,'If is does not, I will open a bug',@b1,'QA',DEFAULT,DEFAULT);
647
--replace_column 7 CURRENT_TIMESTAMP
648
SELECT c1,c2,c3,hex(c4),c5,c6,c7 FROM t14 ORDER BY c1;
651
--echo *** Select on Slave ****
652
sync_slave_with_master;
653
SELECT c1,c2,c3,hex(c4),c5 FROM t14 ORDER BY c1;
655
####################################################
657
--echo ****************************************************
658
--echo * - Alter Master Dropping columns from the middle. *
659
--echo * Expect: columns dropped *
660
--echo ****************************************************
662
--echo *** connect to master and drop columns ***
664
ALTER TABLE t14 DROP COLUMN c2;
665
ALTER TABLE t14 DROP COLUMN c7;
667
--echo *** Select from Master ***
668
SELECT c1,c3,hex(c4),c5,c6 FROM t14 ORDER BY c1;
674
# Uncomment below once fixed
676
#--echo *** Select from Slave ***
677
#sync_slave_with_master;
678
#SELECT c1,c2,c3,hex(c4),c5 FROM t14 ORDER BY c1;
681
# Remove below once fixed
682
#***************************
684
# 1091 = ER_CANT_DROP_FIELD_OR_KEY
685
--let $slave_sql_errno= 1091
686
--let $show_slave_sql_error= 1
687
--source include/wait_for_slave_sql_error.inc
688
#***************************
694
--echo *** Drop t14 ***
704
#################################################
706
--echo *************************************************
707
--echo * - Alter Master adding columns at end of table *
708
--echo * Expect: Error 1054 *
709
--echo *************************************************
711
--echo *** Create t15 on slave ***
714
eval CREATE TABLE t15 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
715
) ENGINE=$engine_type;
718
--echo *** Create t15 on Master ***
720
eval CREATE TABLE t15 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
722
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
723
)ENGINE=$engine_type;
728
--echo *** Start Slave ***
733
--echo *** Master Data Insert ***
735
ALTER TABLE t15 ADD COLUMN c2 DECIMAL(8,2) AFTER c7;
736
set @b1 = 'b1b1b1b1';
737
set @b1 = concat(@b1,@b1);
738
INSERT INTO t15 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT,3.00),
739
(2,@b1,'JOE',DEFAULT,DEFAULT,3.00),
740
(3,@b1,'QA',DEFAULT,DEFAULT,3.00);
741
--replace_column 5 CURRENT_TIMESTAMP
742
SELECT c1,hex(c4),c5,c6,c7,c2 FROM t15 ORDER BY c1;
745
--echo ********************************************
746
--echo *** Expect slave to fail with Error 1054 ***
747
--echo ********************************************
750
# 1054 = ER_BAD_FIELD_ERROR
751
--let $slave_sql_errno= 1054
752
--let $show_slave_sql_error= 1
753
--source include/wait_for_slave_sql_error.inc
758
--echo *** Drop t15 ***
768
####################################################
770
--echo ************************************************
771
--echo * - Create index on Master column not on slave *
772
--echo * Expect:Warning *
773
--echo ************************************************
775
--echo *** Create t16 on slave ***
778
eval CREATE TABLE t16 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
779
) ENGINE=$engine_type;
782
--echo *** Create t16 on Master ***
784
eval CREATE TABLE t16 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
786
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
787
)ENGINE=$engine_type;
792
--echo *** Start Slave ***
797
--echo *** Master Create Index and Data Insert ***
799
CREATE INDEX part_of_c6 ON t16 (c6);
800
set @b1 = 'b1b1b1b1';
801
set @b1 = concat(@b1,@b1);
802
INSERT INTO t16 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),
803
(2,@b1,'JOE',2,DEFAULT),
804
(3,@b1,'QA',3,DEFAULT);
805
--replace_column 5 CURRENT_TIMESTAMP
806
SELECT c1,hex(c4),c5,c6,c7 FROM t16 ORDER BY c1;
808
# Uncomment the below when bug 30434 is patched
810
#--echo *** Select on Slave ****
811
#sync_slave_with_master;
812
#SELECT c1,hex(c4),c5 FROM t16 ORDER BY c1;
814
#--echo *** Drop t16 ***
817
#sync_slave_with_master;
819
# Remove the below when bug 30434 is patched
820
#*******************************************
822
--echo *****************
823
--echo *** BUG 30434 ***
824
--echo *****************
827
# 1072 = ER_KEY_COLUMN_DOES_NOT_EXITS
828
--let $slave_sql_errno= 1072
829
--let $show_slave_sql_error= 1
830
--source include/wait_for_slave_sql_error.inc
835
--echo *** Drop t16 ***
844
#*******************************************
846
####################################################
848
--echo *****************************************************
849
--echo * - Delete rows using column on Master not on slave *
850
--echo * Expect: Rows Deleted *
851
--echo *****************************************************
853
--echo *** Create t17 on slave ***
856
eval CREATE TABLE t17 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
857
) ENGINE=$engine_type;
860
--echo *** Create t17 on Master ***
862
eval CREATE TABLE t17 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
864
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
865
)ENGINE=$engine_type;
870
--echo *** Start Slave ***
875
--echo *** Master Data Insert ***
877
set @b1 = 'b1b1b1b1';
878
set @b1 = concat(@b1,@b1);
879
INSERT INTO t17 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),
880
(2,@b1,'JOE',2,DEFAULT),
881
(3,@b1,'QA',3,DEFAULT);
882
--replace_column 5 CURRENT_TIMESTAMP
883
SELECT c1,hex(c4),c5,c6,c7 FROM t17 ORDER BY c1;
886
--echo ** Select * from Slave **
887
sync_slave_with_master;
888
SELECT c1,hex(c4),c5 FROM t17 ORDER BY c1;
891
--echo ** Delete from master **
893
DELETE FROM t17 WHERE c6 = 3;
894
--replace_column 5 CURRENT_TIMESTAMP
895
SELECT c1,hex(c4),c5,c6,c7 FROM t17 ORDER BY c1;
898
--echo ** Check slave **
899
sync_slave_with_master;
900
SELECT c1,hex(c4),c5 FROM t17 ORDER BY c1;
905
sync_slave_with_master;
908
####################################################
910
--echo *****************************************************
911
--echo * - Update row using column on Master not on slave *
912
--echo * Expect: Rows updated *
913
--echo *****************************************************
915
--echo ** Bug30674 **
917
--echo *** Create t18 on slave ***
922
eval CREATE TABLE t18 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
923
) ENGINE=$engine_type;
926
--echo *** Create t18 on Master ***
928
eval CREATE TABLE t18 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
930
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
931
)ENGINE=$engine_type;
936
--echo *** Start Slave ***
941
--echo *** Master Data Insert ***
943
set @b1 = 'b1b1b1b1';
944
set @b1 = concat(@b1,@b1);
946
INSERT INTO t18 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),
947
(2,@b1,'JOE',2,DEFAULT),
948
(3,@b1,'QA',3,DEFAULT);
949
--replace_column 5 CURRENT_TIMESTAMP
950
SELECT c1,hex(c4),c5,c6,c7 FROM t18 ORDER BY c1;
953
--echo ** Select * from Slave **
954
sync_slave_with_master;
955
SELECT c1,hex(c4),c5 FROM t18 ORDER BY c1;
958
--echo ** update from master **
960
UPDATE t18 SET c5 = 'TEST' WHERE c6 = 3;
962
--replace_column 5 CURRENT_TIMESTAMP
963
SELECT c1,hex(c4),c5,c6,c7 FROM t18 ORDER BY c1;
966
--echo ** Check slave **
967
sync_slave_with_master;
968
SELECT c1,hex(c4),c5 FROM t18 ORDER BY c1;
972
sync_slave_with_master;
975
####################################################
977
--echo *****************************************************
978
--echo * - Insert UUID column on Master not on slave *
979
--echo * Expect: Rows inserted *
980
--echo *****************************************************
982
--echo *** Create t5 on slave ***
985
eval CREATE TABLE t5 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
986
) ENGINE=$engine_type;
989
--echo *** Create t5 on Master ***
991
eval CREATE TABLE t5 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
993
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
994
)ENGINE=$engine_type;
999
--echo *** Start Slave ***
1004
--echo *** Master Data Insert ***
1006
set @b1 = 'b1b1b1b1';
1007
INSERT INTO t5 () VALUES(1,@b1,'Kyle',UUID(),DEFAULT),
1008
(2,@b1,'JOE',UUID(),DEFAULT),
1009
(3,@b1,'QA',UUID(),DEFAULT);
1010
--replace_column 4 UUID 5 TIME
1011
SELECT c1,hex(c4),c5,c6,c7 FROM t5 ORDER BY c1;
1014
--echo ** Select * from Slave **
1015
sync_slave_with_master;
1016
SELECT c1,hex(c4),c5 FROM t5 ORDER BY c1;
1020
sync_slave_with_master;