Debian Codegraph @

mysql-test/suite/rpl/include/rpl_extra_col_slave.test

raw
1 #################################################
2 # Purpose: To test having extra columns on the slave.
3 ##################################################
4
5 # Some tests in here requre partitioning
6 -- source include/have_partition.inc
7
8 #################################################
9 ############ Different Table Def Test ###########
10 #################################################
11 # Purpose: To have different table def on the #
12 # master and slave. Most of these tests#
13 # should stop the slave. #
14 #################################################
15
16 call mtr.add_suppression("Slave: Unknown table 't6' error.* 1051");
17 call mtr.add_suppression("Slave SQL.*Column [0-9] of table .test.t[0-9]*. cannot be converted from type.* error.* 1677");
18
19 --echo **** Diff Table Def Start ****
20
21 ##############################################
22 ### Try to replicate w/ PK on diff columns ###
23 ### Should Stop Slave ###
24 ##############################################
25
26 sync_slave_with_master;
27 STOP SLAVE;
28 --source include/reset_slave.inc
29
30 SET @saved_slave_type_conversions = @@slave_type_conversions;
31 SET GLOBAL SLAVE_TYPE_CONVERSIONS = 'ALL_NON_LOSSY';
32
33 eval CREATE TABLE t1 (a INT, b INT PRIMARY KEY, c CHAR(20),
34 d FLOAT DEFAULT '2.00',
35 e CHAR(4) DEFAULT 'TEST')
36 ENGINE=$engine_type;
37
38 --echo *** Create t1 on Master ***
39 connection master;
40 eval CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c CHAR(10)
41 ) ENGINE=$engine_type;
42
43 RESET MASTER;
44
45 --echo *** Start Slave ***
46 connection slave;
47 START SLAVE;
48
49 --echo *** Master Data Insert ***
50 connection master;
51
52 INSERT INTO t1 () VALUES(1,2,'TEXAS'),(2,1,'AUSTIN'),(3,4,'QA');
53 SELECT * FROM t1 ORDER BY a;
54
55 --echo *** Select from slave ***
56 sync_slave_with_master;
57 SELECT * FROM t1 ORDER BY a;
58
59 SET GLOBAL SLAVE_TYPE_CONVERSIONS = @saved_slave_type_conversions;
60
61 --echo *** Drop t1 ***
62 connection master;
63 DROP TABLE t1;
64 sync_slave_with_master;
65
66 ############################################
67 ### Try to replicate CHAR(10) to CHAR(5) ###
68 ### Should Stop Slave or truncate value ###
69 ############################################
70
71 ## BUG22086
72 --echo *** Create t2 on slave ***
73 STOP SLAVE;
74 --source include/reset_slave.inc
75 eval CREATE TABLE t2 (a INT, b INT PRIMARY KEY, c CHAR(5),
76 d FLOAT DEFAULT '2.00',
77 e CHAR(5) DEFAULT 'TEST2')
78 ENGINE=$engine_type;
79
80 --echo *** Create t2 on Master ***
81 connection master;
82 eval CREATE TABLE t2 (a INT PRIMARY KEY, b INT, c CHAR(10)
83 ) ENGINE=$engine_type;
84 RESET MASTER;
85
86 --echo *** Master Data Insert ***
87 connection master;
88
89 INSERT INTO t2 () VALUES(1,2,'Kyle, TEX'),(2,1,'JOE AUSTIN'),(3,4,'QA TESTING');
90 SELECT * FROM t2 ORDER BY a;
91
92 --echo *** Start Slave ***
93 connection slave;
94 START SLAVE;
95 --let $slave_sql_errno= 1677
96 --let $show_slave_sql_error= 1
97 --source include/wait_for_slave_sql_error.inc
98 STOP SLAVE;
99 --source include/reset_slave.inc
100 SELECT * FROM t2 ORDER BY a;
101
102 connection master;
103 RESET MASTER;
104
105 connection slave;
106 START SLAVE;
107
108 --echo *** Drop t2 ***
109 connection master;
110 DROP TABLE t2;
111 sync_slave_with_master;
112
113 ####################################
114 ### Try to replicate BLOB to INT ###
115 ### Should Stop Slave ###
116 ####################################
117 --echo *** Create t3 on slave ***
118 STOP SLAVE;
119 --source include/reset_slave.inc
120 eval CREATE TABLE t3 (a INT, b INT PRIMARY KEY, c CHAR(20),
121 d FLOAT DEFAULT '2.00',
122 e CHAR(5) DEFAULT 'TEST2')
123 ENGINE=$engine_type;
124
125 --echo *** Create t3 on Master ***
126 connection master;
127 eval CREATE TABLE t3 (a BLOB, b INT PRIMARY KEY, c CHAR(20)
128 ) ENGINE=$engine_type;
129 RESET MASTER;
130
131 --echo *** Start Slave ***
132 connection slave;
133 START SLAVE;
134
135 --echo *** Master Data Insert ***
136 connection master;
137
138 set @b1 = 'b1';
139 set @b1 = concat(@b1,@b1);
140
141 INSERT INTO t3 () VALUES(@b1,2,'Kyle, TEX'),(@b1,1,'JOE AUSTIN'),(@b1,4,'QA TESTING');
142
143 --echo ********************************************
144 --echo *** Expect slave to fail with Error 1677 ***
145 --echo ********************************************
146 connection slave;
147 --let $slave_sql_errno= 1677
148 --let $slave_skip_counter= 2
149 --let $show_slave_sql_error= 1
150 --source include/wait_for_slave_sql_error_and_skip.inc
151
152 --echo *** Drop t3 ***
153 connection master;
154 DROP TABLE t3;
155 sync_slave_with_master;
156
157 #####################################################
158 # Columns with different types, more columns at end #
159 # Expect: proper error message (wrong types) #
160 #####################################################
161
162 --echo *** Create t4 on slave ***
163 STOP SLAVE;
164 --source include/reset_slave.inc
165 eval CREATE TABLE t4 (a INT, b INT PRIMARY KEY, c CHAR(20),
166 d FLOAT DEFAULT '2.00',
167 e CHAR(5) DEFAULT 'TEST2')
168 ENGINE=$engine_type;
169
170 --echo *** Create t4 on Master ***
171 connection master;
172 eval CREATE TABLE t4 (a DECIMAL(8,2), b INT PRIMARY KEY, c CHAR(20)
173 ) ENGINE=$engine_type;
174 RESET MASTER;
175
176 --echo *** Start Slave ***
177 connection slave;
178 START SLAVE;
179
180 --echo *** Master Data Insert ***
181 connection master;
182
183 INSERT INTO t4 () VALUES(100.22,2,'Kyle, TEX'),(200.26,1,'JOE AUSTIN'),
184 (30000.22,4,'QA TESTING');
185
186 --echo ********************************************
187 --echo *** Expect slave to fail with Error 1677 ***
188 --echo ********************************************
189 connection slave;
190 --let $slave_sql_errno= 1677
191 --let $slave_skip_counter= 2
192 --let $show_slave_sql_error= 1
193 --source include/wait_for_slave_sql_error_and_skip.inc
194
195 --echo *** Drop t4 ***
196 connection master;
197 DROP TABLE t4;
198 sync_slave_with_master;
199
200 #######################################################
201 # Columns with different types, same number of colums #
202 # Expect: Proper error message #
203 #######################################################
204
205 --echo *** Create t5 on slave ***
206 STOP SLAVE;
207 --source include/reset_slave.inc
208 eval CREATE TABLE t5 (a INT PRIMARY KEY, b CHAR(5),
209 c FLOAT, d INT, e DOUBLE,
210 f DECIMAL(8,2))ENGINE=$engine_type;
211
212 --echo *** Create t5 on Master ***
213 connection master;
214 eval CREATE TABLE t5 (a INT PRIMARY KEY, b VARCHAR(6),
215 c DECIMAL(8,2), d BIT, e BLOB,
216 f FLOAT) ENGINE=$engine_type;
217 RESET MASTER;
218
219 --echo *** Start Slave ***
220 connection slave;
221 START SLAVE;
222
223 --echo *** Master Data Insert ***
224 connection master;
225
226 INSERT INTO t5 () VALUES(1,'Kyle',200.23,1,'b1b1',23.00098),
227 (2,'JOE',300.01,0,'b2b2',1.0000009);
228
229 --echo ********************************************
230 --echo *** Expect slave to fail with Error 1677 ***
231 --echo ********************************************
232 connection slave;
233 --let $slave_sql_errno= 1677
234 --let $slave_skip_counter= 2
235 --let $show_slave_sql_error= 1
236 --source include/wait_for_slave_sql_error_and_skip.inc
237
238 --echo *** Drop t5 ***
239 connection master;
240 DROP TABLE t5;
241 sync_slave_with_master;
242
243 #######################################################
244 ################## Continued ##########################
245 #######################################################
246 # Columns with different types, same number of colums #
247 # Expect: Proper error message #
248 #######################################################
249
250 --echo *** Create t6 on slave ***
251 STOP SLAVE;
252 --source include/reset_slave.inc
253 eval CREATE TABLE t6 (a INT PRIMARY KEY, b CHAR(5),
254 c FLOAT, d INT)ENGINE=$engine_type;
255
256 --echo *** Create t6 on Master ***
257 connection master;
258 eval CREATE TABLE t6 (a INT PRIMARY KEY, b VARCHAR(6),
259 c DECIMAL(8,2), d BIT
260 ) ENGINE=$engine_type;
261 RESET MASTER;
262
263 --echo *** Start Slave ***
264 connection slave;
265 START SLAVE;
266
267 --echo *** Master Data Insert ***
268 connection master;
269
270 INSERT INTO t6 () VALUES(1,'Kyle',200.23,1),
271 (2,'JOE',300.01,0);
272
273 --echo ********************************************
274 --echo *** Expect slave to fail with Error 1677 ***
275 --echo ********************************************
276 connection slave;
277 --let $slave_sql_errno= 1677
278 --let $show_slave_sql_error= 1
279 --source include/wait_for_slave_sql_error.inc
280
281 --echo *** Drop t6 ***
282 --let $rpl_only_running_threads= 1
283 --source include/rpl_reset.inc
284
285 connection master;
286 DROP TABLE t6;
287 --sync_slave_with_master
288
289
290 --echo **** Diff Table Def End ****
291
292 #######################################
293 #### Extra Column on Slave Testing ####
294 #######################################
295 # Purpose: To test extra colums on the#
296 # Slave #
297 #######################################
298
299 --echo **** Extra Colums Start ****
300
301 ##########################################
302 # More columns in slave at end of table, #
303 # added columns have default values #
304 # Expect: it should work, default values #
305 # should be used #
306 ##########################################
307
308 --echo *** Create t7 on slave ***
309 STOP SLAVE;
310 --source include/reset_slave.inc
311 eval CREATE TABLE t7 (a INT KEY, b BLOB, c CHAR(5),
312 d TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00',
313 e CHAR(20) DEFAULT 'Extra Column Testing')
314 ENGINE=$engine_type;
315
316 --echo *** Create t7 on Master ***
317 connection master;
318 eval CREATE TABLE t7 (a INT PRIMARY KEY, b BLOB, c CHAR(5)
319 ) ENGINE=$engine_type;
320 RESET MASTER;
321
322 --echo *** Start Slave ***
323 connection slave;
324 START SLAVE;
325
326 --echo *** Master Data Insert ***
327 connection master;
328 set @b1 = 'b1';
329 set @b1 = concat(@b1,@b1);
330 INSERT INTO t7 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA');
331 SELECT * FROM t7 ORDER BY a;
332
333 --echo *** Select from slave ***
334 sync_slave_with_master;
335 SELECT * FROM t7 ORDER BY a;
336
337 --echo *** Drop t7 ***
338 connection master;
339 DROP TABLE t7;
340 sync_slave_with_master;
341
342 ###########################################
343 # More columns in slave at end of table, #
344 # added columns do not have default values#
345 # Expect: Proper error message #
346 ###########################################
347 # NOTE: This should fail but currently #
348 # works. BUG#22101 #
349 ###########################################
350 --echo *** Create t8 on slave ***
351 STOP SLAVE;
352 --source include/reset_slave.inc
353 eval CREATE TABLE t8 (a INT KEY, b BLOB, c CHAR(5),
354 d TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00',
355 e INT)ENGINE=$engine_type;
356
357 --echo *** Create t8 on Master ***
358 connection master;
359 eval CREATE TABLE t8 (a INT PRIMARY KEY, b BLOB, c CHAR(5)
360 ) ENGINE=$engine_type;
361 RESET MASTER;
362
363 --echo *** Start Slave ***
364 connection slave;
365 START SLAVE;
366
367 --echo *** Master Data Insert ***
368 connection master;
369 set @b1 = 'b1b1b1b1';
370 set @b1 = concat(@b1,@b1);
371 INSERT INTO t8 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA');
372
373 ### Uncomment once bug is fixed
374
375 #connection slave;
376 #--let $slave_sql_errno= SOMETHING
377 #--let $slave_skip_counter= 2
378 #--let $show_slave_sql_error= 1
379 #--source include/wait_for_slave_sql_error_and_skip.inc
380
381 --echo *** Drop t8 ***
382 connection master;
383 DROP TABLE t8;
384 sync_slave_with_master;
385
386 ###########################################
387 ############# Continued ###################
388 # More columns in slave at end of table, #
389 # added columns do not have default values#
390 # Expect: Proper error message #
391 ###########################################
392 # Bug#22234, Bug#23907 Extra Slave Col is not
393 # erroring on extra col with no default values.
394 ###############################################################
395 # Error reaction is up to sql_mode of the slave sql (bug#38173)
396 #--echo *** Create t9 on slave ***
397 STOP SLAVE;
398 --source include/reset_slave.inc
399 eval CREATE TABLE t9 (a INT KEY, b BLOB, c CHAR(5),
400 d TIMESTAMP NOT NULL DEFAULT 0,
401 e INT NOT NULL,
402 f text not null,
403 g text,
404 h blob not null,
405 i blob) ENGINE=$engine_type;
406
407 --echo *** Create t9 on Master ***
408 connection master;
409 eval CREATE TABLE t9 (a INT PRIMARY KEY, b BLOB, c CHAR(5)
410 ) ENGINE=$engine_type;
411 RESET MASTER;
412
413 --echo *** Start Slave ***
414 connection slave;
415 START SLAVE;
416
417 --echo *** Master Data Insert ***
418 connection master;
419 set @b1 = 'b1b1b1b1';
420
421 set @b1 = concat(@b1,@b1);
422 INSERT INTO t9 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA');
423
424 # the test would stop slave if @@sql_mode for the sql thread
425 # was set to strict. Otherwise, as with this tests setup,
426 # the implicit defaults will be inserted into fields even though
427 # they are declared without DEFAULT clause.
428
429 sync_slave_with_master;
430 select * from t9;
431
432 # todo: fix Bug #43992 slave sql thread can't tune own sql_mode ...
433 # and add/restore waiting for stop test
434
435 #--let $slave_sql_errno= SOMETHING
436 #--let $slave_skip_counter= 2
437 #--let $show_slave_sql_error= 1
438 #--source include/wait_for_slave_sql_error_and_skip.inc
439
440 #--echo *** Drop t9 ***
441 connection master;
442 DROP TABLE t9;
443 sync_slave_with_master;
444
445 ############################################
446 # More columns in slave at middle of table #
447 # Expect: Proper error message #
448 ############################################
449 --echo *** Create t10 on slave ***
450 STOP SLAVE;
451 --source include/reset_slave.inc
452 eval CREATE TABLE t10 (a INT KEY, b BLOB, f DOUBLE DEFAULT '233',
453 c CHAR(5), e INT DEFAULT '1')ENGINE=$engine_type;
454
455 --echo *** Create t10 on Master ***
456 connection master;
457 eval CREATE TABLE t10 (a INT PRIMARY KEY, b BLOB, c CHAR(5)
458 ) ENGINE=$engine_type;
459 RESET MASTER;
460
461 --echo *** Start Slave ***
462 connection slave;
463 START SLAVE;
464
465 --echo *** Master Data Insert ***
466 connection master;
467 set @b1 = 'b1b1b1b1';
468 set @b1 = concat(@b1,@b1);
469 INSERT INTO t10 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA');
470
471 --echo ********************************************
472 --echo *** Expect slave to fail with Error 1677 ***
473 --echo ********************************************
474 connection slave;
475 --let $slave_sql_errno= 1677
476 --let $slave_skip_counter= 2
477 --let $show_slave_sql_error= 1
478 --source include/wait_for_slave_sql_error_and_skip.inc
479
480 --echo *** Drop t10 ***
481 connection master;
482 DROP TABLE t10;
483 sync_slave_with_master;
484
485 ############################################
486 ############## Continued ###################
487 ############################################
488 # More columns in slave at middle of table #
489 # Expect: Proper error message #
490 ############################################
491 --echo *** Create t11 on slave ***
492 STOP SLAVE;
493 --source include/reset_slave.inc
494 eval CREATE TABLE t11 (a INT KEY, b BLOB, f INT,
495 c CHAR(5) DEFAULT 'test', e INT DEFAULT '1')ENGINE=$engine_type;
496
497 --echo *** Create t11 on Master ***
498 connection master;
499 eval CREATE TABLE t11 (a INT PRIMARY KEY, b BLOB, c VARCHAR(254)
500 ) ENGINE=$engine_type;
501 RESET MASTER;
502
503 --echo *** Start Slave ***
504 connection slave;
505 START SLAVE;
506
507 --echo *** Master Data Insert ***
508 connection master;
509 set @b1 = 'b1b1b1b1';
510 set @b1 = concat(@b1,@b1);
511 INSERT INTO t11 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA');
512
513 --echo ********************************************
514 --echo *** Expect slave to fail with Error 1677 ***
515 --echo ********************************************
516 connection slave;
517 --let $slave_sql_errno= 1677
518 --let $slave_skip_counter= 2
519 --let $show_slave_sql_error= 1
520 --source include/wait_for_slave_sql_error_and_skip.inc
521
522 --echo *** Drop t11 ***
523 connection master;
524 DROP TABLE t11;
525 sync_slave_with_master;
526
527 ############################################
528 ############## Continued ###################
529 ############################################
530 # More columns in slave at middle of table #
531 # Expect: This one should pass blob-text #
532 ############################################
533 --echo *** Create t12 on slave ***
534 STOP SLAVE;
535 --source include/reset_slave.inc
536 eval CREATE TABLE t12 (a INT KEY, b BLOB, f TEXT,
537 c CHAR(5) DEFAULT 'test', e INT DEFAULT '1')ENGINE=$engine_type;
538
539 --echo *** Create t12 on Master ***
540 connection master;
541 eval CREATE TABLE t12 (a INT PRIMARY KEY, b BLOB, c BLOB
542 ) ENGINE=$engine_type;
543 RESET MASTER;
544
545 --echo *** Start Slave ***
546 connection slave;
547 START SLAVE;
548
549 --echo *** Master Data Insert ***
550 connection master;
551 set @b1 = 'b1b1b1b1';
552 set @b1 = concat(@b1,@b1);
553 INSERT INTO t12 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA');
554 SELECT * FROM t12 ORDER BY a;
555
556 --echo *** Select on Slave ***
557 sync_slave_with_master;
558 SELECT * FROM t12 ORDER BY a;
559
560 --echo *** Drop t12 ***
561 connection master;
562 DROP TABLE t12;
563 sync_slave_with_master;
564
565 --echo **** Extra Colums End ****
566
567 ###############################
568 # BUG#22177 CURRENT_TIMESTAMP #
569 # Sould work with ^ #
570 ###############################
571 --echo *** BUG 22177 Start ***
572 --echo *** Create t13 on slave ***
573 STOP SLAVE;
574 --source include/reset_slave.inc
575 eval CREATE TABLE t13 (a INT KEY, b BLOB, c CHAR(5),
576 d INT DEFAULT '1',
577 e TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
578 )ENGINE=$engine_type;
579
580 --echo *** Create t13 on Master ***
581 connection master;
582 eval CREATE TABLE t13 (a INT PRIMARY KEY, b BLOB, c CHAR(5)
583 ) ENGINE=$engine_type;
584 RESET MASTER;
585
586 --echo *** Start Slave ***
587 connection slave;
588 START SLAVE;
589
590 --echo *** Master Data Insert ***
591 connection master;
592 set @b1 = 'b1b1b1b1';
593 set @b1 = concat(@b1,@b1);
594 INSERT INTO t13 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA');
595 SELECT * FROM t13 ORDER BY a;
596
597 --echo *** Select on Slave ****
598 sync_slave_with_master;
599 --replace_column 5 CURRENT_TIMESTAMP
600 SELECT * FROM t13 ORDER BY a;
601
602 --echo *** Drop t13 ***
603 connection master;
604 DROP TABLE t13;
605 sync_slave_with_master;
606
607 --echo *** 22117 END ***
608
609 ##############################
610 # ALTER MASTER TABLE TESTING #
611 ##############################
612
613 --echo *** Alter Master Table Testing Start ***
614
615 ####################################################
616 # - Alter Master adding columns at middle of table #
617 # Expect: columns added #
618 ####################################################
619
620 --echo *** Create t14 on slave ***
621 STOP SLAVE;
622 --source include/reset_slave.inc
623 eval CREATE TABLE t14 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
624 c6 INT DEFAULT '1',
625 c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
626 )ENGINE=$engine_type;
627
628 --echo *** Create t14 on Master ***
629 connection master;
630 eval CREATE TABLE t14 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
631 ) ENGINE=$engine_type;
632 RESET MASTER;
633
634 --echo *** Start Slave ***
635 connection slave;
636 START SLAVE;
637
638 --echo *** Master Data Insert ***
639 connection master;
640 ALTER TABLE t14 ADD COLUMN c2 DECIMAL(8,2) AFTER c1;
641 ALTER TABLE t14 ADD COLUMN c3 TEXT AFTER c2;
642 set @b1 = 'b1b1b1b1';
643 set @b1 = concat(@b1,@b1);
644 INSERT INTO t14 () VALUES(1,1.00,'Replication Testing Extra Col',@b1,'Kyle'),
645 (2,2.00,'This Test Should work',@b1,'JOE'),
646 (3,3.00,'If is does not, I will open a bug',@b1,'QA');
647 SELECT * FROM t14 ORDER BY c1;
648
649
650 --echo *** Select on Slave ****
651 sync_slave_with_master;
652 --replace_column 7 CURRENT_TIMESTAMP
653 SELECT * FROM t14 ORDER BY c1;
654
655 ####################################################
656 # - Alter Master drop column at end of table #
657 # Expect: column dropped #
658 ####################################################
659
660 --echo *** Create t14a on slave ***
661 STOP SLAVE;
662 --source include/reset_slave.inc
663 eval CREATE TABLE t14a (c1 INT KEY, c4 BLOB, c5 CHAR(5),
664 c6 INT DEFAULT '1',
665 c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
666 )ENGINE=$engine_type;
667
668 --echo *** Create t14a on Master ***
669 connection master;
670 eval CREATE TABLE t14a (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
671 ) ENGINE=$engine_type;
672 RESET MASTER;
673
674 --echo *** Start Slave ***
675 connection slave;
676 START SLAVE;
677
678 --echo *** Master Data Insert ***
679 connection master;
680 set @b1 = 'b1b1b1b1';
681 set @b1 = concat(@b1,@b1);
682 INSERT INTO t14a () VALUES(1,@b1,'Kyle'),
683 (2,@b1,'JOE'),
684 (3,@b1,'QA');
685
686 SELECT * FROM t14a ORDER BY c1;
687 --echo *** Select on Slave ****
688 sync_slave_with_master;
689 --replace_column 5 CURRENT_TIMESTAMP
690 SELECT * FROM t14a ORDER BY c1;
691 STOP SLAVE;
692 --source include/reset_slave.inc
693
694 --echo *** Master Drop c5 ***
695 connection master;
696 ALTER TABLE t14a DROP COLUMN c5;
697 RESET MASTER;
698
699 --echo *** Start Slave ***
700 connection slave;
701 START SLAVE;
702
703 --echo *** Master Data Insert ***
704 connection master;
705 set @b1 = 'b1b1b1b1';
706 set @b1 = concat(@b1,@b1);
707
708 INSERT INTO t14a () VALUES(4,@b1),
709 (5,@b1),
710 (6,@b1);
711 SELECT * FROM t14a ORDER BY c1;
712
713 --echo *** Select on Slave ****
714 sync_slave_with_master;
715 --replace_column 5 CURRENT_TIMESTAMP
716 SELECT * FROM t14a ORDER BY c1;
717
718 --connection master
719 DROP TABLE t14a;
720 --sync_slave_with_master
721
722 ####################################################
723 # - Alter Master Dropping columns from the middle. #
724 # Expect: columns dropped #
725 ####################################################
726
727 --echo *** connect to master and drop columns ***
728 connection master;
729 ALTER TABLE t14 DROP COLUMN c2;
730 ALTER TABLE t14 DROP COLUMN c4;
731 --echo *** Select from Master ***
732 SELECT * FROM t14 ORDER BY c1;
733
734 --echo *** Select from Slave ***
735 sync_slave_with_master;
736 --replace_column 5 CURRENT_TIMESTAMP
737 SELECT * FROM t14 ORDER BY c1;
738
739 --echo *** Drop t14 ***
740 connection master;
741 DROP TABLE t14;
742 sync_slave_with_master;
743
744 ##############################################################
745 # - Alter Master adding columns that already exist on slave. #
746 # Expect: proper error message #
747 ##############################################################
748
749 --echo *** Create t15 on slave ***
750 STOP SLAVE;
751 --source include/reset_slave.inc
752 eval CREATE TABLE t15 (c1 INT KEY, c2 DECIMAL(8,2), c3 TEXT,
753 c4 BLOB, c5 CHAR(5),
754 c6 INT DEFAULT '1',
755 c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
756 )ENGINE=$engine_type;
757
758 --echo *** Create t15 on Master ***
759 connection master;
760 eval CREATE TABLE t15 (c1 INT PRIMARY KEY, c2 DECIMAL(8,2), c3 TEXT,
761 c4 BLOB, c5 CHAR(5)) ENGINE=$engine_type;
762 RESET MASTER;
763
764 --echo *** Start Slave ***
765 connection slave;
766 START SLAVE;
767
768 call mtr.add_suppression("Error .Unknown table .t6.. on query.* error.* 1051");
769 call mtr.add_suppression("Error .Duplicate column name .c6.. on query.* error.* 1060");
770 call mtr.add_suppression("Table definition on master and slave does not match: Column . ...e mismatch.* error.* 1535");
771
772 --echo *** Master Data Insert ***
773 connection master;
774 set @b1 = 'b1b1b1b1';
775 set @b1 = concat(@b1,@b1);
776 INSERT INTO t15 () VALUES(1,1.00,'Replication Testing Extra Col',@b1,'Kyle'),
777 (2,2.00,'This Test Should work',@b1,'JOE'),
778 (3,3.00,'If is does not, I will open a bug',@b1,'QA');
779 SELECT * FROM t15 ORDER BY c1;
780
781
782 --echo *** Select on Slave ****
783 sync_slave_with_master;
784 --replace_column 7 CURRENT_TIMESTAMP
785 SELECT * FROM t15 ORDER BY c1;
786
787 --echo *** Add column on master that is a Extra on Slave ***
788 connection master;
789 ALTER TABLE t15 ADD COLUMN c6 INT AFTER c5;
790
791 --echo ********************************************
792 --echo *** Expect slave to fail with Error 1060 ***
793 --echo ********************************************
794 connection slave;
795 --let $slave_sql_errno= 1060
796 --let $slave_skip_counter= 1
797 --let $show_slave_sql_error= 1
798 --source include/wait_for_slave_sql_error_and_skip.inc
799
800 --echo *** Try to insert in master ****
801 connection master;
802 INSERT INTO t15 () VALUES(5,2.00,'Replication Testing',@b1,'Buda',2);
803 SELECT * FROM t15 ORDER BY c1;
804
805 #SHOW BINLOG EVENTS;
806
807 --echo *** Try to select from slave ****
808 sync_slave_with_master;
809 --replace_column 7 CURRENT_TIMESTAMP
810 SELECT * FROM t15 ORDER BY c1;
811
812 --echo *** DROP TABLE t15 ***
813 connection master;
814 DROP TABLE t15;
815 sync_slave_with_master;
816
817 ####################################
818 # - Alter Master and ADD PARTITION #
819 # Expect:? #
820 ####################################
821
822 --echo *** Create t16 on slave ***
823 STOP SLAVE;
824 --source include/reset_slave.inc
825 eval CREATE TABLE t16 (c1 INT KEY, c2 DECIMAL(8,2), c3 TEXT,
826 c4 BLOB, c5 CHAR(5),
827 c6 INT DEFAULT '1',
828 c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
829 )ENGINE=$engine_type;
830
831 --echo *** Create t16 on Master ***
832 connection master;
833 eval CREATE TABLE t16 (c1 INT PRIMARY KEY, c2 DECIMAL(8,2), c3 TEXT,
834 c4 BLOB, c5 CHAR(5))ENGINE=$engine_type;
835 RESET MASTER;
836
837 --echo *** Start Slave ***
838 connection slave;
839 START SLAVE;
840
841 --echo *** Master Data Insert ***
842 connection master;
843 set @b1 = 'b1b1b1b1';
844 set @b1 = concat(@b1,@b1);
845 INSERT INTO t16 () VALUES(1,1.00,'Replication Testing Extra Col',@b1,'Kyle'),
846 (2,2.00,'This Test Should work',@b1,'JOE'),
847 (3,3.00,'If is does not, I will open a bug',@b1,'QA');
848 SELECT * FROM t16 ORDER BY c1;
849
850 --echo *** Select on Slave ****
851 sync_slave_with_master;
852 --replace_column 7 CURRENT_TIMESTAMP
853 SELECT * FROM t16 ORDER BY c1;
854
855 --echo *** Add Partition on master ***
856 connection master;
857 ALTER TABLE t16 PARTITION BY KEY(c1) PARTITIONS 4;
858 INSERT INTO t16 () VALUES(4,1.00,'Replication Rocks',@b1,'Omer');
859 SHOW CREATE TABLE t16;
860
861 --echo *** Show table on Slave ****
862 sync_slave_with_master;
863 SHOW CREATE TABLE t16;
864
865 --echo *** DROP TABLE t16 ***
866 connection master;
867 DROP TABLE t16;
868 sync_slave_with_master;
869
870 --echo *** Alter Master End ***
871
872 ############################################
873 ### Try to replicate BIGINT to SMALLINT ###
874 ### Should Stop Slave ###
875 ############################################
876
877 --echo *** Create t17 on slave ***
878 STOP SLAVE;
879 --source include/reset_slave.inc
880 eval CREATE TABLE t17 (a SMALLINT, b INT PRIMARY KEY, c CHAR(5),
881 d FLOAT DEFAULT '2.00',
882 e CHAR(5) DEFAULT 'TEST2')
883 ENGINE=$engine_type;
884
885 --echo *** Create t17 on Master ***
886 connection master;
887 eval CREATE TABLE t17 (a BIGINT PRIMARY KEY, b INT, c CHAR(10)
888 ) ENGINE=$engine_type;
889 RESET MASTER;
890
891 --echo *** Start Slave ***
892 connection slave;
893 START SLAVE;
894
895 --echo *** Master Data Insert ***
896 connection master;
897
898 INSERT INTO t17 () VALUES(9223372036854775807,2,'Kyle, TEX');
899
900 --echo ********************************************
901 --echo *** Expect slave to fail with Error 1677 ***
902 --echo ********************************************
903 connection slave;
904 --let $slave_sql_errno= 1677
905 --let $slave_skip_counter= 2
906 --let $show_slave_sql_error= 1
907 --source include/wait_for_slave_sql_error_and_skip.inc
908
909 --echo ** DROP table t17 ***
910 connection master;
911 DROP TABLE t17;
912 sync_slave_with_master;