Bug #72452 Changed collaction when using Migration Wizard

• Submitted: 25 Apr 2014 10:45
• Modified: 25 Apr 2014 16:37
• Reporter: Fredrik Larsson
• Status: Verified
• Category: MySQL Workbench: Migration
• Severity: S3 (Non-critical)
• Version: 6.1.4
• OS: Any
• CPU Architecture: Any

[25 Apr 2014 10:45] Fredrik Larsson

Description:
While trying to migrate a database, I discovered that the collation of some columns had changed from utf8_bin to utf8_general_ci, leading to duplicate entry errors.

How to repeat:
1. Create a database
2. Create a table with at least one column having collation utf8_bin
3. Migrate the database
4. Column on new server now has collation utf8_general_ci

Suggested fix:
Make sure that collation is transfered if possible

[25 Apr 2014 11:14] MySQL Verification Team

Which is the source server version and target server version in the migration process?. Thanks.

[25 Apr 2014 11:43] Fredrik Larsson

Source: mysql  Ver 15.1 Distrib 5.5.37-MariaDB, for debian-linux-gnu (x86_64) using readline 5.1

Target: mysql  Ver 15.1 Distrib 5.5.36-MariaDB, for debian-linux-gnu (x86_64) using readline 5.1

[25 Apr 2014 16:37] MySQL Verification Team

Thank you for the feedback.

c:\5.5.37\bin>mysql -uroot --port=3337
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.5.37 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW CREATE TABLE MG.TB\G
*************************** 1. row ***************************
       Table: TB
Create Table: CREATE TABLE `tb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> EXIT
Bye

c:\5.5.37\bin>mysql -uroot --port=3336
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW CREATE TABLE MG.TB\G
*************************** 1. row ***************************
       Table: TB
Create Table: CREATE TABLE `tb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>