Bug #33756 query cache with concurrent_insert=0 appears broken
Submitted: 9 Jan 2008 5:10 Modified: 27 Mar 2008 18:50
Reporter: Sean Pringle Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Query Cache Severity:S3 (Non-critical)
Version:5.0.54, 5.0.50 OS:Any
Assigned to: Ingo Strüwing CPU Architecture:Any
Tags: concurrent_insert, query cache, regression

[9 Jan 2008 5: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 2008 5:43] Valeriy Kravchuk 
Thank you for a bug report. Verified just as described with 5.0.54.
[12 Mar 2008 12: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 2008 17: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 2008 13: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 2008 15: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 2008 11:28] Ingo Strüwing 
Queued to mysql-6.0-engines-merge, mysql-5.1-engines-merge, mysql-5.0-engines-merge.
[27 Mar 2008 11:17] Bugs System 
Pushed into 5.1.24-rc
[27 Mar 2008 11:20] Bugs System 
Pushed into 5.0.60
[27 Mar 2008 17:49] Bugs System 
Pushed into 6.0.5-alpha
[27 Mar 2008 18: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 2008 16:54] Jon Stephens 
Also noted in the 5.1.23-ndb-6.3.11 changelog.