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

LOAD DATA INFILE does not work well with a TEXT column when using sjis

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5, 10.0, 10.1
    • 10.2.0
    • Character Sets
    • None

    Description

      I create a text file:

      mysql --default-character-set=sjis --skip-column-names --exec="SELECT CONCAT('x', REPEAT(_sjis 0x835C, 200))" >/tmp/test.txt
      

      It consists of one LATIN SMALL LETTER X, followed by 200 characters KATAKANA LETTER SO (which is encoded as 0x835C in SJIS).

      Now I create a table and load the file into it:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a TEXT CHARACTER SET sjis);
      LOAD DATA INFILE '/tmp/test.txt' INTO TABLE t1 CHARACTER SET sjis;
      SHOW WARNINGS;
      SELECT a FROM t1;
      

      It returns the following warning:

      +---------+------+------------------------------------------------------------+
      | Level   | Code | Message                                                    |
      +---------+------+------------------------------------------------------------+
      | Warning | 1366 | Incorrect string value: '\x83\x0A' for column 'a' at row 1 |
      +---------+------+------------------------------------------------------------+
      

      and this result (make sure to scroll the below window to the right):

      +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | a                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
      +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | xソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソャャャャャャャャャャャャャャャャャャャャャャャャャャャャャャャャャャャャ?
                                                                                                                                                                          |
      +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      

      The warning and the result look wrong:

      • Notice, it started to import the data well, but at some offset KATAKANA LETTER SO changed to something else.
      • The question mark at the end is wrong.

      If I now change the column type from TEXT to VARCHAR(1000), it loads the file without problems and without warnings:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a VARCHAR(1000) CHARACTER SET sjis);
      LOAD DATA INFILE '/tmp/test.txt' INTO TABLE t1 CHARACTER SET sjis; 
      SELECT a FROM t1;
      

      +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | a                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
      +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | xソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソソ                                                                                                                                                                                                         |
      +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.