"Incorrect string value" while migrating from MSSQL to MySQL with Workbench

Question

I got the task to migrate our MSSQL database into a MySQL database. Therefore I am using the recent version of MySQL Workbench and FreeTDS Driver.

During the migration I always get errors like this:

ERROR: `dbo`.`emails_text`:Inserting Data: Incorrect string value: '\xA0</td>...' for column 'description' at row 1
ERROR: `dbo`.`prospect_lists`:Inserting Data: Incorrect string value: '\xFCrs MB...' for column 'description' at row 4
ERROR: `dbo`.`campaigns`:Inserting Data: Incorrect string value: '\xFChrung...' for column 'name' at row 24

I assume that utf8 is the troublemaker but I couldn't find any solution to my problem till now.

Answer 1

I had to do the following:

In the source configuration (ODBC FreeTDS), tab "Advanced", I set:

TDS_VERSION=7.2;ClientCharset=UTF-8

and check "Driver sends Unicode data as UTF-8". (for more info in http://www.freetds.org/userguide/odbcconnattr.htm )

Then, in the target configuration, tab "Advanced", in textbox "Others" I added the following new line: (change it as you whish, always utf8* )

preInit=SET default_storage_engine=MYISAM,character_set_connection=utf8mb4,collation_connection=utf8mb4_spanish_ci,collation_server=utf8mb4_spanish_ci,character_set_server=utf8mb4

(for more info see: https://dev.mysql.com/doc/refman/5.7/en/server-options.html https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html https://dev.mysql.com/doc/connector-cpp/en/connector-cpp-connect-options.html )

Finally in the Data Copy step, in Options group check "Driver sends data already encoded as UTF-8".

Answer 2

Which of these look correct for \xFC ?

             armscii8   2  1 'Ֆ'
cp1250, cp1256, cp1257, dec8, latin1, latin2, latin5, latin7   2  1 'ü'
               cp1251   2  1 'ь'
                cp850   2  1 '³'
                cp852   2  1 'Ř'
                greek   2  1 'ό'
         koi8r, koi8u   2  1 'Э'
                macce   2  1 'Ł'
             macroman   2  1 '¸'
       cp866, keybcs2   3  1 'ⁿ'

If none of them, then MSSQL is generating unknown garbage.

\A0 is a "hard blank" in many character sets. Some exceptions:

cp850, cp852, keybcs2   2  1 'á'
                cp866   2  1 'а'
         koi8r, koi8u   3  1 '═'
      macce, macroman   3  1 '†'

You could move forward by declaring the column in question to be CHARACTER SET latin1 and live with the garbage until figuring out what to do.

Do you have more examples of bad characters? Maybe a pattern will emerge.