Bug #88488 Forward engineer with INSERTions containing binary data reports syntax error
Submitted: 14 Nov 2017 16:37 Modified: 16 Nov 2017 21:13
Reporter: Stephen Zhao Email Updates:
Status: Verified Impact on me:
Category:MySQL Workbench: Modeling Severity:S3 (Non-critical)
Version: - CE (64-bit) OS:Windows (10)
Assigned to: CPU Architecture:Any
Tags: BINARY, BLOB, forward engineer, insert, syntax error

[14 Nov 2017 16:37] Stephen Zhao
If entries are added to a table when creating a model, and this model is forward engineered with the the option "Generate INSERT Statements for Tables" checked, when the generated query runs, it will report a syntax error.

How to repeat:
1. Create a model
2. Create a table with the columns:
>    id : INT
>    data : BINARY(16)
3. For that table in the model, insert an entry. Use the "Edit value" dialogue to type in some binary data of length 16 bytes.
4. Enter the forward engineer wizard.
5. In the step where you are presented with checkboxes for many options, choose "Generate INSERT Statements for Tables".
6. In the step where you are presented with a preview of the query to be run, notice that the INSERT statements use "..." to represent the binary data...
7. Run the SQL query and get a syntax error at the exact point where the ellipses ("...") occurs.

Expected behaviour: AS A user I WOULD LIKE the sql query to run without a syntax error and perform the binary data insertion BECAUSE this would improve the functionality of the "pre-insertions". As it stands, the auto-insertions-on-forward-engineer are useless if I cannot perform these binary insertions. I use the BINARY data type to hold values such as UUID and arbitrary-length data.
[14 Nov 2017 18:33] MySQL Verification Team
Thank you for the bug report. Please provide the file of model test case, use the tab Files to attach it. THanks in advance,
[15 Nov 2017 1:13] Stephen Zhao
The model test case

Attachment: bug-88488.mwb (application/octet-stream, text), 5.72 KiB.

[15 Nov 2017 6:33] MySQL Verification Team
Thank you for the sample model file, observed reported issue on Win7.
Also, same issue can be even observed when exporting as SQL script Ctr+Shift+G

[16 Nov 2017 21:13] Stephen Zhao
Although this is an issue, I don't know if it is exactly a bug; it could just be intended that binary data cannot be exported in rawtext (for SQL queries).

I'd like to request that for the fix to be useful and intuitive to the user, the current "..." should be replaced with a hexadecimal literal or hexadecimal string: e.g. My data is 0d.20.21.8d.ab.28. This should be converted to either the text """0x0d20218dab28""" or the text """X'0d20218dab28'"""