MySQL Bugs: #81354: zero is used as a literal value for auto

Bug #81354	zero is used as a literal value for auto_increment columns
Submitted:	9 May 2016 12:58	Modified:	9 Jul 2016 16:26
Reporter:	Mikhail Izioumtchenko (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6.27	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
the documentation states on an INSERT into an auto_increment column, 0 is treated
exactly in the same way as NULL, that is, 0 should be replaced with the next 
autoinc value:

http://dev.mysql.com/doc/refman/5.6/en/numeric-type-attributes.html

>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

yet in the example below, 0 itself is inserted (or is attempted to be inserted)

Note: this would not reproduce in 5.7.12

How to repeat:
create table td (td int unsigned  auto_increment primary key, b char(10));
Query OK, 0 rows affected (0.02 sec)

insert into td values(0,0);
Query OK, 1 row affected (0.01 sec)

insert into td values(0,0);
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'

 select * from td;
+----+------+
| td | b    |
+----+------+
|  0 | 0    |
+----+------+
1 row in set (0.00 sec)

select @@version;
+------------+
| @@version  |
+------------+
| 5.6.25-log |

Will this explain:
http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_auto_value_on_zero
?

Maybe the NO_AUTO_VALUE_ON_ZERO settings for the two servers (5.6 and 5.7) are not the same?

-- Peter
-- Not a MySQL person

this does explain it, thank you.
Could be better documented such as a mention of SQL_MODE wrt auto_increment
on the pages that explain auto_increment.

Then a documentation bug instead?. Thanks.

yes, a small documentation improvement would be nice

Thank you for the feedback.

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