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:
-
Changed order of fields in primary key
PRIMARY KEY (ru_sid
,ru_rule_id
), => PRIMARY KEY (ru_rule_id
,ru_sid
) -
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)
rules
CREATE TABLE(
ru_rule_id
int(10) unsigned NOT NULL AUTO_INCREMENT,
ru_title
varchar(254) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
ru_data
text COLLATE utf8_unicode_ci NOT NULL,
ru_name
varchar(64) COLLATE utf8_unicode_ci NOT NULL,
ru_tags
varchar(64) COLLATE utf8_unicode_ci DEFAULT '',
ru_priority
varchar(12) COLLATE utf8_unicode_ci NOT NULL DEFAULT '1',
ru_sid
PRIMARY KEY (,
ru_rule_id),
num
KEY(
ru_rule_id) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=9998 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Create statement after:
rules
CREATE TABLE(
ru_rule_id
int(10) unsigned NOT NULL AUTO_INCREMENT,
ru_title
varchar(254) CHARACTER SET utf8 NOT NULL DEFAULT '',
ru_data
text CHARACTER SET utf8 NOT NULL,
ru_name
varchar(64) CHARACTER SET utf8 NOT NULL,
ru_tags
varchar(64) CHARACTER SET utf8 DEFAULT '',
ru_priority
varchar(12) CHARACTER SET utf8 NOT NULL DEFAULT '1',
ru_rule_id
PRIMARY KEY (,
ru_sid),
num
KEY(
ru_rule_id)
) ENGINE=MyISAM AUTO_INCREMENT=9954 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Best Answer
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.