Why do I get "ERROR 1062 (23000): Duplicate entry '4289' for key 1" on an update?

Question

This question has been asked in a number of different flavors before, but the answers supplied in those cases so not come close to helping me solve my problem.

We are running MySQL version "5.0.41-community-log MySQL Community Edition (GPL)" The table in question is described like this (I have removed most of the table definition for business reasons):

 Field      Type          Null     Key     Default     Extra          
 ---------  ------------  -------  ------  ----------  -------------- 
 id         bigint(20)    NO       PRI     (null)      auto_increment 
 ...
 extracted  tinyint(1)    YES              (null)                     

When I run the update SQL statement:

UPDATE transaction_tbl SET extracted = 1 WHERE id = 4289

I get the error:

ERROR 1062 (23000): Duplicate entry '4289' for key 1

running this select statement:

SELECT id, extracted FROM transaction_tbl WHERE id BETWEEN 4288 AND 4290

produces this:

 id     extracted   
 -----  ------------ 
 4288   0                
 4289   0                
 4290   0                

I have read in places about using REPAIR on the table but received a response that "The storage engine for the table doesn't support repair"

I am at a bit of a loss. If anyone can help me. I do love to know how to fix this in a way that does not require me to destroy data.

Solution

As mentioned by @rabudde and @Eljakim in the comments section of the question, this problem was, in my case, being caused by the actions of a trigger.

The trigger was attempting to insert a record into another table and that insert was failing because of a duplicate key issue.

use SHOW TRIGGERS LIKE 'transaction_tbl' (in my case) to find all the triggers acting on the table and from there it is a bit of footwork to discover which trigger is being bad.

Ultimately I had to modify the trigger to only perform the insert in the case where they record was not in the target table and to do an update if it was there.