MySQL — error 1366 Incorrect string value for a column

Fix MySQL 1366 by aligning connection, column and table character sets with the data being stored.

intermediatedebuggingmysqlcharset

Symptoms

Incorrect string value: '\xF0\x9F...' for column at row 1
The error occurs when storing emoji or non-Latin text
It works in one environment but not another
utf8mb3 columns reject four-byte characters

Possible causes

The column character set is utf8mb3 which cannot store four-byte characters
The connection character set differs from the column
Emoji or supplementary characters require utf8mb4
The client sends bytes the server cannot decode as the declared charset

Diagnosis steps

Check connection and column charsets

mysql -e "show variables like 'character_set%'; show create table t\G"

Interpretation

The output reveals utf8mb3 columns and connection charset, explaining which layer rejects the value.

Convert the column to utf8mb4

ALTER TABLE t MODIFY col TEXT CHARACTER SET utf8mb4

Interpretation

utf8mb4 supports four-byte characters such as emoji that utf8mb3 rejects.

Set the connection charset

mysql -e "SET NAMES utf8mb4"

Interpretation

Aligning the connection charset prevents misinterpretation during transfer.

Root cause

The value contained characters the column character set cannot represent, most often four-byte characters in a utf8mb3 column, or the connection charset disagreed with the column.

Fix

Convert tables and columns to utf8mb4, set the connection character set to utf8mb4, and ensure the client library and connection string request the same charset.

Verify the fix

Insert the previously failing value and confirm it stores correctly, and read it back to confirm no corruption occurred.

Symptom walkthrough

An insert fails with Incorrect string value, showing a byte sequence such as a four-byte emoji, and the message names the column. MySQL utf8mb3 cannot store characters that need four bytes, which is why emoji and some CJK extension characters fail. A connection charset that differs from the column also causes rejection even when the column supports the character.

Fix in practice

Convert affected tables and columns to utf8mb4, set the connection and client charset to match, and use a connection string that requests utf8mb4. Aligning all three layers is necessary, since fixing only the column leaves the connection layer able to corrupt or reject the data.

Verification

Insert the value that previously failed and confirm it stores, then read it back and compare to confirm no mangling. Round-tripping the exact characters proves the charset is correct end to end.

Diagnostic commands

Check the behavior with commands like these:

mysql -e "show variables like 'character_set%'; show create table t\G"
ALTER TABLE t MODIFY col TEXT CHARACTER SET utf8mb4
mysql -e "SET NAMES utf8mb4"