Re: MySQL 8.0 LOAD DATA INFILE error 1265 Truncated column

[Peter Brawley]

> On dates and time: as you can see those are varchars, not actual date columns.

Yes. Design errors.

> On capital names: It's windows and will stay so.

Ditto.

> 0 PGEN_TEST.19TXT_000 0 0 20191119 0 0 1 0 10:55:39 0 0 000 1

Your Load Data cmd produced this result ...

LOAD DATA INFILE 'c:/users/pb/in.txt'
-> INTO TABLE palajjobs
-> LINES TERMINATED BY '\r\n';
Query OK, 1 row affected, 19 warnings (0.01 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 19

Warning (Code 1265): Data truncated for column 'SEQ' at row 1
Warning (Code 1261): Row 1 doesn't contain data for all columns
Warning (Code 1261): Row 1 doesn't contain data for all columns
Warning (Code 1261): Row 1 doesn't contain data for all columns
Warning (Code 1261): Row 1 doesn't contain data for all columns
Warning (Code 1261): Row 1 doesn't contain data for all columns
Warning (Code 1261): Row 1 doesn't contain data for all columns
Warning (Code 1261): Row 1 doesn't contain data for all columns
Warning (Code 1261): Row 1 doesn't contain data for all columns
Warning (Code 1261): Row 1 doesn't contain data for all columns
Warning (Code 1261): Row 1 doesn't contain data for all columns
Warning (Code 1261): Row 1 doesn't contain data for all columns
Warning (Code 1261): Row 1 doesn't contain data for all columns
Warning (Code 1261): Row 1 doesn't contain data for all columns
Warning (Code 1261): Row 1 doesn't contain data for all columns
Warning (Code 1261): Row 1 doesn't contain data for all columns
Warning (Code 1261): Row 1 doesn't contain data for all columns
Warning (Code 1261): Row 1 doesn't contain data for all columns
Warning (Code 1261): Row 1 doesn't contain data for all columns

I've had this experience before from leaving column terminators to the default. Adding the declared column delimiter to ' ' in the command produced this ...

LOAD DATA INFILE 'c:/users/pb/in.txt'
-> INTO TABLE palajjobs
-> FIELDS TERMINATED BY ' '
-> LINES TERMINATED BY '\r\n';
Query OK, 1 row affected, 6 warnings (0.01 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 6

Warning (Code 1265): Data truncated for column 'END_REC' at row 1
Warning (Code 1261): Row 1 doesn't contain data for all columns
Warning (Code 1261): Row 1 doesn't contain data for all columns
Warning (Code 1261): Row 1 doesn't contain data for all columns
Warning (Code 1261): Row 1 doesn't contain data for all columns
Warning (Code 1261): Row 1 doesn't contain data for all columns
localhost.t>

localhost.t>
select * from palajjobs\G
*************************** 1. row ***************************
SEQ: 0
JOB_ID: PGEN_TEST.19TXT_000
JID: 0
COUNT: 0
DATE_IN: 20191119
DATE_PROD: 0
STATUS: 0
GENERATION: 1
START_REC: 0
END_REC: 10
NOTE: 0
DATE_DISPATCH: 0
TIME_IN: 000
TIME_PROD: 1
TIME_DISPATCH:
SCRAPS: 0
FSTATUS: 0
TMPL_ID:
INST_ID: 0
1 row in set (0.00 sec)

... indicating that the data file still doesn't match the table def.