MySQL Bugs: #64817: INSERT INTO ... VALUES(0,... uses auto

Bug #64817	INSERT INTO ... VALUES(0,... uses auto_increment value instead
Submitted:	30 Mar 2012 16:04	Modified:	31 Mar 2012 7:32
Reporter:	Naeel Maqsudov	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	auto_increment insert values zero

Description:
If I insert a new record into a table with auto_increment field like this
INSERT INTO ATable VALUES(0,'others');
MySQL adds the record without any error or warning but uses the next auto_increment value instead of exactly specified 0(zero)

How to repeat:
mysql> create table tmp (id int not null auto_increment,data varchar(100),primary key(id));
Query OK, 0 rows affected (0.08 sec)
mysql> insert into tmp(data) values ('second record');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tmp values (0,'technological record with ID=0');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tmp values (0,'technological record with ID=0');
Query OK, 1 row affected (0.06 sec)  <--- (WOW! Look at this! OK again? WTF?)
mysql> select * from tmp;
+----+--------------------------------+
| id | data                           |
+----+--------------------------------+
|  1 | second record                  |
|  2 | technological record with ID=0 |
|  3 | technological record with ID=0 |
+----+--------------------------------+
3 rows in set (0.00 sec)

Suggested fix:
There are several ways to solve the issue:

1) just add value 0 as it is requested by INSERT INTO command

2) does not allow to execute the command

3) execute with issuing a warning, and explaining that the value 0 was replaced by next auto_increment value

Please, send the output of:

select version(), @@sql_mode;

from your MySQL server. 

Read http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html#sqlmode_no_auto_value_on_zero. Probably NO_AUTO_VALUE_ON_ZERO SQL mode is what you really need.

+---------------------------------------+----------------------------------------------------------------+
| version()                             | @@sql_mode     |
+---------------------------------------+----------------------------------------------------------------+
| 5.1.52-enterprise-commercial-advanced | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------------------------------+----------------------------------------------------------------+

Yes, right. NO_AUTO_VALUE_ON_ZERO is definitely what I need.
Thanks, I didn't know that.
Oracle has no auoincrements at all, uses sequences.
Paradox has, but starts autoincrements from zero.
MSJet doesn't allow neither insert nor update values in aotoincrement field.
etc.
So every RDBMS provides use different approach to PK generation. :)