MySQL Bugs: #53282: Synchronize model fails to apply collation changes

Bug #53282	Synchronize model fails to apply collation changes
Submitted:	29 Apr 2010 7:59	Modified:	4 May 2010 12:05
Reporter:	Álvaro González	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	5.2.19 OSS Beta r5680	OS:	Windows (XP Professional)
Assigned to:		CPU Architecture:	Any

Description:
In certain circumstances, synchronize model seems to generate an incomplete SQL code to alter the database. The result is that Workbench generates the same alter SQL every time a synchronization is run and the database is permanently out of sync.

It appears to be related to column collation. Please note `area_id` differs:

::: Create code in model :::

CREATE  TABLE IF NOT EXISTS `foo`.`area` (
  `area_id` CHAR(3) NOT NULL ,
  `area_name` VARCHAR(30) CHARACTER SET 'utf8' COLLATE 'utf8_spanish_ci' NOT NULL ,
  PRIMARY KEY (`area_id`) ,
  UNIQUE INDEX `area_name_UNIQUE` (`area_name` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_spanish_ci

::: Create code in database :::

CREATE TABLE `area` (
  `area_id` char(3) COLLATE utf8_spanish_ci NOT NULL,
  `area_name` varchar(30) COLLATE utf8_spanish_ci NOT NULL,
  PRIMARY KEY (`area_id`),
  UNIQUE KEY `area_name_UNIQUE` (`area_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci

How to repeat:
1. Open a MySQL client and create a database and a table with this code:

CREATE DATABASE `foo` /*!40100 CHARACTER SET utf8 COLLATE utf8_general_ci */;

USE `foo`;

CREATE TABLE `area` (
  `area_id` INT(10) NOT NULL,
  `area_name` varchar(30) COLLATE utf8_spanish_ci NOT NULL,
  PRIMARY KEY (`area_id`),
  UNIQUE KEY `area_name_UNIQUE` (`area_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;

2. Open Workbench and select "Create EER Model from Existing Database" in the Home tab. Proceed through the wizard.

3. Once table `area` shows up in the diagram, double click on it. Go to the "Columns" tab and change the datatype for `area_id` to CHAR(3). Leave "Table default" in the collation combo.

4. Go to "Database-> Synchronize model" and complete synchronization. You basically get this SQL:

ALTER TABLE `foo`.`area` CHANGE COLUMN `area_id` `area_id` CHAR(3) NOT NULL  ;

5. Run synchronization again. Table is still tagged as modified. Alter SQL remains the same:

ALTER TABLE `foo`.`area` CHANGE COLUMN `area_id` `area_id` CHAR(3) NOT NULL  ;

I am not able to repeat this by using Workbench 5.2.20.

Please try actual version 5.2.20 here.

I'm testing it and I'll be reporting back ASAP.

I didn't notice there was a more recent version because the "Check for updates" menu inside "Help" points to http://wb.mysql.com/workbench/version-check.php?config=OSS&version=5.2.19&revision=5680 where I'm informed that "No new version of MySQL Workbench available at this time".

Tested in Workbench 5.2.20 OSS Beta r5783: problem remains :-?

Do you get the same ALTER TABLE code as I do? If I run it directly on the DB server (5.1.36) it doesn't change the column collation:

ALTER TABLE `foo`.`area` CHANGE COLUMN `area_id` `area_id` CHAR(3) NOT NULL  ;

I see the reason know ....

*****

My test was:

CREATE TABLE t(v VARCHAR(100) collate latin1_german2_ci)Engine=innodb collate utf8_general_ci;

I reverse engineered the table with Workbench and changed in modeling the column from varchar to char. After that I synced and got:

ALTER TABLE t CHANGE column v CHAR(100) collate latin1_german2_ci;

*****

Looking into your example:

CREATE TABLE `area` (
  `area_id` char(3) COLLATE utf8_spanish_ci NOT NULL,
  `area_name` varchar(30) COLLATE utf8_spanish_ci NOT NULL,
  PRIMARY KEY (`area_id`),
  UNIQUE KEY `area_name_UNIQUE` (`area_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci

area_id has collation utf8_spanish_ci but also the default collation of the table is utf8_spanish_ci.

Which means it is not necessary here to explicit give the collation at the column because it is the default table collation anyway.

That Workbench didn't point out the column collation in your example is not a bug it is an optimization. Workbench recognised that table collation is same as column collation and so it is not necessary to explicit point out the collation with the column data type.

I can't fully follow your reasoning. Workbench recognises that table collation is the same as column collation, yet it:

1. Highlights it as difference
2. Generates SQL to change it
3. Generated SQL changes nothing
4. Go to #1

How does this differ from not recognising it all?

I'm afraid in some cases Workbench probably will also drop and rebiuld indices, which may take some processor load for non-empty databases.

It looks like we may avoid the case by explicitly specifying the collation for every text column and both the table and scheme defaults.

It would be nice if Workbench would normalize both the scheme reported by the server - and the internal representation - to the same form, before comparison.

Also, I noticed one more case when Workbench repeatedly alters column type: the optional delimiting whitespaces, eg. I like enums to be written in the following style:
ENUM('person', 'event', 'place'), while the server reports this as
ENUM('person','event','place'). Workbench takes it for difference and every synchronization tries to persuade the server to my slyle, vainly, though.