I have seen this problem before. It has occurred when the database was being written to outside of the Magento API and something went wrong. An easy solution is to turn on the MySQL general log to record all sql queries. On the command line be looking at the mysql general log in realtime by using the tail program and the -f flag, like tail -f general.log
Now try to perform the action that's giving you the integrity constraint violation, you should see the attempted sql query in the log. With this info you should be able to better trace the problem and if needed, delete a bad entry in the database.
I experienced a similar issue, as a result of programmatically importing products.
The issue arose when trying to re-index the "Product Attributes" index (which consistently failed). The error message was almost exactly what you quoted above and was very difficult to isolate the root cause.
I discovered that for "multiselect" attributes, I was rather erroneously adding duplicate selections.
Consider the following example to explain the issue and how I resolved it...
Picture a "multiselect" attribute called "Writers", with three options as follows:
- Jimbob (ID 1)
- Mary (ID 2)
- Stephen (ID 3)
I was programmatically assigning selections using the code :
$product->setData("attribute_code",$values);
Where $values was an array of Ids. So for instance a book (product) written by Jimbob and Stephen would have an array $values of [1,3].
Somewhere along the line I was adding in values like [3,3].
This duplicate selection will not cause errors elsewhere in the system, but certainly will when it comes to indexing.
I suggest simply using the PHP method "array_unique" to prevent duplicates.
You might have some luck looking at the "catalog_product_entity_varchar" table for possible duplicates strings of IDs as described above (this is the table where product attribute multiselect selections are stored).
Perhaps this suggestion will help, I know it took me some considerable digging to find it.
Best Answer
The error has been found on our import/update script (product->save() was called twice).
Important: no database "cleaning" was required.
I'm happy to have not choose to follow some advice on the web that propose to do that kind of operations (@fabian, you're golden rule was successfully followed).