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:
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
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;
drop function if exists f;
drop table if exists t;
create table t (id int) engine=innodb;
delimiter |;
create function f ()
  returns int
  declare i_var int;
  set i_var = sleep(5);
  insert t values(3);
  set i_var = sleep(5);
  return 0;
delimiter ;|

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

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

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


[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.