Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL rejects an INSERT with Data too long for column

ErrorERROR 1406 (22001): Data too long for column 'description' at row 1

Short answer

In strict mode, MySQL refuses a value longer than the column allows instead of cutting it. Find the column's real limit, then either shorten the data or widen the column. A TEXT column holds 65,535 bytes; MEDIUMTEXT and LONGTEXT hold far more. Limits are in bytes for TEXT, so multibyte text fits less.

  • Applies to MySQL 8.0, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Look up the declared type of the column named in the error, and measure the value you tried to insert.

    SHOW COLUMNS FROM products LIKE 'description';
    SELECT CHAR_LENGTH('your text'), LENGTH('your text');
  2. 2Match what you see, then jump to its fix

    1. 1SHOW COLUMNS shows varchar(255) or similar and the value has more charactersThe VARCHAR or CHAR column is shorter than the dataFix
    2. 2The column is TEXT (or TINYTEXT) and the value is long, or full of multibyte charactersThe text is bigger than a TEXT column's byte limitFix

What does the error look like?

  • ERROR 1406 (22001): Data too long for column 'description' at row 1
  • ER_DATA_TOO_LONG: Data too long for column '%s' at row %ld

Cause 1 of 2

The VARCHAR or CHAR column is shorter than the data

1Check

A VARCHAR(M) holds at most M characters. In strict mode, cutting non-space characters is an error and the row is not inserted. Compare CHAR_LENGTH of the value with M.

SELECT MAX(CHAR_LENGTH(description)) FROM staging_products;

2Fix

Widen the column. MODIFY needs the full definition, because attributes you leave out are not carried forward.

ALTER TABLE products
  MODIFY description VARCHAR(1000) NOT NULL DEFAULT '';

3Verify

Repeat the INSERT that failed.

SHOW COLUMNS FROM products LIKE 'description';

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 2

The text is bigger than a TEXT column's byte limit

1Check

TINYTEXT holds 255 bytes, TEXT 65,535, MEDIUMTEXT 16,777,215 and LONGTEXT 4,294,967,295. These are bytes, so utf8mb4 text with emoji or non-Latin letters fits fewer characters than the number suggests.

SELECT MAX(LENGTH(body)) AS bytes FROM staging_articles;

2Fix

Change the column to a type that holds the largest value you expect.

ALTER TABLE articles
  MODIFY body MEDIUMTEXT NOT NULL;

3Verify

The failed statement now succeeds.

SHOW COLUMNS FROM articles LIKE 'body';

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Validate length in the application before the INSERT and show the user a clear message.
  • Keep strict mode on: with it off, MySQL silently truncates and only raises a warning, so you lose data without noticing.
  • Size columns from real data (MAX of LENGTH) with some headroom, not from a guess.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.