CSV engine does not properly process "" (in quotes)

Bug #1316042 reported by Jericho Rivera on 2014-05-05
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MySQL Server
Unknown
Unknown
Percona Server moved to https://jira.percona.com/projects/PS
Fix Released
Medium
Laurynas Biveinis
5.1
Won't Fix
Undecided
Laurynas Biveinis
5.5
Fix Released
Medium
Laurynas Biveinis
5.6
Fix Released
Medium
Laurynas Biveinis

Bug Description

Original description can be found on upstream bug.

MySQL's CSV engine does NOT interpret "", inside quotes properly. It requires sequence like \", to be there instead, without clear reason (I see nothing like this explained here, http://dev.mysql.com/doc/refman/5.6/en/csv-storage-engine.html).

How to repeat (taken from upstream bug description):

On any MySQL server version execute the following:

mysql> CREATE TABLE `emp` (
    -> `col1` text NOT NULL,
    -> `col2` text NOT NULL
    -> ) ENGINE=CSV DEFAULT CHARSET=utf8
    -> ;
Query OK, 0 rows affected (0.11 sec)

mysql> insert into emp values ("alan", "newyork"), ("jim", "CA\",boston");
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from emp;
+------+------------+
| col1 | col2 |
+------+------------+
| alan | newyork |
| jim | CA",boston |
+------+------------+
2 rows in set (0.00 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.05 sec)

Now, check what is in emp.CSV file in the database directory. You'll see:

"alan","newyork"
"jim","CA\",boston"

I do not see this documented on Wiki as acceptable way of quoting. Now, change file to conform to "standard":

"alan","newyork"
"jim","CA"",boston"

Then check how it is interpreted/loaded:

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from emp;
+------+---------+
| col1 | col2 |
+------+---------+
| alan | newyork |
| jim | CA" |
+------+---------+
2 rows in set (0.02 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.33 |
+-----------+
1 row in set (0.01 sec)

Related branches

tags: added: upstream
description: updated
tags: added: i37457
removed: i37547
Jericho Rivera (jericho-rivera) wrote :

Tested as described above affects 5.5.36-34.2, 5.6.16-64.2 and 5.1.73

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-153

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.