Bug #73340 ALTER statement adds extra newline for COMMENT
Submitted: 21 Jul 2014 12:06
Reporter: Umesh Shastry Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Workbench: SQL Editor Severity:S3 (Non-critical)
Version:6.1.7 OS:Windows (7/64)
Assigned to: CPU Architecture:Any

[21 Jul 2014 12:06] Umesh Shastry
Description:
Observed that WB adds newline to the "ALTER TABLE... COMMENT =" statement which results in syntax error.

How to repeat:
How to repeat:

// Create a table from mysql> prompt. 

use test;
drop table if exists test.bug;
create table bug(id int not null auto_increment primary key);
ALTER TABLE `test`.`bug`  COMMENT =  'Alter table, remove comments triggers syntax error';

mysql> select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.6.21-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> use test;
Database changed
mysql> drop table if exists test.bug;
Query OK, 0 rows affected (0.10 sec)

mysql> create table bug(id int not null auto_increment primary key);
Query OK, 0 rows affected (0.31 sec)

mysql> ALTER TABLE `test`.`bug`  COMMENT =  'Alter table, remove comments triggers syntax error';
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table `test`.`bug`\G
*************************** 1. row ***************************
       Table: bug
Create Table: CREATE TABLE `bug` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Alter table, remove comments triggers syntax error'
1 row in set (0.00 sec)

## Use WB alter table and remove comments

- Connect to the server using WB 6.1.7
- Set "test" as default schema
- Right-click on "bug" table name in the schema, select "Alter Table...", remove the entire text from the comments text area, and click "Apply".

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 '' at line 3
SQL Statement:
ALTER TABLE `test`.`bug` 

COMMENT =

ERROR: Error when running failback script. Details follow.

ERROR 1050: Table 'bug' already exists
SQL Statement:
CREATE TABLE `bug` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Alter table, remove comments triggers syntax error'

Suggested fix:
Remove that extra newline from the ALTER.. statement