Bug #38094 query_cache works fine on busy CPUs only
Submitted: 14 Jul 2008 9:48 Modified: 15 Oct 2008 8:41
Reporter: Gleb Shchepa Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Query Cache Severity:S3 (Non-critical)
Version:5.1+ OS:Any (SMP)
Assigned to: Assigned Account CPU Architecture:Any
Tags: wrong result

[14 Jul 2008 9:48] Gleb Shchepa
Description:
Probably this problem never occurs on busy CPUs, otherwise it fails (at the
lease on multicore CPUs) with unexpected result:

$ ./mtr query_cache
[...]
=======================================================

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

main.query_cache               [ fail ]

--- /work/bzr/mysql-5.1-bugteam/mysql-test/r/query_cache.result	2008-07-09 22:38:15.000000000 +0300
+++ /work/bzr/mysql-5.1-bugteam/mysql-test/r/query_cache.reject	2008-07-14 12:43:01.000000000 +0300
@@ -1641,7 +1641,6 @@
 1	0
 2	0
 3	0
-4	0
 reset query cache;
 select *, (select count(*) from t2) from t1;
 a	(select count(*) from t2)

mysqltest: Result content mismatch

How to repeat:
Please try "./mtr query_cache" on 4 core CPU/Linux x86_32 with a low load average.
[25 Jul 2008 14:41] BJ Dierkes
Verified same issue on:

Redhat Enterprise Linux 4ES and 5Server i386/x86_86
Single CPU (Single Core)
1G RAM
IDE Disk Drive

Failed on all 4 boxes....  was not failing with 5.1.25-rc ... only since going to 5.1.26-rc.
[15 Oct 2008 8:38] Kristofer Pettersson
The test was written on my laptop (dual core) with (very) low CPU load.

The result indicates that the snapshot created in the SE (MyISAM) isn't consistent with the expected transaction level. Ie query cache does not become aware that the SE has inserted new data.
[15 Oct 2008 8:39] Kristofer Pettersson
Reference http://bugs.mysql.com/bug.php?id=39249