MySQL Forums
Forum List  »  MyISAM

Error "Duplicate entry for key 1", but there don't seem to be any duplicates...
Posted by: Kenn Baker
Date: August 02, 2012 04:50AM

I am performing a sync process on some data in a mysql database (The mysql server version is 5.0.91-log and it is running on a Linux box).

I am receiving an error like "Duplicate entry '26552' for key 1" every time a certain query sequence is executed as part of a sync process I have set up.

The entry number is gradually increasing each time the query fails as per "Error in query: Duplicate entry '25065' for key 1" then "Error in query: Duplicate entry '25337' for key 1", then "Error in query: Duplicate entry '25759' for key 1" ... , but other than that, the error is the same.

The error is returned as a result of running the query, "INSERT INTO `mydatabase`.mytable_just_before_umd_sync SELECT * FROM `mydatabase`.mytable"

I run the process via cron on an hourly basis.

In order to verfify the sync process, I create some temporary copies of table/s that can be modified by the sync process.

I do this using some php, as below (executeQuery(...) is basically mysql_query($query) with some error checking and logging):

$tableName = "mytable_just_before_umd_sync";
$query = "DROP TABLE IF EXISTS $tableName";
$result = executeQuery($query, $tableName, __FUNCTION__, __LINE__);

$query = "CREATE TABLE $tableName LIKE $table";
$result = executeQuery($query, $tableName, __FUNCTION__, __LINE__);

$query = "INSERT INTO $tableName SELECT * FROM $table";
$result = executeQuery($query, $tableName, __FUNCTION__, __LINE__);


mytable is as follows:

CREATE TABLE `mytable` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`user_id` bigint(20) unsigned NOT NULL,
`school_id` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED AUTO_INCREMENT=26958 ;

I've checked, and there are no duplicates in mytable (couldn't really be anyhow as id is primary).

Is key 1 refering to mytable_just_before_umd_sync.id?

Would this be some kind of race issue (i.e. is the DROP TABLE still operating and then maybe the INSERT INTO operation catches up and hence the duplicate. I don't know how DROP TABLE works? Does it complete before returning?)?

Should I put something like "LOCK TABLES `mytable` WRITE;" and "UNLOCK TABLES;" around the "INSERT INTO $tableName SELECT * FROM $table;" command?

I've used mysql for some time, but this is a bit above my level.

Anyone have any ideas about what might be causing this issue?

I've googled and looked into the issue, but am at a bit of a loss really.

any help appreciated

Kenn

Options: ReplyQuote


Subject
Views
Written By
Posted
Error "Duplicate entry for key 1", but there don't seem to be any duplicates...
7563
August 02, 2012 04:50AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.