Fixing MySQL "Incorrect string value": utf8 vs utf8mb4 Done Right

Posted by Kyle Hankinson August 14, 2026

---

A user leaves a comment ending in a thumbs-up emoji, and your application logs this:

ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x91\x8D' for column 'body' at row 1

The table looked reasonable when it was created years ago:

CREATE TABLE comments (
  id INT PRIMARY KEY AUTO_INCREMENT,
  body TEXT
) CHARACTER SET utf8;

The column claims to be UTF-8, the application sends valid UTF-8, and MySQL still rejects it. Walking through why, and fixing it at every layer, is the difference between a five-minute patch that resurfaces next month and actually being done with this error.

Reading the bytes in the message

MySQL shows you exactly what it choked on. F0 9F 91 8D is the UTF-8 encoding of U+1F44D, the thumbs-up emoji, and the significant part is that it is four bytes long. MySQL's historical utf8 character set stores at most three bytes per character. It is not real UTF-8; it is a subset now officially named utf8mb3, and current servers display it that way. On MySQL 8.4, SHOW CREATE TABLE for the table above reports DEFAULT CHARSET=utf8mb3.

Three bytes cover the Basic Multilingual Plane, which is why the column worked for years of English, European, and most CJK text. Everything beyond it needs four bytes: emoji, many less common CJK ideographs, mathematical symbols, and other supplementary-plane characters. The moment one arrives, error 1366.

The real UTF-8 type is utf8mb4, available since MySQL 5.5 and the server default since MySQL 8.0 (with collation utf8mb4_0900_ai_ci). MariaDB made utf8mb4 its default in 10.6. New databases are fine; this error lives in tables and connections created before those defaults, or created with an explicit utf8.

The layers that must agree

Fixing only the column is the classic half-fix, because character set is negotiated in more than one place:

1. The column (or its table/database default) controls what can be stored.
2. The connection controls how MySQL interprets the bytes your client sends.
3. The client library or driver decides what charset it requests, often via a charset option or SET NAMES.

The failure modes differ by layer, and one is much worse than the error. With the column already fixed but the connection charset wrong (here, a client connected with latin1 sending an emoji), MySQL raised no error at all in testing on 8.4. It stored this:

id	body	HEX(body)
2	Great job ðŸ‘	4772656174206A6F6220C3B0C5B8E28098C28D

That is mojibake, silently committed. The UTF-8 bytes were reinterpreted as latin1 characters and then re-encoded, and no amount of later charset fixing will un-mangle rows stored this way. If you are diagnosing this error, look at what is already in the table before altering anything; browsing the data in SQLPro for MySQL makes it immediately obvious whether past writes were rejected cleanly (error 1366) or corrupted quietly (mojibake like the above), which determines whether you also have data repair ahead of you.

To see the connection side, check:

SHOW VARIABLES LIKE 'character_set_c%';

You want character_set_client and character_set_connection reporting utf8mb4. Set your driver's charset option to utf8mb4 (not utf8), or issue SET NAMES utf8mb4 at connection setup. The full negotiation rules are in the manual's connection character sets chapter.

Converting the tables

The straightforward, verified fix:

ALTER TABLE comments CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Caution: on large tables this rebuilds the table and can lock writes for the duration, so run it in a maintenance window and take a backup first. Also note one documented surprise: CONVERT TO may widen types to guarantee the converted data fits. In testing, the TEXT column came back as MEDIUMTEXT. If you need exact types preserved, alter columns individually with ALTER TABLE ... MODIFY body TEXT CHARACTER SET utf8mb4 instead of converting the whole table. On MySQL 8.x, pick utf8mb4_0900_ai_ci as the collation; on MariaDB, utf8mb4_general_ci or the modern utf8mb4_uca1400_ai_ci (its 11.4 default).

After conversion, the emoji insert succeeds. But two side effects are worth knowing before you convert a whole schema.

Side effect 1: index length limits (error 1071)

utf8mb4 makes every character potentially four bytes, and index keys are sized in bytes. On old InnoDB row formats a single-column index maxes out at 767 bytes, so a VARCHAR(255) utf8mb4 unique key (255 x 4 = 1020 bytes) fails. Reproduced on MySQL 8.4 by forcing the old row format:

mysql> CREATE TABLE old_style (
    ->   email VARCHAR(255),
    ->   UNIQUE KEY (email)
    -> ) CHARACTER SET utf8mb4 ROW_FORMAT=COMPACT;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

The same table without ROW_FORMAT=COMPACT creates fine, because modern defaults (innodb_default_row_format=dynamic, standard in MySQL 5.7.9+ and MariaDB 10.2.2+) raise the limit to 3072 bytes. So this bites you only on tables carried forward with ROW_FORMAT=COMPACT or REDUNDANT, or servers with non-default settings. The old workaround of VARCHAR(191) keys is rarely needed now; rebuilding the table with a modern row format is the better fix. If you are also revisiting whether those columns should be VARCHAR at all, see VARCHAR vs TEXT vs NVARCHAR.

Side effect 2: mixed charsets and collations after a partial conversion

Convert half your tables and joins across the boundary start misbehaving. Comparing a utf8mb3 column to a utf8mb4 column did not error in testing, but the optimizer could no longer use a normal index lookup for the join (EXPLAIN showed it falling back to a hash join), which on real table sizes reads as "the query got slow after the migration". Mixing two collations of the same charset is louder. Verified on 8.4:

ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT)
and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

The cure for both is consistency: pick one charset and one collation, and convert everything that joins together, in one migration.

Moving between MySQL and MariaDB

One last trap for anyone restoring a MySQL 8 dump into MariaDB: MySQL's default collation utf8mb4_0900_ai_ci does not exist in older MariaDB. Verified on MariaDB 10.6:

ERROR 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'

MariaDB 10.10 and later accept the 0900 names and map them to their uca1400 equivalents (confirmed on 11.4, where the same DDL succeeds). If you are importing into an older MariaDB, rewrite the collation in the dump first; our guide to importing a SQL file into MySQL covers the mechanics of the import itself.

The end state you want is boring: utf8mb4 columns, utf8mb4 connections, one collation everywhere, on defaults that every current MySQL and MariaDB already ship. The manual's utf8mb4 chapter is the reference when a legacy system will not let you get there in one step.

---