Bug #89225 INSERT INTO ... VALUES (DEFAULT) on auto_increment becomes unexpected value 0
Submitted: 14 Jan 2018 21:09 Modified: 15 Jan 2018 7:30
Reporter: Ryuta Kamizono Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any

[14 Jan 2018 21:09] Ryuta Kamizono
Description:
We want to use bulk insert for loading fixtures, but the following INSERT doesn't work on NO_AUTO_VALUE_ON_ZERO due to DEFAULT on auto_increment becomes unexpected value 0:

root@localhost [test] > select @@sql_mode;
+-----------------------+
| @@sql_mode            |
+-----------------------+
| NO_AUTO_VALUE_ON_ZERO |
+-----------------------+
1 row in set (0.00 sec)

root@localhost [test] > create table users (id int auto_increment primary key, name varchar(255));
Query OK, 0 rows affected (0.04 sec)

root@localhost [test] > INSERT INTO users (id, name) VALUES (100, 'Reserved'), (DEFAULT, 'David'), (DEFAULT, 'Rafael');
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'

How to repeat:
root@localhost [test] > select @@sql_mode;
+-----------------------+
| @@sql_mode            |
+-----------------------+
| NO_AUTO_VALUE_ON_ZERO |
+-----------------------+
1 row in set (0.00 sec)

root@localhost [test] > create table users (id int auto_increment primary key, name varchar(255));
Query OK, 0 rows affected (0.04 sec)

root@localhost [test] > INSERT INTO users (id, name) VALUES (DEFAULT, 'David');
Query OK, 1 row affected (0.00 sec)

root@localhost [test] > select * from users;
+----+-------+
| id | name  |
+----+-------+
|  0 | David |
+----+-------+
1 row in set (0.00 sec)

root@localhost [test] > INSERT INTO users (id, name) VALUES (DEFAULT, 'Rafael');
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'

Suggested fix:
INSERT INTO ... VALUES (DEFAULT) on auto_increment should become NULL to work on NO_AUTO_VALUE_ON_ZERO.
[15 Jan 2018 7:30] MySQL Verification Team
Thank you for the bug report. Expected behavior as documented:

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_auto_value_on_zero

"NO_AUTO_VALUE_ON_ZERO

NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number."