<p>MySQL Error 1406 appears when an INSERT or UPDATE value exceeds the defined length of its target column, violating the column size limit.</p>
<p>MySQL Error 1406: ER_DATA_TOO_LONG occurs when a value you try to store exceeds the column’s length definition. Trim or truncate the data, enlarge the column with ALTER TABLE, or switch to an appropriate data type to resolve the issue.</p>
Data too long for column '%s' at row %ld
Error 1406 - ER_DATA_TOO_LONG is raised when MySQL receives a value longer than the target column’s declared length or size during INSERT or UPDATE.
The exact message is: Data too long for column '%s' at row %ld. MySQL stops the statement, rolls back the row, and returns SQLSTATE 22001 - string data right truncation.
The error fires when string, binary, or numeric data exceeds column length, precision, or scale. It also occurs with improper character set conversions that expand byte length.
MySQL additionally flags the error when strict SQL mode is active; in non-strict mode, excess bytes may be silently truncated.
First confirm the column definition with SHOW CREATE TABLE. Compare defined length to incoming data length. Shorten the value, cast, or pad only within limits.
Alternatively, expand the column using ALTER TABLE ... MODIFY ... or switch to a type with a larger capacity such as TEXT or VARCHAR with a higher length.
Bulk CSV loads often contain unexpected long strings. Pre-clean the file or load into a staging table with wider columns, then migrate cleaned data.
JSON payloads stored in VARCHAR columns may overflow. Replace the column with JSON or LONGTEXT for safe storage.
Validate input lengths at the application layer before executing SQL. Enforce client-side constraints or use parameterized queries that trim values.
Maintain explicit column sizing documentation and automate schema checks in CI pipelines. Monitor application logs for repeated 1406 occurrences to react proactively.
Error 1264: Out of range value - raised for numeric overflow. Widen the numeric column or constrain the value.
Error 1366: Incorrect string value - occurs with invalid character set data. Ensure proper encoding and column collation.
User-supplied strings, file imports, or concatenations exceed VARCHAR or CHAR limits.
Values with more digits than DECIMAL or integer types allow trigger the error in strict mode.
UTF-8 characters can occupy up to 4 bytes, pushing total length beyond defined byte size.
Storing large blobs or JSON in small VARCHAR columns eventually exceeds capacity.
Thrown when numeric data exceeds column range. Fix by widening numeric types.
Raised for invalid encoding in character columns. Ensure proper charset conversion.
Occurs when inserting NULL into a NOT NULL column. Provide a value or adjust schema.
Only when strict SQL mode is disabled. In strict mode, MySQL aborts the statement with Error 1406.
INSERT IGNORE converts the error into a warning, truncates the value, and inserts the row. Data may be lost.
Yes. TEXT columns cannot be fully indexed without length limits; you may need prefix indexes.
Galaxy’s SQL editor highlights column lengths during code completion and warns when literals exceed size constraints, reducing 1406 incidents.
.avif)
.avif){kind=logo}
