MySQL Bugs: #21860: Problem with Aotuincerement field with zero value

Bug #21860	Problem with Aotuincerement field with zero value
Submitted:	28 Aug 2006 6:54	Modified:	29 Aug 2006 8:44
Reporter:	Zigmund Bulinsh	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.24	OS:	Windows (Windows XP/Windows 2003)
Assigned to:		CPU Architecture:	Any

Description:
I cannot set ID field of tabel to autoincrement - MySQL says "Duplicate entry `1` for key 1".

Change ID to autoincrement causes 0 value to be replaced with "1" value ignoring foreign key of another tables!!!

Trigger also doesn't fires when DDL command changes zero value.

How to repeat:
Create following table where is no autoincrement field:

CREATE TABLE `bug` (
  `id` int(10) unsigned NOT NULL default '0',
  `value` varchar(84) character set utf8 NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `value` (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `bug` VALUES ('0', '1');
INSERT INTO `bug` VALUES ('1', '2');
INSERT INTO `bug` VALUES ('2', '3');
INSERT INTO `bug` VALUES ('3', '4');
INSERT INTO `bug` VALUES ('4', '5');
INSERT INTO `bug` VALUES ('5', '6');
INSERT INTO `bug` VALUES ('6', '7');
INSERT INTO `bug` VALUES ('7', '8');

CREATE TRIGGER `before_insert` BEFORE INSERT ON `bug` FOR EACH ROW begin
  set NEW.id=(select ifnull(max(id),0)+1 from bug);
end; 
#trigger is not essential.

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `bug_id` int(10) unsigned default NULL,
  PRIMARY KEY  (`id`),
  KEY `bug_id` (`bug_id`),
  CONSTRAINT `test_ibfk_1` FOREIGN KEY (`bug_id`) REFERENCES `bug` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` VALUES ('1', '0');

With this values I cannot set ID field of tabel `bug` to autoincrement - MySQL says "Duplicate entry `1` for key 1".

Now if I change "1" id value to 100 (in `bug` table) and then change ID to autoincrement - 0 value is replaced by "1" value - ignoring foreign key of `test` table!!!

And if I create this trigger:
CREATE TRIGGER `before_update` BEFORE UPDATE ON `bug` FOR EACH ROW begin
  set NEW.id=OLD.id;
end;

This trigger also doesn't fires when this change happens.

Suggested fix:
don't update zero and null values!!!

I can reproduce on 4.1.21.

There seems to be a particular issue with 0 (zero) -values of a PK.
This works:

CREATE TABLE `bug` (
  `id` int(10) NOT NULL,
  `value` varchar(84) character set utf8 NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `value` (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `bug` VALUES ('-1', '1');
INSERT INTO `bug` VALUES ('1', '2');
INSERT INTO `bug` VALUES ('2', '3');
INSERT INTO `bug` VALUES ('3', '4');
INSERT INTO `bug` VALUES ('4', '5');
INSERT INTO `bug` VALUES ('5', '6');
INSERT INTO `bug` VALUES ('6', '7');
INSERT INTO `bug` VALUES ('7', '8');

and you can:
"alter table `test`.`bug`, change `id` `id` int (10) NOT NULL AUTO_INCREMENT;"
... the server does not attempt to change '-1'. But it does attempt to change '0' to '1' and next it prevents that ifself of course as the PK-value '1' allready exists.

In my opinion the FK and the TRIGGER are both irrelevant for the discussion! 

It is an issue with "alter table ... auto_increment" (simply!) when current value of the PK that is made auto_increment is ZERO.

About FK:
Trigger it's ok - beacause triggers doesn't fires on DDL events.
But in this situation value of field ID was changed, but detail tables was not updated. Sorry if I missunderstand your phrase: "In my opinion the FK and the TRIGGER are both irrelevant for the discussion!"

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

Read carefully about AUTO_INCREMENT limitations at http://dev.mysql.com/doc/refman/5.0/en/create-table.html

NO_AUTO_VALUE_ON_ZERO it's ok. but can you explain me how it can be not a bug if MySQL doing all correctly destroy referential integrity between tables? There must be error "referental integrity chechks fails..",because on update for detail table is set RESTRICT! I was not inserting records. Records already was in table!