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(
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(