| 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: | |
| 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 | ||
[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.
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.