Bug #49055 ALTER TABLE wizard creates incorrect script for changing partitioning

• Submitted: 24 Nov 2009 18:58
• Modified: 22 Jan 2010 15:05
• Reporter: Todd Farmer (OCA)
• Status: Closed
• Category: MySQL Workbench
• Severity: S2 (Serious)
• Version: 5.2 r4753
• OS: Windows (XP)
• Assigned to: Alexander Musienko
• CPU Architecture: Any

[24 Nov 2009 18:58] Todd Farmer

Description:
I used the subpartitioning example from our manual (http://dev.mysql.com/doc/refman/5.1/en/partitioning-subpartitions.html) to create a table:

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0,
            SUBPARTITION s1
        ),
        PARTITION p1 VALUES LESS THAN (2000) (
            SUBPARTITION s2,
            SUBPARTITION s3
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE (
            SUBPARTITION s4,
            SUBPARTITION s5
        )
    );

Then, I changed the partition type to KEY, eliminated the partitioning arguments, and unchecked the "manual" box.  This produced an ALTER TABLE script to remove partitioning, for some reason:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

ALTER TABLE `test`.`ts`  REMOVE PARTITIONING;

SET SQL_MODE=@OLD_SQL_MODE;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Obviously not correct.

How to repeat:
See above.

Suggested fix:
Create correct ALTER TABLE statement.

[20 Jan 2010 15:18] Johannes Taxacher

fix confirmed.
will be included in 5.2.14

[22 Jan 2010 15:05] Tony Bedford

An entry has been added to the 5.2.14 changelog:

In the SQL Editor, the Alter Table dialog created incorrect DDL for changes to the partitioning.