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

LOAD DATA INFILE with geometry data fails

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

          is caused by

          New Feature - A new feature of the product, which has yet to be developed. MDEV-19123 Change default charset from latin1 to utf8mb4

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          New Feature - A new feature of the product, which has yet to be developed. MDEV-34959 Add an OUTFILE option to dump binary data using HEX encoding

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-34890 SELECT INTO OUTFILE/LOAD DATA INFILE - option to encode binary data as hex of base64

          • Major - Major loss of function.
          • Open

          Activity

            Transition Time In Source Status Execution Times
            Oleksandr Byelkin made transition -
            Open In Progress
            		38d 16h 27m 1
            Oleksandr Byelkin made transition -
            In Progress In Review
            		2d 36m 1
            Oleksandr Byelkin made transition -
            In Review Stalled
            		22h 1m 1
            Oleksandr Byelkin made transition -
            Stalled Closed
            		14s 1
            Oleksandr Byelkin made transition -
            Closed Stalled
            		9h 17m 1
            Oleksandr Byelkin made transition -
            Stalled In Progress
            		5s 1
            Alexander Barkov made transition -
            In Progress Closed
            		3d 17h 35m 1

            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.