Bug #14767 | INSERT in SF + concurrent SELECTs may result in wrong data in query cache | ||
---|---|---|---|
Submitted: | 8 Nov 2005 18:49 | Modified: | 6 Apr 2006 13:08 |
Reporter: | Dmitry Lenev | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server | Severity: | S2 (Serious) |
Version: | 5.0.17-BK, 5.0.16-BK | OS: | Linux (Linux/x86, MacOSX) |
Assigned to: | Oleksandr Byelkin | CPU Architecture: | Any |
[8 Nov 2005 18:49]
Dmitry Lenev
[13 Nov 2005 14:33]
Valeriy Kravchuk
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
[16 Jan 2006 6:18]
Oleksandr Byelkin
Fixed status of the bug after re-assignment.
[3 Mar 2006 0:07]
Bugs System
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
[15 Mar 2006 17:16]
Bugs System
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
[17 Mar 2006 7:50]
Oleksandr Byelkin
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
[23 Mar 2006 8:11]
Oleksandr Byelkin
merged to 5.1.8 (thanks gluh)
[6 Apr 2006 13:08]
Jon Stephens
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.
[30 Oct 2006 9:56]
Paul McCullagh
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.
[31 Oct 2006 13:02]
Oleksandr Byelkin
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