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

LOAD DATA INFILE with geometry data fails

    XMLWordPrintable

Details

    Description

      "LOAD DATA INFILE" fails, for binary (or, geometry) data , for the file created with SELECT * INTO OUTFILE

      I'm attaching example data where it can be demonstrated.

      To reproduce, extract the dump file and load it

      mariadb -uroot test < /mnt/e/airport_geo.mysqldump.sql
      mariadb -uroot test -e "SELECT * FROM airport_geo INTO OUTFILE '/mnt/e/airport_geo.tsv'"
      mariadb -uroot test -e "TRUNCATE airport_geo;  LOAD DATA INFILE '/mnt/e/airport_geo.tsv' INTO TABLE airport_geo"
      

      The last command fails with

      --------------
      LOAD DATA INFILE '/mnt/e/airport_geo.tsv' INTO TABLE airport_geo
      --------------
       
      ERROR 1263 (22004) at line 1: Column set to default value; NULL supplied to NOT NULL column 'geolocation' at row 42
      

      this is the DDL for the create table, column "geolocation" is a POINT

      CREATE TABLE `airport_geo` (
        `airport_id` smallint(6) NOT NULL,
        `name` varchar(50) NOT NULL,
        `city` varchar(50) DEFAULT NULL,
        `country` varchar(50) DEFAULT NULL,
        `latitude` decimal(11,8) NOT NULL,
        `longitude` decimal(11,8) NOT NULL,
        `geolocation` point NOT NULL,
        KEY `flughafen_idx` (`airport_id`),
        SPATIAL KEY `geolokation_spt` (`geolocation`)
      ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
      

      "git bisect" points to MDEV-19123 as first bad commit

      36eba98817339f53cc57f1d4884ace3efb38db8d is the first bad commit
      commit 36eba98817339f53cc57f1d4884ace3efb38db8d
      Date:   Tue May 28 09:08:51 2024 +0400
       
          MDEV-19123 Change default charset from latin1 to utf8mb4
       
          Changing the default server character set from latin1 to utf8mb4.
      

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              wlad Vladislav Vaintroub
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.