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) Email Updates:
Status: Closed Impact on me:
None 
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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.052 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.