Bug #2669 Update fails for no reason - Incorrect "1062 = Duplicate entry"
Submitted: 6 Feb 2004 17:21 Modified: 9 Feb 2004 15:34
Reporter: Hadrian Oliver Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:4.0.16 OS:Linux (Linux - Mandrake 9.0)
Assigned to: Dean Ellis CPU Architecture:Any

[6 Feb 2004 17:21] Hadrian Oliver
Description:
I have created a simple InnoDB database for storing server log details in. It currently contains around 15000 recorded hits.

When performing an update across 4000 records or more (approx.) that will affect a few hundred of them, I am getting the error "1062 = Duplicate entry '13-[timestamp]' on key 2" (where timestamp is the current time).

Repeating the same update in smaller increments such that fewer records will be affected each time (eg. by saying 'Hit_ID < 2000', then 'Hit_ID < 4000', etc.) the updates all succeed, as they should have do when performin the update in one go.

The update being performed was: -

update HIT set Site_ID = 1 where Full_Name like '%coins_0__.jpg%' and Site_ID = 7;

How to repeat:
Create a database such as the following:

= START SQL ================================================================

DROP DATABASE IF EXISTS SERVER_LOG; 
CREATE DATABASE SERVER_LOG; 
CREATE TABLE `AGENT` ( 
`Agent_ID` int(10) unsigned NOT NULL auto_increment, 
`Full_Name` varchar(255) NOT NULL default '', 
`Type` varchar(50) default NULL, 
`Version` varchar(30) default NULL, 
`Is_WebBot` tinyint(3) unsigned NOT NULL default '0', 
PRIMARY KEY (`Agent_ID`), 
UNIQUE KEY `Full_Name` (`Full_Name`) 
) TYPE=InnoDB 

CREATE TABLE `STATUS_CODE` ( 
`Status_Code` int(3) unsigned NOT NULL default '0', 
`Description` varchar(100) NOT NULL default '', 
PRIMARY KEY (`Status_Code`) 
) TYPE=InnoDB 

CREATE TABLE `SITE` ( 
`Site_ID` int(10) unsigned NOT NULL auto_increment, 
`Site_Name` varchar(100) NOT NULL default '', 
`Base_URL` varchar(100) NOT NULL default '', 
PRIMARY KEY (`Site_ID`), 
UNIQUE KEY `Base_URL` (`Base_URL`) 
) TYPE=InnoDB 

CREATE TABLE `LOCAL_IP` ( 
`Local_IP_ID` int(10) unsigned NOT NULL auto_increment, 
`Site_ID` int(10) unsigned NOT NULL default '0', 
`IP` varchar(15) NOT NULL default '', 
PRIMARY KEY (`Local_IP_ID`), 
KEY `Idx_Site_Id` (`Site_ID`), 
CONSTRAINT `0_28` FOREIGN KEY (`Site_ID`) REFERENCES `SITE` (`Site_ID`) ON DELETE CASCADE 
) TYPE=InnoDB 

CREATE TABLE `HIT` ( 
`Hit_ID` int(10) unsigned NOT NULL auto_increment, 
`Site_ID` int(10) unsigned NOT NULL default '0', 
`Hit_Date` timestamp(14) NOT NULL, 
`Visitor_IP` varchar(15) NOT NULL default '', 
`Method` varchar(30) NOT NULL default '', 
`File_Requested` mediumtext NOT NULL, 
`HTTP` varchar(30) NOT NULL default '', 
`Status_Code` int(3) unsigned NOT NULL default '0', 
`Bytes_Returned` int(10) unsigned NOT NULL default '0', 
`Referer` mediumtext, 
`Agent_ID` int(10) unsigned NOT NULL default '0', 
`User_ID` varchar(50) default NULL, 
`Originating_Row` int(8) unsigned NOT NULL default '0', 
PRIMARY KEY (`Hit_ID`), 
UNIQUE KEY `Originating_Row` (`Originating_Row`,`Hit_Date`), 
KEY `Idx_Site_ID` (`Site_ID`), 
KEY `Idx_Status_Code` (`Status_Code`), 
KEY `Idx_Agent_ID` (`Agent_ID`), 
CONSTRAINT `0_75` FOREIGN KEY (`Site_ID`) REFERENCES `SITE` (`Site_ID`), 
CONSTRAINT `0_76` FOREIGN KEY (`Status_Code`) REFERENCES `STATUS_CODE` (`Status_Code`), 
CONSTRAINT `0_77` FOREIGN KEY (`Agent_ID`) REFERENCES `AGENT` (`Agent_ID`) 
) TYPE=InnoDB

= END SQL ==================================================================

***AS A MINIMUM***

Insert 1 record into the AGENT table. 
Insert 1 record into the STATUS table. 
Insert 2 records into the SITE table. 
Insert 15000 records into the HIT table, all for SITE 1, AGENT 1.
Perform the following update such that it should affect a 1000 or so records.

     update HIT set Site_ID = 2 where Full_Name like '%matches_a_1000_records%' 
     and Site_ID = 1;

There is no reason I can see why this should fail, but it does. Repeat the update starting with: -

     update HIT set Site_ID = 2 where Full_Name like '%matches_a_1000_records%' 
     and Site_ID = 1 and Hit_ID < 2000;

... works fine. Keep doing this, increasing the max Hit_ID each time, until all records have been processed.

I'm happy to submit my actual data if this would help. Can provide in 2.45 MB SQL file.

Suggested fix:
Make it so that it doesn't fail? 

Not really sure what else I can suggest here. 

Sorry :o(
[8 Feb 2004 10:21] Hadrian Oliver
Further to what has already been stated:

The duplication of the key has been occurring because the DATA HAS BEEN CORRUPTED!!! The timestamp displayed in the error message has actually replaced the timestamp originally stored in the records. This should not have happened!
[9 Feb 2004 15:34] Dean Ellis
You are not providing a value (ie: its current value) for the TIMESTAMP column, so it is being updated to the current time, which is the defined behavior for TIMESTAMP column types.  See the manual:

http://www.mysql.com/doc/en/DATETIME.html