Bug #74861 REMOVE PARTITIONING does not work in ALTER TABLE dialog

• Submitted: 14 Nov 2014 10:04
• Modified: 14 Nov 2014 13:24
• Reporter: Mario Beck
• Status: Verified
• Category: MySQL Workbench: SQL Editor
• Severity: S3 (Non-critical)
• Version: 6.2.3.12312 build 2281 commercial
• OS: Linux (OL6)
• CPU Architecture: Any
• Tags: partitioning, workbench

[14 Nov 2014 10:04] Mario Beck

Description:
Disabling partitioning creates the wrong SQL statement. And this statement produces an error when executed on the server.

How to repeat:
Prepare a table that has partitioning defined.
Select that table in the schema windows. Right click -> Alter Table.
Use the tab "Partitioning".
Un-tick the checkbox "Enable Partitioning".
Click on "Apply".

The statement that is generated is:
ALTER TABLE test.a PARTITION BY (id) PARTITIONS 3
This statement produces a syntax error when executed. (Keyword HASH is missing)
Partitioning is not removed. 
 

Suggested fix:
If the partitioning box is deselected the statement that is created should be "ALTER TABLE test.a REMOVE PARTITIONING"

[14 Nov 2014 13:24] MySQL Verification Team

Hello Mario Beck,

Thank you for the bug report and steps.

Thanks,
Umesh

[14 Nov 2014 13:27] MySQL Verification Team

// MySQL Workbench 6.2.4.12437 build 2426 CE (winx64)

CREATE TABLE t1 (
    id INT,
    year_col INT
) PARTITION BY RANGE (year_col) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1995),
    PARTITION p2 VALUES LESS THAN (1999)
);

// From WB

Select that table t1 in the schema windows. 
Right click -> Alter Table.
Use the tab "Partitioning".
Un-tick the checkbox "Enable Partitioning".
Click on "Apply".

Executing:
ALTER TABLE `test`.`t1` 
 PARTITION BY (year_col) PARTITIONS 3;

ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(year_col) PARTITIONS 3' at line 2
SQL Statement:
ALTER TABLE `test`.`t1` 
 PARTITION BY (year_col) PARTITIONS 3

Operation failed: There was an error while applying the SQL script to the database.

// With CLI

mysql> CREATE TABLE t1 (
    ->     id INT,
    ->     year_col INT
    -> ) PARTITION BY RANGE (year_col) (
    ->     PARTITION p0 VALUES LESS THAN (1991),
    ->     PARTITION p1 VALUES LESS THAN (1995),
    ->     PARTITION p2 VALUES LESS THAN (1999)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> ALTER TABLE t1 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `year_col` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)