MySQL Bugs: #78817: Workbench ignores Collation of a table column when exporting a model as SQL

Bug #78817	Workbench ignores Collation of a table column when exporting a model as SQL
Submitted:	13 Oct 2015 10:26	Modified:	16 Mar 2018 20:32
Reporter:	Herbert Mlsaidhkasjd	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	6.3.4.0 build 828 (64 bits)	OS:	Windows (Any Windows version)
Assigned to:		CPU Architecture:	Any
Tags:	collation, model, workbench

Description:
Hi,

I'm using Workbench to create a model with a table which has VARCHAR columns. The schema collation is "utf8mb4 - default collation".
One of the VARCHAR columns has a custom collation defined, "utf8mb4_bin". 

However, when exporting the model as SQL, Workbench does not include the COLLATE statement. This means:
- When importing the SQL to a MySQL DB, it will use the default collation ("utf8mb4_general_ci") instead of the specified one, "utf8mb4_bin".
- Every time the column is changed and you want to synchronize the model with the DB (which generates an ALTER TABLE... CHANGE COLUMN... sql), the collation of the column in the DB is reverted to the default one.

How to repeat:
1. Open the example model using the link below. Or:
a) Create a new Model in MySQL Workbench
b) Create a new schema with name "MySchema1" and collation "utf8mb4 - default collation"
c) Create a new table "MyTable1" in that schema.
d) In that table, create a new column name "DisplayName", type "VARCHAR(100)", NOT NULLABLE.
d) In that table, create a new column name "IDString", type "VARCHAR(100)", NOT NULLABLE and collation "utf8mb4 - utf8mb4_bin".

2. Export the schema as SQL, using the menu "File", "Export", "Forward Engineer SQL Script...". 

3. In the dialog, click "Next" two times.

4. The generated SQL will be:

(...)

CREATE TABLE IF NOT EXISTS `MyTestDB1`.`MyTable1` (
  `ID` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '',
  `DisplayName` VARCHAR(100) NOT NULL COMMENT '',
  `IDString` VARCHAR(45) CHARACTER SET 'utf8mb4' NOT NULL COMMENT '',
  PRIMARY KEY (`ID`)  COMMENT '')
ENGINE = InnoDB;

(...)

As you can see, although the SQL for "IDString" column contains CHARACTER SET, it does not contain COLLATE. This means MySQL will use the schema default (utf8mb4_general_ci) instead of the specified one.

Instead, a SQL like the following should be output:
CREATE TABLE IF NOT EXISTS `MyTestDB1`.`MyTable1` (
  `ID` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '',
  `DisplayName` VARCHAR(100) NOT NULL COMMENT '',
  `IDString` VARCHAR(45) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_bin' NOT NULL COMMENT '',
  PRIMARY KEY (`ID`)  COMMENT '')
ENGINE = InnoDB;

Link to the example Workbench Model: http://s000.tinyupload.com/index.php?file_id=00011142415451040737

Hello Herbert Mlsaidhkasjd,
 
Thank you for the report.
Observed this on Win7 with WB 6.3.4.

Thanks,
Umesh

Same problem here, tested on macOS Sierra / MySQLWorkbench 6.3.8. AFAIK the problem exists since 6.3.4.

Thanks for fixing,
Philip

This is still a bug, the SQL generator does not add the collation. 

This happen EVEN if you select a case sensitive collation instead of using default case sensitive.

To duplicate it, create a new table, add a varchar column, change collation to a case sensitive one, and click on "Copy SQL to Clipboard", or try to use "Syncronize Model". Both will just set the charset, and ignore the collation.

This bus is still present in Workbench 6.3.8

Same problem on Debain 9 x64 / MySQLWorkbench 6.3.8 build 1228 CE

Carlo

Posted by developer:
 
Fixed as of the upcoming MySQL Workbench 8.0.11 release, and here's the changelog entry:

Columns with custom collation were reset to the default collation when the
model containing the table was exported.

Thank you for the bug report.