Debian Codegraph @

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