Debian Codegraph @

mysql-test/suite/parts/inc/partition_auto_increment.inc

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