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
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