MySQL Bugs: #663: Problem resulting from zeros in autoincrement fields

Bug #663	Problem resulting from zeros in autoincrement fields
Submitted:	17 Jun 2003 13:25	Modified:	22 Jul 2003 8:10
Reporter:	Gary Thornock	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	all	OS:	Any (all)
Assigned to:	Victor Vagin	CPU Architecture:	Any

Description:
The inability to insert zero into an auto-increment field causes a problem: Suppose you have a table where you have, in fact, put in a zero value in that field (using update or whatever), and your application needs the zero in that field in order to work as expected.  Suppose further that you need to copy the database to another server, and you use mysqldump | mysql to do the copy.  Your new table doesn't have the zero where you needed it.

How to repeat:
Create a table with an autoincrement column.  Insert a few rows, and explicitly set the autoincrement value for one row to 0.  Then, export the table with mysqldump and import it into another database.  Note that the zero value is lost, replaced with an auto-incremented value.

Suggested fix:
Explicitly inserting a zero into an autoincrement field should result in a zero value for the field.

first variant is to add 'update ..' line for every auto_increment zero field
second variant is to add session variable 'disable_auto_increment'

Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html

This bug is fixed in development tree mysql-4.1