ERROR 1406 (22001): Data too long for column

What it means

The column name has a maximum size, say varchar(10), and the value you sent is longer. In strict mode (STRICT_TRANS_TABLES, on by default in MySQL 8.4 and MariaDB 11.4) the server refuses the whole statement. Without strict mode it would store the first part and only warn, which silently loses data.

at row N counts rows within the statement, so in a multi-row INSERT it tells you which row was too long.

Sizes are counted differently by type:

• char(N) and varchar(N) count characters: varchar(10) holds Zoë Ångstr (10 characters, 12 bytes) but not Zoë Ångström (12 characters).
• TEXT counts bytes: at most 65,535. MEDIUMTEXT holds about 16 MB, LONGTEXT about 4 GB.
• binary, varbinary and BLOB count bytes too.

Common causes

1. The column is smaller than real data: names, titles, URLs, user agents and email addresses are often longer than the size someone guessed.
2. A TEXT column for long content. 64 KB is less than it sounds for HTML, JSON or logs, especially with multi-byte characters.
3. A code field (char(2) for a country) receiving a different format (GBR instead of GB).
4. An UPDATE that appends (CONCAT(name, …)) pushes an existing value over the limit.
5. Data from a source with different limits: an import, another database, a form without a maxlength.

How to fix it

Check the column’s size and your longest value

SELECT column_name, column_type, character_maximum_length
FROM information_schema.columns
WHERE table_schema = DATABASE() AND table_name = 'people';

SELECT MAX(CHAR_LENGTH(name)) FROM people;

For incoming data, find the rows that won’t fit before you load them: WHERE CHAR_LENGTH(name) > 10.

Widen the column

ALTER TABLE people MODIFY name varchar(100);

MODIFY restates the whole column, so repeat NOT NULL, DEFAULT and any comment, or they’re lost. Widening a varchar can run in place, without blocking writes, as long as the column stays on the same side of 255 bytes (the size of its length prefix changes there). With utf8mb4, four bytes per character, that’s varchar(63): widening to varchar(64) or more needs a full table copy that blocks writes. Ask for it explicitly and the server tells you if it can’t:

ALTER TABLE people MODIFY name varchar(63), ALGORITHM=INPLACE, LOCK=NONE;

See changing a column type for running this on a large table. For TEXT, change to MEDIUMTEXT.

Or shorten the value

If the limit is right, validate input to the same size in your app, or trim on purpose:

INSERT INTO people (id, name) VALUES (1, LEFT('Christopher', 10));

Don’t switch off strict mode to make it go away

Removing STRICT_TRANS_TABLES from sql_mode, or using INSERT IGNORE, makes the error go away by cutting your data short with only a warning (1265 Data truncated for column).

Reproduce it

On MySQL 8.4.11:

CREATE TABLE people (id int PRIMARY KEY, code char(2), name varchar(10), bio text,
                     status enum('active','closed'));
INSERT INTO people (id, name) VALUES (1, 'Christopher');
INSERT INTO people (id, name) VALUES (2, 'Zoë Ångström');
INSERT INTO people (id, name) VALUES (4, 'ok'), (5, 'also fine'), (6, 'far too long here');
INSERT INTO people (id, code) VALUES (7, 'GBR');
INSERT INTO people (id, bio) VALUES (8, REPEAT('x', 70000));

ERROR 1406 (22001): Data too long for column 'name' at row 1
ERROR 1406 (22001): Data too long for column 'name' at row 1
ERROR 1406 (22001): Data too long for column 'name' at row 3
ERROR 1406 (22001): Data too long for column 'code' at row 1
ERROR 1406 (22001): Data too long for column 'bio' at row 1

Zoë Ångstr (10 characters, 12 bytes) went in. A value not in an ENUM gives a different error:

ERROR 1265 (01000): Data truncated for column 'status' at row 1

With sql_mode emptied for the session, Christopher was stored as Christophe with Warning 1265 Data truncated for column 'name' at row 1; INSERT IGNORE did the same to GBR, storing GB.

Widening name in place to varchar(63) worked; to varchar(64):

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

MariaDB 11.4.13 gave exactly the same messages, numbers and SQLSTATEs, and the same in-place limit (its refusal ends Reason: Cannot change column type. Try ALGORITHM=COPY).

In Inlet

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