MySQL Bugs: #43861: main.query_cache_28249 fails sporadically

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

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

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.

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)

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.

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)

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)

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)

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)

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)

Re opening since the test is still disabled

http://trollheim.norway.sun.com/archive/2348637.disabled_tests.html

Ignore last post. It was directed to the wrong bug report.

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

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...

Changes to test suite. No changelog entry needed.

I don't know why this was closed, as with the latest bzr tree on launchpad I still see the test failures.

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.

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/

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!

At least disable the test. It's just embarrassing watching this cause so many failures.

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/

This isn't really fixed, only the test case has been disabled.