Bug #43526 Issue with auto-increment value
Submitted: 10 Mar 2009 4:49 Modified: 10 Mar 2009 6:39
Reporter: Nidhi Shrotriya Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:6.0.11 OS:Any
Assigned to: CPU Architecture:Any

[10 Mar 2009 4:49] Nidhi Shrotriya 
Description:
There is discrepancy between results for MyISAM/Maria with those of Innodb when auto-increment column value is incremented on inserting 0.
Attaching the .result file with MyISAM and .reject file with Innodb.

@@ -2420,7 +2418,7 @@
 114	-108	-109	111	112	113	114	115
 115	-115	-116	118	119	120	121	122
 116	-122	-123	125	126	127	128	128
-117	-128	1	2	3	4	5	5
+118	-128	1	2	3	4	5	5
 255	127	6	7	8	9	10	10
 255	33	34	NULL	NULL	NULL	NULL	NULL

CREATE TABLE t5(c1 TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, c2 TINYINT SIGNED NULL, c3 TINYINT SIGNED NOT NULL, c4 SMALLINT, c5 MEDIUMINT, c6 INT, c7 INTEGER, c8 BIGINT, PRIMARY KEY(c1,c2), UNIQUE INDEX(c3));

MyISAM/Maria:
----------------
SELECT * FROM t5;
c1      c2      c3      c4      c5      c6      c7      c8
101     -102    -103    -104    105     106     107     108
102     0       0       37      38      39      40      41
108     -109    -110    111     112     113     114     115
109     -32     -32     33      34      35      36      37
110     0       -104    2       NULL    NULL    NULL    NULL
111     0       -17     18      19      20      21      22
112     -1      -1      8       9       10      11      12
113     -101    -102    104     105     106     107     108
114     -108    -109    111     112     113     114     115
115     -115    -116    118     119     120     121     122
116     -122    -123    125     126     127     128     128
117     -128    1       2       3       4       5       5
255     127     6       7       8       9       10      10
255     33      34      NULL    NULL    NULL    NULL    NULL

With Innodb:
----------------
SELECT * FROM t5;
c1      c2      c3      c4      c5      c6      c7      c8
101     -102    -103    -104    105     106     107     108
102     0       0       37      38      39      40      41
108     -109    -110    111     112     113     114     115
109     -32     -32     33      34      35      36      37
110     0       -104    2       NULL    NULL    NULL    NULL
111     0       -17     18      19      20      21      22
112     -1      -1      8       9       10      11      12
113     -101    -102    104     105     106     107     108
114     -108    -109    111     112     113     114     115
115     -115    -116    118     119     120     121     122
116     -122    -123    125     126     127     128     128
118     -128    1       2       3       4       5       5
255     127     6       7       8       9       10      10
255     33      34      NULL    NULL    NULL    NULL    NULL

c1 instead of 117 incremented to 118. 

How to repeat:
Please see the attached .result file.
[10 Mar 2009 5:07] Nidhi Shrotriya 
.result file

Attachment: insert_number_auto.result (application/octet-stream, text), 78.60 KiB.
[10 Mar 2009 5:07] Nidhi Shrotriya 
.reject file

Attachment: insert_number_auto.reject (application/octet-stream, text), 78.53 KiB.
[10 Mar 2009 6:39] Sveta Smirnova 
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read about AUTO_INCREMENT Handling in InnoDB at http://dev.mysql.com/doc/refman/6.0/en/innodb-auto-increment-handling.html