MySQL Bugs: #69543: Synchronize Model / Forward Engineer: missing CHARSET directives

Bug #69543	Synchronize Model / Forward Engineer: missing CHARSET directives
Submitted:	21 Jun 2013 19:34	Modified:	24 Jun 2013 13:27
Reporter:	Rasmus Schultz	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	6.0.2 beta	OS:	Windows (8)
Assigned to:		CPU Architecture:	Any

Description:
The charset directive is frequently missing when doing a "Synchronize Model" or "Forward Engineer" operation.

On a system with a different DEFAULT CHARSET on the database, this causes problems with keys and indexes - a CREATE TABLE statement with a foreign key constraint on a CHAR or VARCHAR column in a different table will fail, if the DEFAULT CHARSET is something different from that of the foreign key column.

For example, the following will fail:

CREATE TABLE IF NOT EXISTS `project` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `type_id` CHAR(10) NOT NULL ,
  `name` VARCHAR(50) NOT NULL ,

  ...

  CONSTRAINT `fk_project_type`
    FOREIGN KEY (`type_id` )
    REFERENCES `chess_live`.`project_type` (`id` )
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;

Whereas adding the CHARSET = UTF8 directive at the end, guarantees that this will succeed on a system with a different DEFAULT CHARSET setting.

On that subject, I don't see any way to specify the default character set in a project, or for a specific schema? It appears to be connection-dependent, which seems wrong to me - character set is not typically something you would change on a per-system or per-deployment basis, but more likely something application-dependent that needs to be migrated consistently across systems.

How to repeat:
See above.

Suggested fix:
Always generate the CHARSET directive.

If you're talking about the modeling feature, you should set the default charset for the schema. 
That will make the schema to be created as in CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;

I have the table's collation set to "Table Default", and the table's collation is set to "Schema Default" - but where do I configure the schema default?

Double click the schema "tab" under Model to open the schema editor.

The schema options are hidden behind a double-click on a tab - a type of UI element that never traditionally has any function associated with a double-click.

It took me like 15 minutes to even figure out what you were asking me to double-click, because this is so unorthodox.

I strongly suggest you move these options into the accordion below, with "Schema Privileges", "SQL Scripts", etc. - where somebody might actually find it.

Double-clicking a tab would be the last thing anybody would ever guess or try ;-)

You can also right click and Edit Schema...
But I agree it could be better, we'll implement something when we figure out something good.