MySQL 'Incorrect string value' (error 1366): converting utf8mb3 and latin1 tables to utf8mb4 safely

#Symptom

Inserts or updates fail, usually only for some users or some records:

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

A value starting with \xF0 is a 4-byte UTF-8 character (emoji, some rarer CJK characters, mathematical symbols). The column, or the connection, is using utf8mb3 (called utf8 in older output) or latin1, which cannot store it. If sql_mode is not strict, MySQL may instead store ? or truncate the string with a warning, which is worse because nobody notices.

#Check 1: what the column actually uses

SELECT table_schema, table_name, column_name, character_set_name, collation_name, column_type
FROM   information_schema.columns
WHERE  table_schema = 'appdb'
  AND  table_name   = 'reviews'
  AND  character_set_name IS NOT NULL;

Then list everything in the schema that is not utf8mb4:

SELECT table_name, column_name, character_set_name, collation_name
FROM   information_schema.columns
WHERE  table_schema = 'appdb'
  AND  character_set_name IS NOT NULL
  AND  character_set_name <> 'utf8mb4'
ORDER BY table_name, ordinal_position;

MySQL 8.0.30 and later report the 3-byte set as utf8mb3; older versions show utf8. Both mean the same thing.

Table and schema defaults matter for columns added later:

SELECT schema_name, default_character_set_name, default_collation_name
FROM   information_schema.schemata WHERE schema_name = 'appdb';

SELECT table_name, table_collation, row_format
FROM   information_schema.tables
WHERE  table_schema = 'appdb' AND table_collation NOT LIKE 'utf8mb4%';

#Check 2: what the connection uses

A utf8mb4 column still fails if the client talks to the server in utf8mb3 or latin1.

SHOW SESSION VARIABLES WHERE Variable_name IN
 ('character_set_client','character_set_connection','character_set_results',
  'character_set_server','collation_connection','collation_server');

Run this from the application's connection (or check performance_schema.session_variables for its thread), not just from your own mysql client.

#Driver settings

• mysql client: mysql --default-character-set=utf8mb4
• Any session: SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci; (use utf8mb4_unicode_ci on 5.7)
• Connector/J: characterEncoding=UTF-8 in the JDBC URL (Connector/J 8.x maps it to utf8mb4)
• PHP PDO: charset=utf8mb4 in the DSN
• Python mysql-connector-python or PyMySQL: charset='utf8mb4'

On the server, set the default so new databases and connections start right:

[mysqld]
character_set_server = utf8mb4
collation_server     = utf8mb4_0900_ai_ci

MySQL 8.0 and later already default to these. On RDS, Aurora and Azure Flexible Server, change them in the parameter group or server parameters.

#Before you convert

#Index key length

utf8mb4 needs up to 4 bytes per character. With ROW_FORMAT=DYNAMIC or COMPRESSED (the default in 5.7.9+ and 8.x) the InnoDB index key limit is 3,072 bytes, so VARCHAR(255) (1,020 bytes) is fine. Tables still on COMPACT or REDUNDANT have a 767-byte limit, which allows only VARCHAR(191). Check row_format in the query above and add ROW_FORMAT=DYNAMIC to the ALTER if needed.

Composite indexes add up: an index on two VARCHAR(500) columns is 4,000 bytes in utf8mb4 and will fail.

#Collation changes can create duplicates

Converting utf8mb3_general_ci to utf8mb4_0900_ai_ci changes comparison rules. Values that were distinct under the old collation can compare equal under the new one, and a unique index then fails the ALTER with a duplicate-key error. Test on a restored copy first, or check with:

SELECT CONVERT(username USING utf8mb4) COLLATE utf8mb4_0900_ai_ci AS k, COUNT(*)
FROM   appdb.users
GROUP BY k HAVING COUNT(*) > 1;

#Double-encoded data

If an application wrote UTF-8 bytes through a latin1 connection into a latin1 column, the bytes are really UTF-8. CONVERT TO CHARACTER SET would encode them a second time and corrupt them. Check a few rows with SELECT HEX(col): C3A9 for é means UTF-8 bytes are stored. The usual repair is to change the column to the matching VARBINARY/BLOB type and then to utf8mb4, which relabels the bytes without converting them. Do this on a copy and verify before touching production.

#Convert

Change the schema default first. This only affects tables created later:

ALTER DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Convert one table, including all its character columns:

ALTER TABLE appdb.reviews
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  ROW_FORMAT = DYNAMIC;

Or convert a single column, keeping its definition intact. Repeat NOT NULL, DEFAULT and COMMENT or you will lose them:

ALTER TABLE appdb.reviews
  MODIFY comment TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL;

Generate the statements for every table that still needs it:

SELECT CONCAT('ALTER TABLE `', table_schema, '`.`', table_name,
              '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;') AS ddl
FROM   information_schema.tables
WHERE  table_schema = 'appdb'
  AND  table_type   = 'BASE TABLE'
  AND  table_collation NOT LIKE 'utf8mb4%';

Warning

CONVERT TO CHARACTER SET rebuilds the table with a full copy and blocks writes for the duration. It needs free disk space roughly the size of the table and generates large binary logs and replica lag. For large or busy tables, use gh-ost or pt-online-schema-change, or run it in a maintenance window.

Caution

CONVERT TO also changes TEXT columns: a TEXT column whose data no longer fits after conversion is promoted to MEDIUMTEXT. Review the resulting SHOW CREATE TABLE and take a backup before you run it on production.

#After the conversion

Stored routines, views and triggers keep the character set and collation of the session that created them. Find the old ones:

SELECT routine_schema, routine_name, character_set_client, collation_connection
FROM   information_schema.routines
WHERE  routine_schema = 'appdb' AND character_set_client <> 'utf8mb4';

SELECT trigger_name, character_set_client, collation_connection
FROM   information_schema.triggers
WHERE  trigger_schema = 'appdb' AND character_set_client <> 'utf8mb4';

Re-create them from a utf8mb4 session (SHOW CREATE PROCEDURE, drop, create) so their parameters and literals do not trigger collation-mix errors.

Then test the original failure:

INSERT INTO appdb.reviews (comment) VALUES ('Great service 😀');
SELECT comment, HEX(comment) FROM appdb.reviews ORDER BY id DESC LIMIT 1;

The hex should contain F09F9880.

#Prevent it next time

• Set character_set_server = utf8mb4 and a utf8mb4 collation on every server and parameter group.
• Add a CI or schema-review check that rejects new columns that are not utf8mb4.
• Put the charset in every connection string, not just the server defaults.
• Keep sql_mode strict (STRICT_TRANS_TABLES) so bad data fails loudly instead of becoming ?.
• Run the "not utf8mb4" column query monthly until it returns no rows.