Details
-
Bug
-
Status: Closed (View Workflow)
-
Trivial
-
Resolution: Fixed
-
6.4.2
-
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:
- The missing newline
- Export of null values (to either \N or NULL)
- Literal NULL and \N values being confused with SQL null values
