Bug #43861 main.query_cache_28249 fails sporadically
Submitted: 25 Mar 2009 15:52 Modified: 15 Feb 2012 7:44
Reporter: Alexander Nozdrin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Query Cache Severity:S3 (Non-critical)
Version:5.1 OS:Any
Assigned to: CPU Architecture:Any
Tags: disabled, pushbuild, sporadic, test failure

[25 Mar 2009 15:52] Alexander Nozdrin
Description:
main.query_cache_28249 fails sporadically.

See also Bug#41098, Bug#28249.

Symptoms:
@@ -46,7 +46,6 @@
 1	0
 2	0
 3	0
-4	0
 RESET QUERY CACHE;
 SELECT *, (SELECT COUNT(*) FROM t2) FROM t1;
 a	(SELECT COUNT(*) FROM t2)

How to repeat:
XRef: http://tinyurl.com/d3977f
[25 Mar 2009 16:03] 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/70375

2835 Alexander Nozdrin	2009-03-25
      Disable query_cache_28249 due to Bug#43861.
[25 Mar 2009 16:03] Bugs System
Pushed into 6.0.11-alpha (revid:alik@sun.com-20090325160309-5ki8ueoaxqcqgvl6) (version source revid:alik@sun.com-20090325160309-5ki8ueoaxqcqgvl6) (merge vers: 6.0.11-alpha) (pib:6)
[31 Mar 2009 8:19] Kristofer Pettersson
These are my thoughts on the matter:
Sanja  (original implementer)  claimed this is a bug because the result from QC should at any point in time reflect the actual data. I think what we see is that in some cases (when MyISAM uses concurrent insert) there is a difference between the result the storage engine returns and what QC thinks the storage engine will return. MyISAM has a callback hook through which it can invalidate a query or prevent it from being cached. There has been two serious attempts to fix this hook.

I'm not certain that it is a sane claim to say that QC at all times should be able to predict what the SE is going to return. We want the SE and cache to be loosely connected to that they can act more or less independently.

How can it be possible for QC to know what MyISAM will return before we've opened and locked the tables using the handler or at least made an equal attempt? We cache because we want to trade accuracy for speed.

By allowing for more false positives in the MyISAM hook it might still be able to fix this issue in a satisfying way. Potentially stupid question: Do we know when concurrently inserted rows are suppose to be visible globally in MyISAM? It seems for backup we ensure that concurrent is disabled to be able to re-produce an exact copy of the data.
[5 May 2009 19:38] Bugs System
Pushed into 5.1.35 (revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (version source revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (merge vers: 5.1.35) (pib:6)
[6 May 2009 14:08] Bugs System
Pushed into 6.0.12-alpha (revid:svoj@sun.com-20090506125450-yokcmvqf2g7jhujq) (version source revid:horst@mysql.com-20090327184517-25eq077q2beocs6y) (merge vers: 6.0.11-alpha) (pib:6)
[15 Jun 2009 8:25] Bugs System
Pushed into 5.1.35-ndb-6.3.26 (revid:jonas@mysql.com-20090615074202-0r5r2jmi83tww6sf) (version source revid:jonas@mysql.com-20090615070837-9pccutgc7repvb4d) (merge vers: 5.1.35-ndb-6.3.26) (pib:6)
[15 Jun 2009 9:04] Bugs System
Pushed into 5.1.35-ndb-7.0.7 (revid:jonas@mysql.com-20090615074335-9hcltksp5cu5fucn) (version source revid:jonas@mysql.com-20090615072714-rmfkvrbbipd9r32c) (merge vers: 5.1.35-ndb-7.0.7) (pib:6)
[15 Jun 2009 9:45] Bugs System
Pushed into 5.1.35-ndb-6.2.19 (revid:jonas@mysql.com-20090615061520-sq7ds4yw299ggugm) (version source revid:jonas@mysql.com-20090615054654-ebgpz7elwu1xj36j) (merge vers: 5.1.35-ndb-6.2.19) (pib:6)
[5 Oct 2010 5:41] Anitha Gopi
Re opening since the test is still disabled
[5 Oct 2010 5:42] Anitha Gopi
http://trollheim.norway.sun.com/archive/2348637.disabled_tests.html
[4 Jan 2011 10:31] Kristofer Pettersson
Ignore last post. It was directed to the wrong bug report.
[24 May 2011 4:23] Anitha Gopi
This bug was closed a while back, but the test was not enabled. Add the test to experimental group. Enable the test after monitoring it for sometime
[1 Aug 2011 21:38] Stewart Smith
I still see sporadic test failures. for example: http://jenkins.percona.com/job/mysql-5.1-trunk/BUILD_TYPE=debug-no-Werror,Host=ubuntu-natt...
[21 Sep 2011 1:46] Paul DuBois
Changes to test suite. No changelog entry needed.
[10 Oct 2011 6:46] Stewart Smith
I don't know why this was closed, as with the latest bzr tree on launchpad I still see the test failures.
[11 Oct 2011 12:01] Jon Olav Hauglid
Hello!

Is the test failing for you on 5.1 or 5.5?

This test was failing for separate reasons in 5.1 and 5.5+. The 5.5+ issue
was prioritized higher and should now be fixed (which is why this bug was 
closed). We are aware that the test still fails sporadically on 5.1.
[17 Oct 2011 5:51] Stewart Smith
5.1 - see previous comments.

You can at any point see our build+test of lp:mysql-server/5.1 at http://jenkins.percona.com/view/MySQL/job/mysql-5.1-trunk/
[10 Nov 2011 0:01] Clint Byrum
https://launchpadlibrarian.net/84808548/buildlog_ubuntu-precise-amd64.mysql-5.1_5.1.58-1ub...

We're seeing this on 5.1.58 as well, please re-open!
[10 Nov 2011 0:27] Stewart Smith
At least disable the test. It's just embarrassing watching this cause so many failures.
[15 Feb 2012 7:44] Jon Stephens
Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/
[16 Feb 2012 4:18] Stewart Smith
This isn't really fixed, only the test case has been disabled.