Debian Codegraph @

mysql-test/suite/rpl/include/rpl_extra_col_master.inc

raw
1 #############################################################
2 # Purpose: To test having extra columns on the master WL#3915
3 # engine inspecific sourced part
4 #############################################################
5
6 #
7 # Setup differently defined tables on master and slave
8 #
9
10 # Def on master: t (f_1 type_m_1,... f_s type_m_s, f_s1, f_m)
11 # Def on slave: t (f_1 type_s_1,... f_s type_s_s)
12 # where type_mi,type_si (0 < i-1 <s1) pairs are compatible types (WL#3228)
13 # Arbitrary paramaters of the test are:
14 # 1. the tables type
15 # 2. the types of the extra master's column f_s1,..., f_m
16 # 3. the numbers of common columns `s'
17 # 4. and extra columns `m' are par
18 #
19 # optionally
20 #
21 # 5. vary the common columns type within compatible ranges.
22
23 #
24 # constant size column type:
25
26 #BIGINT
27 #BLOB
28 #DATE
29 #DATETIME
30 #FLOAT
31 #INT, INTEGER
32 #LONGBLOB
33 #LONGTEXT
34 #MEDIUMBLOB
35 #MEDIUMINT
36 #MEDIUMTEXT
37 #REAL
38 #SMALLINT
39 #TEXT
40 #TIME
41 #TIMESTAMP
42 #TINYBLOB
43 #TINYINT
44 #TINYTEXT
45 #YEAR
46
47 # variable size column types:
48
49 #BINARY(M)
50 #BIT(M)
51 #CHAR(M)
52 #DECIMAL(M,D)
53 #DOUBLE[P]
54 #ENUM
55 #FLOAT(p)
56 #NUMERIC(M,D)
57 #SET
58 #VARBINARY(M)
59 #VARCHAR(M)
60 #
61
62 --disable_query_log
63 call mtr.add_suppression("Could not read packet:.* errno: 11");
64 --enable_query_log
65
66 --let $_saved_conn= $CURRENT_CONNECTION
67
68 let $binformat = `SHOW VARIABLES LIKE '%binlog_format%'`;
69 --echo
70 --echo ***********************************************************
71 --echo ***********************************************************
72 --echo ***************** Start of Testing ************************
73 --echo ***********************************************************
74 --echo ***********************************************************
75 --echo * This test format == $binformat and engine == $engine_type
76 --echo ***********************************************************
77 --echo ***********************************************************
78 --echo
79 --echo ***** Testing more columns on the Master *****
80 --echo
81 connection master;
82 eval CREATE TABLE t1 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
83 /* extra */
84 f5 FLOAT DEFAULT '2.00',
85 f6 CHAR(4) DEFAULT 'TEST',
86 f7 INT DEFAULT '0',
87 f8 TEXT,
88 f9 LONGBLOB,
89 f10 BIT(63),
90 f11 VARBINARY(64))ENGINE=$engine_type CHARSET=latin1;
91 --echo
92 --echo * Alter Table on Slave and drop columns f5 through f11 *
93 --echo
94 sync_slave_with_master;
95 alter table t1 drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11;
96
97 --echo
98 --echo * Insert data in Master then update and delete some rows*
99 --echo
100 connection master;
101 let $j= 50;
102 --disable_query_log
103 while ($j)
104 {
105 eval INSERT INTO t1 VALUES ($j, $j, $j, 'second', 2.0, 'kaks', 2,
106 'got stolen from the paradise',
107 'very fat blob', b'01010101010101',
108 0x123456);
109 dec $j;
110 }
111 let $j= 30;
112 while ($j)
113 {
114 eval update t1 set f4= 'next' where f1=$j;
115 dec $j;
116 dec $j;
117 eval delete from t1 where f1=$j;
118 dec $j;
119 }
120 --enable_query_log
121
122 --echo * Select count and 20 rows from Master *
123 --echo
124 SELECT COUNT(*) FROM t1;
125 --echo
126 SELECT f1,f2,f3,f4,f5,f6,f7,f8,f9,
127 hex(f10),hex(f11) FROM t1 ORDER BY f3 LIMIT 20;
128
129 #connection slave;
130
131 --disable_query_log
132 call mtr.add_suppression("Slave SQL.*Table definition on master and slave does not match: Column 2 type mismatch.* 1535");
133 call mtr.add_suppression("Slave.*Can.t DROP COLUMN .c7.; check that .* exists.* error.* 1091");
134 call mtr.add_suppression("Slave.*Unknown column .c7. in .t15.* error.* 1054");
135 call mtr.add_suppression("Slave.*Key column .c6. doesn.t exist in table.* error.* 1072");
136 call mtr.add_suppression("Slave SQL.*Column 2 of table .test.t1.. cannot be converted from type.* error.* 1677");
137 --enable_query_log
138
139 sync_slave_with_master;
140 --echo
141 --echo * Select count and 20 rows from Slave *
142 --echo
143 SELECT COUNT(*) FROM t1;
144 --echo
145 SELECT * FROM t1 ORDER BY f3 LIMIT 20;
146
147 --source include/check_slave_is_running.inc
148
149 ### Altering table def scenario
150 --echo
151 --echo ***** Testing Altering table def scenario *****
152 --echo
153
154 connection master;
155
156 eval CREATE TABLE t2 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
157 /* extra */
158 f5 DOUBLE DEFAULT '2.00',
159 f6 ENUM('a', 'b', 'c') default 'a',
160 f7 DECIMAL(17,9) default '1000.00',
161 f8 MEDIUMBLOB,
162 f9 NUMERIC(6,2) default '2000.00',
163 f10 VARCHAR(1024),
164 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',
165 f12 SET('a', 'b', 'c') default 'b')
166 ENGINE=$engine_type CHARSET=latin1;
167 --echo
168 eval CREATE TABLE t3 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
169 /* extra */
170 f5 DOUBLE DEFAULT '2.00',
171 f6 ENUM('a', 'b', 'c') default 'a',
172 f8 MEDIUMBLOB,
173 f10 VARCHAR(1024),
174 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',
175 f12 SET('a', 'b', 'c') default 'b')
176 ENGINE=$engine_type CHARSET=latin1;
177
178 --echo
179 # no ENUM and SET
180 eval CREATE TABLE t4 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
181 /* extra */
182 f5 DOUBLE DEFAULT '2.00',
183 f6 DECIMAL(17,9) default '1000.00',
184 f7 MEDIUMBLOB,
185 f8 NUMERIC(6,2) default '2000.00',
186 f9 VARCHAR(1024),
187 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',
188 f11 CHAR(255))
189 ENGINE=$engine_type CHARSET=latin1;
190
191 --echo
192 eval CREATE TABLE t31 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
193
194 /* extra */
195
196 f5 BIGINT,
197 f6 BLOB,
198 f7 DATE,
199 f8 DATETIME,
200 f9 FLOAT,
201 f10 INT,
202 f11 LONGBLOB,
203 f12 LONGTEXT,
204 f13 MEDIUMBLOB,
205 f14 MEDIUMINT,
206 f15 MEDIUMTEXT,
207 f16 REAL,
208 f17 SMALLINT,
209 f18 TEXT,
210 f19 TIME,
211 f20 TIMESTAMP,
212 f21 TINYBLOB,
213 f22 TINYINT,
214 f23 TINYTEXT,
215 f24 YEAR,
216 f25 BINARY(255),
217 f26 BIT(64),
218 f27 CHAR(255),
219 f28 DECIMAL(30,7),
220 f29 DOUBLE,
221 f30 ENUM ('a','b', 'c') default 'a',
222 f31 FLOAT,
223 f32 NUMERIC(17,9),
224 f33 SET ('a', 'b', 'c') default 'b',
225 f34 VARBINARY(1025),
226 f35 VARCHAR(257)
227 ) ENGINE=$engine_type CHARSET=latin1;
228 --echo
229 --echo ** Alter tables on slave and drop columns **
230 --echo
231 #connection slave;
232 sync_slave_with_master;
233 alter table t2 drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11, drop
234 f12;
235 alter table t3 drop f5, drop f6, drop f8, drop f10, drop f11, drop f12;
236 alter table t4 drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11;
237
238 alter table t31
239 drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11,
240 drop f12, drop f13, drop f14, drop f15, drop f16, drop f17, drop f18,
241 drop f19, drop f20, drop f21, drop f22, drop f23, drop f24, drop f25,
242 drop f26, drop f27, drop f28, drop f29, drop f30, drop f31, drop f32,
243 drop f33, drop f34, drop f35;
244
245 --echo
246 --echo ** Insert Data into Master **
247 connection master;
248 INSERT into t2 set f1=1, f2=1, f3=1, f4='first', f8='f8: medium size blob', f10='f10:
249 some var char';
250 INSERT into t2 values (2, 2, 2, 'second',
251 2.0, 'b', 2000.0002, 'f8: medium size blob', 2000, 'f10: some var char',
252 '01234567', 'c'),
253 (3, 3, 3, 'third',
254 3.0, 'b', 3000.0003, 'f8: medium size blob', 3000, 'f10: some var char',
255 '01234567', 'c');
256 INSERT into t3 set f1=1, f2=1, f3=1, f4='first', f10='f10: some var char';
257 INSERT into t4 set f1=1, f2=1, f3=1, f4='first', f7='f7: medium size blob', f10='f10:
258 binary data';
259 INSERT into t31 set f1=1, f2=1, f3=1, f4='first';
260 insert ignore into t31 set f1=1, f2=1, f3=2, f4='second',
261 f9=2.2, f10='seven samurai', f28=222.222, f35='222';
262 insert ignore into t31 values (1, 1, 3, 'third',
263 /* f5 BIGINT, */ 333333333333333333333333,
264 /* f6 BLOB, */ '3333333333333333333333',
265 /* f7 DATE, */ '2007-07-18',
266 /* f8 DATETIME, */ "2007-07-18",
267 /* f9 FLOAT, */ 3.33333333,
268 /* f10 INT, */ 333333333,
269 /* f11 LONGBLOB, */ '3333333333333333333',
270 /* f12 LONGTEXT, */ '3333333333333333333',
271 /* f13 MEDIUMBLOB, */ '3333333333333333333',
272 /* f14 MEDIUMINT, */ 33,
273 /* f15 MEDIUMTEXT, */ 3.3,
274 /* f16 REAL, */ 3.3,
275 /* f17 SMALLINT, */ 3,
276 /* f18 TEXT, */ '33',
277 /* f19 TIME, */ '2:59:58.999',
278 /* f20 TIMESTAMP, */ 20000303000000,
279 /* f21 TINYBLOB, */ '3333',
280 /* f22 TINYINT, */ 3,
281 /* f23 TINYTEXT, */ '3',
282 /* f24 YEAR, */ 3000,
283 /* f25 BINARY(255), */ 'three_33333',
284 /* f26 BIT(64), */ b'011',
285 /* f27 CHAR(255), */ 'three',
286 /* f28 DECIMAL(30,7), */ 3.333,
287 /* f29 DOUBLE, */ 3.333333333333333333333333333,
288 /* f30 ENUM ('a','b','c')*/ 'c',
289 /* f31 FLOAT, */ 3.0,
290 /* f32 NUMERIC(17,9), */ 3.3333,
291 /* f33 SET ('a','b','c'),*/ 'c',
292 /*f34 VARBINARY(1025),*/ '3333 minus 3',
293 /*f35 VARCHAR(257),*/ 'three times three'
294 );
295
296 insert ignore into t31 values (1, 1, 4, 'fourth',
297 /* f5 BIGINT, */ 333333333333333333333333,
298 /* f6 BLOB, */ '3333333333333333333333',
299 /* f7 DATE, */ '2007-07-18',
300 /* f8 DATETIME, */ "2007-07-18",
301 /* f9 FLOAT, */ 3.33333333,
302 /* f10 INT, */ 333333333,
303 /* f11 LONGBLOB, */ '3333333333333333333',
304 /* f12 LONGTEXT, */ '3333333333333333333',
305 /* f13 MEDIUMBLOB, */ '3333333333333333333',
306 /* f14 MEDIUMINT, */ 33,
307 /* f15 MEDIUMTEXT, */ 3.3,
308 /* f16 REAL, */ 3.3,
309 /* f17 SMALLINT, */ 3,
310 /* f18 TEXT, */ '33',
311 /* f19 TIME, */ '2:59:58.999',
312 /* f20 TIMESTAMP, */ 20000303000000,
313 /* f21 TINYBLOB, */ '3333',
314 /* f22 TINYINT, */ 3,
315 /* f23 TINYTEXT, */ '3',
316 /* f24 YEAR, */ 3000,
317 /* f25 BINARY(255), */ 'three_33333',
318 /* f26 BIT(64), */ b'011',
319 /* f27 CHAR(255), */ 'three',
320 /* f28 DECIMAL(30,7), */ 3.333,
321 /* f29 DOUBLE, */ 3.333333333333333333333333333,
322 /* f30 ENUM ('a','b','c')*/ 'c',
323 /* f31 FLOAT, */ 3.0,
324 /* f32 NUMERIC(17,9), */ 3.3333,
325 /* f33 SET ('a','b','c'),*/ 'c',
326 /*f34 VARBINARY(1025),*/ '3333 minus 3',
327 /*f35 VARCHAR(257),*/ 'three times three'
328 ),
329 (1, 1, 5, 'fifth',
330 /* f5 BIGINT, */ 333333333333333333333333,
331 /* f6 BLOB, */ '3333333333333333333333',
332 /* f7 DATE, */ '2007-07-18',
333 /* f8 DATETIME, */ "2007-07-18",
334 /* f9 FLOAT, */ 3.33333333,
335 /* f10 INT, */ 333333333,
336 /* f11 LONGBLOB, */ '3333333333333333333',
337 /* f12 LONGTEXT, */ '3333333333333333333',
338 /* f13 MEDIUMBLOB, */ '3333333333333333333',
339 /* f14 MEDIUMINT, */ 33,
340 /* f15 MEDIUMTEXT, */ 3.3,
341 /* f16 REAL, */ 3.3,
342 /* f17 SMALLINT, */ 3,
343 /* f18 TEXT, */ '33',
344 /* f19 TIME, */ '2:59:58.999',
345 /* f20 TIMESTAMP, */ 20000303000000,
346 /* f21 TINYBLOB, */ '3333',
347 /* f22 TINYINT, */ 3,
348 /* f23 TINYTEXT, */ '3',
349 /* f24 YEAR, */ 3000,
350 /* f25 BINARY(255), */ 'three_33333',
351 /* f26 BIT(64), */ b'011',
352 /* f27 CHAR(255), */ 'three',
353 /* f28 DECIMAL(30,7), */ 3.333,
354 /* f29 DOUBLE, */ 3.333333333333333333333333333,
355 /* f30 ENUM ('a','b','c')*/ 'c',
356 /* f31 FLOAT, */ 3.0,
357 /* f32 NUMERIC(17,9), */ 3.3333,
358 /* f33 SET ('a','b','c'),*/ 'c',
359 /*f34 VARBINARY(1025),*/ '3333 minus 3',
360 /*f35 VARCHAR(257),*/ 'three times three'
361 ),
362 (1, 1, 6, 'sixth',
363 /* f5 BIGINT, */ NULL,
364 /* f6 BLOB, */ '3333333333333333333333',
365 /* f7 DATE, */ '2007-07-18',
366 /* f8 DATETIME, */ "2007-07-18",
367 /* f9 FLOAT, */ 3.33333333,
368 /* f10 INT, */ 333333333,
369 /* f11 LONGBLOB, */ '3333333333333333333',
370 /* f12 LONGTEXT, */ '3333333333333333333',
371 /* f13 MEDIUMBLOB, */ '3333333333333333333',
372 /* f14 MEDIUMINT, */ 33,
373 /* f15 MEDIUMTEXT, */ 3.3,
374 /* f16 REAL, */ 3.3,
375 /* f17 SMALLINT, */ 3,
376 /* f18 TEXT, */ '33',
377 /* f19 TIME, */ '2:59:58.999',
378 /* f20 TIMESTAMP, */ 20000303000000,
379 /* f21 TINYBLOB, */ '3333',
380 /* f22 TINYINT, */ 3,
381 /* f23 TINYTEXT, */ '3',
382 /* f24 YEAR, */ 3000,
383 /* f25 BINARY(255), */ 'three_33333',
384 /* f26 BIT(64), */ b'011',
385 /* f27 CHAR(255), */ 'three',
386 /* f28 DECIMAL(30,7), */ 3.333,
387 /* f29 DOUBLE, */ 3.333333333333333333333333333,
388 /* f30 ENUM ('a','b','c')*/ 'c',
389 /* f31 FLOAT, */ 3.0,
390 /* f32 NUMERIC(17,9), */ 3.3333,
391 /* f33 SET ('a','b','c'),*/ 'c',
392 /*f34 VARBINARY(1025),*/ '3333 minus 3',
393 /*f35 VARCHAR(257),*/ NULL
394 );
395 --echo
396 --echo ** Sync slave with master **
397 --echo ** Do selects from tables **
398 --echo
399 #connection slave;
400 sync_slave_with_master;
401
402 select * from t1 order by f3;
403 select * from t2 order by f1;
404 select * from t3 order by f1;
405 select * from t4 order by f1;
406 select * from t31 order by f3;
407
408 connection master;
409 --echo
410 --echo ** Do updates master **
411 --echo
412 update t31 set f5=555555555555555 where f3=6;
413 update t31 set f2=2 where f3=2;
414 update t31 set f1=NULL where f3=1;
415 update ignore t31 set f3=NULL, f27=NULL, f35='f35 new value' where f3=3;
416
417 --echo
418 --echo ** Delete from Master **
419 --echo
420
421 delete from t1;
422 delete from t2;
423 delete from t3;
424 delete from t4;
425 delete from t31;
426
427 #connection slave;
428 sync_slave_with_master;
429 select * from t31;
430
431 --echo
432 --echo ** Check slave status **
433 --echo
434 --source include/check_slave_is_running.inc
435
436 #### Clean Up ####
437
438 connection master;
439 --disable_warnings
440 --disable_query_log
441 DROP TABLE t1,t2,t3,t4,t31;
442
443 ######################################################
444 #connection slave;
445 sync_slave_with_master;
446 --enable_query_log
447 --enable_warnings
448 --echo
449 --echo ****************************************
450 --echo * columns in master at middle of table *
451 --echo * Expect: Proper error message *
452 --echo ****************************************
453 --echo
454 --echo ** Stop and Reset Slave **
455 --echo
456 STOP SLAVE;
457 --source include/reset_slave.inc
458 --echo
459 --echo ** create table slave side **
460 eval CREATE TABLE t10 (a INT PRIMARY KEY, b BLOB, c CHAR(5)
461 ) ENGINE=$engine_type CHARSET=latin1;
462
463 --echo
464 --echo ** Connect to master and create table **
465 --echo
466 --connection master
467 eval CREATE TABLE t10 (a INT KEY, b BLOB, f DOUBLE DEFAULT '233',
468 c CHAR(5), e INT DEFAULT '1')ENGINE=$engine_type CHARSET=latin1;
469 RESET MASTER;
470
471 --echo
472 --echo *** Start Slave ***
473 connection slave;
474 START SLAVE;
475
476 --echo
477 --echo *** Master Data Insert ***
478 connection master;
479 set @b1 = 'b1b1b1b1';
480 set @b1 = concat(@b1,@b1);
481 INSERT INTO t10 () VALUES(1,@b1,DEFAULT,'Kyle',DEFAULT),
482 (2,@b1,DEFAULT,'JOE',DEFAULT),
483 (3,@b1,DEFAULT,'QA',DEFAULT);
484
485 --echo
486 --echo ********************************************
487 --echo *** Expect slave to fail with Error 1677 ***
488 --echo ********************************************
489 --echo
490 connection slave;
491 # 1677 = ER_SLAVE_CONVERSION_FAILED
492 --let $slave_sql_errno= 1677
493 --let $slave_skip_counter= 2
494 --let $show_slave_sql_error= 1
495 --source include/wait_for_slave_sql_error_and_skip.inc
496
497 --echo
498 --echo *** Drop t10 ***
499 connection master;
500 DROP TABLE t10;
501 sync_slave_with_master;
502
503 ############################################
504 ############## Continued ###################
505 ############################################
506 --echo
507 --echo *********************************************
508 --echo * More columns in master at middle of table *
509 --echo * Expect: Proper error message *
510 --echo *********************************************
511 --echo
512 --echo *** Create t11 on slave ***
513 STOP SLAVE;
514 --source include/reset_slave.inc
515
516 eval CREATE TABLE t11 (a INT PRIMARY KEY, b BLOB, c VARCHAR(254)
517 ) ENGINE=$engine_type CHARSET=latin1;
518
519 --echo
520 --echo *** Create t11 on Master ***
521 connection master;
522 eval CREATE TABLE t11 (a INT KEY, b BLOB, f TEXT,
523 c CHAR(5) DEFAULT 'test', e INT DEFAULT '1')ENGINE=$engine_type CHARSET=latin1;
524
525 RESET MASTER;
526
527 --echo
528 --echo *** Start Slave ***
529 connection slave;
530 START SLAVE;
531
532 --echo
533 --echo *** Master Data Insert ***
534 connection master;
535 set @b1 = 'b1b1b1b1';
536 set @b1 = concat(@b1,@b1);
537 INSERT INTO t11 () VALUES(1,@b1,'Testing is fun','Kyle',DEFAULT),
538 (2,@b1,'Testing is cool','JOE',DEFAULT),
539 (3,@b1,DEFAULT,'QA',DEFAULT);
540
541 --echo
542 --echo ********************************************
543 --echo *** Expect slave to fail with Error 1677 ***
544 --echo ********************************************
545 --echo
546 connection slave;
547 # 1677 = ER_SLAVE_CONVERSION_FAILED
548 --let $slave_sql_errno= 1677
549 --let $slave_skip_counter= 2
550 --let $show_slave_sql_error= 1
551 --source include/wait_for_slave_sql_error_and_skip.inc
552
553 --echo
554 --echo *** Drop t11 ***
555 connection master;
556 DROP TABLE t11;
557 sync_slave_with_master;
558
559 ############################################
560 ############## Continued ###################
561 ############################################
562 --echo
563 --echo *********************************************
564 --echo * More columns in master at middle of table *
565 --echo * Expect: This one should pass blob-text *
566 --echo *********************************************
567 --echo
568 --echo *** Create t12 on slave ***
569 STOP SLAVE;
570 --source include/reset_slave.inc
571 eval CREATE TABLE t12 (a INT PRIMARY KEY, b BLOB, c BLOB
572 ) ENGINE=$engine_type CHARSET=latin1;
573
574 --echo
575 --echo *** Create t12 on Master ***
576 connection master;
577 eval CREATE TABLE t12 (a INT KEY, b BLOB, f TEXT,
578 c CHAR(5) DEFAULT 'test', e INT DEFAULT '1')ENGINE=$engine_type CHARSET=latin1;
579
580 RESET MASTER;
581
582 --echo
583 --echo *** Start Slave ***
584 connection slave;
585 START SLAVE;
586
587 --echo
588 --echo *** Master Data Insert ***
589 connection master;
590 set @b1 = 'b1b1b1b1';
591 set @b1 = concat(@b1,@b1);
592 INSERT INTO t12 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),
593 (2,@b1,'JOE',DEFAULT,DEFAULT),
594 (3,@b1,'QA',DEFAULT,DEFAULT);
595 --echo
596 SELECT a,hex(b),f,c,e FROM t12 ORDER BY a;
597
598 --echo
599 --echo *** Select on Slave ***
600 sync_slave_with_master;
601 SELECT a,hex(b),c FROM t12 ORDER BY a;
602
603 --echo
604 --echo *** Drop t12 ***
605 connection master;
606 DROP TABLE t12;
607 sync_slave_with_master;
608
609 ############################################
610 ############## Continued ###################
611 ############################################
612 --echo
613 --echo ****************************************************
614 --echo * - Alter Master adding columns at middle of table *
615 --echo * Expect: columns added *
616 --echo ****************************************************
617 --echo
618 --echo
619 --echo *** Create t14 on slave ***
620 STOP SLAVE;
621 --source include/reset_slave.inc
622 eval CREATE TABLE t14 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
623 ) ENGINE=$engine_type CHARSET=latin1;
624
625 --echo
626 --echo *** Create t14 on Master ***
627 connection master;
628 eval CREATE TABLE t14 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
629 c6 INT DEFAULT '1',
630 c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
631 )ENGINE=$engine_type CHARSET=latin1;
632
633 RESET MASTER;
634
635 --echo
636 --echo *** Start Slave ***
637 connection slave;
638 START SLAVE;
639
640 --echo
641 --echo *** Master Data Insert ***
642 connection master;
643 ALTER TABLE t14 ADD COLUMN c2 DECIMAL(8,2) AFTER c1;
644 ALTER TABLE t14 ADD COLUMN c3 TEXT AFTER c2;
645 --echo
646 set @b1 = 'b1b1b1b1';
647 set @b1 = concat(@b1,@b1);
648 INSERT INTO t14 () VALUES(1,1.00,'Replication Testing Extra Col',@b1,'Kyle',DEFAULT,DEFAULT),
649 (2,2.00,'This Test Should work',@b1,'JOE',DEFAULT,DEFAULT),
650 (3,3.00,'If is does not, I will open a bug',@b1,'QA',DEFAULT,DEFAULT);
651 --echo
652 --replace_column 7 CURRENT_TIMESTAMP
653 SELECT c1,c2,c3,hex(c4),c5,c6,c7 FROM t14 ORDER BY c1;
654
655 --echo
656 --echo *** Select on Slave ****
657 sync_slave_with_master;
658 SELECT c1,c2,c3,hex(c4),c5 FROM t14 ORDER BY c1;
659
660 ####################################################
661 --echo
662 --echo ****************************************************
663 --echo * - Alter Master Dropping columns from the middle. *
664 --echo * Expect: columns dropped *
665 --echo ****************************************************
666 --echo
667 --echo *** connect to master and drop columns ***
668 connection master;
669 ALTER TABLE t14 DROP COLUMN c2;
670 ALTER TABLE t14 DROP COLUMN c7;
671 --echo
672 --echo *** Select from Master ***
673 SELECT c1,c3,hex(c4),c5,c6 FROM t14 ORDER BY c1;
674 --echo
675
676 --echo ************
677 --echo * Bug30415 *
678 --echo ************
679 # Uncomment below once fixed
680
681 #--echo *** Select from Slave ***
682 #sync_slave_with_master;
683 #SELECT c1,c2,c3,hex(c4),c5 FROM t14 ORDER BY c1;
684
685 # Bug30415
686 # Remove below once fixed
687 #***************************
688 connection slave;
689 # 1091 = ER_CANT_DROP_FIELD_OR_KEY
690 --let $slave_sql_errno= 1091
691 --let $show_slave_sql_error= 1
692 --source include/wait_for_slave_sql_error.inc
693 #***************************
694
695 STOP SLAVE;
696 --source include/reset_slave.inc
697
698 --echo
699 --echo *** Drop t14 ***
700 DROP TABLE t14;
701
702 connection master;
703 DROP TABLE t14;
704 RESET MASTER;
705
706 connection slave;
707 START SLAVE;
708
709 #################################################
710 --echo
711 --echo *************************************************
712 --echo * - Alter Master adding columns at end of table *
713 --echo * Expect: Error 1054 *
714 --echo *************************************************
715 --echo
716 --echo *** Create t15 on slave ***
717 STOP SLAVE;
718 --source include/reset_slave.inc
719 eval CREATE TABLE t15 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
720 ) ENGINE=$engine_type CHARSET=latin1;
721
722 --echo
723 --echo *** Create t15 on Master ***
724 connection master;
725 eval CREATE TABLE t15 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
726 c6 INT DEFAULT '1',
727 c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
728 )ENGINE=$engine_type CHARSET=latin1;
729
730 RESET MASTER;
731
732 --echo
733 --echo *** Start Slave ***
734 connection slave;
735 START SLAVE;
736
737 --echo
738 --echo *** Master Data Insert ***
739 connection master;
740 ALTER TABLE t15 ADD COLUMN c2 DECIMAL(8,2) AFTER c7;
741 set @b1 = 'b1b1b1b1';
742 set @b1 = concat(@b1,@b1);
743 INSERT INTO t15 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT,3.00),
744 (2,@b1,'JOE',DEFAULT,DEFAULT,3.00),
745 (3,@b1,'QA',DEFAULT,DEFAULT,3.00);
746 --replace_column 5 CURRENT_TIMESTAMP
747 SELECT c1,hex(c4),c5,c6,c7,c2 FROM t15 ORDER BY c1;
748
749 --echo
750 --echo ********************************************
751 --echo *** Expect slave to fail with Error 1054 ***
752 --echo ********************************************
753 --echo
754 connection slave;
755 # 1054 = ER_BAD_FIELD_ERROR
756 --let $slave_sql_errno= 1054
757 --let $show_slave_sql_error= 1
758 --source include/wait_for_slave_sql_error.inc
759 STOP SLAVE;
760 --source include/reset_slave.inc
761
762 --echo
763 --echo *** Drop t15 ***
764 DROP TABLE t15;
765
766 connection master;
767 DROP TABLE t15;
768 RESET MASTER;
769
770 connection slave;
771 START SLAVE;
772
773 ####################################################
774 --echo
775 --echo ************************************************
776 --echo * - Create index on Master column not on slave *
777 --echo * Expect:Warning *
778 --echo ************************************************
779 --echo
780 --echo *** Create t16 on slave ***
781 STOP SLAVE;
782 --source include/reset_slave.inc
783 eval CREATE TABLE t16 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
784 ) ENGINE=$engine_type CHARSET=latin1;
785
786 --echo
787 --echo *** Create t16 on Master ***
788 connection master;
789 eval CREATE TABLE t16 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
790 c6 INT DEFAULT '1',
791 c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
792 )ENGINE=$engine_type CHARSET=latin1;
793
794 RESET MASTER;
795
796 --echo
797 --echo *** Start Slave ***
798 connection slave;
799 START SLAVE;
800
801 --echo
802 --echo *** Master Create Index and Data Insert ***
803 connection master;
804 CREATE INDEX part_of_c6 ON t16 (c6);
805 set @b1 = 'b1b1b1b1';
806 set @b1 = concat(@b1,@b1);
807 INSERT INTO t16 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),
808 (2,@b1,'JOE',2,DEFAULT),
809 (3,@b1,'QA',3,DEFAULT);
810 --replace_column 5 CURRENT_TIMESTAMP
811 SELECT c1,hex(c4),c5,c6,c7 FROM t16 ORDER BY c1;
812
813 # Uncomment the below when bug 30434 is patched
814
815 #--echo *** Select on Slave ****
816 #sync_slave_with_master;
817 #SELECT c1,hex(c4),c5 FROM t16 ORDER BY c1;
818 #
819 #--echo *** Drop t16 ***
820 #connection master;
821 #DROP TABLE t16;
822 #sync_slave_with_master;
823
824 # Remove the below when bug 30434 is patched
825 #*******************************************
826 --echo
827 --echo *****************
828 --echo *** BUG 30434 ***
829 --echo *****************
830 --echo
831 connection slave;
832 # 1072 = ER_KEY_COLUMN_DOES_NOT_EXIST
833 --let $slave_sql_errno= 1072
834 --let $show_slave_sql_error= 1
835 --source include/wait_for_slave_sql_error.inc
836 STOP SLAVE;
837 --source include/reset_slave.inc
838
839 --echo
840 --echo *** Drop t16 ***
841 DROP TABLE t16;
842
843 connection master;
844 DROP TABLE t16;
845 RESET MASTER;
846
847 connection slave;
848 START SLAVE;
849 #*******************************************
850
851 ####################################################
852 --echo
853 --echo *****************************************************
854 --echo * - Delete rows using column on Master not on slave *
855 --echo * Expect: Rows Deleted *
856 --echo *****************************************************
857 --echo
858 --echo *** Create t17 on slave ***
859 STOP SLAVE;
860 --source include/reset_slave.inc
861 eval CREATE TABLE t17 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
862 ) ENGINE=$engine_type CHARSET=latin1;
863
864 --echo
865 --echo *** Create t17 on Master ***
866 connection master;
867 eval CREATE TABLE t17 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
868 c6 INT DEFAULT '1',
869 c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
870 )ENGINE=$engine_type CHARSET=latin1;
871
872 RESET MASTER;
873
874 --echo
875 --echo *** Start Slave ***
876 connection slave;
877 START SLAVE;
878
879 --echo
880 --echo *** Master Data Insert ***
881 connection master;
882 set @b1 = 'b1b1b1b1';
883 set @b1 = concat(@b1,@b1);
884 INSERT INTO t17 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),
885 (2,@b1,'JOE',2,DEFAULT),
886 (3,@b1,'QA',3,DEFAULT);
887 --replace_column 5 CURRENT_TIMESTAMP
888 SELECT c1,hex(c4),c5,c6,c7 FROM t17 ORDER BY c1;
889
890 --echo
891 --echo ** Select * from Slave **
892 sync_slave_with_master;
893 SELECT c1,hex(c4),c5 FROM t17 ORDER BY c1;
894
895 --echo
896 --echo ** Delete from master **
897 connection master;
898 DELETE FROM t17 WHERE c6 = 3;
899 --replace_column 5 CURRENT_TIMESTAMP
900 SELECT c1,hex(c4),c5,c6,c7 FROM t17 ORDER BY c1;
901
902 --echo
903 --echo ** Check slave **
904 sync_slave_with_master;
905 SELECT c1,hex(c4),c5 FROM t17 ORDER BY c1;
906
907
908 connection master;
909 DROP TABLE t17;
910 sync_slave_with_master;
911 --echo
912
913 ####################################################
914 --echo
915 --echo *****************************************************
916 --echo * - Update row using column on Master not on slave *
917 --echo * Expect: Rows updated *
918 --echo *****************************************************
919 --echo
920 --echo ** Bug30674 **
921 --echo
922 --echo *** Create t18 on slave ***
923 --echo
924
925 STOP SLAVE;
926 --source include/reset_slave.inc
927 eval CREATE TABLE t18 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
928 ) ENGINE=$engine_type CHARSET=latin1;
929
930 --echo
931 --echo *** Create t18 on Master ***
932 connection master;
933 eval CREATE TABLE t18 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
934 c6 INT DEFAULT '1',
935 c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
936 )ENGINE=$engine_type CHARSET=latin1;
937
938 RESET MASTER;
939
940 --echo
941 --echo *** Start Slave ***
942 connection slave;
943 START SLAVE;
944
945 --echo
946 --echo *** Master Data Insert ***
947 connection master;
948 set @b1 = 'b1b1b1b1';
949 set @b1 = concat(@b1,@b1);
950
951 INSERT INTO t18 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),
952 (2,@b1,'JOE',2,DEFAULT),
953 (3,@b1,'QA',3,DEFAULT);
954 --replace_column 5 CURRENT_TIMESTAMP
955 SELECT c1,hex(c4),c5,c6,c7 FROM t18 ORDER BY c1;
956
957 --echo
958 --echo ** Select * from Slave **
959 sync_slave_with_master;
960 SELECT c1,hex(c4),c5 FROM t18 ORDER BY c1;
961
962 --echo
963 --echo ** update from master **
964 connection master;
965 UPDATE t18 SET c5 = 'TEST' WHERE c6 = 3;
966
967 --replace_column 5 CURRENT_TIMESTAMP
968 SELECT c1,hex(c4),c5,c6,c7 FROM t18 ORDER BY c1;
969
970 --echo
971 --echo ** Check slave **
972 sync_slave_with_master;
973 SELECT c1,hex(c4),c5 FROM t18 ORDER BY c1;
974
975 connection master;
976 DROP TABLE t18;
977 sync_slave_with_master;
978 --echo
979
980 ####################################################
981 --echo
982 --echo *****************************************************
983 --echo * - Insert UUID column on Master not on slave *
984 --echo * Expect: Rows inserted *
985 --echo *****************************************************
986 --echo
987 --echo *** Create t5 on slave ***
988 STOP SLAVE;
989 --source include/reset_slave.inc
990 eval CREATE TABLE t5 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
991 ) ENGINE=$engine_type CHARSET=latin1;
992
993 --echo
994 --echo *** Create t5 on Master ***
995 connection master;
996 eval CREATE TABLE t5 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
997 c6 LONG,
998 c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
999 )ENGINE=$engine_type CHARSET=latin1;
1000
1001 RESET MASTER;
1002
1003 --echo
1004 --echo *** Start Slave ***
1005 connection slave;
1006 START SLAVE;
1007
1008 --echo
1009 --echo *** Master Data Insert ***
1010 connection master;
1011 set @b1 = 'b1b1b1b1';
1012 INSERT INTO t5 () VALUES(1,@b1,'Kyle',UUID(),DEFAULT),
1013 (2,@b1,'JOE',UUID(),DEFAULT),
1014 (3,@b1,'QA',UUID(),DEFAULT);
1015 --replace_column 4 UUID 5 TIME
1016 SELECT c1,hex(c4),c5,c6,c7 FROM t5 ORDER BY c1;
1017
1018 --echo
1019 --echo ** Select * from Slave **
1020 sync_slave_with_master;
1021 SELECT c1,hex(c4),c5 FROM t5 ORDER BY c1;
1022
1023 connection master;
1024 DROP TABLE t5;
1025 sync_slave_with_master;
1026
1027 --connection $_saved_conn