Bug #19684 partitions column of EXPLAIN PARTITIONS can't handle long text
Submitted: 10 May 2006 14:05 Modified: 10 Jun 2006 4:02
Reporter: Brett Simpson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1.9/5.1BK OS:Linux (CentOS 4.3)
Assigned to: Sergey Petrunya CPU Architecture:Any

[10 May 2006 14:05] Brett Simpson
Description:
Creating a table with 35 or more partitions causes the EXPLAINS PARTITIONS to not display properly. 34 or less show up.

How to repeat:
CREATE TABLE event  ( sid         INT      UNSIGNED NOT NULL,
                       cid         INT      UNSIGNED NOT NULL, 
                       signature   INT      UNSIGNED NOT NULL,
                       timestamp            DATETIME NOT NULL,
                       PRIMARY KEY (sid,cid,timestamp),
                       INDEX       sig (signature),
                       INDEX       time (timestamp))
                       PARTITION BY HASH(DAYOFYEAR(timestamp)) 
                       PARTITIONS 35;

EXPLAIN PARTITIONS SELECT * FROM event\G
*************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: event    partitions: X?A          type: system possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra:
1 row in set (0.00 sec)
[10 May 2006 14:16] Miguel Solorzano
Thank you for the bug report.

mysql> CREATE TABLE event  ( sid         INT      UNSIGNED NOT NULL,
    ->                        cid         INT      UNSIGNED NOT NULL, 
    ->                        signature   INT      UNSIGNED NOT NULL,
    ->                        timestamp            DATETIME NOT NULL,
    ->                        PRIMARY KEY (sid,cid,timestamp),
    ->                        INDEX       sig (signature),
    ->                        INDEX       time (timestamp))
    ->                        PARTITION BY HASH(DAYOFYEAR(timestamp)) 
    ->                        PARTITIONS 35;
Query OK, 0 rows affected (0.06 sec)

mysql> EXPLAIN PARTITIONS SELECT * FROM event\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: event
   partitions: 
         type: system
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 0
        Extra: const row not found
1 row in set (0.02 sec)

->                        INDEX       sig (signature),
    ->                        INDEX       time (timestamp))
    ->                        PARTITION BY HASH(DAYOFYEAR(timestamp)) 
    ->                        PARTITIONS 34;    

mysql> EXPLAIN PARTITIONS SELECT * FROM event\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: event
   partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,<cut>
<cut>
[5 Jun 2006 9:55] 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/7264
[5 Jun 2006 11:06] 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/7267
[9 Jun 2006 20:25] Sergey Petrunya
The fix has been pushed into 5.1.12 tree.

Notes for the changelog: The bug was that EXPLAIN PARTITIONS would produce garbage in "partitions" column if the length of text to be displayed in that column would exceed certain limit.
[10 Jun 2006 4:02] Jon Stephens
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented bugfix in 5.1.12 changelog. Updated summary per Sergey's comments. Closed.