Bug #23802 INSERT (innodb) SF + concurrent SELECTs may result in wrong data in query cache
Submitted: 31 Oct 2006 13:00 Modified: 15 Nov 2006 18:57
Reporter: Oleksandr Byelkin Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0 OS:Any (all)
Assigned to: Assigned Account CPU Architecture:Any

[31 Oct 2006 13:00] Oleksandr Byelkin
Description:
Execution of stored function(trigger) which inserts data into innodb table while concurrently running selects on the same table may result in storing wrong data in query cache.

How to repeat:
#  Script for mysqltest which demonstrates problem
set global query_cache_size=1355776;
flush query cache;
--disable_warnings
drop function if exists f;
drop table if exists t;
--enable_warnings
create table t (id int) engine=innodb;
delimiter |;
create function f ()
  returns int
begin
  declare i_var int;
  set i_var = sleep(5);
  insert t values(3);
  set i_var = sleep(5);
  return 0;
end;|
delimiter ;|

connect (con1,localhost,root,,);
connect (con2,localhost,root,,);

connection con1;
send select f();
connection con2;
select sleep(6);
select * from t;
connection con1;
reap;
connection con2;
# This gives wrong result i.e. 't' table seems to be empty
select * from t;
[31 Oct 2006 14:19] Heikki Tuuri
Sanja,

it is the MySQL code that should invalidate the query cache immediately when the INSERT is processed. Why that does not happen?

Regards,

Heikki
[15 Nov 2006 18:57] Tomash Brechko
This bug is a duplicate of bug#12713.

The bug is repeatable without query cache.  The cause that the final SELECT * FROM t; returns empty result is not because it was cached, but because InnoDB transaction wasn't committed yet, and this is the subject of bug#12713.