Debian Codegraph @

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

raw
1 DROP TABLE IF EXISTS t1;
2 SET SESSION information_schema_stats_expiry=0;
3 # test without partitioning for reference
4 CREATE TABLE t1 (
5 c1 INT NOT NULL AUTO_INCREMENT,
6 PRIMARY KEY (c1))
7 ENGINE='InnoDB';
8 SHOW CREATE TABLE t1;
9 Table Create Table
10 t1 CREATE TABLE `t1` (
11 `c1` int NOT NULL AUTO_INCREMENT,
12 PRIMARY KEY (`c1`)
13 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
14 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
15 AND TABLE_NAME='t1';
16 AUTO_INCREMENT
17 1
18 INSERT INTO t1 VALUES (2);
19 INSERT INTO t1 VALUES (4);
20 INSERT INTO t1 VALUES (NULL);
21 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
22 AND TABLE_NAME='t1';
23 AUTO_INCREMENT
24 6
25 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
26 AND TABLE_NAME='t1';
27 AUTO_INCREMENT
28 6
29 INSERT INTO t1 VALUES (0);
30 INSERT INTO t1 VALUES (5), (16);
31 INSERT INTO t1 VALUES (17);
32 INSERT INTO t1 VALUES (19), (NULL);
33 INSERT INTO t1 VALUES (NULL), (10), (NULL);
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 UPDATE t1 SET c1 = 50 WHERE c1 = 17;
41 UPDATE t1 SET c1 = 51 WHERE c1 = 19;
42 FLUSH TABLES;
43 UPDATE t1 SET c1 = 40 WHERE c1 = 50;
44 UPDATE t1 SET c1 = -1 WHERE c1 = 40;
45 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
46 AND TABLE_NAME='t1';
47 AUTO_INCREMENT
48 52
49 UPDATE IGNORE t1 SET c1 = NULL WHERE c1 = 4;
50 Warnings:
51 Warning 1048 Column 'c1' cannot be null
52 INSERT INTO t1 VALUES (NULL);
53 INSERT INTO t1 VALUES (NULL);
54 SELECT * FROM t1 ORDER BY c1;
55 c1
56 -1
57 0
58 2
59 5
60 6
61 10
62 20
63 22
64 23
65 25
66 30
67 31
68 51
69 52
70 53
71 DROP TABLE t1;
72 CREATE TABLE t1 (
73 c1 INT NOT NULL AUTO_INCREMENT,
74 PRIMARY KEY (c1))
75 ENGINE='InnoDB';
76 SHOW CREATE TABLE t1;
77 Table Create Table
78 t1 CREATE TABLE `t1` (
79 `c1` int NOT NULL AUTO_INCREMENT,
80 PRIMARY KEY (`c1`)
81 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
82 FLUSH TABLE;
83 SHOW CREATE TABLE t1;
84 Table Create Table
85 t1 CREATE TABLE `t1` (
86 `c1` int NOT NULL AUTO_INCREMENT,
87 PRIMARY KEY (`c1`)
88 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
89 INSERT INTO t1 VALUES (4);
90 FLUSH TABLE;
91 SHOW CREATE TABLE t1;
92 Table Create Table
93 t1 CREATE TABLE `t1` (
94 `c1` int NOT NULL AUTO_INCREMENT,
95 PRIMARY KEY (`c1`)
96 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
97 INSERT INTO t1 VALUES (NULL);
98 FLUSH TABLE;
99 SHOW CREATE TABLE t1;
100 Table Create Table
101 t1 CREATE TABLE `t1` (
102 `c1` int NOT NULL AUTO_INCREMENT,
103 PRIMARY KEY (`c1`)
104 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
105 DELETE FROM t1;
106 INSERT INTO t1 VALUES (NULL);
107 INSERT INTO t1 VALUES (-1);
108 INSERT INTO t1 VALUES (NULL);
109 INSERT INTO t1 VALUES (NULL);
110 SHOW CREATE TABLE t1;
111 Table Create Table
112 t1 CREATE TABLE `t1` (
113 `c1` int NOT NULL AUTO_INCREMENT,
114 PRIMARY KEY (`c1`)
115 ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
116 SELECT * FROM t1 ORDER BY c1;
117 c1
118 -1
119 6
120 7
121 8
122 TRUNCATE TABLE t1;
123 INSERT INTO t1 VALUES (NULL);
124 SHOW CREATE TABLE t1;
125 Table Create Table
126 t1 CREATE TABLE `t1` (
127 `c1` int NOT NULL AUTO_INCREMENT,
128 PRIMARY KEY (`c1`)
129 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
130 SELECT * FROM t1 ORDER BY c1;
131 c1
132 1
133 INSERT INTO t1 VALUES (100);
134 INSERT INTO t1 VALUES (NULL);
135 DELETE FROM t1 WHERE c1 >= 100;
136 OPTIMIZE TABLE t1;
137 Table Op Msg_type Msg_text
138 test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
139 test.t1 optimize status OK
140 SHOW CREATE TABLE t1;
141 Table Create Table
142 t1 CREATE TABLE `t1` (
143 `c1` int NOT NULL AUTO_INCREMENT,
144 PRIMARY KEY (`c1`)
145 ) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
146 DROP TABLE t1;
147 CREATE TABLE t1
148 (a INT NULL AUTO_INCREMENT,
149 UNIQUE KEY (a))
150 ENGINE='InnoDB';
151 SET LAST_INSERT_ID = 999;
152 SET INSERT_ID = 0;
153 INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
154 SELECT LAST_INSERT_ID();
155 LAST_INSERT_ID()
156 999
157 SELECT * FROM t1;
158 a
159 1
160 INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
161 SELECT LAST_INSERT_ID();
162 LAST_INSERT_ID()
163 999
164 SELECT * FROM t1;
165 a
166 0
167 UPDATE t1 SET a = 1 WHERE a IS NULL;
168 SELECT LAST_INSERT_ID();
169 LAST_INSERT_ID()
170 999
171 SELECT * FROM t1;
172 a
173 0
174 UPDATE t1 SET a = NULL WHERE a = 1;
175 SELECT LAST_INSERT_ID();
176 LAST_INSERT_ID()
177 999
178 SELECT * FROM t1;
179 a
180 0
181 DROP TABLE t1;
182 SET INSERT_ID = 1;
183 # Simple test with NULL
184 CREATE TABLE t1 (
185 c1 INT NOT NULL AUTO_INCREMENT,
186 PRIMARY KEY (c1))
187 ENGINE='InnoDB'
188 PARTITION BY HASH(c1)
189 PARTITIONS 2;
190 INSERT INTO t1 VALUES (NULL);
191 SHOW CREATE TABLE t1;
192 Table Create Table
193 t1 CREATE TABLE `t1` (
194 `c1` int NOT NULL AUTO_INCREMENT,
195 PRIMARY KEY (`c1`)
196 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
197 /*!50100 PARTITION BY HASH (`c1`)
198 PARTITIONS 2 */
199 SELECT * FROM t1;
200 c1
201 1
202 DROP TABLE t1;
203 # Test with sql_mode and first insert as 0
204 CREATE TABLE t1 (
205 c1 INT,
206 c2 INT NOT NULL AUTO_INCREMENT,
207 PRIMARY KEY (c2))
208 ENGINE='InnoDB'
209 PARTITION BY HASH(c2)
210 PARTITIONS 2;
211 INSERT INTO t1 VALUES (1, NULL);
212 INSERT INTO t1 VALUES (1, 1), (99, 99);
213 INSERT INTO t1 VALUES (1, NULL);
214 SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
215 INSERT INTO t1 VALUES (1, 0);
216 SELECT * FROM t1 ORDER BY c1, c2;
217 c1 c2
218 1 0
219 1 1
220 1 2
221 DROP TABLE t1;
222 CREATE TABLE t1 (
223 c1 INT,
224 c2 INT NOT NULL AUTO_INCREMENT,
225 PRIMARY KEY (c2))
226 ENGINE='InnoDB'
227 PARTITION BY HASH(c2)
228 PARTITIONS 2;
229 INSERT INTO t1 VALUES (1, 0);
230 INSERT INTO t1 VALUES (1, 1), (1, NULL);
231 INSERT INTO t1 VALUES (2, NULL), (4, 7);
232 INSERT INTO t1 VALUES (1, NULL);
233 SELECT * FROM t1 ORDER BY c1, c2;
234 c1 c2
235 1 0
236 1 1
237 1 2
238 1 8
239 2 3
240 4 7
241 SET @@session.sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
242 DROP TABLE t1;
243 # Simple test with NULL, 0 and explicit values both incr. and desc.
244 CREATE TABLE t1 (
245 c1 INT NOT NULL AUTO_INCREMENT,
246 PRIMARY KEY (c1))
247 ENGINE='InnoDB'
248 PARTITION BY HASH(c1)
249 PARTITIONS 2;
250 INSERT INTO t1 VALUES (2), (4), (NULL);
251 INSERT INTO t1 VALUES (0);
252 INSERT INTO t1 VALUES (5), (16);
253 INSERT INTO t1 VALUES (17), (19), (NULL);
254 INSERT INTO t1 VALUES (NULL), (10), (NULL);
255 INSERT INTO t1 VALUES (NULL), (9);
256 INSERT INTO t1 VALUES (59), (55);
257 INSERT INTO t1 VALUES (NULL), (90);
258 INSERT INTO t1 VALUES (NULL);
259 UPDATE t1 SET c1 = 150 WHERE c1 = 17;
260 UPDATE t1 SET c1 = 151 WHERE c1 = 19;
261 FLUSH TABLES;
262 UPDATE t1 SET c1 = 140 WHERE c1 = 150;
263 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
264 AND TABLE_NAME='t1';
265 AUTO_INCREMENT
266 152
267 UPDATE IGNORE t1 SET c1 = NULL WHERE c1 = 4;
268 Warnings:
269 Warning 1048 Column 'c1' cannot be null
270 INSERT INTO t1 VALUES (NULL);
271 INSERT INTO t1 VALUES (NULL);
272 SELECT * FROM t1 ORDER BY c1;
273 c1
274 0
275 2
276 5
277 6
278 9
279 10
280 20
281 21
282 22
283 23
284 55
285 59
286 60
287 90
288 91
289 140
290 151
291 152
292 153
293 DROP TABLE t1;
294 # Test with auto_increment_increment and auto_increment_offset.
295 CREATE TABLE t1 (
296 c1 INT NOT NULL AUTO_INCREMENT,
297 PRIMARY KEY (c1))
298 ENGINE='InnoDB'
299 PARTITION BY HASH(c1)
300 PARTITIONS 2;
301 SET @@session.auto_increment_increment = 10;
302 SET @@session.auto_increment_offset = 5;
303 INSERT INTO t1 VALUES (1);
304 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
305 SET @@session.auto_increment_increment = 5;
306 SET @@session.auto_increment_offset = 3;
307 INSERT INTO t1 VALUES (NULL);
308 INSERT INTO t1 VALUES (33 + 1);
309 INSERT INTO t1 VALUES (NULL);
310 INSERT INTO t1 VALUES (38 + 2);
311 INSERT INTO t1 VALUES (NULL);
312 INSERT INTO t1 VALUES (43 + 3);
313 INSERT INTO t1 VALUES (NULL);
314 INSERT INTO t1 VALUES (48 + 4);
315 INSERT INTO t1 VALUES (NULL);
316 INSERT INTO t1 VALUES (53 + 5);
317 INSERT INTO t1 VALUES (NULL);
318 INSERT INTO t1 VALUES (63 + 6);
319 INSERT INTO t1 VALUES (NULL);
320 SET @@session.auto_increment_increment = 1;
321 SET @@session.auto_increment_offset = 1;
322 SELECT * FROM t1 ORDER BY c1;
323 c1
324 1
325 5
326 15
327 25
328 33
329 34
330 38
331 40
332 43
333 46
334 48
335 52
336 53
337 58
338 63
339 69
340 73
341 DROP TABLE t1;
342 # Test reported auto_increment value
343 CREATE TABLE t1 (
344 c1 INT NOT NULL AUTO_INCREMENT,
345 PRIMARY KEY (c1))
346 ENGINE='InnoDB'
347 PARTITION BY HASH (c1)
348 PARTITIONS 2;
349 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
350 AND TABLE_NAME='t1';
351 AUTO_INCREMENT
352 1
353 INSERT INTO t1 VALUES (2);
354 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
355 AND TABLE_NAME='t1';
356 AUTO_INCREMENT
357 3
358 INSERT INTO t1 VALUES (4);
359 INSERT INTO t1 VALUES (NULL);
360 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
361 AND TABLE_NAME='t1';
362 AUTO_INCREMENT
363 6
364 INSERT INTO t1 VALUES (NULL);
365 INSERT INTO t1 VALUES (17);
366 INSERT INTO t1 VALUES (19);
367 INSERT INTO t1 VALUES (NULL);
368 INSERT INTO t1 VALUES (NULL);
369 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
370 AND TABLE_NAME='t1';
371 AUTO_INCREMENT
372 22
373 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
374 AND TABLE_NAME='t1';
375 AUTO_INCREMENT
376 22
377 INSERT INTO t1 VALUES (10);
378 SELECT * FROM t1 ORDER BY c1;
379 c1
380 2
381 4
382 5
383 6
384 10
385 17
386 19
387 20
388 21
389 INSERT INTO t1 VALUES (NULL);
390 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
391 AND TABLE_NAME='t1';
392 AUTO_INCREMENT
393 23
394 INSERT INTO t1 VALUES (NULL);
395 INSERT INTO t1 VALUES (15);
396 INSERT INTO t1 VALUES (NULL);
397 SELECT * FROM t1 ORDER BY c1;
398 c1
399 2
400 4
401 5
402 6
403 10
404 15
405 17
406 19
407 20
408 21
409 22
410 23
411 24
412 INSERT INTO t1 VALUES (NULL);
413 DELETE FROM t1;
414 INSERT INTO t1 VALUES (NULL);
415 SHOW CREATE TABLE t1;
416 Table Create Table
417 t1 CREATE TABLE `t1` (
418 `c1` int NOT NULL AUTO_INCREMENT,
419 PRIMARY KEY (`c1`)
420 ) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
421 /*!50100 PARTITION BY HASH (`c1`)
422 PARTITIONS 2 */
423 SELECT * FROM t1 ORDER BY c1;
424 c1
425 26
426 TRUNCATE TABLE t1;
427 INSERT INTO t1 VALUES (NULL);
428 SHOW CREATE TABLE t1;
429 Table Create Table
430 t1 CREATE TABLE `t1` (
431 `c1` int NOT NULL AUTO_INCREMENT,
432 PRIMARY KEY (`c1`)
433 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
434 /*!50100 PARTITION BY HASH (`c1`)
435 PARTITIONS 2 */
436 SELECT * FROM t1 ORDER BY c1;
437 c1
438 1
439 INSERT INTO t1 VALUES (100);
440 INSERT INTO t1 VALUES (NULL);
441 DELETE FROM t1 WHERE c1 >= 100;
442 OPTIMIZE TABLE t1;
443 Table Op Msg_type Msg_text
444 test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
445 test.t1 optimize status OK
446 SHOW CREATE TABLE t1;
447 Table Create Table
448 t1 CREATE TABLE `t1` (
449 `c1` int NOT NULL AUTO_INCREMENT,
450 PRIMARY KEY (`c1`)
451 ) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
452 /*!50100 PARTITION BY HASH (`c1`)
453 PARTITIONS 2 */
454 DROP TABLE t1;
455 # Test with two threads
456 # con default
457 CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
458 ENGINE = 'InnoDB'
459 PARTITION BY HASH(c1)
460 PARTITIONS 2;
461 INSERT INTO t1 (c1) VALUES (2);
462 INSERT INTO t1 (c1) VALUES (4);
463 # con1
464 INSERT INTO t1 (c1) VALUES (NULL);
465 INSERT INTO t1 (c1) VALUES (10);
466 # con default
467 INSERT INTO t1 (c1) VALUES (NULL);
468 INSERT INTO t1 (c1) VALUES (NULL);
469 INSERT INTO t1 (c1) VALUES (19);
470 INSERT INTO t1 (c1) VALUES (21);
471 # con1
472 INSERT INTO t1 (c1) VALUES (NULL);
473 # con default
474 INSERT INTO t1 (c1) VALUES (16);
475 # con1
476 INSERT INTO t1 (c1) VALUES (NULL);
477 # con default
478 INSERT INTO t1 (c1) VALUES (NULL);
479 SELECT * FROM t1 ORDER BY c1;
480 c1
481 2
482 4
483 5
484 10
485 11
486 12
487 16
488 19
489 21
490 22
491 23
492 24
493 DROP TABLE t1;
494 # Test with two threads + start transaction NO PARTITIONING
495 # con default
496 CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
497 ENGINE = 'InnoDB';
498 START TRANSACTION;
499 INSERT INTO t1 (c1) VALUES (2);
500 INSERT INTO t1 (c1) VALUES (4);
501 # con1
502 START TRANSACTION;
503 INSERT INTO t1 (c1) VALUES (NULL);
504 INSERT INTO t1 (c1) VALUES (10);
505 # con default
506 INSERT INTO t1 (c1) VALUES (NULL);
507 INSERT INTO t1 (c1) VALUES (NULL);
508 INSERT INTO t1 (c1) VALUES (19);
509 INSERT INTO t1 (c1) VALUES (21);
510 # con1
511 INSERT INTO t1 (c1) VALUES (NULL);
512 # con default
513 INSERT INTO t1 (c1) VALUES (16);
514 # con1
515 INSERT INTO t1 (c1) VALUES (NULL);
516 SELECT * FROM t1 ORDER BY c1;
517 c1
518 5
519 10
520 22
521 23
522 COMMIT;
523 SELECT * FROM t1 ORDER BY c1;
524 c1
525 5
526 10
527 22
528 23
529 # con default
530 INSERT INTO t1 (c1) VALUES (NULL);
531 SELECT * FROM t1 ORDER BY c1;
532 c1
533 2
534 4
535 5
536 10
537 11
538 12
539 16
540 19
541 21
542 22
543 23
544 24
545 COMMIT;
546 SELECT * FROM t1 ORDER BY c1;
547 c1
548 2
549 4
550 5
551 10
552 11
553 12
554 16
555 19
556 21
557 22
558 23
559 24
560 DROP TABLE t1;
561 # Test with two threads + start transaction
562 # con default
563 CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
564 ENGINE = 'InnoDB'
565 PARTITION BY HASH(c1)
566 PARTITIONS 2;
567 START TRANSACTION;
568 INSERT INTO t1 (c1) VALUES (2);
569 INSERT INTO t1 (c1) VALUES (4);
570 # con1
571 START TRANSACTION;
572 INSERT INTO t1 (c1) VALUES (NULL), (10);
573 # con default
574 INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19);
575 INSERT INTO t1 (c1) VALUES (21);
576 # con1
577 INSERT INTO t1 (c1) VALUES (NULL);
578 # con default
579 INSERT INTO t1 (c1) VALUES (16);
580 # con1
581 INSERT INTO t1 (c1) VALUES (NULL);
582 SELECT * FROM t1 ORDER BY c1;
583 c1
584 5
585 10
586 22
587 23
588 COMMIT;
589 SELECT * FROM t1 ORDER BY c1;
590 c1
591 5
592 10
593 22
594 23
595 # con default
596 INSERT INTO t1 (c1) VALUES (NULL);
597 SELECT * FROM t1 ORDER BY c1;
598 c1
599 2
600 4
601 5
602 10
603 11
604 12
605 16
606 19
607 21
608 22
609 23
610 24
611 COMMIT;
612 SELECT * FROM t1 ORDER BY c1;
613 c1
614 2
615 4
616 5
617 10
618 11
619 12
620 16
621 19
622 21
623 22
624 23
625 24
626 DROP TABLE t1;
627 # Test with another column after
628 CREATE TABLE t1 (
629 c1 INT NOT NULL AUTO_INCREMENT,
630 c2 INT,
631 PRIMARY KEY (c1,c2))
632 ENGINE = 'InnoDB'
633 PARTITION BY HASH(c2)
634 PARTITIONS 2;
635 INSERT INTO t1 VALUES (1, 0);
636 INSERT INTO t1 VALUES (1, 1);
637 INSERT INTO t1 VALUES (NULL, 1), (NULL, 2), (NULL, 3);
638 INSERT INTO t1 VALUES (NULL, 3);
639 INSERT INTO t1 VALUES (2, 0), (NULL, 2);
640 INSERT INTO t1 VALUES (2, 2);
641 INSERT INTO t1 VALUES (2, 22);
642 INSERT INTO t1 VALUES (NULL, 2);
643 SELECT * FROM t1 ORDER BY c1,c2;
644 c1 c2
645 1 0
646 1 1
647 2 0
648 2 1
649 2 2
650 2 22
651 3 2
652 4 3
653 5 3
654 6 2
655 7 2
656 DROP TABLE t1;
657 # Test with another column before
658 CREATE TABLE t1 (
659 c1 INT,
660 c2 INT NOT NULL AUTO_INCREMENT,
661 PRIMARY KEY (c2))
662 ENGINE = 'InnoDB'
663 PARTITION BY HASH(c2)
664 PARTITIONS 2;
665 INSERT INTO t1 VALUES (1, 0);
666 INSERT INTO t1 VALUES (1, 1);
667 INSERT INTO t1 VALUES (1, NULL);
668 INSERT INTO t1 VALUES (2, NULL), (3, 11), (3, NULL), (2, 0);
669 INSERT INTO t1 VALUES (2, NULL);
670 INSERT INTO t1 VALUES (2, 2);
671 INSERT INTO t1 VALUES (2, 22);
672 INSERT INTO t1 VALUES (2, NULL);
673 SELECT * FROM t1 ORDER BY c1,c2;
674 c1 c2
675 1 1
676 1 2
677 2 3
678 2 13
679 2 14
680 2 22
681 2 23
682 3 11
683 3 12
684 DROP TABLE t1;
685 # Test with auto_increment on secondary column in multi-column-index
686 CREATE TABLE t1 (
687 c1 INT,
688 c2 INT NOT NULL AUTO_INCREMENT,
689 PRIMARY KEY (c1,c2))
690 ENGINE = 'InnoDB'
691 PARTITION BY HASH(c2)
692 PARTITIONS 2;
693 ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key
694 # Test AUTO_INCREMENT in CREATE
695 CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
696 ENGINE = 'InnoDB'
697 AUTO_INCREMENT = 15
698 PARTITION BY HASH(c1)
699 PARTITIONS 2;
700 SHOW CREATE TABLE t1;
701 Table Create Table
702 t1 CREATE TABLE `t1` (
703 `c1` int NOT NULL AUTO_INCREMENT,
704 PRIMARY KEY (`c1`)
705 ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
706 /*!50100 PARTITION BY HASH (`c1`)
707 PARTITIONS 2 */
708 INSERT INTO t1 (c1) VALUES (4);
709 SHOW CREATE TABLE t1;
710 Table Create Table
711 t1 CREATE TABLE `t1` (
712 `c1` int NOT NULL AUTO_INCREMENT,
713 PRIMARY KEY (`c1`)
714 ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
715 /*!50100 PARTITION BY HASH (`c1`)
716 PARTITIONS 2 */
717 INSERT INTO t1 (c1) VALUES (0);
718 SHOW CREATE TABLE t1;
719 Table Create Table
720 t1 CREATE TABLE `t1` (
721 `c1` int NOT NULL AUTO_INCREMENT,
722 PRIMARY KEY (`c1`)
723 ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
724 /*!50100 PARTITION BY HASH (`c1`)
725 PARTITIONS 2 */
726 INSERT INTO t1 (c1) VALUES (NULL);
727 SHOW CREATE TABLE t1;
728 Table Create Table
729 t1 CREATE TABLE `t1` (
730 `c1` int NOT NULL AUTO_INCREMENT,
731 PRIMARY KEY (`c1`)
732 ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
733 /*!50100 PARTITION BY HASH (`c1`)
734 PARTITIONS 2 */
735 SELECT * FROM t1 ORDER BY c1;
736 c1
737 4
738 15
739 16
740 # Test sql_mode 'NO_AUTO_VALUE_ON_ZERO'
741 SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
742 INSERT INTO t1 (c1) VALUES (300);
743 SHOW CREATE TABLE t1;
744 Table Create Table
745 t1 CREATE TABLE `t1` (
746 `c1` int NOT NULL AUTO_INCREMENT,
747 PRIMARY KEY (`c1`)
748 ) ENGINE=InnoDB AUTO_INCREMENT=301 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
749 /*!50100 PARTITION BY HASH (`c1`)
750 PARTITIONS 2 */
751 INSERT INTO t1 (c1) VALUES (0);
752 SHOW CREATE TABLE t1;
753 Table Create Table
754 t1 CREATE TABLE `t1` (
755 `c1` int NOT NULL AUTO_INCREMENT,
756 PRIMARY KEY (`c1`)
757 ) ENGINE=InnoDB AUTO_INCREMENT=301 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
758 /*!50100 PARTITION BY HASH (`c1`)
759 PARTITIONS 2 */
760 INSERT INTO t1 (c1) VALUES (NULL);
761 SHOW CREATE TABLE t1;
762 Table Create Table
763 t1 CREATE TABLE `t1` (
764 `c1` int NOT NULL AUTO_INCREMENT,
765 PRIMARY KEY (`c1`)
766 ) ENGINE=InnoDB AUTO_INCREMENT=302 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
767 /*!50100 PARTITION BY HASH (`c1`)
768 PARTITIONS 2 */
769 SELECT * FROM t1 ORDER BY c1;
770 c1
771 0
772 4
773 15
774 16
775 300
776 301
777 SET @@session.sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
778 DROP TABLE t1;
779 # Test SET INSERT_ID
780 CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
781 ENGINE = 'InnoDB'
782 PARTITION BY HASH(c1)
783 PARTITIONS 2;
784 SHOW CREATE TABLE t1;
785 Table Create Table
786 t1 CREATE TABLE `t1` (
787 `c1` int NOT NULL AUTO_INCREMENT,
788 PRIMARY KEY (`c1`)
789 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
790 /*!50100 PARTITION BY HASH (`c1`)
791 PARTITIONS 2 */
792 INSERT INTO t1 (c1) VALUES (NULL);
793 SHOW CREATE TABLE t1;
794 Table Create Table
795 t1 CREATE TABLE `t1` (
796 `c1` int NOT NULL AUTO_INCREMENT,
797 PRIMARY KEY (`c1`)
798 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
799 /*!50100 PARTITION BY HASH (`c1`)
800 PARTITIONS 2 */
801 SELECT * FROM t1;
802 c1
803 1
804 SET INSERT_ID = 23;
805 SHOW CREATE TABLE t1;
806 Table Create Table
807 t1 CREATE TABLE `t1` (
808 `c1` int NOT NULL AUTO_INCREMENT,
809 PRIMARY KEY (`c1`)
810 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
811 /*!50100 PARTITION BY HASH (`c1`)
812 PARTITIONS 2 */
813 INSERT INTO t1 (c1) VALUES (NULL);
814 SHOW CREATE TABLE t1;
815 Table Create Table
816 t1 CREATE TABLE `t1` (
817 `c1` int NOT NULL AUTO_INCREMENT,
818 PRIMARY KEY (`c1`)
819 ) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
820 /*!50100 PARTITION BY HASH (`c1`)
821 PARTITIONS 2 */
822 SET INSERT_ID = 22;
823 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
824 INSERT INTO t1 VALUES (NULL);
825 SELECT * FROM t1 ORDER BY c1;
826 c1
827 1
828 23
829 24
830 DROP TABLE t1;
831 # Testing with FLUSH TABLE
832 CREATE TABLE t1 (
833 c1 INT NOT NULL AUTO_INCREMENT,
834 PRIMARY KEY (c1))
835 ENGINE='InnoDB'
836 PARTITION BY HASH(c1)
837 PARTITIONS 2;
838 SHOW CREATE TABLE t1;
839 Table Create Table
840 t1 CREATE TABLE `t1` (
841 `c1` int NOT NULL AUTO_INCREMENT,
842 PRIMARY KEY (`c1`)
843 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
844 /*!50100 PARTITION BY HASH (`c1`)
845 PARTITIONS 2 */
846 FLUSH TABLE;
847 SHOW CREATE TABLE t1;
848 Table Create Table
849 t1 CREATE TABLE `t1` (
850 `c1` int NOT NULL AUTO_INCREMENT,
851 PRIMARY KEY (`c1`)
852 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
853 /*!50100 PARTITION BY HASH (`c1`)
854 PARTITIONS 2 */
855 INSERT INTO t1 VALUES (4);
856 FLUSH TABLE;
857 SHOW CREATE TABLE t1;
858 Table Create Table
859 t1 CREATE TABLE `t1` (
860 `c1` int NOT NULL AUTO_INCREMENT,
861 PRIMARY KEY (`c1`)
862 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
863 /*!50100 PARTITION BY HASH (`c1`)
864 PARTITIONS 2 */
865 INSERT INTO t1 VALUES (NULL);
866 FLUSH TABLE;
867 SHOW CREATE TABLE t1;
868 Table Create Table
869 t1 CREATE TABLE `t1` (
870 `c1` int NOT NULL AUTO_INCREMENT,
871 PRIMARY KEY (`c1`)
872 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
873 /*!50100 PARTITION BY HASH (`c1`)
874 PARTITIONS 2 */
875 SELECT * FROM t1 ORDER BY c1;
876 c1
877 4
878 5
879 DROP TABLE t1;
880 #############################################################################
881 # Bug #45823 - Assertion failure in file row/row0mysql.c line 1386
882 # Bug #43988 - AUTO_INCREMENT errors with partitioned InnoDB tables in 5.1.31
883 ##############################################################################
884 # Inserting negative autoincrement values into a partition table (partitions >= 4)
885 CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
886 c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 4;
887 INSERT INTO t(c2) VALUES (10);
888 INSERT INTO t(c2) VALUES (20);
889 INSERT INTO t VALUES (-1,-10);
890 INSERT INTO t(c2) VALUES (30);
891 INSERT INTO t(c2) VALUES (40);
892 SELECT * FROM t ORDER BY c1 ASC;
893 c1 c2
894 -1 -10
895 1 10
896 2 20
897 3 30
898 4 40
899 DROP TABLE t;
900 # Reading from a partition table (partitions >= 2 ) after inserting a negative
901 # value into the auto increment column
902 CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
903 c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 2;
904 INSERT INTO t VALUES (-2,-20);
905 INSERT INTO t(c2) VALUES (30);
906 SELECT * FROM t ORDER BY c1 ASC;
907 c1 c2
908 -2 -20
909 1 30
910 DROP TABLE t;
911 # Inserting negative auto increment value into a partition table (partitions >= 2)
912 # auto increment value > 2.
913 CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
914 c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 2;
915 INSERT INTO t VALUES (-4,-20);
916 INSERT INTO t(c2) VALUES (30);
917 INSERT INTO t(c2) VALUES (40);
918 SELECT * FROM t ORDER BY c1 ASC;
919 c1 c2
920 -4 -20
921 1 30
922 2 40
923 DROP TABLE t;
924 # Inserting -1 into autoincrement column of a partition table (partition >= 4)
925 CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
926 c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 4;
927 INSERT INTO t(c2) VALUES (10);
928 INSERT INTO t(c2) VALUES (20);
929 INSERT INTO t VALUES (-1,-10);
930 SELECT * FROM t ORDER BY c1 ASC;
931 c1 c2
932 -1 -10
933 1 10
934 2 20
935 INSERT INTO t(c2) VALUES (30);
936 SELECT * FROM t ORDER BY c1 ASC;
937 c1 c2
938 -1 -10
939 1 10
940 2 20
941 3 30
942 DROP TABLE t;
943 # Deleting from an auto increment table after inserting negative values
944 CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
945 c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 4;
946 INSERT INTO t(c2) VALUES (10);
947 INSERT INTO t(c2) VALUES (20);
948 INSERT INTO t VALUES (-1,-10);
949 INSERT INTO t(c2) VALUES (30);
950 INSERT INTO t VALUES (-3,-20);
951 INSERT INTO t(c2) VALUES (40);
952 SELECT * FROM t ORDER BY c1 ASC;
953 c1 c2
954 -3 -20
955 -1 -10
956 1 10
957 2 20
958 3 30
959 4 40
960 DELETE FROM t WHERE c1 > 1;
961 SELECT * FROM t ORDER BY c1 ASC;
962 c1 c2
963 -3 -20
964 -1 -10
965 1 10
966 DROP TABLE t;
967 # Inserting a positive value that exceeds maximum allowed value for an
968 # Auto Increment column (positive maximum)
969 CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
970 c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 4;
971 INSERT INTO t(c2) VALUES (10);
972 INSERT INTO t(c2) VALUES (20);
973 INSERT INTO t VALUES (126,30);
974 INSERT INTO t VALUES (127,40);
975 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
976 INSERT INTO t VALUES (128,50);
977 INSERT INTO t VALUES (129,60);
978 SELECT * FROM t ORDER BY c1 ASC;
979 c1 c2
980 1 10
981 2 20
982 126 30
983 127 40
984 DROP TABLE t;
985 # Inserting a negative value that goes below minimum allowed value for an
986 # Auto Increment column (negative minimum)
987 CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
988 c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 4;
989 INSERT INTO t(c2) VALUES (10);
990 INSERT INTO t(c2) VALUES (20);
991 INSERT INTO t VALUES (-127,30);
992 INSERT INTO t VALUES (-128,40);
993 INSERT INTO t VALUES (-129,50);
994 INSERT INTO t VALUES (-130,60);
995 SELECT * FROM t ORDER BY c1 ASC;
996 c1 c2
997 -128 40
998 -127 30
999 1 10
1000 2 20
1001 DROP TABLE t;
1002 # Updating the partition table with a negative Auto Increment value
1003 CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
1004 c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 4;
1005 INSERT INTO t(c2) VALUES (10);
1006 INSERT INTO t(c2) VALUES (20);
1007 INSERT INTO t VALUES (-1,-10);
1008 INSERT INTO t(c2) VALUES (30);
1009 SELECT * FROM t ORDER BY c1 ASC;
1010 c1 c2
1011 -1 -10
1012 1 10
1013 2 20
1014 3 30
1015 UPDATE t SET c1 = -6 WHERE c1 = 2;
1016 SELECT * FROM t ORDER BY c1 ASC;
1017 c1 c2
1018 -6 20
1019 -1 -10
1020 1 10
1021 3 30
1022 INSERT INTO t(c2) VALUES (40);
1023 INSERT INTO t(c2) VALUES (50);
1024 UPDATE t SET c1 = -6 WHERE c1 = 2;
1025 SELECT * FROM t ORDER BY c1 ASC;
1026 c1 c2
1027 -6 20
1028 -1 -10
1029 1 10
1030 3 30
1031 4 40
1032 5 50
1033 DROP TABLE t;
1034 SET sql_mode = default;
1035 # Updating the partition table with a value that crosses the upper limits
1036 # on both the positive and the negative side.
1037 CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
1038 c2 INT) ENGINE='InnoDB' PARTITION BY HASH(c1) PARTITIONS 4;
1039 INSERT INTO t(c2) VALUES (10);
1040 INSERT INTO t(c2) VALUES (20);
1041 INSERT INTO t VALUES (126,30);
1042 INSERT INTO t VALUES (127,40);
1043 SELECT * FROM t ORDER BY c1 ASC;
1044 c1 c2
1045 1 10
1046 2 20
1047 126 30
1048 127 40
1049 UPDATE IGNORE t SET c1 = 130 where c1 = 127;
1050 Warnings:
1051 Warning 1264 Out of range value for column 'c1' at row 1
1052 SELECT * FROM t ORDER BY c1 ASC;
1053 c1 c2
1054 1 10
1055 2 20
1056 126 30
1057 127 40
1058 UPDATE IGNORE t SET c1 = -140 where c1 = 126;
1059 Warnings:
1060 Warning 1264 Out of range value for column 'c1' at row 1
1061 SELECT * FROM t ORDER BY c1 ASC;
1062 c1 c2
1063 -128 30
1064 1 10
1065 2 20
1066 127 40
1067 DROP TABLE t;
1068 CREATE TABLE t1
1069 (a INT NULL AUTO_INCREMENT,
1070 UNIQUE KEY (a))
1071 ENGINE='InnoDB'
1072 PARTITION BY KEY(a) PARTITIONS 2;
1073 SET LAST_INSERT_ID = 999;
1074 INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
1075 SELECT LAST_INSERT_ID();
1076 LAST_INSERT_ID()
1077 999
1078 SELECT * FROM t1;
1079 a
1080 1
1081 INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
1082 SELECT LAST_INSERT_ID();
1083 LAST_INSERT_ID()
1084 999
1085 SELECT * FROM t1;
1086 a
1087 0
1088 UPDATE t1 SET a = 1 WHERE a IS NULL;
1089 SELECT LAST_INSERT_ID();
1090 LAST_INSERT_ID()
1091 999
1092 SELECT * FROM t1;
1093 a
1094 0
1095 UPDATE t1 SET a = NULL WHERE a = 1;
1096 SELECT LAST_INSERT_ID();
1097 LAST_INSERT_ID()
1098 999
1099 SELECT * FROM t1;
1100 a
1101 0
1102 DROP TABLE t1;
1103 ##############################################################################
1104 #
1105 # Test overflow
1106 #
1107 # Single row insert signed int
1108 CREATE TABLE t1
1109 (a int not null auto_increment primary key, b varchar(64))
1110 ENGINE = 'InnoDB'
1111 PARTITION BY HASH (a) PARTITIONS 3;
1112 INSERT INTO t1 VALUES (0x7FFFFFFD, "2");
1113 INSERT INTO t1 VALUES (NULL, "1");
1114 INSERT INTO t1 VALUES (NULL, "0");
1115 INSERT INTO t1 VALUES (NULL, "Overflow");
1116 SHOW WARNINGS;
1117 Level Code Message
1118 Error 1264 Out of range value for column 'a' at row 1
1119 Error 1264 Out of range value for column 't1' at row 167
1120 INSERT INTO t1 VALUES (5, "non-generated number");
1121 FLUSH TABLES;
1122 SHOW CREATE TABLE t1;
1123 Table Create Table
1124 t1 CREATE TABLE `t1` (
1125 `a` int NOT NULL AUTO_INCREMENT,
1126 `b` varchar(64) DEFAULT NULL,
1127 PRIMARY KEY (`a`)
1128 ) ENGINE=InnoDB AUTO_INCREMENT=2147483648 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1129 /*!50100 PARTITION BY HASH (`a`)
1130 PARTITIONS 3 */
1131 SELECT * FROM t1;
1132 a b
1133 2147483645 2
1134 2147483646 1
1135 2147483647 0
1136 5 non-generated number
1137 DROP TABLE t1;
1138 CREATE TABLE `t1` (
1139 `a` int(11) NOT NULL AUTO_INCREMENT,
1140 `b` varchar(64) DEFAULT NULL,
1141 PRIMARY KEY (`a`)
1142 ) ENGINE='InnoDB'
1143 AUTO_INCREMENT=2147483648 DEFAULT CHARSET=latin1
1144 PARTITION BY HASH (a) PARTITIONS 3;
1145 Warnings:
1146 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1147 INSERT INTO t1 VALUES (NULL, "Overflow");
1148 # mysql_errno: 1264
1149 INSERT INTO t1 VALUES (NULL, "Overflow");
1150 # mysql_errno: 1264
1151 DROP TABLE t1;
1152 # Multi row insert signed int
1153 CREATE TABLE t1
1154 (a int not null auto_increment primary key, b varchar(64))
1155 ENGINE = 'InnoDB'
1156 PARTITION BY HASH (a) PARTITIONS 3;
1157 INSERT INTO t1 VALUES (0x7FFFFFFD, "2");
1158 INSERT INTO t1 VALUES (NULL, "1"), (NULL, "0"), (NULL, "Overflow");
1159 ERROR HY000: Failed to read auto-increment value from storage engine
1160 INSERT INTO t1 VALUES (NULL, "1");
1161 # mysql_errno: 1264
1162 INSERT INTO t1 VALUES (NULL, "0");
1163 # mysql_errno: 1264
1164 INSERT INTO t1 VALUES (NULL, "Overflow");
1165 ERROR 22003: Out of range value for column 'a' at row 1
1166 INSERT INTO t1 VALUES (5, "non-generated number");
1167 FLUSH TABLES;
1168 SHOW CREATE TABLE t1;
1169 Table Create Table
1170 t1 CREATE TABLE `t1` (
1171 `a` int NOT NULL AUTO_INCREMENT,
1172 `b` varchar(64) DEFAULT NULL,
1173 PRIMARY KEY (`a`)
1174 ) ENGINE=InnoDB AUTO_INCREMENT=2147483648 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1175 /*!50100 PARTITION BY HASH (`a`)
1176 PARTITIONS 3 */
1177 SELECT * FROM t1;
1178 a b
1179 2147483645 2
1180 5 non-generated number
1181 DROP TABLE t1;
1182 # Single row insert unsigned int
1183 CREATE TABLE t1
1184 (a int unsigned not null auto_increment primary key, b varchar(64))
1185 ENGINE = 'InnoDB'
1186 PARTITION BY HASH (a) PARTITIONS 3;
1187 INSERT INTO t1 VALUES (0xFFFFFFFD, "2");
1188 INSERT INTO t1 VALUES (NULL, "1");
1189 INSERT INTO t1 VALUES (NULL, "0");
1190 INSERT INTO t1 VALUES (NULL, "Overflow");
1191 ERROR 22003: Out of range value for column 'a' at row 1
1192 INSERT INTO t1 VALUES (5, "non-generated number");
1193 FLUSH TABLES;
1194 SHOW CREATE TABLE t1;
1195 Table Create Table
1196 t1 CREATE TABLE `t1` (
1197 `a` int unsigned NOT NULL AUTO_INCREMENT,
1198 `b` varchar(64) DEFAULT NULL,
1199 PRIMARY KEY (`a`)
1200 ) ENGINE=InnoDB AUTO_INCREMENT=4294967296 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1201 /*!50100 PARTITION BY HASH (`a`)
1202 PARTITIONS 3 */
1203 SELECT * FROM t1;
1204 a b
1205 4294967293 2
1206 4294967294 1
1207 4294967295 0
1208 5 non-generated number
1209 DROP TABLE t1;
1210 CREATE TABLE `t1` (
1211 `a` int(10) unsigned NOT NULL AUTO_INCREMENT,
1212 `b` varchar(64) DEFAULT NULL,
1213 PRIMARY KEY (`a`)
1214 ) ENGINE='InnoDB' AUTO_INCREMENT=4294967296 DEFAULT CHARSET=latin1
1215 PARTITION BY HASH (a) PARTITIONS 3;
1216 Warnings:
1217 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1218 INSERT INTO t1 VALUES (NULL, "Overflow");
1219 # mysql_errno: 1264
1220 DROP TABLE t1;
1221 # Multi row insert unsigned int
1222 CREATE TABLE t1
1223 (a int unsigned not null auto_increment primary key, b varchar(64))
1224 ENGINE = 'InnoDB'
1225 PARTITION BY HASH (a) PARTITIONS 3;
1226 INSERT INTO t1 VALUES (0xFFFFFFFD, "2");
1227 INSERT INTO t1 VALUES (NULL, "1"), (NULL, "0"), (NULL, "Overflow");
1228 ERROR HY000: Failed to read auto-increment value from storage engine
1229 INSERT INTO t1 VALUES (NULL, "1");
1230 # mysql_errno: 1264
1231 INSERT INTO t1 VALUES (NULL, "0");
1232 # mysql_errno: 1264
1233 INSERT INTO t1 VALUES (NULL, "Overflow");
1234 ERROR 22003: Out of range value for column 'a' at row 1
1235 INSERT INTO t1 VALUES (5, "non-generated number");
1236 FLUSH TABLES;
1237 SHOW CREATE TABLE t1;
1238 Table Create Table
1239 t1 CREATE TABLE `t1` (
1240 `a` int unsigned NOT NULL AUTO_INCREMENT,
1241 `b` varchar(64) DEFAULT NULL,
1242 PRIMARY KEY (`a`)
1243 ) ENGINE=InnoDB AUTO_INCREMENT=4294967296 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1244 /*!50100 PARTITION BY HASH (`a`)
1245 PARTITIONS 3 */
1246 SELECT * FROM t1;
1247 a b
1248 4294967293 2
1249 5 non-generated number
1250 DROP TABLE t1;
1251 # Single row insert signed bigint
1252 CREATE TABLE t1
1253 (a bigint not null auto_increment primary key, b varchar(64))
1254 ENGINE = 'InnoDB'
1255 PARTITION BY HASH (a) PARTITIONS 3;
1256 INSERT INTO t1 VALUES (0x7FFFFFFFFFFFFFFD, "2");
1257 INSERT INTO t1 VALUES (NULL, "1");
1258 INSERT INTO t1 VALUES (NULL, "0");
1259 INSERT INTO t1 VALUES (NULL, "Overflow");
1260 ERROR 22003: Out of range value for column 'a' at row 1
1261 INSERT INTO t1 VALUES (5, "non-generated number");
1262 FLUSH TABLES;
1263 SHOW CREATE TABLE t1;
1264 Table Create Table
1265 t1 CREATE TABLE `t1` (
1266 `a` bigint NOT NULL AUTO_INCREMENT,
1267 `b` varchar(64) DEFAULT NULL,
1268 PRIMARY KEY (`a`)
1269 ) ENGINE=InnoDB AUTO_INCREMENT=9223372036854775808 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1270 /*!50100 PARTITION BY HASH (`a`)
1271 PARTITIONS 3 */
1272 SELECT * FROM t1;
1273 a b
1274 5 non-generated number
1275 9223372036854775805 2
1276 9223372036854775806 1
1277 9223372036854775807 0
1278 DROP TABLE t1;
1279 CREATE TABLE `t1` (
1280 `a` bigint(20) NOT NULL AUTO_INCREMENT,
1281 `b` varchar(64) DEFAULT NULL,
1282 PRIMARY KEY (`a`)
1283 ) ENGINE='InnoDB' AUTO_INCREMENT=9223372036854775808 DEFAULT CHARSET=latin1
1284 PARTITION BY HASH (a)
1285 PARTITIONS 3;
1286 Warnings:
1287 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1288 INSERT INTO t1 VALUES (NULL, "Overflow");
1289 # mysql_errno: 1264
1290 DROP TABLE t1;
1291 # Multi row insert signed bigint
1292 CREATE TABLE t1
1293 (a bigint not null auto_increment primary key, b varchar(64))
1294 ENGINE = 'InnoDB'
1295 PARTITION BY HASH (a) PARTITIONS 3;
1296 INSERT INTO t1 VALUES (0x7FFFFFFFFFFFFFFD, "2");
1297 INSERT INTO t1 VALUES (NULL, "1"), (NULL, "0"), (NULL, "Overflow");
1298 ERROR HY000: Failed to read auto-increment value from storage engine
1299 INSERT INTO t1 VALUES (NULL, "1");
1300 # mysql_errno: 1264
1301 INSERT INTO t1 VALUES (NULL, "0");
1302 # mysql_errno: 1264
1303 INSERT INTO t1 VALUES (NULL, "Overflow");
1304 ERROR 22003: Out of range value for column 'a' at row 1
1305 INSERT INTO t1 VALUES (5, "non-generated number");
1306 FLUSH TABLES;
1307 SHOW CREATE TABLE t1;
1308 Table Create Table
1309 t1 CREATE TABLE `t1` (
1310 `a` bigint NOT NULL AUTO_INCREMENT,
1311 `b` varchar(64) DEFAULT NULL,
1312 PRIMARY KEY (`a`)
1313 ) ENGINE=InnoDB AUTO_INCREMENT=9223372036854775808 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1314 /*!50100 PARTITION BY HASH (`a`)
1315 PARTITIONS 3 */
1316 SELECT * FROM t1;
1317 a b
1318 5 non-generated number
1319 9223372036854775805 2
1320 DROP TABLE t1;
1321 # Single row insert unsigned bigint
1322 CREATE TABLE t1
1323 (a bigint unsigned not null auto_increment primary key, b varchar(64))
1324 ENGINE = 'InnoDB'
1325 PARTITION BY HASH (a) PARTITIONS 3;
1326 INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFD, "2");
1327 INSERT INTO t1 VALUES (NULL, "1");
1328 INSERT INTO t1 VALUES (NULL, "0");
1329 ERROR HY000: Failed to read auto-increment value from storage engine
1330 INSERT INTO t1 VALUES (NULL, "Overflow");
1331 ERROR HY000: Failed to read auto-increment value from storage engine
1332 INSERT INTO t1 VALUES (5, "non-generated number");
1333 FLUSH TABLES;
1334 SHOW CREATE TABLE t1;
1335 Table Create Table
1336 t1 CREATE TABLE `t1` (
1337 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1338 `b` varchar(64) DEFAULT NULL,
1339 PRIMARY KEY (`a`)
1340 ) ENGINE=InnoDB AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1341 /*!50100 PARTITION BY HASH (`a`)
1342 PARTITIONS 3 */
1343 SELECT * FROM t1;
1344 a b
1345 18446744073709551613 2
1346 18446744073709551614 1
1347 5 non-generated number
1348 DROP TABLE t1;
1349 CREATE TABLE `t1` (
1350 `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
1351 `b` varchar(64) DEFAULT NULL,
1352 PRIMARY KEY (`a`)
1353 ) ENGINE='InnoDB' AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=latin1
1354 PARTITION BY HASH (a)
1355 PARTITIONS 3;
1356 Warnings:
1357 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1358 INSERT INTO t1 VALUES (NULL, "Overflow");
1359 # mysql_errno: 1467
1360 DROP TABLE t1;
1361 # Multi row insert unsigned bigint
1362 CREATE TABLE t1
1363 (a bigint unsigned not null auto_increment primary key, b varchar(64))
1364 ENGINE = 'InnoDB'
1365 PARTITION BY HASH (a) PARTITIONS 3;
1366 INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFD, "2");
1367 INSERT INTO t1 VALUES (NULL, "1"), (NULL, "0"), (NULL, "Overflow");
1368 ERROR HY000: Failed to read auto-increment value from storage engine
1369 INSERT INTO t1 VALUES (NULL, "1");
1370 # mysql_errno: 1467
1371 INSERT INTO t1 VALUES (NULL, "0");
1372 # mysql_errno: 1467
1373 INSERT INTO t1 VALUES (NULL, "Overflow");
1374 Got one of the listed errors
1375 # mysql_errno: 1467
1376 INSERT INTO t1 VALUES (5, "non-generated number");
1377 FLUSH TABLES;
1378 SHOW CREATE TABLE t1;
1379 Table Create Table
1380 t1 CREATE TABLE `t1` (
1381 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1382 `b` varchar(64) DEFAULT NULL,
1383 PRIMARY KEY (`a`)
1384 ) ENGINE=InnoDB AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1385 /*!50100 PARTITION BY HASH (`a`)
1386 PARTITIONS 3 */
1387 SELECT * FROM t1;
1388 a b
1389 18446744073709551613 2
1390 5 non-generated number
1391 DROP TABLE t1;
1392 SET SESSION information_schema_stats_expiry=default;
1393 # test auto increment with export import
1394 SET SESSION information_schema_stats_expiry=0;
1395 CREATE TABLE t1 (
1396 col_1_int int AUTO_INCREMENT,
1397 col_2_varchar VARCHAR (20),
1398 primary key (col_1_int))
1399 ENGINE = 'InnoDB'
1400 PARTITION BY HASH (col_1_int)
1401 PARTITIONS 3;
1402 SHOW CREATE TABLE t1;
1403 Table Create Table
1404 t1 CREATE TABLE `t1` (
1405 `col_1_int` int NOT NULL AUTO_INCREMENT,
1406 `col_2_varchar` varchar(20) DEFAULT NULL,
1407 PRIMARY KEY (`col_1_int`)
1408 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1409 /*!50100 PARTITION BY HASH (`col_1_int`)
1410 PARTITIONS 3 */
1411 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE
1412 TABLE_SCHEMA='test' AND TABLE_NAME='t1';
1413 AUTO_INCREMENT
1414 1
1415 # Insert values in all partitions
1416 INSERT INTO t1 VALUES (1,'a1'),(2,'a2'),(3,'a3'),(4,'a4'),(5,'a5');
1417 SELECT * FROM t1 ORDER BY col_1_int;
1418 col_1_int col_2_varchar
1419 1 a1
1420 2 a2
1421 3 a3
1422 4 a4
1423 5 a5
1424 # Export table and save the data [auto_inc = 6]
1425 SHOW CREATE TABLE t1;
1426 Table Create Table
1427 t1 CREATE TABLE `t1` (
1428 `col_1_int` int NOT NULL AUTO_INCREMENT,
1429 `col_2_varchar` varchar(20) DEFAULT NULL,
1430 PRIMARY KEY (`col_1_int`)
1431 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1432 /*!50100 PARTITION BY HASH (`col_1_int`)
1433 PARTITIONS 3 */
1434 FLUSH TABLES t1 FOR EXPORT;
1435 UNLOCK TABLES;
1436 INSERT INTO t1 VALUES (5,'a5');
1437 ERROR 23000: Duplicate entry '5' for key 't1.PRIMARY'
1438 # Check auto increment INSERT
1439 INSERT INTO t1(col_2_varchar) VALUES('a6'),('a7'),('a8');
1440 SELECT * FROM t1 ORDER BY col_1_int;
1441 col_1_int col_2_varchar
1442 1 a1
1443 2 a2
1444 3 a3
1445 4 a4
1446 5 a5
1447 6 a6
1448 7 a7
1449 8 a8
1450 # Drop and create the table and import old data
1451 DROP TABLE t1;
1452 CREATE TABLE t1 (
1453 col_1_int int AUTO_INCREMENT,
1454 col_2_varchar VARCHAR (20),
1455 primary key (col_1_int))
1456 ENGINE = 'InnoDB'
1457 PARTITION BY HASH (col_1_int)
1458 PARTITIONS 3;
1459 ALTER TABLE t1 DISCARD TABLESPACE;
1460 # Import old table and saved data [auto_inc = 6]
1461 ALTER TABLE t1 IMPORT TABLESPACE;
1462 SHOW CREATE TABLE t1;
1463 Table Create Table
1464 t1 CREATE TABLE `t1` (
1465 `col_1_int` int NOT NULL AUTO_INCREMENT,
1466 `col_2_varchar` varchar(20) DEFAULT NULL,
1467 PRIMARY KEY (`col_1_int`)
1468 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1469 /*!50100 PARTITION BY HASH (`col_1_int`)
1470 PARTITIONS 3 */
1471 SELECT * FROM t1 ORDER BY col_1_int;
1472 col_1_int col_2_varchar
1473 1 a1
1474 2 a2
1475 3 a3
1476 4 a4
1477 5 a5
1478 INSERT INTO t1 VALUES (5,'a5');
1479 ERROR 23000: Duplicate entry '5' for key 't1.PRIMARY'
1480 # Check auto increment INSERT
1481 INSERT INTO t1(col_2_varchar) VALUES('a6'),('a7'),('a8');
1482 SELECT * FROM t1 ORDER BY col_1_int;
1483 col_1_int col_2_varchar
1484 1 a1
1485 2 a2
1486 3 a3
1487 4 a4
1488 5 a5
1489 6 a6
1490 7 a7
1491 8 a8
1492 DROP TABLE t1;
1493 # test auto increment with export import and missing cfg file
1494 CREATE TABLE t1 (
1495 col_1_int int AUTO_INCREMENT,
1496 col_2_varchar VARCHAR (20),
1497 primary key (col_1_int))
1498 ENGINE = 'InnoDB'
1499 PARTITION BY HASH (col_1_int)
1500 PARTITIONS 3;
1501 SHOW CREATE TABLE t1;
1502 Table Create Table
1503 t1 CREATE TABLE `t1` (
1504 `col_1_int` int NOT NULL AUTO_INCREMENT,
1505 `col_2_varchar` varchar(20) DEFAULT NULL,
1506 PRIMARY KEY (`col_1_int`)
1507 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1508 /*!50100 PARTITION BY HASH (`col_1_int`)
1509 PARTITIONS 3 */
1510 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE
1511 TABLE_SCHEMA='test' AND TABLE_NAME='t1';
1512 AUTO_INCREMENT
1513 1
1514 # Insert values in all partitions
1515 INSERT INTO t1 VALUES (1,'a1'),(2,'a2'),(3,'a3'),(4,'a4'),(5,'a5');
1516 SELECT * FROM t1 ORDER BY col_1_int;
1517 col_1_int col_2_varchar
1518 1 a1
1519 2 a2
1520 3 a3
1521 4 a4
1522 5 a5
1523 # Export table and save the .ibd files only [auto_inc = 6]
1524 SHOW CREATE TABLE t1;
1525 Table Create Table
1526 t1 CREATE TABLE `t1` (
1527 `col_1_int` int NOT NULL AUTO_INCREMENT,
1528 `col_2_varchar` varchar(20) DEFAULT NULL,
1529 PRIMARY KEY (`col_1_int`)
1530 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1531 /*!50100 PARTITION BY HASH (`col_1_int`)
1532 PARTITIONS 3 */
1533 FLUSH TABLES t1 FOR EXPORT;
1534 UNLOCK TABLES;
1535 INSERT INTO t1 VALUES (5,'a5');
1536 ERROR 23000: Duplicate entry '5' for key 't1.PRIMARY'
1537 # Check auto increment INSERT
1538 INSERT INTO t1(col_2_varchar) VALUES('a6'),('a7'),('a8');
1539 SELECT * FROM t1 ORDER BY col_1_int;
1540 col_1_int col_2_varchar
1541 1 a1
1542 2 a2
1543 3 a3
1544 4 a4
1545 5 a5
1546 6 a6
1547 7 a7
1548 8 a8
1549 # Drop and create the table and import old data
1550 DROP TABLE t1;
1551 CREATE TABLE t1 (
1552 col_1_int int AUTO_INCREMENT,
1553 col_2_varchar VARCHAR (20),
1554 primary key (col_1_int))
1555 ENGINE = 'InnoDB'
1556 PARTITION BY HASH (col_1_int)
1557 PARTITIONS 3;
1558 ALTER TABLE t1 DISCARD TABLESPACE;
1559 # Import old table and saved data from .ibd files [auto_inc = 6]
1560 ALTER TABLE t1 IMPORT TABLESPACE;
1561 Warnings:
1562 Warning 1810 InnoDB: IO Read error: (2, No such file or directory) Error opening './test/t1#P#p0.cfg', will attempt to import without schema verification
1563 Warning 1810 InnoDB: IO Read error: (2, No such file or directory) Error opening './test/t1#P#p1.cfg', will attempt to import without schema verification
1564 Warning 1810 InnoDB: IO Read error: (2, No such file or directory) Error opening './test/t1#P#p2.cfg', will attempt to import without schema verification
1565 SHOW CREATE TABLE t1;
1566 Table Create Table
1567 t1 CREATE TABLE `t1` (
1568 `col_1_int` int NOT NULL AUTO_INCREMENT,
1569 `col_2_varchar` varchar(20) DEFAULT NULL,
1570 PRIMARY KEY (`col_1_int`)
1571 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1572 /*!50100 PARTITION BY HASH (`col_1_int`)
1573 PARTITIONS 3 */
1574 SELECT * FROM t1 ORDER BY col_1_int;
1575 col_1_int col_2_varchar
1576 1 a1
1577 2 a2
1578 3 a3
1579 4 a4
1580 5 a5
1581 INSERT INTO t1 VALUES (5,'a5');
1582 ERROR 23000: Duplicate entry '5' for key 't1.PRIMARY'
1583 # Check auto increment INSERT
1584 INSERT INTO t1(col_2_varchar) VALUES('a6'),('a7'),('a8');
1585 SELECT * FROM t1 ORDER BY col_1_int;
1586 col_1_int col_2_varchar
1587 1 a1
1588 2 a2
1589 3 a3
1590 4 a4
1591 5 a5
1592 6 a6
1593 7 a7
1594 8 a8
1595 DROP TABLE t1;