Bug #9549 Use of blocks is incorrect by query cache in embedded server
Submitted: 1 Apr 2005 2:13 Modified: 18 May 2005 2:02
Reporter: Jim Winstead Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Embedded Library ( libmysqld ) Severity:S2 (Serious)
Version:4.1 OS:
Assigned to: Jim Winstead CPU Architecture:Any

[1 Apr 2005 2:13] Jim Winstead
When more than one block is used to store the result of a query in the query cache, the Querycache_stream object does not correctly use multiple blocks. The Querycache_stream::use_next_block() method never actually uses anything other than the first block.

How to repeat:
# We just want a small query cache, so we can fragment it easily
set GLOBAL query_cache_size=64*1024;
# This actually gives us a usable cache size of about 48K

# Each table is about 14K
create table t1 (a text);
insert into t1 values (repeat('abcdefghijklmnopqrstuvwxyz', 550));
create table t2 (a text);
insert into t2 values (repeat('ijklmnopqrstuvwxyzabcdefgh', 550));

# Load a query from each table into the query cache
select a from t1; # Q1
select a from t2; # Q2
show status like 'Qcache_%_blocks';

# Now the cache looks like (14K for Q1)(14K for Q2)(20K free)

# Flush Q1 from the cache by adding an out-of-order chunk to t1
insert into t1 select reverse(a) from t1;
show status like 'Qcache_%_blocks';

# Now the cache looks like (14K free)(14K for Q2)(20K free)

# Load our new data into the query cache
select a from t1;  # Q3
show status like 'Qcache_%_blocks';

# Now the cache should be like (14K for Q3)(14K for Q2)(14K for Q3)(6K free)

# Note that Q3 is split across two chunks!

# Load Q3 from the cache, and actually pay attention to the results
select a from t1;

flush query cache;

drop table t1, t2;

Suggested fix:
Querycache_stream::use_next_block() needs to be fixed to actually make use of additional blocks and mark them appropriately. Querycache_stream::store_str_only() and Querycache_stream::load_str_only() also have off-by-one errors that cause them to call use_next_block() at the wrong time.
[1 Apr 2005 3:16] 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:

[2 May 2005 19:07] Jim Winstead
Fixed in 4.1.12 and 5.0.6.
[18 May 2005 2:02] Paul DuBois
Noted in 4.1.12, 5.0.6 changelogs.