Bug #17631 SHOW TABLE STATUS reports wrong engine
Submitted: 21 Feb 2006 23:07 Modified: 2 Apr 2006 6:42
Reporter: blubb blubb Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S1 (Critical)
Version:5.1.5-alpha-nt-max/5.1.8 BK OS:Windows (Windows XP/Suse Linux)
Assigned to: Reggie Burnett CPU Architecture:Any

[21 Feb 2006 23:07] blubb blubb
Description:
SHOW TABLE STATUS reports "PARTITION" as engine type for partitioned tables.

This is wrong, since PARTITION is not listed with SHOW ENGINES and it is different from the CREATE TABLE statement (ENGINE=MyISAM).

How to repeat:
CREATE TABLE k1 (   
    id INT NOT NULL PRIMARY KEY,   
    name VARCHAR(20)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 
PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (10) ENGINE = MyISAM, 
PARTITION p1 VALUES LESS THAN (20) ENGINE = MyISAM, 
PARTITION p2 VALUES LESS THAN (30) ENGINE = MyISAM);

SHOW TABLE STATUS;

Suggested fix:
One of this:
- Change the result of SHOW TABLE STATUS >AND< DISALLOW the usage of it while using CREATE TABLE.
- Remove the "ENGINE=MyISAM from SHOW CREATE TALBE >AND< add "PARTITION" to the result of SHOW ENGINES;

... while you have redundant informations please make sure they are equal or remove the redundance.
[22 Feb 2006 2:59] Peter Laursen
Changelog of 5.1.6 > http://dev.mysql.com/doc/refman/5.1/en/news-5-1-6.html

"Partition support is not an “engine”, but it was included in the output of SHOW ENGINES. Now it is not. (Bug #14355) The have_partition_engine variable was renamed to have_partitioning. (Bug #16718) "

But I gues you will have to wait for 5.1.7 to verify the fix:
http://bugs.mysql.com/bug.php?id=17331
[22 Feb 2006 3:32] MySQL Verification Team
c:\mysql\bin>mysql -uroot db22
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.1.8-beta-nt-max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE k1 (
    ->     id INT NOT NULL PRIMARY KEY,
    ->     name VARCHAR(20)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
    -> PARTITION BY RANGE (id)
    -> (PARTITION p0 VALUES LESS THAN (10) ENGINE = MyISAM,
    -> PARTITION p1 VALUES LESS THAN (20) ENGINE = MyISAM,
    -> PARTITION p2 VALUES LESS THAN (30) ENGINE = MyISAM);
Query OK, 0 rows affected (0.09 sec)

mysql>
mysql> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: k1
         Engine: PARTITION
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 1
    Data_length: 0
Max_data_length: 0
   Index_length: 3072
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2006-02-22 00:28:18
    Update_time: 2006-02-22 00:28:18
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql>
[22 Feb 2006 9:00] blubb blubb
Seems you have to decide what a partitioned table is:
1. A table
2. A box of (partitioned) tables

In the first case you have to change the output of SHOW TABLE STATUS from "PARTITON" to "MyISAM".

In the second case you have to change the output of SHOW CREATE TABLE from "ENGINE=MyISAM" to "ENGINE=PARTITION".
[23 Feb 2006 6:27] blubb blubb
Maybe you have to add a new column in the result of SHOW TABLE STATUS, if a table is partitioned.

By the way: "PARTITON" as engine is in every case wrong! "PARTITIONED" would be right. (A "PARTITION" is one part of the whole. But you are trying to tell the whole is separated into several parts.)
[23 Feb 2006 6:33] blubb blubb
... but you have still the same problem:
While the SHOW TABLE STATUS say:
Engine=PARTITION
... you have to list the "engine" "PARTITION" in the SHOW ENGINES result... ;-)

Or with other words: I think the fix of the Bug #14355 was a mistake in this case.

Seems the only solution is the write the engine MyISAM into the result of SHOW TABLE STATUS. If you want to add the informations "PARTITIONED" you have to a new column to the result.
[23 Feb 2006 15:42] MySQL Verification Team
Thank you for the bug report.
[23 Feb 2006 21:36] blubb blubb
Can you let me know how the fix will be solved?
[30 Mar 2006 17: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/4333
[30 Mar 2006 18:50] 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/4336
[31 Mar 2006 6:08] 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/4350
[31 Mar 2006 16:38] Reggie Burnett
Fixed in 5.1.9
[2 Apr 2006 6:42] 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 feature change in 5.1.9 changelog; updated Partitioning documentation in Manual. Closed.