How to fix ERROR 1406: Data too long for column in MySQL

MySQLBEGINNERMEDIUMUpdated Dec 22, 2025· 314 views

Error 1406 occurs when you attempt to insert or update data that exceeds the defined column width. This commonly happens with VARCHAR columns or when string data length mismatches occur due to character set encoding differences.

Quick fix

Fixes ERROR 1406: Data too long for column

-- This would fail on the 'email' column if it's too small
INSERT INTO users (id, name, email)
VALUES (1, 'John Doe', 'very-long-email-address-that-exceeds-column-size@example.com');
  1. Identify which column is causing the error
  2. Check the current column size and data type
  3. Increase the column size (most common fix)
  4. Switch to a larger data type if needed
mysql — error
-- This would fail on the 'email' column if it's too small
INSERT INTO users (id, name, email)
VALUES (1, 'John Doe', 'very-long-email-address-that-exceeds-column-size@example.com');
ERROR 1406: Data too long for column

What this error means

Error 1406 (SQLSTATE 22001) is raised by MySQL when an INSERT or UPDATE statement attempts to write a value that exceeds the maximum size allowed for a column. This is a data integrity safeguard that prevents data truncation and ensures your database remains consistent. The error typically occurs with string columns like VARCHAR, CHAR, TEXT, and BLOB types. Each of these has defined maximum lengths: CHAR can hold up to 255 characters, VARCHAR up to 65,535 bytes per column, and TEXT variants have even larger limits. When strict SQL mode is enabled (STRICT_TRANS_TABLES, which is the default in MySQL 5.7+), MySQL rejects the insert/update entirely rather than silently truncating the data. Character set considerations also play a role. UTF-8 multibyte characters (utf8mb4) consume 4 bytes per character, so a VARCHAR(100) column can only store 25 UTF-8 characters, not 100. If you try to insert 50 characters into that column, it will exceed the byte limit and trigger Error 1406.

Typical symptoms

  • INSERT or UPDATE statement fails with "ERROR 1406: Data too long for column"
  • Application or migration fails when loading data into the database
  • Error message specifies the exact column name causing the issue
  • Data import tools (like MySQL Workbench, phpMyAdmin) fail mid-operation
  • The same data succeeds in non-strict mode but fails in strict mode MySQL instances

Common causes

  1. String data length exceeds the defined VARCHAR or CHAR column width
  2. Character set encoding (UTF-8 multibyte characters) consuming more bytes than expected
  3. STRICT_TRANS_TABLES SQL mode enabled, causing errors instead of silent truncation
  4. Column size too small for actual data requirements
  5. BIT column receiving string data instead of binary literal (e.g., '1' instead of b'1')
  6. Data migration or import from non-strict MySQL to strict-mode MySQL

How to fix "ERROR 1406: Data too long for column"

1Identify which column is causing the error

The error message includes the column name. If it doesn't clearly show which column, check your INSERT/UPDATE statement:

sql
-- This would fail on the 'email' column if it's too small
INSERT INTO users (id, name, email)
VALUES (1, 'John Doe', 'very-long-email-address-that-exceeds-column-size@example.com');

The error output shows: ERROR 1406: Data too long for column 'email' at row 1

Review the column definition to see its current maximum size.

2Check the current column size and data type

Query the table schema to see the current column definition:

sql
DESCRIBE users;
-- or
SHOW COLUMNS FROM users;

Look at the column causing the issue. For example, you might see:

bash
email | varchar(50) | NO | UNI | NULL |

If you're trying to store email addresses, 50 characters is likely too small. Measure your actual data to determine the required size.

3Increase the column size (most common fix)

Use ALTER TABLE to expand the column:

sql
ALTER TABLE users MODIFY COLUMN email varchar(255);

Choosing the right size:
- Email addresses: VARCHAR(255)
- Phone numbers: VARCHAR(20)
- URLs: VARCHAR(2083)
- Short text (names): VARCHAR(100-150)
- Long text (descriptions): TEXT or LONGTEXT

After altering the column, retry your INSERT/UPDATE operation.

4Switch to a larger data type if needed

If the data is genuinely variable in length and could exceed VARCHAR limits, use TEXT types:

sql
-- For large text up to 64KB
ALTER TABLE articles MODIFY COLUMN content MEDIUMTEXT;

-- For very large text up to 4GB
ALTER TABLE logs MODIFY COLUMN message LONGTEXT;

Data type sizes:
- VARCHAR(N): max 65,535 bytes total (all columns combined)
- TEXT: 64 KB
- MEDIUMTEXT: 16 MB
- LONGTEXT: 4 GB

TEXT types are appropriate for blog posts, descriptions, and log entries.

5Validate data before insertion

To prevent this error in the future, add application-level validation:

JavaScript/Node.js:

javascript
const email = userData.email;
if (email.length > 255) {
  throw new Error('Email exceeds maximum length of 255 characters');
}

PHP:

php
$maxLength = 255;
if (strlen($email) > $maxLength) {
  throw new Exception('Email too long');
}

Python:

python
if len(email) > 255:
    raise ValueError('Email exceeds maximum length')

Validating data length in your application prevents bad data from ever reaching the database.

6Fix BIT column issues (if applicable)

If the error occurs with a BIT column, the issue is usually incorrect value format. BIT columns expect binary literals, not quoted strings:

Incorrect (causes Error 1406):

sql
UPDATE users SET is_active = '1' WHERE id = 1;  -- String '1' is 8 bits

Correct:

sql
UPDATE users SET is_active = b'1' WHERE id = 1;  -- Binary literal is 1 bit
-- or
UPDATE users SET is_active = 1 WHERE id = 1;  -- Numeric value

The b prefix tells MySQL to interpret the value as a binary literal.

Advanced notes

Strict Mode Behavior:
Modern MySQL (5.7+) defaults to STRICT_TRANS_TABLES enabled. In strict mode, oversized data causes an error and the entire statement is rejected. In older MySQL or non-strict mode, the data is silently truncated, which can lead to data loss without warning.

Character Set Considerations:
The default charset utf8mb4 uses 4 bytes per character. A VARCHAR(100) with utf8mb4 only holds ~25 characters, not 100. Check your connection charset:

sql
SHOW VARIABLES LIKE 'character_set_client';

Row Size Limits:
All columns in a row share a maximum of 65,535 bytes. A table with multiple VARCHAR columns may hit this limit before individual columns max out. Check total row size:

sql
-- Rough calculation: sum of all column max sizes
SELECT (50 + 255 + 100 + 255) as estimated_row_size;

UTF-8 vs UTF-8MB4:
- utf8 (legacy): 3 bytes per character max
- utf8mb4 (current standard): 4 bytes per character (supports emojis)

If you have emoji data, utf8 will cause truncation. Always use utf8mb4 for new tables.

Decimal Columns:
Decimal columns can also trigger Error 1406 if fractional parts exceed the defined precision:

sql
-- Stores 5 digits total, 2 after decimal
ALTER TABLE prices MODIFY price DECIMAL(5, 2);

-- Inserting 123.456 fails (needs 6 digits: 123.456)
INSERT INTO prices VALUES (123.456);  -- ERROR 1406

-- Fix: use DECIMAL(6, 3) for 6 digits total
ALTER TABLE prices MODIFY price DECIMAL(6, 3);

Data Migration:
When migrating from non-strict MySQL to strict MySQL, old data might fail. Test migration with a sample first:

sql
-- Preview which rows would fail
SELECT * FROM users WHERE LENGTH(email) > 255;

Frequently asked questions

What does "ERROR 1406: Data too long for column" mean?
Error 1406 (SQLSTATE 22001) is raised by MySQL when an INSERT or UPDATE statement attempts to write a value that exceeds the maximum size allowed for a column. This is a data integrity safeguard that prevents data truncation and ensures your database remains consistent. The error typically occurs with string columns like VARCHAR, CHAR, TEXT, and BLOB types.
How do I fix "ERROR 1406: Data too long for column"?
Error 1406 occurs when you attempt to insert or update data that exceeds the defined column width. This commonly happens with VARCHAR columns or when string data length mismatches occur due to character set encoding differences.
What causes "ERROR 1406: Data too long for column"?
String data length exceeds the defined VARCHAR or CHAR column width Character set encoding (UTF-8 multibyte characters) consuming more bytes than expected STRICT_TRANS_TABLES SQL mode enabled, causing errors instead of silent truncation
Is the "ERROR 1406: Data too long for column" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading