Bug #69957 "AUTO_INCREMENT" is not a serial number.
Submitted: 8 Aug 2013 2:22 Modified: 8 Aug 2013 3:17
Reporter: kazushige uratani Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:mysql5.5.19, mysql5.5.22, mysql5.6.10, m OS:Linux
Assigned to: CPU Architecture:Any

[8 Aug 2013 2:22] kazushige uratani
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"
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;

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)

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');

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)

Suggested fix:
▼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)
[8 Aug 2013 3:17] kazushige uratani
I'm going to re-create report.