MySQL Bugs: #79734: Create/Alter Table not working an partition tables: Syntax error: 'PARTITION'...

Bug #79734	Create/Alter Table not working an partition tables: Syntax error: 'PARTITION'...
Submitted:	22 Dec 2015 8:30	Modified:	23 Jan 2018 9:01
Reporter:	Brain Schmieder	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	6.3.8	OS:	Windows (Microsoft Windows 7 Professional Service Pack 1)
Assigned to:		CPU Architecture:	Any
Tags:	WBBugReporter

Description:
Hello,

on tables with partition the Alter Table... does not work.
The View DDL gives this error:

"Syntax error: 'PARTITION' (identifier) is not valid input at this position"

Looks like on MySQL Server < 5.6 (tested on Percona 5.1.60, MySQL 5.5.43 and Percona 5.5.44), works fine with MySQL Server 5.6 and 5.7.

best regards

How to repeat:
Create/Alter Table like this test on a MySQL Server 5.5:

CREATE TABLE `partition_test` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `team` int(10) NOT NULL DEFAULT '0',
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`,`team`,`created`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (team)
(PARTITION t1 VALUES LESS THAN (1) ENGINE = MyISAM,
 PARTITION t2 VALUES LESS THAN (2) ENGINE = MyISAM,
 PARTITION t3 VALUES LESS THAN (3) ENGINE = MyISAM,
 PARTITION t4 VALUES LESS THAN (4) ENGINE = MyISAM,
 PARTITION t5 VALUES LESS THAN (5) ENGINE = MyISAM,
 PARTITION t6 VALUES LESS THAN (6) ENGINE = MyISAM,
 PARTITION t7 VALUES LESS THAN (7) ENGINE = MyISAM,
 PARTITION t8 VALUES LESS THAN (8) ENGINE = MyISAM,
 PARTITION t9 VALUES LESS THAN (9) ENGINE = MyISAM,
 PARTITION t10 VALUES LESS THAN (10) ENGINE = MyISAM,
 PARTITION t11 VALUES LESS THAN (11) ENGINE = MyISAM,
 PARTITION t12 VALUES LESS THAN (12) ENGINE = MyISAM,
 PARTITION t13 VALUES LESS THAN (13) ENGINE = MyISAM,
 PARTITION t14 VALUES LESS THAN (14) ENGINE = MyISAM,
 PARTITION t15 VALUES LESS THAN (15) ENGINE = MyISAM,
 PARTITION t16 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */;

Syntax error: 'PARTITION'...

Attachment: workbench_alter-table_error.png (image/png, text), 57.59 KiB.

Hello Brain Schmieder,

Thank you for the report.
Verified as described with WB 6.3.6 on Win7.

Thanks,
Umesh

Hello,

I have same problem with Workbench 6.3.6 not able to open 'Alter table' fro table with partitions. But it only happens with DB versions 5.5 or lower. Maybe this can help.

Workbench 6.3.6, Win7

Fixed as of the upcoming MySQL Workbench 6.3.7 release, and here's the changelog entry:

With MySQL Server 5.5 and lower, executing Alter Table on tables with
partitions would fail with an error similar to "Syntax error: 'PARTITION'
(identifier) is not valid input at this position".

Thank you for the bug report.

Hello,

unfortunately the bug is still present.

Tested with update of existing installation, reinstallation and completely new installation on a new system.

Both 32 and 64 bit versions tested.
Win 7, Win 8.1 Pro, Win Server 2012 Std R2, also Win Server 10 TP

Best Regards
Brain Schmieder

I can also confirm, that issue was not solved. Same error for MySql DB versions 5.6 or lower.

09:16:34 [WRN][SqlEditorSchemaTree]: Error parsing DDL for <schema>.<table>: delimiter $$

I couldn't repeat with version 6.3.8 the error is: ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL (which is reported by bug: https://bugs.mysql.com/bug.php?id=63083) so not WorkBench issue.

Parttition error from server

Attachment: partition_error.png (image/png, text), 372.31 KiB.

Hello,

the Bug is still there.

You`re screenshot looks like you have tested with MySQL Server 5.7? The Bug only occurs on MySQL Server 5.5 and lower.

Oh, I just saw that I had an error in the query, but it should go with this (had accidentally written InnoDB up and down MyISAM):

CREATE TABLE `partition_test` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `team` int(10) NOT NULL DEFAULT '0',
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`,`team`,`created`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (team)
(PARTITION t1 VALUES LESS THAN (1) ENGINE = MyISAM,
 PARTITION t2 VALUES LESS THAN (2) ENGINE = MyISAM,
 PARTITION t3 VALUES LESS THAN (3) ENGINE = MyISAM,
 PARTITION t4 VALUES LESS THAN (4) ENGINE = MyISAM,
 PARTITION t5 VALUES LESS THAN (5) ENGINE = MyISAM,
 PARTITION t6 VALUES LESS THAN (6) ENGINE = MyISAM,
 PARTITION t7 VALUES LESS THAN (7) ENGINE = MyISAM,
 PARTITION t8 VALUES LESS THAN (8) ENGINE = MyISAM,
 PARTITION t9 VALUES LESS THAN (9) ENGINE = MyISAM,
 PARTITION t10 VALUES LESS THAN (10) ENGINE = MyISAM,
 PARTITION t11 VALUES LESS THAN (11) ENGINE = MyISAM,
 PARTITION t12 VALUES LESS THAN (12) ENGINE = MyISAM,
 PARTITION t13 VALUES LESS THAN (13) ENGINE = MyISAM,
 PARTITION t14 VALUES LESS THAN (14) ENGINE = MyISAM,
 PARTITION t15 VALUES LESS THAN (15) ENGINE = MyISAM,
 PARTITION t16 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */;

Best regards
Brain Schmieder

Thank you for the feedback. Yes you are right the test I did was with 5.7 however now testing with 5.5 the line marking as error appears and running the query the table is created, so I think is related/duplicate of http://bugs.mysql.com/bug.php?id=83395. Please check. Thanks in advance.

Hello,

thank you for your quick response.

Unfortunately, not quite, yes the query can run, the real problem comes however afterwards with right click on table->"Alter Table ..." when you want to edit the table.

Then the error comes as in the attached screenshot and you can not edit the table in the workbench.

Best regards
Brain Schmieder

mysql error partition

Attachment: mysql_error_alter_table.png (image/png, text), 47.10 KiB.

Thank you for the feedback. Verified.

Error Alter Table

Attachment: 0part.png (image/png, text), 229.48 KiB.

Fixed in the upcoming release of MySQL Workbench.