Fix MySQL 'Incorrect string value' errors by moving to utf8mb4

If MySQL rejects an emoji or a rare character with ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x9A\x80', the column is almost certainly using the 3-byte utf8mb3 character set (often declared as utf8). The fix is to convert the column, table and connection to utf8mb4, MySQL's full 4-byte UTF-8, and pick one collation for the whole schema.

This guide shows how to confirm the cause, find every affected table, convert safely and avoid the two errors that usually follow a careless conversion: Specified key was too long and Illegal mix of collations. Every statement and error message below was run on MySQL 8.4.11 LTS in a throwaway schema; the behaviour is the same on 9.7 LTS.

Why the error happens

UTF-8 encodes characters in one to four bytes. MySQL's utf8mb3 stores at most three bytes per character, so it can hold only the Basic Multilingual Plane (BMP). Emoji, many historic scripts, musical and mathematical symbols and some CJK ideographs live outside the BMP and need four bytes. utf8mb4 is a superset of utf8mb3: BMP characters are stored identically, and supplementary characters get their fourth byte.

Here is the failure, reproduced on a table created with the legacy character set and the default strict SQL mode:

CREATE TABLE legacy_comments (
  id     INT PRIMARY KEY,
  author VARCHAR(100),
  body   TEXT
) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci;

INSERT INTO legacy_comments VALUES (1, 'Ana', 'Great post 🚀');
-- ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x9A\x80'
--   for column 'body' at row 1

The four bytes in the message, F0 9F 9A 80, are the UTF-8 encoding of the rocket emoji. Any value whose first byte is \xF0 to \xF4 is a 4-byte character, which is your confirmation that this is a character set problem rather than corrupted input.

Without strict mode, MySQL would store the row with the character replaced and only a warning, which is worse: data is lost silently. Keep strict mode on.

Where MySQL 8.4 stands on utf8 and utf8mb3

• The server defaults are character_set_server = utf8mb4 and collation_server = utf8mb4_0900_ai_ci. New schemas get these unless you say otherwise.
• utf8mb3 is deprecated. The manual says it remains supported for the lifetimes of the 8.0 and 8.4 LTS series and should be expected to disappear in a future major release.
• utf8 is still an alias for utf8mb3. Using it raises warning 3719: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. SHOW CREATE TABLE already prints utf8mb3.

So the problem is rarely a fresh 8.4 install. It is tables created years ago, dumps restored with old CREATE TABLE statements, or application code that still says utf8.

Step 1: find every column still on utf8mb3

Check the schema default, the table defaults and, most importantly, the columns. Column settings are what decide whether an insert succeeds.

-- Schema defaults (used for new tables)
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'app';

-- Table defaults
SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'app' AND TABLE_COLLATION NOT LIKE 'utf8mb4%';

-- Columns that cannot store 4-byte characters
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'app'
  AND CHARACTER_SET_NAME IS NOT NULL
  AND CHARACTER_SET_NAME <> 'utf8mb4'
ORDER BY TABLE_NAME, ORDINAL_POSITION;

Replace app with your schema name. Also list the distinct collations in use across utf8mb4 columns; if you see more than one, plan to unify them in the same change.

Step 2: check the connection, not just the tables

A perfect table still breaks if the client talks to the server in a 3-byte character set. The server reads statements in character_set_client, converts them to character_set_connection and returns results in character_set_results. SET NAMES 'utf8mb4' sets all three, and sets collation_connection to the default collation for utf8mb4.

SHOW SESSION VARIABLES LIKE 'character\_set\_%';
SHOW SESSION VARIABLES LIKE 'collation\_%';

In application code, set the character set in the driver configuration rather than with a manual SET NAMES, so that the driver's own escaping knows about it. For PHP's PDO that means charset=utf8mb4 in the DSN; Laravel's MySQL connection uses utf8mb4 in its default configuration. If your config still says utf8, change it to utf8mb4.

Step 3: convert tables to utf8mb4

Change the schema default first so new tables are correct, then convert each table. CONVERT TO CHARACTER SET changes the table default and every character column in one statement.

ALTER DATABASE app
  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

ALTER TABLE legacy_comments
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

INSERT INTO legacy_comments VALUES (2, 'Ana', 'Great post 🚀');
SELECT body, LENGTH(body) AS bytes, CHAR_LENGTH(body) AS chars
FROM legacy_comments WHERE id = 2;
-- Great post 🚀 | 15 | 12

Three things are worth knowing before you run this on production:

• It rebuilds the table. On 8.4, asking for ALGORITHM=INPLACE fails with error 1846, Cannot change column type INPLACE. Try ALGORITHM=COPY. A copy rebuild blocks writes while it runs, so schedule large tables in a maintenance window or use an online schema change tool.
• TEXT types can grow. In our test, CONVERT TO changed the TEXT column to MEDIUMTEXT so it could still hold the same number of characters. Check SHOW CREATE TABLE afterwards and update your migrations so they match.
• Existing data is preserved. utf8mb3 data cannot contain supplementary characters, so there is nothing to re-encode. Conversion does not repair text that was already garbled (mojibake) by a misconfigured client; that needs a separate cleanup.

Gotcha 1: Specified key was too long

Index limits are measured in bytes, not characters, and utf8mb4 reserves four bytes per character. With the default DYNAMIC row format, an InnoDB index key prefix can be up to 3072 bytes, which is 768 utf8mb4 characters. Tables still using COMPACT or REDUNDANT are limited to 767 bytes, or 191 utf8mb4 characters, which is where the famous VARCHAR(191) convention comes from.

CREATE TABLE t_idx2 (
  a VARCHAR(500), b VARCHAR(500), KEY (a, b)
) CHARACTER SET utf8mb4;
-- ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

Fixes, in order of preference: make sure old tables use ROW_FORMAT=DYNAMIC; shorten columns that never need their declared length (an email address does not need 500 characters); or index a prefix, such as KEY (url(191)), accepting that a prefix index cannot enforce uniqueness of the full value.

Gotcha 2: Illegal mix of collations

Convert half the schema to utf8mb4_0900_ai_ci while older tables use utf8mb4_unicode_ci, and joins between them fail:

SELECT * FROM t_a JOIN t_b ON t_a.c = t_b.c;
-- ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT)
--   and (utf8mb4_unicode_ci,IMPLICIT) for operation '='

Pick one collation for the whole application and convert everything to it. Adding COLLATE to the query makes the error go away but usually stops MySQL using an index on the converted side, so it is a stopgap, not a fix.

Choosing a collation

The collation decides how strings compare, sort and collide in unique indexes. These were checked with SELECT comparisons on 8.4.11:

For a new or fully converted application, utf8mb4_0900_ai_ci is the sensible default: it is the server default, the manual notes UCA 9.0.0 collations are faster than older UCA ones, and it handles modern Unicode. If your framework or existing schema is standardised on utf8mb4_unicode_ci, staying on it consistently is better than mixing.

Gotcha 3: new duplicate keys after conversion

Changing collation changes what counts as equal. In our test, a UNIQUE column holding both Strasse and Straße was valid under utf8mb3_general_ci, but converting it to utf8mb4_0900_ai_ci failed with ERROR 1062 (23000): Duplicate entry 'Straße' for key 'cv.name'. Find collisions before you convert:

SELECT a.id, b.id, a.name, b.name
FROM cv AS a
JOIN cv AS b
  ON a.id < b.id
 AND CONVERT(a.name USING utf8mb4) COLLATE utf8mb4_0900_ai_ci
   = CONVERT(b.name USING utf8mb4) COLLATE utf8mb4_0900_ai_ci;

This self-join scans the table, so run it on a replica or a restored copy for large tables. Resolve each pair with the data owner before the migration.

A safe conversion checklist

1. Take and test a backup, and rehearse the conversion on a restored copy.
2. List non-utf8mb4 columns and all collations in use (Step 1).
3. Check unique indexes for collisions under the target collation.
4. Check index lengths and row formats; fix anything over 3072 bytes.
5. Set the driver character set to utf8mb4 in every application and job.
6. Run ALTER DATABASE, then ALTER TABLE ... CONVERT TO per table, largest last.
7. Insert a 4-byte test string through the application, not just the MySQL client.
8. Update your migrations and ORM models so new tables are created as utf8mb4.

If you replicate, every replica must understand the character set used on the source; the manual specifically warns against running utf8mb3 on the source and utf8mb4 on a replica because the index length limits differ. Convert in a way that keeps source and replicas identical.

What to do next

Run the Step 1 queries today even if nothing is failing yet: every utf8mb3 column is a future support ticket and a blocker for the release that removes utf8mb3. Once the schema is clean, add a check to your migration review so that utf8 and utf8mb3 never come back.