MySQL Bugs: #39295: cluster table with TEXT column cannot be cached in query cache

Bug #39295	cluster table with TEXT column cannot be cached in query cache
Submitted:	7 Sep 2008 14:35	Modified:	22 Oct 2008 12:57
Reporter:	chris lin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S5 (Performance)
Version:	6.2.15-com	OS:	Any (linux 2.6)
Assigned to:	Frazer Clement	CPU Architecture:	Any
Tags:	cluster, query cache, text

Description:
the query cache works incorrectly together with the cluster table that include a TEXT column. some of the queries cannot be cached, and some of them can. Not found any rules that can decide what cache and what not cache. 
1. not cache
mysql> show create table t6;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                    |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t6    | CREATE TABLE `t6` (
  `id` int(11) NOT NULL,
  `a` text NOT NULL,
  `b` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=utf8 | 
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t6;
+----+--------+---+
| id | a      | b |
+----+--------+---+
|  1 | alkdjf | 2 | 
|  2 | alkdjf | 5 | 
+----+--------+---+
2 rows in set (0.01 sec)

mysql> show status like 'qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 2        | 
| Qcache_free_memory      | 16747680 | 
| Qcache_hits             | 33       | 
| Qcache_inserts          | 110      | 
| Qcache_lowmem_prunes    | 0        | 
| Qcache_not_cached       | 4        | 
| Qcache_queries_in_cache | 12       | 
| Qcache_total_blocks     | 37       | 
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> select * from t6;
+----+--------+---+
| id | a      | b |
+----+--------+---+
|  1 | alkdjf | 2 | 
|  2 | alkdjf | 5 | 
+----+--------+---+
2 rows in set (0.00 sec)

mysql> select * from t6;
+----+--------+---+
| id | a      | b |
+----+--------+---+
|  1 | alkdjf | 2 | 
|  2 | alkdjf | 5 | 
+----+--------+---+
2 rows in set (0.00 sec)

mysql> show status like 'qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 2        | 
| Qcache_free_memory      | 16747680 | 
| Qcache_hits             | 33       | 
| Qcache_inserts          | 112      | 
| Qcache_lowmem_prunes    | 0        | 
| Qcache_not_cached       | 4        | 
| Qcache_queries_in_cache | 12       | 
| Qcache_total_blocks     | 37       | 
+-------------------------+----------+
8 rows in set (0.01 sec)

2. cache
mysql> select * from t6 where id=1;
+----+--------+---+
| id | a      | b |
+----+--------+---+
|  1 | alkdjf | 2 | 
+----+--------+---+
1 row in set (0.00 sec)

mysql> show status like 'qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 3        | 
| Qcache_free_memory      | 15987768 | 
| Qcache_hits             | 1        | 
| Qcache_inserts          | 24       | 
| Qcache_lowmem_prunes    | 0        | 
| Qcache_not_cached       | 3        | 
| Qcache_queries_in_cache | 4        | 
| Qcache_total_blocks     | 15       | 
+-------------------------+----------+
8 rows in set (0.01 sec)

mysql> select * from t6 where id=1;
+----+--------+---+
| id | a      | b |
+----+--------+---+
|  1 | alkdjf | 2 | 
+----+--------+---+
1 row in set (0.00 sec)

mysql> select * from t6 where id=1;
+----+--------+---+
| id | a      | b |
+----+--------+---+
|  1 | alkdjf | 2 | 
+----+--------+---+
1 row in set (0.00 sec)

mysql> show status like 'qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 3        | 
| Qcache_free_memory      | 15987768 | 
| Qcache_hits             | 3        | 
| Qcache_inserts          | 24       | 
| Qcache_lowmem_prunes    | 0        | 
| Qcache_not_cached       | 3        | 
| Qcache_queries_in_cache | 4        | 
| Qcache_total_blocks     | 15       | 
+-------------------------+----------+
8 rows in set (0.01 sec)

How to repeat:
create a cluster table as above, insert some records, and execute as above.

Thanks for the bug report.

The bug as been reproduced, and root cause identified.

Fix is in progress.

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/56763

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/56765

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/56766

Fix pushed to 6.2.17, 6.3.19, 6.4.0

Documented bugfix in the ndb-6.2.17 and ndb-6.3.19 changelogs as follows:

        The MySQL Query Cache did not function correctly with NDBCLUSTER tables
        containing TEXT columns.

Pushed into 5.1.28-ndb-6.2.17  (revid:frazer@mysql.com-20081021225332-iacutj4ewkrh39gl) (version source revid:frazer@mysql.com-20081021225332-iacutj4ewkrh39gl) (pib:5)

Pushed into 5.1.28-ndb-6.3.19  (revid:frazer@mysql.com-20081021225332-iacutj4ewkrh39gl) (version source revid:frazer@mysql.com-20081021230334-qnfrlwsb8izkufld) (pib:5)

Pushed into 5.1.28-ndb-6.4.0  (revid:frazer@mysql.com-20081021225332-iacutj4ewkrh39gl) (version source revid:frazer@mysql.com-20081021230625-mec1ik1fng48k6j1) (pib:5)

Pushed into 6.0.9-alpha  (revid:frazer@mysql.com-20081021225332-iacutj4ewkrh39gl) (version source revid:tomas.ulin@sun.com-20081209185954-9svcixh2p5hsfi6w) (pib:5)