Re: Error 1032 Can't find record in ...
Phil Hildebrand Wrote:
-------------------------------------------------------
> What is the transaction_isolation level you have
> set for innodb ?
Transaction_isolation is default, that is REPEATABLE-READ
> It's possible that could be related... But maybe
> it's actually related to the temp table being a
> heap, and not an innodb table, so it doesn't
> enforce a consistent read as expected?
I will try that suggestion although for me it seems more to be a problem of the source tables than one of the target.
>
http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html
>
> " ...InnoDB uses a consistent read for select in
> clauses like INSERT INTO ... SELECT and UPDATE ...
> (SELECT) that do not specify FOR UPDATE or IN
> SHARE MODE if the innodb_locks_unsafe_for_binlog
> option is set and the isolation level of the
> transaction is not set to serializable. Thus no
> locks are set to rows read from selected table.
> ..."
Well, both conditions are false. innodb_locks_unsafe_for_binlog is OFF and the statement does use "IN SHARE MODE".
As far as I understand, the next sentences from the docs would apply then:
"Otherwise, InnoDB uses stronger locks and the SELECT part acts like READ COMMITTED, where each consistent read, even within the same transaction, sets and reads its own fresh snapshot."
For my understanding, this should ensure consistent reads, but obviously it doesn't.