mysql duplicate entry error when there is no duplicate entry (bulk load via php)

Question

I am using mysql (5.0.32-Debian_7etch6-log) and i've got a nightly running bulk load php (5.2.6) script (using Zend_DB (1.5.1) via PDO) which does the following:

1. truncating a set of 4 'import' tables
2. bulk inserting data into these 4 'import' tables (re-using ids that have previously been in the tables as well, but i truncated the whole table, so that shouldn't be an issue, right?)
3. if everything goes well, rename the 'live' tables to 'temp', the 'import' tables to 'live' and then the 'temp' (old 'live') tables to 'import'

This worked great for weeks. Now I am occassionally getting this, somewhere in the middle of the whole bulk loading process:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '911' for key 1

Mind you that, this is not the first id that has been in the table before the truncation already. When I just start the script manually again, it works like a charm.

Any ideas? leftover indexes, something to do with the renaming maybe?

In addition, when I check the table for an entry with the id 911 afterwards, it is not even in there.

Stalingrad

Answer 1

Errors like this can occur when a MyISAM table becomes corrupt. Running the repair command on the table in question is usually all that's required to fix it:

> repair table mytablename;

A better solution is not to use MyISAM for tables where the data is constantly changing - InnoDB is much more bulletproof, and as Paul correctly points out, you can use transactions on InnoDB tables, but not on MyISAM.

By the way, I would avoid renaming tables on the fly - that's a fairly clunky thing to be doing on a regular basis, and could cause some very unexpected results if you ever have other users on the system while the renaming is going on. Why not just do something like this:

> truncate table temptable;
> truncate table importtable;

> #bulk insert new data
> insert into importtable(col1,col2,col3) 
> values(1,2,3),(4,5,6),(7,8,9);

> #now archive the live data
> insert into temptable(col1,col2,col3)
> select col1,col2,col3 from livetable;

> #finally copy the new data to live
> truncate table livetable;
> insert into livetable(col1,col2,col3)
> select col1,col2,col3 from importtable;

Of course if you are inserting a very large number of rows then the risk would be that all of your live data is unavailable for as long as the insert takes to complete, but overall this approach is far less destructive to indexes, triggers or anything else that may be linked to the tables in question.

Resonant

Answer 2

Apparently there were some lock issues or something, I was able to reproduce the behavior by shooting 'SELECT' statements to the affected and related tables in a parallel connection.

now i used DELETE FROM instead of TRUNCATE and changed the RENAME TABLE statements (where i did 3 renames at once each) to a bunch of single ALTER TABLE xxx RENAME TO zzz statements and can't reproduce the error any more.

so this might be solved. maybe someone else can profit from my day spent with research and a lot of try-and-error.

Stalingrad

Answer 3

Could some other script be inserting into the database while your import script is running?

Henn

Answer 4

Have you tried enabling the query log to see if you really ARE inserting a duplicate?

Can you reproduce it in your test environment? Do not enable the query log in production.

It is possible that the table has been corrupted if the problem is genuine; this could be caused by a number of things, but dodgy hardware or power failure are possibilities.

Check the mysql log to see if it has had any problems (or crashed) recently or during the period.

Again, all I can suggest is to try to reproduce it in your test environment. Create very large loads of test data and repeatedly load them.

Remake

Answer 5

Are you using transactions? You can eliminate a lot of these sorts of problems with transactions, especially if it's possible to either lock the tables or set the transaction isolation mode to serializable. I'm not really familiar with those on MySQL, but I believe that transactions only work on InnoDB tables (or that could be obsolete knowledge).

Fracas

Answer 6

You are creating a new record with 'id' field omitted (or NULL), BUT previously you have updated another record and changed it's 'id' to '911'. In other words, you can't create another record if your table's AUTO_INCREMENT value is taken.

Cere