Bug #49369 No testcase for key caches on partitions
Submitted: 2 Dec 2009 20:59 Modified: 3 Jan 2010 17:26
Reporter: Jon Stephens Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.5.0-bzr OS:Linux (opensuse11.1/64)
Assigned to: Mattias Jonsson CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[2 Dec 2009 20:59] Jon Stephens
Description:
Although WL#4571 shows that it should be possible to assign a key cache to one or more individual partitions either by name or by using 'all', all attempts to do so fail with a syntax error.

However, it is possible to assign an index on a partitioned table to a key cache without referring to any partitions. 

How to repeat:
Attaching SQL script that demonstrates the problem.

Summary: Only the versions of the CACHE INDEX and LOAD INDEX INTO CACHE statements that do not refer to any partitions are successful.

Suggested fix:
Get rid of syntax errors when attempting to execute the partition-specific versions of CACHE INDEX and LOAD INDEX INTO CACHE.
[2 Dec 2009 21:02] Jon Stephens
BUG#49369 - SQL script with test cases

Attachment: bug49369.sql (text/x-sql), 2.60 KiB.

[2 Dec 2009 21:39] Mattias Jonsson
Seems like the test case file partition_key_cache.test is missing too.
[2 Dec 2009 21:53] Jon Stephens
My bad. Thanks, Elena!
[2 Dec 2009 21:57] Mattias Jonsson
The syntax should be as it is stated in the worklog:
      CACHE INDEX keycache_list|keycache_parts IN key_cache_name
      
      keycache_list:
      tbl_name [INDEX|KEY (index_name[, index_name] ...)][, tbl_name ...]
      
      keycache_parts:
      tbl_name PARTITION (ALL|partition[, partition] ...)
      [INDEX|KEY (index_name[, index_name] ...)]
      
      
      LOAD INDEX INTO CACHE tbl_index_list|parts_index_list
      
      tbl_index_list:
      tbl_name [INDEX|KEY (index_name[, index_name] ...)]
      [INGORE LEAVES][, tbl_name ...]
      
      parts_index_list:
      tbl_name PARTITION (ALL|partition[, partition] ...)
      [INDEX|KEY (index_name[,index_name] ...)
      [IGNORE LEAVES]

So when one wants to specify partition use:
CACHE INDEX tbl_name PARTITION (p0) IN key_cache_name
LOAD INDEX INTO CACHE tbl_name PARTITION (p0)
(Note the parentheses!)

And I though I followed the documentation standard as seen in the manual :)

If OK by you, I would like to add the tests as the fix for this test, since that would
give an example of how to use it.
[2 Dec 2009 22:10] Jon Stephens
Ok, Mattias, I'm setting to Verified and assigning to you. Not sure who the lead should be, can you fix that (and the Synopsis)?
[3 Dec 2009 10:55] Mattias Jonsson
I changed the synopsis from 'cannot assign key caches per partition' to 'No testcase for key caches on partitions' since that is the current problem.

I will commit the original test case that was somehow forgotten in the merge process.
[3 Dec 2009 12:25] 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/92668

2911 Mattias Jonsson	2009-12-03
      Bug#49369: No testcase for key caches on partitions
      
      The original test case was lost when
      merging WL#4571.
      
      Added the testcase.
     @ mysql-test/include/check_key_reads.inc
        Bug#49369: No testcase for key caches on partitions
        
        New include file for testing key caches on partitions
        WL#4571
     @ mysql-test/include/check_key_req.inc
        Bug#49369: No testcase for key caches on partitions
        
        New include file for testing key caches on partitions
        WL#4571
     @ mysql-test/r/partition_key_cache.result
        Bug#49369: No testcase for key caches on partitions
        
        New test case for testing key caches on partitions
        WL#4571
     @ mysql-test/t/partition_key_cache.test
        Bug#49369: No testcase for key caches on partitions
        
        New test case for testing key caches on partitions
        WL#4571
[3 Dec 2009 12:32] 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/92669

2911 Mattias Jonsson	2009-12-03
      Bug#49369: No testcase for key caches on partitions
      
      The original test case was lost when
      merging WL#4571.
      
      Added the testcase.
     @ mysql-test/include/check_key_reads.inc
        Bug#49369: No testcase for key caches on partitions
        
        New include file for testing key caches on partitions
        WL#4571
     @ mysql-test/include/check_key_req.inc
        Bug#49369: No testcase for key caches on partitions
        
        New include file for testing key caches on partitions
        WL#4571
     @ mysql-test/r/partition_key_cache.result
        Bug#49369: No testcase for key caches on partitions
        
        New test case for testing key caches on partitions
        WL#4571
     @ mysql-test/t/partition_key_cache.test
        Bug#49369: No testcase for key caches on partitions
        
        New test case for testing key caches on partitions
        WL#4571
[9 Dec 2009 11:40] Mattias Jonsson
From Philip:
Matthias,

Bug #49369 No testcase for key caches on partitions is OK to push. I do not think two reviews are necessary.

Philip Stoev
[16 Dec 2009 7:46] 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/94368

2920 Mattias Jonsson	2009-12-16
      post push patch of bug#49369 updating test by removing
      select of platform dependend variable.
[21 Dec 2009 15:39] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091221153807-80nxoli1tw1z9bxn) (version source revid:mattias.jonsson@sun.com-20091216074827-j5q1y4ob70ewz1fv) (merge vers: 6.0.14-alpha) (pib:15)
[21 Dec 2009 15:40] Bugs System
Pushed into 5.5.1-m2 (revid:alik@sun.com-20091221153538-ifi3mxf3y10ozxin) (version source revid:mattias.jonsson@sun.com-20091216074525-u9p182amobb584zu) (merge vers: 5.5.0-beta) (pib:15)
[21 Dec 2009 15:41] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20091221153659-d7bt0fh6mhhclxuf) (version source revid:mattias.jonsson@sun.com-20091216074647-2xju7372fpxzx2t5) (pib:15)
[3 Jan 2010 17:26] Jon Stephens
This represents the addition of a test case only, with no user-facing changes to document.

(The issue originally reported was not a bug.)

Closed without without action.