Bug #14326 No formatting of SHOW CREATE TABLE output
Submitted: 26 Oct 2005 11:14 Modified: 10 Nov 2008 15:55
Reporter: Jon Stephens Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S4 (Feature request)
Version:5.1.2 (pulled 2005-10-20) OS:Linux (SuSE 9.3, SuSE 10)
Assigned to: Mattias Jonsson CPU Architecture:Any

[26 Oct 2005 11:14] Jon Stephens 
Description:
Output of PARTITION BY clause is on a single line. This is difficult to read.

How to repeat:
Run SHOW CREATE TABLE on a partitioned table.

Suggested fix:
Modify SHOW CREATE TABLE so that PARTITION and SUBPARTITION clauses are formatted with linebreaks, e.g.

mysql> SHOW CREATE TABLE trb3\G
*************************** 1. row ***************************
       Table: trb3
Create Table: CREATE TABLE `trb3` (
  `id` int(11) default NULL,
  `name` varchar(50) default NULL,
  `purchased` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 
PARTITION BY RANGE (YEAR(purchased)) (
  PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM, 
  PARTITION p1 VALUES LESS THAN (1995) ENGINE = MyISAM, 
  PARTITION p2 VALUES LESS THAN (2000) ENGINE = MyISAM, 
  PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM
)

Instead of

mysql> SHOW CREATE TABLE trb3\G
*************************** 1. row ***************************
       Table: trb3
Create Table: CREATE TABLE `trb3` (
  `id` int(11) default NULL,
  `name` varchar(50) default NULL,
  `purchased` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (YEAR(purchased)) (PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (1995) ENGINE = MyISAM, PARTITION p2 VALUES LESS THAN (2000) ENGINE = MyISAM, PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM)
[7 Apr 2008 7:27] Mattias Jonsson 
My proposal are to format it like this:
mysql> create table t7 (a INT) partition by range (a) subpartition by hash(a) (partition p1 values less than (20) (subpartition s1, subpartition s2, subpartition s3), partition p2 values less than (40) (subpartition s4, subpartition s5, subpartition s6), partition p3 values less than (MAXVALUE) (subpartition s7, subpartition s8, subpartition s9));
Query OK, 0 rows affected (0.02 sec)

mysql> show create table t7\G
*************************** 1. row ***************************
       Table: t7
Create Table: CREATE TABLE `t7` (
  `a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100
PARTITION BY RANGE (a)
SUBPARTITION BY HASH (a)
(PARTITION p1 VALUES LESS THAN (20)
 (SUBPARTITION s1 ENGINE = MyISAM,
  SUBPARTITION s2 ENGINE = MyISAM,
  SUBPARTITION s3 ENGINE = MyISAM),
 PARTITION p2 VALUES LESS THAN (40)
 (SUBPARTITION s4 ENGINE = MyISAM,
  SUBPARTITION s5 ENGINE = MyISAM,
  SUBPARTITION s6 ENGINE = MyISAM),
 PARTITION p3 VALUES LESS THAN MAXVALUE
 (SUBPARTITION s7 ENGINE = MyISAM,
  SUBPARTITION s8 ENGINE = MyISAM,
  SUBPARTITION s9 ENGINE = MyISAM)) */
1 row in set (0.01 sec)
[8 Apr 2008 13:19] 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/45056

ChangeSet@1.2576, 2008-04-08 15:18:57+02:00, mattiasj@witty. +56 -0
  Bug#14326: No formatting of SHOW CREATE TABLE output
  
  Problem was that all partitioning info was in one single line.
  
  Solution was to add line breaks for better formatting.
  (affects all result files with partitioned tables.)
[8 Apr 2008 13:26] Mattias Jonsson 
The full cset. The change applies to all test case files using partitioning so it is too big to show in above link.

Attachment: bug14326.cset.gz (application/x-gzip, text), 39.42 KiB.
[23 Jul 2008 14:20] Ingo Strüwing 
Ok to push by me.
[4 Nov 2008 7:54] Mattias Jonsson 
Recommitted the patch in bzr

Attachment: bug14326.bzr.commit-mail.gz (application/x-gzip, text), 60.15 KiB.
[5 Nov 2008 10:28] Mattias Jonsson 
pushed into mysql-5.1-bugteam and mysql-6.0-bugteam
[10 Nov 2008 10:51] Bugs System 
Pushed into 6.0.8-alpha  (revid:mattias.jonsson@sun.com-20081104074321-he2hcm9mx1ameui1) (version source revid:mattias.jonsson@sun.com-20081104113000-1clp99d31cfs9onj) (pib:5)
[10 Nov 2008 11:35] Bugs System 
Pushed into 5.1.30  (revid:mattias.jonsson@sun.com-20081104074321-he2hcm9mx1ameui1) (version source revid:mattias.jonsson@sun.com-20081104084701-f0gsow6q2n827o4f) (pib:5)
[10 Nov 2008 15:55] Jon Stephens 
Documented bugfix in the 5.1.31 and 6.0.8 changelogs as follows:

        When SHOW CREATE TABLE was used on a partitioned table, all of 
        the table's PARTITION and SUBPARTITION clauses were output on a single 
        line, making it difficult to read or parse.
[19 Jan 2009 11:24] Bugs System 
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:02] Bugs System 
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 16:08] Bugs System 
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)