|
Rewording
Cpimport and LDIF of the same file doesn't have the same result. Cpimport appears to not truncate strings
cpimport test flights /tmp/flights.txt -m1 -s '\t'
|
versus
|
mariadb test -e "LOAD DATA INFILE '/tmp/flights.txt' IGNORE INTO TABLE flights2 FIELDS TERMINATED BY '\t';"
|
Expected:
When using cpimport - Strings longer than 255 are truncated to fit varchar(255) just like LDIF does
Actual:
cpimport does not truncate strings even when the column is defined as varchar(255), unlike LDIF
Reproduction:
Follow the commands/steps in reproduction.bash after scp of flights.txt to /tmp/ directory
-----------------------------
it seems that cpimport could multiply some characters (up to number of charset bytes) when loading data into varchar column(s).
For example, in the original case, data loaded from .tsv file into varchar(255) as
cpimport test flights_repro flights_repro.txt -m1 -e1 -s '\t' -n1
resulted in the following output (charset=utf8mb3), which does not look right:
select id, lengthb(notes),char_length(notes) from flights_repro;
------
| id |
lengthb(notes) |
char_length(notes) |
------
| 3 |
765 |
765 |
| 5199 |
765 |
765 |
| 7275 |
765 |
765 |
...
If the same data were loaded via LDIF as
LOAD DATA INFILE '/tmp/flights2.txt' INTO TABLE flights2_cs FIELDS TERMINATED BY '\t';
then result looks correct:
select id, lengthb(notes),char_length(notes) from flights_repro;
------
| id |
lengthb(notes) |
char_length(notes) |
------
| 3 |
255 |
255 |
| 5199 |
255 |
255 |
| 7275 |
255 |
255 |
...
An attempted simplified repro is the following:
repro.tsv produced as (in same way/options as in the original case)
mysql -Ns -B -D test --execute="select id,notes from flights_biu where id =3" > repro.txt
use test;
CREATE TABLE `repro` (
`id` int(11) NOT NULL,
`notes` varchar(255) DEFAULT NULL
) ENGINE=Columnstore DEFAULT CHARSET=utf8mb3
;
LOAD DATA INFILE '/tmp/repro.tsv' INTO TABLE repro FIELDS TERMINATED BY '\t';
...
Query OK, 1 row affected (1.186 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
...
select id, lengthb(notes),char_length(notes) from repro;
---
| id |
lengthb(notes) |
char_length(notes) |
---
---
\q
mysql -Ns -B -D test --execute="select id,notes from repro" > repro_ldif.tsv
truncate table repro;
cpimport test repro repro.tsv -m1 -e1 -s '\t' -n1
...
2021-11-22 16:19:25 (4607) INFO : Running distributed import (mode 1) on all PMs...
2021-11-22 16:19:25 (4607) INFO : For table test.repro: 1 rows processed and 1 rows inserted.
2021-11-22 16:19:25 (4607) INFO : Bulk load completed, total run time : 0.192545 seconds
...
select id, lengthb(notes),char_length(notes) from repro;
---
| id |
lengthb(notes) |
char_length(notes) |
---
---
1 row in set (0.037 sec)
\q
mysql -Ns -B -D test --execute="select id,notes from flights_biu where id =3" > repro_cpimp.tsv
SELECT and comparison of dumps produced by cpimp and ldif shows that cpimport loads 2 extra '
' at the beginning of line. While LDIF loads data correctly, without prepending.
I not sure whether options are wrong or is there a problem with cpimport ?
|
flights.txt
repro.tsv