MySQL Bugs: #5683: Update delayed

Bug #5683	Update delayed
Submitted:	21 Sep 2004 16:14	Modified:	27 Oct 2004 13:36
Reporter:	Taco van den Broek	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.1.4-gamma-nt	OS:	Windows (Windows XP)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
Using PHP5 and MySQLi I've modelled a tree in an InnoDB database. The primary table consists of 18 columns of which 4 are foreign keys to fairly small tables (4 to 6 columns 0 to 2 foreign key constraints). My code only uses buffered queries.

The situation is as follows:
 - In PHP I generate the tree.
 - I do a change to one of the rows in the main table.
 - I header back to the treeview and the updated row disappears from my result set of which I build my tree.
 - After a reload which only triggers select queries my row is back in the result set.

The only way to stop this behaviour was to let the script sleep a second before headering back to the tree.

The behaviour suggests that the update query is delayed untill after the select query, but why does the row completely disappear? Shouldn't it show the row but with the 'old' values?

How to repeat:
I haven't been able to build a minimal test case since this structure is a part of a larger project and making a 'lighter' example doesn't trigger this behaviour.

We need a repeatable test case.

Are you aware how InnoDB does consistent reads? Maybe the behavior is as documented?

Regards,

Heikki

I am aware of the consistent reads of InnoDB which could not have been the case here, could have because the bug doesn't seem to occur anymore. We've updated to a newer PHP and MySQL version (4.1.5 gamma) and we haven't seen this behaviour since.
The reason that this couldn't have been designed behaviour is that, when the bug occurred, a row was completely missing. Would it be designed behaviour, the row should be visible with either the old, or the updated data.