Bug #90772 Synchronize model makes different between single quotes escapes
Submitted: 6 May 2018 11:10 Modified: 26 Sep 2018 5:48
Reporter: Georgi Sotirov Email Updates:
Status: Verified Impact on me:
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:8.0.12 OS:Microsoft Windows (Windows 7 Professional SP1 (6.1.7601))
Assigned to: CPU Architecture:x86

[6 May 2018 11:10] Georgi Sotirov
If in the model, I have a comment escaped with a backslash like this

COMMENT 'Currency\' symbol'

then MySQL Workbench (even before 8.0) detects this as a difference with the server on "Synchronize model..." and generates an ALTER TABLE statement to update column's comment. However, the server rewrites the escape with a single quote like this

COMMENT 'Currency''s symbol'

Thus, on the next synchronization the difference remains. What is interesting also is that after the synchronization MySQL Workbench gives "Error parsing DDL" on attempt to alter the table.

How to repeat:
1. Create a table column with comment "Currency''s symbol".
2. Synchronize to server.
3. Synchronize again (the difference remains) or try opening the table for editing from the server with MySQL Workbench.

Suggested fix:
According to the specification (see https://dev.mysql.com/doc/refman/8.0/en/string-literals.html#character-escape-sequences ) both backslash and single quote are possible for escaping single quote. So the difference should either be ignored by MySQL Workbench or the change accepted by the server and not rewritten.
[7 May 2018 8:23] Chiranjeevi Battula
Hello Georgi Sotirov,

Thank you for the bug report.
I could not repeat the issue at our end using with MySQL workbench 8.0.11 version.
Could you please provide repeatable steps (exact steps, screenshot, provide full MySQL Workbench Log file etc. - please make it as private if you prefer) to confirm this issue at our end?

[8 May 2018 14:29] Georgi Sotirov
I though I'm clear, but anyway here's the reproducible example with screenshots that you've requested:

1. Create schema test with table currencies in MySQL workbench (see step1.png). Script follows:

USE `test`;

CREATE TABLE `currencies` (
  `symbol` char(1) DEFAULT NULL COMMENT 'Currency''s symbol',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2. Create new model with the same database name and same table (see step2.png).

3. Synchronize the model from Database -> Synchronize model... and you get query to synchronize the comment even though it's the same into the database (see step3.png). If I synchronize again, I get again a query updating the comment even though I changed nothing into the model (see step3_2.png).

I hope it's clear now and you could reproduce it.

P.S. I'm using MySQL Workbench 8.0.11 RC.
[8 May 2018 14:29] Georgi Sotirov
Step 1: Create table with column having comment containing single quote

Attachment: step1.png (image/png, text), 26.57 KiB.

[8 May 2018 14:30] Georgi Sotirov
Step 2: Create model having the same table and same comment.

Attachment: step2.png (image/png, text), 28.77 KiB.

[8 May 2018 14:30] Georgi Sotirov
Step 3: First synchrinization

Attachment: step3.png (image/png, text), 70.89 KiB.

[8 May 2018 14:31] Georgi Sotirov
Step 3.2: Second synchrnization

Attachment: step3_2.png (image/png, text), 68.14 KiB.

[9 May 2018 9:50] Chiranjeevi Battula
Hello Georgi Sotirov,

Thank you for the feedback.
Verified this behavior on MySQL Workbench in 8.0.11 version.

[24 Sep 2018 18:54] Matthew Roberts
I spotted this after submitting mine. Although the issue I am commenting on is different (so not strictly a duplicate also I am raising this against 8.0.12 not .11), the cause is likely the same:

#92548 - Cannot Reverse Engineer When Table Comment Contains Single Quote
#92549 - Cannot Alter Table That Contains A Table Comment With Single Quote
#92550 - Syntax Parser Errors With Valid Table Comment
[25 Sep 2018 11:05] Matthew Roberts
Possible duplicate Bug #92191 (DDL error same but difference might still exist)?
[26 Sep 2018 5:48] Georgi Sotirov
Yes, it's still the same in 8.0.12 Matthew Roberts, because apparently Oracle hasn't changed anything in regards to this bug since May.