1406ERRORTier 1 — Safe✅ HIGH confidence

Data too long for column

Category: Data TruncationVersions: All MariaDB / MySQL versions

What this means

Error 1406 (SQLSTATE 22001) is raised when a string value being inserted or updated is longer than the declared maximum length of the column (e.g., VARCHAR(50) receiving a 100-character string). In strict mode this is an error; in non-strict mode the value is silently truncated to the column's maximum length.

Why it happens

1Inserting a string longer than the VARCHAR or CHAR column definition allows
2Inserting a binary value larger than the VARBINARY or BLOB column definition allows
3A multi-byte character set (utf8mb4) where a 10-character string may consume up to 40 bytes, exceeding a byte-limited column definition

How to reproduce

A 100-character string is inserted into a VARCHAR(50) column.

trigger — this will ERROR

SET sql_mode = 'STRICT_TRANS_TABLES';
CREATE TABLE profiles (id INT PRIMARY KEY, username VARCHAR(20) NOT NULL);
INSERT INTO profiles VALUES (1, 'this_username_is_way_too_long_for_twenty_chars');

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

Fix 1: Increase the column length

When the column definition is too restrictive for the actual data.

fix

ALTER TABLE profiles MODIFY username VARCHAR(100) NOT NULL;

Why this works

ALTER TABLE MODIFY changes the column's declared maximum length. Existing data is not affected. The table may need to be rebuilt for InnoDB, which acquires a metadata lock.

Fix 2: Truncate the input value in the application

When the column length is correct and the input should be capped.

fix

-- In the application:
username = username[:20]  -- Python: trim to 20 characters

-- Or in SQL:
INSERT INTO profiles VALUES (1, LEFT('this_username_is_way_too_long_for_twenty_chars', 20));

Why this works

LEFT(str, n) returns the first n characters of the string. This enforces the maximum length before the INSERT, preventing the truncation error while preserving as much of the string as possible.

What not to do

✗

Disable strict mode to allow silent truncation

Why it's wrong: Silent truncation destroys data without any indication — stored values may differ from what users submitted, causing confusion and potential data integrity issues.

Version notes

MariaDB 10.2+Strict mode is the default. VARCHAR length is in characters, not bytes, for character set columns. A VARCHAR(50) utf8mb4 column can store 50 characters but up to 200 bytes.

Sources

📚 Official docs: https://mariadb.com/kb/en/varchar/

🔧 Source ref: MariaDB Server error code 1406 / ER_DATA_TOO_LONG

📖 Further reading: MariaDB VARCHAR type

📖 Further reading: MariaDB LEFT function

Confidence assessment

✅ HIGH confidence

Stable. VARCHAR length semantics are well-documented for all character sets.