MySQL Bugs: #84815: Table Editor loses column collation in every ALTER TABLE

Bug #84815	Table Editor loses column collation in every ALTER TABLE
Submitted:	4 Feb 2017 0:12	Modified:	16 May 2018 18:50
Reporter:	Andy Schmidt	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	6.3.8	OS:	Windows (Microsoft Windows 10 Pro)
Assigned to:		CPU Architecture:	Any
Tags:	ALTER CHANGE COLLATE

Description:
Every time the column specification of a table are editing, it changes the column's explicit collation back to default (e.g. Swedish).

The reason is that the ALTER / CHANGE statement includes only the CHARACTER SET option but omits the explicit COLLATE setting. 

Consequence: At present the table editor function must not be used by any customer who has columns with non-Swedish collation or risk unintentionally changing the collation. This causes applications to suddenly break when they subsequently attempt to join with other tables with the correct collation!

How to repeat:
Create a COLUMN `test_column` char(25) CHARACTER SET 'latin1' COLLATE 'latin1_general_ci'

Then use the table editor to change any other aspect of that column, e.g. assigning a default value. The table editor will create:

ALTER TABLE `test_table` 
MODIFY COLUMN `test_column` char(25) CHARACTER SET 'latin1' DEFAULT NULL

which reset the explicit COLLATE back to table default.

Suggested fix:
The table editor's ALTER TABLE statement must include the COLLATE option, at least if the collation is not the table default.

Thank you for the bug report. Please try version 6.3.8 if the issue continues please provide screenshot of before and after (use Files tas for). Thanks.

I just installed 6.3.8, then I selected a VARCHAR column and picked the "Latin1 latin1_general_ci" from the drop down. It generated the following ALTER statement:

ALTER TABLE `anamera_wpstage`.`ac_test` 
CHANGE COLUMN `source_label` `source_label` VARCHAR(64) CHARACTER SET 'latin1' NULL DEFAULT NULL COMMENT 'If source_uid is NULL' ;

As you note, the COLLATION is not set. After executing the command, and checking the altered column, the collation had been defaulted to "Swedish"!

Here is the obvious result: collation altered to default

Attachment: bug-after.jpg (image/jpeg, text), 297.87 KiB.

Thank you for the requested details.
Verified as described.

Thanks,
Umesh

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

An explicit collation set on a column was reverted to the default
collation each time the column specification was modified with the table
editor.

Thank you for the bug report.