MySQL Bugs: #21465: Unable to insert zero value int(0) into primary key column.

Bug #21465	Unable to insert zero value int(0) into primary key column.
Submitted:	6 Aug 2006 22:02	Modified:	7 Aug 2006 22:20
Reporter:	Radek Zyka	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.8-nt and 4.1.20 Free BSD	OS:	Windows (win && Linux)
Assigned to:		CPU Architecture:	Any
Tags:	insert zero value, primary key

Description:
Unable to insert zero value int(0) into column which is primary key.
Due to this bug is impossible to create functional .sql backup with tables and datas, which already contains several rows with zero values.
Such generated backup complains 'Duplicate entry '1' for key 1' and
all of available frontends or commandline tool produces this error and stops importing.
And if i have huge .sql statement with lot of tables, nightmare begin.....

How to repeat:
This is sql dump, created eq. phpmyadmin (all tools i have, generate almost the same data, so no matter which tool is used for dump):

----------------->8-----------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(11) NOT NULL auto_increment,
  `nazev` varchar(250) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

#
# Dumping data for table test
#

INSERT INTO `test` VALUES (-1,'Abc');
INSERT INTO `test` VALUES (0,'def');
INSERT INTO `test` VALUES (1,'ghi');
INSERT INTO `test` VALUES (2,'jkl');

--------------8<-----------------
This is exactly perfect dump, from one of my tables. (shorted for report)

1. Try exec at first only table structure...

2. mysql> INSERT INTO `test` VALUES (-1,'Abc');
INSERT INTO `test` VALUES (0,'def');
Query OK, 1 row affected

Query OK, 1 row affected

3. mysql> SELECT * FROM test;
+----+-------+
| id | nazev |
+----+-------+
| -1 | Abc   |
|  1 | def   | <- here is 1 !!! How is this possible ? I want insert value 0
+----+-------+
2 rows in set

4. Because of this is script, next INSERT will produce duplicate error:
mysql> INSERT INTO `test` VALUES (1,'ghi');
INSERT INTO `test` VALUES (2,'jkl');

ERROR 1062 : Duplicate entry '1' for key 1
Query OK, 1 row affected

Suggested fix:
Manual fix this problem:

----------------->8-----------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(11) NOT NULL auto_increment,
  `nazev` varchar(250) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

#
# Dumping data for table subjekt
#

INSERT INTO `test` VALUES (-1,'Abc');
INSERT INTO `test` VALUES (0,'def');
UPDATE `test` SET id = 0 WHERE id = 1;  ## manual fix to fix prev. INSERT
INSERT INTO `test` VALUES (1,'ghi');
INSERT INTO `test` VALUES (2,'jkl');
--------------8<-----------------

This work, but i don't want type these lines after every INSERT with zero, every times when i need transfer database.

If column is defined as Autoincrement, maybe 0 is recognized as 
 NULL, which is legal, but only if I don't want specify value to that column.

Which version of mysqldump did you use to create the dump?
If it was a version from 4.0 then this is expected behavior,
if it was a mysqldump binary from a 4.1 distribuion then
please reopen the bug.

You misunderstand me, is no matter what program is used for dump.
Point is, that is impossible insert zero (integer 0) into table with primary key autoincrement.
Please read my post again. Especially this part:

2. mysql> INSERT INTO `test` VALUES (-1,'Abc');
INSERT INTO `test` VALUES (0,'def');
Query OK, 1 row affected

Query OK, 1 row affected

3. mysql> SELECT * FROM test;
+----+-------+
| id | nazev |
+----+-------+
| -1 | Abc   |
|  1 | def   | <- here is 1 !!! How is this possible ? I inserted value 0 ...
+----+-------+
2 rows in set

I'm sorry, if i'm wrong, but this is still bug IMO.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

http://dev.mysql.com/doc/refman/4.1/en/create-table.html

  As of MySQL 4.1.1, if the NO_AUTO_VALUE_ON_ZERO SQL mode is enabled, 
  you can store 0 in AUTO_INCREMENT columns as 0 without generating 
  a new sequence value. See Section 5.2.5, “The Server SQL Mode”.

mysqldump as of 4.1 also takes care of setting NO_AUTO_VALUE_ON_ZERO
in the header of the dump file it generates, that's why i was asking
for its version.