Fix MySQL ERROR 1406: Data Too Long for Column on INSERT or UPDATE

The Error

You're running an INSERT or UPDATE and MySQL throws:

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

This means the value you're trying to store exceeds what the column's data type allows. A VARCHAR(50) column will reject anything over 50 characters — no exceptions. If this is hitting you at 2 AM from a production insert, here's how to get past it fast.

Why This Happens

Usually one of three things:

• The column was defined too narrow for real-world data (e.g. VARCHAR(50) but users paste 200-char strings)
• Something upstream changed — a form field limit was removed, or an API started returning longer strings
• STRICT_TRANS_TABLES mode is on (default since MySQL 5.7.5), which rejects oversized values instead of silently truncating them

Step 1: Find Out the Column's Current Length

Don't guess — check what the column is actually defined as:

DESCRIBE users;
-- or
SHOW COLUMNS FROM users LIKE 'username';

For full detail including charset:

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_NAME = 'users'
  AND COLUMN_NAME = 'username';

Then check exactly how long the offending value is:

SELECT LENGTH('the_value_that_failed');

Knowing both numbers tells you exactly how much headroom you need.

Step 2: Increase the Column Length (Recommended Fix)

The cleanest solution: widen the column to fit the real data.

ALTER TABLE users MODIFY COLUMN username VARCHAR(255) NOT NULL;

VARCHAR(255) covers most username and name fields comfortably. For bios, descriptions, or anything open-ended, skip VARCHAR entirely and use TEXT:

ALTER TABLE users MODIFY COLUMN bio TEXT;

On large tables, ALTER TABLE can lock the table for the duration. On a table with 10M+ rows, that's minutes of downtime. Use pt-online-schema-change or MySQL 8.0's instant DDL instead:

-- MySQL 8.0: instant column modification (no table rebuild needed)
ALTER TABLE users
  MODIFY COLUMN username VARCHAR(255) NOT NULL,
  ALGORITHM=INSTANT;

INSTANT works for VARCHAR increases in most cases. It won't help for type changes or charset conversions.

Step 3: Truncate the Data (If You Can Accept Data Loss)

Can't touch the schema right now? You can truncate the value at insert time:

INSERT INTO users (username) VALUES (LEFT('some_very_long_username_here', 50));

Or clip it in application code before it ever reaches the database. Either way, treat this as a stopgap — fix the schema when the dust settles.

Step 4: Disable Strict Mode (Temporary Escape Hatch)

Batch import failing and silent truncation is acceptable for now? You can turn off strict mode for just your session:

SET SESSION sql_mode = REPLACE(@@SESSION.sql_mode, 'STRICT_TRANS_TABLES', '');

-- Run your inserts here

-- Re-enable when done
SET SESSION sql_mode = @@GLOBAL.sql_mode;

Never disable this globally in production. Strict mode is what catches bad data before it corrupts your tables. Use this only as a one-off during a controlled migration or data import.

Verify the Fix

After widening the column, double-check the new definition:

SHOW COLUMNS FROM users LIKE 'username';
-- Should show VARCHAR(255) or whatever you set

Re-run the query that was failing:

INSERT INTO users (username) VALUES ('the_value_that_was_failing');
-- Query OK, 1 row affected

Confirm the data landed correctly:

SELECT username, LENGTH(username) FROM users ORDER BY id DESC LIMIT 1;

Handling This in Application Code

A database error is a rough way to discover your input isn't validated. Add a length check before the DB call:

# Python example — validate before inserting
if len(username) > 255:
    raise ValueError(f"Username too long: {len(username)} chars")

Prefer enforcing the rule at the DB level too? MySQL 8.0.16+ supports CHECK constraints:

ALTER TABLE users
  ADD CONSTRAINT chk_username_length CHECK (CHAR_LENGTH(username) <= 255);

This gives a clear error regardless of how data enters the database — ORM, raw query, or direct psql connection.

Watch Out for Charset Issues

Storing emoji or CJK characters? Charset affects how much space each character actually uses. Under utf8mb4, a single emoji can take 4 bytes. A VARCHAR(255) column stores 255 characters but may consume up to 1020 bytes — and that matters for index key limits.

Check your column's charset:

SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_NAME = 'users'
  AND COLUMN_NAME = 'username';

If you're on utf8mb4 and hitting index prefix limits (the default InnoDB key limit is 767 bytes in MySQL 5.7, 3072 bytes in 8.0 with innodb_large_prefix), use a prefix index or shorten the column definition.

Quick Reference

• Fast fix (schema change): ALTER TABLE t MODIFY COLUMN col VARCHAR(255);
• Check current size: SHOW COLUMNS FROM table LIKE 'column';
• Temporary workaround: SET SESSION sql_mode = REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', '');
• For very long text: switch from VARCHAR to TEXT
• Large table in production: use ALGORITHM=INSTANT or pt-online-schema-change