Source data contains values longer than the column's VARCHAR/CHAR length. Column size was not updated when source system expanded a field length. ADF copy activity does not truncate or validate string length before insert. Encoding issue — multi-byte UTF-8 characters take more bytes than single-byte characters

Step 1: Identify the maximum value length in the source: `SELECT MAX(CHAR_LENGTH(col)) FROM source_table;`. Step 2: Expand the column: `ALTER TABLE your_table MODIFY COLUMN col VARCHAR(500);`. Step 3: Truncate values in the pipeline if expansion is not possible: use SUBSTRING in ADF derived column or dbt model.. Step 4: In MySQL strict mode, switch to TEXT type for unbounded strings: `ALTER TABLE your_table MODIFY COLUMN col TEXT;`

Medium severitydata integrity

Power BI Refresh Error:
1406

What does this error mean?

A value being inserted or updated exceeds the maximum length defined for the column.

Common causes

1Source data contains values longer than the column's VARCHAR/CHAR length
2Column size was not updated when source system expanded a field length
3ADF copy activity does not truncate or validate string length before insert
4Encoding issue — multi-byte UTF-8 characters take more bytes than single-byte characters

How to fix it

1Step 1: Identify the maximum value length in the source: `SELECT MAX(CHAR_LENGTH(col)) FROM source_table;`
2Step 2: Expand the column: `ALTER TABLE your_table MODIFY COLUMN col VARCHAR(500);`
3Step 3: Truncate values in the pipeline if expansion is not possible: use SUBSTRING in ADF derived column or dbt model.
4Step 4: In MySQL strict mode, switch to TEXT type for unbounded strings: `ALTER TABLE your_table MODIFY COLUMN col TEXT;`

Frequently asked questions

Why does MySQL error 1406 appear now when the pipeline worked before?

Source data changed — a new value is longer than the column definition. Check when the column was last reviewed and run `SELECT MAX(CHAR_LENGTH(col)) FROM source;` to find the new maximum.

What is the difference between VARCHAR and TEXT in MySQL for this error?

VARCHAR has a defined maximum length and triggers error 1406 on overflow. TEXT has no explicit length limit (up to 65535 bytes) and avoids this error, but does not allow indexing without a prefix length.

Can MetricSign alert me when data length changes cause pipeline failures?

Yes — MetricSign captures ADF pipeline failures with the specific MySQL error code, giving you immediate notification when data size issues break a load.

Official documentation: https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html