2

I am trying to migrate a SQL-Server database to MYSQL. I am getting the error

"Incorrect string value: '\xF0\x9F\x98\x8B\xF0\x9F...'"

I am using the batch file created by MySQL Workbench 8.0 CE to see the error because the UI crashes and freezes.

The source table is in Latin1_General_CI_AS

The destination table has been created as utf8mb4_0900_ai_ci

Source database: Mssql@DRIVER=ODBC Driver 17 for SQL Server;SERVER=localhost\SQLEXPRESS (Microsoft SQL Server)

Target database: Mysql@localhost:3306

Is there anything else I can try?

Improve this question
3
  • The error is clear. You tried to run a rather weird string. SQL Server doesn't use such strings as it supports Unicode (UTF16) through nvarchar and nchar columns. You can simply type the text you want. What does the script look like and where did that string come from? Commented May 29, 2019 at 11:20
  • You're asking a migration question without providing any information about how you tried to migrate the data or any transformations that took place. This string could be due to an attempt by some tool to either treat UTF16 text as if it were ASCII, or some other strange and unnecessary conversion that ended up using escape sequences instead of characters. Commented May 29, 2019 at 11:25
  • As for unnecessary, SO itself is an ASP.NET application that saves text in nvarchar columns. The pages are served using UTF8 encoding. No special code is needed to allow any poster to post non US-ASCII characters like αυτό εδώ or ²³£§¶¤¦°±. You could export the Unicode text to a UTF8 file easily in SQL Server and load it into MySQL. You could use SQL Server's SSIS to move the data directly from one database to the other. There's no reason to use escape sequences Commented May 29, 2019 at 11:26

1 Answer 1

Reset to default
1

That hex is not Latin1, nor UTF16; it matches the UTF-8 encoding for 😋. (That's the first 4 bytes; the next two are probably the beginning of another Emoji.)

The connection parameters need to say that the data is encoded utf8mb4. It is not sufficient to specify the table to be utf8mb4.

The source table was not Latin1. Or you did not notice that that Emoji displayed as this Mojibake: 😋.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.