Migrations

MySQL Error 1366 "Incorrect string value": Causes and Fixes

Error 1366 usually means an emoji or a non-Latin letter hit a column that cannot store it, or a client that talks to the server in the wrong character set. Here is how to read the hex in the message, fix the column or the connection, and convert big tables without locking writes for hours.

· 16 min read · AG Data Team

MySQL error 1366, "Incorrect string value", means the server received bytes that cannot be stored in the target column's character set. In practice it is almost always one of three things: an emoji going into a utf8mb3 column, a non-Western letter going into a latin1 column, or a client connection that declares one character set and sends another.

The error is annoying, but it is also the server doing its job. Without it, the same row would be stored with question marks or garbage, and you would find out months later. Below: how to read the message, how to find the real cause in a few queries, how to fix it, and what changes when the table has hundreds of millions of rows.

Two errors behind one number

Error 1366 has the symbol ER_TRUNCATED_WRONG_VALUE_FOR_FIELD, SQLSTATE HY000, and the message template Incorrect %s value: '%s' for column '%s' at row %ld (MySQL server error reference). The %s is the type, so you will see two very different families:

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

ERROR 1366 (HY000): Incorrect integer value: '' for column 'qty' at row 1

Read the hex bytes first

The quoted value in the message is the raw bytes, and they usually tell you the cause before you run any query.

Step 1: check the column

Look at the table with SHOW CREATE TABLE, or list every column that still uses an old character set across the whole server:

SELECT table_schema, table_name, column_name,
       character_set_name, collation_name, column_type
FROM information_schema.columns
WHERE character_set_name IN ('utf8mb3', 'latin1')
  AND table_schema NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema')
ORDER BY table_schema, table_name, ordinal_position;

Since MySQL 8.0 the server defaults are utf8mb4 and utf8mb4_0900_ai_ci, so new databases are fine. The old character sets come from schemas created on 5.x and carried along through dumps and upgrades. MySQL displays utf8 as utf8mb3 in SHOW CREATE TABLE and INFORMATION_SCHEMA, and utf8mb3 is deprecated, with removal expected in a future major release. Converting now is cheaper than converting under pressure later.

Step 2: check the connection

If the column is already utf8mb4 and the error still appears, the problem is the connection. The server interprets incoming statements in character_set_client, and SET NAMES sets character_set_client, character_set_connection and character_set_results in one go (connection character sets):

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

-- Fix for the current session (sets client, connection and results)
SET NAMES utf8mb4;

Do this check from the application's own connection, not from your terminal; the mysql client usually defaults to utf8mb4, so your manual test will pass while the application fails. The durable fix is the driver's character set option (for example charset=utf8mb4 in a PHP PDO DSN) rather than a SET NAMES sprinkled through code. In our experience, a connection set to utf8mb3 rejects emoji even when every column is utf8mb4.

Step 3: convert the column to utf8mb4

There are two ways, and the difference matters (ALTER TABLE):

-- Per column: keeps the data type you chose
ALTER TABLE reviews
  MODIFY comment TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  MODIFY title VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL;

-- Whole table: simpler, but may turn TEXT/VARCHAR into MEDIUMTEXT
ALTER TABLE reviews CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- Default for tables created later (existing tables are not changed)
ALTER DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Before converting, check limits measured in bytes. Index keys are limited to 3072 bytes on DYNAMIC and COMPRESSED tables and 767 bytes on COMPACT and REDUNDANT, which is 768 or 191 utf8mb4 characters (converting to utf8mb4). If an index is too long, the conversion fails with the error described in our guide to MySQL error 1071.

Do not run CONVERT TO on mojibake. If a latin1 column already contains UTF-8 bytes (the app wrote UTF-8 over a latin1 connection for years, and you see é instead of é in other tools), CONVERT TO will convert those bytes again and make the mess permanent. The manual's fix is to go through a binary type, because there is no conversion to or from BLOB:
-- Column is labelled latin1, but the bytes are really UTF-8
ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8mb4;
Test it on a copy and compare a sample of rows before and after.

The "Incorrect integer value" variant

When the message says integer, decimal or datetime, the character set is not involved. In strict mode, an invalid value in an INSERT or UPDATE on a transactional table aborts and rolls back the statement. Without strict mode, MySQL stores an adjusted value and only produces a warning (SQL modes). The default sql_mode in MySQL 8.4 includes STRICT_TRANS_TABLES, which is why these errors often show up right after an upgrade from a server that ran with an empty sql_mode.

On large databases

Converting a few small tables takes seconds. On a schema with terabytes of data, it is a project, and these are the parts that bite:

Key takeaways

MySQL utf8mb4 Character sets SQL mode
Work with us

Planning a utf8mb4 conversion?

We convert character sets on large MySQL tables with online schema change tools, collation checks and a tested rollback plan.

Get in touch More from the blog