Debian Codegraph @

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

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