Description:
Migrating a Drupal database from MS SQL 2008 to MySQL 5.7 using MySQL Workbench Database Migration. I am doing this in Windows 7.
Migration fails.
This is a common error with lots of frequently asked question on boards but no real solution except to use utfmb4 which fails for me.
How to repeat:
I have two System DSNs configured. The source DSN uses SQL Server Native Client 11.0. I have ANSI quoted identifiers and ANSI null, paddings and warnings are checked. The target DSN uses MySQL ODBC 5.3 Unicode Driver.
My empty target DB is using default collation of utf8mb4_unicode_ci and default character set of utf8mb4.
I start migration and the Source RDBMS Connection Parameters are Microsoft SQL Server. Connection Method is ODBC Data Source (FreeTDS). I am using my MS SQL Source DSN and Protocol Version is Microsft SQL Server 2008 7.2. In the Advanced tab I have Driver sends Unicode data as UTF-8 selected.
The next screen is Target RDBMS Connection Parameters and for MySQL I am using the Connection Method of Standard (TCP/IP). Nothing is checked off in the Advanced tab but there is text in the Others box:
DbSqlEditor:LastDefaultSchema= OPT_READ_TIMEOUT=300 lastConnected=1469033848 serverVersion=5.7.13-log
I continue through the migration wizard and when I reach Schemas Selection I choose the DB I want migrated. I move on to the Source Objects step and I deselect all tables but one test table in which I have only one record--the record that is causing my migration to fail.
When I reach Manual Editing I see:
No migration problems found. 1 warning(s).
I choose the View, All Objects see that my table has a warning of, "Migration warnings, expand to view. I drill down to the field that is giving me a problem and see the message, "Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci." Remember that I configured this db to use utf8mb4_unicode_ci and not utf8_general_ci but Workbench wants this table to be utf8_general_ci.
In the Target Creation Options screen I choose "Keep shemas if they already exist..." I don't want my db to be reconfigured.
At the Create Target Results screen I change my collation. Oddly the character set I want is set just not the collation. I add the collation of utf8mb4_unicode_ci to the column I am having a problem with.
title VARCHAR(255) CHARACTER SET 'utf8mb4' COLLATION 'utf8mb4_unicode_ci' NOT NULL,
and click Apply.
I continue and at the Bulk Data Transfer screen I get the usual error.
14:42:18 [INF][ copytable]: Statement execution failed: Incorrect string value: '\x92s Web...' for column 'title' at row 1:
INSERT INTO FAS_Drupal.node_test (nid, vid, type, language, title, uid, status, created, changed, comment, promote, sticky, tnid, translate, __pk, __unique_vid, rh_action, rh_redirect, rh_redirect_response) VALUES (32,235,'article','und','Japan�s Site',1,1,1360234200,1370630023,0,1,0,0,0,'99258CF3-986F-4C56-92D1-08654C71D9DD',NULL,-1,'',301)
Look at the word Japan's above. It has a square instead of a Windows curly single closing quote. From the error above I believe the issue is that \x92s (the curly quote) cannot be written to this table.
