Bug #79597 "load data infile" is limited to ASCII characters as field enclosers
Submitted: 10 Dec 2015 19:47 Modified: 17 May 2018 16:15
Reporter: Jörg Brühe (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Charsets Severity:S3 (Non-critical)
Version:5.6.24, 5.7.10 OS:Any
Assigned to: CPU Architecture:Any
Tags: ASCII, utf8

[10 Dec 2015 19:47] Jörg Brühe
Description:
A "load data infile" command will not accept non-ASCII characters as string delimiters.

This restriction is not documented.

I did not check any of the other separator or delimiter characters.

I did not check 5.5, but I assume it is also affected.

How to repeat:
mysql@testmaster:~ [mysql-5624-gtid, 3311]> cat /tmp/utf8.csv  
€between EUR€

=====

root@mysql-5624-gtid [utf_test] SQL> show global variables like 'version' ;
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| version       | 5.6.24-log |
+---------------+------------+
1 row in set (0.00 sec)

root@mysql-5624-gtid [utf_test] SQL> show create database utf_test ;
+----------+------------------------------------------------------------------------------------+
| Database | Create Database                                                                    |
+----------+------------------------------------------------------------------------------------+
| utf_test | CREATE DATABASE `utf_test` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */ |
+----------+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@mysql-5624-gtid [utf_test] SQL> show create table t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `f1` varchar(30) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

root@mysql-5624-gtid [utf_test] SQL> load data infile '/tmp/utf8.csv' into table t1 fields optionally enclosed by '€' ;
ERROR 1083 (42000): Field separator argument is not what is expected; check the manual
root@mysql-5624-gtid [utf_test] SQL> load data infile '/tmp/utf8.csv' into table t1 fields optionally enclosed by '=' ;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

Suggested fix:
At least, document that the single character of "fields enclosed by" must be an ASCII character.

Even better, lift that restriction.
[11 Dec 2015 5:38] MySQL Verification Team
Hello Jörg Brühe,

Thank you for the report.

Thanks,
Umesh
[11 Dec 2015 5:40] MySQL Verification Team
// 5.6.10
[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.10: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.10-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> \! cat /tmp/utf8.csv
€between EUR€

mysql> CREATE TABLE `t1` (
    ->   `f1` varchar(30) COLLATE utf8_bin DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Query OK, 0 rows affected (0.00 sec)

mysql> load data infile '/tmp/utf8.csv' into table t1 fields optionally enclosed by "€" ;
ERROR 1083 (42000): Field separator argument is not what is expected; check the manual
mysql> load data infile '/tmp/utf8.csv' into table t1 fields optionally enclosed by '€' ;
ERROR 1083 (42000): Field separator argument is not what is expected; check the manual

mysql>
mysql>
mysql> \! vi /tmp/utf8.csv
mysql> \! cat /tmp/utf8.csv
=between EUR=
mysql>
mysql> load data infile '/tmp/utf8.csv' into table t1 fields optionally enclosed by "=" ;
Query OK, 1 row affected (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from t1;
+-------------+
| f1          |
+-------------+
| between EUR |
+-------------+
1 row in set (0.00 sec)
[20 Dec 2016 19:28] Zeeshan Zinch
Is there any plan to fix this ?
[17 May 2018 16:15] Paul Dubois
Posted by developer:
 
LOAD DATA page updated.