Error 1032 Can't find record in ...

[Horst Pralow]

I repeatedly get "error 1032 Can't find record in ..." with an INSERT ... SELECT type of statement where there is a fairly complex join in the SELECT part and the involved source-tables are all InnoDB.
That's why I'm posting into this forum since I didn't find one more suiteable.

Details:
My application is a web-based multiuser system running against a single MySQL DB. The Insert-statement producing the error is used to construct a user specific To-Do-List by applying user-selectable criteria to a join of up to 4 tables called 'claim', 'thread', 'timer' and 'depot' and inserting the matches into a temp table. The source tables are InnoDB, the temporary table 'todolist' is of type HEAP.

The sequence is something like
1. CREATE TEMPORARY TABLE todolist ....

2. Perform the statement
INSERT INTO todolist /* constant column list */ SELECT /* column list */ FROM
(thread, claim)
LEFT JOIN depot AS d1 ON claim.send_depot_no = d1.depot_no
LEFT JOIN depot AS d2 ON claim.responsable_depot_no = d2.depot_no
LEFT JOIN depot AS d3 ON claim.receive_depot_no = d3.depot_no
LEFT JOIN timer ON timer.fk_thread_id = thread.parent_thread_id
WHERE /* complex where clause */
AND /* variable additional where_conditions ORed together following a pattern like
OR (claim.fk_initiator_id=1448 AND thread.fk_user_id=0 AND thread.parent_thread_id=0)
*/
AND /* additional simple where conditions */
...

The problem is: sometimes statements like the one pointed out above under 2. fail with
error 1032: Can't find record in 'thread'

This happens repeatedly (although not frequently) but I haven't been able to figure out a pattern of the where clause triggering the error.
Even adding a LOCK IN SHARE MODE to the select part of the statement doesn't fix the problem.

Although we usually have several hundred users working concurrently
(and possibly, even likely modifying the tables being joined together in the questionable statement) the problem infrequently but repeatedly (usually only a few times per hour) where several hundred other statments of the same type succeed without problem.

Interestingly I never have been able to trigger the error when only performing the SELECT part of an INSERT ... SELECT copied out of our error logs.

This all makes me think that the select part and the insert part of the statement are treated by MySQL as two separate atomic operations.
Then something like this could happen:
The DBMS performs the SELECT part of the statement to gather the data to be inserted into the temp-table.
Suppose copying the gathered data is a separated atomic operation for MySQL. Then if some other user by accident happens to modify an entry in the table 'thread' in a way it no longer matches the where-condition of our select statement then the data is copied to the destination table, the resulting error would make sense. And it would explain why adding LOCK IN SHARE MODE doesn't make any difference because the lock would have been released before inserting
the found data starts.

Well this is just an assumption and I'm currently uncertain, wether this is a bug in the RDBMS or in the applicatoin's logic.

Any clue anyone about what's going on (and what the error really means btw)?
Any idea what could fix the problem?

BTW. I'm running MySQL 5.0.51a-community-log on 64-bit Linux (8 CPU). However the problem has been around on any older 5.0 Version of MySQL. CHECK TABLE on all source tables yielded OK status.