Error "Duplicate entry for key 1", but there don't seem to be any duplicates...

[Kenn Baker]

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