MySQL Bugs: #14767: INSERT in SF + concurrent SELECTs may result in wrong data in query cache

Bug #14767	INSERT in SF + concurrent SELECTs may result in wrong data in query cache
Submitted:	8 Nov 2005 19:49	Modified:	6 Apr 2006 15:08
Reporter:	Dmitri Lenev
Status:	Closed
Category:	Server	Severity:	S2 (Serious)
Version:	5.0.17-BK, 5.0.16-BK	OS:	Linux (Linux/x86, MacOSX)
Assigned to:	Bugs System	Target Version:

Description:
Execution of stored function(trigger) which inserts data into table while concurrently
running selects on the same table may result in storing wrong data in query cache. See how
to repeat section for details. I also suspect that under high load similar problem can
occur for ordinary insert (which is not used in stored function).

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);
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;

Thank you for a bug report. Verified just as described on 5.0.17-BK (ChangeSet@1.1967,
2005-11-11 13:39:46+04:00,...) on Linux: 

[openxs@Fedora mysql-test]$ mysqltest -r -uroot --host=127.0.0.1 --port=3306
--result-file=r/14767.result < t/14767.test
ok
[openxs@Fedora mysql-test]$ cat r/14767.result
set global query_cache_size=1355776;
flush query cache;
use test;
drop function if exists f;
drop table if exists t;
create table t (id int);
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;|
 select f();
select sleep(6);
sleep(6)
0
select * from t;
id
f()
0
select * from t;
id
[openxs@Fedora mysql-test]$ cd ..
[openxs@Fedora 5.0]$ bin/mysql -uroot test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18 to server version: 5.0.17

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> desc t;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select * from t;
Empty set (0.00 sec)

mysql> select f();
+------+
| f()  |
+------+
|    0 |
+------+
1 row in set (10.02 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    3 |
|    3 |
+------+
2 rows in set (0.00 sec)

mysql> exit
Bye
[openxs@Fedora 5.0]$ uname -a
Linux Fedora 2.4.22-1.2115.nptl #1 Wed Oct 29 15:42:51 EST 2003 i686 i686 i386 GNU/Linux

Fixed status of the bug after re-assignment.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/3408

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/3864

pushed to 5.0.20

changed mode of locking table in which we are inserting (if we have query cache compiled
in and insert statement is inside SP/SF we do not allow other thread to see this table
(MyISAM) even before unlock) to privent incorrect result from query cache

merged to 5.1.8 (thanks gluh)

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented bugfix in 5.1.8 changelog. Closed.

While this bug has been fixed for MyISAM, it seems to remain with InnoDB. Just perform the
same test with engine=innodb.

The "select * from t1" produces an empty set until "reset query cache;" is executed.

thank you for information, I registered BUG#23802 about it. Please refer to it to trace
progress of bugfixing.
http://bugs.mysql.com/bug.php?id=23802