MySQL Bugs: #98085: Foward engineer script from model editor produces invalid SQL

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

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.

Updated version, OS, CPU

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.

An example model file with the problem

Attachment: bad.mwb (application/octet-stream, text), 4.93 KiB.

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

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.

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.

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.

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)