Fixing Data Truncated Warnings in a MySQL CSV Load

A LOAD DATA LOCAL that ends with Warnings: 6 is a load that altered six values, and the number is the only thing on the screen. The message text is gentle: data truncated, out of range, incorrect integer value. The effect is not gentle, because the table now holds names cut to five letters, quantities clamped to 32767 and strings that were not numbers stored as zero. This article reproduces each warning on a six row file, shows why the same file fails on a server side load and on an INSERT, and gives the checks that would have found each problem before the load. The overall path is in the MySQL import guide. The statement's clauses are in using LOAD DATA INFILE, and the same problems with dates are in fixing incorrect date value errors. The data type limits quoted below come from the MySQL numeric type reference.

The test file and the table

The warning and error codes below are the ones listed in the MySQL error reference, and the behavior of strict mode is described in the SQL modes page.

Six rows, each with one problem for the columns below:

id,name,qty,price,flag
1,Ana,5,12.50,1
2,Bartholomew,7,3.00,0
3,Cy,40000,1.25,1
4,Dee,2,12345.67,1
5,Eli,x,2.00,yes
6,Fay,1,1.234,1

The table is items (id INT, name VARCHAR(5), qty SMALLINT, price DECIMAL(5,2), flag TINYINT(1)). Row 2 has a name of 11 characters in a column of 5, row 3 a quantity of 40000 in a column that holds up to 32767, row 4 a price of 12345.67 in a column that holds up to 999.99, row 5 the text x for a quantity and yes for a flag, and row 6 a price with three decimals.

What LOAD DATA LOCAL does

I loaded it with LOAD DATA LOCAL INFILE, as most people run it, and printed the warnings and the table:

mysql -h mysql -uroot --local-infile=1 lab <<'SQL' 2>&1
DROP TABLE IF EXISTS items;
CREATE TABLE items (id INT, name VARCHAR(5), qty SMALLINT, price DECIMAL(5,2), flag TINYINT(1));
LOAD DATA LOCAL INFILE 'truncation.csv' INTO TABLE items FIELDS TERMINATED BY ',' IGNORE 1 LINES;
SHOW WARNINGS;
SELECT * FROM items ORDER BY id;
SQL

Level	Code	Message
Warning	1265	Data truncated for column 'name' at row 2
Warning	1264	Out of range value for column 'qty' at row 3
Warning	1264	Out of range value for column 'price' at row 4
Warning	1366	Incorrect integer value: 'x' for column 'qty' at row 5
Warning	1366	Incorrect integer value: 'yes' for column 'flag' at row 5
Note	1265	Data truncated for column 'price' at row 6
id	name	qty	price	flag
1	Ana	5	12.50	1
2	Barth	7	3.00	0
3	Cy	32767	1.25	1
4	Dee	2	999.99	1
5	Eli	0	2.00	0
6	Fay	1	1.23	1

Six rows went in. Each warning names the column and the row, and each corresponds to a value in the table that is not what the file said:

MySQL moved each value to the closest value that fits: the first five characters, the maximum of the type, zero for text that is not a number. The last row is a Note, not a Warning, since losing a third decimal is considered a lesser event, and it appears in the same list. The summary line counted six, and SELECT @@warning_count after the load gives the same number, which is what a script should read:

mysql -h mysql -uroot --local-infile=1 lab -e "TRUNCATE items; LOAD DATA LOCAL INFILE 'truncation.csv' INTO TABLE items FIELDS TERMINATED BY ',' IGNORE 1 LINES; SELECT @@warning_count AS warnings, ROW_COUNT() AS rows_changed" 2>&1

warnings	rows_changed
6	6

warnings is 6 and rows_changed 6. The two numbers are equal here by coincidence, and the second one counts the rows loaded. If your script checks only that rows changed is positive, it passes.

Why the same data fails elsewhere

The same six values in other statements do not load. A single INSERT of a name that is too long stops with ERROR 1406 (22001): Data too long for column 'name' at row 1, and a quantity of 40000 stops with ERROR 1264 (22003): Out of range value for column 'qty' at row 1. The third, a price with a third decimal, is a note only, not an error:

mysql -h mysql -uroot lab -e "INSERT INTO items VALUES (7, 'Bartholomew', 1, 1, 1)" 2>&1
mysql -h mysql -uroot lab -e "INSERT INTO items VALUES (8, 'Gus', 40000, 1, 1)" 2>&1
mysql -h mysql -uroot lab -e "INSERT INTO items VALUES (9, 'Hal', 1, 1.234, 1); SHOW WARNINGS" 2>&1

ERROR 1406 (22001) at line 1: Data too long for column 'name' at row 1
ERROR 1264 (22003) at line 1: Out of range value for column 'qty' at row 1
Level	Code	Message
Note	1265	Data truncated for column 'price' at row 1

A server side LOAD DATA INFILE of the same file, with strict mode on, also stops, at the first bad value:

mysql -h mysql -uroot lab <<'SQL' 2>&1
TRUNCATE items;
LOAD DATA INFILE '/var/lib/mysql-files/truncation.csv' INTO TABLE items FIELDS TERMINATED BY ',' IGNORE 1 LINES;
SELECT COUNT(*) AS rows_loaded FROM items;
SQL

ERROR 1406 (22001) at line 2: Data too long for column 'name' at row 2

ERROR 1406 (22001) at line 2: Data too long for column 'name' at row 2, and nothing was loaded. What differs between the two loads is the word LOCAL. The manual's page for LOAD DATA says that LOCAL has the same effect as IGNORE on how input is interpreted and how errors are handled, because the server cannot stop the client mid-transfer, so data interpretation errors become warnings and the load continues even in a restrictive SQL mode. I put IGNORE on the server side load to confirm the equivalence, and it loaded all six rows too. The hub article shows the same effect with dates, where it writes 0000-00-00.

I also tried SET sql_mode = 'STRICT_ALL_TABLES' on the local load, expecting it to help, and it loaded six rows with warnings. Strict mode governs the statement, and LOCAL overrides it.

Finding the problem before the load

Every warning above is predictable from the file. Two measurements cover most of them: how long the longest string in each column is, and the range of each number. qsv stats computes both, and also tells you which columns are not numbers:

python - <<'PY'
import csv
rows = list(csv.DictReader(open("truncation.csv", newline="")))
limits = {"name": 5}
for r in rows:
    if len(r["name"]) > limits["name"]:
        print(f"id {r['id']}: name {r['name']!r} is {len(r['name'])} characters, column holds {limits['name']}")
PY
qsv stats --everything truncation.csv | qsv select field,type,min,max,max_length | qsv table

id 2: name 'Bartholomew' is 11 characters, column holds 5
field  type     min    max       max_length
id     Integer  1      6         
name   String   Ana    Fay       11
qty    String   1      x         5
price  Float    1.234  12345.67  
flag   String   0      yes       3

The script found Bartholomew at 11 characters against a limit of 5. The table of statistics shows name with a maximum length of 11, qty as a String column, because x is in it, and with a maximum of x where a number should be, price with a maximum of 12345.67, and flag as a String with a maximum length of 3. Each is a column that will lose data. I run this before any load into a table I did not design, and compare each column's maximum length with the column definition and each numeric maximum with the type's range. A SMALLINT tops out at 32767, INT at 2147483647, and DECIMAL(5,2) at 999.99.

Loading everything as text

When the file is untrusted, the dependable route is a staging table whose columns are all wide VARCHAR, which cannot clip or convert anything, and a query that finds the rows that will not fit the real table:

mysql -h mysql -uroot --local-infile=1 lab <<'SQL' 2>&1
DROP TABLE IF EXISTS items_stage;
CREATE TABLE items_stage (id VARCHAR(20), name VARCHAR(100), qty VARCHAR(20), price VARCHAR(20), flag VARCHAR(10));
LOAD DATA LOCAL INFILE 'truncation.csv' INTO TABLE items_stage FIELDS TERMINATED BY ',' IGNORE 1 LINES;
SELECT id, name, CHAR_LENGTH(name) > 5 AS name_too_long, qty REGEXP '^-?[0-9]+$' AND CAST(qty AS SIGNED) BETWEEN -32768 AND 32767 AS qty_ok, price REGEXP '^[0-9]{1,3}(\\.[0-9]{1,2})?$' AS price_ok, flag IN ('0','1') AS flag_ok FROM items_stage ORDER BY id;
SQL

id	name	name_too_long	qty_ok	price_ok	flag_ok
1	Ana	0	1	1	1
2	Bartholomew	1	1	1	1
3	Cy	0	0	1	1
4	Dee	0	1	0	1
5	Eli	0	0	1	0
6	Fay	0	1	0	1

For each row the query returns flags: whether the name is too long, whether the quantity is a number in range, whether the price matches a pattern with at most two decimals, and whether the flag is 0 or 1. Rows 2 to 6 each carry exactly one of the problems and row 1 is clean. The next step is to insert the clean rows into the real table and the others into a reject table, as in the PostgreSQL staging article does with pg_input_is_valid, which MySQL does not have.

The fix, by warning

For Data truncated on a string, widen the column if the data is legitimate, or fix the data at the source if it is not; clipping a name to its first five letters is never the right answer. For Out of range value, choose a bigger type, since an INT for a count that reaches 40000 is better than a SMALLINT that clamps. For Incorrect integer value, find out why a number column holds text: a header row loaded as data, a footer, a note, or a unit such as 5kg. For the decimal note, round on purpose in a SET clause, ROUND(@price, 2), so the decision is in the statement and not in a warning.

Whichever fix you choose, make the load fail on any warning from now on: read @@warning_count in the same session as the load and abort when it is not zero, as the load script in the import guide does. A load that cannot succeed with warnings is a load that cannot quietly corrupt a table.

When the clipped value is not what the warning says

A Data truncated warning on a numeric column can mean a string was cut, but it can also mean a decimal was rounded, or an enum value was not recognized and became the empty string. A Data truncated for column 'status' at row 7 on an ENUM column means the text was not one of the listed values, which is a different bug: the list in the table definition and the values in the file do not agree. And if the warning count is much larger than the number of bad rows, one row can raise several, so count rows by their numbers in the warning text, not by the total.

If you have only Records: 6 Deleted: 0 Skipped: 0 Warnings: 6 to go on and the load already finished, the table is already altered. Reload from the file after fixing the cause, and compare the new table with the old one to see which rows changed. If the table is large and the damage is small, find the changed values by comparing against the source with a join, and fix those rows only.