[MDEV-34883] LOAD DATA INFILE with geometry data fails - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Blocker
Resolution: Fixed
Affects Version/s: 11.6.1
Fix Version/s: 10.5.27, 10.6.20, 10.11.10, 11.2.6, 11.4.4
Component/s: Server
Labels:
- regression

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

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

airport_geo.mysqldump.zip
388 kB
2024-09-05 17:49
airport_geo.zip
357 kB
2024-09-05 19:32

Issue Links

is caused by

MDEV-19123 Change default charset from latin1 to utf8mb4

Closed

relates to

MDEV-34959 Add an OUTFILE option to dump binary data using HEX encoding

Open

MDEV-34890 SELECT INTO OUTFILE/LOAD DATA INFILE - option to encode binary data as hex of base64

Open

Activity

Descending order - Click to sort in ascending order

Oleksandr Byelkin added a comment - 2024-10-16 12:00

commit c492d7fcc8b94720fc7b62402c39cc66f8892b50 (HEAD -> bb-10.5-MDEV-34883, origin/bb-10.5-MDEV-34883)

Author: Oleksandr Byelkin <sanja@mariadb.com>

Date:   Wed Oct 16 13:28:20 2024 +0200

    MDEV-34883 LOAD DATA INFILE with geometry data fails

    We rite field using field data charset, so we should read it

    using the field charset.

Oleksandr Byelkin added a comment - 2024-10-16 12:00 commit c492d7fcc8b94720fc7b62402c39cc66f8892b50 (HEAD -> bb-10.5-MDEV-34883, origin/bb-10.5-MDEV-34883) Author: Oleksandr Byelkin <sanja@mariadb.com> Date: Wed Oct 16 13:28:20 2024 +0200 MDEV-34883 LOAD DATA INFILE with geometry data fails We rite field using field data charset, so we should read it using the field charset.

Oleksandr Byelkin added a comment - 2024-10-16 11:29

commit e8b1507dcb7300ba84a07fdb3a04ed4b65b0b566 (HEAD -> bb-10.5-MDEV-34883, origin/bb-10.5-MDEV-34883)

Author: Oleksandr Byelkin <sanja@mariadb.com>

Date:   Wed Oct 16 13:28:20 2024 +0200

    MDEV-34883 LOAD DATA INFILE with geometry data fails

    We rite field using field data charset, so we should read it

    using the field charset.

Oleksandr Byelkin added a comment - 2024-10-16 11:29 commit e8b1507dcb7300ba84a07fdb3a04ed4b65b0b566 (HEAD -> bb-10.5-MDEV-34883, origin/bb-10.5-MDEV-34883) Author: Oleksandr Byelkin <sanja@mariadb.com> Date: Wed Oct 16 13:28:20 2024 +0200 MDEV-34883 LOAD DATA INFILE with geometry data fails We rite field using field data charset, so we should read it using the field charset.

Alexander Barkov added a comment - 2024-09-18 08:08

The problem is repeatable with 10.5+ with this script:

CREATE DATABASE db1 CHARACTER SET utf8mb4;

USE db1;

CREATE OR REPLACE TABLE t1 (

  p point NOT NULL

) ENGINE=MyISAM;

INSERT INTO t1 VALUES (GeomFromText('POINT(37.646944 -75.761111)'));

SELECT * FROM t1 INTO OUTFILE 't1.tsv';

CREATE OR REPLACE TABLE t2 LIKE t1;

LOAD DATA INFILE 't1.tsv' INTO TABLE t2;

Alexander Barkov added a comment - 2024-09-18 08:08 The problem is repeatable with 10.5+ with this script: CREATE DATABASE db1 CHARACTER SET utf8mb4; USE db1; CREATE OR REPLACE TABLE t1 ( p point NOT NULL ) ENGINE=MyISAM; INSERT INTO t1 VALUES (GeomFromText( 'POINT(37.646944 -75.761111)' )); SELECT * FROM t1 INTO OUTFILE 't1.tsv' ; CREATE OR REPLACE TABLE t2 LIKE t1; LOAD DATA INFILE 't1.tsv' INTO TABLE t2;

Alexander Barkov added a comment - 2024-09-18 07:53 - edited

Even smaller script:

CREATE OR REPLACE TABLE t1 (

  p point NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO t1 VALUES (GeomFromText('POINT(37.646944 -75.761111)'));

SELECT * FROM t1 INTO OUTFILE 't1.tsv';

CREATE OR REPLACE TABLE t2 LIKE t1;

LOAD DATA INFILE 't1.tsv' INTO TABLE t2;

ERROR 1416 (22003): Cannot get geometry object from data you send to the GEOMETRY field

Alexander Barkov added a comment - 2024-09-18 07:53 - edited Even smaller script: CREATE OR REPLACE TABLE t1 ( p point NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE =utf8mb4_unicode_ci; INSERT INTO t1 VALUES (GeomFromText( 'POINT(37.646944 -75.761111)' )); SELECT * FROM t1 INTO OUTFILE 't1.tsv' ; CREATE OR REPLACE TABLE t2 LIKE t1; LOAD DATA INFILE 't1.tsv' INTO TABLE t2; ERROR 1416 (22003): Cannot get geometry object from data you send to the GEOMETRY field

Vladislav Vaintroub added a comment - 2024-09-06 07:56 - edited

The exact mysqldump command line was, I think

mysqldump -uroot flughafendb_large --tables airport_geo --hex-blob ...

The important part was --hex-blob

The original dataset is from https://github.com/stefanproell/flughafendb, and the my intention was trying something large-ish and more "real-life" for "mysqldump --dir / mariadb-import --dir" combo, which both do SELECT * into outfile rsp LOAD DATA INFILE for every table in the database internally.

Vladislav Vaintroub added a comment - 2024-09-06 07:56 - edited The exact mysqldump command line was, I think mysqldump -uroot flughafendb_large --tables airport_geo --hex-blob ... The important part was --hex-blob The original dataset is from https://github.com/stefanproell/flughafendb , and the my intention was trying something large-ish and more "real-life" for "mysqldump --dir / mariadb-import --dir" combo, which both do SELECT * into outfile rsp LOAD DATA INFILE for every table in the database internally.

Alexander Barkov added a comment - 2024-09-06 05:17

A smaller script demonstating the problem:

CREATE OR REPLACE 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;

INSERT INTO `airport_geo` VALUES (53,'ACCOMACK CO','MELFA','UNITED STATES',37.64694400,-75.76111100,GeomFromText('POINT(37.646944 -75.761111)'));

SELECT * FROM airport_geo INTO OUTFILE 'airport_geo.tsv';

CREATE OR REPLACE TABLE airport_geo2 LIKE airport_geo;

LOAD DATA INFILE 'airport_geo.tsv' INTO TABLE airport_geo2;

ERROR 1263 (22004): Column set to default value; NULL supplied to NOT NULL column 'geolocation' at row 2

Alexander Barkov added a comment - 2024-09-06 05:17 A smaller script demonstating the problem: CREATE OR REPLACE 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; INSERT INTO `airport_geo` VALUES (53, 'ACCOMACK CO' , 'MELFA' , 'UNITED STATES' ,37.64694400,-75.76111100,GeomFromText( 'POINT(37.646944 -75.761111)' )); SELECT * FROM airport_geo INTO OUTFILE 'airport_geo.tsv' ; CREATE OR REPLACE TABLE airport_geo2 LIKE airport_geo; LOAD DATA INFILE 'airport_geo.tsv' INTO TABLE airport_geo2; ERROR 1263 (22004): Column set to default value; NULL supplied to NOT NULL column 'geolocation' at row 2

Alexander Barkov added a comment - 2024-09-06 04:32 - edited

The safest way would be to dump binary data using some encoding: hex or base64.

wlad, what was the exact command line with the mysqldump command?

Alexander Barkov added a comment - 2024-09-06 04:32 - edited The safest way would be to dump binary data using some encoding: hex or base64. wlad , what was the exact command line with the mysqldump command?

Alexander Barkov added a comment - 2024-09-06 04:30 - edited

A workaround is:

mariadb -uroot test -e "TRUNCATE airport_geo;  LOAD DATA INFILE '/mnt/e/airport_geo.tsv' INTO TABLE airport_geo CHARACTER SET latin1"

or better:

mariadb -uroot test -e "TRUNCATE airport_geo;  LOAD DATA INFILE '/mnt/e/airport_geo.tsv' INTO TABLE airport_geo CHARACTER SET binary"

This command also worked for me:

mariadb -uroot test -e "TRUNCATE airport_geo;  SET character_set_database=latin1; LOAD DATA INFILE '/mnt/e/airport_geo.tsv' INTO TABLE airport_geo"

Alexander Barkov added a comment - 2024-09-06 04:30 - edited A workaround is: mariadb -uroot test -e "TRUNCATE airport_geo; LOAD DATA INFILE '/mnt/e/airport_geo.tsv' INTO TABLE airport_geo CHARACTER SET latin1" or better: mariadb -uroot test -e "TRUNCATE airport_geo; LOAD DATA INFILE '/mnt/e/airport_geo.tsv' INTO TABLE airport_geo CHARACTER SET binary" This command also worked for me: mariadb -uroot test -e "TRUNCATE airport_geo; SET character_set_database=latin1; LOAD DATA INFILE '/mnt/e/airport_geo.tsv' INTO TABLE airport_geo"

People

Assignee:: Alexander Barkov

Reporter:: Vladislav Vaintroub

Votes:: 0 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2024-09-05 18:25

Updated:: 2024-10-21 12:24

Resolved:: 2024-10-21 12:24

Git Integration

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

MariaDB Server

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration