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
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.6.22, 5.6.26, 5.7.8
• OS: Any
• 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.