Bug #78004 auto_increment tinyint column value jumps from 125 to 127
Submitted: 10 Aug 2015 6:19 Modified: 31 Oct 2015 9:05
Reporter: Su Dylan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.6.22, 5.6.26, 5.7.8 OS:Any
Assigned to: CPU Architecture:Any

[10 Aug 2015 6:19] Su Dylan
Description:
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 ( a tinyint primary key auto_increment, b char(20));
INSERQuery OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES  (123,'') ;
Query OK, 1 row affected (0.00 sec)

mysql>  INSERT INTO t1 (a) VALUES ( 0 ) ;
Query OK, 1 row affected (0.00 sec)

mysql>  INSERT INTO t1 (a) VALUES ( 0 ) ;
Query OK, 1 row affected (0.00 sec)

mysql>  INSERT INTO t1 (a) VALUES ( 0 ) ;
FRQuery OK, 1 row affected (0.00 sec)

mysql>  SELECT * FROM t1 order by a;
+-----+------+
| a   | b    |
+-----+------+
| 123 |      |
| 124 | NULL |
| 125 | NULL |
| 127 | NULL |
+-----+------+
4 rows in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.22-log |
+------------+
1 row in set (0.00 sec)

How to repeat:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( a tinyint primary key auto_increment, b char(20));
INSERT INTO t1 VALUES  (123,'') ;
 INSERT INTO t1 (a) VALUES ( 0 ) ;
 INSERT INTO t1 (a) VALUES ( 0 ) ;
 INSERT INTO t1 (a) VALUES ( 0 ) ;
 SELECT * FROM t1 order by a;

Suggested fix:
The last row is expected to be '126´╝îNULL'.
[10 Aug 2015 9:42] MySQL Verification Team
Hello Su Dylan,

Thank you for the report and test case.

Thanks,
Umesh
[31 Oct 2015 9:05] Su Dylan
This problem also appears on 5.7.8.