Debian Codegraph @

mysql-test/suite/rpl/r/rpl_extra_col_master_myisam.result

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