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:
- truncating a set of 4 'import' tables
- 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?)
- 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.