ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F…' for column

What it means

Every text column has a character set, which decides which characters it can hold. Error 1366 means the bytes in the message (\xF0\x9F\x98\x80 is 😀 in UTF-8) form a character that the column, or the connection on the way in, can’t represent. In strict mode (the default) the server refuses the row.

The usual culprit is utf8. In MySQL and MariaDB, utf8 has long meant utf8mb3: UTF-8 limited to three bytes per character. That covers most of the world’s scripts but not emoji or other characters outside the Basic Multilingual Plane, which take four bytes. The full UTF-8 is called utf8mb4.

A value starting \xF0 is a four-byte character, usually an emoji. Other bytes in a latin1 column mean characters latin1 doesn’t have, such as Chinese or Cyrillic.

Error 1366 also covers numbers: Incorrect integer value: '' for column 'qty' is the same error for an empty or non-numeric string sent to a number column.

Common causes

1. The column is utf8/utf8mb3 and the text has an emoji.
2. The column is latin1, common in tables created on older servers or by MariaDB with its built-in defaults, and the text has characters outside Western European languages.
3. The connection isn’t utf8mb4. Even with a utf8mb4 column, a client that connects as utf8mb3 can’t send a four-byte character.
4. A table copied from an older schema keeps its old character set, even on a server whose default is utf8mb4.

How to fix it

Find the columns that aren’t utf8mb4

SELECT table_name, column_name, character_set_name
FROM information_schema.columns
WHERE table_schema = DATABASE()
  AND character_set_name IS NOT NULL AND character_set_name <> 'utf8mb4';

TABLE_NAME	COLUMN_NAME	CHARACTER_SET_NAME
legacy	body	latin1
u8	body	utf8mb3

Convert the table to utf8mb4

ALTER TABLE notes CONVERT TO CHARACTER SET utf8mb4;

This converts every text column in the table and sets its default. Existing text is converted, not lost. It always rebuilds the table, and how much it blocks depends on what you convert. In our tests, MySQL 8.4 converted utf8mb3 columns in place (ALGORITHM=INPLACE, LOCK=NONE, so writes carry on) unless a converted column was indexed; with an index, or from latin1, it needed ALGORITHM=COPY, which blocks writes for the duration. MariaDB 11.4 converted utf8mb3 in place even with an index, but also needed COPY from latin1. Ask for ALGORITHM=INPLACE, LOCK=NONE explicitly: if the server can’t, it says so instead of locking the table. See changing a column type.

To make new tables utf8mb4 too, change the database’s default (existing tables keep theirs):

ALTER DATABASE shop CHARACTER SET utf8mb4;

Write utf8mb4 everywhere, never utf8: MySQL 8.4 warns that utf8 will one day mean utf8mb4, but today it still creates utf8mb3 on both servers.

Connect with utf8mb4

The connection’s character set must be utf8mb4 too. In SQL:

SET NAMES utf8mb4;

In a driver, set the charset option (for example charset=utf8mb4 in a DSN or URL) rather than running SET NAMES by hand, so the driver knows how to encode and decode. With the mysql client, --default-character-set=utf8mb4.

A mismatched connection doesn’t always fail loudly. In our test, a client connected as latin1 sent José as UTF-8 bytes, and the server stored them as José, without any error.

Reproduce it

On MySQL 8.4.11:

CREATE TABLE notes (id int PRIMARY KEY, body varchar(255)) CHARACTER SET utf8mb3;
INSERT INTO notes VALUES (1, 'Thanks 😀');

CREATE TABLE legacy (id int PRIMARY KEY, body varchar(255)) CHARACTER SET latin1;
INSERT INTO legacy VALUES (1, '東京');

ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x80' for column 'body' at row 1
ERROR 1366 (HY000): Incorrect string value: '\xE6\x9D\xB1\xE4\xBA\xAC' for column 'body' at row 1

Café went into both tables without trouble. Into a utf8mb4 column, with the client connected as utf8mb3 (--default-character-set=utf8mb3):

ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x8E\x89' for column 'body' at row 1

After SET NAMES utf8mb4 on the same connection, the insert worked. With sql_mode emptied for the session (not strict), the emoji insert into notes succeeded with a warning and stored Thanks ?. After ALTER TABLE notes CONVERT TO CHARACTER SET utf8mb4 the original insert worked. With a unique index on body, asking for ALGORITHM=INPLACE first was refused:

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

MariaDB 11.4.13 gave the same errors with a different SQLSTATE, 22007, and a fully qualified column name:

ERROR 1366 (22007): Incorrect string value: '\xF0\x9F\x98\x80' for column `seo_mysql`.`notes`.`body` at row 1

CREATE TABLE … CHARACTER SET utf8 made a utf8mb3 column on both servers; MySQL added warning 3719, 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. MariaDB 11.4’s built-in default server character set is still latin1; its Docker image sets utf8mb4 in its config file. MySQL 8.4’s default is utf8mb4.

In Inlet

Inlet’s structure editor shows the DDL for a change before it runs and warns when a change rewrites or scans the table. When an insert fails, the error is shown with a hint.