MySQL Bugs: #38094: query_cache works fine on busy CPUs only

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

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.

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.

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.

Reference http://bugs.mysql.com/bug.php?id=39249