MySQL Bugs: #23802: INSERT (innodb) SF + concurrent SELECTs may result in wrong data in query cache

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

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;

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

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.