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
- "Incorrect string value" is a character set problem. Most of this article is about it.
- "Incorrect integer value" (or decimal, or datetime) is a strict mode problem: the application sent a value that is not valid for the column type. It has its own section near the end.
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.
- Starts with
\xF0(for example\xF0\x9F\x98\x80, which is the 😀 emoji): a four-byte UTF-8 character. The column is almost certainlyutf8mb3(often shown asutf8), which uses at most three bytes per character and supports only the Basic Multilingual Plane, so it cannot store emoji or other supplementary characters at all (utf8mb3 character set). - Two-byte sequences such as
\xC4\x81(the Latvian ā) or Cyrillic text: the column is probablylatin1, which has no such letters. French and German accents often fit inlatin1, which is why these tables survive for years until the first customer from Riga or Warsaw signs up. - Bytes that are not valid UTF-8 at all: in our experience this is binary data (a hash, an encrypted token, a file) being written into a text column, or a client that sends one encoding while declaring
utf8mb4. The fix there is aVARBINARY/BLOBcolumn or a corrected client, not a new character set.
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;
MODIFYper column keeps exactly the type you write. Prefer it on production schemas.CONVERT TO CHARACTER SETconverts every character column, and the manual warns it changes types when needed to keep the same number of characters: alatin1TEXTcolumn becomesMEDIUMTEXT, and a longVARCHARcan also becomeMEDIUMTEXT. ORMs and schema-diff tools tend to notice that later.- Name the collation explicitly. It keeps the source, the replicas and the next migration in agreement.
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.
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.
- The usual culprit is a form field sent as an empty string
''into a numeric column. SendNULLor a real number from the application. - Do not "fix" it by removing strict mode globally. That turns errors back into silent data changes, such as zeros where nothing was entered.
INSERT IGNOREalso downgrades the error: the manual says rows with conversion errors get the closest valid value. That is acceptable for a one-off import you have reviewed, not for application writes.
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:
- Writes are blocked during conversion. The online DDL table lists converting a character set as rebuilding the table and not permitting concurrent DML (online DDL operations). On a 300 GB table that means hours with no writes. Use gh-ost or pt-online-schema-change instead; our guide to ALTER TABLE without downtime covers how they work and fail.
- Disk space. A rebuild or an online tool writes a full new copy of the table plus its indexes before dropping the old one. Check free space per table, not per server, and remember the binary logs that the copy generates.
- Replica lag. A direct
ALTERruns on each replica after it finishes on the source. In our experience the lag is roughly as long as theALTERitself, so a two-hour conversion means replicas two hours behind. Online tools copy in chunks and can throttle on lag. - Row size. The 65,535-byte row limit counts declared bytes (row size limits). A wide table with several long
VARCHARcolumns can fit inutf8mb3and fail with error 1118 inutf8mb4; a singleNOT NULLutf8mb4VARCHARtops out around 16,383 characters. - Collation changes behaviour, not just storage.
utf8mb4_0900_*collations areNO PAD, so'a'and'a 'are different strings, while the olderutf8mb4_general_ciandutf8mb4_unicode_ciarePAD SPACE(Unicode collations). Different collations also treat some accented and special characters differently, so a unique key can reject rows that used to be distinct. Check before you convert:-- Would a unique key collide under the new collation? SELECT CONVERT(email USING utf8mb4) COLLATE utf8mb4_0900_ai_ci AS k, COUNT(*) FROM users GROUP BY k HAVING COUNT(*) > 1; - Mixed charsets during the migration. While some tables are converted and others are not, joins between them compare different character sets. In our experience that can stop the optimizer from using an index on the join column, and some queries fail with error 1267 "Illegal mix of collations". Convert tables that join each other in the same window.
- Replicas first need to agree. The manual warns that different definitions on source and replica lead to unexpected results, and that every replica must understand the new character set. Do not leave replicas on
utf8mb3while the source writes emoji. - Measure on one table first. Convert a medium table, record the GB per hour on your hardware, then plan the large ones from that number.
Key takeaways
- "Incorrect string value" is a character set mismatch; "Incorrect integer value" is strict mode rejecting a bad value.
- Bytes starting with
\xF0mean a four-byte character, usually an emoji, hitting autf8mb3column. - Check the column, then the application's connection; both must be
utf8mb4. - Prefer
MODIFYper column overCONVERT TO, and name the collation. - For double-encoded data, convert through
BLOBinstead ofCONVERT TO. - On big tables, use an online schema change tool and plan disk, replica lag, index length, row size and collation behaviour.