Bug #36692 Query cache only works with cluster if AUTOCOMMIT is active
Submitted: 13 May 2008 17:33 Modified: 2 Jul 2008 13:27
Reporter: Hartmut Holzgraefe
Status: Closed
Category:Server: Cluster Severity:S3 (Non-critical)
Version:5.1.23-ndb-6.3.10 OS:Linux
Assigned to: Magnus Blaudd Target Version:
Triage: D3 (Medium)

[13 May 2008 17:33] Hartmut Holzgraefe 
Description:
Query cache only works with cluster if AUTOCOMMIT is active,
if either AUTOCOMMIT is set to 0 or an explicit transaction
is started with BEGIN TRANSACTION queries are no longer 
cached ...

How to repeat:
Set up a system with active query cache, then execute:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id int primary key) ENGINE=ndb;
INSERT INTO t1 VALUES(1);

SET AUTOCOMMIT=1;

FLUSH STATUS;

SELECT SQL_CACHE * FROM t1;
SHOW STATUS LIKE "Qcache_hits";
SHOW STATUS LIKE "Qcache_inserts";
SHOW STATUS LIKE "Qcache_not_cached";

SELECT SQL_CACHE * FROM t1;
SHOW STATUS LIKE "Qcache_hits";
SHOW STATUS LIKE "Qcache_inserts";
SHOW STATUS LIKE "Qcache_not_cached";

SET AUTOCOMMIT=0;

FLUSH STATUS;

SELECT SQL_CACHE * FROM t1;
SHOW STATUS LIKE "Qcache_hits";
SHOW STATUS LIKE "Qcache_inserts";
SHOW STATUS LIKE "Qcache_not_cached";

SELECT SQL_CACHE * FROM t1;
SHOW STATUS LIKE "Qcache_hits";
SHOW STATUS LIKE "Qcache_inserts";
SHOW STATUS LIKE "Qcache_not_cached";

== Expected Result ==

id
1
Variable_name   Value
Qcache_hits     0
Variable_name   Value
Qcache_inserts  1
Variable_name   Value
Qcache_not_cached       0
id
1
Variable_name   Value
Qcache_hits     1
Variable_name   Value
Qcache_inserts  1
Variable_name   Value
Qcache_not_cached       0
id
1
Variable_name   Value
Qcache_hits     0
Variable_name   Value
Qcache_inserts  1
Variable_name   Value
Qcache_not_cached       0
id
1
Variable_name   Value
Qcache_hits     1
Variable_name   Value
Qcache_inserts  1
Variable_name   Value
Qcache_not_cached       0

== Actual Result ==

id
1
Variable_name   Value
Qcache_hits     0
Variable_name   Value
Qcache_inserts  1
Variable_name   Value
Qcache_not_cached       0
id
1
Variable_name   Value
Qcache_hits     1
Variable_name   Value
Qcache_inserts  1
Variable_name   Value
Qcache_not_cached       0
id
1
Variable_name   Value
Qcache_hits     0
Variable_name   Value
Qcache_inserts  0
Variable_name   Value
Qcache_not_cached       1
id
1
Variable_name   Value
Qcache_hits     0
Variable_name   Value
Qcache_inserts  0
Variable_name   Value
Qcache_not_cached       2

== Diff ==

@@ -19,14 +19,14 @@
 Variable_name  Value
 Qcache_hits    0
 Variable_name  Value
-Qcache_inserts 1
+Qcache_inserts 0
 Variable_name  Value
-Qcache_not_cached      0
+Qcache_not_cached      1
 id
 1
 Variable_name  Value
-Qcache_hits    1
+Qcache_hits    0
 Variable_name  Value
-Qcache_inserts 1
+Qcache_inserts 0
 Variable_name  Value
-Qcache_not_cached      0
+Qcache_not_cached      2

Suggested fix:
Either make sure results are cached independent of transaction status or clearly document
this limitation and the reasons behind this behavior
[28 May 2008 16:28] 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/47146

ChangeSet@1.2597, 2008-05-28 16:26:26+02:00, msvensson@shellback.(none) +4 -0
  Bug#36692 Query cache only works with cluster if AUTOCOMMIT is active
   - Allow results to be taken out of the query cache as long
  as current transaction hasn't changed the table.
[30 May 2008 13:30] Bugs System 
Pushed into 5.1.24-ndb-6.2.16
[30 May 2008 15:22] Bugs System 
Pushed into 5.1.23-ndb-6.4.0
[30 May 2008 15:23] Bugs System 
Pushed into 5.1.24-ndb-6.3.16
[2 Jul 2008 13:27] Jon Stephens 
Documented in the NDB 6.2.16 and 6.3.16 changelogs as follows:

        Queries against NDBCLUSTER tables were cached only if
        AUTOCOMMIT was in use.
[13 Dec 2008 0:25] Bugs System 
Pushed into 6.0.6-alpha  (revid:sp1r-msvensson@shellback.(none)-20080528142626-09441)
(version source revid:jonas@mysql.com-20080808094047-4e1yiarqa2t3opg3) (pib:5)