MySQL Bugs: #69958: "AUTO INCREMENT" is not a serial number.

Bug #69958	"AUTO INCREMENT" is not a serial number.
Submitted:	8 Aug 2013 3:22	Modified:	8 Aug 2013 4:03
Reporter:	kazushige uratani	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.5.19, 5.5.22, 5.5.10, 5.5.13	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Hello.

If execute "select insert",
I will report "AUTO_INCREMENT" because not a serial number.

kazushige uratani

How to repeat:
▼Prepare
mysql> CREATE TABLE `tbl_sample` (
    ->   `id` smallint(5) unsigned NOT NULL DEFAULT '0',
    ->   `name` varchar(30) DEFAULT NULL,
    ->   `cnt` tinyint(3) unsigned NOT NULL DEFAULT '0',
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE tbl_sample MODIFY id INT AUTO_INCREMENT;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

▼execute "select insert"
mysql> INSERT INTO tbl_sample (name, cnt)
    -> SELECT 'sample', '1' FROM dual UNION ALL
SELECT 'sample', '1' FROM dual UNION ALL
    -> SELECT 'sample', '1' FROM dual UNION ALL
    -> SELECT 'sample', '1' FROM dual UNION ALL
    -> SELECT 'sample', '1' FROM dual UNION ALL
SELECT 'sample', '1' FROM dual UNION ALL
    -> SELECT 'sample', '1' FROM dual UNION ALL
    -> SELECT 'sample', '1' FROM dual UNION ALL
SELECT 'sample', '1' FROM dual UNION ALL
    -> SELECT 'sample', '1' FROM dual UNION ALL
    -> SELECT 'sample', '1' FROM dual UNION ALL
    -> SELECT 'sample', '1' FROM dual UNION ALL
SELECT 'sample', '1' FROM dual;    -> SELECT 'sample', '1' FROM dual;
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tbl_sample;
+----+--------+-----+
| id | name   | cnt |
+----+--------+-----+
|  1 | sample |   1 |
|  2 | sample |   1 |
|  3 | sample |   1 |
|  4 | sample |   1 |
|  5 | sample |   1 |
|  6 | sample |   1 |
|  7 | sample |   1 |
|  8 | sample |   1 |
|  9 | sample |   1 |
| 10 | sample |   1 |
+----+--------+-----+
10 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE tbl_sample\G
*************************** 1. row ***************************
       Table: tbl_sample
Create Table: CREATE TABLE `tbl_sample` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `cnt` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8　　　　←"AUTO_INCREMENT" is not 11
1 row in set (0.00 sec)

mysql> INSERT INTO tbl_sample (name, cnt)
    -> SELECT 'sample', '1' FROM dual UNION ALL
    -> SELECT 'sample', '1' FROM dual UNION ALL
    -> SELECT 'sample', '1' FROM dual UNION ALL
SELECT 'sample', '1' FROM dual UNION ALL
    -> SELECT 'sample', '1' FROM dual UNION ALL
    -> SELECT 'sample', '1' FROM dual UNION ALL
    -> SELECT 'sample', '1' FROM dual UNION ALL
    -> SELECT 'sample', '1' FROM dual UNION ALL
SELECT 'sample', '1' FROM dual UNION ALL
    -> SELECT 'sample', '1' FROM dual UNION ALL
    -> SELECT 'sample', '1' FROM dual UNION ALL
    -> SELECT 'sample', '1' FROM dual;
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tbl_sample;
+----+--------+-----+
| id | name   | cnt |
+----+--------+-----+
|  1 | sample |   1 |
|  2 | sample |   1 |
|  3 | sample |   1 |
|  4 | sample |   1 |
|  5 | sample |   1 |
|  6 | sample |   1 |
|  7 | sample |   1 |
|  8 | sample |   1 |
|  9 | sample |   1 |
| 10 | sample |   1 |
| 16 | sample |   1 |←It is started from the 16
| 17 | sample |   1 |
| 18 | sample |   1 |
| 19 | sample |   1 |
| 20 | sample |   1 |
| 21 | sample |   1 |
| 22 | sample |   1 |
| 23 | sample |   1 |
| 24 | sample |   1 |
| 25 | sample |   1 |
+----+--------+-----+
20 rows in set (0.00 sec)

▼execute "bulk insert"
mysql> TRUNCATE TABLE tbl_sample;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO tbl_sample (name, cnt)
    -> VALUES
    -> ('sample', '1'),
    -> ('sample', '1'),
    -> ('sample', '1'),
    -> ('sample', '1'),
    -> ('sample', '1'),
    -> ('sample', '1'),
    -> ('sample', '1'),
    -> ('sample', '1'),
    -> ('sample', '1'),
    -> ('sample', '1');
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tbl_sample;
+----+--------+-----+
| id | name   | cnt |
+----+--------+-----+
|  1 | sample |   1 |
|  2 | sample |   1 |
|  3 | sample |   1 |
|  4 | sample |   1 |
|  5 | sample |   1 |
|  6 | sample |   1 |
|  7 | sample |   1 |
|  8 | sample |   1 |
|  9 | sample |   1 |
| 10 | sample |   1 |
+----+--------+-----+
10 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE tbl_sample\G
*************************** 1. row ***************************
       Table: tbl_sample
Create Table: CREATE TABLE `tbl_sample` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `cnt` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> INSERT INTO tbl_sample (name, cnt)
    -> VALUES
    -> ('sample', '1'),
    -> ('sample', '1'),
    -> ('sample', '1'),
    -> ('sample', '1'),
    -> ('sample', '1'),
    -> ('sample', '1'),
    -> ('sample', '1'),
    -> ('sample', '1'),
    -> ('sample', '1'),
    -> ('sample', '1');
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tbl_sample;
+----+--------+-----+
| id | name   | cnt |
+----+--------+-----+
|  1 | sample |   1 |
|  2 | sample |   1 |
|  3 | sample |   1 |
|  4 | sample |   1 |
|  5 | sample |   1 |
|  6 | sample |   1 |
|  7 | sample |   1 |
|  8 | sample |   1 |
|  9 | sample |   1 |
| 10 | sample |   1 |
| 11 | sample |   1 |
| 12 | sample |   1 |
| 13 | sample |   1 |
| 14 | sample |   1 |
| 15 | sample |   1 |
| 16 | sample |   1 |
| 17 | sample |   1 |
| 18 | sample |   1 |
| 19 | sample |   1 |
| 20 | sample |   1 |
+----+--------+-----+
20 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE tbl_sample\G
*************************** 1. row ***************************
       Table: tbl_sample
Create Table: CREATE TABLE `tbl_sample` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `cnt` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+
1 row in set (0.00 sec)

Has been resolved. Thank you all!

mysql> show variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 0     |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql> INSERT INTO tbl_sample (name, cnt)
    -> SELECT 'sample', '1' FROM dual UNION ALL
    -> SELECT 'sample', '1' FROM dual UNION ALL
    -> SELECT 'sample', '1' FROM dual UNION ALL
    -> SELECT 'sample', '1' FROM dual UNION ALL
    -> SELECT 'sample', '1' FROM dual UNION ALL
    -> SELECT 'sample', '1' FROM dual UNION ALL
    -> SELECT 'sample', '1' FROM dual UNION ALL
SELECT 'sample', '1' FROM dual UNION ALL
    -> SELECT 'sample', '1' FROM dual UNION ALL
    -> SELECT 'sample', '1' FROM dual UNION ALL
    -> SELECT 'sample', '1' FROM dual;
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tbl_sample;
+----+--------+-----+
| id | name   | cnt |
+----+--------+-----+
|  1 | sample |   1 |
|  2 | sample |   1 |
|  3 | sample |   1 |
|  4 | sample |   1 |
|  5 | sample |   1 |
|  6 | sample |   1 |
|  7 | sample |   1 |
|  8 | sample |   1 |
|  9 | sample |   1 |
| 10 | sample |   1 |
+----+--------+-----+
10 rows in set (0.01 sec)

mysql> SHOW CREATE TABLE tbl_sample\G
*************************** 1. row ***************************
       Table: tbl_sample
Create Table: CREATE TABLE `tbl_sample` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `cnt` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Has been resolved. Thank you all!