1
include/master-slave.inc
3
call mtr.add_suppression("Slave: Unknown table 't6' Error_code: 1051");
4
**** Diff Table Def Start ****
8
CREATE TABLE t1 (a INT, b INT PRIMARY KEY, c CHAR(20),
9
d FLOAT DEFAULT '2.00',
10
e CHAR(4) DEFAULT 'TEST')
12
*** Create t1 on Master ***
13
CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c CHAR(10)
18
*** Master Data Insert ***
19
INSERT INTO t1 () VALUES(1,2,'TEXAS'),(2,1,'AUSTIN'),(3,4,'QA');
20
SELECT * FROM t1 ORDER BY a;
25
*** Select from slave ***
26
SELECT * FROM t1 ORDER BY a;
33
*** Create t2 on slave ***
36
CREATE TABLE t2 (a INT, b INT PRIMARY KEY, c CHAR(5),
37
d FLOAT DEFAULT '2.00',
38
e CHAR(5) DEFAULT 'TEST2')
40
*** Create t2 on Master ***
41
CREATE TABLE t2 (a INT PRIMARY KEY, b INT, c CHAR(10)
44
*** Master Data Insert ***
45
INSERT INTO t2 () VALUES(1,2,'Kyle, TEX'),(2,1,'JOE AUSTIN'),(3,4,'QA TESTING');
46
SELECT * FROM t2 ORDER BY a;
53
include/wait_for_slave_sql_error.inc [errno=1535]
54
Last_SQL_Error = 'Table definition on master and slave does not match: Column 2 size mismatch - master has size 10, test.t2 on slave has size 6. Master's column size should be <= the slave's column size.'
57
SELECT * FROM t2 ORDER BY a;
63
*** Create t3 on slave ***
66
CREATE TABLE t3 (a INT, b INT PRIMARY KEY, c CHAR(20),
67
d FLOAT DEFAULT '2.00',
68
e CHAR(5) DEFAULT 'TEST2')
70
*** Create t3 on Master ***
71
CREATE TABLE t3 (a BLOB, b INT PRIMARY KEY, c CHAR(20)
76
*** Master Data Insert ***
78
set @b1 = concat(@b1,@b1);
79
INSERT INTO t3 () VALUES(@b1,2,'Kyle, TEX'),(@b1,1,'JOE AUSTIN'),(@b1,4,'QA TESTING');
80
********************************************
81
*** Expect slave to fail with Error 1535 ***
82
********************************************
83
include/wait_for_slave_sql_error_and_skip.inc [errno=1535]
84
Last_SQL_Error = 'Table definition on master and slave does not match: Column 0 type mismatch - received type 252, test.t3 has type 3'
87
*** Create t4 on slave ***
90
CREATE TABLE t4 (a INT, b INT PRIMARY KEY, c CHAR(20),
91
d FLOAT DEFAULT '2.00',
92
e CHAR(5) DEFAULT 'TEST2')
94
*** Create t4 on Master ***
95
CREATE TABLE t4 (a DECIMAL(8,2), b INT PRIMARY KEY, c CHAR(20)
100
*** Master Data Insert ***
101
INSERT INTO t4 () VALUES(100.22,2,'Kyle, TEX'),(200.26,1,'JOE AUSTIN'),
102
(30000.22,4,'QA TESTING');
103
********************************************
104
*** Expect slave to fail with Error 1535 ***
105
********************************************
106
include/wait_for_slave_sql_error_and_skip.inc [errno=1535]
107
Last_SQL_Error = 'Table definition on master and slave does not match: Column 0 type mismatch - received type 246, test.t4 has type 3'
110
*** Create t5 on slave ***
113
CREATE TABLE t5 (a INT PRIMARY KEY, b CHAR(5),
114
c FLOAT, d INT, e DOUBLE,
115
f DECIMAL(8,2))ENGINE='MyISAM';
116
*** Create t5 on Master ***
117
CREATE TABLE t5 (a INT PRIMARY KEY, b VARCHAR(6),
118
c DECIMAL(8,2), d BIT, e BLOB,
119
f FLOAT) ENGINE='MyISAM';
123
*** Master Data Insert ***
124
INSERT INTO t5 () VALUES(1,'Kyle',200.23,1,'b1b1',23.00098),
125
(2,'JOE',300.01,0,'b2b2',1.0000009);
126
********************************************
127
*** Expect slave to fail with Error 1535 ***
128
********************************************
129
include/wait_for_slave_sql_error_and_skip.inc [errno=1535]
130
Last_SQL_Error = 'Table definition on master and slave does not match: Column 5 type mismatch - received type 4, test.t5 has type 246'
133
*** Create t6 on slave ***
136
CREATE TABLE t6 (a INT PRIMARY KEY, b CHAR(5),
137
c FLOAT, d INT)ENGINE='MyISAM';
138
*** Create t6 on Master ***
139
CREATE TABLE t6 (a INT PRIMARY KEY, b VARCHAR(6),
140
c DECIMAL(8,2), d BIT
145
*** Master Data Insert ***
146
INSERT INTO t6 () VALUES(1,'Kyle',200.23,1),
148
********************************************
149
*** Expect slave to fail with Error 1535 ***
150
********************************************
151
include/wait_for_slave_sql_error.inc [errno=1535]
152
Last_SQL_Error = 'Table definition on master and slave does not match: Column 3 type mismatch - received type 16, test.t6 has type 3'
153
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=3;
158
**** Diff Table Def End ****
159
**** Extra Colums Start ****
160
*** Create t7 on slave ***
163
CREATE TABLE t7 (a INT KEY, b BLOB, c CHAR(5),
164
d TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00',
165
e CHAR(20) DEFAULT 'Extra Column Testing')
167
*** Create t7 on Master ***
168
CREATE TABLE t7 (a INT PRIMARY KEY, b BLOB, c CHAR(5)
173
*** Master Data Insert ***
175
set @b1 = concat(@b1,@b1);
176
INSERT INTO t7 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA');
177
SELECT * FROM t7 ORDER BY a;
182
*** Select from slave ***
183
SELECT * FROM t7 ORDER BY a;
185
1 b1b1 Kyle 0000-00-00 00:00:00 Extra Column Testing
186
2 b1b1 JOE 0000-00-00 00:00:00 Extra Column Testing
187
3 b1b1 QA 0000-00-00 00:00:00 Extra Column Testing
190
*** Create t8 on slave ***
193
CREATE TABLE t8 (a INT KEY, b BLOB, c CHAR(5),
194
d TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00',
195
e INT)ENGINE='MyISAM';
196
*** Create t8 on Master ***
197
CREATE TABLE t8 (a INT PRIMARY KEY, b BLOB, c CHAR(5)
202
*** Master Data Insert ***
203
set @b1 = 'b1b1b1b1';
204
set @b1 = concat(@b1,@b1);
205
INSERT INTO t8 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA');
210
CREATE TABLE t9 (a INT KEY, b BLOB, c CHAR(5),
216
i blob) ENGINE='MyISAM';
217
*** Create t9 on Master ***
218
CREATE TABLE t9 (a INT PRIMARY KEY, b BLOB, c CHAR(5)
223
*** Master Data Insert ***
224
set @b1 = 'b1b1b1b1';
225
set @b1 = concat(@b1,@b1);
226
INSERT INTO t9 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA');
229
1 b1b1b1b1b1b1b1b1 Kyle 0000-00-00 00:00:00 0 NULL NULL
230
2 b1b1b1b1b1b1b1b1 JOE 0000-00-00 00:00:00 0 NULL NULL
231
3 b1b1b1b1b1b1b1b1 QA 0000-00-00 00:00:00 0 NULL NULL
233
*** Create t10 on slave ***
236
CREATE TABLE t10 (a INT KEY, b BLOB, f DOUBLE DEFAULT '233',
237
c CHAR(5), e INT DEFAULT '1')ENGINE='MyISAM';
238
*** Create t10 on Master ***
239
CREATE TABLE t10 (a INT PRIMARY KEY, b BLOB, c CHAR(5)
244
*** Master Data Insert ***
245
set @b1 = 'b1b1b1b1';
246
set @b1 = concat(@b1,@b1);
247
INSERT INTO t10 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA');
248
********************************************
249
*** Expect slave to fail with Error 1535 ***
250
********************************************
251
include/wait_for_slave_sql_error_and_skip.inc [errno=1535]
252
Last_SQL_Error = 'Table definition on master and slave does not match: Column 2 type mismatch - received type 254, test.t10 has type 5'
255
*** Create t11 on slave ***
258
CREATE TABLE t11 (a INT KEY, b BLOB, f TEXT,
259
c CHAR(5) DEFAULT 'test', e INT DEFAULT '1')ENGINE='MyISAM';
260
*** Create t11 on Master ***
261
CREATE TABLE t11 (a INT PRIMARY KEY, b BLOB, c VARCHAR(254)
266
*** Master Data Insert ***
267
set @b1 = 'b1b1b1b1';
268
set @b1 = concat(@b1,@b1);
269
INSERT INTO t11 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA');
270
********************************************
271
*** Expect slave to fail with Error 1535 ***
272
********************************************
273
include/wait_for_slave_sql_error_and_skip.inc [errno=1535]
274
Last_SQL_Error = 'Table definition on master and slave does not match: Column 2 type mismatch - received type 15, test.t11 has type 252'
277
*** Create t12 on slave ***
280
CREATE TABLE t12 (a INT KEY, b BLOB, f TEXT,
281
c CHAR(5) DEFAULT 'test', e INT DEFAULT '1')ENGINE='MyISAM';
282
*** Create t12 on Master ***
283
CREATE TABLE t12 (a INT PRIMARY KEY, b BLOB, c BLOB
288
*** Master Data Insert ***
289
set @b1 = 'b1b1b1b1';
290
set @b1 = concat(@b1,@b1);
291
INSERT INTO t12 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA');
292
SELECT * FROM t12 ORDER BY a;
294
1 b1b1b1b1b1b1b1b1 Kyle
295
2 b1b1b1b1b1b1b1b1 JOE
296
3 b1b1b1b1b1b1b1b1 QA
297
*** Select on Slave ***
298
SELECT * FROM t12 ORDER BY a;
300
1 b1b1b1b1b1b1b1b1 Kyle test 1
301
2 b1b1b1b1b1b1b1b1 JOE test 1
302
3 b1b1b1b1b1b1b1b1 QA test 1
305
**** Extra Colums End ****
306
*** BUG 22177 Start ***
307
*** Create t13 on slave ***
310
CREATE TABLE t13 (a INT KEY, b BLOB, c CHAR(5),
312
e TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
314
*** Create t13 on Master ***
315
CREATE TABLE t13 (a INT PRIMARY KEY, b BLOB, c CHAR(5)
320
*** Master Data Insert ***
321
set @b1 = 'b1b1b1b1';
322
set @b1 = concat(@b1,@b1);
323
INSERT INTO t13 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA');
324
SELECT * FROM t13 ORDER BY a;
326
1 b1b1b1b1b1b1b1b1 Kyle
327
2 b1b1b1b1b1b1b1b1 JOE
328
3 b1b1b1b1b1b1b1b1 QA
329
*** Select on Slave ****
330
SELECT * FROM t13 ORDER BY a;
332
1 b1b1b1b1b1b1b1b1 Kyle 1 CURRENT_TIMESTAMP
333
2 b1b1b1b1b1b1b1b1 JOE 1 CURRENT_TIMESTAMP
334
3 b1b1b1b1b1b1b1b1 QA 1 CURRENT_TIMESTAMP
338
*** Alter Master Table Testing Start ***
339
*** Create t14 on slave ***
342
CREATE TABLE t14 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
344
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
346
*** Create t14 on Master ***
347
CREATE TABLE t14 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
352
*** Master Data Insert ***
353
ALTER TABLE t14 ADD COLUMN c2 DECIMAL(8,2) AFTER c1;
354
ALTER TABLE t14 ADD COLUMN c3 TEXT AFTER c2;
355
set @b1 = 'b1b1b1b1';
356
set @b1 = concat(@b1,@b1);
357
INSERT INTO t14 () VALUES(1,1.00,'Replication Testing Extra Col',@b1,'Kyle'),
358
(2,2.00,'This Test Should work',@b1,'JOE'),
359
(3,3.00,'If is does not, I will open a bug',@b1,'QA');
360
SELECT * FROM t14 ORDER BY c1;
362
1 1.00 Replication Testing Extra Col b1b1b1b1b1b1b1b1 Kyle
363
2 2.00 This Test Should work b1b1b1b1b1b1b1b1 JOE
364
3 3.00 If is does not, I will open a bug b1b1b1b1b1b1b1b1 QA
365
*** Select on Slave ****
366
SELECT * FROM t14 ORDER BY c1;
368
1 1.00 Replication Testing Extra Col b1b1b1b1b1b1b1b1 Kyle 1 CURRENT_TIMESTAMP
369
2 2.00 This Test Should work b1b1b1b1b1b1b1b1 JOE 1 CURRENT_TIMESTAMP
370
3 3.00 If is does not, I will open a bug b1b1b1b1b1b1b1b1 QA 1 CURRENT_TIMESTAMP
371
*** Create t14a on slave ***
374
CREATE TABLE t14a (c1 INT KEY, c4 BLOB, c5 CHAR(5),
376
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
378
*** Create t14a on Master ***
379
CREATE TABLE t14a (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
384
*** Master Data Insert ***
385
set @b1 = 'b1b1b1b1';
386
set @b1 = concat(@b1,@b1);
387
INSERT INTO t14a () VALUES(1,@b1,'Kyle'),
390
SELECT * FROM t14a ORDER BY c1;
392
1 b1b1b1b1b1b1b1b1 Kyle
393
2 b1b1b1b1b1b1b1b1 JOE
394
3 b1b1b1b1b1b1b1b1 QA
395
*** Select on Slave ****
396
SELECT * FROM t14a ORDER BY c1;
398
1 b1b1b1b1b1b1b1b1 Kyle 1 CURRENT_TIMESTAMP
399
2 b1b1b1b1b1b1b1b1 JOE 1 CURRENT_TIMESTAMP
400
3 b1b1b1b1b1b1b1b1 QA 1 CURRENT_TIMESTAMP
403
*** Master Drop c5 ***
404
ALTER TABLE t14a DROP COLUMN c5;
408
*** Master Data Insert ***
409
set @b1 = 'b1b1b1b1';
410
set @b1 = concat(@b1,@b1);
411
INSERT INTO t14a () VALUES(4,@b1),
414
SELECT * FROM t14a ORDER BY c1;
422
*** Select on Slave ****
423
SELECT * FROM t14a ORDER BY c1;
425
1 b1b1b1b1b1b1b1b1 Kyle 1 CURRENT_TIMESTAMP
426
2 b1b1b1b1b1b1b1b1 JOE 1 CURRENT_TIMESTAMP
427
3 b1b1b1b1b1b1b1b1 QA 1 CURRENT_TIMESTAMP
428
4 b1b1b1b1b1b1b1b1 NULL 1 CURRENT_TIMESTAMP
429
5 b1b1b1b1b1b1b1b1 NULL 1 CURRENT_TIMESTAMP
430
6 b1b1b1b1b1b1b1b1 NULL 1 CURRENT_TIMESTAMP
432
*** connect to master and drop columns ***
433
ALTER TABLE t14 DROP COLUMN c2;
434
ALTER TABLE t14 DROP COLUMN c4;
435
*** Select from Master ***
436
SELECT * FROM t14 ORDER BY c1;
438
1 Replication Testing Extra Col Kyle
439
2 This Test Should work JOE
440
3 If is does not, I will open a bug QA
441
*** Select from Slave ***
442
SELECT * FROM t14 ORDER BY c1;
444
1 Replication Testing Extra Col Kyle 1 CURRENT_TIMESTAMP
445
2 This Test Should work JOE 1 CURRENT_TIMESTAMP
446
3 If is does not, I will open a bug QA 1 CURRENT_TIMESTAMP
449
*** Create t15 on slave ***
452
CREATE TABLE t15 (c1 INT KEY, c2 DECIMAL(8,2), c3 TEXT,
455
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
457
*** Create t15 on Master ***
458
CREATE TABLE t15 (c1 INT PRIMARY KEY, c2 DECIMAL(8,2), c3 TEXT,
459
c4 BLOB, c5 CHAR(5)) ENGINE='MyISAM';
463
call mtr.add_suppression("Slave SQL.*Error .Unknown table .t6.. on query.* Error_code: 1051");
464
call mtr.add_suppression("Slave SQL.*Error .Duplicate column name .c6.. on query.* Error_code: 1060");
465
call mtr.add_suppression("Slave SQL.*Table definition on master and slave does not match: Column . ...e mismatch.* Error_code: 1535");
466
*** Master Data Insert ***
467
set @b1 = 'b1b1b1b1';
468
set @b1 = concat(@b1,@b1);
469
INSERT INTO t15 () VALUES(1,1.00,'Replication Testing Extra Col',@b1,'Kyle'),
470
(2,2.00,'This Test Should work',@b1,'JOE'),
471
(3,3.00,'If is does not, I will open a bug',@b1,'QA');
472
SELECT * FROM t15 ORDER BY c1;
474
1 1.00 Replication Testing Extra Col b1b1b1b1b1b1b1b1 Kyle
475
2 2.00 This Test Should work b1b1b1b1b1b1b1b1 JOE
476
3 3.00 If is does not, I will open a bug b1b1b1b1b1b1b1b1 QA
477
*** Select on Slave ****
478
SELECT * FROM t15 ORDER BY c1;
480
1 1.00 Replication Testing Extra Col b1b1b1b1b1b1b1b1 Kyle 1 CURRENT_TIMESTAMP
481
2 2.00 This Test Should work b1b1b1b1b1b1b1b1 JOE 1 CURRENT_TIMESTAMP
482
3 3.00 If is does not, I will open a bug b1b1b1b1b1b1b1b1 QA 1 CURRENT_TIMESTAMP
483
*** Add column on master that is a Extra on Slave ***
484
ALTER TABLE t15 ADD COLUMN c6 INT AFTER c5;
485
********************************************
486
*** Expect slave to fail with Error 1060 ***
487
********************************************
488
include/wait_for_slave_sql_error_and_skip.inc [errno=1060]
489
Last_SQL_Error = 'Error 'Duplicate column name 'c6'' on query. Default database: 'test'. Query: 'ALTER TABLE t15 ADD COLUMN c6 INT AFTER c5''
490
*** Try to insert in master ****
491
INSERT INTO t15 () VALUES(5,2.00,'Replication Testing',@b1,'Buda',2);
492
SELECT * FROM t15 ORDER BY c1;
494
1 1.00 Replication Testing Extra Col b1b1b1b1b1b1b1b1 Kyle NULL
495
2 2.00 This Test Should work b1b1b1b1b1b1b1b1 JOE NULL
496
3 3.00 If is does not, I will open a bug b1b1b1b1b1b1b1b1 QA NULL
497
5 2.00 Replication Testing b1b1b1b1b1b1b1b1 Buda 2
498
*** Try to select from slave ****
499
SELECT * FROM t15 ORDER BY c1;
501
1 1.00 Replication Testing Extra Col b1b1b1b1b1b1b1b1 Kyle 1 CURRENT_TIMESTAMP
502
2 2.00 This Test Should work b1b1b1b1b1b1b1b1 JOE 1 CURRENT_TIMESTAMP
503
3 3.00 If is does not, I will open a bug b1b1b1b1b1b1b1b1 QA 1 CURRENT_TIMESTAMP
504
5 2.00 Replication Testing b1b1b1b1b1b1b1b1 Buda 2 CURRENT_TIMESTAMP
505
*** DROP TABLE t15 ***
507
*** Create t16 on slave ***
510
CREATE TABLE t16 (c1 INT KEY, c2 DECIMAL(8,2), c3 TEXT,
513
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
515
*** Create t16 on Master ***
516
CREATE TABLE t16 (c1 INT PRIMARY KEY, c2 DECIMAL(8,2), c3 TEXT,
517
c4 BLOB, c5 CHAR(5))ENGINE='MyISAM';
521
*** Master Data Insert ***
522
set @b1 = 'b1b1b1b1';
523
set @b1 = concat(@b1,@b1);
524
INSERT INTO t16 () VALUES(1,1.00,'Replication Testing Extra Col',@b1,'Kyle'),
525
(2,2.00,'This Test Should work',@b1,'JOE'),
526
(3,3.00,'If is does not, I will open a bug',@b1,'QA');
527
SELECT * FROM t16 ORDER BY c1;
529
1 1.00 Replication Testing Extra Col b1b1b1b1b1b1b1b1 Kyle
530
2 2.00 This Test Should work b1b1b1b1b1b1b1b1 JOE
531
3 3.00 If is does not, I will open a bug b1b1b1b1b1b1b1b1 QA
532
*** Select on Slave ****
533
SELECT * FROM t16 ORDER BY c1;
535
1 1.00 Replication Testing Extra Col b1b1b1b1b1b1b1b1 Kyle 1 CURRENT_TIMESTAMP
536
2 2.00 This Test Should work b1b1b1b1b1b1b1b1 JOE 1 CURRENT_TIMESTAMP
537
3 3.00 If is does not, I will open a bug b1b1b1b1b1b1b1b1 QA 1 CURRENT_TIMESTAMP
538
*** Add Partition on master ***
539
ALTER TABLE t16 PARTITION BY KEY(c1) PARTITIONS 4;
540
INSERT INTO t16 () VALUES(4,1.00,'Replication Rocks',@b1,'Omer');
541
SHOW CREATE TABLE t16;
543
t16 CREATE TABLE `t16` (
544
`c1` int(11) NOT NULL,
545
`c2` decimal(8,2) DEFAULT NULL,
548
`c5` char(5) DEFAULT NULL,
550
) ENGINE=MyISAM DEFAULT CHARSET=latin1
551
/*!50100 PARTITION BY KEY (c1)
553
*** Show table on Slave ****
554
SHOW CREATE TABLE t16;
556
t16 CREATE TABLE `t16` (
557
`c1` int(11) NOT NULL,
558
`c2` decimal(8,2) DEFAULT NULL,
561
`c5` char(5) DEFAULT NULL,
562
`c6` int(11) DEFAULT '1',
563
`c7` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
565
) ENGINE=MyISAM DEFAULT CHARSET=latin1
566
/*!50100 PARTITION BY KEY (c1)
568
*** DROP TABLE t16 ***
570
*** Alter Master End ***
571
*** Create t17 on slave ***
574
CREATE TABLE t17 (a SMALLINT, b INT PRIMARY KEY, c CHAR(5),
575
d FLOAT DEFAULT '2.00',
576
e CHAR(5) DEFAULT 'TEST2')
578
*** Create t17 on Master ***
579
CREATE TABLE t17 (a BIGINT PRIMARY KEY, b INT, c CHAR(10)
584
*** Master Data Insert ***
585
INSERT INTO t17 () VALUES(9223372036854775807,2,'Kyle, TEX');
586
********************************************
587
*** Expect slave to fail with Error 1535 ***
588
********************************************
589
include/wait_for_slave_sql_error_and_skip.inc [errno=1535]
590
Last_SQL_Error = 'Table definition on master and slave does not match: Column 0 type mismatch - received type 8, test.t17 has type 2'
591
** DROP table t17 ***