MySQL Bugs: #76206: AUTO_INCREMENT behaviour not fully documented where values given explicitly

Bug #76206	AUTO_INCREMENT behaviour not fully documented where values given explicitly
Submitted:	7 Mar 2015 9:41	Modified:	17 Mar 2015 17:02
Reporter:	Alan Egerton	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	All	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	auto_increment

Description:
The manual page "Numeric Type Attributes"[1] states:

> Integer or floating-point data types can have the additional attribute AUTO_INCREMENT.
> When you insert a value of NULL (recommended) or 0 into an indexed AUTO_INCREMENT
> column, the column is set to the next sequence value. Typically this is value+1, where value
> is the largest value for the column currently in the table. AUTO_INCREMENT sequences
> begin with 1. (Inserting NULL to generate AUTO_INCREMENT values requires that the
> column be declared NOT NULL. If the column is declared NULL, inserting NULL stores a
> NULL.)

An example on the manual page "Using AUTO_INCREMENT"[2] is followed by the comment:

> No value was specified for the AUTO_INCREMENT column, so MySQL assigned sequence
> numbers automatically.

Neither page states *explicitly* that, where a value is given for an AUTO_INCREMENT
column, that value is used (and the AUTO_INCREMENT counter updated to hold that value).

That said, the updating of the counter value (though not the use of the explicitly given value in
the inserted row) *is* documented for InnoDB[3]:

> If you insert a row that explicitly specifies the column value, and the value is bigger than the
> current counter value, the counter is set to the specified column value.

Historically, the documentation for IBMDB2I[4] was a little more explicit, in commenting on an
example:

> For the first INSERT statement, an explicit value of 3 is specified for the auto_increment
> column, so the value 3 is stored in the inserted row.

[1]: http://dev.mysql.com/doc/en/numeric-type-attributes.html
[2]: http://dev.mysql.com/doc/en/example-auto-increment.html
[3]: http://dev.mysql.com/doc/en/innodb-auto-increment-traditional.html
[4]: http://dev.mysql.com/doc/refman/5.1/en/se-db2-terms.html

How to repeat:
See the manual.

Suggested fix:
The above-quoted paragraph on the "Numeric Type Attributes" page should add:

> When you insert any other value into an indexed AUTO_INCREMENT column, the column is set to that inserted value; and the value that will be auto-assigned next is updated so that it follows sequentially from your inserted value.

Thank you for the bug report.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.

Posted by developer:
 
The documentation update for the bug added the following text:

"When you insert any other value into an AUTO_INCREMENT column, the column
is set to that value and the sequence is reset so that the next
automatically generated value follows sequentially from the largest column
value."

This is only true in MySQL 8.0. For previous versions of MySQL, the sequence is not reset as demonstrated by this example on MySQL 5.7:

CREATE TABLE animals (
     id MEDIUMINT AUTO_INCREMENT,
     name CHAR(30),
     PRIMARY KEY (id)
);

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;
+------+---------+
| id   | name    |
+------+---------+
|    1 | dog     |
|    2 | cat     |
|    3 | penguin |
|    4 | lax     |
|    5 | whale   |
|    6 | ostrich |
+------+---------+
6 rows in set (0.00 sec)

UPDATE animals SET id=1000 WHERE name='ostrich'; 
	
INSERT INTO animals (name) VALUES ('groundhog'), ('squirrel');

mysql> SELECT * FROM animals;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | dog       |
|    2 | cat       |
|    3 | penguin   |
|    4 | lax       |
|    5 | whale     |
|    7 | groundhog |
|    8 | squirrel  |
| 1000 | ostrich   |
+------+-----------+

Posted by developer:
 
Correction: 

Inserting an explicit value that is greater than the internal auto-increment
counter, does reset the sequence. Updating an existing auto-increment column value, as demonstrated above, does not (prior to MySQL 8.0).

Test script:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (i INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
INSERT INTO t1 () VALUES();
INSERT INTO t1 () VALUES();
INSERT INTO t1 () VALUES();
SELECT * FROM t1;
INSERT INTO t1 () VALUES(47);
INSERT INTO t1 () VALUES();
SELECT * FROM t1;

Result:

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.12 sec)

mysql> CREATE TABLE t1 (i INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.03 sec)

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

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

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

mysql> SELECT * FROM t1;
+---+
| i |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.00 sec)

mysql> INSERT INTO t1 () VALUES(47);
Query OK, 1 row affected (0.01 sec)

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

mysql> SELECT * FROM t1;
+----+
| i  |
+----+
|  1 |
|  2 |
|  3 |
| 47 |
| 48 |
+----+
5 rows in set (0.00 sec)

Posted by developer:
 
The following behavior applies in MySQL 5.5, 5.6, and 5.7:

"When you insert any other value into an AUTO_INCREMENT column, the column
is set to that value and the sequence is reset so that the next
automatically generated value follows sequentially from the largest column
value. Updating an existing AUTO_INCREMENT column value in an InnoDB table
does not reset the AUTO_INCREMENT sequence as it does for MyISAM and NDB
tables."

In MySQL 8.0, updating an existing AUTO_INCREMENT column value in an InnoDB table
also resets the AUTO_INCREMENT sequence.

https://dev.mysql.com/doc/mysql-tutorial-excerpt/5.7/en/example-auto-increment.html

The revised documentation should appear online soon.