MySQL Bugs: #66680: MySQL Workbench Model Synchronization Single Quote in Comments

Bug #66680	MySQL Workbench Model Synchronization Single Quote in Comments
Submitted:	4 Sep 2012 12:10	Modified:	30 Nov 2012 2:21
Reporter:	Stuart Goss	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S2 (Serious)
Version:	5.2.42 CE	OS:	Windows (7 x64)
Assigned to:		CPU Architecture:	Any

Description:
If a field within a model contains a comment that includes a single quote, the Synchronize Model feature will incorrectly escape the quote and cause the generated SQL code to fail. Note that no error is actually caught and displayed and the synchronisation appears successful. However, any further ALTER commands following the single quote in a fields comment will not execute. Notably, this applies to adding Foreign Key constraints.

How to repeat:
Create database model. Add table. Create field of any name and type and add a comment containing a single quote (apostrophe). Go to Database / Syncronize Model. Complete the Wizard.

It will show as Synchronisation Completed Successfully.

However, any foreign key definitions after the commented field will not be added / altered.

Suggested fix:
Change escaping of single quotes in comments to fix or disallow use of single quotes in GUI comments field.

Thank you for the bug report. I can't repeat with my own model, are you able to provide the model file?. Thanks.

Newly created model file exhibiting bug

Attachment: Test Bug Model.mwb (application/octet-stream, text), 6.44 KiB.

The initial forward engineer of the attached model also produces the bug. Process completed successfully but foreign keys are missing in actual database from table1 due to single quote in comment for the some_text field.

Also then happens if anything is changed on the table and Synchronise Model is performed, with the following code being produced. Copying and pasting produced SQL into MySQL command line also fails to create foreign keys (although does not display error).

ALTER TABLE `bugtest`.`table1` CHANGE COLUMN `some_text` `some_text` VARCHAR(45) NULL DEFAULT NULL COMMENT 'a Something\\\'s going on here'  , 

  ADD CONSTRAINT `fk_test`
  FOREIGN KEY (`table2_id` )
  REFERENCES `bugtest`.`table2` (`id` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION, 
  ADD CONSTRAINT `fk_table3`
  FOREIGN KEY (`table3_id` )
  REFERENCES `bugtest`.`table3` (`id` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION

Thank you for the feedback.

also, it didn't seem to be explicitly noted, but on every subsequent model sync, an extra pair of \\'s are prepended to any comment that was escaped. for example:

in workbench, if a column comment is entered as: (note 1 single quote)

  OID of the subject who's action resulted in the logged event

forward engineer *tries* to escapes it, but produces ddl: (note 3 backslashes + 1 single quote)

  ...
  `oid_subject` INT(10) UNSIGNED NOT NULL COMMENT 'OID of the subject who\\\'s action resulted in the logged event' ,
  ...

when i ran the freshly forward engineered ddl with show warnings enabled, not a single error was logged, even though, as stuart mentioned, the foreign keys for that table didn't get generated

without making any changes to the workbench model or the database just generated via forward eningeered script, if you then model sync, any tables that have buggy quote commenting are flagged as different

configuration:
- windows 7, 64-bit
- mysql workbench 5.4.22 ce, revision 9933
  - forward eng & sync sql_mode: traditional (as recommended)
- mysql server 5.1.65

also confirmed same behavior if double quotes are used in the comment instead of single quotes

just tried the other way to escape single quotes as described in the mysql manual:

  There are several ways to include quote characters within a string:

    A “'” inside a string quoted with “'” may be written as “''”.

same triple quoted bung from forward engineer. arggghhhh...

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

A model could not be synchronized if a field contained a comment with a
single quote.

Thank you for the bug report.