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 --disable_query_log
7 set sql_mode="";
8 --enable_query_log
9
10 -- disable_warnings
11 DROP TABLE IF EXISTS t1;
12 -- enable_warnings
13
14 -- echo # test without partitioning for reference
15 eval CREATE TABLE t1 (
16 c1 INT NOT NULL AUTO_INCREMENT,
17 PRIMARY KEY (c1))
18 ENGINE=$engine;
19 SHOW CREATE TABLE t1;
20 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
21 AND TABLE_NAME='t1';
22 INSERT INTO t1 VALUES (2);
23 INSERT INTO t1 VALUES (4);
24 INSERT INTO t1 VALUES (NULL);
25 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
26 AND TABLE_NAME='t1';
27 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
28 AND TABLE_NAME='t1';
29 INSERT INTO t1 VALUES (0);
30 -- error 0, ER_DUP_KEY, ER_DUP_ENTRY
31 INSERT INTO t1 VALUES (5), (16);
32 if (!$mysql_errno)
33 {
34 echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
35 echo # mysql_errno: $mysql_errno;
36 }
37 INSERT INTO t1 VALUES (17);
38 INSERT INTO t1 VALUES (19), (NULL);
39 -- error 0, ER_DUP_ENTRY, ER_DUP_KEY
40 INSERT INTO t1 VALUES (NULL), (10), (NULL);
41 if ($mysql_errno)
42 {
43 echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
44 }
45 INSERT INTO t1 VALUES (NULL);
46 SET INSERT_ID = 30;
47 INSERT INTO t1 VALUES (NULL);
48 SET INSERT_ID = 29;
49 -- error 0, ER_DUP_ENTRY, ER_DUP_KEY
50 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
51 if (!$mysql_errno)
52 {
53 echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
54 echo # mysql_errno: $mysql_errno;
55 }
56 INSERT INTO t1 VALUES (NULL);
57 if (!$skip_update)
58 {
59 # InnoDB Does not handle this correctly, see bug#14793, bug#21641
60 UPDATE t1 SET c1 = 50 WHERE c1 = 17;
61 UPDATE t1 SET c1 = 51 WHERE c1 = 19;
62 FLUSH TABLES;
63 UPDATE t1 SET c1 = 40 WHERE c1 = 50;
64 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
65 AND TABLE_NAME='t1';
66 UPDATE 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 SHOW CREATE TABLE t1;
91 SELECT * FROM t1 ORDER BY c1;
92 if (!$skip_truncate)
93 {
94 TRUNCATE TABLE t1;
95 }
96 INSERT INTO t1 VALUES (NULL);
97 SHOW CREATE TABLE t1;
98 SELECT * FROM t1 ORDER BY c1;
99 INSERT INTO t1 VALUES (100);
100 INSERT INTO t1 VALUES (NULL);
101 if (!$skip_delete)
102 {
103 DELETE FROM t1 WHERE c1 >= 100;
104 }
105 # InnoDB does reset auto_increment on OPTIMIZE, Bug#18274
106 # Archive does reset auto_increment on OPTIMIZE, Bug#40216
107 OPTIMIZE TABLE t1;
108 SHOW CREATE TABLE t1;
109 DROP TABLE t1;
110
111 if (!$skip_update)
112 {
113 eval CREATE TABLE t1
114 (a INT NULL AUTO_INCREMENT,
115 UNIQUE KEY (a))
116 ENGINE=$engine;
117 SET LAST_INSERT_ID = 999;
118 SET INSERT_ID = 0;
119 INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
120 SELECT LAST_INSERT_ID();
121 SELECT * FROM t1;
122 INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
123 SELECT LAST_INSERT_ID();
124 SELECT * FROM t1;
125 UPDATE t1 SET a = 1 WHERE a IS NULL;
126 SELECT LAST_INSERT_ID();
127 SELECT * FROM t1;
128 UPDATE t1 SET a = NULL WHERE a = 1;
129 SELECT LAST_INSERT_ID();
130 SELECT * FROM t1;
131 DROP TABLE t1;
132 SET INSERT_ID = 1;
133 }
134
135 -- echo # Simple test with NULL
136 eval CREATE TABLE t1 (
137 c1 INT NOT NULL AUTO_INCREMENT,
138 PRIMARY KEY (c1))
139 ENGINE=$engine
140 PARTITION BY HASH(c1)
141 PARTITIONS 2;
142 INSERT INTO t1 VALUES (NULL);
143 SHOW CREATE TABLE t1;
144 SELECT * FROM t1;
145 DROP TABLE t1;
146
147 -- echo # Test with sql_mode and first insert as 0
148 eval CREATE TABLE t1 (
149 c1 INT,
150 c2 INT NOT NULL AUTO_INCREMENT,
151 PRIMARY KEY (c2))
152 ENGINE=$engine
153 PARTITION BY HASH(c2)
154 PARTITIONS 2;
155 INSERT INTO t1 VALUES (1, NULL);
156 -- error 0, ER_DUP_KEY, ER_DUP_ENTRY
157 INSERT INTO t1 VALUES (1, 1), (99, 99);
158 if (!$mysql_errno)
159 {
160 echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
161 echo # mysql_errno: $mysql_errno;
162 }
163 INSERT INTO t1 VALUES (1, NULL);
164 let $old_sql_mode = `select @@session.sql_mode`;
165 SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
166 -- error 0, ER_DUP_ENTRY, ER_DUP_KEY
167 INSERT INTO t1 VALUES (1, 0);
168 if ($mysql_errno)
169 {
170 echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
171 }
172 SELECT * FROM t1 ORDER BY c1, c2;
173 DROP TABLE t1;
174 eval CREATE TABLE t1 (
175 c1 INT,
176 c2 INT NOT NULL AUTO_INCREMENT,
177 PRIMARY KEY (c2))
178 ENGINE=$engine
179 PARTITION BY HASH(c2)
180 PARTITIONS 2;
181 -- error 0, ER_DUP_ENTRY, ER_DUP_KEY
182 INSERT INTO t1 VALUES (1, 0);
183 if ($mysql_errno)
184 {
185 echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
186 }
187 INSERT INTO t1 VALUES (1, 1), (1, NULL);
188 INSERT INTO t1 VALUES (2, NULL), (4, 7);
189 INSERT INTO t1 VALUES (1, NULL);
190 SELECT * FROM t1 ORDER BY c1, c2;
191 eval SET @@session.sql_mode = '$old_sql_mode';
192 DROP TABLE t1;
193
194
195 -- echo # Simple test with NULL, 0 and explicit values both incr. and desc.
196 eval CREATE TABLE t1 (
197 c1 INT NOT NULL AUTO_INCREMENT,
198 PRIMARY KEY (c1))
199 ENGINE=$engine
200 PARTITION BY HASH(c1)
201 PARTITIONS 2;
202 INSERT INTO t1 VALUES (2), (4), (NULL);
203 INSERT INTO t1 VALUES (0);
204 -- error 0, ER_DUP_KEY, ER_DUP_ENTRY
205 INSERT INTO t1 VALUES (5), (16);
206 if (!$mysql_errno)
207 {
208 echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
209 echo # mysql_errno: $mysql_errno;
210 }
211 INSERT INTO t1 VALUES (17), (19), (NULL);
212 -- error 0, ER_DUP_ENTRY, ER_DUP_KEY
213 INSERT INTO t1 VALUES (NULL), (10), (NULL);
214 if ($mysql_errno)
215 {
216 echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
217 }
218 -- error 0, ER_DUP_ENTRY, ER_DUP_KEY
219 INSERT INTO t1 VALUES (NULL), (9);
220 if ($mysql_errno)
221 {
222 echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
223 }
224 -- error 0, ER_DUP_ENTRY, ER_DUP_KEY
225 INSERT INTO t1 VALUES (59), (55);
226 if ($mysql_errno)
227 {
228 echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
229 }
230 INSERT INTO t1 VALUES (NULL), (90);
231 INSERT INTO t1 VALUES (NULL);
232 if (!$skip_update)
233 {
234 UPDATE t1 SET c1 = 150 WHERE c1 = 17;
235 UPDATE t1 SET c1 = 151 WHERE c1 = 19;
236 FLUSH TABLES;
237 UPDATE t1 SET c1 = 140 WHERE c1 = 150;
238 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
239 AND TABLE_NAME='t1';
240 UPDATE t1 SET c1 = NULL WHERE c1 = 4;
241 INSERT INTO t1 VALUES (NULL);
242 INSERT INTO t1 VALUES (NULL);
243 }
244 SELECT * FROM t1 ORDER BY c1;
245 DROP TABLE t1;
246
247 -- echo # Test with auto_increment_increment and auto_increment_offset.
248 eval CREATE TABLE t1 (
249 c1 INT NOT NULL AUTO_INCREMENT,
250 PRIMARY KEY (c1))
251 ENGINE=$engine
252 PARTITION BY HASH(c1)
253 PARTITIONS 2;
254 let $old_increment = `SELECT @@session.auto_increment_increment`;
255 let $old_offset = `SELECT @@session.auto_increment_offset`;
256 SET @@session.auto_increment_increment = 10;
257 SET @@session.auto_increment_offset = 5;
258 INSERT INTO t1 VALUES (1);
259 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
260 SET @@session.auto_increment_increment = 5;
261 SET @@session.auto_increment_offset = 3;
262 INSERT INTO t1 VALUES (NULL);
263 let $new_val = `SELECT LAST_INSERT_ID()`;
264 eval INSERT INTO t1 VALUES ($new_val + 1);
265 INSERT INTO t1 VALUES (NULL);
266 let $new_val = `SELECT LAST_INSERT_ID()`;
267 eval INSERT INTO t1 VALUES ($new_val + 2);
268 INSERT INTO t1 VALUES (NULL);
269 let $new_val = `SELECT LAST_INSERT_ID()`;
270 eval INSERT INTO t1 VALUES ($new_val + 3);
271 INSERT INTO t1 VALUES (NULL);
272 let $new_val = `SELECT LAST_INSERT_ID()`;
273 eval INSERT INTO t1 VALUES ($new_val + 4);
274 INSERT INTO t1 VALUES (NULL);
275 let $new_val = `SELECT LAST_INSERT_ID()`;
276 eval INSERT INTO t1 VALUES ($new_val + 5);
277 INSERT INTO t1 VALUES (NULL);
278 let $new_val = `SELECT LAST_INSERT_ID()`;
279 eval INSERT INTO t1 VALUES ($new_val + 6);
280 INSERT INTO t1 VALUES (NULL);
281 eval SET @@session.auto_increment_increment = $old_increment;
282 eval SET @@session.auto_increment_offset = $old_offset;
283 SELECT * FROM t1 ORDER BY c1;
284 DROP TABLE t1;
285
286
287 -- echo # Test reported auto_increment value
288 eval CREATE TABLE t1 (
289 c1 INT NOT NULL AUTO_INCREMENT,
290 PRIMARY KEY (c1))
291 ENGINE=$engine
292 PARTITION BY HASH (c1)
293 PARTITIONS 2;
294 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
295 AND TABLE_NAME='t1';
296 INSERT INTO t1 VALUES (2);
297 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
298 AND TABLE_NAME='t1';
299 INSERT INTO t1 VALUES (4);
300 INSERT INTO t1 VALUES (NULL);
301 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
302 AND TABLE_NAME='t1';
303 INSERT INTO t1 VALUES (NULL);
304 INSERT INTO t1 VALUES (17);
305 INSERT INTO t1 VALUES (19);
306 INSERT INTO t1 VALUES (NULL);
307 INSERT INTO t1 VALUES (NULL);
308 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
309 AND TABLE_NAME='t1';
310 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
311 AND TABLE_NAME='t1';
312 -- error 0, ER_DUP_ENTRY, ER_DUP_KEY
313 INSERT INTO t1 VALUES (10);
314 if ($mysql_errno)
315 {
316 echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
317 }
318 SELECT * FROM t1 ORDER BY c1;
319 INSERT INTO t1 VALUES (NULL);
320 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
321 AND TABLE_NAME='t1';
322 INSERT INTO t1 VALUES (NULL);
323 -- error 0, ER_DUP_ENTRY, ER_DUP_KEY
324 INSERT INTO t1 VALUES (15);
325 if ($mysql_errno)
326 {
327 echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
328 }
329 INSERT INTO t1 VALUES (NULL);
330 SELECT * FROM t1 ORDER BY c1;
331 INSERT INTO t1 VALUES (NULL);
332 if (!$skip_delete)
333 {
334 DELETE FROM t1;
335 }
336 INSERT INTO t1 VALUES (NULL);
337 SHOW CREATE TABLE t1;
338 SELECT * FROM t1 ORDER BY c1;
339 if (!$skip_truncate)
340 {
341 TRUNCATE TABLE t1;
342 }
343 INSERT INTO t1 VALUES (NULL);
344 SHOW CREATE TABLE t1;
345 SELECT * FROM t1 ORDER BY c1;
346 INSERT INTO t1 VALUES (100);
347 INSERT INTO t1 VALUES (NULL);
348 if (!$skip_delete)
349 {
350 DELETE FROM t1 WHERE c1 >= 100;
351 }
352 # InnoDB does reset auto_increment on OPTIMIZE, Bug#18274
353 OPTIMIZE TABLE t1;
354 SHOW CREATE TABLE t1;
355 DROP TABLE t1;
356
357 -- echo # Test with two threads
358 connection default;
359 eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
360 ENGINE = $engine
361 PARTITION BY HASH(c1)
362 PARTITIONS 2;
363 INSERT INTO t1 (c1) VALUES (2);
364 INSERT INTO t1 (c1) VALUES (4);
365 connect(con1, localhost, root,,);
366 connection con1;
367 INSERT INTO t1 (c1) VALUES (NULL);
368 INSERT INTO t1 (c1) VALUES (10);
369 connection default;
370 INSERT INTO t1 (c1) VALUES (NULL);
371 INSERT INTO t1 (c1) VALUES (NULL);
372 INSERT INTO t1 (c1) VALUES (19);
373 INSERT INTO t1 (c1) VALUES (21);
374 connection con1;
375 INSERT INTO t1 (c1) VALUES (NULL);
376 connection default;
377 -- error 0, ER_DUP_ENTRY, ER_DUP_KEY
378 INSERT INTO t1 (c1) VALUES (16);
379 if ($mysql_errno)
380 {
381 echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
382 }
383 connection con1;
384 INSERT INTO t1 (c1) VALUES (NULL);
385 disconnect con1;
386 connection default;
387 INSERT INTO t1 (c1) VALUES (NULL);
388 SELECT * FROM t1 ORDER BY c1;
389 DROP TABLE t1;
390
391 -- echo # Test with two threads + start transaction NO PARTITIONING
392 connect(con1, localhost, root,,);
393 connection default;
394 eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
395 ENGINE = $engine;
396 if ($engine == "'InnoDB'")
397 {
398 # MDEV-515 takes X-lock on the table for the first insert.
399 # So concurrent insert won't happen on the table
400 INSERT INTO t1(c1) VALUES(100);
401 }
402 START TRANSACTION;
403 INSERT INTO t1 (c1) VALUES (2);
404 INSERT INTO t1 (c1) VALUES (4);
405 connection con1;
406 START TRANSACTION;
407 INSERT INTO t1 (c1) VALUES (NULL);
408 INSERT INTO t1 (c1) VALUES (10);
409 connection default;
410 INSERT INTO t1 (c1) VALUES (NULL);
411 INSERT INTO t1 (c1) VALUES (NULL);
412 INSERT INTO t1 (c1) VALUES (19);
413 INSERT INTO t1 (c1) VALUES (21);
414 connection con1;
415 INSERT INTO t1 (c1) VALUES (NULL);
416 connection default;
417 -- error 0, ER_DUP_KEY
418 INSERT INTO t1 (c1) VALUES (16);
419 if ($mysql_errno)
420 {
421 echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
422 }
423 connection con1;
424 INSERT INTO t1 (c1) VALUES (NULL);
425 SELECT * FROM t1 ORDER BY c1;
426 COMMIT;
427 SELECT * FROM t1 ORDER BY c1;
428 disconnect con1;
429 connection default;
430 INSERT INTO t1 (c1) VALUES (NULL);
431 SELECT * FROM t1 ORDER BY c1;
432 COMMIT;
433 SELECT * FROM t1 ORDER BY c1;
434 DROP TABLE t1;
435
436 -- echo # Test with two threads + start transaction
437 connect(con1, localhost, root,,);
438 connection default;
439 eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
440 ENGINE = $engine
441 PARTITION BY HASH(c1)
442 PARTITIONS 2;
443 IF ($engine == "'InnoDB'")
444 {
445 # MDEV-515 takes X-lock on the table for the first insert.
446 # So concurrent insert won't happen on the table
447 INSERT INTO t1 (c1) VALUES (100);
448 INSERT INTO t1 (c1) VALUES (101);
449 }
450 START TRANSACTION;
451 INSERT INTO t1 (c1) VALUES (2);
452 INSERT INTO t1 (c1) VALUES (4);
453 connection con1;
454 START TRANSACTION;
455 INSERT INTO t1 (c1) VALUES (NULL), (10);
456 connection default;
457 INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19);
458 INSERT INTO t1 (c1) VALUES (21);
459 connection con1;
460 INSERT INTO t1 (c1) VALUES (NULL);
461 connection default;
462 -- error 0, ER_DUP_ENTRY, ER_DUP_KEY
463 INSERT INTO t1 (c1) VALUES (16);
464 if ($mysql_errno)
465 {
466 echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
467 }
468 connection con1;
469 INSERT INTO t1 (c1) VALUES (NULL);
470 SELECT * FROM t1 ORDER BY c1;
471 COMMIT;
472 SELECT * FROM t1 ORDER BY c1;
473 disconnect con1;
474 connection default;
475 INSERT INTO t1 (c1) VALUES (NULL);
476 SELECT * FROM t1 ORDER BY c1;
477 COMMIT;
478 SELECT * FROM t1 ORDER BY c1;
479 DROP TABLE t1;
480
481 if (!$only_ai_pk)
482 {
483 -- echo # Test with another column after
484 eval CREATE TABLE t1 (
485 c1 INT NOT NULL AUTO_INCREMENT,
486 c2 INT,
487 PRIMARY KEY (c1,c2))
488 ENGINE = $engine
489 PARTITION BY HASH(c2)
490 PARTITIONS 2;
491 INSERT INTO t1 VALUES (1, 0);
492 INSERT INTO t1 VALUES (1, 1);
493 INSERT INTO t1 VALUES (NULL, 1), (NULL, 2), (NULL, 3);
494 INSERT INTO t1 VALUES (NULL, 3);
495 INSERT INTO t1 VALUES (2, 0), (NULL, 2);
496 INSERT INTO t1 VALUES (2, 2);
497 INSERT INTO t1 VALUES (2, 22);
498 INSERT INTO t1 VALUES (NULL, 2);
499 SELECT * FROM t1 ORDER BY c1,c2;
500 DROP TABLE t1;
501 }
502
503 -- echo # Test with another column before
504 eval CREATE TABLE t1 (
505 c1 INT,
506 c2 INT NOT NULL AUTO_INCREMENT,
507 PRIMARY KEY (c2))
508 ENGINE = $engine
509 PARTITION BY HASH(c2)
510 PARTITIONS 2;
511 INSERT INTO t1 VALUES (1, 0);
512 -- error 0, ER_DUP_KEY, ER_DUP_ENTRY
513 INSERT INTO t1 VALUES (1, 1);
514 if (!$mysql_errno)
515 {
516 echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
517 echo # mysql_errno: $mysql_errno;
518 }
519 INSERT INTO t1 VALUES (1, NULL);
520 INSERT INTO t1 VALUES (2, NULL), (3, 11), (3, NULL), (2, 0);
521 INSERT INTO t1 VALUES (2, NULL);
522 -- error 0, ER_DUP_KEY, ER_DUP_ENTRY
523 INSERT INTO t1 VALUES (2, 2);
524 if (!$mysql_errno)
525 {
526 echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
527 echo # mysql_errno: $mysql_errno;
528 }
529 INSERT INTO t1 VALUES (2, 22);
530 INSERT INTO t1 VALUES (2, NULL);
531 SELECT * FROM t1 ORDER BY c1,c2;
532 DROP TABLE t1;
533
534 -- echo # Test with auto_increment on secondary column in multi-column-index
535 -- disable_abort_on_error
536 eval CREATE TABLE t1 (
537 c1 INT,
538 c2 INT NOT NULL AUTO_INCREMENT,
539 PRIMARY KEY (c1,c2))
540 ENGINE = $engine
541 PARTITION BY HASH(c2)
542 PARTITIONS 2;
543 -- enable_abort_on_error
544 -- disable_query_log
545 eval SET @my_errno= $mysql_errno ;
546 let $run = `SELECT @my_errno = 0`;
547 # ER_WRONG_AUTO_KEY is 1075
548 let $ER_WRONG_AUTO_KEY= 1075;
549 if (`SELECT @my_errno NOT IN (0,$ER_WRONG_AUTO_KEY)`)
550 {
551 -- echo # Unknown error code, exits
552 exit;
553 }
554 -- enable_query_log
555 if ($run)
556 {
557 INSERT INTO t1 VALUES (1, 0);
558 -- error 0, ER_DUP_KEY, ER_DUP_ENTRY
559 INSERT INTO t1 VALUES (1, 1);
560 if (!$mysql_errno)
561 {
562 echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
563 echo # mysql_errno: $mysql_errno;
564 }
565 INSERT INTO t1 VALUES (1, NULL);
566 INSERT INTO t1 VALUES (2, NULL);
567 INSERT INTO t1 VALUES (3, NULL);
568 INSERT INTO t1 VALUES (3, NULL), (2, 0), (2, NULL);
569 -- error 0, ER_DUP_ENTRY, ER_DUP_KEY
570 INSERT INTO t1 VALUES (2, 2);
571 if (!$mysql_errno)
572 {
573 echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
574 echo # mysql_errno: $mysql_errno;
575 }
576 INSERT INTO t1 VALUES (2, 22), (2, NULL);
577 SELECT * FROM t1 ORDER BY c1,c2;
578 DROP TABLE t1;
579 }
580
581 -- echo # Test AUTO_INCREMENT in CREATE
582 eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
583 ENGINE = $engine
584 AUTO_INCREMENT = 15
585 PARTITION BY HASH(c1)
586 PARTITIONS 2;
587 SHOW CREATE TABLE t1;
588 -- error 0, ER_DUP_ENTRY, ER_DUP_KEY
589 INSERT INTO t1 (c1) VALUES (4);
590 if ($mysql_errno)
591 {
592 echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
593 }
594 SHOW CREATE TABLE t1;
595 INSERT INTO t1 (c1) VALUES (0);
596 SHOW CREATE TABLE t1;
597 INSERT INTO t1 (c1) VALUES (NULL);
598 SHOW CREATE TABLE t1;
599 SELECT * FROM t1 ORDER BY c1;
600
601 -- echo # Test sql_mode 'NO_AUTO_VALUE_ON_ZERO'
602 let $old_sql_mode = `select @@session.sql_mode`;
603 SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
604 INSERT INTO t1 (c1) VALUES (300);
605 SHOW CREATE TABLE t1;
606 -- error 0, ER_DUP_ENTRY, ER_DUP_KEY
607 INSERT INTO t1 (c1) VALUES (0);
608 if ($mysql_errno)
609 {
610 echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
611 }
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 eval SET @@session.sql_mode = '$old_sql_mode';
617 DROP TABLE t1;
618
619 -- echo # Test SET INSERT_ID
620 eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
621 ENGINE = $engine
622 PARTITION BY HASH(c1)
623 PARTITIONS 2;
624 SHOW CREATE TABLE t1;
625 INSERT INTO t1 (c1) VALUES (NULL);
626 SHOW CREATE TABLE t1;
627 SELECT * FROM t1;
628 SET INSERT_ID = 23;
629 SHOW CREATE TABLE t1;
630 INSERT INTO t1 (c1) VALUES (NULL);
631 SHOW CREATE TABLE t1;
632 SET INSERT_ID = 22;
633 -- error 0, ER_DUP_ENTRY, ER_DUP_KEY
634 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
635 if (!$mysql_errno)
636 {
637 echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
638 echo # mysql_errno: $mysql_errno;
639 }
640 INSERT INTO t1 VALUES (NULL);
641 SELECT * FROM t1 ORDER BY c1;
642 DROP TABLE t1;
643
644 -- echo # Testing with FLUSH TABLE
645 eval CREATE TABLE t1 (
646 c1 INT NOT NULL AUTO_INCREMENT,
647 PRIMARY KEY (c1))
648 ENGINE=$engine
649 PARTITION BY HASH(c1)
650 PARTITIONS 2;
651 SHOW CREATE TABLE t1;
652 FLUSH TABLE;
653 SHOW CREATE TABLE t1;
654 INSERT INTO t1 VALUES (4);
655 FLUSH TABLE;
656 SHOW CREATE TABLE t1;
657 INSERT INTO t1 VALUES (NULL);
658 FLUSH TABLE;
659 SHOW CREATE TABLE t1;
660 SELECT * FROM t1 ORDER BY c1;
661 DROP TABLE t1;
662
663 if (!$skip_negative_auto_inc)
664 {
665 --echo #############################################################################
666 --echo # Bug #45823 - Assertion failure in file row/row0mysql.c line 1386
667 --echo # Bug #43988 - AUTO_INCREMENT errors with partitioned InnoDB tables in 5.1.31
668 --echo ##############################################################################
669
670 --echo # Inserting negative autoincrement values into a partition table (partitions >= 4)
671
672 eval CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
673 c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 4;
674
675 INSERT INTO t(c2) VALUES (10);
676 INSERT INTO t(c2) VALUES (20);
677 INSERT INTO t VALUES (-1,-10);
678 INSERT INTO t(c2) VALUES (30);
679 INSERT INTO t(c2) VALUES (40);
680
681 SELECT * FROM t ORDER BY c1 ASC;
682
683 DROP TABLE t;
684
685 --echo # Reading from a partition table (partitions >= 2 ) after inserting a negative
686 --echo # value into the auto increment column
687
688
689 eval CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
690 c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 2;
691
692 INSERT INTO t VALUES (-2,-20);
693 INSERT INTO t(c2) VALUES (30);
694
695 SELECT * FROM t ORDER BY c1 ASC;
696
697 DROP TABLE t;
698
699 --echo # Inserting negative auto increment value into a partition table (partitions >= 2)
700 --echo # auto increment value > 2.
701
702 eval CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
703 c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 2;
704
705 INSERT INTO t VALUES (-4,-20);
706 INSERT INTO t(c2) VALUES (30);
707 INSERT INTO t(c2) VALUES (40);
708
709 SELECT * FROM t ORDER BY c1 ASC;
710
711 DROP TABLE t;
712
713 --echo # Inserting -1 into autoincrement column of a partition table (partition >= 4)
714
715 eval CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
716 c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 4;
717
718 INSERT INTO t(c2) VALUES (10);
719 INSERT INTO t(c2) VALUES (20);
720 INSERT INTO t VALUES (-1,-10);
721
722 SELECT * FROM t ORDER BY c1 ASC;
723
724 INSERT INTO t(c2) VALUES (30);
725
726 SELECT * FROM t ORDER BY c1 ASC;
727
728 DROP TABLE t;
729
730 --echo # Deleting from an auto increment table after inserting negative values
731
732 eval CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
733 c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 4;
734
735 INSERT INTO t(c2) VALUES (10);
736 INSERT INTO t(c2) VALUES (20);
737 INSERT INTO t VALUES (-1,-10);
738 INSERT INTO t(c2) VALUES (30);
739 INSERT INTO t VALUES (-3,-20);
740 INSERT INTO t(c2) VALUES (40);
741
742 SELECT * FROM t ORDER BY c1 ASC;
743
744 if (!$skip_delete)
745 {
746 DELETE FROM t WHERE c1 > 1;
747 }
748
749 SELECT * FROM t ORDER BY c1 ASC;
750
751 DROP TABLE t;
752
753 --echo # Inserting a positive value that exceeds maximum allowed value for an
754 --echo # Auto Increment column (positive maximum)
755
756 eval CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
757 c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 4;
758
759 INSERT INTO t(c2) VALUES (10);
760 INSERT INTO t(c2) VALUES (20);
761 INSERT INTO t VALUES (126,30);
762 INSERT INTO t VALUES (127,40);
763
764 --error ER_DUP_ENTRY
765 INSERT INTO t VALUES (128,50);
766 --error ER_DUP_ENTRY
767 INSERT INTO t VALUES (129,60);
768
769 SELECT * FROM t ORDER BY c1 ASC;
770
771 DROP TABLE t;
772
773 --echo # Inserting a negative value that goes below minimum allowed value for an
774 --echo # Auto Increment column (negative minimum)
775
776 eval CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
777 c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 4;
778
779 INSERT INTO t(c2) VALUES (10);
780 INSERT INTO t(c2) VALUES (20);
781 INSERT INTO t VALUES (-127,30);
782 INSERT INTO t VALUES (-128,40);
783
784 --error ER_DUP_ENTRY
785 INSERT INTO t VALUES (-129,50);
786 --error ER_DUP_ENTRY
787 INSERT INTO t VALUES (-130,60);
788
789 SELECT * FROM t ORDER BY c1 ASC;
790
791 DROP TABLE t;
792
793 --echo # Updating the partition table with a negative Auto Increment value
794
795 eval CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
796 c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 4;
797
798 INSERT INTO t(c2) VALUES (10);
799 INSERT INTO t(c2) VALUES (20);
800 INSERT INTO t VALUES (-1,-10);
801 INSERT INTO t(c2) VALUES (30);
802
803 SELECT * FROM t ORDER BY c1 ASC;
804
805 if (!$skip_update)
806 {
807 UPDATE t SET c1 = -6 WHERE c1 = 2;
808 }
809
810 SELECT * FROM t ORDER BY c1 ASC;
811
812 INSERT INTO t(c2) VALUES (40);
813 INSERT INTO t(c2) VALUES (50);
814
815 if (!$skip_update)
816 {
817 UPDATE t SET c1 = -6 WHERE c1 = 2;
818 }
819
820 SELECT * FROM t ORDER BY c1 ASC;
821
822 DROP TABLE t;
823
824 --echo # Updating the partition table with a value that crosses the upper limits
825 --echo # on both the positive and the negative side.
826
827 eval CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
828 c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 4;
829
830 INSERT INTO t(c2) VALUES (10);
831 INSERT INTO t(c2) VALUES (20);
832 INSERT INTO t VALUES (126,30);
833 INSERT INTO t VALUES (127,40);
834
835 SELECT * FROM t ORDER BY c1 ASC;
836
837 if (!$skip_update)
838 {
839 UPDATE t SET c1 = 130 where c1 = 127;
840 }
841
842 SELECT * FROM t ORDER BY c1 ASC;
843
844 if (!$skip_update)
845 {
846 UPDATE t SET c1 = -140 where c1 = 126;
847 }
848
849 SELECT * FROM t ORDER BY c1 ASC;
850
851 DROP TABLE t;
852
853 if (!$skip_update)
854 {
855 eval CREATE TABLE t1
856 (a INT NULL AUTO_INCREMENT,
857 UNIQUE KEY (a))
858 ENGINE=$engine
859 PARTITION BY KEY(a) PARTITIONS 2;
860 SET LAST_INSERT_ID = 999;
861 SET INSERT_ID = 0;
862 INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
863 SELECT LAST_INSERT_ID();
864 SELECT * FROM t1;
865 INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
866 SELECT LAST_INSERT_ID();
867 SELECT * FROM t1;
868 UPDATE t1 SET a = 1 WHERE a IS NULL;
869 SELECT LAST_INSERT_ID();
870 SELECT * FROM t1;
871 UPDATE t1 SET a = NULL WHERE a = 1;
872 SELECT LAST_INSERT_ID();
873 SELECT * FROM t1;
874 DROP TABLE t1;
875 }
876 --echo ##############################################################################
877 }
878
879 if (!$skip_update)
880 {
881 --echo #
882 --echo # MDEV-19622 Assertion failures in
883 --echo # ha_partition::set_auto_increment_if_higher upon UPDATE on Aria table
884 --echo #
885 eval CREATE OR REPLACE TABLE t1 (pk INT AUTO_INCREMENT, a INT, KEY(pk)) ENGINE=$engine PARTITION BY HASH(a);
886 INSERT INTO t1 VALUES (1,1),(2,2);
887 UPDATE t1 SET pk = 0;
888 DROP TABLE t1;
889
890 --echo #
891 --echo # MDEV-29636 Assertion `part_share->auto_inc_initialized || !can_use_for_auto_inc_init()'
892 --echo # failed in ha_partition::set_auto_increment_if_higher upon REPLACE
893 --echo # with partition pruning
894 --echo #
895 eval CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY) ENGINE=$engine
896 PARTITION BY RANGE (a) (
897 PARTITION p0 VALUES LESS THAN (10),
898 PARTITION pn VALUES LESS THAN MAXVALUE
899 );
900 REPLACE INTO t1 PARTITION (p0) SELECT 1;
901 DROP TABLE t1;
902 }
903
904 if (!$skip_delete)
905 {
906 --echo #
907 --echo # MDEV-21027 Assertion `part_share->auto_inc_initialized || !can_use_for_auto_inc_init()'
908 --echo # ha_partition::set_auto_increment_if_higher
909 --echo #
910 eval CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY) ENGINE=$engine PARTITION BY HASH (a) PARTITIONS 3;
911 REPLACE INTO t1 PARTITION (p0) VALUES (3);
912 DROP TABLE t1;
913 }
914
915 if (!$skip_truncate)
916 {
917 --echo #
918 --echo # MDEV-21310 AUTO_INCREMENT column throws range error on INSERT in partitioned table |
919 --echo # Assertion `part_share->auto_inc_initialized || !can_use_for_auto_inc_init()' failed.
920 --echo #
921 eval CREATE TABLE t1 (c INT AUTO_INCREMENT KEY) ENGINE=$engine PARTITION BY LIST (c) (PARTITION p1 VALUES IN (1), PARTITION p2 VALUES IN (2));
922 ALTER TABLE t1 TRUNCATE PARTITION p1;
923 INSERT INTO t1 PARTITION (p1) (c) SELECT 1;
924 DROP TABLE t1;
925 }