MySQL Bugs: #36692: Query cache only works with cluster if AUTOCOMMIT is active

Bug #36692	Query cache only works with cluster if AUTOCOMMIT is active
Submitted:	13 May 2008 15:33	Modified:	2 Jul 2008 11:27
Reporter:	Hartmut Holzgraefe	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	5.1.23-ndb-6.3.10	OS:	Linux
Assigned to:	Magnus Blåudd	CPU Architecture:	Any

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

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.

Pushed into 5.1.24-ndb-6.2.16

Pushed into 5.1.23-ndb-6.4.0

Pushed into 5.1.24-ndb-6.3.16

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.

Pushed into 6.0.6-alpha  (revid:sp1r-msvensson@shellback.(none)-20080528142626-09441) (version source revid:jonas@mysql.com-20080808094047-4e1yiarqa2t3opg3) (pib:5)