How to apply collation changes from SQL Alter script to EER model?

Question

I'm experiencing troubles while trying to change collations of existing tables in my EER model inside MySQL Workbench (5.2.40, rev 8790) on Linux Debian (3.2.41-2).

My alter script looks like this:

ALTER TABLE `my_db`.`articles` COLLATE = utf8_general_ci;

ALTER TABLE `my_db`.`articles_tags` COLLATE = utf8_general_ci;

ALTER TABLE `my_db`.`articles_tags_assignment` COLLATE = utf8_general_ci;

etc. for approximately hundreds of tables.

When I go to File > Import > Reverse Engineer MySQL Create Script... and select the script described above, push "Execute", it says "Import finished".

But actually when I go to Database > Synchronize Model... click through all details and get to differences... then I see that it still thinks, that my EER model have the old collation (latin1_swedish_ci).

Am I doing something wrong or should I just report this as a bug in MySQL workbench?

Not sure the "Reverse Engineer MySQL **Create** Script" feature is expected to support `ALTER` statements. — RandomSeed, May 23 '13 at 12:54
Can't table collation and field collation be different? Did you look at a field or at the table itself? — 0xCAFEBABE, May 23 '13 at 12:57

score 0 · Answer 1 · answered May 24 '13 at 07:57

As the menu entry says, MySQL Workbench expects a create script, that is a script containing CREATE statements. If you only want to change a handful collations just do it in the table editor. Mass changes are a bit more difficult as you can do that only with a plugin in script. There's a plugin for changing the storage engine of all tables. Maybe this can help you to get started.

score 0 · Answer 2 · answered Feb 28 '14 at 06:49

I'm not 100% sure this will work, but is worth a try if anyone else is trying to achieve something like the above:

Use "forward engineer" to create an instance of the database as described in your model
Execute your alter script on the instance you've created
Use "synchronize model" and change all the actions to "update model" to import the changes back into workbench

How to apply collation changes from SQL Alter script to EER model?

2 Answers2