Bug #23802 INSERT (innodb) SF + concurrent SELECTs may result in wrong data in query cache
Submitted: 31 Oct 2006 14:00 Modified: 15 Nov 2006 19:57
Reporter: Oleksandr Byelkin
Status: Duplicate
Category:Server Severity:S2 (Serious)
Version:5.0 OS:Any (all)
Assigned to: Bugs System Target Version:

[31 Oct 2006 14: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 15: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 19: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.