2
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
5
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
9
***********************************************************
10
***********************************************************
11
***************** Start of Testing ************************
12
***********************************************************
13
***********************************************************
14
* This test format == binlog_format ROW and engine == 'InnoDB'
15
***********************************************************
16
***********************************************************
18
***** Testing more columns on the Master *****
20
CREATE TABLE t1 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
22
f5 FLOAT DEFAULT '2.00',
23
f6 CHAR(4) DEFAULT 'TEST',
28
f11 VARBINARY(64))ENGINE='InnoDB';
30
* Alter Table on Slave and drop columns f5 through f11 *
32
alter table t1 drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11;
34
* Insert data in Master then update and delete some rows*
36
* Select count and 20 rows from Master *
38
SELECT COUNT(*) FROM t1;
42
SELECT f1,f2,f3,f4,f5,f6,f7,f8,f9,
43
hex(f10),hex(f11) FROM t1 ORDER BY f3 LIMIT 20;
44
f1 f2 f3 f4 f5 f6 f7 f8 f9 hex(f10) hex(f11)
45
2 2 2 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
46
3 3 3 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
47
5 5 5 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
48
6 6 6 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
49
8 8 8 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
50
9 9 9 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
51
11 11 11 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
52
12 12 12 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
53
14 14 14 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
54
15 15 15 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
55
17 17 17 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
56
18 18 18 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
57
20 20 20 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
58
21 21 21 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
59
23 23 23 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
60
24 24 24 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
61
26 26 26 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
62
27 27 27 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
63
29 29 29 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
64
30 30 30 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
66
* Select count and 20 rows from Slave *
68
SELECT COUNT(*) FROM t1;
72
SELECT * FROM t1 ORDER BY f3 LIMIT 20;
103
Master_Log_File master-bin.000001
104
Read_Master_Log_Pos #
107
Relay_Master_Log_File master-bin.000001
109
Slave_SQL_Running Yes
113
Replicate_Ignore_Table
114
Replicate_Wild_Do_Table
115
Replicate_Wild_Ignore_Table
119
Exec_Master_Log_Pos #
124
Master_SSL_Allowed No
130
Seconds_Behind_Master #
131
Master_SSL_Verify_Server_Cert No
138
***** Testing Altering table def scenario *****
140
CREATE TABLE t2 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
142
f5 DOUBLE DEFAULT '2.00',
143
f6 ENUM('a', 'b', 'c') default 'a',
144
f7 DECIMAL(17,9) default '1000.00',
146
f9 NUMERIC(6,4) default '2000.00',
148
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',
149
f12 SET('a', 'b', 'c') default 'b')
152
Warning 1264 Out of range value for column 'f9' at row 1
154
CREATE TABLE t3 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
156
f5 DOUBLE DEFAULT '2.00',
157
f6 ENUM('a', 'b', 'c') default 'a',
160
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',
161
f12 SET('a', 'b', 'c') default 'b')
164
CREATE TABLE t4 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
166
f5 DOUBLE DEFAULT '2.00',
167
f6 DECIMAL(17,9) default '1000.00',
169
f8 NUMERIC(6,4) default '2000.00',
171
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',
175
Warning 1264 Out of range value for column 'f8' at row 1
177
CREATE TABLE t31 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
204
f30 ENUM ('a','b', 'c') default 'a',
207
f33 SET ('a', 'b', 'c') default 'b',
212
** Alter tables on slave and drop columns **
214
alter table t2 drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11, drop
216
alter table t3 drop f5, drop f6, drop f8, drop f10, drop f11, drop f12;
217
alter table t4 drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11;
219
drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11,
220
drop f12, drop f13, drop f14, drop f15, drop f16, drop f17, drop f18,
221
drop f19, drop f20, drop f21, drop f22, drop f23, drop f24, drop f25,
222
drop f26, drop f27, drop f28, drop f29, drop f30, drop f31, drop f32,
223
drop f33, drop f34, drop f35;
225
** Insert Data into Master **
226
INSERT into t2 set f1=1, f2=1, f3=1, f4='first', f8='f8: medium size blob', f10='f10:
228
INSERT into t2 values (2, 2, 2, 'second',
229
2.0, 'b', 2000.0002, 'f8: medium size blob', 2000, 'f10: some var char',
232
3.0, 'b', 3000.0003, 'f8: medium size blob', 3000, 'f10: some var char',
235
Warning 1264 Out of range value for column 'f9' at row 1
236
Warning 1264 Out of range value for column 'f9' at row 2
237
INSERT into t3 set f1=1, f2=1, f3=1, f4='first', f10='f10: some var char';
238
INSERT into t4 set f1=1, f2=1, f3=1, f4='first', f7='f7: medium size blob', f10='f10:
240
INSERT into t31 set f1=1, f2=1, f3=1, f4='first';
241
INSERT into t31 set f1=1, f2=1, f3=2, f4='second',
242
f9=2.2, f10='seven samurai', f28=222.222, f35='222';
244
Warning 1366 Incorrect integer value: 'seven samurai' for column 'f10' at row 1
245
INSERT into t31 values (1, 1, 3, 'third',
246
/* f5 BIGINT, */ 333333333333333333333333,
247
/* f6 BLOB, */ '3333333333333333333333',
248
/* f7 DATE, */ '2007-07-18',
249
/* f8 DATETIME, */ "2007-07-18",
250
/* f9 FLOAT, */ 3.33333333,
251
/* f10 INT, */ 333333333,
252
/* f11 LONGBLOB, */ '3333333333333333333',
253
/* f12 LONGTEXT, */ '3333333333333333333',
254
/* f13 MEDIUMBLOB, */ '3333333333333333333',
255
/* f14 MEDIUMINT, */ 33,
256
/* f15 MEDIUMTEXT, */ 3.3,
258
/* f17 SMALLINT, */ 3,
259
/* f18 TEXT, */ '33',
260
/* f19 TIME, */ '2:59:58.999',
261
/* f20 TIMESTAMP, */ 20000303000000,
262
/* f21 TINYBLOB, */ '3333',
263
/* f22 TINYINT, */ 3,
264
/* f23 TINYTEXT, */ '3',
265
/* f24 YEAR, */ 3000,
266
/* f25 BINARY(255), */ 'three_33333',
267
/* f26 BIT(64), */ b'011',
268
/* f27 CHAR(255), */ 'three',
269
/* f28 DECIMAL(30,7), */ 3.333,
270
/* f29 DOUBLE, */ 3.333333333333333333333333333,
271
/* f30 ENUM ('a','b','c')*/ 'c',
272
/* f31 FLOAT, */ 3.0,
273
/* f32 NUMERIC(17,9), */ 3.3333,
274
/* f33 SET ('a','b','c'),*/ 'c',
275
/*f34 VARBINARY(1025),*/ '3333 minus 3',
276
/*f35 VARCHAR(257),*/ 'three times three'
279
Warning 1264 Out of range value for column 'f5' at row 1
280
Warning 1264 Out of range value for column 'f24' at row 1
281
INSERT into t31 values (1, 1, 4, 'fourth',
282
/* f5 BIGINT, */ 333333333333333333333333,
283
/* f6 BLOB, */ '3333333333333333333333',
284
/* f7 DATE, */ '2007-07-18',
285
/* f8 DATETIME, */ "2007-07-18",
286
/* f9 FLOAT, */ 3.33333333,
287
/* f10 INT, */ 333333333,
288
/* f11 LONGBLOB, */ '3333333333333333333',
289
/* f12 LONGTEXT, */ '3333333333333333333',
290
/* f13 MEDIUMBLOB, */ '3333333333333333333',
291
/* f14 MEDIUMINT, */ 33,
292
/* f15 MEDIUMTEXT, */ 3.3,
294
/* f17 SMALLINT, */ 3,
295
/* f18 TEXT, */ '33',
296
/* f19 TIME, */ '2:59:58.999',
297
/* f20 TIMESTAMP, */ 20000303000000,
298
/* f21 TINYBLOB, */ '3333',
299
/* f22 TINYINT, */ 3,
300
/* f23 TINYTEXT, */ '3',
301
/* f24 YEAR, */ 3000,
302
/* f25 BINARY(255), */ 'three_33333',
303
/* f26 BIT(64), */ b'011',
304
/* f27 CHAR(255), */ 'three',
305
/* f28 DECIMAL(30,7), */ 3.333,
306
/* f29 DOUBLE, */ 3.333333333333333333333333333,
307
/* f30 ENUM ('a','b','c')*/ 'c',
308
/* f31 FLOAT, */ 3.0,
309
/* f32 NUMERIC(17,9), */ 3.3333,
310
/* f33 SET ('a','b','c'),*/ 'c',
311
/*f34 VARBINARY(1025),*/ '3333 minus 3',
312
/*f35 VARCHAR(257),*/ 'three times three'
315
/* f5 BIGINT, */ 333333333333333333333333,
316
/* f6 BLOB, */ '3333333333333333333333',
317
/* f7 DATE, */ '2007-07-18',
318
/* f8 DATETIME, */ "2007-07-18",
319
/* f9 FLOAT, */ 3.33333333,
320
/* f10 INT, */ 333333333,
321
/* f11 LONGBLOB, */ '3333333333333333333',
322
/* f12 LONGTEXT, */ '3333333333333333333',
323
/* f13 MEDIUMBLOB, */ '3333333333333333333',
324
/* f14 MEDIUMINT, */ 33,
325
/* f15 MEDIUMTEXT, */ 3.3,
327
/* f17 SMALLINT, */ 3,
328
/* f18 TEXT, */ '33',
329
/* f19 TIME, */ '2:59:58.999',
330
/* f20 TIMESTAMP, */ 20000303000000,
331
/* f21 TINYBLOB, */ '3333',
332
/* f22 TINYINT, */ 3,
333
/* f23 TINYTEXT, */ '3',
334
/* f24 YEAR, */ 3000,
335
/* f25 BINARY(255), */ 'three_33333',
336
/* f26 BIT(64), */ b'011',
337
/* f27 CHAR(255), */ 'three',
338
/* f28 DECIMAL(30,7), */ 3.333,
339
/* f29 DOUBLE, */ 3.333333333333333333333333333,
340
/* f30 ENUM ('a','b','c')*/ 'c',
341
/* f31 FLOAT, */ 3.0,
342
/* f32 NUMERIC(17,9), */ 3.3333,
343
/* f33 SET ('a','b','c'),*/ 'c',
344
/*f34 VARBINARY(1025),*/ '3333 minus 3',
345
/*f35 VARCHAR(257),*/ 'three times three'
348
/* f5 BIGINT, */ NULL,
349
/* f6 BLOB, */ '3333333333333333333333',
350
/* f7 DATE, */ '2007-07-18',
351
/* f8 DATETIME, */ "2007-07-18",
352
/* f9 FLOAT, */ 3.33333333,
353
/* f10 INT, */ 333333333,
354
/* f11 LONGBLOB, */ '3333333333333333333',
355
/* f12 LONGTEXT, */ '3333333333333333333',
356
/* f13 MEDIUMBLOB, */ '3333333333333333333',
357
/* f14 MEDIUMINT, */ 33,
358
/* f15 MEDIUMTEXT, */ 3.3,
360
/* f17 SMALLINT, */ 3,
361
/* f18 TEXT, */ '33',
362
/* f19 TIME, */ '2:59:58.999',
363
/* f20 TIMESTAMP, */ 20000303000000,
364
/* f21 TINYBLOB, */ '3333',
365
/* f22 TINYINT, */ 3,
366
/* f23 TINYTEXT, */ '3',
367
/* f24 YEAR, */ 3000,
368
/* f25 BINARY(255), */ 'three_33333',
369
/* f26 BIT(64), */ b'011',
370
/* f27 CHAR(255), */ 'three',
371
/* f28 DECIMAL(30,7), */ 3.333,
372
/* f29 DOUBLE, */ 3.333333333333333333333333333,
373
/* f30 ENUM ('a','b','c')*/ 'c',
374
/* f31 FLOAT, */ 3.0,
375
/* f32 NUMERIC(17,9), */ 3.3333,
376
/* f33 SET ('a','b','c'),*/ 'c',
377
/*f34 VARBINARY(1025),*/ '3333 minus 3',
378
/*f35 VARCHAR(257),*/ NULL
381
Warning 1264 Out of range value for column 'f5' at row 1
382
Warning 1264 Out of range value for column 'f24' at row 1
383
Warning 1264 Out of range value for column 'f5' at row 2
384
Warning 1264 Out of range value for column 'f24' at row 2
385
Warning 1264 Out of range value for column 'f24' at row 3
387
** Sync slave with master **
388
** Do selects from tables **
390
select * from t1 order by f3;
432
select * from t2 order by f1;
437
select * from t3 order by f1;
440
select * from t4 order by f1;
443
select * from t31 order by f3;
452
** Do updates master **
454
update t31 set f5=555555555555555 where f3=6;
455
update t31 set f2=2 where f3=2;
456
update t31 set f1=NULL where f3=1;
457
update t31 set f3=NULL, f27=NULL, f35='f35 new value' where f3=3;
459
Warning 1048 Column 'f3' cannot be null
461
** Delete from Master **
469
** Check slave status **
475
Master_Host 127.0.0.1
479
Master_Log_File master-bin.000001
480
Read_Master_Log_Pos #
483
Relay_Master_Log_File master-bin.000001
485
Slave_SQL_Running Yes
489
Replicate_Ignore_Table
490
Replicate_Wild_Do_Table
491
Replicate_Wild_Ignore_Table
495
Exec_Master_Log_Pos #
500
Master_SSL_Allowed No
506
Seconds_Behind_Master #
507
Master_SSL_Verify_Server_Cert No
513
****************************************
514
* columns in master at middle of table *
515
* Expect: Proper error message *
516
****************************************
518
** Stop and Reset Slave **
523
** create table slave side **
524
CREATE TABLE t10 (a INT PRIMARY KEY, b BLOB, c CHAR(5)
527
** Connect to master and create table **
529
CREATE TABLE t10 (a INT KEY, b BLOB, f DOUBLE DEFAULT '233',
530
c CHAR(5), e INT DEFAULT '1')ENGINE='InnoDB';
536
*** Master Data Insert ***
537
set @b1 = 'b1b1b1b1';
538
set @b1 = concat(@b1,@b1);
539
INSERT INTO t10 () VALUES(1,@b1,DEFAULT,'Kyle',DEFAULT),
540
(2,@b1,DEFAULT,'JOE',DEFAULT),
541
(3,@b1,DEFAULT,'QA',DEFAULT);
543
********************************************
544
*** Expect slave to fail with Error 1523 ***
545
********************************************
549
Master_Host 127.0.0.1
553
Master_Log_File master-bin.000001
554
Read_Master_Log_Pos #
557
Relay_Master_Log_File master-bin.000001
563
Replicate_Ignore_Table
564
Replicate_Wild_Do_Table
565
Replicate_Wild_Ignore_Table
567
Last_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 5, test.t10 has type 254
569
Exec_Master_Log_Pos #
574
Master_SSL_Allowed No
580
Seconds_Behind_Master #
581
Master_SSL_Verify_Server_Cert No
585
Last_SQL_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 5, test.t10 has type 254
586
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
592
*********************************************
593
* More columns in master at middle of table *
594
* Expect: Proper error message *
595
*********************************************
597
*** Create t11 on slave ***
600
CREATE TABLE t11 (a INT PRIMARY KEY, b BLOB, c VARCHAR(254)
603
*** Create t11 on Master ***
604
CREATE TABLE t11 (a INT KEY, b BLOB, f TEXT,
605
c CHAR(5) DEFAULT 'test', e INT DEFAULT '1')ENGINE='InnoDB';
611
*** Master Data Insert ***
612
set @b1 = 'b1b1b1b1';
613
set @b1 = concat(@b1,@b1);
614
INSERT INTO t11 () VALUES(1,@b1,'Testing is fun','Kyle',DEFAULT),
615
(2,@b1,'Testing is cool','JOE',DEFAULT),
616
(3,@b1,DEFAULT,'QA',DEFAULT);
618
********************************************
619
*** Expect slave to fail with Error 1523 ***
620
********************************************
624
Master_Host 127.0.0.1
628
Master_Log_File master-bin.000001
629
Read_Master_Log_Pos #
632
Relay_Master_Log_File master-bin.000001
638
Replicate_Ignore_Table
639
Replicate_Wild_Do_Table
640
Replicate_Wild_Ignore_Table
642
Last_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 252, test.t11 has type 15
644
Exec_Master_Log_Pos #
649
Master_SSL_Allowed No
655
Seconds_Behind_Master #
656
Master_SSL_Verify_Server_Cert No
660
Last_SQL_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 252, test.t11 has type 15
661
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
667
*********************************************
668
* More columns in master at middle of table *
669
* Expect: This one should pass blob-text *
670
*********************************************
672
*** Create t12 on slave ***
675
CREATE TABLE t12 (a INT PRIMARY KEY, b BLOB, c BLOB
678
*** Create t12 on Master ***
679
CREATE TABLE t12 (a INT KEY, b BLOB, f TEXT,
680
c CHAR(5) DEFAULT 'test', e INT DEFAULT '1')ENGINE='InnoDB';
686
*** Master Data Insert ***
687
set @b1 = 'b1b1b1b1';
688
set @b1 = concat(@b1,@b1);
689
INSERT INTO t12 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),
690
(2,@b1,'JOE',DEFAULT,DEFAULT),
691
(3,@b1,'QA',DEFAULT,DEFAULT);
693
SELECT a,hex(b),f,c,e FROM t12 ORDER BY a;
695
1 62316231623162316231623162316231 Kyle test 1
696
2 62316231623162316231623162316231 JOE test 1
697
3 62316231623162316231623162316231 QA test 1
699
*** Select on Slave ***
700
SELECT a,hex(b),c FROM t12 ORDER BY a;
702
1 62316231623162316231623162316231 Kyle
703
2 62316231623162316231623162316231 JOE
704
3 62316231623162316231623162316231 QA
709
****************************************************
710
* - Alter Master adding columns at middle of table *
711
* Expect: columns added *
712
****************************************************
715
*** Create t14 on slave ***
718
CREATE TABLE t14 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
721
*** Create t14 on Master ***
722
CREATE TABLE t14 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
724
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
731
*** Master Data Insert ***
732
ALTER TABLE t14 ADD COLUMN c2 DECIMAL(8,2) AFTER c1;
733
ALTER TABLE t14 ADD COLUMN c3 TEXT AFTER c2;
735
set @b1 = 'b1b1b1b1';
736
set @b1 = concat(@b1,@b1);
737
INSERT INTO t14 () VALUES(1,1.00,'Replication Testing Extra Col',@b1,'Kyle',DEFAULT,DEFAULT),
738
(2,2.00,'This Test Should work',@b1,'JOE',DEFAULT,DEFAULT),
739
(3,3.00,'If is does not, I will open a bug',@b1,'QA',DEFAULT,DEFAULT);
741
SELECT c1,c2,c3,hex(c4),c5,c6,c7 FROM t14 ORDER BY c1;
742
c1 c2 c3 hex(c4) c5 c6 c7
743
1 1.00 Replication Testing Extra Col 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP
744
2 2.00 This Test Should work 62316231623162316231623162316231 JOE 1 CURRENT_TIMESTAMP
745
3 3.00 If is does not, I will open a bug 62316231623162316231623162316231 QA 1 CURRENT_TIMESTAMP
747
*** Select on Slave ****
748
SELECT c1,c2,c3,hex(c4),c5 FROM t14 ORDER BY c1;
750
1 1.00 Replication Testing Extra Col 62316231623162316231623162316231 Kyle
751
2 2.00 This Test Should work 62316231623162316231623162316231 JOE
752
3 3.00 If is does not, I will open a bug 62316231623162316231623162316231 QA
754
****************************************************
755
* - Alter Master Dropping columns from the middle. *
756
* Expect: columns dropped *
757
****************************************************
759
*** connect to master and drop columns ***
760
ALTER TABLE t14 DROP COLUMN c2;
761
ALTER TABLE t14 DROP COLUMN c7;
763
*** Select from Master ***
764
SELECT c1,c3,hex(c4),c5,c6 FROM t14 ORDER BY c1;
766
1 Replication Testing Extra Col 62316231623162316231623162316231 Kyle 1
767
2 This Test Should work 62316231623162316231623162316231 JOE 1
768
3 If is does not, I will open a bug 62316231623162316231623162316231 QA 1
775
Master_Host 127.0.0.1
779
Master_Log_File master-bin.000001
780
Read_Master_Log_Pos #
783
Relay_Master_Log_File master-bin.000001
789
Replicate_Ignore_Table
790
Replicate_Wild_Do_Table
791
Replicate_Wild_Ignore_Table
793
Last_Error Error 'Can't DROP 'c7'; check that column/key exists' on query. Default database: 'test'. Query: 'ALTER TABLE t14 DROP COLUMN c7'
795
Exec_Master_Log_Pos #
800
Master_SSL_Allowed No
806
Seconds_Behind_Master #
807
Master_SSL_Verify_Server_Cert No
811
Last_SQL_Error Error 'Can't DROP 'c7'; check that column/key exists' on query. Default database: 'test'. Query: 'ALTER TABLE t14 DROP COLUMN c7'
821
*************************************************
822
* - Alter Master adding columns at end of table *
823
* Expect: Error 1054 *
824
*************************************************
826
*** Create t15 on slave ***
829
CREATE TABLE t15 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
832
*** Create t15 on Master ***
833
CREATE TABLE t15 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
835
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
842
*** Master Data Insert ***
843
ALTER TABLE t15 ADD COLUMN c2 DECIMAL(8,2) AFTER c7;
844
set @b1 = 'b1b1b1b1';
845
set @b1 = concat(@b1,@b1);
846
INSERT INTO t15 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT,3.00),
847
(2,@b1,'JOE',DEFAULT,DEFAULT,3.00),
848
(3,@b1,'QA',DEFAULT,DEFAULT,3.00);
849
SELECT c1,hex(c4),c5,c6,c7,c2 FROM t15 ORDER BY c1;
850
c1 hex(c4) c5 c6 c7 c2
851
1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP 3.00
852
2 62316231623162316231623162316231 JOE 1 CURRENT_TIMESTAMP 3.00
853
3 62316231623162316231623162316231 QA 1 CURRENT_TIMESTAMP 3.00
855
********************************************
856
*** Expect slave to fail with Error 1054 ***
857
********************************************
861
Master_Host 127.0.0.1
865
Master_Log_File master-bin.000001
866
Read_Master_Log_Pos #
869
Relay_Master_Log_File master-bin.000001
875
Replicate_Ignore_Table
876
Replicate_Wild_Do_Table
877
Replicate_Wild_Ignore_Table
879
Last_Error Error 'Unknown column 'c7' in 't15'' on query. Default database: 'test'. Query: 'ALTER TABLE t15 ADD COLUMN c2 DECIMAL(8,2) AFTER c7'
881
Exec_Master_Log_Pos #
886
Master_SSL_Allowed No
892
Seconds_Behind_Master #
893
Master_SSL_Verify_Server_Cert No
897
Last_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'
907
************************************************
908
* - Create index on Master column not on slave *
910
************************************************
912
*** Create t16 on slave ***
915
CREATE TABLE t16 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
918
*** Create t16 on Master ***
919
CREATE TABLE t16 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
921
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
928
*** Master Create Index and Data Insert ***
929
CREATE INDEX part_of_c6 ON t16 (c6);
930
set @b1 = 'b1b1b1b1';
931
set @b1 = concat(@b1,@b1);
932
INSERT INTO t16 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),
933
(2,@b1,'JOE',2,DEFAULT),
934
(3,@b1,'QA',3,DEFAULT);
935
SELECT c1,hex(c4),c5,c6,c7 FROM t16 ORDER BY c1;
937
1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP
938
2 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP
939
3 62316231623162316231623162316231 QA 3 CURRENT_TIMESTAMP
947
Master_Host 127.0.0.1
951
Master_Log_File master-bin.000001
952
Read_Master_Log_Pos #
955
Relay_Master_Log_File master-bin.000001
961
Replicate_Ignore_Table
962
Replicate_Wild_Do_Table
963
Replicate_Wild_Ignore_Table
965
Last_Error Error 'Key column 'c6' doesn't exist in table' on query. Default database: 'test'. Query: 'CREATE INDEX part_of_c6 ON t16 (c6)'
967
Exec_Master_Log_Pos #
972
Master_SSL_Allowed No
978
Seconds_Behind_Master #
979
Master_SSL_Verify_Server_Cert No
983
Last_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)'
993
*****************************************************
994
* - Delete rows using column on Master not on slave *
995
* Expect: Rows Deleted *
996
*****************************************************
998
*** Create t17 on slave ***
1001
CREATE TABLE t17 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
1004
*** Create t17 on Master ***
1005
CREATE TABLE t17 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
1007
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
1014
*** Master Data Insert ***
1015
set @b1 = 'b1b1b1b1';
1016
set @b1 = concat(@b1,@b1);
1017
INSERT INTO t17 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),
1018
(2,@b1,'JOE',2,DEFAULT),
1019
(3,@b1,'QA',3,DEFAULT);
1020
SELECT c1,hex(c4),c5,c6,c7 FROM t17 ORDER BY c1;
1022
1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP
1023
2 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP
1024
3 62316231623162316231623162316231 QA 3 CURRENT_TIMESTAMP
1026
** Select * from Slave **
1027
SELECT c1,hex(c4),c5 FROM t17 ORDER BY c1;
1029
1 62316231623162316231623162316231 Kyle
1030
2 62316231623162316231623162316231 JOE
1031
3 62316231623162316231623162316231 QA
1033
** Delete from master **
1034
DELETE FROM t17 WHERE c6 = 3;
1035
SELECT c1,hex(c4),c5,c6,c7 FROM t17 ORDER BY c1;
1037
1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP
1038
2 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP
1041
SELECT c1,hex(c4),c5 FROM t17 ORDER BY c1;
1043
1 62316231623162316231623162316231 Kyle
1044
2 62316231623162316231623162316231 JOE
1048
*****************************************************
1049
* - Update row using column on Master not on slave *
1050
* Expect: Rows updated *
1051
*****************************************************
1055
*** Create t18 on slave ***
1059
CREATE TABLE t18 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
1062
*** Create t18 on Master ***
1063
CREATE TABLE t18 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
1065
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
1072
*** Master Data Insert ***
1073
set @b1 = 'b1b1b1b1';
1074
set @b1 = concat(@b1,@b1);
1075
INSERT INTO t18 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),
1076
(2,@b1,'JOE',2,DEFAULT),
1077
(3,@b1,'QA',3,DEFAULT);
1078
SELECT c1,hex(c4),c5,c6,c7 FROM t18 ORDER BY c1;
1080
1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP
1081
2 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP
1082
3 62316231623162316231623162316231 QA 3 CURRENT_TIMESTAMP
1084
** Select * from Slave **
1085
SELECT c1,hex(c4),c5 FROM t18 ORDER BY c1;
1087
1 62316231623162316231623162316231 Kyle
1088
2 62316231623162316231623162316231 JOE
1089
3 62316231623162316231623162316231 QA
1091
** update from master **
1092
UPDATE t18 SET c5 = 'TEST' WHERE c6 = 3;
1093
SELECT c1,hex(c4),c5,c6,c7 FROM t18 ORDER BY c1;
1095
1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP
1096
2 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP
1097
3 62316231623162316231623162316231 TEST 3 CURRENT_TIMESTAMP
1100
SELECT c1,hex(c4),c5 FROM t18 ORDER BY c1;
1102
1 62316231623162316231623162316231 Kyle
1103
2 62316231623162316231623162316231 JOE
1104
3 62316231623162316231623162316231 TEST
1108
*****************************************************
1109
* - Insert UUID column on Master not on slave *
1110
* Expect: Rows inserted *
1111
*****************************************************
1113
*** Create t5 on slave ***
1116
CREATE TABLE t5 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
1119
*** Create t5 on Master ***
1120
CREATE TABLE t5 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
1122
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
1129
*** Master Data Insert ***
1130
set @b1 = 'b1b1b1b1';
1131
INSERT INTO t5 () VALUES(1,@b1,'Kyle',UUID(),DEFAULT),
1132
(2,@b1,'JOE',UUID(),DEFAULT),
1133
(3,@b1,'QA',UUID(),DEFAULT);
1134
SELECT c1,hex(c4),c5,c6,c7 FROM t5 ORDER BY c1;
1136
1 6231623162316231 Kyle UUID TIME
1137
2 6231623162316231 JOE UUID TIME
1138
3 6231623162316231 QA UUID TIME
1140
** Select * from Slave **
1141
SELECT c1,hex(c4),c5 FROM t5 ORDER BY c1;
1143
1 6231623162316231 Kyle
1144
2 6231623162316231 JOE
1145
3 6231623162316231 QA
1148
set binlog_format=statement;
1150
***********************************************************
1151
***********************************************************
1152
***************** Start of Testing ************************
1153
***********************************************************
1154
***********************************************************
1155
* This test format == binlog_format STATEMENT and engine == 'InnoDB'
1156
***********************************************************
1157
***********************************************************
1159
***** Testing more columns on the Master *****
1161
CREATE TABLE t1 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
1163
f5 FLOAT DEFAULT '2.00',
1164
f6 CHAR(4) DEFAULT 'TEST',
1169
f11 VARBINARY(64))ENGINE='InnoDB';
1171
* Alter Table on Slave and drop columns f5 through f11 *
1173
alter table t1 drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11;
1175
* Insert data in Master then update and delete some rows*
1177
* Select count and 20 rows from Master *
1179
SELECT COUNT(*) FROM t1;
1183
SELECT f1,f2,f3,f4,f5,f6,f7,f8,f9,
1184
hex(f10),hex(f11) FROM t1 ORDER BY f3 LIMIT 20;
1185
f1 f2 f3 f4 f5 f6 f7 f8 f9 hex(f10) hex(f11)
1186
2 2 2 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
1187
3 3 3 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
1188
5 5 5 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
1189
6 6 6 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
1190
8 8 8 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
1191
9 9 9 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
1192
11 11 11 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
1193
12 12 12 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
1194
14 14 14 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
1195
15 15 15 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
1196
17 17 17 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
1197
18 18 18 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
1198
20 20 20 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
1199
21 21 21 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
1200
23 23 23 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
1201
24 24 24 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
1202
26 26 26 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
1203
27 27 27 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
1204
29 29 29 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
1205
30 30 30 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
1207
* Select count and 20 rows from Slave *
1209
SELECT COUNT(*) FROM t1;
1213
SELECT * FROM t1 ORDER BY f3 LIMIT 20;
1236
* Show Slave Status *
1240
Master_Host 127.0.0.1
1244
Master_Log_File master-bin.000001
1245
Read_Master_Log_Pos #
1248
Relay_Master_Log_File master-bin.000001
1249
Slave_IO_Running Yes
1250
Slave_SQL_Running Yes
1254
Replicate_Ignore_Table
1255
Replicate_Wild_Do_Table
1256
Replicate_Wild_Ignore_Table
1260
Exec_Master_Log_Pos #
1262
Until_Condition None
1265
Master_SSL_Allowed No
1271
Seconds_Behind_Master #
1272
Master_SSL_Verify_Server_Cert No
1279
***** Testing Altering table def scenario *****
1281
CREATE TABLE t2 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
1283
f5 DOUBLE DEFAULT '2.00',
1284
f6 ENUM('a', 'b', 'c') default 'a',
1285
f7 DECIMAL(17,9) default '1000.00',
1287
f9 NUMERIC(6,4) default '2000.00',
1289
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',
1290
f12 SET('a', 'b', 'c') default 'b')
1293
Warning 1264 Out of range value for column 'f9' at row 1
1295
CREATE TABLE t3 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
1297
f5 DOUBLE DEFAULT '2.00',
1298
f6 ENUM('a', 'b', 'c') default 'a',
1301
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',
1302
f12 SET('a', 'b', 'c') default 'b')
1305
CREATE TABLE t4 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
1307
f5 DOUBLE DEFAULT '2.00',
1308
f6 DECIMAL(17,9) default '1000.00',
1310
f8 NUMERIC(6,4) default '2000.00',
1312
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',
1316
Warning 1264 Out of range value for column 'f8' at row 1
1318
CREATE TABLE t31 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
1345
f30 ENUM ('a','b', 'c') default 'a',
1348
f33 SET ('a', 'b', 'c') default 'b',
1349
f34 VARBINARY(1025),
1353
** Alter tables on slave and drop columns **
1355
alter table t2 drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11, drop
1357
alter table t3 drop f5, drop f6, drop f8, drop f10, drop f11, drop f12;
1358
alter table t4 drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11;
1360
drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11,
1361
drop f12, drop f13, drop f14, drop f15, drop f16, drop f17, drop f18,
1362
drop f19, drop f20, drop f21, drop f22, drop f23, drop f24, drop f25,
1363
drop f26, drop f27, drop f28, drop f29, drop f30, drop f31, drop f32,
1364
drop f33, drop f34, drop f35;
1366
** Insert Data into Master **
1367
INSERT into t2 set f1=1, f2=1, f3=1, f4='first', f8='f8: medium size blob', f10='f10:
1369
INSERT into t2 values (2, 2, 2, 'second',
1370
2.0, 'b', 2000.0002, 'f8: medium size blob', 2000, 'f10: some var char',
1373
3.0, 'b', 3000.0003, 'f8: medium size blob', 3000, 'f10: some var char',
1376
Warning 1264 Out of range value for column 'f9' at row 1
1377
Warning 1264 Out of range value for column 'f9' at row 2
1378
INSERT into t3 set f1=1, f2=1, f3=1, f4='first', f10='f10: some var char';
1379
INSERT into t4 set f1=1, f2=1, f3=1, f4='first', f7='f7: medium size blob', f10='f10:
1381
INSERT into t31 set f1=1, f2=1, f3=1, f4='first';
1382
INSERT into t31 set f1=1, f2=1, f3=2, f4='second',
1383
f9=2.2, f10='seven samurai', f28=222.222, f35='222';
1385
Warning 1366 Incorrect integer value: 'seven samurai' for column 'f10' at row 1
1386
INSERT into t31 values (1, 1, 3, 'third',
1387
/* f5 BIGINT, */ 333333333333333333333333,
1388
/* f6 BLOB, */ '3333333333333333333333',
1389
/* f7 DATE, */ '2007-07-18',
1390
/* f8 DATETIME, */ "2007-07-18",
1391
/* f9 FLOAT, */ 3.33333333,
1392
/* f10 INT, */ 333333333,
1393
/* f11 LONGBLOB, */ '3333333333333333333',
1394
/* f12 LONGTEXT, */ '3333333333333333333',
1395
/* f13 MEDIUMBLOB, */ '3333333333333333333',
1396
/* f14 MEDIUMINT, */ 33,
1397
/* f15 MEDIUMTEXT, */ 3.3,
1398
/* f16 REAL, */ 3.3,
1399
/* f17 SMALLINT, */ 3,
1400
/* f18 TEXT, */ '33',
1401
/* f19 TIME, */ '2:59:58.999',
1402
/* f20 TIMESTAMP, */ 20000303000000,
1403
/* f21 TINYBLOB, */ '3333',
1404
/* f22 TINYINT, */ 3,
1405
/* f23 TINYTEXT, */ '3',
1406
/* f24 YEAR, */ 3000,
1407
/* f25 BINARY(255), */ 'three_33333',
1408
/* f26 BIT(64), */ b'011',
1409
/* f27 CHAR(255), */ 'three',
1410
/* f28 DECIMAL(30,7), */ 3.333,
1411
/* f29 DOUBLE, */ 3.333333333333333333333333333,
1412
/* f30 ENUM ('a','b','c')*/ 'c',
1413
/* f31 FLOAT, */ 3.0,
1414
/* f32 NUMERIC(17,9), */ 3.3333,
1415
/* f33 SET ('a','b','c'),*/ 'c',
1416
/*f34 VARBINARY(1025),*/ '3333 minus 3',
1417
/*f35 VARCHAR(257),*/ 'three times three'
1420
Warning 1264 Out of range value for column 'f5' at row 1
1421
Warning 1264 Out of range value for column 'f24' at row 1
1422
INSERT into t31 values (1, 1, 4, 'fourth',
1423
/* f5 BIGINT, */ 333333333333333333333333,
1424
/* f6 BLOB, */ '3333333333333333333333',
1425
/* f7 DATE, */ '2007-07-18',
1426
/* f8 DATETIME, */ "2007-07-18",
1427
/* f9 FLOAT, */ 3.33333333,
1428
/* f10 INT, */ 333333333,
1429
/* f11 LONGBLOB, */ '3333333333333333333',
1430
/* f12 LONGTEXT, */ '3333333333333333333',
1431
/* f13 MEDIUMBLOB, */ '3333333333333333333',
1432
/* f14 MEDIUMINT, */ 33,
1433
/* f15 MEDIUMTEXT, */ 3.3,
1434
/* f16 REAL, */ 3.3,
1435
/* f17 SMALLINT, */ 3,
1436
/* f18 TEXT, */ '33',
1437
/* f19 TIME, */ '2:59:58.999',
1438
/* f20 TIMESTAMP, */ 20000303000000,
1439
/* f21 TINYBLOB, */ '3333',
1440
/* f22 TINYINT, */ 3,
1441
/* f23 TINYTEXT, */ '3',
1442
/* f24 YEAR, */ 3000,
1443
/* f25 BINARY(255), */ 'three_33333',
1444
/* f26 BIT(64), */ b'011',
1445
/* f27 CHAR(255), */ 'three',
1446
/* f28 DECIMAL(30,7), */ 3.333,
1447
/* f29 DOUBLE, */ 3.333333333333333333333333333,
1448
/* f30 ENUM ('a','b','c')*/ 'c',
1449
/* f31 FLOAT, */ 3.0,
1450
/* f32 NUMERIC(17,9), */ 3.3333,
1451
/* f33 SET ('a','b','c'),*/ 'c',
1452
/*f34 VARBINARY(1025),*/ '3333 minus 3',
1453
/*f35 VARCHAR(257),*/ 'three times three'
1456
/* f5 BIGINT, */ 333333333333333333333333,
1457
/* f6 BLOB, */ '3333333333333333333333',
1458
/* f7 DATE, */ '2007-07-18',
1459
/* f8 DATETIME, */ "2007-07-18",
1460
/* f9 FLOAT, */ 3.33333333,
1461
/* f10 INT, */ 333333333,
1462
/* f11 LONGBLOB, */ '3333333333333333333',
1463
/* f12 LONGTEXT, */ '3333333333333333333',
1464
/* f13 MEDIUMBLOB, */ '3333333333333333333',
1465
/* f14 MEDIUMINT, */ 33,
1466
/* f15 MEDIUMTEXT, */ 3.3,
1467
/* f16 REAL, */ 3.3,
1468
/* f17 SMALLINT, */ 3,
1469
/* f18 TEXT, */ '33',
1470
/* f19 TIME, */ '2:59:58.999',
1471
/* f20 TIMESTAMP, */ 20000303000000,
1472
/* f21 TINYBLOB, */ '3333',
1473
/* f22 TINYINT, */ 3,
1474
/* f23 TINYTEXT, */ '3',
1475
/* f24 YEAR, */ 3000,
1476
/* f25 BINARY(255), */ 'three_33333',
1477
/* f26 BIT(64), */ b'011',
1478
/* f27 CHAR(255), */ 'three',
1479
/* f28 DECIMAL(30,7), */ 3.333,
1480
/* f29 DOUBLE, */ 3.333333333333333333333333333,
1481
/* f30 ENUM ('a','b','c')*/ 'c',
1482
/* f31 FLOAT, */ 3.0,
1483
/* f32 NUMERIC(17,9), */ 3.3333,
1484
/* f33 SET ('a','b','c'),*/ 'c',
1485
/*f34 VARBINARY(1025),*/ '3333 minus 3',
1486
/*f35 VARCHAR(257),*/ 'three times three'
1489
/* f5 BIGINT, */ NULL,
1490
/* f6 BLOB, */ '3333333333333333333333',
1491
/* f7 DATE, */ '2007-07-18',
1492
/* f8 DATETIME, */ "2007-07-18",
1493
/* f9 FLOAT, */ 3.33333333,
1494
/* f10 INT, */ 333333333,
1495
/* f11 LONGBLOB, */ '3333333333333333333',
1496
/* f12 LONGTEXT, */ '3333333333333333333',
1497
/* f13 MEDIUMBLOB, */ '3333333333333333333',
1498
/* f14 MEDIUMINT, */ 33,
1499
/* f15 MEDIUMTEXT, */ 3.3,
1500
/* f16 REAL, */ 3.3,
1501
/* f17 SMALLINT, */ 3,
1502
/* f18 TEXT, */ '33',
1503
/* f19 TIME, */ '2:59:58.999',
1504
/* f20 TIMESTAMP, */ 20000303000000,
1505
/* f21 TINYBLOB, */ '3333',
1506
/* f22 TINYINT, */ 3,
1507
/* f23 TINYTEXT, */ '3',
1508
/* f24 YEAR, */ 3000,
1509
/* f25 BINARY(255), */ 'three_33333',
1510
/* f26 BIT(64), */ b'011',
1511
/* f27 CHAR(255), */ 'three',
1512
/* f28 DECIMAL(30,7), */ 3.333,
1513
/* f29 DOUBLE, */ 3.333333333333333333333333333,
1514
/* f30 ENUM ('a','b','c')*/ 'c',
1515
/* f31 FLOAT, */ 3.0,
1516
/* f32 NUMERIC(17,9), */ 3.3333,
1517
/* f33 SET ('a','b','c'),*/ 'c',
1518
/*f34 VARBINARY(1025),*/ '3333 minus 3',
1519
/*f35 VARCHAR(257),*/ NULL
1522
Warning 1264 Out of range value for column 'f5' at row 1
1523
Warning 1264 Out of range value for column 'f24' at row 1
1524
Warning 1264 Out of range value for column 'f5' at row 2
1525
Warning 1264 Out of range value for column 'f24' at row 2
1526
Warning 1264 Out of range value for column 'f24' at row 3
1528
** Sync slave with master **
1529
** Do selects from tables **
1531
select * from t1 order by f3;
1573
select * from t2 order by f1;
1578
select * from t3 order by f1;
1581
select * from t4 order by f1;
1584
select * from t31 order by f3;
1593
** Do updates master **
1595
update t31 set f5=555555555555555 where f3=6;
1596
update t31 set f2=2 where f3=2;
1597
update t31 set f1=NULL where f3=1;
1598
update t31 set f3=NULL, f27=NULL, f35='f35 new value' where f3=3;
1600
Warning 1048 Column 'f3' cannot be null
1602
** Delete from Master **
1610
** Check slave status **
1616
Master_Host 127.0.0.1
1620
Master_Log_File master-bin.000001
1621
Read_Master_Log_Pos #
1624
Relay_Master_Log_File master-bin.000001
1625
Slave_IO_Running Yes
1626
Slave_SQL_Running Yes
1630
Replicate_Ignore_Table
1631
Replicate_Wild_Do_Table
1632
Replicate_Wild_Ignore_Table
1636
Exec_Master_Log_Pos #
1638
Until_Condition None
1641
Master_SSL_Allowed No
1647
Seconds_Behind_Master #
1648
Master_SSL_Verify_Server_Cert No
1654
****************************************
1655
* columns in master at middle of table *
1656
* Expect: Proper error message *
1657
****************************************
1659
** Stop and Reset Slave **
1664
** create table slave side **
1665
CREATE TABLE t10 (a INT PRIMARY KEY, b BLOB, c CHAR(5)
1668
** Connect to master and create table **
1670
CREATE TABLE t10 (a INT KEY, b BLOB, f DOUBLE DEFAULT '233',
1671
c CHAR(5), e INT DEFAULT '1')ENGINE='InnoDB';
1677
*** Master Data Insert ***
1678
set @b1 = 'b1b1b1b1';
1679
set @b1 = concat(@b1,@b1);
1680
INSERT INTO t10 () VALUES(1,@b1,DEFAULT,'Kyle',DEFAULT),
1681
(2,@b1,DEFAULT,'JOE',DEFAULT),
1682
(3,@b1,DEFAULT,'QA',DEFAULT);
1684
********************************************
1685
*** Expect slave to fail with Error 1523 ***
1686
********************************************
1690
Master_Host 127.0.0.1
1694
Master_Log_File master-bin.000001
1695
Read_Master_Log_Pos #
1698
Relay_Master_Log_File master-bin.000001
1699
Slave_IO_Running Yes
1700
Slave_SQL_Running No
1704
Replicate_Ignore_Table
1705
Replicate_Wild_Do_Table
1706
Replicate_Wild_Ignore_Table
1708
Last_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 5, test.t10 has type 254
1710
Exec_Master_Log_Pos #
1712
Until_Condition None
1715
Master_SSL_Allowed No
1721
Seconds_Behind_Master #
1722
Master_SSL_Verify_Server_Cert No
1726
Last_SQL_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 5, test.t10 has type 254
1727
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
1733
*********************************************
1734
* More columns in master at middle of table *
1735
* Expect: Proper error message *
1736
*********************************************
1738
*** Create t11 on slave ***
1741
CREATE TABLE t11 (a INT PRIMARY KEY, b BLOB, c VARCHAR(254)
1744
*** Create t11 on Master ***
1745
CREATE TABLE t11 (a INT KEY, b BLOB, f TEXT,
1746
c CHAR(5) DEFAULT 'test', e INT DEFAULT '1')ENGINE='InnoDB';
1752
*** Master Data Insert ***
1753
set @b1 = 'b1b1b1b1';
1754
set @b1 = concat(@b1,@b1);
1755
INSERT INTO t11 () VALUES(1,@b1,'Testing is fun','Kyle',DEFAULT),
1756
(2,@b1,'Testing is cool','JOE',DEFAULT),
1757
(3,@b1,DEFAULT,'QA',DEFAULT);
1759
********************************************
1760
*** Expect slave to fail with Error 1523 ***
1761
********************************************
1765
Master_Host 127.0.0.1
1769
Master_Log_File master-bin.000001
1770
Read_Master_Log_Pos #
1773
Relay_Master_Log_File master-bin.000001
1774
Slave_IO_Running Yes
1775
Slave_SQL_Running No
1779
Replicate_Ignore_Table
1780
Replicate_Wild_Do_Table
1781
Replicate_Wild_Ignore_Table
1783
Last_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 252, test.t11 has type 15
1785
Exec_Master_Log_Pos #
1787
Until_Condition None
1790
Master_SSL_Allowed No
1796
Seconds_Behind_Master #
1797
Master_SSL_Verify_Server_Cert No
1801
Last_SQL_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 252, test.t11 has type 15
1802
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
1808
*********************************************
1809
* More columns in master at middle of table *
1810
* Expect: This one should pass blob-text *
1811
*********************************************
1813
*** Create t12 on slave ***
1816
CREATE TABLE t12 (a INT PRIMARY KEY, b BLOB, c BLOB
1819
*** Create t12 on Master ***
1820
CREATE TABLE t12 (a INT KEY, b BLOB, f TEXT,
1821
c CHAR(5) DEFAULT 'test', e INT DEFAULT '1')ENGINE='InnoDB';
1827
*** Master Data Insert ***
1828
set @b1 = 'b1b1b1b1';
1829
set @b1 = concat(@b1,@b1);
1830
INSERT INTO t12 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),
1831
(2,@b1,'JOE',DEFAULT,DEFAULT),
1832
(3,@b1,'QA',DEFAULT,DEFAULT);
1834
SELECT a,hex(b),f,c,e FROM t12 ORDER BY a;
1836
1 62316231623162316231623162316231 Kyle test 1
1837
2 62316231623162316231623162316231 JOE test 1
1838
3 62316231623162316231623162316231 QA test 1
1840
*** Select on Slave ***
1841
SELECT a,hex(b),c FROM t12 ORDER BY a;
1843
1 62316231623162316231623162316231 Kyle
1844
2 62316231623162316231623162316231 JOE
1845
3 62316231623162316231623162316231 QA
1850
****************************************************
1851
* - Alter Master adding columns at middle of table *
1852
* Expect: columns added *
1853
****************************************************
1856
*** Create t14 on slave ***
1859
CREATE TABLE t14 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
1862
*** Create t14 on Master ***
1863
CREATE TABLE t14 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
1865
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
1872
*** Master Data Insert ***
1873
ALTER TABLE t14 ADD COLUMN c2 DECIMAL(8,2) AFTER c1;
1874
ALTER TABLE t14 ADD COLUMN c3 TEXT AFTER c2;
1876
set @b1 = 'b1b1b1b1';
1877
set @b1 = concat(@b1,@b1);
1878
INSERT INTO t14 () VALUES(1,1.00,'Replication Testing Extra Col',@b1,'Kyle',DEFAULT,DEFAULT),
1879
(2,2.00,'This Test Should work',@b1,'JOE',DEFAULT,DEFAULT),
1880
(3,3.00,'If is does not, I will open a bug',@b1,'QA',DEFAULT,DEFAULT);
1882
SELECT c1,c2,c3,hex(c4),c5,c6,c7 FROM t14 ORDER BY c1;
1883
c1 c2 c3 hex(c4) c5 c6 c7
1884
1 1.00 Replication Testing Extra Col 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP
1885
2 2.00 This Test Should work 62316231623162316231623162316231 JOE 1 CURRENT_TIMESTAMP
1886
3 3.00 If is does not, I will open a bug 62316231623162316231623162316231 QA 1 CURRENT_TIMESTAMP
1888
*** Select on Slave ****
1889
SELECT c1,c2,c3,hex(c4),c5 FROM t14 ORDER BY c1;
1891
1 1.00 Replication Testing Extra Col 62316231623162316231623162316231 Kyle
1892
2 2.00 This Test Should work 62316231623162316231623162316231 JOE
1893
3 3.00 If is does not, I will open a bug 62316231623162316231623162316231 QA
1895
****************************************************
1896
* - Alter Master Dropping columns from the middle. *
1897
* Expect: columns dropped *
1898
****************************************************
1900
*** connect to master and drop columns ***
1901
ALTER TABLE t14 DROP COLUMN c2;
1902
ALTER TABLE t14 DROP COLUMN c7;
1904
*** Select from Master ***
1905
SELECT c1,c3,hex(c4),c5,c6 FROM t14 ORDER BY c1;
1907
1 Replication Testing Extra Col 62316231623162316231623162316231 Kyle 1
1908
2 This Test Should work 62316231623162316231623162316231 JOE 1
1909
3 If is does not, I will open a bug 62316231623162316231623162316231 QA 1
1916
Master_Host 127.0.0.1
1920
Master_Log_File master-bin.000001
1921
Read_Master_Log_Pos #
1924
Relay_Master_Log_File master-bin.000001
1925
Slave_IO_Running Yes
1926
Slave_SQL_Running No
1930
Replicate_Ignore_Table
1931
Replicate_Wild_Do_Table
1932
Replicate_Wild_Ignore_Table
1934
Last_Error Error 'Can't DROP 'c7'; check that column/key exists' on query. Default database: 'test'. Query: 'ALTER TABLE t14 DROP COLUMN c7'
1936
Exec_Master_Log_Pos #
1938
Until_Condition None
1941
Master_SSL_Allowed No
1947
Seconds_Behind_Master #
1948
Master_SSL_Verify_Server_Cert No
1952
Last_SQL_Error Error 'Can't DROP 'c7'; check that column/key exists' on query. Default database: 'test'. Query: 'ALTER TABLE t14 DROP COLUMN c7'
1962
*************************************************
1963
* - Alter Master adding columns at end of table *
1964
* Expect: Error 1054 *
1965
*************************************************
1967
*** Create t15 on slave ***
1970
CREATE TABLE t15 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
1973
*** Create t15 on Master ***
1974
CREATE TABLE t15 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
1976
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
1983
*** Master Data Insert ***
1984
ALTER TABLE t15 ADD COLUMN c2 DECIMAL(8,2) AFTER c7;
1985
set @b1 = 'b1b1b1b1';
1986
set @b1 = concat(@b1,@b1);
1987
INSERT INTO t15 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT,3.00),
1988
(2,@b1,'JOE',DEFAULT,DEFAULT,3.00),
1989
(3,@b1,'QA',DEFAULT,DEFAULT,3.00);
1990
SELECT c1,hex(c4),c5,c6,c7,c2 FROM t15 ORDER BY c1;
1991
c1 hex(c4) c5 c6 c7 c2
1992
1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP 3.00
1993
2 62316231623162316231623162316231 JOE 1 CURRENT_TIMESTAMP 3.00
1994
3 62316231623162316231623162316231 QA 1 CURRENT_TIMESTAMP 3.00
1996
********************************************
1997
*** Expect slave to fail with Error 1054 ***
1998
********************************************
2002
Master_Host 127.0.0.1
2006
Master_Log_File master-bin.000001
2007
Read_Master_Log_Pos #
2010
Relay_Master_Log_File master-bin.000001
2011
Slave_IO_Running Yes
2012
Slave_SQL_Running No
2016
Replicate_Ignore_Table
2017
Replicate_Wild_Do_Table
2018
Replicate_Wild_Ignore_Table
2020
Last_Error Error 'Unknown column 'c7' in 't15'' on query. Default database: 'test'. Query: 'ALTER TABLE t15 ADD COLUMN c2 DECIMAL(8,2) AFTER c7'
2022
Exec_Master_Log_Pos #
2024
Until_Condition None
2027
Master_SSL_Allowed No
2033
Seconds_Behind_Master #
2034
Master_SSL_Verify_Server_Cert No
2038
Last_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'
2048
************************************************
2049
* - Create index on Master column not on slave *
2051
************************************************
2053
*** Create t16 on slave ***
2056
CREATE TABLE t16 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
2059
*** Create t16 on Master ***
2060
CREATE TABLE t16 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
2062
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
2069
*** Master Create Index and Data Insert ***
2070
CREATE INDEX part_of_c6 ON t16 (c6);
2071
set @b1 = 'b1b1b1b1';
2072
set @b1 = concat(@b1,@b1);
2073
INSERT INTO t16 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),
2074
(2,@b1,'JOE',2,DEFAULT),
2075
(3,@b1,'QA',3,DEFAULT);
2076
SELECT c1,hex(c4),c5,c6,c7 FROM t16 ORDER BY c1;
2078
1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP
2079
2 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP
2080
3 62316231623162316231623162316231 QA 3 CURRENT_TIMESTAMP
2088
Master_Host 127.0.0.1
2092
Master_Log_File master-bin.000001
2093
Read_Master_Log_Pos #
2096
Relay_Master_Log_File master-bin.000001
2097
Slave_IO_Running Yes
2098
Slave_SQL_Running No
2102
Replicate_Ignore_Table
2103
Replicate_Wild_Do_Table
2104
Replicate_Wild_Ignore_Table
2106
Last_Error Error 'Key column 'c6' doesn't exist in table' on query. Default database: 'test'. Query: 'CREATE INDEX part_of_c6 ON t16 (c6)'
2108
Exec_Master_Log_Pos #
2110
Until_Condition None
2113
Master_SSL_Allowed No
2119
Seconds_Behind_Master #
2120
Master_SSL_Verify_Server_Cert No
2124
Last_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)'
2134
*****************************************************
2135
* - Delete rows using column on Master not on slave *
2136
* Expect: Rows Deleted *
2137
*****************************************************
2139
*** Create t17 on slave ***
2142
CREATE TABLE t17 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
2145
*** Create t17 on Master ***
2146
CREATE TABLE t17 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
2148
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
2155
*** Master Data Insert ***
2156
set @b1 = 'b1b1b1b1';
2157
set @b1 = concat(@b1,@b1);
2158
INSERT INTO t17 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),
2159
(2,@b1,'JOE',2,DEFAULT),
2160
(3,@b1,'QA',3,DEFAULT);
2161
SELECT c1,hex(c4),c5,c6,c7 FROM t17 ORDER BY c1;
2163
1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP
2164
2 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP
2165
3 62316231623162316231623162316231 QA 3 CURRENT_TIMESTAMP
2167
** Select * from Slave **
2168
SELECT c1,hex(c4),c5 FROM t17 ORDER BY c1;
2170
1 62316231623162316231623162316231 Kyle
2171
2 62316231623162316231623162316231 JOE
2172
3 62316231623162316231623162316231 QA
2174
** Delete from master **
2175
DELETE FROM t17 WHERE c6 = 3;
2176
SELECT c1,hex(c4),c5,c6,c7 FROM t17 ORDER BY c1;
2178
1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP
2179
2 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP
2182
SELECT c1,hex(c4),c5 FROM t17 ORDER BY c1;
2184
1 62316231623162316231623162316231 Kyle
2185
2 62316231623162316231623162316231 JOE
2189
*****************************************************
2190
* - Update row using column on Master not on slave *
2191
* Expect: Rows updated *
2192
*****************************************************
2196
*** Create t18 on slave ***
2200
CREATE TABLE t18 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
2203
*** Create t18 on Master ***
2204
CREATE TABLE t18 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
2206
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
2213
*** Master Data Insert ***
2214
set @b1 = 'b1b1b1b1';
2215
set @b1 = concat(@b1,@b1);
2216
INSERT INTO t18 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),
2217
(2,@b1,'JOE',2,DEFAULT),
2218
(3,@b1,'QA',3,DEFAULT);
2219
SELECT c1,hex(c4),c5,c6,c7 FROM t18 ORDER BY c1;
2221
1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP
2222
2 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP
2223
3 62316231623162316231623162316231 QA 3 CURRENT_TIMESTAMP
2225
** Select * from Slave **
2226
SELECT c1,hex(c4),c5 FROM t18 ORDER BY c1;
2228
1 62316231623162316231623162316231 Kyle
2229
2 62316231623162316231623162316231 JOE
2230
3 62316231623162316231623162316231 QA
2232
** update from master **
2233
UPDATE t18 SET c5 = 'TEST' WHERE c6 = 3;
2234
SELECT c1,hex(c4),c5,c6,c7 FROM t18 ORDER BY c1;
2236
1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP
2237
2 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP
2238
3 62316231623162316231623162316231 TEST 3 CURRENT_TIMESTAMP
2241
SELECT c1,hex(c4),c5 FROM t18 ORDER BY c1;
2243
1 62316231623162316231623162316231 Kyle
2244
2 62316231623162316231623162316231 JOE
2245
3 62316231623162316231623162316231 TEST
2249
*****************************************************
2250
* - Insert UUID column on Master not on slave *
2251
* Expect: Rows inserted *
2252
*****************************************************
2254
*** Create t5 on slave ***
2257
CREATE TABLE t5 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
2260
*** Create t5 on Master ***
2261
CREATE TABLE t5 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
2263
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
2270
*** Master Data Insert ***
2271
set @b1 = 'b1b1b1b1';
2272
INSERT INTO t5 () VALUES(1,@b1,'Kyle',UUID(),DEFAULT),
2273
(2,@b1,'JOE',UUID(),DEFAULT),
2274
(3,@b1,'QA',UUID(),DEFAULT);
2275
SELECT c1,hex(c4),c5,c6,c7 FROM t5 ORDER BY c1;
2277
1 6231623162316231 Kyle UUID TIME
2278
2 6231623162316231 JOE UUID TIME
2279
3 6231623162316231 QA UUID TIME
2281
** Select * from Slave **
2282
SELECT c1,hex(c4),c5 FROM t5 ORDER BY c1;
2284
1 6231623162316231 Kyle
2285
2 6231623162316231 JOE
2286
3 6231623162316231 QA
2289
set binlog_format=mixed;
2291
***********************************************************
2292
***********************************************************
2293
***************** Start of Testing ************************
2294
***********************************************************
2295
***********************************************************
2296
* This test format == binlog_format MIXED and engine == 'InnoDB'
2297
***********************************************************
2298
***********************************************************
2300
***** Testing more columns on the Master *****
2302
CREATE TABLE t1 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
2304
f5 FLOAT DEFAULT '2.00',
2305
f6 CHAR(4) DEFAULT 'TEST',
2310
f11 VARBINARY(64))ENGINE='InnoDB';
2312
* Alter Table on Slave and drop columns f5 through f11 *
2314
alter table t1 drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11;
2316
* Insert data in Master then update and delete some rows*
2318
* Select count and 20 rows from Master *
2320
SELECT COUNT(*) FROM t1;
2324
SELECT f1,f2,f3,f4,f5,f6,f7,f8,f9,
2325
hex(f10),hex(f11) FROM t1 ORDER BY f3 LIMIT 20;
2326
f1 f2 f3 f4 f5 f6 f7 f8 f9 hex(f10) hex(f11)
2327
2 2 2 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
2328
3 3 3 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
2329
5 5 5 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
2330
6 6 6 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
2331
8 8 8 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
2332
9 9 9 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
2333
11 11 11 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
2334
12 12 12 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
2335
14 14 14 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
2336
15 15 15 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
2337
17 17 17 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
2338
18 18 18 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
2339
20 20 20 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
2340
21 21 21 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
2341
23 23 23 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
2342
24 24 24 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
2343
26 26 26 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
2344
27 27 27 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
2345
29 29 29 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
2346
30 30 30 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456
2348
* Select count and 20 rows from Slave *
2350
SELECT COUNT(*) FROM t1;
2354
SELECT * FROM t1 ORDER BY f3 LIMIT 20;
2377
* Show Slave Status *
2381
Master_Host 127.0.0.1
2385
Master_Log_File master-bin.000001
2386
Read_Master_Log_Pos #
2389
Relay_Master_Log_File master-bin.000001
2390
Slave_IO_Running Yes
2391
Slave_SQL_Running Yes
2395
Replicate_Ignore_Table
2396
Replicate_Wild_Do_Table
2397
Replicate_Wild_Ignore_Table
2401
Exec_Master_Log_Pos #
2403
Until_Condition None
2406
Master_SSL_Allowed No
2412
Seconds_Behind_Master #
2413
Master_SSL_Verify_Server_Cert No
2420
***** Testing Altering table def scenario *****
2422
CREATE TABLE t2 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
2424
f5 DOUBLE DEFAULT '2.00',
2425
f6 ENUM('a', 'b', 'c') default 'a',
2426
f7 DECIMAL(17,9) default '1000.00',
2428
f9 NUMERIC(6,4) default '2000.00',
2430
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',
2431
f12 SET('a', 'b', 'c') default 'b')
2434
Warning 1264 Out of range value for column 'f9' at row 1
2436
CREATE TABLE t3 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
2438
f5 DOUBLE DEFAULT '2.00',
2439
f6 ENUM('a', 'b', 'c') default 'a',
2442
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',
2443
f12 SET('a', 'b', 'c') default 'b')
2446
CREATE TABLE t4 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
2448
f5 DOUBLE DEFAULT '2.00',
2449
f6 DECIMAL(17,9) default '1000.00',
2451
f8 NUMERIC(6,4) default '2000.00',
2453
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',
2457
Warning 1264 Out of range value for column 'f8' at row 1
2459
CREATE TABLE t31 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
2486
f30 ENUM ('a','b', 'c') default 'a',
2489
f33 SET ('a', 'b', 'c') default 'b',
2490
f34 VARBINARY(1025),
2494
** Alter tables on slave and drop columns **
2496
alter table t2 drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11, drop
2498
alter table t3 drop f5, drop f6, drop f8, drop f10, drop f11, drop f12;
2499
alter table t4 drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11;
2501
drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11,
2502
drop f12, drop f13, drop f14, drop f15, drop f16, drop f17, drop f18,
2503
drop f19, drop f20, drop f21, drop f22, drop f23, drop f24, drop f25,
2504
drop f26, drop f27, drop f28, drop f29, drop f30, drop f31, drop f32,
2505
drop f33, drop f34, drop f35;
2507
** Insert Data into Master **
2508
INSERT into t2 set f1=1, f2=1, f3=1, f4='first', f8='f8: medium size blob', f10='f10:
2510
INSERT into t2 values (2, 2, 2, 'second',
2511
2.0, 'b', 2000.0002, 'f8: medium size blob', 2000, 'f10: some var char',
2514
3.0, 'b', 3000.0003, 'f8: medium size blob', 3000, 'f10: some var char',
2517
Warning 1264 Out of range value for column 'f9' at row 1
2518
Warning 1264 Out of range value for column 'f9' at row 2
2519
INSERT into t3 set f1=1, f2=1, f3=1, f4='first', f10='f10: some var char';
2520
INSERT into t4 set f1=1, f2=1, f3=1, f4='first', f7='f7: medium size blob', f10='f10:
2522
INSERT into t31 set f1=1, f2=1, f3=1, f4='first';
2523
INSERT into t31 set f1=1, f2=1, f3=2, f4='second',
2524
f9=2.2, f10='seven samurai', f28=222.222, f35='222';
2526
Warning 1366 Incorrect integer value: 'seven samurai' for column 'f10' at row 1
2527
INSERT into t31 values (1, 1, 3, 'third',
2528
/* f5 BIGINT, */ 333333333333333333333333,
2529
/* f6 BLOB, */ '3333333333333333333333',
2530
/* f7 DATE, */ '2007-07-18',
2531
/* f8 DATETIME, */ "2007-07-18",
2532
/* f9 FLOAT, */ 3.33333333,
2533
/* f10 INT, */ 333333333,
2534
/* f11 LONGBLOB, */ '3333333333333333333',
2535
/* f12 LONGTEXT, */ '3333333333333333333',
2536
/* f13 MEDIUMBLOB, */ '3333333333333333333',
2537
/* f14 MEDIUMINT, */ 33,
2538
/* f15 MEDIUMTEXT, */ 3.3,
2539
/* f16 REAL, */ 3.3,
2540
/* f17 SMALLINT, */ 3,
2541
/* f18 TEXT, */ '33',
2542
/* f19 TIME, */ '2:59:58.999',
2543
/* f20 TIMESTAMP, */ 20000303000000,
2544
/* f21 TINYBLOB, */ '3333',
2545
/* f22 TINYINT, */ 3,
2546
/* f23 TINYTEXT, */ '3',
2547
/* f24 YEAR, */ 3000,
2548
/* f25 BINARY(255), */ 'three_33333',
2549
/* f26 BIT(64), */ b'011',
2550
/* f27 CHAR(255), */ 'three',
2551
/* f28 DECIMAL(30,7), */ 3.333,
2552
/* f29 DOUBLE, */ 3.333333333333333333333333333,
2553
/* f30 ENUM ('a','b','c')*/ 'c',
2554
/* f31 FLOAT, */ 3.0,
2555
/* f32 NUMERIC(17,9), */ 3.3333,
2556
/* f33 SET ('a','b','c'),*/ 'c',
2557
/*f34 VARBINARY(1025),*/ '3333 minus 3',
2558
/*f35 VARCHAR(257),*/ 'three times three'
2561
Warning 1264 Out of range value for column 'f5' at row 1
2562
Warning 1264 Out of range value for column 'f24' at row 1
2563
INSERT into t31 values (1, 1, 4, 'fourth',
2564
/* f5 BIGINT, */ 333333333333333333333333,
2565
/* f6 BLOB, */ '3333333333333333333333',
2566
/* f7 DATE, */ '2007-07-18',
2567
/* f8 DATETIME, */ "2007-07-18",
2568
/* f9 FLOAT, */ 3.33333333,
2569
/* f10 INT, */ 333333333,
2570
/* f11 LONGBLOB, */ '3333333333333333333',
2571
/* f12 LONGTEXT, */ '3333333333333333333',
2572
/* f13 MEDIUMBLOB, */ '3333333333333333333',
2573
/* f14 MEDIUMINT, */ 33,
2574
/* f15 MEDIUMTEXT, */ 3.3,
2575
/* f16 REAL, */ 3.3,
2576
/* f17 SMALLINT, */ 3,
2577
/* f18 TEXT, */ '33',
2578
/* f19 TIME, */ '2:59:58.999',
2579
/* f20 TIMESTAMP, */ 20000303000000,
2580
/* f21 TINYBLOB, */ '3333',
2581
/* f22 TINYINT, */ 3,
2582
/* f23 TINYTEXT, */ '3',
2583
/* f24 YEAR, */ 3000,
2584
/* f25 BINARY(255), */ 'three_33333',
2585
/* f26 BIT(64), */ b'011',
2586
/* f27 CHAR(255), */ 'three',
2587
/* f28 DECIMAL(30,7), */ 3.333,
2588
/* f29 DOUBLE, */ 3.333333333333333333333333333,
2589
/* f30 ENUM ('a','b','c')*/ 'c',
2590
/* f31 FLOAT, */ 3.0,
2591
/* f32 NUMERIC(17,9), */ 3.3333,
2592
/* f33 SET ('a','b','c'),*/ 'c',
2593
/*f34 VARBINARY(1025),*/ '3333 minus 3',
2594
/*f35 VARCHAR(257),*/ 'three times three'
2597
/* f5 BIGINT, */ 333333333333333333333333,
2598
/* f6 BLOB, */ '3333333333333333333333',
2599
/* f7 DATE, */ '2007-07-18',
2600
/* f8 DATETIME, */ "2007-07-18",
2601
/* f9 FLOAT, */ 3.33333333,
2602
/* f10 INT, */ 333333333,
2603
/* f11 LONGBLOB, */ '3333333333333333333',
2604
/* f12 LONGTEXT, */ '3333333333333333333',
2605
/* f13 MEDIUMBLOB, */ '3333333333333333333',
2606
/* f14 MEDIUMINT, */ 33,
2607
/* f15 MEDIUMTEXT, */ 3.3,
2608
/* f16 REAL, */ 3.3,
2609
/* f17 SMALLINT, */ 3,
2610
/* f18 TEXT, */ '33',
2611
/* f19 TIME, */ '2:59:58.999',
2612
/* f20 TIMESTAMP, */ 20000303000000,
2613
/* f21 TINYBLOB, */ '3333',
2614
/* f22 TINYINT, */ 3,
2615
/* f23 TINYTEXT, */ '3',
2616
/* f24 YEAR, */ 3000,
2617
/* f25 BINARY(255), */ 'three_33333',
2618
/* f26 BIT(64), */ b'011',
2619
/* f27 CHAR(255), */ 'three',
2620
/* f28 DECIMAL(30,7), */ 3.333,
2621
/* f29 DOUBLE, */ 3.333333333333333333333333333,
2622
/* f30 ENUM ('a','b','c')*/ 'c',
2623
/* f31 FLOAT, */ 3.0,
2624
/* f32 NUMERIC(17,9), */ 3.3333,
2625
/* f33 SET ('a','b','c'),*/ 'c',
2626
/*f34 VARBINARY(1025),*/ '3333 minus 3',
2627
/*f35 VARCHAR(257),*/ 'three times three'
2630
/* f5 BIGINT, */ NULL,
2631
/* f6 BLOB, */ '3333333333333333333333',
2632
/* f7 DATE, */ '2007-07-18',
2633
/* f8 DATETIME, */ "2007-07-18",
2634
/* f9 FLOAT, */ 3.33333333,
2635
/* f10 INT, */ 333333333,
2636
/* f11 LONGBLOB, */ '3333333333333333333',
2637
/* f12 LONGTEXT, */ '3333333333333333333',
2638
/* f13 MEDIUMBLOB, */ '3333333333333333333',
2639
/* f14 MEDIUMINT, */ 33,
2640
/* f15 MEDIUMTEXT, */ 3.3,
2641
/* f16 REAL, */ 3.3,
2642
/* f17 SMALLINT, */ 3,
2643
/* f18 TEXT, */ '33',
2644
/* f19 TIME, */ '2:59:58.999',
2645
/* f20 TIMESTAMP, */ 20000303000000,
2646
/* f21 TINYBLOB, */ '3333',
2647
/* f22 TINYINT, */ 3,
2648
/* f23 TINYTEXT, */ '3',
2649
/* f24 YEAR, */ 3000,
2650
/* f25 BINARY(255), */ 'three_33333',
2651
/* f26 BIT(64), */ b'011',
2652
/* f27 CHAR(255), */ 'three',
2653
/* f28 DECIMAL(30,7), */ 3.333,
2654
/* f29 DOUBLE, */ 3.333333333333333333333333333,
2655
/* f30 ENUM ('a','b','c')*/ 'c',
2656
/* f31 FLOAT, */ 3.0,
2657
/* f32 NUMERIC(17,9), */ 3.3333,
2658
/* f33 SET ('a','b','c'),*/ 'c',
2659
/*f34 VARBINARY(1025),*/ '3333 minus 3',
2660
/*f35 VARCHAR(257),*/ NULL
2663
Warning 1264 Out of range value for column 'f5' at row 1
2664
Warning 1264 Out of range value for column 'f24' at row 1
2665
Warning 1264 Out of range value for column 'f5' at row 2
2666
Warning 1264 Out of range value for column 'f24' at row 2
2667
Warning 1264 Out of range value for column 'f24' at row 3
2669
** Sync slave with master **
2670
** Do selects from tables **
2672
select * from t1 order by f3;
2714
select * from t2 order by f1;
2719
select * from t3 order by f1;
2722
select * from t4 order by f1;
2725
select * from t31 order by f3;
2734
** Do updates master **
2736
update t31 set f5=555555555555555 where f3=6;
2737
update t31 set f2=2 where f3=2;
2738
update t31 set f1=NULL where f3=1;
2739
update t31 set f3=NULL, f27=NULL, f35='f35 new value' where f3=3;
2741
Warning 1048 Column 'f3' cannot be null
2743
** Delete from Master **
2751
** Check slave status **
2757
Master_Host 127.0.0.1
2761
Master_Log_File master-bin.000001
2762
Read_Master_Log_Pos #
2765
Relay_Master_Log_File master-bin.000001
2766
Slave_IO_Running Yes
2767
Slave_SQL_Running Yes
2771
Replicate_Ignore_Table
2772
Replicate_Wild_Do_Table
2773
Replicate_Wild_Ignore_Table
2777
Exec_Master_Log_Pos #
2779
Until_Condition None
2782
Master_SSL_Allowed No
2788
Seconds_Behind_Master #
2789
Master_SSL_Verify_Server_Cert No
2795
****************************************
2796
* columns in master at middle of table *
2797
* Expect: Proper error message *
2798
****************************************
2800
** Stop and Reset Slave **
2805
** create table slave side **
2806
CREATE TABLE t10 (a INT PRIMARY KEY, b BLOB, c CHAR(5)
2809
** Connect to master and create table **
2811
CREATE TABLE t10 (a INT KEY, b BLOB, f DOUBLE DEFAULT '233',
2812
c CHAR(5), e INT DEFAULT '1')ENGINE='InnoDB';
2818
*** Master Data Insert ***
2819
set @b1 = 'b1b1b1b1';
2820
set @b1 = concat(@b1,@b1);
2821
INSERT INTO t10 () VALUES(1,@b1,DEFAULT,'Kyle',DEFAULT),
2822
(2,@b1,DEFAULT,'JOE',DEFAULT),
2823
(3,@b1,DEFAULT,'QA',DEFAULT);
2825
********************************************
2826
*** Expect slave to fail with Error 1523 ***
2827
********************************************
2831
Master_Host 127.0.0.1
2835
Master_Log_File master-bin.000001
2836
Read_Master_Log_Pos #
2839
Relay_Master_Log_File master-bin.000001
2840
Slave_IO_Running Yes
2841
Slave_SQL_Running No
2845
Replicate_Ignore_Table
2846
Replicate_Wild_Do_Table
2847
Replicate_Wild_Ignore_Table
2849
Last_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 5, test.t10 has type 254
2851
Exec_Master_Log_Pos #
2853
Until_Condition None
2856
Master_SSL_Allowed No
2862
Seconds_Behind_Master #
2863
Master_SSL_Verify_Server_Cert No
2867
Last_SQL_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 5, test.t10 has type 254
2868
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
2874
*********************************************
2875
* More columns in master at middle of table *
2876
* Expect: Proper error message *
2877
*********************************************
2879
*** Create t11 on slave ***
2882
CREATE TABLE t11 (a INT PRIMARY KEY, b BLOB, c VARCHAR(254)
2885
*** Create t11 on Master ***
2886
CREATE TABLE t11 (a INT KEY, b BLOB, f TEXT,
2887
c CHAR(5) DEFAULT 'test', e INT DEFAULT '1')ENGINE='InnoDB';
2893
*** Master Data Insert ***
2894
set @b1 = 'b1b1b1b1';
2895
set @b1 = concat(@b1,@b1);
2896
INSERT INTO t11 () VALUES(1,@b1,'Testing is fun','Kyle',DEFAULT),
2897
(2,@b1,'Testing is cool','JOE',DEFAULT),
2898
(3,@b1,DEFAULT,'QA',DEFAULT);
2900
********************************************
2901
*** Expect slave to fail with Error 1523 ***
2902
********************************************
2906
Master_Host 127.0.0.1
2910
Master_Log_File master-bin.000001
2911
Read_Master_Log_Pos #
2914
Relay_Master_Log_File master-bin.000001
2915
Slave_IO_Running Yes
2916
Slave_SQL_Running No
2920
Replicate_Ignore_Table
2921
Replicate_Wild_Do_Table
2922
Replicate_Wild_Ignore_Table
2924
Last_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 252, test.t11 has type 15
2926
Exec_Master_Log_Pos #
2928
Until_Condition None
2931
Master_SSL_Allowed No
2937
Seconds_Behind_Master #
2938
Master_SSL_Verify_Server_Cert No
2942
Last_SQL_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 252, test.t11 has type 15
2943
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
2949
*********************************************
2950
* More columns in master at middle of table *
2951
* Expect: This one should pass blob-text *
2952
*********************************************
2954
*** Create t12 on slave ***
2957
CREATE TABLE t12 (a INT PRIMARY KEY, b BLOB, c BLOB
2960
*** Create t12 on Master ***
2961
CREATE TABLE t12 (a INT KEY, b BLOB, f TEXT,
2962
c CHAR(5) DEFAULT 'test', e INT DEFAULT '1')ENGINE='InnoDB';
2968
*** Master Data Insert ***
2969
set @b1 = 'b1b1b1b1';
2970
set @b1 = concat(@b1,@b1);
2971
INSERT INTO t12 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),
2972
(2,@b1,'JOE',DEFAULT,DEFAULT),
2973
(3,@b1,'QA',DEFAULT,DEFAULT);
2975
SELECT a,hex(b),f,c,e FROM t12 ORDER BY a;
2977
1 62316231623162316231623162316231 Kyle test 1
2978
2 62316231623162316231623162316231 JOE test 1
2979
3 62316231623162316231623162316231 QA test 1
2981
*** Select on Slave ***
2982
SELECT a,hex(b),c FROM t12 ORDER BY a;
2984
1 62316231623162316231623162316231 Kyle
2985
2 62316231623162316231623162316231 JOE
2986
3 62316231623162316231623162316231 QA
2991
****************************************************
2992
* - Alter Master adding columns at middle of table *
2993
* Expect: columns added *
2994
****************************************************
2997
*** Create t14 on slave ***
3000
CREATE TABLE t14 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
3003
*** Create t14 on Master ***
3004
CREATE TABLE t14 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
3006
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
3013
*** Master Data Insert ***
3014
ALTER TABLE t14 ADD COLUMN c2 DECIMAL(8,2) AFTER c1;
3015
ALTER TABLE t14 ADD COLUMN c3 TEXT AFTER c2;
3017
set @b1 = 'b1b1b1b1';
3018
set @b1 = concat(@b1,@b1);
3019
INSERT INTO t14 () VALUES(1,1.00,'Replication Testing Extra Col',@b1,'Kyle',DEFAULT,DEFAULT),
3020
(2,2.00,'This Test Should work',@b1,'JOE',DEFAULT,DEFAULT),
3021
(3,3.00,'If is does not, I will open a bug',@b1,'QA',DEFAULT,DEFAULT);
3023
SELECT c1,c2,c3,hex(c4),c5,c6,c7 FROM t14 ORDER BY c1;
3024
c1 c2 c3 hex(c4) c5 c6 c7
3025
1 1.00 Replication Testing Extra Col 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP
3026
2 2.00 This Test Should work 62316231623162316231623162316231 JOE 1 CURRENT_TIMESTAMP
3027
3 3.00 If is does not, I will open a bug 62316231623162316231623162316231 QA 1 CURRENT_TIMESTAMP
3029
*** Select on Slave ****
3030
SELECT c1,c2,c3,hex(c4),c5 FROM t14 ORDER BY c1;
3032
1 1.00 Replication Testing Extra Col 62316231623162316231623162316231 Kyle
3033
2 2.00 This Test Should work 62316231623162316231623162316231 JOE
3034
3 3.00 If is does not, I will open a bug 62316231623162316231623162316231 QA
3036
****************************************************
3037
* - Alter Master Dropping columns from the middle. *
3038
* Expect: columns dropped *
3039
****************************************************
3041
*** connect to master and drop columns ***
3042
ALTER TABLE t14 DROP COLUMN c2;
3043
ALTER TABLE t14 DROP COLUMN c7;
3045
*** Select from Master ***
3046
SELECT c1,c3,hex(c4),c5,c6 FROM t14 ORDER BY c1;
3048
1 Replication Testing Extra Col 62316231623162316231623162316231 Kyle 1
3049
2 This Test Should work 62316231623162316231623162316231 JOE 1
3050
3 If is does not, I will open a bug 62316231623162316231623162316231 QA 1
3057
Master_Host 127.0.0.1
3061
Master_Log_File master-bin.000001
3062
Read_Master_Log_Pos #
3065
Relay_Master_Log_File master-bin.000001
3066
Slave_IO_Running Yes
3067
Slave_SQL_Running No
3071
Replicate_Ignore_Table
3072
Replicate_Wild_Do_Table
3073
Replicate_Wild_Ignore_Table
3075
Last_Error Error 'Can't DROP 'c7'; check that column/key exists' on query. Default database: 'test'. Query: 'ALTER TABLE t14 DROP COLUMN c7'
3077
Exec_Master_Log_Pos #
3079
Until_Condition None
3082
Master_SSL_Allowed No
3088
Seconds_Behind_Master #
3089
Master_SSL_Verify_Server_Cert No
3093
Last_SQL_Error Error 'Can't DROP 'c7'; check that column/key exists' on query. Default database: 'test'. Query: 'ALTER TABLE t14 DROP COLUMN c7'
3103
*************************************************
3104
* - Alter Master adding columns at end of table *
3105
* Expect: Error 1054 *
3106
*************************************************
3108
*** Create t15 on slave ***
3111
CREATE TABLE t15 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
3114
*** Create t15 on Master ***
3115
CREATE TABLE t15 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
3117
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
3124
*** Master Data Insert ***
3125
ALTER TABLE t15 ADD COLUMN c2 DECIMAL(8,2) AFTER c7;
3126
set @b1 = 'b1b1b1b1';
3127
set @b1 = concat(@b1,@b1);
3128
INSERT INTO t15 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT,3.00),
3129
(2,@b1,'JOE',DEFAULT,DEFAULT,3.00),
3130
(3,@b1,'QA',DEFAULT,DEFAULT,3.00);
3131
SELECT c1,hex(c4),c5,c6,c7,c2 FROM t15 ORDER BY c1;
3132
c1 hex(c4) c5 c6 c7 c2
3133
1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP 3.00
3134
2 62316231623162316231623162316231 JOE 1 CURRENT_TIMESTAMP 3.00
3135
3 62316231623162316231623162316231 QA 1 CURRENT_TIMESTAMP 3.00
3137
********************************************
3138
*** Expect slave to fail with Error 1054 ***
3139
********************************************
3143
Master_Host 127.0.0.1
3147
Master_Log_File master-bin.000001
3148
Read_Master_Log_Pos #
3151
Relay_Master_Log_File master-bin.000001
3152
Slave_IO_Running Yes
3153
Slave_SQL_Running No
3157
Replicate_Ignore_Table
3158
Replicate_Wild_Do_Table
3159
Replicate_Wild_Ignore_Table
3161
Last_Error Error 'Unknown column 'c7' in 't15'' on query. Default database: 'test'. Query: 'ALTER TABLE t15 ADD COLUMN c2 DECIMAL(8,2) AFTER c7'
3163
Exec_Master_Log_Pos #
3165
Until_Condition None
3168
Master_SSL_Allowed No
3174
Seconds_Behind_Master #
3175
Master_SSL_Verify_Server_Cert No
3179
Last_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'
3189
************************************************
3190
* - Create index on Master column not on slave *
3192
************************************************
3194
*** Create t16 on slave ***
3197
CREATE TABLE t16 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
3200
*** Create t16 on Master ***
3201
CREATE TABLE t16 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
3203
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
3210
*** Master Create Index and Data Insert ***
3211
CREATE INDEX part_of_c6 ON t16 (c6);
3212
set @b1 = 'b1b1b1b1';
3213
set @b1 = concat(@b1,@b1);
3214
INSERT INTO t16 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),
3215
(2,@b1,'JOE',2,DEFAULT),
3216
(3,@b1,'QA',3,DEFAULT);
3217
SELECT c1,hex(c4),c5,c6,c7 FROM t16 ORDER BY c1;
3219
1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP
3220
2 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP
3221
3 62316231623162316231623162316231 QA 3 CURRENT_TIMESTAMP
3229
Master_Host 127.0.0.1
3233
Master_Log_File master-bin.000001
3234
Read_Master_Log_Pos #
3237
Relay_Master_Log_File master-bin.000001
3238
Slave_IO_Running Yes
3239
Slave_SQL_Running No
3243
Replicate_Ignore_Table
3244
Replicate_Wild_Do_Table
3245
Replicate_Wild_Ignore_Table
3247
Last_Error Error 'Key column 'c6' doesn't exist in table' on query. Default database: 'test'. Query: 'CREATE INDEX part_of_c6 ON t16 (c6)'
3249
Exec_Master_Log_Pos #
3251
Until_Condition None
3254
Master_SSL_Allowed No
3260
Seconds_Behind_Master #
3261
Master_SSL_Verify_Server_Cert No
3265
Last_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)'
3275
*****************************************************
3276
* - Delete rows using column on Master not on slave *
3277
* Expect: Rows Deleted *
3278
*****************************************************
3280
*** Create t17 on slave ***
3283
CREATE TABLE t17 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
3286
*** Create t17 on Master ***
3287
CREATE TABLE t17 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
3289
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
3296
*** Master Data Insert ***
3297
set @b1 = 'b1b1b1b1';
3298
set @b1 = concat(@b1,@b1);
3299
INSERT INTO t17 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),
3300
(2,@b1,'JOE',2,DEFAULT),
3301
(3,@b1,'QA',3,DEFAULT);
3302
SELECT c1,hex(c4),c5,c6,c7 FROM t17 ORDER BY c1;
3304
1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP
3305
2 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP
3306
3 62316231623162316231623162316231 QA 3 CURRENT_TIMESTAMP
3308
** Select * from Slave **
3309
SELECT c1,hex(c4),c5 FROM t17 ORDER BY c1;
3311
1 62316231623162316231623162316231 Kyle
3312
2 62316231623162316231623162316231 JOE
3313
3 62316231623162316231623162316231 QA
3315
** Delete from master **
3316
DELETE FROM t17 WHERE c6 = 3;
3317
SELECT c1,hex(c4),c5,c6,c7 FROM t17 ORDER BY c1;
3319
1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP
3320
2 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP
3323
SELECT c1,hex(c4),c5 FROM t17 ORDER BY c1;
3325
1 62316231623162316231623162316231 Kyle
3326
2 62316231623162316231623162316231 JOE
3330
*****************************************************
3331
* - Update row using column on Master not on slave *
3332
* Expect: Rows updated *
3333
*****************************************************
3337
*** Create t18 on slave ***
3341
CREATE TABLE t18 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
3344
*** Create t18 on Master ***
3345
CREATE TABLE t18 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
3347
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
3354
*** Master Data Insert ***
3355
set @b1 = 'b1b1b1b1';
3356
set @b1 = concat(@b1,@b1);
3357
INSERT INTO t18 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),
3358
(2,@b1,'JOE',2,DEFAULT),
3359
(3,@b1,'QA',3,DEFAULT);
3360
SELECT c1,hex(c4),c5,c6,c7 FROM t18 ORDER BY c1;
3362
1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP
3363
2 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP
3364
3 62316231623162316231623162316231 QA 3 CURRENT_TIMESTAMP
3366
** Select * from Slave **
3367
SELECT c1,hex(c4),c5 FROM t18 ORDER BY c1;
3369
1 62316231623162316231623162316231 Kyle
3370
2 62316231623162316231623162316231 JOE
3371
3 62316231623162316231623162316231 QA
3373
** update from master **
3374
UPDATE t18 SET c5 = 'TEST' WHERE c6 = 3;
3375
SELECT c1,hex(c4),c5,c6,c7 FROM t18 ORDER BY c1;
3377
1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP
3378
2 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP
3379
3 62316231623162316231623162316231 TEST 3 CURRENT_TIMESTAMP
3382
SELECT c1,hex(c4),c5 FROM t18 ORDER BY c1;
3384
1 62316231623162316231623162316231 Kyle
3385
2 62316231623162316231623162316231 JOE
3386
3 62316231623162316231623162316231 TEST
3390
*****************************************************
3391
* - Insert UUID column on Master not on slave *
3392
* Expect: Rows inserted *
3393
*****************************************************
3395
*** Create t5 on slave ***
3398
CREATE TABLE t5 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
3401
*** Create t5 on Master ***
3402
CREATE TABLE t5 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
3404
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
3411
*** Master Data Insert ***
3412
set @b1 = 'b1b1b1b1';
3413
INSERT INTO t5 () VALUES(1,@b1,'Kyle',UUID(),DEFAULT),
3414
(2,@b1,'JOE',UUID(),DEFAULT),
3415
(3,@b1,'QA',UUID(),DEFAULT);
3416
SELECT c1,hex(c4),c5,c6,c7 FROM t5 ORDER BY c1;
3418
1 6231623162316231 Kyle UUID TIME
3419
2 6231623162316231 JOE UUID TIME
3420
3 6231623162316231 QA UUID TIME
3422
** Select * from Slave **
3423
SELECT c1,hex(c4),c5 FROM t5 ORDER BY c1;
3425
1 6231623162316231 Kyle
3426
2 6231623162316231 JOE
3427
3 6231623162316231 QA