MySQL Bugs: #21722: bug with '0' (zero) value in a field of type auto

Bug #21722	bug with '0' (zero) value in a field of type auto_increment and alter table
Submitted:	18 Aug 2006 14:37	Modified:	23 Sep 2006 11:10
Reporter:	Frederic linot	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	mysql Ver 14.12 Distrib 5.0.18	OS:	pc-linux-gnu (i686)
Assigned to:		CPU Architecture:	Any
Tags:	0, auto_increment

Description:
We can not force the value '0' (zero) in a field of type auto_increment when we insert a new row in table.

The 0 value is interpreted like no value.

How to repeat:
CREATE TABLE `mat_marques` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `libelle` varchar(50) NOT NULL default '',
  `site_internet` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM;

INSERT INTO `mat_marques` VALUES (38, 'NI', 'http://www.n*s.com/';);
INSERT INTO `mat_marques` VALUES (39, 'S', 'http://www.s*s.net/';);
INSERT INTO `mat_marques` VALUES (0, 'BUG!!', 'http://www.f*s.net/';);

SELECT * FROM `mat_marques`;

Suggested fix:
We have to do an UPDATE after the insert.

In our case, the ID of the last row will be 40 (because of the use of auto index instead of forced index), so we have to do :

UPDATE `mat_marques` SET id=0 WHERE id=40 ;

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

As stated at http://dev.mysql.com/doc/refman/5.0/en/create-table.html:

> An integer column can have the additional attribute AUTO_INCREMENT. 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.

I didn't knew about this specificity.

But imagine you want to dump a such table :

+----+----------+------------------------------+
| id | libelle  | site_internet                |
+----+----------+------------------------------+
|  6 | Boss     | http://www.bossus.com/       |
| 11 | Creative | http://www.creative.com/     |
|  0 | empty    |                              |
+----+----------+------------------------------+

if you use the mysqldump command, you will obtain the code :

INSERT INTO `table` (`id`, `libelle`, `site_internet`) VALUES (6,'Boss','http://www.bossus.com/'),(11,'Creative','http://www.creative.com/'),(0,'empty','';)

Imagine now that you want to import your dump into a new database !
Your table will be :

+----+----------+------------------------------+
| id | libelle  | site_internet                |
+----+----------+------------------------------+
|  6 | Boss     | http://www.bossus.com/       |
| 11 | Creative | http://www.creative.com/     |
| 12 | empty    |                              |
+----+----------+------------------------------+

DO YOU THINK THIS IS AN EXPECTED BEHAVIOR ???

consider a field named "f" of type INTEGER, autoincrement.
You have a row with the number 0 inside.

now, you do a "ALTER TABLE t ORDER BY f"

THE ROW WITH THE INITIAL NUMBER 0 IS NOW RE-ORDERED AND SET TO THE NEXT AUTOINCREMENT VALUE !!

O_O

consider a field named "f" of type INTEGER, autoincrement.
You have a row with the number 0 inside.

now, you do a "ALTER TABLE t ORDER BY f"

THE ROW WITH THE INITIAL NUMBER 0 IS NOW RE-ORDERED AND SET TO THE NEXT AUTOINCREMENT VALUE !!

O_O

Still not a bug. If you need 0 to be stored in auto_increment column and processed "correctly", just use SQL mode NO_AUTO_VALUE_ON_ZERO.

Read http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html for the details.