Debian Codegraph @

mysql-test/suite/parts/r/partition_auto_increment_archive.result

raw
1 DROP TABLE IF EXISTS t1;
2 # test without partitioning for reference
3 CREATE TABLE t1 (
4 c1 INT NOT NULL AUTO_INCREMENT,
5 PRIMARY KEY (c1))
6 ENGINE='Archive';
7 SHOW CREATE TABLE t1;
8 Table Create Table
9 t1 CREATE TABLE `t1` (
10 `c1` int(11) NOT NULL AUTO_INCREMENT,
11 PRIMARY KEY (`c1`)
12 ) ENGINE=ARCHIVE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
13 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
14 AND TABLE_NAME='t1';
15 AUTO_INCREMENT
16 1
17 INSERT INTO t1 VALUES (2);
18 INSERT INTO t1 VALUES (4);
19 INSERT INTO t1 VALUES (NULL);
20 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
21 AND TABLE_NAME='t1';
22 AUTO_INCREMENT
23 6
24 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
25 AND TABLE_NAME='t1';
26 AUTO_INCREMENT
27 6
28 INSERT INTO t1 VALUES (0);
29 INSERT INTO t1 VALUES (5), (16);
30 INSERT INTO t1 VALUES (17);
31 INSERT INTO t1 VALUES (19), (NULL);
32 INSERT INTO t1 VALUES (NULL), (10), (NULL);
33 # ERROR (only OK if Archive) mysql_errno: 1022
34 INSERT INTO t1 VALUES (NULL);
35 SET INSERT_ID = 30;
36 INSERT INTO t1 VALUES (NULL);
37 SET INSERT_ID = 29;
38 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
39 INSERT INTO t1 VALUES (NULL);
40 SELECT * FROM t1 ORDER BY c1;
41 c1
42 2
43 4
44 5
45 6
46 17
47 19
48 20
49 21
50 22
51 30
52 31
53 DROP TABLE t1;
54 CREATE TABLE t1 (
55 c1 INT NOT NULL AUTO_INCREMENT,
56 PRIMARY KEY (c1))
57 ENGINE='Archive';
58 SHOW CREATE TABLE t1;
59 Table Create Table
60 t1 CREATE TABLE `t1` (
61 `c1` int(11) NOT NULL AUTO_INCREMENT,
62 PRIMARY KEY (`c1`)
63 ) ENGINE=ARCHIVE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
64 FLUSH TABLE;
65 SHOW CREATE TABLE t1;
66 Table Create Table
67 t1 CREATE TABLE `t1` (
68 `c1` int(11) NOT NULL AUTO_INCREMENT,
69 PRIMARY KEY (`c1`)
70 ) ENGINE=ARCHIVE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
71 INSERT INTO t1 VALUES (4);
72 FLUSH TABLE;
73 SHOW CREATE TABLE t1;
74 Table Create Table
75 t1 CREATE TABLE `t1` (
76 `c1` int(11) NOT NULL AUTO_INCREMENT,
77 PRIMARY KEY (`c1`)
78 ) ENGINE=ARCHIVE AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
79 INSERT INTO t1 VALUES (NULL);
80 FLUSH TABLE;
81 SHOW CREATE TABLE t1;
82 Table Create Table
83 t1 CREATE TABLE `t1` (
84 `c1` int(11) NOT NULL AUTO_INCREMENT,
85 PRIMARY KEY (`c1`)
86 ) ENGINE=ARCHIVE AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
87 INSERT INTO t1 VALUES (NULL);
88 SHOW CREATE TABLE t1;
89 Table Create Table
90 t1 CREATE TABLE `t1` (
91 `c1` int(11) NOT NULL AUTO_INCREMENT,
92 PRIMARY KEY (`c1`)
93 ) ENGINE=ARCHIVE AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
94 SELECT * FROM t1 ORDER BY c1;
95 c1
96 4
97 5
98 6
99 INSERT INTO t1 VALUES (NULL);
100 SHOW CREATE TABLE t1;
101 Table Create Table
102 t1 CREATE TABLE `t1` (
103 `c1` int(11) NOT NULL AUTO_INCREMENT,
104 PRIMARY KEY (`c1`)
105 ) ENGINE=ARCHIVE AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
106 SELECT * FROM t1 ORDER BY c1;
107 c1
108 4
109 5
110 6
111 7
112 INSERT INTO t1 VALUES (100);
113 INSERT INTO t1 VALUES (NULL);
114 OPTIMIZE TABLE t1;
115 Table Op Msg_type Msg_text
116 test.t1 optimize status OK
117 SHOW CREATE TABLE t1;
118 Table Create Table
119 t1 CREATE TABLE `t1` (
120 `c1` int(11) NOT NULL AUTO_INCREMENT,
121 PRIMARY KEY (`c1`)
122 ) ENGINE=ARCHIVE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
123 DROP TABLE t1;
124 # Simple test with NULL
125 CREATE TABLE t1 (
126 c1 INT NOT NULL AUTO_INCREMENT,
127 PRIMARY KEY (c1))
128 ENGINE='Archive'
129 PARTITION BY HASH(c1)
130 PARTITIONS 2;
131 INSERT INTO t1 VALUES (NULL);
132 SHOW CREATE TABLE t1;
133 Table Create Table
134 t1 CREATE TABLE `t1` (
135 `c1` int(11) NOT NULL AUTO_INCREMENT,
136 PRIMARY KEY (`c1`)
137 ) ENGINE=ARCHIVE AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
138 PARTITION BY HASH (`c1`)
139 PARTITIONS 2
140 SELECT * FROM t1;
141 c1
142 1
143 DROP TABLE t1;
144 # Test with sql_mode and first insert as 0
145 CREATE TABLE t1 (
146 c1 INT,
147 c2 INT NOT NULL AUTO_INCREMENT,
148 PRIMARY KEY (c2))
149 ENGINE='Archive'
150 PARTITION BY HASH(c2)
151 PARTITIONS 2;
152 INSERT INTO t1 VALUES (1, NULL);
153 INSERT INTO t1 VALUES (1, 1), (99, 99);
154 INSERT INTO t1 VALUES (1, NULL);
155 SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
156 INSERT INTO t1 VALUES (1, 0);
157 # ERROR (only OK if Archive) mysql_errno: 1022
158 SELECT * FROM t1 ORDER BY c1, c2;
159 c1 c2
160 1 1
161 1 2
162 DROP TABLE t1;
163 CREATE TABLE t1 (
164 c1 INT,
165 c2 INT NOT NULL AUTO_INCREMENT,
166 PRIMARY KEY (c2))
167 ENGINE='Archive'
168 PARTITION BY HASH(c2)
169 PARTITIONS 2;
170 INSERT INTO t1 VALUES (1, 0);
171 # ERROR (only OK if Archive) mysql_errno: 1022
172 INSERT INTO t1 VALUES (1, 1), (1, NULL);
173 INSERT INTO t1 VALUES (2, NULL), (4, 7);
174 INSERT INTO t1 VALUES (1, NULL);
175 SELECT * FROM t1 ORDER BY c1, c2;
176 c1 c2
177 1 1
178 1 2
179 1 8
180 2 3
181 4 7
182 SET @@session.sql_mode = '';
183 DROP TABLE t1;
184 # Simple test with NULL, 0 and explicit values both incr. and desc.
185 CREATE TABLE t1 (
186 c1 INT NOT NULL AUTO_INCREMENT,
187 PRIMARY KEY (c1))
188 ENGINE='Archive'
189 PARTITION BY HASH(c1)
190 PARTITIONS 2;
191 INSERT INTO t1 VALUES (2), (4), (NULL);
192 INSERT INTO t1 VALUES (0);
193 INSERT INTO t1 VALUES (5), (16);
194 INSERT INTO t1 VALUES (17), (19), (NULL);
195 INSERT INTO t1 VALUES (NULL), (10), (NULL);
196 # ERROR (only OK if Archive) mysql_errno: 1022
197 INSERT INTO t1 VALUES (NULL), (9);
198 # ERROR (only OK if Archive) mysql_errno: 1022
199 INSERT INTO t1 VALUES (59), (55);
200 # ERROR (only OK if Archive) mysql_errno: 1022
201 INSERT INTO t1 VALUES (NULL), (90);
202 INSERT INTO t1 VALUES (NULL);
203 SELECT * FROM t1 ORDER BY c1;
204 c1
205 2
206 4
207 5
208 6
209 17
210 19
211 20
212 21
213 22
214 59
215 60
216 90
217 91
218 DROP TABLE t1;
219 # Test with auto_increment_increment and auto_increment_offset.
220 CREATE TABLE t1 (
221 c1 INT NOT NULL AUTO_INCREMENT,
222 PRIMARY KEY (c1))
223 ENGINE='Archive'
224 PARTITION BY HASH(c1)
225 PARTITIONS 2;
226 SET @@session.auto_increment_increment = 10;
227 SET @@session.auto_increment_offset = 5;
228 INSERT INTO t1 VALUES (1);
229 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
230 SET @@session.auto_increment_increment = 5;
231 SET @@session.auto_increment_offset = 3;
232 INSERT INTO t1 VALUES (NULL);
233 INSERT INTO t1 VALUES (33 + 1);
234 INSERT INTO t1 VALUES (NULL);
235 INSERT INTO t1 VALUES (38 + 2);
236 INSERT INTO t1 VALUES (NULL);
237 INSERT INTO t1 VALUES (43 + 3);
238 INSERT INTO t1 VALUES (NULL);
239 INSERT INTO t1 VALUES (48 + 4);
240 INSERT INTO t1 VALUES (NULL);
241 INSERT INTO t1 VALUES (53 + 5);
242 INSERT INTO t1 VALUES (NULL);
243 INSERT INTO t1 VALUES (63 + 6);
244 INSERT INTO t1 VALUES (NULL);
245 SET @@session.auto_increment_increment = 1;
246 SET @@session.auto_increment_offset = 1;
247 SELECT * FROM t1 ORDER BY c1;
248 c1
249 1
250 5
251 15
252 25
253 33
254 34
255 38
256 40
257 43
258 46
259 48
260 52
261 53
262 58
263 63
264 69
265 73
266 DROP TABLE t1;
267 # Test reported auto_increment value
268 CREATE TABLE t1 (
269 c1 INT NOT NULL AUTO_INCREMENT,
270 PRIMARY KEY (c1))
271 ENGINE='Archive'
272 PARTITION BY HASH (c1)
273 PARTITIONS 2;
274 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
275 AND TABLE_NAME='t1';
276 AUTO_INCREMENT
277 1
278 INSERT INTO t1 VALUES (2);
279 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
280 AND TABLE_NAME='t1';
281 AUTO_INCREMENT
282 3
283 INSERT INTO t1 VALUES (4);
284 INSERT INTO t1 VALUES (NULL);
285 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
286 AND TABLE_NAME='t1';
287 AUTO_INCREMENT
288 6
289 INSERT INTO t1 VALUES (NULL);
290 INSERT INTO t1 VALUES (17);
291 INSERT INTO t1 VALUES (19);
292 INSERT INTO t1 VALUES (NULL);
293 INSERT INTO t1 VALUES (NULL);
294 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
295 AND TABLE_NAME='t1';
296 AUTO_INCREMENT
297 22
298 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
299 AND TABLE_NAME='t1';
300 AUTO_INCREMENT
301 22
302 INSERT INTO t1 VALUES (10);
303 # ERROR (only OK if Archive) mysql_errno: 1022
304 SELECT * FROM t1 ORDER BY c1;
305 c1
306 2
307 4
308 5
309 6
310 17
311 19
312 20
313 21
314 INSERT INTO t1 VALUES (NULL);
315 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
316 AND TABLE_NAME='t1';
317 AUTO_INCREMENT
318 23
319 INSERT INTO t1 VALUES (NULL);
320 INSERT INTO t1 VALUES (15);
321 # ERROR (only OK if Archive) mysql_errno: 1022
322 INSERT INTO t1 VALUES (NULL);
323 SELECT * FROM t1 ORDER BY c1;
324 c1
325 2
326 4
327 5
328 6
329 17
330 19
331 20
332 21
333 22
334 23
335 24
336 INSERT INTO t1 VALUES (NULL);
337 INSERT INTO t1 VALUES (NULL);
338 SHOW CREATE TABLE t1;
339 Table Create Table
340 t1 CREATE TABLE `t1` (
341 `c1` int(11) NOT NULL AUTO_INCREMENT,
342 PRIMARY KEY (`c1`)
343 ) ENGINE=ARCHIVE AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
344 PARTITION BY HASH (`c1`)
345 PARTITIONS 2
346 SELECT * FROM t1 ORDER BY c1;
347 c1
348 2
349 4
350 5
351 6
352 17
353 19
354 20
355 21
356 22
357 23
358 24
359 25
360 26
361 INSERT INTO t1 VALUES (NULL);
362 SHOW CREATE TABLE t1;
363 Table Create Table
364 t1 CREATE TABLE `t1` (
365 `c1` int(11) NOT NULL AUTO_INCREMENT,
366 PRIMARY KEY (`c1`)
367 ) ENGINE=ARCHIVE AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
368 PARTITION BY HASH (`c1`)
369 PARTITIONS 2
370 SELECT * FROM t1 ORDER BY c1;
371 c1
372 2
373 4
374 5
375 6
376 17
377 19
378 20
379 21
380 22
381 23
382 24
383 25
384 26
385 27
386 INSERT INTO t1 VALUES (100);
387 INSERT INTO t1 VALUES (NULL);
388 OPTIMIZE TABLE t1;
389 Table Op Msg_type Msg_text
390 test.t1 optimize status OK
391 SHOW CREATE TABLE t1;
392 Table Create Table
393 t1 CREATE TABLE `t1` (
394 `c1` int(11) NOT NULL AUTO_INCREMENT,
395 PRIMARY KEY (`c1`)
396 ) ENGINE=ARCHIVE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
397 PARTITION BY HASH (`c1`)
398 PARTITIONS 2
399 DROP TABLE t1;
400 # Test with two threads
401 connection default;
402 CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
403 ENGINE = 'Archive'
404 PARTITION BY HASH(c1)
405 PARTITIONS 2;
406 INSERT INTO t1 (c1) VALUES (2);
407 INSERT INTO t1 (c1) VALUES (4);
408 connect con1, localhost, root,,;
409 connection con1;
410 INSERT INTO t1 (c1) VALUES (NULL);
411 INSERT INTO t1 (c1) VALUES (10);
412 connection default;
413 INSERT INTO t1 (c1) VALUES (NULL);
414 INSERT INTO t1 (c1) VALUES (NULL);
415 INSERT INTO t1 (c1) VALUES (19);
416 INSERT INTO t1 (c1) VALUES (21);
417 connection con1;
418 INSERT INTO t1 (c1) VALUES (NULL);
419 connection default;
420 INSERT INTO t1 (c1) VALUES (16);
421 # ERROR (only OK if Archive) mysql_errno: 1022
422 connection con1;
423 INSERT INTO t1 (c1) VALUES (NULL);
424 disconnect con1;
425 connection default;
426 INSERT INTO t1 (c1) VALUES (NULL);
427 SELECT * FROM t1 ORDER BY c1;
428 c1
429 2
430 4
431 5
432 10
433 11
434 12
435 19
436 21
437 22
438 23
439 24
440 DROP TABLE t1;
441 # Test with two threads + start transaction NO PARTITIONING
442 connect con1, localhost, root,,;
443 connection default;
444 CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
445 ENGINE = 'Archive';
446 START TRANSACTION;
447 INSERT INTO t1 (c1) VALUES (2);
448 INSERT INTO t1 (c1) VALUES (4);
449 connection con1;
450 START TRANSACTION;
451 INSERT INTO t1 (c1) VALUES (NULL);
452 INSERT INTO t1 (c1) VALUES (10);
453 connection default;
454 INSERT INTO t1 (c1) VALUES (NULL);
455 INSERT INTO t1 (c1) VALUES (NULL);
456 INSERT INTO t1 (c1) VALUES (19);
457 INSERT INTO t1 (c1) VALUES (21);
458 connection con1;
459 INSERT INTO t1 (c1) VALUES (NULL);
460 connection default;
461 INSERT INTO t1 (c1) VALUES (16);
462 # ERROR (only OK if Archive) mysql_errno: 1022
463 connection con1;
464 INSERT INTO t1 (c1) VALUES (NULL);
465 SELECT * FROM t1 ORDER BY c1;
466 c1
467 2
468 4
469 5
470 10
471 11
472 12
473 19
474 21
475 22
476 23
477 COMMIT;
478 SELECT * FROM t1 ORDER BY c1;
479 c1
480 2
481 4
482 5
483 10
484 11
485 12
486 19
487 21
488 22
489 23
490 disconnect con1;
491 connection default;
492 INSERT INTO t1 (c1) VALUES (NULL);
493 SELECT * FROM t1 ORDER BY c1;
494 c1
495 2
496 4
497 5
498 10
499 11
500 12
501 19
502 21
503 22
504 23
505 24
506 COMMIT;
507 SELECT * FROM t1 ORDER BY c1;
508 c1
509 2
510 4
511 5
512 10
513 11
514 12
515 19
516 21
517 22
518 23
519 24
520 DROP TABLE t1;
521 # Test with two threads + start transaction
522 connect con1, localhost, root,,;
523 connection default;
524 CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
525 ENGINE = 'Archive'
526 PARTITION BY HASH(c1)
527 PARTITIONS 2;
528 START TRANSACTION;
529 INSERT INTO t1 (c1) VALUES (2);
530 INSERT INTO t1 (c1) VALUES (4);
531 connection con1;
532 START TRANSACTION;
533 INSERT INTO t1 (c1) VALUES (NULL), (10);
534 connection default;
535 INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19);
536 INSERT INTO t1 (c1) VALUES (21);
537 connection con1;
538 INSERT INTO t1 (c1) VALUES (NULL);
539 connection default;
540 INSERT INTO t1 (c1) VALUES (16);
541 # ERROR (only OK if Archive) mysql_errno: 1022
542 connection con1;
543 INSERT INTO t1 (c1) VALUES (NULL);
544 SELECT * FROM t1 ORDER BY c1;
545 c1
546 2
547 4
548 5
549 10
550 11
551 12
552 19
553 21
554 22
555 23
556 COMMIT;
557 SELECT * FROM t1 ORDER BY c1;
558 c1
559 2
560 4
561 5
562 10
563 11
564 12
565 19
566 21
567 22
568 23
569 disconnect con1;
570 connection default;
571 INSERT INTO t1 (c1) VALUES (NULL);
572 SELECT * FROM t1 ORDER BY c1;
573 c1
574 2
575 4
576 5
577 10
578 11
579 12
580 19
581 21
582 22
583 23
584 24
585 COMMIT;
586 SELECT * FROM t1 ORDER BY c1;
587 c1
588 2
589 4
590 5
591 10
592 11
593 12
594 19
595 21
596 22
597 23
598 24
599 DROP TABLE t1;
600 # Test with another column before
601 CREATE TABLE t1 (
602 c1 INT,
603 c2 INT NOT NULL AUTO_INCREMENT,
604 PRIMARY KEY (c2))
605 ENGINE = 'Archive'
606 PARTITION BY HASH(c2)
607 PARTITIONS 2;
608 INSERT INTO t1 VALUES (1, 0);
609 INSERT INTO t1 VALUES (1, 1);
610 INSERT INTO t1 VALUES (1, NULL);
611 INSERT INTO t1 VALUES (2, NULL), (3, 11), (3, NULL), (2, 0);
612 INSERT INTO t1 VALUES (2, NULL);
613 INSERT INTO t1 VALUES (2, 2);
614 INSERT INTO t1 VALUES (2, 22);
615 INSERT INTO t1 VALUES (2, NULL);
616 SELECT * FROM t1 ORDER BY c1,c2;
617 c1 c2
618 1 1
619 1 2
620 2 3
621 2 13
622 2 14
623 2 22
624 2 23
625 3 11
626 3 12
627 DROP TABLE t1;
628 # Test with auto_increment on secondary column in multi-column-index
629 CREATE TABLE t1 (
630 c1 INT,
631 c2 INT NOT NULL AUTO_INCREMENT,
632 PRIMARY KEY (c1,c2))
633 ENGINE = 'Archive'
634 PARTITION BY HASH(c2)
635 PARTITIONS 2;
636 ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key
637 # Test AUTO_INCREMENT in CREATE
638 CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
639 ENGINE = 'Archive'
640 AUTO_INCREMENT = 15
641 PARTITION BY HASH(c1)
642 PARTITIONS 2;
643 SHOW CREATE TABLE t1;
644 Table Create Table
645 t1 CREATE TABLE `t1` (
646 `c1` int(11) NOT NULL AUTO_INCREMENT,
647 PRIMARY KEY (`c1`)
648 ) ENGINE=ARCHIVE AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
649 PARTITION BY HASH (`c1`)
650 PARTITIONS 2
651 INSERT INTO t1 (c1) VALUES (4);
652 # ERROR (only OK if Archive) mysql_errno: 1022
653 SHOW CREATE TABLE t1;
654 Table Create Table
655 t1 CREATE TABLE `t1` (
656 `c1` int(11) NOT NULL AUTO_INCREMENT,
657 PRIMARY KEY (`c1`)
658 ) ENGINE=ARCHIVE AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
659 PARTITION BY HASH (`c1`)
660 PARTITIONS 2
661 INSERT INTO t1 (c1) VALUES (0);
662 SHOW CREATE TABLE t1;
663 Table Create Table
664 t1 CREATE TABLE `t1` (
665 `c1` int(11) NOT NULL AUTO_INCREMENT,
666 PRIMARY KEY (`c1`)
667 ) ENGINE=ARCHIVE AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
668 PARTITION BY HASH (`c1`)
669 PARTITIONS 2
670 INSERT INTO t1 (c1) VALUES (NULL);
671 SHOW CREATE TABLE t1;
672 Table Create Table
673 t1 CREATE TABLE `t1` (
674 `c1` int(11) NOT NULL AUTO_INCREMENT,
675 PRIMARY KEY (`c1`)
676 ) ENGINE=ARCHIVE AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
677 PARTITION BY HASH (`c1`)
678 PARTITIONS 2
679 SELECT * FROM t1 ORDER BY c1;
680 c1
681 15
682 16
683 # Test sql_mode 'NO_AUTO_VALUE_ON_ZERO'
684 SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
685 INSERT INTO t1 (c1) VALUES (300);
686 SHOW CREATE TABLE t1;
687 Table Create Table
688 t1 CREATE TABLE `t1` (
689 `c1` int(11) NOT NULL AUTO_INCREMENT,
690 PRIMARY KEY (`c1`)
691 ) ENGINE=ARCHIVE AUTO_INCREMENT=301 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
692 PARTITION BY HASH (`c1`)
693 PARTITIONS 2
694 INSERT INTO t1 (c1) VALUES (0);
695 # ERROR (only OK if Archive) mysql_errno: 1022
696 SHOW CREATE TABLE t1;
697 Table Create Table
698 t1 CREATE TABLE `t1` (
699 `c1` int(11) NOT NULL AUTO_INCREMENT,
700 PRIMARY KEY (`c1`)
701 ) ENGINE=ARCHIVE AUTO_INCREMENT=301 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
702 PARTITION BY HASH (`c1`)
703 PARTITIONS 2
704 INSERT INTO t1 (c1) VALUES (NULL);
705 SHOW CREATE TABLE t1;
706 Table Create Table
707 t1 CREATE TABLE `t1` (
708 `c1` int(11) NOT NULL AUTO_INCREMENT,
709 PRIMARY KEY (`c1`)
710 ) ENGINE=ARCHIVE AUTO_INCREMENT=302 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
711 PARTITION BY HASH (`c1`)
712 PARTITIONS 2
713 SELECT * FROM t1 ORDER BY c1;
714 c1
715 15
716 16
717 300
718 301
719 SET @@session.sql_mode = '';
720 DROP TABLE t1;
721 # Test SET INSERT_ID
722 CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
723 ENGINE = 'Archive'
724 PARTITION BY HASH(c1)
725 PARTITIONS 2;
726 SHOW CREATE TABLE t1;
727 Table Create Table
728 t1 CREATE TABLE `t1` (
729 `c1` int(11) NOT NULL AUTO_INCREMENT,
730 PRIMARY KEY (`c1`)
731 ) ENGINE=ARCHIVE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
732 PARTITION BY HASH (`c1`)
733 PARTITIONS 2
734 INSERT INTO t1 (c1) VALUES (NULL);
735 SHOW CREATE TABLE t1;
736 Table Create Table
737 t1 CREATE TABLE `t1` (
738 `c1` int(11) NOT NULL AUTO_INCREMENT,
739 PRIMARY KEY (`c1`)
740 ) ENGINE=ARCHIVE AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
741 PARTITION BY HASH (`c1`)
742 PARTITIONS 2
743 SELECT * FROM t1;
744 c1
745 1
746 SET INSERT_ID = 23;
747 SHOW CREATE TABLE t1;
748 Table Create Table
749 t1 CREATE TABLE `t1` (
750 `c1` int(11) NOT NULL AUTO_INCREMENT,
751 PRIMARY KEY (`c1`)
752 ) ENGINE=ARCHIVE AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
753 PARTITION BY HASH (`c1`)
754 PARTITIONS 2
755 INSERT INTO t1 (c1) VALUES (NULL);
756 SHOW CREATE TABLE t1;
757 Table Create Table
758 t1 CREATE TABLE `t1` (
759 `c1` int(11) NOT NULL AUTO_INCREMENT,
760 PRIMARY KEY (`c1`)
761 ) ENGINE=ARCHIVE AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
762 PARTITION BY HASH (`c1`)
763 PARTITIONS 2
764 SET INSERT_ID = 22;
765 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
766 INSERT INTO t1 VALUES (NULL);
767 SELECT * FROM t1 ORDER BY c1;
768 c1
769 1
770 22
771 23
772 24
773 DROP TABLE t1;
774 # Testing with FLUSH TABLE
775 CREATE TABLE t1 (
776 c1 INT NOT NULL AUTO_INCREMENT,
777 PRIMARY KEY (c1))
778 ENGINE='Archive'
779 PARTITION BY HASH(c1)
780 PARTITIONS 2;
781 SHOW CREATE TABLE t1;
782 Table Create Table
783 t1 CREATE TABLE `t1` (
784 `c1` int(11) NOT NULL AUTO_INCREMENT,
785 PRIMARY KEY (`c1`)
786 ) ENGINE=ARCHIVE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
787 PARTITION BY HASH (`c1`)
788 PARTITIONS 2
789 FLUSH TABLE;
790 SHOW CREATE TABLE t1;
791 Table Create Table
792 t1 CREATE TABLE `t1` (
793 `c1` int(11) NOT NULL AUTO_INCREMENT,
794 PRIMARY KEY (`c1`)
795 ) ENGINE=ARCHIVE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
796 PARTITION BY HASH (`c1`)
797 PARTITIONS 2
798 INSERT INTO t1 VALUES (4);
799 FLUSH TABLE;
800 SHOW CREATE TABLE t1;
801 Table Create Table
802 t1 CREATE TABLE `t1` (
803 `c1` int(11) NOT NULL AUTO_INCREMENT,
804 PRIMARY KEY (`c1`)
805 ) ENGINE=ARCHIVE AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
806 PARTITION BY HASH (`c1`)
807 PARTITIONS 2
808 INSERT INTO t1 VALUES (NULL);
809 FLUSH TABLE;
810 SHOW CREATE TABLE t1;
811 Table Create Table
812 t1 CREATE TABLE `t1` (
813 `c1` int(11) NOT NULL AUTO_INCREMENT,
814 PRIMARY KEY (`c1`)
815 ) ENGINE=ARCHIVE AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
816 PARTITION BY HASH (`c1`)
817 PARTITIONS 2
818 SELECT * FROM t1 ORDER BY c1;
819 c1
820 4
821 5
822 DROP TABLE t1;