Bug #98085 | Foward engineer script from model editor produces invalid SQL | ||
---|---|---|---|
Submitted: | 27 Dec 2019 18:16 | Modified: | 26 Feb 2020 13:06 |
Reporter: | Rob Agnese | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | MySQL Workbench: Modeling | Severity: | S3 (Non-critical) |
Version: | 8.0.x | OS: | MacOS |
Assigned to: | CPU Architecture: | x86 | |
Tags: | char, character, character set, column, DEFAULT, details |
[27 Dec 2019 18:16]
Rob Agnese
[27 Dec 2019 18:18]
Rob Agnese
Updated version, OS, CPU
[28 Dec 2019 15:21]
MySQL Verification Team
Thank you for the bug report. I couldn't repeat this issue, please attach here a model file which present this issue on your side. Thanks in advance.
[7 Jan 2020 18:01]
Rob Agnese
An example model file with the problem
Attachment: bad.mwb (application/octet-stream, text), 4.93 KiB.
[26 Feb 2020 13:06]
MySQL Verification Team
Hello Rob Agnese, Thank you for the model file but it has varchar instead of char. I tried to change the datatype from varchar to char but I am not seeing any issues at my end. Regards, Ashwini Patil
[14 Oct 2020 17:49]
Brian Plunkett
This problem persists in MySQL Workbench v8.0.21 (Win64), is not operating system specific, behaves the same whether the data is any of the XXXXCHAR datatypes (i.e. CHAR, NCHAR, VARCHAR, NVARCHAR, etc.), and this ticket should be reopened.
[14 Oct 2020 18:12]
Brian Plunkett
The sample workbench file provided was "MySQL Workbench v1.4.4" compatible. Either by design flaw or issue during compilation (e.g. not a MySQL provided binary), the state file for the table defined in this workbench file has an improper "characterSetName" & "collationName" of "DEFAULT" (i.e. both values) which is what is causing the rendering of the "Forward Engineering to Database" script to contain invalid SQL. Solution (long term for Software Engineering team): Modify the code to do validation of legacy state file formats, compare to current standards for rendering, and issue a warning to the user if there is a construct in the state file which is no longer compatible. A visual indicator (e.g. red field on the model tool) would be a possible, helpful forewarning to the user community.
[14 Oct 2020 18:36]
Brian Plunkett
Correction: My previous comment should have read "MySQL Workbench Model v1.4.4" not "MySQL Workbench v1.4.4". NOTE: The Oracle/MySQL Developers should know what this differentiation means.
[17 Oct 2020 21:47]
Brian Plunkett
The "Target MySQL Version" (i.e. "5.6.30") set in the sample workbench document "bad.wkb" gives more insight as to why the syntax is being rendered seemingly incorrectly. Perhaps, that was the correct syntax to use with that version of MySQL Server. Compare the global preferences, for all models, to the specific model's preferences. Global Preferences (from menu bar): "Model" -> "Model Options..." -> "MySQL" (on popup) Model Preferences (from menu bar): "Edit" -> "Preferences..." -> "MySQL" (on popup) If the "Use defaults from global settings" isn't checked on the model preferences, any *new* objects created should have the MySQL Server v5.6.30 semantics when generated. The model preference should probably be changed, after disabling the global setting, to v5.6.49 unless needed for historical documentation. Existing objects might need to be refreshed by clicking on the to select them after changing the model preferences, possible design flaw. This is how I cleared the error on MySQL Workbench v8.0.21. NOTE: If the database to which you are deploying is MySQL Server 5.6.30 (released: 03-Mar-2016), that database should be upgraded *immediately* to v5.6.49 or higher as there are significant security concerns addressed between those version (e.g. *most importantly* improved SSL encryption). Again, the type of character field doesn't matter. Suggestion(s) (for admins w/ multiple MySQL Server versions): 1. Set global preferences to the oldest version (based on your business' supported production environments) 2. Minimize maintenance for new models & server versions 3. Set model preferences to it's or MySQL's oldest "Current GA" version (i.e. >= global preference) 4. Leave only one or two variables to change in the model when deploying to the other and/or newer versioned servers (i.e. target stored connection and/or model preference)