"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.
|
Bug
Blocker
MDEV-19123 Change default charset from latin1 to utf8mb4
MDEV-34890 SELECT INTO OUTFILE/LOAD DATA INFILE - option to encode binary data as hex of base64
