Problems during migration from SQL Server to MySQL via MySQL Workbench

kai 07/30/2018. 1 answers, 71 views
sql-server mysql migration mysql-workbench

We've found most of the options we needed to include the necessary CHARSET. Here is what we've included in the MySQL my.cnf:

[client] 
default-character-set = utf8mb4 

[mysql] 
default-character-set = utf8mb4 

[mysqld] 
character-set-client-handshake = FALSE 
character-set-server = utf8mb4 
collation-server = utf8mb4_unicode_ci

But we still get an error which points to a wrong charset and/or collation:

10:17:38 [INF][      copytable]: Statement execution failed: Incorrect string value: '\xF3\xB3\xB4\xAF)<...' for column 'LangText' at row 57

Complete Log file: https://ufile.io/52s13

Can anybody point to a solution for his issue? Thanks!




We need to migrate a SQL Server database to MySQL for the first time (for a customer project). To do so, we've quickly set up a local testing machine to try one of the many tutorials to migrate via MySQL Workbench.

Installed/ prerequisites:

  • Local Windows 10 (64-bit)
  • MySQL Workbench 6.3 (Community Server)
  • MySQL Server 8.0
  • Microsoft SQL Server Management Studio (SSMS) 17.0
  • Microsoft SQL Server 2017 (Developer Version)
  • Driver: SQL Server Native Client 11.0

Unfortunately, we run into some problems during migration and re-importing the DB on another machine. To quickly move forward, we edited the resulting SQL dump with the following queries:

Regarding Export:

sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' mssql_to_mysql_20180730.local.sql
sed -i 's/ENGINE=InnoDB DEFAULT/ENGINE=InnoDB ROW_FORMAT=DYNAMIC/g' mssql_to_mysql_20180730.local.sql
sed -i 's/DEFAULT CHARSET=utf8mb4/ROW_FORMAT=DYNAMIC CHARSET=utf8mb4/g' mssql_to_mysql_20180730.local.sql

Performance:

sed -i '1s/^/SET foreign_key_checks=0;\nSET unique_checks=0;\nSET autocommit=0;\n/' mssql_to_mysql_20180730.local.sql
sed -i -e "\$aCOMMIT;\nSET unique_checks=1;\nSET foreign_key_checks=1;\n" mssql_to_mysql_20180730.local.sql

Additionally, we've adjusted the target db:

SET GLOBAL innodb_default_row_format=DYNAMIC;

How can we set these options during the migration in workbench? Is there an overview on which options can be altered during the migration configuration?

1 Answers


Rick James 08/21/2018.

I think F3B3B4AF is beyond the end of established UTF-8 characters. Perhaps MSSQL allowed, but MySQL did not.

Please provide SHOW CREATE TABLE for the target table. It had better be using utf8mb4 for the target column.

Do any of these look correct?

                              gb2312, gbk   6  2 '蟪疮'
                                    euckr   6  2 '車눕'
                                     big5   6  2 '馧敞'
                            eucjpms, ujis   6  2 '鶻姦'

HighResolutionMusic.com - Download Hi-Res Songs

1 The Chainsmokers

Beach House flac

The Chainsmokers. 2018. Writer: Andrew Taggart.
2 (G)I-DLE

POP/STARS flac

(G)I-DLE. 2018. Writer: Riot Music Team;Harloe.
3 Ariana Grande

​Thank U, Next flac

Ariana Grande. 2018. Writer: Crazy Mike;Scootie;Victoria Monét;Tayla Parx;TBHits;Ariana Grande.
4 Anne-Marie

Rewrite The Stars flac

Anne-Marie. 2018. Writer: Benj Pasek;Justin Paul.
5 Clean Bandit

Baby flac

Clean Bandit. 2018. Writer: Jack Patterson;Kamille;Jason Evigan;Matthew Knott;Marina;Luis Fonsi.
6 Nicki Minaj

No Candle No Light flac

Nicki Minaj. 2018. Writer: Denisia “Blu June” Andrews;Kathryn Ostenberg;Brittany "Chi" Coney;Brian Lee;TJ Routon;Tushar Apte;ZAYN;Nicki Minaj.
7 BlackPink

Kiss And Make Up flac

BlackPink. 2018. Writer: Soke;Kny Factory;Billboard;Chelcee Grimes;Teddy Park;Marc Vincent;Dua Lipa.
8 Imagine Dragons

Bad Liar flac

Imagine Dragons. 2018. Writer: Jorgen Odegard;Daniel Platzman;Ben McKee;Wayne Sermon;Aja Volkman;Dan Reynolds.
9 BTS

Waste It On Me flac

BTS. 2018. Writer: Steve Aoki;Jeff Halavacs;Ryan Ogren;Michael Gazzo;Nate Cyphert;Sean Foreman;RM.
10 Halsey

Without Me flac

Halsey. 2018. Writer: Halsey;Delacey;Louis Bell;Amy Allen;Justin Timberlake;Timbaland;Scott Storch.
11 Little Mix

Woman Like Me flac

Little Mix. 2018. Writer: Nicki Minaj;Steve Mac;Ed Sheeran;Jess Glynne.
12 Brooks

Limbo flac

Brooks. 2018.
13 Fitz And The Tantrums

HandClap flac

Fitz And The Tantrums. 2017. Writer: Fitz And The Tantrums;Eric Frederic;Sam Hollander.
14 Backstreet Boys

Chances flac

Backstreet Boys. 2018.
15 Lady Gaga

I'll Never Love Again flac

Lady Gaga. 2018. Writer: Benjamin Rice;Lady Gaga.
16 Diplo

Close To Me flac

Diplo. 2018. Writer: Ellie Goulding;Savan Kotecha;Peter Svensson;Ilya;Swae Lee;Diplo.
17 Rita Ora

Velvet Rope flac

Rita Ora. 2018.
18 Bradley Cooper

Always Remember Us This Way flac

Bradley Cooper. 2018. Writer: Lady Gaga;Dave Cobb.
19 Imagine Dragons

Machine flac

Imagine Dragons. 2018. Writer: Wayne Sermon;Daniel Platzman;Dan Reynolds;Ben McKee;Alex Da Kid.
20 Erika Sirola

Speechless flac

Erika Sirola. 2018. Writer: Teemu Brunila;Stefan Dabruck;Jürgen Dohr;Guido Kramer;Dennis Bierbrodt;Chris Braide;Robin Schulz.

Related questions

Hot questions

Language

Popular Tags