EC
1406
MySQLERRORCommonData TruncationHIGH confidence

Data too long for column

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
  1. 1Inserting a string longer than the VARCHAR or CHAR column definition allows
  2. 2Inserting a binary value larger than the VARBINARY or BLOB column definition allows
  3. 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
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');

expected output

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

Fix 1

Increase the column length

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

Increase the column length
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 When the column length is correct and the input should be capped.

Truncate the input value in the application
-- 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

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

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MySQL errors