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 Email Updates:
Status: Won't Fix Impact on me:
None 
Category:MySQL Workbench: SQL Editor Severity:S3 (Non-critical)
Version:6.2.3.12312 build 2281 commercial OS:Linux (OL6)
Assigned to: 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)
[14 Sep 18:02] Omer Barnir
Posted by developer:

This issue applies to the legacy MySQL Workbench 8.x implementation that is no longer supported or maintained.
MySQL Workbench 26.7 is a complete rewrite using a new technology stack and does not use the affected legacy implementation.
Therefore, this issue is not applicable to the current MySQL Workbench product and as such is closed.

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

Page generated in 0.051 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.