MySQL Incorrect string value '\xF0\x9F\x98\x80': emoji in utf8mb3 columns

Two weeks after we upgraded our shop's database from MySQL 5.7 to 8.0, customer service forwarded a complaint: a customer had written a long product review, pressed submit and got "Something went wrong". She tried three times. The Laravel log had the reason:

SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF0\x9F\x98\x80' for column 'body' at row 1
(Connection: mysql, SQL: insert into `reviews` (`product_id`, `user_id`, `rating`, `body`, `updated_at`, `created_at`) values (8812, 40211, 5, Love it ..., 2024-10-01 09:12:44, 2024-10-01 09:12:44))

\xF0\x9F\x98\x80 is the UTF-8 encoding of the grinning face emoji, U+1F600. A query against the error tracker showed 312 failed review submissions in two weeks, all containing emoji or other characters outside the Basic Multilingual Plane.

While investigating, we found a second, quieter symptom: reviews imported nightly from a partner marketplace contained ???? where the original text had emoji. Those inserts had succeeded.

Reproducing it

CREATE TABLE t (body TEXT) CHARACTER SET utf8mb3;
SET NAMES utf8mb4;
INSERT INTO t VALUES (CONCAT('Great product ', _utf8mb4 X'F09F9880'));  -- U+1F600

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

With sql_mode strict (the default in 8.0) it is an error. Without strict mode it becomes a warning and the value is truncated at the first unsupported character.

What we ruled out

• Laravel's configuration. config/database.php already had 'charset' => 'utf8mb4' and 'collation' => 'utf8mb4_unicode_ci', so the web app's connection was correct.
• The MySQL 8.0 server default. character_set_server was utf8mb4, the 8.0 default. But server and database defaults only apply to tables created later; existing tables keep their own charset.
• The web form or a proxy mangling input. The bytes in the error message were valid UTF-8 for the emoji, so the data arrived intact.

Root cause

The reviews table had been created in 2017 with DEFAULT CHARSET=utf8, which in MySQL means utf8mb3: at most three bytes per character. Emoji need four bytes in UTF-8, so the column could not store them, and strict mode turned that into error 1366. The upgrade to 8.0 did not change existing tables. The nightly import was a separate Python job using PyMySQL with charset="utf8", which negotiated a three-byte connection charset; MySQL converted the unsupported characters to ? before they reached the table, so nothing failed and data was silently lost.

What is happening under the hood

MySQL's historical utf8 charset is utf8mb3, a subset of UTF-8 limited to the Basic Multilingual Plane (U+0000 to U+FFFF). Characters above that, including emoji, many CJK extension characters and historic scripts, need four bytes and require utf8mb4. The MySQL manual notes that utf8 is an alias for utf8mb3 and that utf8mb3 is deprecated.

Three settings matter for every string that reaches a column:

If any layer is three-byte, four-byte characters are either rejected or replaced with ?.

Environment

Minimal reproduction

The SQL in the Investigate section reproduces the error. This reproduces the silent replacement:

CREATE TABLE t2 (body VARCHAR(100)) CHARACTER SET utf8mb4;
SET NAMES utf8mb3;
INSERT INTO t2 VALUES (CONCAT('Great product ', _utf8mb4 X'F09F9880'));
SELECT body, HEX(body) FROM t2;

The stored value ends in ? (hex 3F), even though the column itself supports emoji.

Who is affected

Databases created before MySQL 8.0 with utf8 defaults, or created on 8.0 by tools or ORMs that still request utf8. Also any client that connects with a three-byte charset, which includes older drivers and explicit charset=utf8 settings in DSNs. MariaDB has the same utf8 alias behaviour by default.

The fix

1. Find every three-byte column.

SELECT table_name, column_name, character_set_name, collation_name
FROM   information_schema.columns
WHERE  table_schema = 'shop' AND character_set_name = 'utf8mb3'
ORDER  BY table_name;

2. Fix connection charsets in every client. The import job was a one-line change:

-conn = pymysql.connect(host=DB_HOST, user=DB_USER, password=DB_PASS, db="shop", charset="utf8")
+conn = pymysql.connect(host=DB_HOST, user=DB_USER, password=DB_PASS, db="shop", charset="utf8mb4")

3. Convert tables. For each table we ran:

ALTER TABLE reviews CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

We kept utf8mb4_unicode_ci to match Laravel's collation and to avoid comparison changes discussed below. CONVERT TO also promotes TEXT columns to MEDIUMTEXT where needed so that they can still hold the same number of characters at four bytes each.

4. Handle index length limits. Converting nine old tables failed:

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

Those tables used ROW_FORMAT=COMPACT, where an index key prefix is limited to 767 bytes. A VARCHAR(255) column needs 765 bytes in utf8mb3 but 1,020 bytes in utf8mb4. Switching those tables to DYNAMIC, the default row format since MySQL 5.7, raises the limit to 3,072 bytes:

ALTER TABLE customers ROW_FORMAT=DYNAMIC;
ALTER TABLE customers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

One composite index on (email, external_ref) with two VARCHAR(512) columns exceeded even 3,072 bytes. We changed it to a prefix index on external_ref(191) after checking that the first 191 characters were unique in practice.

5. Set database defaults so new tables are right without thinking about it:

ALTER DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Alternatives and trade-offs

1. Online schema change tools. CONVERT TO CHARACTER SET rebuilds the table and blocks writes while it copies. Small tables took seconds; for reviews (19 million rows) we used gh-ost to copy in the background and cut over in a short lock.
2. utf8mb4_0900_ai_ci collation. The MySQL 8.0 default, faster and based on newer Unicode rules. It is a NO PAD collation, so trailing spaces become significant in comparisons and unique indexes, and mixing it with utf8mb4_unicode_ci in joins raises error 1267, "Illegal mix of collations". Switching collation is a separate project for us.
3. Stripping four-byte characters in the application. Avoids the migration but throws away what users wrote. We only consider this for fields like usernames where we deliberately restrict the character set.

How to test the fix

A feature test posts a review containing several four-byte characters and reads it back:

public function test_review_body_round_trips_four_byte_characters(): void
{
    $body = "Great fit \u{1F600}, colour \u{1F7E6}, size \u{2070E} ok";
    $this->actingAs(User::factory()->create())
        ->post(route('reviews.store', $product = Product::factory()->create()), ['rating' => 5, 'body' => $body])
        ->assertRedirect();

    $this->assertSame($body, $product->reviews()->latest('id')->value('body'));
}

For the import job, a test inserts and reads back the same string through PyMySQL and asserts no ? appears. We also run this check against staging after every migration:

mysql -e "SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='shop' AND character_set_name='utf8mb3';"

Confirming in production

After the migration the utf8mb3 column count was zero, and error 1366 disappeared from the logs. Review submissions with emoji went from 312 failures in two weeks to roughly 90 successful submissions per day, which tells us how many people had quietly given up before. We re-ran the partner import for the affected months from the partner's source files, restoring about 4,800 reviews that had been stored with question marks.

Regression guard

• A CI test runs the migrations on an empty MySQL 8.0 instance and fails if any column in the schema uses utf8mb3.
• Every database client's connection settings are defined in one shared config module that uses utf8mb4.
• The partner import compares character counts before and after insert for a sample of rows and alerts if ? count increases.

Security implications

The error itself is not a vulnerability, but charset handling has a history of security issues: mismatched client and server charsets have enabled SQL injection bypasses of escaping functions in the past, and silent truncation at an invalid character has been used to bypass uniqueness checks, for example registering admin followed by U+1F600, which truncates to admin in non-strict mode. Keeping strict sql_mode, using parameterised queries and using one consistent utf8mb4 charset end to end avoids both classes of problem.

Prevention checklist

• Use utf8mb4 for server, database, tables, columns and every client connection.
• Never write utf8 in DDL or DSNs; spell out utf8mb4.
• Keep STRICT_TRANS_TABLES on so bad data fails loudly instead of being truncated.
• Check row format and index lengths before converting legacy tables.
• Test round trips with four-byte characters in CI.

References

• MySQL 8.0 Reference Manual, The utf8mb4 Character Set — https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb4.html
• MySQL 8.0 Reference Manual, Converting Between 3-Byte and 4-Byte Unicode Character Sets — https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-conversion.html
• MySQL 8.0 Reference Manual, Connection Character Sets and Collations — https://dev.mysql.com/doc/refman/8.0/en/charset-connection.html
• MySQL 8.0 Reference Manual, InnoDB Limits (index key prefix length) — https://dev.mysql.com/doc/refman/8.0/en/innodb-limits.html
• MySQL 8.0 Reference Manual, ALTER TABLE, CONVERT TO CHARACTER SET — https://dev.mysql.com/doc/refman/8.0/en/alter-table.html