Debian Codegraph @

mysql-test/suite/rpl/r/rpl_innodb_auto_increment.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 CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.");
7 [connection slave]
8 CHANGE REPLICATION SOURCE TO SOURCE_CONNECT_RETRY = 30 ;
9 include/start_slave.inc
10 # Restart master with innodb_autoinc_lock_mode = 1
11 include/rpl_restart_server.inc [server_number=1 parameters: --innodb_autoinc_lock_mode=1]
12 # Restart slave with innodb_autoinc_lock_mode = 1
13 include/rpl_restart_server.inc [server_number=2 parameters: --innodb_autoinc_lock_mode=1]
14 include/start_slave.inc
15 # Replicating simple inserts
16 [Connection Master]
17 SELECT @@global.innodb_autoinc_lock_mode;
18 @@global.innodb_autoinc_lock_mode
19 1
20 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
21 INSERT INTO t1 VALUES (NULL,10),
22 (NULL,20),
23 (NULL,30);
24 include/sync_slave_sql_with_master.inc
25 [Connection Slave]
26 SELECT @@global.innodb_autoinc_lock_mode;
27 @@global.innodb_autoinc_lock_mode
28 1
29 SELECT * FROM t1;
30 pkey aa
31 1 10
32 2 20
33 3 30
34 include/diff_tables.inc [master:t1, slave:t1]
35 [Connection Master]
36 INSERT INTO t1 VALUES (3,35)
37 ON DUPLICATE KEY UPDATE aa = aa+1;
38 include/sync_slave_sql_with_master.inc
39 [Connection Slave]
40 SELECT * FROM t1;
41 pkey aa
42 1 10
43 2 20
44 3 31
45 include/diff_tables.inc [master:t1, slave:t1]
46 [Connection Master]
47 SET @default_auto_increment_offset = @@auto_increment_offset;
48 SET @default_auto_increment_increment = @@auto_increment_increment;
49 SET auto_increment_offset = 5;
50 SET auto_increment_increment = 15;
51 INSERT INTO t1 VALUES (NULL,40),
52 (NULL,50),
53 (NULL,60),
54 (NULL,70),
55 (NULL,80);
56 include/sync_slave_sql_with_master.inc
57 [Connection Slave]
58 SELECT * FROM t1;
59 pkey aa
60 1 10
61 2 20
62 3 31
63 5 40
64 20 50
65 35 60
66 50 70
67 65 80
68 include/diff_tables.inc [master:t1, slave:t1]
69 [Conenction Master]
70 DELETE FROM t1 WHERE aa IN (40,60);
71 include/sync_slave_sql_with_master.inc
72 [Connection Slave]
73 SELECT * FROM t1;
74 pkey aa
75 1 10
76 2 20
77 3 31
78 20 50
79 50 70
80 65 80
81 include/diff_tables.inc [master:t1, slave:t1]
82 [Connection Master]
83 SET @@auto_increment_offset = @default_auto_increment_offset;
84 SET @@auto_increment_increment= @default_auto_increment_increment;
85 DROP TABLE t1;
86 include/sync_slave_sql_with_master.inc
87 [Connection Slave]
88 SELECT * FROM t1;
89 ERROR 42S02: Table 'test.t1' doesn't exist
90 # Replicating bulk inserts
91 [Connection Master]
92 CREATE TABLE t1(aa INT);
93 INSERT INTO t1 VALUES(5),(10),(15),(20),(25),(30),(35),(40);
94 CREATE TABLE t2(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
95 INSERT INTO t2(aa) SELECT * FROM t1;
96 INSERT INTO t2(aa) SELECT * FROM t1;
97 include/sync_slave_sql_with_master.inc
98 [Connection Slave]
99 SELECT COUNT(*) FROM t2;
100 COUNT(*)
101 16
102 include/diff_tables.inc [master:t2, slave:t2]
103 [Connection Master]
104 SET auto_increment_offset = 4;
105 SET auto_increment_increment = 16;
106 INSERT INTO t2(aa) SELECT * FROM t1;
107 INSERT INTO t2(aa) SELECT * FROM t1;
108 include/sync_slave_sql_with_master.inc
109 [Connection Slave]
110 SELECT COUNT(*) FROM t2;
111 COUNT(*)
112 32
113 include/diff_tables.inc [master:t2, slave:t2]
114 [Connection Master]
115 SET @@auto_increment_offset = @default_auto_increment_offset;
116 SET @@auto_increment_increment = @default_auto_increment_increment;
117 DROP TABLE t1;
118 DROP TABLE t2;
119 include/sync_slave_sql_with_master.inc
120 [Connection Slave]
121 SELECT * FROM t2;
122 ERROR 42S02: Table 'test.t2' doesn't exist
123 # Replicating mixed mode inserts
124 [Connection Master]
125 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, bb INT);
126 INSERT INTO t1 VALUES(NULL,20),
127 (20,40),
128 (NULL,60),
129 (NULL,80),
130 (30,100),
131 (NULL,120);
132 include/sync_slave_sql_with_master.inc
133 [Connection Slave]
134 SELECT COUNT(*) FROM t1;
135 COUNT(*)
136 6
137 include/diff_tables.inc [master:t1, slave:t1]
138 [Connection Master]
139 DROP TABLE t1;
140 include/sync_slave_sql_with_master.inc
141 [Connection Slave]
142 SELECT * FROM t1;
143 ERROR 42S02: Table 'test.t1' doesn't exist
144 # Restart master with innodb_autoinc_lock_mode = 0
145 include/rpl_restart_server.inc [server_number=1 parameters: --innodb_autoinc_lock_mode=0]
146 # Restart slave with innodb_autoinc_lock_mode = 0
147 include/rpl_restart_server.inc [server_number=2 parameters: --innodb_autoinc_lock_mode=0]
148 include/start_slave.inc
149 # Replicating simple inserts
150 [Connection Master]
151 SELECT @@global.innodb_autoinc_lock_mode;
152 @@global.innodb_autoinc_lock_mode
153 0
154 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
155 INSERT INTO t1 VALUES (NULL,10),
156 (NULL,20),
157 (NULL,30);
158 include/sync_slave_sql_with_master.inc
159 [Connection Slave]
160 SELECT @@global.innodb_autoinc_lock_mode;
161 @@global.innodb_autoinc_lock_mode
162 0
163 SELECT * FROM t1;
164 pkey aa
165 1 10
166 2 20
167 3 30
168 include/diff_tables.inc [master:t1, slave:t1]
169 [Connection Master]
170 INSERT INTO t1 VALUES (3,35)
171 ON DUPLICATE KEY UPDATE aa = aa+1;
172 include/sync_slave_sql_with_master.inc
173 [Connection Slave]
174 SELECT * FROM t1;
175 pkey aa
176 1 10
177 2 20
178 3 31
179 include/diff_tables.inc [master:t1, slave:t1]
180 [Connection Master]
181 SET @default_auto_increment_offset = @@auto_increment_offset;
182 SET @default_auto_increment_increment = @@auto_increment_increment;
183 SET auto_increment_offset = 5;
184 SET auto_increment_increment = 15;
185 INSERT INTO t1 VALUES (NULL,40),
186 (NULL,50),
187 (NULL,60),
188 (NULL,70),
189 (NULL,80);
190 include/sync_slave_sql_with_master.inc
191 [Connection Slave]
192 SELECT * FROM t1;
193 pkey aa
194 1 10
195 2 20
196 3 31
197 5 40
198 20 50
199 35 60
200 50 70
201 65 80
202 include/diff_tables.inc [master:t1, slave:t1]
203 [Conenction Master]
204 DELETE FROM t1 WHERE aa IN (40,60);
205 include/sync_slave_sql_with_master.inc
206 [Connection Slave]
207 SELECT * FROM t1;
208 pkey aa
209 1 10
210 2 20
211 3 31
212 20 50
213 50 70
214 65 80
215 include/diff_tables.inc [master:t1, slave:t1]
216 [Connection Master]
217 SET @@auto_increment_offset = @default_auto_increment_offset;
218 SET @@auto_increment_increment= @default_auto_increment_increment;
219 DROP TABLE t1;
220 include/sync_slave_sql_with_master.inc
221 [Connection Slave]
222 SELECT * FROM t1;
223 ERROR 42S02: Table 'test.t1' doesn't exist
224 # Replicating bulk inserts
225 [Connection Master]
226 CREATE TABLE t1(aa INT);
227 INSERT INTO t1 VALUES(5),(10),(15),(20),(25),(30),(35),(40);
228 CREATE TABLE t2(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
229 INSERT INTO t2(aa) SELECT * FROM t1;
230 INSERT INTO t2(aa) SELECT * FROM t1;
231 include/sync_slave_sql_with_master.inc
232 [Connection Slave]
233 SELECT COUNT(*) FROM t2;
234 COUNT(*)
235 16
236 include/diff_tables.inc [master:t2, slave:t2]
237 [Connection Master]
238 SET auto_increment_offset = 4;
239 SET auto_increment_increment = 16;
240 INSERT INTO t2(aa) SELECT * FROM t1;
241 INSERT INTO t2(aa) SELECT * FROM t1;
242 include/sync_slave_sql_with_master.inc
243 [Connection Slave]
244 SELECT COUNT(*) FROM t2;
245 COUNT(*)
246 32
247 include/diff_tables.inc [master:t2, slave:t2]
248 [Connection Master]
249 SET @@auto_increment_offset = @default_auto_increment_offset;
250 SET @@auto_increment_increment = @default_auto_increment_increment;
251 DROP TABLE t1;
252 DROP TABLE t2;
253 include/sync_slave_sql_with_master.inc
254 [Connection Slave]
255 SELECT * FROM t2;
256 ERROR 42S02: Table 'test.t2' doesn't exist
257 # Replicating mixed mode inserts
258 [Connection Master]
259 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, bb INT);
260 INSERT INTO t1 VALUES(NULL,20),
261 (20,40),
262 (NULL,60),
263 (NULL,80),
264 (30,100),
265 (NULL,120);
266 include/sync_slave_sql_with_master.inc
267 [Connection Slave]
268 SELECT COUNT(*) FROM t1;
269 COUNT(*)
270 6
271 include/diff_tables.inc [master:t1, slave:t1]
272 [Connection Master]
273 DROP TABLE t1;
274 include/sync_slave_sql_with_master.inc
275 [Connection Slave]
276 SELECT * FROM t1;
277 ERROR 42S02: Table 'test.t1' doesn't exist
278 # Restart master with innodb_autoinc_lock_mode = 2
279 include/rpl_restart_server.inc [server_number=1 parameters: --innodb_autoinc_lock_mode=2]
280 # Restart slave with innodb_autoinc_lock_mode = 2
281 include/rpl_restart_server.inc [server_number=2 parameters: --innodb_autoinc_lock_mode=2]
282 include/start_slave.inc
283 # Replicating simple inserts
284 [Connection Master]
285 SELECT @@global.innodb_autoinc_lock_mode;
286 @@global.innodb_autoinc_lock_mode
287 2
288 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
289 INSERT INTO t1 VALUES (NULL,10),
290 (NULL,20),
291 (NULL,30);
292 include/sync_slave_sql_with_master.inc
293 [Connection Slave]
294 SELECT @@global.innodb_autoinc_lock_mode;
295 @@global.innodb_autoinc_lock_mode
296 2
297 SELECT * FROM t1;
298 pkey aa
299 1 10
300 2 20
301 3 30
302 include/diff_tables.inc [master:t1, slave:t1]
303 [Connection Master]
304 INSERT INTO t1 VALUES (3,35)
305 ON DUPLICATE KEY UPDATE aa = aa+1;
306 include/sync_slave_sql_with_master.inc
307 [Connection Slave]
308 SELECT * FROM t1;
309 pkey aa
310 1 10
311 2 20
312 3 31
313 include/diff_tables.inc [master:t1, slave:t1]
314 [Connection Master]
315 SET @default_auto_increment_offset = @@auto_increment_offset;
316 SET @default_auto_increment_increment = @@auto_increment_increment;
317 SET auto_increment_offset = 5;
318 SET auto_increment_increment = 15;
319 INSERT INTO t1 VALUES (NULL,40),
320 (NULL,50),
321 (NULL,60),
322 (NULL,70),
323 (NULL,80);
324 include/sync_slave_sql_with_master.inc
325 [Connection Slave]
326 SELECT * FROM t1;
327 pkey aa
328 1 10
329 2 20
330 3 31
331 5 40
332 20 50
333 35 60
334 50 70
335 65 80
336 include/diff_tables.inc [master:t1, slave:t1]
337 [Conenction Master]
338 DELETE FROM t1 WHERE aa IN (40,60);
339 include/sync_slave_sql_with_master.inc
340 [Connection Slave]
341 SELECT * FROM t1;
342 pkey aa
343 1 10
344 2 20
345 3 31
346 20 50
347 50 70
348 65 80
349 include/diff_tables.inc [master:t1, slave:t1]
350 [Connection Master]
351 SET @@auto_increment_offset = @default_auto_increment_offset;
352 SET @@auto_increment_increment= @default_auto_increment_increment;
353 DROP TABLE t1;
354 include/sync_slave_sql_with_master.inc
355 [Connection Slave]
356 SELECT * FROM t1;
357 ERROR 42S02: Table 'test.t1' doesn't exist
358 # Replicating bulk inserts
359 [Connection Master]
360 CREATE TABLE t1(aa INT);
361 INSERT INTO t1 VALUES(5),(10),(15),(20),(25),(30),(35),(40);
362 CREATE TABLE t2(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
363 INSERT INTO t2(aa) SELECT * FROM t1;
364 INSERT INTO t2(aa) SELECT * FROM t1;
365 include/sync_slave_sql_with_master.inc
366 [Connection Slave]
367 SELECT COUNT(*) FROM t2;
368 COUNT(*)
369 16
370 include/diff_tables.inc [master:t2, slave:t2]
371 [Connection Master]
372 SET auto_increment_offset = 4;
373 SET auto_increment_increment = 16;
374 INSERT INTO t2(aa) SELECT * FROM t1;
375 INSERT INTO t2(aa) SELECT * FROM t1;
376 include/sync_slave_sql_with_master.inc
377 [Connection Slave]
378 SELECT COUNT(*) FROM t2;
379 COUNT(*)
380 32
381 include/diff_tables.inc [master:t2, slave:t2]
382 [Connection Master]
383 SET @@auto_increment_offset = @default_auto_increment_offset;
384 SET @@auto_increment_increment = @default_auto_increment_increment;
385 DROP TABLE t1;
386 DROP TABLE t2;
387 include/sync_slave_sql_with_master.inc
388 [Connection Slave]
389 SELECT * FROM t2;
390 ERROR 42S02: Table 'test.t2' doesn't exist
391 # Replicating mixed mode inserts
392 [Connection Master]
393 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, bb INT);
394 INSERT INTO t1 VALUES(NULL,20),
395 (20,40),
396 (NULL,60),
397 (NULL,80),
398 (30,100),
399 (NULL,120);
400 include/sync_slave_sql_with_master.inc
401 [Connection Slave]
402 SELECT COUNT(*) FROM t1;
403 COUNT(*)
404 6
405 include/diff_tables.inc [master:t1, slave:t1]
406 [Connection Master]
407 DROP TABLE t1;
408 include/sync_slave_sql_with_master.inc
409 [Connection Slave]
410 SELECT * FROM t1;
411 ERROR 42S02: Table 'test.t1' doesn't exist
412 # Restart slave with innodb_autoinc_lock_mode = 0
413 include/rpl_restart_server.inc [server_number=2 parameters: --innodb_autoinc_lock_mode=0]
414 include/start_slave.inc
415 # Replicating simple inserts
416 [Connection Master]
417 SELECT @@global.innodb_autoinc_lock_mode;
418 @@global.innodb_autoinc_lock_mode
419 2
420 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
421 INSERT INTO t1 VALUES (NULL,10),
422 (NULL,20),
423 (NULL,30);
424 include/sync_slave_sql_with_master.inc
425 [Connection Slave]
426 SELECT @@global.innodb_autoinc_lock_mode;
427 @@global.innodb_autoinc_lock_mode
428 0
429 SELECT * FROM t1;
430 pkey aa
431 1 10
432 2 20
433 3 30
434 include/diff_tables.inc [master:t1, slave:t1]
435 [Connection Master]
436 INSERT INTO t1 VALUES (3,35)
437 ON DUPLICATE KEY UPDATE aa = aa+1;
438 include/sync_slave_sql_with_master.inc
439 [Connection Slave]
440 SELECT * FROM t1;
441 pkey aa
442 1 10
443 2 20
444 3 31
445 include/diff_tables.inc [master:t1, slave:t1]
446 [Connection Master]
447 SET @default_auto_increment_offset = @@auto_increment_offset;
448 SET @default_auto_increment_increment = @@auto_increment_increment;
449 SET auto_increment_offset = 5;
450 SET auto_increment_increment = 15;
451 INSERT INTO t1 VALUES (NULL,40),
452 (NULL,50),
453 (NULL,60),
454 (NULL,70),
455 (NULL,80);
456 include/sync_slave_sql_with_master.inc
457 [Connection Slave]
458 SELECT * FROM t1;
459 pkey aa
460 1 10
461 2 20
462 3 31
463 5 40
464 20 50
465 35 60
466 50 70
467 65 80
468 include/diff_tables.inc [master:t1, slave:t1]
469 [Conenction Master]
470 DELETE FROM t1 WHERE aa IN (40,60);
471 include/sync_slave_sql_with_master.inc
472 [Connection Slave]
473 SELECT * FROM t1;
474 pkey aa
475 1 10
476 2 20
477 3 31
478 20 50
479 50 70
480 65 80
481 include/diff_tables.inc [master:t1, slave:t1]
482 [Connection Master]
483 SET @@auto_increment_offset = @default_auto_increment_offset;
484 SET @@auto_increment_increment= @default_auto_increment_increment;
485 DROP TABLE t1;
486 include/sync_slave_sql_with_master.inc
487 [Connection Slave]
488 SELECT * FROM t1;
489 ERROR 42S02: Table 'test.t1' doesn't exist
490 # Replicating bulk inserts
491 [Connection Master]
492 CREATE TABLE t1(aa INT);
493 INSERT INTO t1 VALUES(5),(10),(15),(20),(25),(30),(35),(40);
494 CREATE TABLE t2(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
495 INSERT INTO t2(aa) SELECT * FROM t1;
496 INSERT INTO t2(aa) SELECT * FROM t1;
497 include/sync_slave_sql_with_master.inc
498 [Connection Slave]
499 SELECT COUNT(*) FROM t2;
500 COUNT(*)
501 16
502 include/diff_tables.inc [master:t2, slave:t2]
503 [Connection Master]
504 SET auto_increment_offset = 4;
505 SET auto_increment_increment = 16;
506 INSERT INTO t2(aa) SELECT * FROM t1;
507 INSERT INTO t2(aa) SELECT * FROM t1;
508 include/sync_slave_sql_with_master.inc
509 [Connection Slave]
510 SELECT COUNT(*) FROM t2;
511 COUNT(*)
512 32
513 include/diff_tables.inc [master:t2, slave:t2]
514 [Connection Master]
515 SET @@auto_increment_offset = @default_auto_increment_offset;
516 SET @@auto_increment_increment = @default_auto_increment_increment;
517 DROP TABLE t1;
518 DROP TABLE t2;
519 include/sync_slave_sql_with_master.inc
520 [Connection Slave]
521 SELECT * FROM t2;
522 ERROR 42S02: Table 'test.t2' doesn't exist
523 # Replicating mixed mode inserts
524 [Connection Master]
525 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, bb INT);
526 INSERT INTO t1 VALUES(NULL,20),
527 (20,40),
528 (NULL,60),
529 (NULL,80),
530 (30,100),
531 (NULL,120);
532 include/sync_slave_sql_with_master.inc
533 [Connection Slave]
534 SELECT COUNT(*) FROM t1;
535 COUNT(*)
536 6
537 include/diff_tables.inc [master:t1, slave:t1]
538 [Connection Master]
539 DROP TABLE t1;
540 include/sync_slave_sql_with_master.inc
541 [Connection Slave]
542 SELECT * FROM t1;
543 ERROR 42S02: Table 'test.t1' doesn't exist
544 # Restart slave with innodb_autoinc_lock_mode = 1
545 include/rpl_restart_server.inc [server_number=2 parameters: --innodb_autoinc_lock_mode=1]
546 include/start_slave.inc
547 # Replicating simple inserts
548 [Connection Master]
549 SELECT @@global.innodb_autoinc_lock_mode;
550 @@global.innodb_autoinc_lock_mode
551 2
552 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
553 INSERT INTO t1 VALUES (NULL,10),
554 (NULL,20),
555 (NULL,30);
556 include/sync_slave_sql_with_master.inc
557 [Connection Slave]
558 SELECT @@global.innodb_autoinc_lock_mode;
559 @@global.innodb_autoinc_lock_mode
560 1
561 SELECT * FROM t1;
562 pkey aa
563 1 10
564 2 20
565 3 30
566 include/diff_tables.inc [master:t1, slave:t1]
567 [Connection Master]
568 INSERT INTO t1 VALUES (3,35)
569 ON DUPLICATE KEY UPDATE aa = aa+1;
570 include/sync_slave_sql_with_master.inc
571 [Connection Slave]
572 SELECT * FROM t1;
573 pkey aa
574 1 10
575 2 20
576 3 31
577 include/diff_tables.inc [master:t1, slave:t1]
578 [Connection Master]
579 SET @default_auto_increment_offset = @@auto_increment_offset;
580 SET @default_auto_increment_increment = @@auto_increment_increment;
581 SET auto_increment_offset = 5;
582 SET auto_increment_increment = 15;
583 INSERT INTO t1 VALUES (NULL,40),
584 (NULL,50),
585 (NULL,60),
586 (NULL,70),
587 (NULL,80);
588 include/sync_slave_sql_with_master.inc
589 [Connection Slave]
590 SELECT * FROM t1;
591 pkey aa
592 1 10
593 2 20
594 3 31
595 5 40
596 20 50
597 35 60
598 50 70
599 65 80
600 include/diff_tables.inc [master:t1, slave:t1]
601 [Conenction Master]
602 DELETE FROM t1 WHERE aa IN (40,60);
603 include/sync_slave_sql_with_master.inc
604 [Connection Slave]
605 SELECT * FROM t1;
606 pkey aa
607 1 10
608 2 20
609 3 31
610 20 50
611 50 70
612 65 80
613 include/diff_tables.inc [master:t1, slave:t1]
614 [Connection Master]
615 SET @@auto_increment_offset = @default_auto_increment_offset;
616 SET @@auto_increment_increment= @default_auto_increment_increment;
617 DROP TABLE t1;
618 include/sync_slave_sql_with_master.inc
619 [Connection Slave]
620 SELECT * FROM t1;
621 ERROR 42S02: Table 'test.t1' doesn't exist
622 # Replicating bulk inserts
623 [Connection Master]
624 CREATE TABLE t1(aa INT);
625 INSERT INTO t1 VALUES(5),(10),(15),(20),(25),(30),(35),(40);
626 CREATE TABLE t2(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
627 INSERT INTO t2(aa) SELECT * FROM t1;
628 INSERT INTO t2(aa) SELECT * FROM t1;
629 include/sync_slave_sql_with_master.inc
630 [Connection Slave]
631 SELECT COUNT(*) FROM t2;
632 COUNT(*)
633 16
634 include/diff_tables.inc [master:t2, slave:t2]
635 [Connection Master]
636 SET auto_increment_offset = 4;
637 SET auto_increment_increment = 16;
638 INSERT INTO t2(aa) SELECT * FROM t1;
639 INSERT INTO t2(aa) SELECT * FROM t1;
640 include/sync_slave_sql_with_master.inc
641 [Connection Slave]
642 SELECT COUNT(*) FROM t2;
643 COUNT(*)
644 32
645 include/diff_tables.inc [master:t2, slave:t2]
646 [Connection Master]
647 SET @@auto_increment_offset = @default_auto_increment_offset;
648 SET @@auto_increment_increment = @default_auto_increment_increment;
649 DROP TABLE t1;
650 DROP TABLE t2;
651 include/sync_slave_sql_with_master.inc
652 [Connection Slave]
653 SELECT * FROM t2;
654 ERROR 42S02: Table 'test.t2' doesn't exist
655 # Replicating mixed mode inserts
656 [Connection Master]
657 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, bb INT);
658 INSERT INTO t1 VALUES(NULL,20),
659 (20,40),
660 (NULL,60),
661 (NULL,80),
662 (30,100),
663 (NULL,120);
664 include/sync_slave_sql_with_master.inc
665 [Connection Slave]
666 SELECT COUNT(*) FROM t1;
667 COUNT(*)
668 6
669 include/diff_tables.inc [master:t1, slave:t1]
670 [Connection Master]
671 DROP TABLE t1;
672 include/sync_slave_sql_with_master.inc
673 [Connection Slave]
674 SELECT * FROM t1;
675 ERROR 42S02: Table 'test.t1' doesn't exist
676 # Restart master with innodb_autoinc_lock_mode = 0
677 include/rpl_restart_server.inc [server_number=1 parameters: --innodb_autoinc_lock_mode=0]
678 # Replicating simple inserts
679 [Connection Master]
680 SELECT @@global.innodb_autoinc_lock_mode;
681 @@global.innodb_autoinc_lock_mode
682 0
683 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
684 INSERT INTO t1 VALUES (NULL,10),
685 (NULL,20),
686 (NULL,30);
687 include/sync_slave_sql_with_master.inc
688 [Connection Slave]
689 SELECT @@global.innodb_autoinc_lock_mode;
690 @@global.innodb_autoinc_lock_mode
691 1
692 SELECT * FROM t1;
693 pkey aa
694 1 10
695 2 20
696 3 30
697 include/diff_tables.inc [master:t1, slave:t1]
698 [Connection Master]
699 INSERT INTO t1 VALUES (3,35)
700 ON DUPLICATE KEY UPDATE aa = aa+1;
701 include/sync_slave_sql_with_master.inc
702 [Connection Slave]
703 SELECT * FROM t1;
704 pkey aa
705 1 10
706 2 20
707 3 31
708 include/diff_tables.inc [master:t1, slave:t1]
709 [Connection Master]
710 SET @default_auto_increment_offset = @@auto_increment_offset;
711 SET @default_auto_increment_increment = @@auto_increment_increment;
712 SET auto_increment_offset = 5;
713 SET auto_increment_increment = 15;
714 INSERT INTO t1 VALUES (NULL,40),
715 (NULL,50),
716 (NULL,60),
717 (NULL,70),
718 (NULL,80);
719 include/sync_slave_sql_with_master.inc
720 [Connection Slave]
721 SELECT * FROM t1;
722 pkey aa
723 1 10
724 2 20
725 3 31
726 5 40
727 20 50
728 35 60
729 50 70
730 65 80
731 include/diff_tables.inc [master:t1, slave:t1]
732 [Conenction Master]
733 DELETE FROM t1 WHERE aa IN (40,60);
734 include/sync_slave_sql_with_master.inc
735 [Connection Slave]
736 SELECT * FROM t1;
737 pkey aa
738 1 10
739 2 20
740 3 31
741 20 50
742 50 70
743 65 80
744 include/diff_tables.inc [master:t1, slave:t1]
745 [Connection Master]
746 SET @@auto_increment_offset = @default_auto_increment_offset;
747 SET @@auto_increment_increment= @default_auto_increment_increment;
748 DROP TABLE t1;
749 include/sync_slave_sql_with_master.inc
750 [Connection Slave]
751 SELECT * FROM t1;
752 ERROR 42S02: Table 'test.t1' doesn't exist
753 # Replicating bulk inserts
754 [Connection Master]
755 CREATE TABLE t1(aa INT);
756 INSERT INTO t1 VALUES(5),(10),(15),(20),(25),(30),(35),(40);
757 CREATE TABLE t2(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
758 INSERT INTO t2(aa) SELECT * FROM t1;
759 INSERT INTO t2(aa) SELECT * FROM t1;
760 include/sync_slave_sql_with_master.inc
761 [Connection Slave]
762 SELECT COUNT(*) FROM t2;
763 COUNT(*)
764 16
765 include/diff_tables.inc [master:t2, slave:t2]
766 [Connection Master]
767 SET auto_increment_offset = 4;
768 SET auto_increment_increment = 16;
769 INSERT INTO t2(aa) SELECT * FROM t1;
770 INSERT INTO t2(aa) SELECT * FROM t1;
771 include/sync_slave_sql_with_master.inc
772 [Connection Slave]
773 SELECT COUNT(*) FROM t2;
774 COUNT(*)
775 32
776 include/diff_tables.inc [master:t2, slave:t2]
777 [Connection Master]
778 SET @@auto_increment_offset = @default_auto_increment_offset;
779 SET @@auto_increment_increment = @default_auto_increment_increment;
780 DROP TABLE t1;
781 DROP TABLE t2;
782 include/sync_slave_sql_with_master.inc
783 [Connection Slave]
784 SELECT * FROM t2;
785 ERROR 42S02: Table 'test.t2' doesn't exist
786 # Replicating mixed mode inserts
787 [Connection Master]
788 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, bb INT);
789 INSERT INTO t1 VALUES(NULL,20),
790 (20,40),
791 (NULL,60),
792 (NULL,80),
793 (30,100),
794 (NULL,120);
795 include/sync_slave_sql_with_master.inc
796 [Connection Slave]
797 SELECT COUNT(*) FROM t1;
798 COUNT(*)
799 6
800 include/diff_tables.inc [master:t1, slave:t1]
801 [Connection Master]
802 DROP TABLE t1;
803 include/sync_slave_sql_with_master.inc
804 [Connection Slave]
805 SELECT * FROM t1;
806 ERROR 42S02: Table 'test.t1' doesn't exist
807 # Restart slave with innodb_autoinc_lock_mode = 2
808 include/rpl_restart_server.inc [server_number=2 parameters: --innodb_autoinc_lock_mode=2]
809 include/start_slave.inc
810 # Replicating simple inserts
811 [Connection Master]
812 SELECT @@global.innodb_autoinc_lock_mode;
813 @@global.innodb_autoinc_lock_mode
814 0
815 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
816 INSERT INTO t1 VALUES (NULL,10),
817 (NULL,20),
818 (NULL,30);
819 include/sync_slave_sql_with_master.inc
820 [Connection Slave]
821 SELECT @@global.innodb_autoinc_lock_mode;
822 @@global.innodb_autoinc_lock_mode
823 2
824 SELECT * FROM t1;
825 pkey aa
826 1 10
827 2 20
828 3 30
829 include/diff_tables.inc [master:t1, slave:t1]
830 [Connection Master]
831 INSERT INTO t1 VALUES (3,35)
832 ON DUPLICATE KEY UPDATE aa = aa+1;
833 include/sync_slave_sql_with_master.inc
834 [Connection Slave]
835 SELECT * FROM t1;
836 pkey aa
837 1 10
838 2 20
839 3 31
840 include/diff_tables.inc [master:t1, slave:t1]
841 [Connection Master]
842 SET @default_auto_increment_offset = @@auto_increment_offset;
843 SET @default_auto_increment_increment = @@auto_increment_increment;
844 SET auto_increment_offset = 5;
845 SET auto_increment_increment = 15;
846 INSERT INTO t1 VALUES (NULL,40),
847 (NULL,50),
848 (NULL,60),
849 (NULL,70),
850 (NULL,80);
851 include/sync_slave_sql_with_master.inc
852 [Connection Slave]
853 SELECT * FROM t1;
854 pkey aa
855 1 10
856 2 20
857 3 31
858 5 40
859 20 50
860 35 60
861 50 70
862 65 80
863 include/diff_tables.inc [master:t1, slave:t1]
864 [Conenction Master]
865 DELETE FROM t1 WHERE aa IN (40,60);
866 include/sync_slave_sql_with_master.inc
867 [Connection Slave]
868 SELECT * FROM t1;
869 pkey aa
870 1 10
871 2 20
872 3 31
873 20 50
874 50 70
875 65 80
876 include/diff_tables.inc [master:t1, slave:t1]
877 [Connection Master]
878 SET @@auto_increment_offset = @default_auto_increment_offset;
879 SET @@auto_increment_increment= @default_auto_increment_increment;
880 DROP TABLE t1;
881 include/sync_slave_sql_with_master.inc
882 [Connection Slave]
883 SELECT * FROM t1;
884 ERROR 42S02: Table 'test.t1' doesn't exist
885 # Replicating bulk inserts
886 [Connection Master]
887 CREATE TABLE t1(aa INT);
888 INSERT INTO t1 VALUES(5),(10),(15),(20),(25),(30),(35),(40);
889 CREATE TABLE t2(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
890 INSERT INTO t2(aa) SELECT * FROM t1;
891 INSERT INTO t2(aa) SELECT * FROM t1;
892 include/sync_slave_sql_with_master.inc
893 [Connection Slave]
894 SELECT COUNT(*) FROM t2;
895 COUNT(*)
896 16
897 include/diff_tables.inc [master:t2, slave:t2]
898 [Connection Master]
899 SET auto_increment_offset = 4;
900 SET auto_increment_increment = 16;
901 INSERT INTO t2(aa) SELECT * FROM t1;
902 INSERT INTO t2(aa) SELECT * FROM t1;
903 include/sync_slave_sql_with_master.inc
904 [Connection Slave]
905 SELECT COUNT(*) FROM t2;
906 COUNT(*)
907 32
908 include/diff_tables.inc [master:t2, slave:t2]
909 [Connection Master]
910 SET @@auto_increment_offset = @default_auto_increment_offset;
911 SET @@auto_increment_increment = @default_auto_increment_increment;
912 DROP TABLE t1;
913 DROP TABLE t2;
914 include/sync_slave_sql_with_master.inc
915 [Connection Slave]
916 SELECT * FROM t2;
917 ERROR 42S02: Table 'test.t2' doesn't exist
918 # Replicating mixed mode inserts
919 [Connection Master]
920 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, bb INT);
921 INSERT INTO t1 VALUES(NULL,20),
922 (20,40),
923 (NULL,60),
924 (NULL,80),
925 (30,100),
926 (NULL,120);
927 include/sync_slave_sql_with_master.inc
928 [Connection Slave]
929 SELECT COUNT(*) FROM t1;
930 COUNT(*)
931 6
932 include/diff_tables.inc [master:t1, slave:t1]
933 [Connection Master]
934 DROP TABLE t1;
935 include/sync_slave_sql_with_master.inc
936 [Connection Slave]
937 SELECT * FROM t1;
938 ERROR 42S02: Table 'test.t1' doesn't exist
939 # Restart master with innodb_autoinc_lock_mode = 1
940 include/rpl_restart_server.inc [server_number=1 parameters: --innodb_autoinc_lock_mode=1]
941 # Replicating simple inserts
942 [Connection Master]
943 SELECT @@global.innodb_autoinc_lock_mode;
944 @@global.innodb_autoinc_lock_mode
945 1
946 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
947 INSERT INTO t1 VALUES (NULL,10),
948 (NULL,20),
949 (NULL,30);
950 include/sync_slave_sql_with_master.inc
951 [Connection Slave]
952 SELECT @@global.innodb_autoinc_lock_mode;
953 @@global.innodb_autoinc_lock_mode
954 2
955 SELECT * FROM t1;
956 pkey aa
957 1 10
958 2 20
959 3 30
960 include/diff_tables.inc [master:t1, slave:t1]
961 [Connection Master]
962 INSERT INTO t1 VALUES (3,35)
963 ON DUPLICATE KEY UPDATE aa = aa+1;
964 include/sync_slave_sql_with_master.inc
965 [Connection Slave]
966 SELECT * FROM t1;
967 pkey aa
968 1 10
969 2 20
970 3 31
971 include/diff_tables.inc [master:t1, slave:t1]
972 [Connection Master]
973 SET @default_auto_increment_offset = @@auto_increment_offset;
974 SET @default_auto_increment_increment = @@auto_increment_increment;
975 SET auto_increment_offset = 5;
976 SET auto_increment_increment = 15;
977 INSERT INTO t1 VALUES (NULL,40),
978 (NULL,50),
979 (NULL,60),
980 (NULL,70),
981 (NULL,80);
982 include/sync_slave_sql_with_master.inc
983 [Connection Slave]
984 SELECT * FROM t1;
985 pkey aa
986 1 10
987 2 20
988 3 31
989 5 40
990 20 50
991 35 60
992 50 70
993 65 80
994 include/diff_tables.inc [master:t1, slave:t1]
995 [Conenction Master]
996 DELETE FROM t1 WHERE aa IN (40,60);
997 include/sync_slave_sql_with_master.inc
998 [Connection Slave]
999 SELECT * FROM t1;
1000 pkey aa
1001 1 10
1002 2 20
1003 3 31
1004 20 50
1005 50 70
1006 65 80
1007 include/diff_tables.inc [master:t1, slave:t1]
1008 [Connection Master]
1009 SET @@auto_increment_offset = @default_auto_increment_offset;
1010 SET @@auto_increment_increment= @default_auto_increment_increment;
1011 DROP TABLE t1;
1012 include/sync_slave_sql_with_master.inc
1013 [Connection Slave]
1014 SELECT * FROM t1;
1015 ERROR 42S02: Table 'test.t1' doesn't exist
1016 # Replicating bulk inserts
1017 [Connection Master]
1018 CREATE TABLE t1(aa INT);
1019 INSERT INTO t1 VALUES(5),(10),(15),(20),(25),(30),(35),(40);
1020 CREATE TABLE t2(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
1021 INSERT INTO t2(aa) SELECT * FROM t1;
1022 INSERT INTO t2(aa) SELECT * FROM t1;
1023 include/sync_slave_sql_with_master.inc
1024 [Connection Slave]
1025 SELECT COUNT(*) FROM t2;
1026 COUNT(*)
1027 16
1028 include/diff_tables.inc [master:t2, slave:t2]
1029 [Connection Master]
1030 SET auto_increment_offset = 4;
1031 SET auto_increment_increment = 16;
1032 INSERT INTO t2(aa) SELECT * FROM t1;
1033 INSERT INTO t2(aa) SELECT * FROM t1;
1034 include/sync_slave_sql_with_master.inc
1035 [Connection Slave]
1036 SELECT COUNT(*) FROM t2;
1037 COUNT(*)
1038 32
1039 include/diff_tables.inc [master:t2, slave:t2]
1040 [Connection Master]
1041 SET @@auto_increment_offset = @default_auto_increment_offset;
1042 SET @@auto_increment_increment = @default_auto_increment_increment;
1043 DROP TABLE t1;
1044 DROP TABLE t2;
1045 include/sync_slave_sql_with_master.inc
1046 [Connection Slave]
1047 SELECT * FROM t2;
1048 ERROR 42S02: Table 'test.t2' doesn't exist
1049 # Replicating mixed mode inserts
1050 [Connection Master]
1051 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, bb INT);
1052 INSERT INTO t1 VALUES(NULL,20),
1053 (20,40),
1054 (NULL,60),
1055 (NULL,80),
1056 (30,100),
1057 (NULL,120);
1058 include/sync_slave_sql_with_master.inc
1059 [Connection Slave]
1060 SELECT COUNT(*) FROM t1;
1061 COUNT(*)
1062 6
1063 include/diff_tables.inc [master:t1, slave:t1]
1064 [Connection Master]
1065 DROP TABLE t1;
1066 include/sync_slave_sql_with_master.inc
1067 [Connection Slave]
1068 SELECT * FROM t1;
1069 ERROR 42S02: Table 'test.t1' doesn't exist
1070 # Restart slave with innodb_autoinc_lock_mode = 0
1071 include/rpl_restart_server.inc [server_number=2 parameters: --innodb_autoinc_lock_mode=0]
1072 include/start_slave.inc
1073 # Replicating simple inserts
1074 [Connection Master]
1075 SELECT @@global.innodb_autoinc_lock_mode;
1076 @@global.innodb_autoinc_lock_mode
1077 1
1078 CREATE TABLE t1 (pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
1079 INSERT INTO t1 VALUES (NULL,10),
1080 (NULL,20),
1081 (NULL,30);
1082 include/sync_slave_sql_with_master.inc
1083 [Connection Slave]
1084 SELECT @@global.innodb_autoinc_lock_mode;
1085 @@global.innodb_autoinc_lock_mode
1086 0
1087 SELECT * FROM t1;
1088 pkey aa
1089 1 10
1090 2 20
1091 3 30
1092 include/diff_tables.inc [master:t1, slave:t1]
1093 [Connection Master]
1094 INSERT INTO t1 VALUES (3,35)
1095 ON DUPLICATE KEY UPDATE aa = aa+1;
1096 include/sync_slave_sql_with_master.inc
1097 [Connection Slave]
1098 SELECT * FROM t1;
1099 pkey aa
1100 1 10
1101 2 20
1102 3 31
1103 include/diff_tables.inc [master:t1, slave:t1]
1104 [Connection Master]
1105 SET @default_auto_increment_offset = @@auto_increment_offset;
1106 SET @default_auto_increment_increment = @@auto_increment_increment;
1107 SET auto_increment_offset = 5;
1108 SET auto_increment_increment = 15;
1109 INSERT INTO t1 VALUES (NULL,40),
1110 (NULL,50),
1111 (NULL,60),
1112 (NULL,70),
1113 (NULL,80);
1114 include/sync_slave_sql_with_master.inc
1115 [Connection Slave]
1116 SELECT * FROM t1;
1117 pkey aa
1118 1 10
1119 2 20
1120 3 31
1121 5 40
1122 20 50
1123 35 60
1124 50 70
1125 65 80
1126 include/diff_tables.inc [master:t1, slave:t1]
1127 [Conenction Master]
1128 DELETE FROM t1 WHERE aa IN (40,60);
1129 include/sync_slave_sql_with_master.inc
1130 [Connection Slave]
1131 SELECT * FROM t1;
1132 pkey aa
1133 1 10
1134 2 20
1135 3 31
1136 20 50
1137 50 70
1138 65 80
1139 include/diff_tables.inc [master:t1, slave:t1]
1140 [Connection Master]
1141 SET @@auto_increment_offset = @default_auto_increment_offset;
1142 SET @@auto_increment_increment= @default_auto_increment_increment;
1143 DROP TABLE t1;
1144 include/sync_slave_sql_with_master.inc
1145 [Connection Slave]
1146 SELECT * FROM t1;
1147 ERROR 42S02: Table 'test.t1' doesn't exist
1148 # Replicating bulk inserts
1149 [Connection Master]
1150 CREATE TABLE t1(aa INT);
1151 INSERT INTO t1 VALUES(5),(10),(15),(20),(25),(30),(35),(40);
1152 CREATE TABLE t2(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aa INT);
1153 INSERT INTO t2(aa) SELECT * FROM t1;
1154 INSERT INTO t2(aa) SELECT * FROM t1;
1155 include/sync_slave_sql_with_master.inc
1156 [Connection Slave]
1157 SELECT COUNT(*) FROM t2;
1158 COUNT(*)
1159 16
1160 include/diff_tables.inc [master:t2, slave:t2]
1161 [Connection Master]
1162 SET auto_increment_offset = 4;
1163 SET auto_increment_increment = 16;
1164 INSERT INTO t2(aa) SELECT * FROM t1;
1165 INSERT INTO t2(aa) SELECT * FROM t1;
1166 include/sync_slave_sql_with_master.inc
1167 [Connection Slave]
1168 SELECT COUNT(*) FROM t2;
1169 COUNT(*)
1170 32
1171 include/diff_tables.inc [master:t2, slave:t2]
1172 [Connection Master]
1173 SET @@auto_increment_offset = @default_auto_increment_offset;
1174 SET @@auto_increment_increment = @default_auto_increment_increment;
1175 DROP TABLE t1;
1176 DROP TABLE t2;
1177 include/sync_slave_sql_with_master.inc
1178 [Connection Slave]
1179 SELECT * FROM t2;
1180 ERROR 42S02: Table 'test.t2' doesn't exist
1181 # Replicating mixed mode inserts
1182 [Connection Master]
1183 CREATE TABLE t1(pkey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, bb INT);
1184 INSERT INTO t1 VALUES(NULL,20),
1185 (20,40),
1186 (NULL,60),
1187 (NULL,80),
1188 (30,100),
1189 (NULL,120);
1190 include/sync_slave_sql_with_master.inc
1191 [Connection Slave]
1192 SELECT COUNT(*) FROM t1;
1193 COUNT(*)
1194 6
1195 include/diff_tables.inc [master:t1, slave:t1]
1196 [Connection Master]
1197 DROP TABLE t1;
1198 include/sync_slave_sql_with_master.inc
1199 [Connection Slave]
1200 SELECT * FROM t1;
1201 ERROR 42S02: Table 'test.t1' doesn't exist
1202 # Restart master with innodb_autoinc_lock_mode = 2
1203 include/rpl_restart_server.inc [server_number=1 parameters: --innodb_autoinc_lock_mode=2]
1204 # Restart slave with innodb_autoinc_lock_mode = 2
1205 include/rpl_restart_server.inc [server_number=2 parameters: --innodb_autoinc_lock_mode=2]
1206 include/start_slave.inc
1207 include/rpl_end.inc