Description:
I created a diagram that creates a schema, with a default collation of utf8_bin. I then created a table with a bunch of columns. Some of the columns have the collation set to utf8_unicode_ci.
When I generate the create script via the export | forward engineer SQL Create Script option, none of the collation settings are put in the sql create script.
I just wasted hours of time loading data into an incorrectly created database because of this bug, because i didn't catch this issue visually.
In the snippet below, notice there are no collation attributes on the create schema, nor in the deviceLocation table on the following columns: deviceId, country, zipcode, censusId.
This is what was generated:
CREATE SCHEMA IF NOT EXISTS `location2` DEFAULT CHARACTER SET utf8 ;
USE `location2` ;
-- -----------------------------------------------------
-- Table `location2`.`deviceLocation`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `location2`.`deviceLocation` ;
CREATE TABLE IF NOT EXISTS `location2`.`deviceLocation` (
`deviceId` VARCHAR(128) CHARACTER SET 'utf8' NOT NULL,
`locationType` BIGINT NOT NULL,
`country` VARCHAR(64) CHARACTER SET 'utf8' NOT NULL,
`startDate` DATETIME(3) NOT NULL,
`endDate` DATETIME(3) NOT NULL,
`lat` DOUBLE NULL,
`lon` DOUBLE NULL,
`zipcode` VARCHAR(64) CHARACTER SET 'utf8' NULL,
`censusId` VARCHAR(64) CHARACTER SET 'utf8' NULL,
PRIMARY KEY (`deviceId`, `locationType`, `country`, `startDate`, `endDate`),
INDEX `ix_zipcode_locaton_type` (`locationType` ASC, `zipcode` ASC),
INDEX `ix_census_id_location_type` (`locationType` ASC, `censusId` ASC))
ENGINE = InnoDB;
How to repeat:
Open mysql workbench. create a new EER model diagram. create a schema with e default collation. create a table with a column a different collation than the default.
do a export | forward engineer, and notice none of the collation settings are in the sql create script.
Suggested fix:
Add the collation settings to the create script.
