MySQL Error 1406

Error number

1406

SQLSTATE

22001

Symbol

ER_DATA_TOO_LONG

Product

MySQL Server

Versions

MySQL 5.7, 8.0 and 8.4 (an error under strict sql_mode, the default since 5.7; a truncation warning without it)

Category

Data Values & Strict Mode

Last reviewed

24 October 2026

What it means

Error 1406 means a string or binary value is longer than the column allows, for example 15 characters into VARCHAR(10), so strict mode rejected the statement instead of cutting the value.

Every string and binary column has a maximum length: VARCHAR(n) and CHAR(n) hold n characters, VARBINARY(n) and BINARY(n) hold n bytes, and the TEXT and BLOB types have byte limits (TINYTEXT 255 bytes, TEXT 65,535 bytes). When an INSERT, UPDATE or LOAD DATA would store a longer value, MySQL in strict sql_mode (the 8.0 default) stops the statement with 1406. The message names the column and the row within the statement, so at row 2 points to the second row of a multi-row INSERT.

VARCHAR and CHAR lengths count characters, not bytes, so in a utf8mb4 column VARCHAR(5) accepts five accented characters. SQLSTATE 22001 is the standard code for "string data, right truncated". Without strict mode the value is silently cut to fit and only warning 1265 is raised. The SQL Server equivalent is error 8152; MySQL's neighbouring errors are 1366 for values of the wrong type and 1264 for numbers out of range.

Common causes

• User input or an API field is longer than the column: a full name, address, URL, user agent string or product title.
• An import or INSERT ... SELECT copies from a wider source column, or from a source that has extra spaces or padding.
• An UPDATE builds a longer value, for example CONCAT(sku, '-RETIRED') or appending to a notes column.
• A code column has the wrong size: a three-letter code such as 'GBR' written to CHAR(2).
• A TINYTEXT or TEXT column reaches its byte limit, or a BIT(1) column is given a value that needs more than one bit.

Diagnose it

Read-only These statements only read metadata (information_schema, performance_schema and SHOW statements) or your own data. They do not change data, privileges or configuration, so they are safe to run first.

SELECT column_name, column_type, character_maximum_length,
       character_octet_length, character_set_name
FROM information_schema.columns
WHERE table_schema = DATABASE()
  AND table_name = 'customers'
ORDER BY ordinal_position;

SELECT id, name, CHAR_LENGTH(name) AS chars, LENGTH(name) AS bytes
FROM staging_customers
WHERE CHAR_LENGTH(name) > 10   -- the target column is VARCHAR(10)
ORDER BY chars DESC;

SELECT MAX(CHAR_LENGTH(name)) AS longest_chars,
       MAX(LENGTH(name))      AS longest_bytes
FROM customers;

SELECT @@SESSION.sql_mode, @@GLOBAL.sql_mode;

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

ALTER TABLE customers MODIFY name VARCHAR(100);

INSERT INTO customers (id, name)
SELECT id, LEFT(TRIM(name), 10)
FROM staging_customers;

ALTER TABLE products MODIFY notes TEXT;

Examples

A name longer than VARCHAR(10)

CREATE TABLE customers (
    id   INT PRIMARY KEY,
    name VARCHAR(10)
);

INSERT INTO customers (id, name) VALUES (1, 'Alexandra Smith');  -- 15 characters

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

The second row of a multi-row INSERT is too long

The whole statement fails: row 1 is not inserted either.

CREATE TABLE customers (
    id   INT PRIMARY KEY,
    name VARCHAR(10)
);

INSERT INTO customers (id, name) VALUES
    (1, 'Ali'),
    (2, 'Bartholomew Jones');

ERROR 1406 (22001): Data too long for column 'name' at row 2

An UPDATE that makes a value longer

CREATE TABLE products (
    id  INT PRIMARY KEY,
    sku VARCHAR(12)
);
INSERT INTO products VALUES (2, 'MUG-002');

UPDATE products SET sku = CONCAT(sku, '-RETIRED') WHERE id = 2;  -- 15 characters

ERROR 1406 (22001): Data too long for column 'sku' at row 1

More than 255 bytes in a TINYTEXT column

CREATE TABLE products (
    id    INT PRIMARY KEY,
    notes TINYTEXT
);

INSERT INTO products (id, notes) VALUES (1, REPEAT('x', 300));

ERROR 1406 (22001): Data too long for column 'notes' at row 1

How to prevent it

Size columns from the real data and the business rule, not from a guess: check the longest values in source systems, leave headroom, and use TEXT for open-ended text. Enforce the same limits in forms and APIs so users get a clear message before the database is reached, and count characters rather than bytes.

Keep strict mode on in every environment. With sql_mode empty (or with INSERT IGNORE), the same INSERT succeeds with warning 1265 and stores the value cut to fit: in our test 'Alexandra Smith' became 'Alexandra ', which nobody notices until the data is wrong.

Common questions

Learn more on CodeWithSQL

• SQL data types explained
• SQL constraints explained

Official documentation

• MySQL 8.0 server error message reference
• Server SQL modes (strict mode)
• The CHAR and VARCHAR types
• Data type storage requirements

Last technically reviewed 24 October 2026; examples and diagnostics run on MySQL 8.0.46. Message templates checked against the server's error catalogue (perror) for that version.