Uploaded image for project: 'MariaDB MaxScale'
  1. MariaDB MaxScale
  2. MXS-4267

NULL values are exported as empty strings when using CSV format

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Trivial
    • Resolution: Fixed
    • 6.4.2
    • 6.4.3
    • maxgui
    • None
    • MXS-SPRINT-166

    Description

      When exporting query results from the query editor, the format of the generated file differs from the one MariaDB uses. The query editor also seems to be missing the terminating newline for the last row of the data which causes MariaDB to treat it as an invalid CSV record.

      Exporting the following query as CSV with tabs as separators reproduces the problem:

      SELECT NULL, 0, '' INTO OUTFILE 'data-server.csv';
      

      Here's a hexdump of both the file from MaxScale and the one generated by the server when used with the SELECT ... INTO OUTFILE command. As can be seen, the data generated by the server seems to have the special value \N for SQL null values when exported via SELECT ... INTO OUTFILE.

      [markusjm@monolith Downloads]$ hexdump -C data-maxscale.csv
      00000000  09 30 09                                          |.0.|
      00000003
      [markusjm@monolith Downloads]$ hexdump -C data-server.csv
      00000000  5c 4e 09 30 09 0a                                 |\N.0..|
      00000006
      

      This also reveals a minor problem where the following query ends up generating a NULL value instead of the string literal \N:

      SELECT '\\N' INTO OUTFILE 'data-server.csv';
      

      To make things worse, this seems to be affected by the current SQL_MODE of the connection: with NO_BACKSLASH_ESCAPES the null values are exported as NULL whereas without it they are exported as \N.

      Here's what happens when the files are loaded into MariaDB. This was tested with MariaDB 10.6.8.

      MariaDB [test]> create or replace table loading(a varchar(20), b varchar(20), c varchar(20));
      Query OK, 0 rows affected (0.072 sec)
       
      MariaDB [test]> load data local infile 'data-maxscale.csv' into table loading;
      Query OK, 1 row affected, 1 warning (0.013 sec)      
      Records: 1  Deleted: 0  Skipped: 0  Warnings: 1
       
      MariaDB [test]> show warnings;
      +---------+------+--------------------------------------------+
      | Level   | Code | Message                                    |
      +---------+------+--------------------------------------------+
      | Warning | 1261 | Row 1 doesn't contain data for all columns |
      +---------+------+--------------------------------------------+
      1 row in set (0.000 sec)
       
      MariaDB [test]> load data local infile 'data-server.csv' into table loading;
      Query OK, 1 row affected (0.012 sec)                 
      Records: 1  Deleted: 0  Skipped: 0  Warnings: 0
       
      MariaDB [test]> select * from loading;
      +------+------+------+
      | a    | b    | c    |
      +------+------+------+
      |      | 0    | NULL |
      | NULL | 0    |      |
      +------+------+------+
      2 rows in set (0.001 sec)
      

      I think the order of importance for these problems is:

      1. The missing newline
      2. Export of null values (to either \N or NULL)
      3. Literal NULL and \N values being confused with SQL null values

      Attachments

        1. data-maxscale.csv
          0.0 kB
          markus makela
        2. data-server.csv
          0.0 kB
          markus makela
        3. new-export-dialog-UI.png
          32 kB
          Thien Ly

        Activity

          People

            thien.ly Thien Ly
            markus makela markus makela
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.