1

I'm migrating a large production database(s) from mysql 5.5 to 5.6. Used Mysql Workbench Migration Wizard which creates a shell script to run on source and taget machines.

The process worked well but there are major changes to the database:

For example:

  1. Changed order of fields in primary key PRIMARY KEY (ru_sid,ru_rule_id), => PRIMARY KEY (ru_rule_id,ru_sid)

  2. Messed encoding while the data is expected to be utf8 I did a query of the same data in the original and new server and the output is different: Pré-Home Flash Sales => Pré-Home Flash Sales

Possibly there are other changes.

My question is - how can I tell if the migration is trustable. I cant check every table to see if changes in the index will not break something in my code. Can the workbench migration be trusted ? Is there a better way to do the migration? What would you do?

EDIT: Create statement before: (I removed the fields that are the same in the before and after) CREATE TABLErules( ru_rule_idint(10) unsigned NOT NULL AUTO_INCREMENT, ru_titlevarchar(254) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', ru_datatext COLLATE utf8_unicode_ci NOT NULL, ru_namevarchar(64) COLLATE utf8_unicode_ci NOT NULL, ru_tagsvarchar(64) COLLATE utf8_unicode_ci DEFAULT '', ru_priorityvarchar(12) COLLATE utf8_unicode_ci NOT NULL DEFAULT '1', PRIMARY KEY (ru_sid,ru_rule_id), KEYnum(ru_rule_id) USING BTREE ) ENGINE=MyISAM AUTO_INCREMENT=9998 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Create statement after: CREATE TABLErules( ru_rule_idint(10) unsigned NOT NULL AUTO_INCREMENT, ru_titlevarchar(254) CHARACTER SET utf8 NOT NULL DEFAULT '', ru_datatext CHARACTER SET utf8 NOT NULL, ru_namevarchar(64) CHARACTER SET utf8 NOT NULL, ru_tagsvarchar(64) CHARACTER SET utf8 DEFAULT '', ru_priorityvarchar(12) CHARACTER SET utf8 NOT NULL DEFAULT '1', PRIMARY KEY (ru_rule_id,ru_sid), KEYnum(ru_rule_id) ) ENGINE=MyISAM AUTO_INCREMENT=9954 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Improve this question
7
  • It's certainly possible that the encoding issue is due to incorrect settings on either the source or destination server (ie, not part of the schema). The re-ordering of the PK is concerning though. – Chris Harrington Feb 1 '16 at 14:58
  • Regarding the encoding, the MySQL encoding manual page shows at least three places where the charset can be configured (compiled-in default, configuration file, and database default) as well as the myriad of ways a MySQL client can negotiate an encoding at runtime. – Chris Harrington Feb 1 '16 at 15:53
  • 1
    Why did you use Workbench at all? – Rick James Feb 2 '16 at 1:20
  • Please show us the SHOW CREATE TABLE before and after. – Rick James Feb 2 '16 at 1:21
  • 1
    Thanks Chris, I will check encoding again. Rick I used workbench because I needed to migrate the data. Not sure if uploading a mysqldump will do the job. Will it? workbench is greate because it creates a shell script to run on source and target so the data is zipped into one file and can be scpd to the target quickly. what could be more efficient?. I'll add the create statements to the question. yes it uses mysqldump. – Nir Feb 2 '16 at 9:15
1

Seems you've found a bug in migration wizard, I follow your description and I was able to repeat issue on my machine. Can you please fill bug report at bugs.mysql.com? Appreciate. We'll do our best to fix that isse. As workaround you can try this way - use Data Export to export only structure of your database. Run exported stricpt on target server. Then run migration wizard normally, but skip create target schema step. Let me know if that works for you.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.