MySQL

MySQL "Data Too Long for Column" Error Fix

The "data too long for column" error means the value you're trying to insert exceeds the maximum length defined for that column — but the actual character count that triggers this can be smaller than expected once character encoding overhead is taken into account.

The Problem

An insert or update fails with:

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

Counting the characters in the value being inserted might even show it's shorter than the column's defined length, which makes the error confusing until encoding is factored in.

Why It Happens

A handful of distinct situations produce this error, and they require different fixes:

  • The value genuinely exceeds the column's defined character limit — straightforward, and the most common cause
  • The column uses utf8mb4 encoding, where certain characters (emoji, some non-Latin scripts) require up to 4 bytes each, but VARCHAR length limits in MySQL are defined in characters, not bytes, so this specific case is less commonly the direct cause of this exact error — though it does affect total row size limits separately
  • Leading or trailing whitespace, or invisible characters, inflating the actual value length beyond what a visual character count suggests
  • Application-level truncation logic that doesn't account for the actual database column limit, allowing a value through that the database itself then rejects

The Fix

First, check the column's actual defined length:

SHOW COLUMNS FROM your_table LIKE 'description';

Compare this against the actual length of the value you're trying to insert:

SELECT LENGTH('your value here'), CHAR_LENGTH('your value here');

LENGTH() returns the byte length, while CHAR_LENGTH() returns the character count — comparing both against the column's character limit clarifies whether encoding overhead is contributing to the mismatch, particularly relevant for text containing non-ASCII characters.

If the value genuinely needs to be longer than the current column allows, and this is a legitimate, expected use case rather than bad input data, increase the column's length:

ALTER TABLE your_table MODIFY description VARCHAR(500);

For content that could genuinely be very long and unpredictable in length (like a blog post body or a large JSON blob), consider switching from VARCHAR to a TEXT type entirely, rather than continuing to guess at an appropriate maximum length:

ALTER TABLE your_table MODIFY description TEXT;

TEXT columns support up to 65,535 bytes by default, with MEDIUMTEXT and LONGTEXT available for even larger content, removing the need to predict an exact maximum length in advance.

If the actual issue is unexpected whitespace or invisible characters inflating the length rather than genuinely long content, trim the value before inserting:

UPDATE your_table SET description = TRIM(description);

Or handle this at the application level before the value ever reaches the database, which is generally the better long-term fix, since it prevents the issue rather than cleaning up after it repeatedly.

If your application has its own length validation, make sure it matches the database column's actual limit exactly, accounting for character vs. byte distinctions if the column uses a multi-byte character set — a mismatch here means the application either rejects valid input unnecessarily, or allows input through that the database then rejects, producing exactly this error downstream instead of a cleaner validation message closer to the user.

Still Not Working?

If you've confirmed the column length is sufficient and the value's character count is genuinely within range, check whether MySQL's strict mode is disabled, since in non-strict mode MySQL sometimes silently truncates data that's too long rather than raising this error at all — meaning if you're seeing the error, strict mode is active and correctly enforcing the limit, and the fix is adjusting the column or the data, not the mode. Conversely, if you were expecting this error but data is instead being silently truncated without any error at all, check whether strict mode needs to be enabled to get consistent, predictable behavior:

SELECT @@sql_mode;

You might also like