Fix "Incorrect string value" in MySQL (error 1366)

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

Common causes

• The table or column uses utf8/utf8mb3, which cannot store 4-byte characters like emoji (bytes starting \xF0)
• The connection charset is utf8 or latin1 while the app sends utf8mb4 data
• The input is Windows-1252/Latin-1 bytes (such as \xE9 for é) sent as if it were UTF-8
• An old dump or CSV import created tables with latin1 default charset
• A single column was left on an old charset after the table was converted

How to fix it

1. Check the column charset. Run SHOW FULL COLUMNS FROM reviews; and SHOW CREATE TABLE reviews; Look at Collation for the failing column; utf8mb3_* or latin1_* cannot hold emoji.
2. Set the connection to utf8mb4. In PDO use a DSN with charset=utf8mb4, in mysqli call $db->set_charset('utf8mb4'), and in WordPress set DB_CHARSET to 'utf8mb4'. Without this, MySQL converts on the way in and still fails.
3. Convert the table to utf8mb4. After a backup run ALTER TABLE reviews CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; (or utf8mb4_0900_ai_ci on MySQL 8). Repeat for each affected table and set the database default too.
4. Fix source data that is not UTF-8. If the bytes look like \xE9 rather than \xC3\xA9, the input is Latin-1. Convert files with iconv -f WINDOWS-1252 -t UTF-8 or mb_convert_encoding() in PHP before inserting.
5. Set server defaults. Add character-set-server = utf8mb4 and collation-server = utf8mb4_unicode_ci under [mysqld] so new tables are created correctly. MySQL 8 already defaults to utf8mb4.

SQL + PHP

ALTER DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE reviews CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

// PHP PDO connection
$pdo = new PDO('mysql:host=localhost;dbname=shop;charset=utf8mb4', $user, $pass);

How to stop it happening again

• Use utf8mb4 for the database, every table and every connection
• Convert imported CSV and text files to UTF-8 before loading them
• Check dumps for CHARSET=utf8 or latin1 before importing them into a new server
• Keep strict SQL mode on so bad data fails loudly instead of being truncated