Mysql duplicate entry error when there is no duplicate record (bulk loading via php)

Question

I am using mysql (5.0.32-Debian_7etch6-log) and I have a nightly bulk upload php (5.2.6) script (using Zend_DB (1.5.1) via PDO) that does the following:

• truncation of a set of 4 "import" tables
• bulk insertion of data into these 4 "import" tables (reusing identifiers that were also in the tables earlier, but I truncated the entire table, so this should not be a problem, right?)
• if all goes well, rename the "live" tables to "temp", the tables "import" to "live", and then the "temp" (old "live") tables to "import"

This worked perfectly for several weeks. Now I get this from time to time, somewhere in the middle of the whole bulk upload process:

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

Keep in mind that this is not the first identifier that was in the table before truncation. When I run the script again manually, it works like a charm.

Any ideas? remaining indexes, do something with renaming, maybe?

In addition, when I check the table for the record with the identifier 911, it is not even there.

Answer 1

Errors like this can occur when the MyISAM table becomes corrupted. The recovery command in the table in question is usually required to correct it:

 > repair table mytablename; 

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

By the way, I would not rename the tables on the fly - this is a rather clumsy thing that needs to be done on a regular basis, and can lead to very unexpected results if you ever have other users on the system while renaming to. 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 insert a very large number of rows, then the risk will be that all your data in real time is not available until the insert is completed, but in general this approach is much less damaging to indexes, triggers, or anything else , which may be related to the tables under consideration.

Answer 2

Apparently there were some kind of locking problems or something like that, I was able to reproduce the behavior by removing the "SELECT" statements in 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 group of single ALTER TABLE xxx RENAME TO zzz statements and can no longer reproduce the error.

so that it can be solved. maybe someone else can benefit from my research day and many attempts and mistakes.

Answer 3

Can any other script be inserted into the database while your script import is in progress?

Answer 4

Have you tried turning on the query log to see if you are really inserting a duplicate?

Can you play it in a test environment? Do not include the query log during production.

It is possible that the table was corrupted if the problem is genuine; this can be caused by a number of things, but dodgy equipment or power failure are opportunities.

Check the mysql log to see if it has had any problems (or has been broken) recently or over a period.

Again, all I can offer is to try to reproduce it in a test environment. Create very large loads of test data and reload them.

Answer 5

Do you use transactions? You can fix many of these transaction problems, especially if it is possible to either lock the tables or set transaction isolation mode for serialization. I am not very familiar with MySQL, but I believe that transactions only work with InnoDB tables (or it may be outdated knowledge).

Answer 6

You create a new record with the id field omitted (or NULL), BUT you previously updated another record and changed its 'id' to '911'. In other words, you cannot create another record if the value of the AUTO_INCREMENT table is accepted.