Bug #73524 CSV export corrupted on char(0) (0x00)
Submitted: 10 Aug 2014 22:50 Modified: 27 Aug 2014 3:25
Reporter: Dan Kloke (Candidate Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench Severity:S5 (Performance)
Version:6.1.7 OS:Microsoft Windows (Microsoft Windows 7 Service Pack 1 build 7601, 64-bit)
Assigned to: CPU Architecture:Any
Tags: WBBugReporter

[10 Aug 2014 22:50] Dan Kloke
Description:
When exporting result sets to CSV, presence of char(0) (0x00) in field corrupts output product file. Binary examination of the product file shows that this is not an artifact of the file viewer app.

Some right-click Copy Row commands are also affected.

Granted, 0x00 means end of string. But returned values indicate that subsequent column values, and/or the separator for the next row, are being lost as well.

It may be considered correct to truncate the product of a column value at 0x00, however this may hide further character data present in the column value. To most correctly render column contents, all control/non-printing characters should be rendered to the output stream, ignoring semantic rules.

How to repeat:
drop table test.test
;
create table test.test (v1 varchar(20),v2 varchar(20))
;
insert into test.test 
values ('asdf','ghjk'),
 ('123',char(0)),
 ('qwer','zxcv'),
 ('456',null),
 (char(0),'has char(0)'),
 (concat('embed',char(0),'here'),'embedded char(0)'),
 ('last','row')
;
select * from test.test
;

/*
export to CSV, examine product:
v1,v2
asdf,ghjk
123,qwer,zxcv
456,NULL
"embedlast,row

should be:
"v1","v2"
"asdf","ghjk"
"123",""
"qwer","zxcv"
"456",""
"","has char(0)"
"embed","embedded char(0)"
"last","row"

or similar.
*/

Suggested fix:
Review CSV export and Copy Row code. Correctly handle case where column value is or contains 0x00. Simple string concatenation of row values is prone to this behavior.

The configurable option to embed or escape control characters may be desirable.
[11 Aug 2014 5:40] Umesh Shastry
Hello Dan,

Thank you for the bug report and test case.
Verified as described on Win7 with WB 6.1.7.

Thanks,
Umesh
[11 Aug 2014 5:42] Umesh Shastry
// exported csv shows

v1,v2
asdf,ghjk
123,qwer,zxcv
456,NULL
embedlast,row

// Rows in table

mysql> select * from test.test;
+------------+------------------+
| v1         | v2               |
+------------+------------------+
| asdf       | ghjk             |
| 123        |                  |
| qwer       | zxcv             |
| 456        | NULL             |
|            | has char(0)      |
| embed here | embedded char(0) |
| last       | row              |
+------------+------------------+
7 rows in set (0.00 sec)
[27 Aug 2014 3:25] Philip Olson
Fixed as of the upcoming MySQL Workbench 6.2.2 release, and here's the changelog entry:

With the presence of char(0) (0x00) in data fields, exporting result sets
to CSV would sometimes generate corrupt data.

Thank you for the bug report.