Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-18161

LOAD DATA INFILE failed "DATA TRUNCATED FOR COLUMN [0] AT ROW 1"

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.2.13
    • N/A
    • OTHER
    • None
    • 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"

      Attachments

        Activity

          People

            Unassigned Unassigned
            MTarbet Margaret Tarbet
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.