Bug #33756 query cache with concurrent_insert=0 appears broken
Submitted: 9 Jan 6:10 Modified: 27 Mar 19:50
Reporter: Sean Pringle
Status: Closed
Category:Server: Query Cache Severity:S3 (Non-critical)
Version:5.0.54, 5.0.50 OS:Any
Assigned to: Ingo Strüwing Target Version:5.0+
Tags: regression, query cache, concurrent_insert
Triage: D3 (Medium) / R2 (Low) / E2 (Low)

[9 Jan 6:10] Sean Pringle 
Description:
With concurrent_insert=0, some queries that should be inserted into the query cache are
not.  This apparently represents a change in behavior between 5.0.46 and 5.0.50.

concurrent_insert=1 (default) or =2 appear not to be affected.

How to repeat:
Start MySQL with:

[mysqld]
query_cache_size=8M
concurrent_insert=0

Run the following:

SELECT VERSION();
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (f1 int NOT NULL) ENGINE=MyISAM;
INSERT INTO t1 (f1) VALUES (1), (2);

SHOW GLOBAL VARIABLES LIKE 'concurrent%';
SHOW STATUS LIKE 'Qc%';
SELECT * FROM t1;
SELECT * FROM t1;
SHOW STATUS LIKE 'Qc%';

Result in 5.0.50 through 5.0.54:

+-----------------------+
| VERSION()             |
+-----------------------+
| 5.0.54-enterprise-gpl | 
+-----------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| concurrent_insert | 0     | 
+-------------------+-------+
1 row in set (0.00 sec)

+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       | 
| Qcache_free_memory      | 8379864 | 
| Qcache_hits             | 0       | 
| Qcache_inserts          | 0       | 
| Qcache_lowmem_prunes    | 0       | 
| Qcache_not_cached       | 4       | 
| Qcache_queries_in_cache | 0       | 
| Qcache_total_blocks     | 1       | 
+-------------------------+---------+
8 rows in set (0.00 sec)

+----+
| f1 |
+----+
|  1 | 
|  2 | 
+----+
2 rows in set (0.00 sec)

+----+
| f1 |
+----+
|  1 | 
|  2 | 
+----+
2 rows in set (0.00 sec)

+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       | 
| Qcache_free_memory      | 8379864 | 
| Qcache_hits             | 0       | 
| Qcache_inserts          | 0       | 
| Qcache_lowmem_prunes    | 0       | 
| Qcache_not_cached       | 7       | 
| Qcache_queries_in_cache | 0       | 
| Qcache_total_blocks     | 1       | 
+-------------------------+---------+
8 rows in set (0.00 sec)

Result in 5.0.46:

+-----------------------+
| VERSION()             |
+-----------------------+
| 5.0.46-enterprise-gpl | 
+-----------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| concurrent_insert | 0     | 
+-------------------+-------+
1 row in set (0.00 sec)

+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       | 
| Qcache_free_memory      | 8379864 | 
| Qcache_hits             | 0       | 
| Qcache_inserts          | 0       | 
| Qcache_lowmem_prunes    | 0       | 
| Qcache_not_cached       | 4       | 
| Qcache_queries_in_cache | 0       | 
| Qcache_total_blocks     | 1       | 
+-------------------------+---------+
8 rows in set (0.00 sec)

+----+
| f1 |
+----+
|  1 | 
|  2 | 
+----+
2 rows in set (0.00 sec)

+----+
| f1 |
+----+
|  1 | 
|  2 | 
+----+
2 rows in set (0.00 sec)

+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       | 
| Qcache_free_memory      | 8378328 | 
| Qcache_hits             | 1       | 
| Qcache_inserts          | 1       | 
| Qcache_lowmem_prunes    | 0       | 
| Qcache_not_cached       | 5       | 
| Qcache_queries_in_cache | 1       | 
| Qcache_total_blocks     | 4       | 
+-------------------------+---------+
8 rows in set (0.00 sec)

Suggested fix:
Unknown.  Change seems to have occurred around the same time as Bug #28249 was patched,
but may be just coincidence.
[9 Jan 6:43] Valeriy Kravchuk 
Thank you for a bug report. Verified just as described with 5.0.54.
[12 Mar 13:25] Kristofer Pettersson 
Progress report: The query cache rejects the queries because of MyISAM handler through the
handler interface register_query_cache_table. In the MyISAM implementation this function
only allows caching if the 'data file size' hasn't changed from the 'thread local data
file size.' This is apparently not good enough when myisam_concurrent_insert=0.
[12 Mar 18:03] 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/43867

ChangeSet@1.2594, 2008-03-12 18:02:59+01:00, istruewing@stella.local +3 -0
  Bug#33756 - query cache with concurrent_insert=0 appears broken
  
  When concurrent inserts were disabled, statements after an INSERT
  were not put into the query cache. This happened because we do not
  save the current data file length at statement start when
  concurrent inserts are disabled. But we checked the always zero
  local length against the real file length anyway.
  
  Fixed by doing the check only if concurrent inserts are not diabled.
[13 Mar 14:53] Kristofer Pettersson 
Looks good.
I think the test case is more stable if you only use "SHOW STATUS LIKE 'Qcache_hits'"
instead of wildcards.
[13 Mar 16:39] 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/43927

ChangeSet@1.2596, 2008-03-13 16:39:27+01:00, istruewing@stella.local +3 -0
  Bug#33756 - query cache with concurrent_insert=0 appears broken
  
  When concurrent inserts were disabled, statements after an INSERT
  were not put into the query cache. This happened because we do not
  save the current data file length at statement start when
  concurrent inserts are disabled. But we checked the always zero
  local length against the real file length anyway.
    
  Fixed by doing the check only if concurrent inserts are not diabled.
[17 Mar 12:28] Ingo Strüwing 
Queued to mysql-6.0-engines-merge, mysql-5.1-engines-merge, mysql-5.0-engines-merge.
[27 Mar 12:17] Bugs System 
Pushed into 5.1.24-rc
[27 Mar 12:20] Bugs System 
Pushed into 5.0.60
[27 Mar 18:49] Bugs System 
Pushed into 6.0.5-alpha
[27 Mar 19:50] Paul DuBois 
Noted in 5.0.60, 5.1.24, 6.0.5 changelogs.

Disabling concurrent inserts caused some cacheable queries not to be
saved in the query cache.
[2 Apr 18:54] Jon Stephens 
Also noted in the 5.1.23-ndb-6.3.11 changelog.