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