[MDEV-18161] LOAD DATA INFILE failed "DATA TRUNCATED FOR COLUMN [0] AT ROW 1" Created: 2019-01-07  Updated: 2019-01-07  Resolved: 2019-01-07

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 10.2.13
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Margaret Tarbet Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

FreeBSD 11.0 Release running on Supermicro 2558 board w/16GB, ZFS 3-way mirror


 Description   

I'm trying to LOAD DATA INFILE because doing inserts is taking forever. But it fails, complaining that the data in the first column of the first row is being truncated. No error number.

The data is from geonames.org, one line per record, that I massaged to add dquotes around fields, tabs in between, and a newline to terminate lines.

The SQL command is
LOAD DATA INFILE "<fully-resolved filename>"
INTO TABLE <tablename>
FIELDS ENCLOSED BY '"' TERMINATED BY '\t'
LINES TERMINATED BY '\n '

The table def is
GeonameID INTEGER UNSIGNED NOT NULL DEFAULT 0,
Name VARCHAR(200) NOT NULL DEFAULT "",
ASCIIname VARCHAR(200) NOT NULL DEFAULT "",
AltNames VARCHAR(10000) NOT NULL DEFAULT "",
Lat DECIMAL(12,7) NOT NULL DEFAULT 0,
Lon DECIMAL(12,7) NOT NULL DEFAULT 0,
FeatureClass CHAR(1) NOT NULL DEFAULT "",
FeatureCode VARCHAR(10) NOT NULL DEFAULT "",
CountryCode CHAR(2) NOT NULL DEFAULT "",
AltCountryCodes CHAR(200) NOT NULL DEFAULT "",
ADM1 VARCHAR(20) NOT NULL DEFAULT "",
ADM2 VARCHAR(80) NOT NULL DEFAULT "",
ADM3 VARCHAR(20) NOT NULL DEFAULT "",
ADM4 VARCHAR(20) NOT NULL DEFAULT "",
Pop BIGINT NOT NULL DEFAULT 0,
ElevationM INTEGER NOT NULL DEFAULT 0,
DEM VARCHAR(80) NOT NULL DEFAULT "",
TimezoneIANA VARCHAR(40) NOT NULL DEFAULT "",
LastMod DATETIME NOT NULL DEFAULT "2019-01-07"

 Comments   
Comment by Elena Stepanova [ 2019-01-07 ]

Please paste the actual error you are getting as is, rather than the verbal description of it, and the row from the data file. If there is confidential information in it, you can obfuscate it, but keep the lengths of the values intact.

Comment by Margaret Tarbet [ 2019-01-07 ]

Error message:
Failed to load /scopedata/geonames_snippet because Data truncated for column 'GeonameID' at row 1

Line from file (there are tabs between fields - they just don't show here):
2986043 Pic de Font Blanca Pic de Font Blanca Pic de Font Blanca 42.64991 1.53335 T PK AD 00 0 2860 Europe/Andorra 2014-11-05

Comment by Margaret Tarbet [ 2019-01-07 ]

Scratch this...operator error.

Generated at Thu Feb 08 08:41:58 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.