🚨 MySQL Error Guide: 'Data too long for column' — Fix ERROR 1406

Overview

ERROR 1406 is raised when an INSERT or UPDATE tries to store a value longer than the target column allows, while strict SQL mode is active:

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

With strict mode on (the default), MySQL rejects the write. Without strict mode it would silently truncate the value and emit only a warning — which is why 1406 often surfaces right after enabling STRICT_TRANS_TABLES or upgrading, exposing data that had been quietly cut off for a long time.

Symptoms

• INSERT/UPDATE fails naming a specific column and row number.
• The value “looks” short enough by character count but still fails — a sign of a multibyte-encoding size mismatch.
• The error appeared only after enabling strict mode or upgrading MySQL.
• Bulk loads (LOAD DATA, batch inserts) fail on one offending row while most succeed.
• An emoji or accented character triggers it where plain ASCII of the same length does not.

Common Root Causes

• Value genuinely longer than the column — a VARCHAR(50) receiving 80 characters.
• Multibyte encoding inflation — under utf8mb4, each character can take up to 4 bytes, so length is measured differently than a naive character count suggests, especially for emoji and CJK text.
• Strict mode now enforcing what used to be silent truncation — the underlying data was always too long; strict mode just stopped hiding it.
• A column too small for real-world input — a schema that assumed names, URLs, or tokens fit in a tighter bound than reality.
• Binary data into a text/VARBINARY column that is shorter than the payload.
• Concatenation or default padding producing a longer string than expected.

Diagnostic Workflow

Confirm the column definition and character set first:

SHOW CREATE TABLE users\G
SELECT column_name, data_type, character_maximum_length,
       character_octet_length, character_set_name
FROM information_schema.columns
WHERE table_name = 'users' AND column_name = 'bio';

character_maximum_length is the character limit; character_octet_length is the byte limit — the gap between them reveals multibyte headroom. Now measure the offending value in both characters and bytes:

SELECT CHAR_LENGTH(@val) AS chars, LENGTH(@val) AS bytes;
-- chars = characters; bytes = storage bytes. If bytes >> chars, multibyte inflation.

Confirm strict mode, which controls reject-vs-truncate:

SELECT @@SESSION.sql_mode;   -- look for STRICT_TRANS_TABLES / STRICT_ALL_TABLES

To find existing rows already at the edge (candidates that were previously truncated):

SELECT id, CHAR_LENGTH(bio) AS chars, LENGTH(bio) AS bytes
FROM users
ORDER BY LENGTH(bio) DESC
LIMIT 10;

Example Root Cause Analysis

A user-profile save started failing:

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

The display_name was VARCHAR(20) and the input was “José 🎉 Developer” — only 17 characters, so the developer was puzzled. Running SELECT CHAR_LENGTH(@val), LENGTH(@val) returned 17 characters but 24 bytes, because the emoji took 4 bytes and the accented é took 2. SHOW CREATE TABLE confirmed the column was utf8mb4. The column limit is by characters (20), so 17 characters should have fit — the real issue was a second, older column downstream defined as VARCHAR(20) under a different, and the app also wrote to a CHAR(20) audit field. Widening the audit column to VARCHAR(64) and validating input length at the application boundary resolved it. The lesson: always measure both CHAR_LENGTH and LENGTH, and check every column the value flows into.

Prevention Best Practices

• Size text columns for real-world input plus headroom; names, URLs, and tokens are routinely longer than first assumed.
• Validate input length at the application boundary and return a clear user-facing error instead of a raw 1406.
• Keep strict mode enabled so truncation surfaces as an error rather than silent data loss.
• Standardize on utf8mb4 and remember length limits are in characters while row-size and index limits are in bytes.
• Audit existing columns with LENGTH() after enabling strict mode to find values that were previously truncated.
• In bulk loads, pre-validate row widths so one long value does not abort the batch.

Quick Command Reference

SHOW CREATE TABLE users\G                            -- column definition + charset
SELECT CHAR_LENGTH(@v) AS chars, LENGTH(@v) AS bytes;-- characters vs storage bytes
SELECT @@SESSION.sql_mode;                           -- strict mode reject vs truncate
ALTER TABLE users MODIFY bio VARCHAR(500);           -- widen the column

Conclusion

ERROR 1406 means a value did not fit, and the fix is either to widen the column or to validate and shorten the input — never to disable strict mode, which only restores silent truncation and data loss. When a “short” value still fails, measure it with both CHAR_LENGTH and LENGTH: multibyte utf8mb4 characters consume more bytes than their character count suggests. Fix the schema or the input at the boundary, and keep strict mode on so problems stay visible.