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:
None 
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
Description:
When editing a table schema in Workbench, if you use the 'Charset' drop down for a char() column and select 'Default Charset', and then export a forward engineer script, it will be invalid. E.g.,

CREATE TABLE IF NOT EXISTS `schema1`.`table1` (
  `id` CHAR(36) CHARACTER SET 'DEFAULT' BINARY NOT NULL COMMENT 'Id',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Created At',
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Updated At',
  PRIMARY KEY (`id`)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COMMENT = 'My Table';

How to repeat:
Open Workbench
Create a new model
Add a new table with a char() column
Use the Charset drop-down menu and select 'Default Charset'
Export -> Forward Engineer SQL CREATE script

Suggested fix:
Use special handling in the drop down logic to NOT set the CHARACTER SET if the first entry in the drop down is selected.
[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)