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

First, inspect the table structure to see the current column definition:

SHOW CREATE TABLE your_table_name;

or

DESCRIBE your_table_name;

Identify the column that triggered error 1406 and note its current data type and length (e.g., VARCHAR(100)).

Check how long the data actually is. In most applications, this means inspecting the raw input or logging the exact value that failed:

SELECT CHARACTER_LENGTH(your_column) FROM your_table_name WHERE your_column IS NOT NULL ORDER BY CHARACTER_LENGTH(your_column) DESC LIMIT 5;

This query shows the longest 5 values in the column, helping you determine the required minimum size.

Increase the column size to accommodate your data. Calculate the required length based on your longest values plus a safety margin:

ALTER TABLE your_table_name MODIFY COLUMN your_column VARCHAR(500);

For very large text, use TEXT, MEDIUMTEXT, or LONGTEXT:

ALTER TABLE your_table_name MODIFY COLUMN your_column TEXT;

This is the most common and recommended fix. It preserves all data and maintains data integrity.

If the error involves a BIT column, ensure values are prefixed with b instead of quoted strings:

-- Wrong (causes ERROR 1406)
UPDATE your_table SET is_active = '1' WHERE id = 9;

-- Correct
UPDATE your_table SET is_active = b'1' WHERE id = 9;

BIT(1) accepts only binary values (b'0' or b'1'), not string representations.

Implement client-side or server-side validation to reject oversized input before it reaches the database. For example, in a web form:

// JavaScript validation
const maxLength = 100;
if (userInput.length > maxLength) {
  alert(`Input must be ${maxLength} characters or less`);
  return;
}

This prevents the error from occurring in the first place and provides immediate user feedback.