Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-4931

cpimport does not truncate strings - need to keep charset/collation? in sys catalog

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: In Progress (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 5.5.1, 6.1.1
    • Fix Version/s: 22.08.2
    • Component/s: cpimport
    • Labels:
      None
    • Environment:
      CentOS; Amazon EC2
    • Sprint:
      2021-16, 2021-17

      Description

      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)

      ------------------------------------

      3 255 255

      ------------------------------------

      \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)

      ------------------------------------

      3 259 259

      ------------------------------------
      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 ?

        Attachments

        1. flights.txt
          20 kB
        2. repro_cpimp.tsv
          0.3 kB
        3. repro_ldif.tsv
          0.3 kB
        4. repro.tsv
          0.3 kB
        5. reproduction.bash
          7 kB

          Issue Links

            Activity

              People

              Assignee:
              ben.thompson Ben Thompson
              Reporter:
              YK Yakov Kushnirsky
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

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