MySQL Bugs: #90772: Synchronize model makes different between single quotes escapes

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:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	8.0.12, 8.0.19	OS:	Windows (Windows 7 Professional SP1 (6.1.7601))
Assigned to:		CPU Architecture:	x86

Description:
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.

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?

Thanks,
Chiranjeevi.

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:

CREATE DATABASE `test`;
USE `test`;

CREATE TABLE `currencies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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.

Step 1: Create table with column having comment containing single quote

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

Step 2: Create model having the same table and same comment.

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

Step 3: First synchrinization

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

Step 3.2: Second synchrnization

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

Hello Georgi Sotirov,

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

Thanks,
Chiranjeevi.

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

Possible duplicate Bug #92191 (DDL error same but difference might still exist)?

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.

Bug #103258 marked as duplicate of this one.