MySQL Bugs: #41848: NO_AUTO_VALUE_ON_ZERO should be on by default.

Bug #41848	NO_AUTO_VALUE_ON_ZERO should be on by default.
Submitted:	4 Jan 2009 4:41	Modified:	16 Jul 2009 19:16
Reporter:	Alexander Pas	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	4.1.1+, 4.2+, 5+	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
PREFACE:
auto-increment currently starts at 1, and uses 0 and NULL to trigger auto-increment, unless NO_AUTO_VALUE_ON_ZERO is set on, when it only uses NULL.

REASON:
0 (zero) is a valid positive integer, which can be used in referential integrity checks to indicate that the value is 'SET and N/A' instead of UNSET/MISSING

USECASE:
- Anonymous users, are allowed to leave comments, comments are stored in a database.
- the comments table contains a referential integrity to the user table.
- the user id column (on both tables) is set to NOT NULL to enforce referential integrity
- the user id column is a PRIMARY KEY on the user table
- in the user table, id = 0 is stored as anonymous user.
- this database should still be able to be backupped and restored.
see also: http://drupal.org/node/204411

From: Wikipedia ( http://en.wikipedia.org/wiki/0_(number)#Null_value )
In databases a field can have a null value. This is equivalent to the field not having a value. For numeric fields it is not the value zero. For text fields this is not blank nor the empty string.

EXPECTED OUTPUT:
SQL query: SELECT * FROM `testcase`;
Rows: 4
id  value
--  ------
0 
1   foo
2   bar
3   foobar

ACTAL OUTPUT:
SQL query: SELECT * FROM `testcase`;
Rows: 4
id  value
--  ------
3 
1   foo
2   bar
4   foobar

How to repeat:
import this database dump/backup:

CREATE TABLE `testcase` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `value` varchar(60) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `value` (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

INSERT INTO `testcase` VALUES (0, '');
INSERT INTO `testcase` VALUES (1, 'foo');
INSERT INTO `testcase` VALUES (2, 'bar');
INSERT INTO `testcase` VALUES (NULL, 'foobar');

Suggested fix:
NO_AUTO_VALUE_ON_ZERO should be (at least) turned on by default, or it's behaviour should the only behaviour.

note: auto-increment should still start at 1

Thank you for a problem report. Current behaviour is clearly described in the manual, so this is NOT a bug. Setting NO_AUTO_VALUE_ON_ZERO explicitly allows to solve the problem in your case.

Having NO_AUTO_VALUE_ON_ZERO turned on by default is a reasonable feature request, though.

coming from: http://drupal.org/node/204411
Is there any Progress on this Feature Request?

After chasing down this bug, I have to agree that no_auto_value_on_zero really should be on by default. I also believe that setting a new value for a key where it is set by the user already (he states that 'id' = 0) is a legacy mechanism that should be deprecated. It is confusing to the best of us! Why should this behavior not be removed?