Debian Codegraph @

mysql-test/suite/rpl/r/rpl_row_basic_3innodb.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 CREATE TABLE t1 (C1 CHAR(1), C2 CHAR(1), INDEX (C1)) ENGINE = 'INNODB' ;
7 SELECT * FROM t1;
8 C1 C2
9 include/sync_slave_sql_with_master.inc
10 SELECT * FROM t1;
11 C1 C2
12 INSERT INTO t1 VALUES ('A','B'), ('X','Y'), ('X','X');
13 INSERT INTO t1 VALUES ('A','C'), ('X','Z'), ('A','A');
14 SELECT * FROM t1 ORDER BY C1,C2;
15 C1 C2
16 A A
17 A B
18 A C
19 X X
20 X Y
21 X Z
22 include/sync_slave_sql_with_master.inc
23 SELECT * FROM t1 ORDER BY C1,C2;
24 C1 C2
25 A A
26 A B
27 A C
28 X X
29 X Y
30 X Z
31 DELETE FROM t1 WHERE C1 = C2;
32 SELECT * FROM t1 ORDER BY C1,C2;
33 C1 C2
34 A B
35 A C
36 X Y
37 X Z
38 include/sync_slave_sql_with_master.inc
39 SELECT * FROM t1 ORDER BY C1,C2;
40 C1 C2
41 A B
42 A C
43 X Y
44 X Z
45 UPDATE t1 SET C2 = 'I' WHERE C1 = 'A' AND C2 = 'C';
46 SELECT * FROM t1 ORDER BY C1,C2;
47 C1 C2
48 A B
49 A I
50 X Y
51 X Z
52 include/sync_slave_sql_with_master.inc
53 SELECT * FROM t1 ORDER BY C1,C2;
54 C1 C2
55 A B
56 A I
57 X Y
58 X Z
59 include/assert.inc [Counter for COM_COMMIT is consistent with the number of actual commits]
60 include/assert.inc [Counter for COM_INSERT is consistent with the number of actual inserts]
61 include/assert.inc [Counter for COM_DELETE is consistent with the number of actual deletes]
62 include/assert.inc [Counter for COM_UPDATE is consistent with the number of actual updates]
63 UPDATE t1 SET c2 = 'Q' WHERE c1 = 'A' AND c2 = 'N';
64 SELECT * FROM t1 ORDER BY c1,c2;
65 C1 C2
66 A B
67 A I
68 X Y
69 X Z
70 include/sync_slave_sql_with_master.inc
71 SELECT * FROM t1 ORDER BY c1,c2;
72 C1 C2
73 A B
74 A I
75 X Y
76 X Z
77 CREATE TABLE t2 (c1 INT, c12 char(1), c2 INT, PRIMARY KEY (c1)) ENGINE = 'INNODB' ;
78 INSERT INTO t2
79 VALUES (1,'A',2), (2,'A',4), (3,'A',9), (4,'A',15), (5,'A',25),
80 (6,'A',35), (7,'A',50), (8,'A',64), (9,'A',81);
81 SELECT * FROM t2 ORDER BY c1,c2;
82 c1 c12 c2
83 1 A 2
84 2 A 4
85 3 A 9
86 4 A 15
87 5 A 25
88 6 A 35
89 7 A 50
90 8 A 64
91 9 A 81
92 SELECT * FROM t2 WHERE c2 = c1 * c1 ORDER BY c1,c2;
93 c1 c12 c2
94 2 A 4
95 3 A 9
96 5 A 25
97 8 A 64
98 9 A 81
99 include/sync_slave_sql_with_master.inc
100 SELECT * FROM t2 ORDER BY c1,c2;
101 c1 c12 c2
102 1 A 2
103 2 A 4
104 3 A 9
105 4 A 15
106 5 A 25
107 6 A 35
108 7 A 50
109 8 A 64
110 9 A 81
111 SELECT * FROM t2 WHERE c2 = c1 * c1 ORDER BY c1,c2;
112 c1 c12 c2
113 2 A 4
114 3 A 9
115 5 A 25
116 8 A 64
117 9 A 81
118 UPDATE t2 SET c2 = c1*c1 WHERE c2 != c1*c1;
119 SELECT * FROM t2 WHERE c2 = c1 * c1 ORDER BY c1,c2;
120 c1 c12 c2
121 1 A 1
122 2 A 4
123 3 A 9
124 4 A 16
125 5 A 25
126 6 A 36
127 7 A 49
128 8 A 64
129 9 A 81
130 include/sync_slave_sql_with_master.inc
131 SELECT * FROM t2 WHERE c2 = c1 * c1 ORDER BY c1,c2;
132 c1 c12 c2
133 1 A 1
134 2 A 4
135 3 A 9
136 4 A 16
137 5 A 25
138 6 A 36
139 7 A 49
140 8 A 64
141 9 A 81
142 UPDATE t2 SET c12 = 'Q' WHERE c1 = 1 AND c2 = 999;
143 SELECT * FROM t2 ORDER BY c1,c2;
144 c1 c12 c2
145 1 A 1
146 2 A 4
147 3 A 9
148 4 A 16
149 5 A 25
150 6 A 36
151 7 A 49
152 8 A 64
153 9 A 81
154 include/sync_slave_sql_with_master.inc
155 SELECT * FROM t2 ORDER BY c1,c2;
156 c1 c12 c2
157 1 A 1
158 2 A 4
159 3 A 9
160 4 A 16
161 5 A 25
162 6 A 36
163 7 A 49
164 8 A 64
165 9 A 81
166 DELETE FROM t2 WHERE c1 % 4 = 0;
167 SELECT * FROM t2 ORDER BY c1,c2;
168 c1 c12 c2
169 1 A 1
170 2 A 4
171 3 A 9
172 5 A 25
173 6 A 36
174 7 A 49
175 9 A 81
176 include/sync_slave_sql_with_master.inc
177 SELECT * FROM t2 ORDER BY c1,c2;
178 c1 c12 c2
179 1 A 1
180 2 A 4
181 3 A 9
182 5 A 25
183 6 A 36
184 7 A 49
185 9 A 81
186 UPDATE t2 SET c12='X';
187 CREATE TABLE t3 (C1 CHAR(1), C2 CHAR(1), pk1 INT, C3 CHAR(1), pk2 INT, PRIMARY KEY (pk1,pk2)) ENGINE = 'INNODB' ;
188 INSERT INTO t3 VALUES ('A','B',1,'B',1), ('X','Y',2,'B',1), ('X','X',3,'B',1);
189 INSERT INTO t3 VALUES ('A','C',1,'B',2), ('X','Z',2,'B',2), ('A','A',3,'B',2);
190 SELECT * FROM t3 ORDER BY C1,C2;
191 C1 C2 pk1 C3 pk2
192 A A 3 B 2
193 A B 1 B 1
194 A C 1 B 2
195 X X 3 B 1
196 X Y 2 B 1
197 X Z 2 B 2
198 include/sync_slave_sql_with_master.inc
199 SELECT * FROM t3 ORDER BY C1,C2;
200 C1 C2 pk1 C3 pk2
201 A A 3 B 2
202 A B 1 B 1
203 A C 1 B 2
204 X X 3 B 1
205 X Y 2 B 1
206 X Z 2 B 2
207 DELETE FROM t3 WHERE C1 = C2;
208 SELECT * FROM t3 ORDER BY C1,C2;
209 C1 C2 pk1 C3 pk2
210 A B 1 B 1
211 A C 1 B 2
212 X Y 2 B 1
213 X Z 2 B 2
214 include/sync_slave_sql_with_master.inc
215 SELECT * FROM t3 ORDER BY C1,C2;
216 C1 C2 pk1 C3 pk2
217 A B 1 B 1
218 A C 1 B 2
219 X Y 2 B 1
220 X Z 2 B 2
221 UPDATE t3 SET C2 = 'I' WHERE C1 = 'A' AND C2 = 'C';
222 SELECT * FROM t3 ORDER BY C1,C2;
223 C1 C2 pk1 C3 pk2
224 A B 1 B 1
225 A I 1 B 2
226 X Y 2 B 1
227 X Z 2 B 2
228 include/sync_slave_sql_with_master.inc
229 SELECT * FROM t3 ORDER BY C1,C2;
230 C1 C2 pk1 C3 pk2
231 A B 1 B 1
232 A I 1 B 2
233 X Y 2 B 1
234 X Z 2 B 2
235 CREATE TABLE t6 (C1 CHAR(1), C2 CHAR(1), C3 INT) ENGINE = 'INNODB' ;
236 INSERT INTO t6 VALUES ('A','B',1), ('X','Y',2), ('X','X',3);
237 INSERT INTO t6 VALUES ('A','C',4), ('X','Z',5), ('A','A',6);
238 SELECT * FROM t6 ORDER BY C3;
239 C1 C2 C3
240 A B 1
241 X Y 2
242 X X 3
243 A C 4
244 X Z 5
245 A A 6
246 include/sync_slave_sql_with_master.inc
247 SELECT * FROM t6 ORDER BY C3;
248 C1 C2 C3
249 A B 1
250 X Y 2
251 X X 3
252 A C 4
253 X Z 5
254 A A 6
255 DELETE FROM t6 WHERE C1 = C2;
256 SELECT * FROM t6 ORDER BY C3;
257 C1 C2 C3
258 A B 1
259 X Y 2
260 A C 4
261 X Z 5
262 include/sync_slave_sql_with_master.inc
263 SELECT * FROM t6 ORDER BY C3;
264 C1 C2 C3
265 A B 1
266 X Y 2
267 A C 4
268 X Z 5
269 UPDATE t6 SET C2 = 'I' WHERE C1 = 'A' AND C2 = 'C';
270 SELECT * FROM t6 ORDER BY C3;
271 C1 C2 C3
272 A B 1
273 X Y 2
274 A I 4
275 X Z 5
276 include/sync_slave_sql_with_master.inc
277 SELECT * FROM t6 ORDER BY C3;
278 C1 C2 C3
279 A B 1
280 X Y 2
281 A I 4
282 X Z 5
283 CREATE TABLE t5 (C1 CHAR(1), C2 CHAR(1), C3 INT PRIMARY KEY) ENGINE = 'INNODB' ;
284 INSERT INTO t5 VALUES ('A','B',1), ('X','Y',2), ('X','X',3);
285 INSERT INTO t5 VALUES ('A','C',4), ('X','Z',5), ('A','A',6);
286 UPDATE t5,t2,t3 SET t5.C2='Q', t2.c12='R', t3.C3 ='S' WHERE t5.C1 = t2.c12 AND t5.C1 = t3.C1;
287 SELECT * FROM t5,t2,t3 WHERE t5.C2='Q' AND t2.c12='R' AND t3.C3 ='S' ORDER BY t5.C3,t2.c1,t3.pk1,t3.pk2;
288 C1 C2 C3 c1 c12 c2 C1 C2 pk1 C3 pk2
289 X Q 2 1 R 1 X Y 2 S 1
290 X Q 2 1 R 1 X Z 2 S 2
291 X Q 2 2 R 4 X Y 2 S 1
292 X Q 2 2 R 4 X Z 2 S 2
293 X Q 2 3 R 9 X Y 2 S 1
294 X Q 2 3 R 9 X Z 2 S 2
295 X Q 2 5 R 25 X Y 2 S 1
296 X Q 2 5 R 25 X Z 2 S 2
297 X Q 2 6 R 36 X Y 2 S 1
298 X Q 2 6 R 36 X Z 2 S 2
299 X Q 2 7 R 49 X Y 2 S 1
300 X Q 2 7 R 49 X Z 2 S 2
301 X Q 2 9 R 81 X Y 2 S 1
302 X Q 2 9 R 81 X Z 2 S 2
303 X Q 3 1 R 1 X Y 2 S 1
304 X Q 3 1 R 1 X Z 2 S 2
305 X Q 3 2 R 4 X Y 2 S 1
306 X Q 3 2 R 4 X Z 2 S 2
307 X Q 3 3 R 9 X Y 2 S 1
308 X Q 3 3 R 9 X Z 2 S 2
309 X Q 3 5 R 25 X Y 2 S 1
310 X Q 3 5 R 25 X Z 2 S 2
311 X Q 3 6 R 36 X Y 2 S 1
312 X Q 3 6 R 36 X Z 2 S 2
313 X Q 3 7 R 49 X Y 2 S 1
314 X Q 3 7 R 49 X Z 2 S 2
315 X Q 3 9 R 81 X Y 2 S 1
316 X Q 3 9 R 81 X Z 2 S 2
317 X Q 5 1 R 1 X Y 2 S 1
318 X Q 5 1 R 1 X Z 2 S 2
319 X Q 5 2 R 4 X Y 2 S 1
320 X Q 5 2 R 4 X Z 2 S 2
321 X Q 5 3 R 9 X Y 2 S 1
322 X Q 5 3 R 9 X Z 2 S 2
323 X Q 5 5 R 25 X Y 2 S 1
324 X Q 5 5 R 25 X Z 2 S 2
325 X Q 5 6 R 36 X Y 2 S 1
326 X Q 5 6 R 36 X Z 2 S 2
327 X Q 5 7 R 49 X Y 2 S 1
328 X Q 5 7 R 49 X Z 2 S 2
329 X Q 5 9 R 81 X Y 2 S 1
330 X Q 5 9 R 81 X Z 2 S 2
331 include/sync_slave_sql_with_master.inc
332 SELECT * FROM t5,t2,t3 WHERE t5.C2='Q' AND t2.c12='R' AND t3.C3 ='S' ORDER BY t5.C3,t2.c1,t3.pk1,t3.pk2;
333 C1 C2 C3 c1 c12 c2 C1 C2 pk1 C3 pk2
334 X Q 2 1 R 1 X Y 2 S 1
335 X Q 2 1 R 1 X Z 2 S 2
336 X Q 2 2 R 4 X Y 2 S 1
337 X Q 2 2 R 4 X Z 2 S 2
338 X Q 2 3 R 9 X Y 2 S 1
339 X Q 2 3 R 9 X Z 2 S 2
340 X Q 2 5 R 25 X Y 2 S 1
341 X Q 2 5 R 25 X Z 2 S 2
342 X Q 2 6 R 36 X Y 2 S 1
343 X Q 2 6 R 36 X Z 2 S 2
344 X Q 2 7 R 49 X Y 2 S 1
345 X Q 2 7 R 49 X Z 2 S 2
346 X Q 2 9 R 81 X Y 2 S 1
347 X Q 2 9 R 81 X Z 2 S 2
348 X Q 3 1 R 1 X Y 2 S 1
349 X Q 3 1 R 1 X Z 2 S 2
350 X Q 3 2 R 4 X Y 2 S 1
351 X Q 3 2 R 4 X Z 2 S 2
352 X Q 3 3 R 9 X Y 2 S 1
353 X Q 3 3 R 9 X Z 2 S 2
354 X Q 3 5 R 25 X Y 2 S 1
355 X Q 3 5 R 25 X Z 2 S 2
356 X Q 3 6 R 36 X Y 2 S 1
357 X Q 3 6 R 36 X Z 2 S 2
358 X Q 3 7 R 49 X Y 2 S 1
359 X Q 3 7 R 49 X Z 2 S 2
360 X Q 3 9 R 81 X Y 2 S 1
361 X Q 3 9 R 81 X Z 2 S 2
362 X Q 5 1 R 1 X Y 2 S 1
363 X Q 5 1 R 1 X Z 2 S 2
364 X Q 5 2 R 4 X Y 2 S 1
365 X Q 5 2 R 4 X Z 2 S 2
366 X Q 5 3 R 9 X Y 2 S 1
367 X Q 5 3 R 9 X Z 2 S 2
368 X Q 5 5 R 25 X Y 2 S 1
369 X Q 5 5 R 25 X Z 2 S 2
370 X Q 5 6 R 36 X Y 2 S 1
371 X Q 5 6 R 36 X Z 2 S 2
372 X Q 5 7 R 49 X Y 2 S 1
373 X Q 5 7 R 49 X Z 2 S 2
374 X Q 5 9 R 81 X Y 2 S 1
375 X Q 5 9 R 81 X Z 2 S 2
376 SET @saved_replica_type_conversions = @@REPLICA_TYPE_CONVERSIONS;
377 SET GLOBAL REPLICA_TYPE_CONVERSIONS = 'ALL_LOSSY';
378 CREATE TABLE t4 (C1 CHAR(1) PRIMARY KEY, B1 BIT(1), B2 BIT(1) NOT NULL DEFAULT 0, C2 CHAR(1) NOT NULL DEFAULT 'A') ENGINE = 'INNODB' ;
379 INSERT INTO t4 SET C1 = 1;
380 SELECT C1,HEX(B1),HEX(B2) FROM t4 ORDER BY C1;
381 C1 HEX(B1) HEX(B2)
382 1 NULL 0
383 include/sync_slave_sql_with_master.inc
384 SELECT C1,HEX(B1),HEX(B2) FROM t4 ORDER BY C1;
385 C1 HEX(B1) HEX(B2)
386 1 NULL 0
387 SET GLOBAL REPLICA_TYPE_CONVERSIONS = @saved_replica_type_conversions;
388 CREATE TABLE t7 (C1 INT PRIMARY KEY, C2 INT) ENGINE = 'INNODB' ;
389 include/sync_slave_sql_with_master.inc
390 --- on slave: original values ---
391 INSERT INTO t7 VALUES (1,3), (2,6), (3,9);
392 SELECT * FROM t7 ORDER BY C1;
393 C1 C2
394 1 3
395 2 6
396 3 9
397 set @@global.replica_exec_mode= 'IDEMPOTENT';
398 --- on master: new values inserted ---
399 INSERT INTO t7 VALUES (1,2), (2,4), (3,6);
400 SELECT * FROM t7 ORDER BY C1;
401 C1 C2
402 1 2
403 2 4
404 3 6
405 include/sync_slave_sql_with_master.inc
406 set @@global.replica_exec_mode= default;
407 --- on slave: old values should be overwritten by replicated values ---
408 SELECT * FROM t7 ORDER BY C1;
409 C1 C2
410 1 2
411 2 4
412 3 6
413 --- on master ---
414 CREATE TABLE t8 (a INT PRIMARY KEY, b INT UNIQUE, c INT UNIQUE) ENGINE = 'INNODB' ;
415 INSERT INTO t8 VALUES (99,99,99);
416 INSERT INTO t8 VALUES (99,22,33);
417 ERROR 23000: Duplicate entry '99' for key 't8.PRIMARY'
418 INSERT INTO t8 VALUES (11,99,33);
419 ERROR 23000: Duplicate entry '99' for key 't8.b'
420 INSERT INTO t8 VALUES (11,22,99);
421 ERROR 23000: Duplicate entry '99' for key 't8.c'
422 SELECT * FROM t8 ORDER BY a;
423 a b c
424 99 99 99
425 include/sync_slave_sql_with_master.inc
426 --- on slave ---
427 SELECT * FROM t8 ORDER BY a;
428 a b c
429 99 99 99
430 INSERT INTO t8 VALUES (1,2,3), (2,4,6), (3,6,9);
431 SELECT * FROM t8 ORDER BY a;
432 a b c
433 1 2 3
434 2 4 6
435 3 6 9
436 99 99 99
437 set @@global.replica_exec_mode= 'IDEMPOTENT';
438 --- on master ---
439 INSERT INTO t8 VALUES (2,4,8);
440 include/sync_slave_sql_with_master.inc
441 set @@global.replica_exec_mode= default;
442 --- on slave ---
443 SELECT * FROM t8 ORDER BY a;
444 a b c
445 1 2 3
446 2 4 8
447 3 6 9
448 99 99 99
449 **** Test for BUG#31552 ****
450 **** On Master ****
451 DELETE FROM t1;
452 include/sync_slave_sql_with_master.inc
453 include/rpl_reset.inc
454 **** On Master ****
455 INSERT INTO t1 VALUES ('K','K'), ('L','L'), ('M','M');
456 **** On Master ****
457 include/sync_slave_sql_with_master.inc
458 set @@global.replica_exec_mode= 'IDEMPOTENT';
459 DELETE FROM t1 WHERE C1 = 'L';
460 DELETE FROM t1;
461 SELECT COUNT(*) FROM t1 ORDER BY c1,c2;
462 COUNT(*) 0
463 include/sync_slave_sql_with_master.inc
464 set @@global.replica_exec_mode= default;
465 include/check_slave_is_running.inc
466 SELECT COUNT(*) FROM t1 ORDER BY c1,c2;
467 COUNT(*) 0
468 **** Test for BUG#37076 ****
469 **** On Master ****
470 DROP TABLE IF EXISTS t1;
471 CREATE TABLE t1 (a TIMESTAMP, b DATETIME, c DATE);
472 INSERT INTO t1 VALUES(
473 '2005-11-14 01:01:01', '2005-11-14 01:01:02', '2005-11-14');
474 **** On Slave ****
475 include/sync_slave_sql_with_master.inc
476 SELECT * FROM t1;
477 a b c
478 2005-11-14 01:01:01 2005-11-14 01:01:02 2005-11-14
479 DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8;
480 include/sync_slave_sql_with_master.inc
481 CREATE TABLE t1 (i INT NOT NULL,
482 c CHAR(16) CHARACTER SET utf8 NOT NULL,
483 j INT NOT NULL) ENGINE = 'INNODB' ;
484 Warnings:
485 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.
486 CREATE TABLE t2 (i INT NOT NULL,
487 c CHAR(16) CHARACTER SET utf8 NOT NULL,
488 j INT NOT NULL) ENGINE = 'INNODB' ;
489 Warnings:
490 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.
491 include/sync_slave_sql_with_master.inc
492 ALTER TABLE t2 MODIFY c CHAR(128) CHARACTER SET utf8 NOT NULL;
493 Warnings:
494 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.
495 CREATE TABLE t3 (i INT NOT NULL,
496 c CHAR(128) CHARACTER SET utf8 NOT NULL,
497 j INT NOT NULL) ENGINE = 'INNODB' ;
498 Warnings:
499 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.
500 include/sync_slave_sql_with_master.inc
501 ALTER TABLE t3 MODIFY c CHAR(16) CHARACTER SET utf8 NOT NULL;
502 Warnings:
503 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.
504 CREATE TABLE t4 (i INT NOT NULL,
505 c CHAR(128) CHARACTER SET utf8 NOT NULL,
506 j INT NOT NULL) ENGINE = 'INNODB' ;
507 Warnings:
508 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.
509 CREATE TABLE t5 (i INT NOT NULL,
510 c CHAR(255) CHARACTER SET utf8 NOT NULL,
511 j INT NOT NULL) ENGINE = 'INNODB' ;
512 Warnings:
513 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.
514 include/sync_slave_sql_with_master.inc
515 ALTER TABLE t5 MODIFY c CHAR(16) CHARACTER SET utf8 NOT NULL;
516 Warnings:
517 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.
518 CREATE TABLE t6 (i INT NOT NULL,
519 c CHAR(255) CHARACTER SET utf8 NOT NULL,
520 j INT NOT NULL) ENGINE = 'INNODB' ;
521 Warnings:
522 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.
523 include/sync_slave_sql_with_master.inc
524 ALTER TABLE t6 MODIFY c CHAR(128) CHARACTER SET utf8 NOT NULL;
525 Warnings:
526 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.
527 CREATE TABLE t7 (i INT NOT NULL,
528 c CHAR(255) CHARACTER SET utf8 NOT NULL,
529 j INT NOT NULL) ENGINE = 'INNODB' ;
530 Warnings:
531 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.
532 SET @saved_replica_type_conversions = @@replica_type_conversions;
533 SET GLOBAL REPLICA_TYPE_CONVERSIONS = 'ALL_NON_LOSSY';
534 [expecting slave to replicate correctly]
535 INSERT INTO t1 VALUES (1, "", 1);
536 INSERT INTO t1 VALUES (2, repeat(_utf8'a', 16), 2);
537 Warnings:
538 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.
539 include/sync_slave_sql_with_master.inc
540 include/diff_tables.inc [master:t1, slave:t1]
541 [expecting slave to replicate correctly]
542 INSERT INTO t2 VALUES (1, "", 1);
543 INSERT INTO t2 VALUES (2, repeat(_utf8'a', 16), 2);
544 Warnings:
545 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.
546 include/sync_slave_sql_with_master.inc
547 include/diff_tables.inc [master:t2, slave:t2]
548 SET GLOBAL REPLICA_TYPE_CONVERSIONS = @saved_replica_type_conversions;
549 call mtr.add_suppression("Replica SQL.*Table definition on source and replica does not match: Column 1 size mismatch.* Error_code: MY-001535");
550 call mtr.add_suppression("Replica SQL.*Could not execute Delete_rows event on table test.t1.* Error_code: MY-001032");
551 call mtr.add_suppression("Replica SQL.*Column 1 of table .test.t.. cannot be converted from type.*, Error_code: MY-013146");
552 call mtr.add_suppression("The replica coordinator and worker threads are stopped, possibly leaving data in inconsistent state");
553 include/rpl_reset.inc
554 [expecting slave to replicate correctly]
555 INSERT INTO t4 VALUES (1, "", 1);
556 INSERT INTO t4 VALUES (2, repeat(_utf8'a', 128), 2);
557 Warnings:
558 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.
559 include/sync_slave_sql_with_master.inc
560 include/diff_tables.inc [master:t4, slave:t4]
561 [expecting slave to stop]
562 INSERT INTO t5 VALUES (1, "", 1);
563 INSERT INTO t5 VALUES (2, repeat(_utf8'a', 255), 2);
564 Warnings:
565 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.
566 include/wait_for_slave_sql_error.inc [errno=13146]
567 include/rpl_reset.inc
568 [expecting slave to stop]
569 INSERT INTO t6 VALUES (1, "", 1);
570 INSERT INTO t6 VALUES (2, repeat(_utf8'a', 255), 2);
571 Warnings:
572 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.
573 include/wait_for_slave_sql_error.inc [errno=13146]
574 include/rpl_reset.inc
575 [expecting slave to replicate correctly]
576 INSERT INTO t7 VALUES (1, "", 1);
577 INSERT INTO t7 VALUES (2, repeat(_utf8'a', 255), 2);
578 Warnings:
579 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.
580 include/sync_slave_sql_with_master.inc
581 include/diff_tables.inc [master:t7, slave:t7]
582 drop table t1, t2, t3, t4, t5, t6, t7;
583 include/sync_slave_sql_with_master.inc
584 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE='INNODB' ;
585 INSERT INTO t1 VALUES (1), (2), (3);
586 UPDATE t1 SET a = 10;
587 ERROR 23000: Duplicate entry '10' for key 't1.PRIMARY'
588 INSERT INTO t1 VALUES (4);
589 include/sync_slave_sql_with_master.inc
590 include/diff_tables.inc [master:t1, slave:t1]
591 drop table t1;
592 include/sync_slave_sql_with_master.inc
593 DROP TABLE IF EXISTS t1, t2;
594 CREATE TABLE t1 (
595 `pk` int(11) NOT NULL AUTO_INCREMENT,
596 `int_nokey` int(11) NOT NULL,
597 `int_key` int(11) NOT NULL,
598 `date_key` date NOT NULL,
599 `date_nokey` date NOT NULL,
600 `time_key` time NOT NULL,
601 `time_nokey` time NOT NULL,
602 `datetime_key` datetime NOT NULL,
603 `datetime_nokey` datetime NOT NULL,
604 `varchar_key` varchar(1) NOT NULL,
605 `varchar_nokey` varchar(1) NOT NULL,
606 PRIMARY KEY (`pk`),
607 KEY `int_key` (`int_key`),
608 KEY `date_key` (`date_key`),
609 KEY `time_key` (`time_key`),
610 KEY `datetime_key` (`datetime_key`),
611 KEY `varchar_key` (`varchar_key`)
612 ) ENGINE='INNODB' ;
613 Warnings:
614 Warning 1681 Integer display width is deprecated and will be removed in a future release.
615 Warning 1681 Integer display width is deprecated and will be removed in a future release.
616 Warning 1681 Integer display width is deprecated and will be removed in a future release.
617 INSERT INTO t1 VALUES (1,8,5,'0000-00-00','0000-00-00','10:37:38','10:37:38','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'),(2,0,9,'0000-00-00','0000-00-00','00:00:00','00:00:00','2007-10-14 00:00:00','2007-10-14 00:00:00','d','d');
618 ERROR 22007: Incorrect date value: '0000-00-00' for column 'date_key' at row 1
619 CREATE TABLE t2 (
620 `pk` int(11) NOT NULL AUTO_INCREMENT,
621 `int_nokey` int(11) NOT NULL,
622 `int_key` int(11) NOT NULL,
623 `date_key` date NOT NULL,
624 `date_nokey` date NOT NULL,
625 `time_key` time NOT NULL,
626 `time_nokey` time NOT NULL,
627 `datetime_key` datetime NOT NULL,
628 `datetime_nokey` datetime NOT NULL,
629 `varchar_key` varchar(1) NOT NULL,
630 `varchar_nokey` varchar(1) NOT NULL,
631 PRIMARY KEY (`pk`),
632 KEY `int_key` (`int_key`),
633 KEY `date_key` (`date_key`),
634 KEY `time_key` (`time_key`),
635 KEY `datetime_key` (`datetime_key`),
636 KEY `varchar_key` (`varchar_key`)
637 ) ENGINE='INNODB' ;
638 Warnings:
639 Warning 1681 Integer display width is deprecated and will be removed in a future release.
640 Warning 1681 Integer display width is deprecated and will be removed in a future release.
641 Warning 1681 Integer display width is deprecated and will be removed in a future release.
642 INSERT INTO t2 VALUES (1,1,6,'2005-12-23','2005-12-23','02:24:28','02:24:28','0000-00-00 00:00:00','0000-00-00 00:00:00','g','g'),(2,0,3,'2009-09-14','2009-09-14','00:00:00','00:00:00','2000-01-30 16:39:40','2000-01-30 16:39:40','q','q'),(3,0,3,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','c','c'),(4,1,6,'2007-03-29','2007-03-29','15:49:00','15:49:00','0000-00-00 00:00:00','0000-00-00 00:00:00','m','m'),(5,4,0,'2002-12-04','2002-12-04','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','o','o'),(6,9,0,'2005-01-28','2005-01-28','00:00:00','00:00:00','2001-05-18 00:00:00','2001-05-18 00:00:00','w','w'),(7,6,0,'0000-00-00','0000-00-00','06:57:25','06:57:25','0000-00-00 00:00:00','0000-00-00 00:00:00','m','m'),(8,0,0,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','z','z'),(9,4,6,'2006-08-15','2006-08-15','00:00:00','00:00:00','2002-04-12 14:44:25','2002-04-12 14:44:25','j','j'),(10,0,5,'2006-12-20','2006-12-20','10:13:53','10:13:53','2008-07-22 00:00:00','2008-07-22 00:00:00','y','y'),(11,9,7,'0000-00-00','0000-00-00','00:00:00','00:00:00','2004-07-05 00:00:00','2004-07-05 00:00:00','{','{'),(12,4,3,'2007-01-26','2007-01-26','23:00:51','23:00:51','2001-05-16 00:00:00','2001-05-16 00:00:00','f','f'),(13,7,0,'2004-03-27','2004-03-27','00:00:00','00:00:00','2005-01-24 03:30:37','2005-01-24 03:30:37','',''),(14,6,0,'2006-07-26','2006-07-26','18:43:57','18:43:57','0000-00-00 00:00:00','0000-00-00 00:00:00','{','{'),(15,0,6,'2000-01-14','2000-01-14','00:00:00','00:00:00','2000-09-21 00:00:00','2000-09-21 00:00:00','o','o'),(16,9,8,'0000-00-00','0000-00-00','21:15:08','21:15:08','0000-00-00 00:00:00','0000-00-00 00:00:00','a','a'),(17,2,0,'2004-10-27','2004-10-27','00:00:00','00:00:00','2004-03-24 22:13:43','2004-03-24 22:13:43','',''),(18,7,4,'0000-00-00','0000-00-00','08:38:27','08:38:27','2002-03-18 19:51:44','2002-03-18 19:51:44','t','t'),(19,5,3,'2008-03-07','2008-03-07','03:29:07','03:29:07','2007-12-01 18:44:44','2007-12-01 18:44:44','t','t'),(20,0,0,'2002-04-09','2002-04-09','16:06:03','16:06:03','2009-04-22 00:00:00','2009-04-22 00:00:00','n','n');
643 ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'datetime_key' at row 1
644 DELETE FROM t2 WHERE `int_key` < 3 LIMIT 1;
645 UPDATE t1 SET `int_key` = 3 ORDER BY `pk` LIMIT 4;
646 DELETE FROM t2 WHERE `int_key` < 3 LIMIT 1;
647 DELETE FROM t2 WHERE `pk` < 6 LIMIT 1;
648 UPDATE t1 SET `int_key` = 6 ORDER BY `pk` LIMIT 3;
649 DELETE FROM t2 WHERE `pk` < 6 LIMIT 1;
650 UPDATE t1 SET `pk` = 6 ORDER BY `int_key` LIMIT 6;
651 DELETE FROM t2 WHERE `pk` < 7 LIMIT 1;
652 UPDATE t1 SET `int_key` = 4 ORDER BY `pk` LIMIT 6;
653 include/sync_slave_sql_with_master.inc
654 *** results: t2 must be consistent ****
655 include/diff_tables.inc [master:t2, slave:t2]
656 DROP TABLE t1, t2;
657 EOF OF TESTS
658 CREATE TABLE t1 (a int) ENGINE='INNODB' ;
659 INSERT IGNORE INTO t1 VALUES (NULL);
660 INSERT INTO t1 ( a ) VALUES ( 0 );
661 INSERT INTO t1 ( a ) VALUES ( 9 );
662 INSERT INTO t1 ( a ) VALUES ( 2 );
663 INSERT INTO t1 ( a ) VALUES ( 9 );
664 INSERT INTO t1 ( a ) VALUES ( 5 );
665 UPDATE t1 SET a = 5 WHERE a = 9;
666 DELETE FROM t1 WHERE a < 6;
667 UPDATE t1 SET a = 9 WHERE a < 3;
668 INSERT INTO t1 ( a ) VALUES ( 3 );
669 UPDATE t1 SET a = 0 WHERE a < 4;
670 UPDATE t1 SET a = 8 WHERE a < 5;
671 include/sync_slave_sql_with_master.inc
672 include/diff_tables.inc [master:t1, slave:t1]
673 drop table t1;
674 include/sync_slave_sql_with_master.inc
675 SET @saved_replica_type_conversions = @@REPLICA_TYPE_CONVERSIONS;
676 SET GLOBAL REPLICA_TYPE_CONVERSIONS = 'ALL_LOSSY';
677 CREATE TABLE t1 (a bit) ENGINE='INNODB' ;
678 INSERT IGNORE INTO t1 VALUES (NULL);
679 INSERT INTO t1 ( a ) VALUES ( 0 );
680 UPDATE t1 SET a = 0 WHERE a = 1 LIMIT 3;
681 INSERT IGNORE INTO t1 ( a ) VALUES ( 5 );
682 DELETE FROM t1 WHERE a < 2 LIMIT 4;
683 DELETE FROM t1 WHERE a < 9 LIMIT 4;
684 INSERT IGNORE INTO t1 ( a ) VALUES ( 9 );
685 UPDATE t1 SET a = 8 WHERE a = 0 LIMIT 6;
686 INSERT IGNORE INTO t1 ( a ) VALUES ( 8 );
687 UPDATE t1 SET a = 0 WHERE a < 6 LIMIT 0;
688 INSERT IGNORE INTO t1 ( a ) VALUES ( 4 );
689 INSERT IGNORE INTO t1 ( a ) VALUES ( 3 );
690 UPDATE t1 SET a = 0 WHERE a = 7 LIMIT 6;
691 DELETE FROM t1 WHERE a = 4 LIMIT 7;
692 UPDATE IGNORE t1 SET a = 9 WHERE a < 2 LIMIT 9;
693 UPDATE t1 SET a = 0 WHERE a < 9 LIMIT 2;
694 DELETE FROM t1 WHERE a < 0 LIMIT 5;
695 INSERT IGNORE INTO t1 ( a ) VALUES ( 5 );
696 UPDATE IGNORE t1 SET a = 4 WHERE a < 6 LIMIT 4;
697 INSERT IGNORE INTO t1 ( a ) VALUES ( 5 );
698 UPDATE IGNORE t1 SET a = 9 WHERE a < 5 LIMIT 8;
699 DELETE FROM t1 WHERE a < 8 LIMIT 8;
700 INSERT IGNORE INTO t1 ( a ) VALUES ( 6 );
701 DELETE FROM t1 WHERE a < 6 LIMIT 7;
702 UPDATE t1 SET a = 7 WHERE a = 3 LIMIT 7;
703 UPDATE t1 SET a = 8 WHERE a = 0 LIMIT 6;
704 INSERT IGNORE INTO t1 ( a ) VALUES ( 7 );
705 DELETE FROM t1 WHERE a < 9 LIMIT 4;
706 INSERT IGNORE INTO t1 ( a ) VALUES ( 7 );
707 INSERT IGNORE INTO t1 ( a ) VALUES ( 6 );
708 UPDATE t1 SET a = 8 WHERE a = 3 LIMIT 4;
709 DELETE FROM t1 WHERE a = 2 LIMIT 9;
710 DELETE FROM t1 WHERE a = 1 LIMIT 4;
711 UPDATE t1 SET a = 4 WHERE a = 2 LIMIT 7;
712 INSERT IGNORE INTO t1 ( a ) VALUES ( 0 );
713 DELETE FROM t1 WHERE a < 3 LIMIT 0;
714 UPDATE t1 SET a = 8 WHERE a = 5 LIMIT 2;
715 INSERT IGNORE INTO t1 ( a ) VALUES ( 1 );
716 UPDATE IGNORE t1 SET a = 9 WHERE a < 5 LIMIT 3;
717 include/sync_slave_sql_with_master.inc
718 SET GLOBAL REPLICA_TYPE_CONVERSIONS = @saved_replica_type_conversions;
719 include/diff_tables.inc [master:t1, slave:t1]
720 drop table t1;
721 include/sync_slave_sql_with_master.inc
722 include/rpl_end.inc