MySQL Bugs: #43861: main.query_cache_28249 fails sporadically

Bug #43861	main.query_cache_28249 fails sporadically
Submitted:	25 Mar 2009 15:52	Modified:	22 Nov 2011 13:21
Reporter:	Alexander Nozdrin	Email Updates:
Status:	Verified
Category:	Server: Query Cache	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:		Target Version:	5.1+
Tags:	pushbuild, test failure, sporadic, disabled
Triage:	Triaged: D2 (Serious) / R2 (Low) / E3 (Medium)

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.