SwapSQL

Migration errors

Fix: "Incorrect string value" when importing into MySQL

ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x80' for column 'name' at row 1

When it happens

Importing a dump or inserting data into a MySQL table whose column or connection still uses utf8 (utf8mb3) — the bytes in the message are an emoji or other 4-byte UTF-8 character.

Why it happens

MySQL's legacy "utf8" is really utf8mb3: at most 3 bytes per character, so it cannot store 4-byte code points. \xF0\x9F\x98\x80 is the UTF-8 encoding of 😀, which doesn't fit, so the INSERT is rejected.

How to fix it

1. Convert the affected table to real UTF-8 (utf8mb4)

ALTER TABLE t
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Repeat for every table that holds emoji or non-BMP characters.

2. Fix the connection and server defaults too

mysql --default-character-set=utf8mb4 mydb < dump.sql

and set character-set-server=utf8mb4 in my.cnf so new tables default to it. A utf8mb4 column still fails if the connection negotiates utf8mb3.

3. Migrating to PostgreSQL instead?

PostgreSQL stores the full UTF-8 range (4-byte characters included) natively, so emoji just work — our converter maps utf8mb3 and utf8mb4 columns to UTF-8 text automatically.

Migrating between MySQL and PostgreSQL?

Our converter handles this and dozens of other gotchas automatically — upload a dump, download working SQL.

Try the converter free

More: all migration guides & error fixes