Bug #40320 select into outfile mistake with multiline strings
Submitted: 24 Oct 2008 20:49 Modified: 30 Oct 2008 18:02
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.67, 4.1, 5.0, 5.1, 6.0 bzr, 8.0.22 OS:Any
Assigned to: CPU Architecture:Any
Tags: qc

[24 Oct 2008 20:49] Peter Laursen
Description:
A multiline strings select's into outfile with one escape character too much and accordingly does not import! Additionally a NULL-line is created when importing with LOAD DATA

How to repeat:
DROP TABLE IF EXISTS `tab10`;

CREATE TABLE `tab10` (
  `chief_id` int(11) NOT NULL,
  `val1` varchar(20) default NULL,
  PRIMARY KEY  (`chief_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `tab10`(`chief_id`,`val1`) values (2,'a');
insert  into `tab10`(`chief_id`,`val1`) values (4,'b\"c');
insert  into `tab10`(`chief_id`,`val1`) values (6,'d\r\ne');

select * from tab10 into outfile  'C:\\tab10.csv'  fields escaped by '"' terminated by ',' optionally enclosed by '"' lines terminated by '\r\n';

truncate table tab10;

load data local infile 'C:\\escape.csv' into table `tab10` fields escaped by '"' terminated by ',' optionally enclosed by '"' lines terminated by '\r\n';

show warnings;
/*
Level      Code  Message                                                     
-------  ------  ------------------------------------------------------------
Warning    1366  Incorrect integer value: 'e"' for column 'chief_id' at row 4
Warning    1261  Row 4 doesn't contain data for all columns  
*/

select * from tab10; -- last row is truncated before <newline> 

/*
chief_id  val1  
--------  ------
       0  (NULL)  -- what is this?
       2  a     
       4  b"c   
       6  d       -- last row is truncated before <newline>
*(

Suggested fix:
two issues

1) an empty row with values (0,NULL) is created
2) truncation issue: because there is one " (doublequote) too much after 'd'!

A customer tells that the escapes etc. here are required to import the data into a spreadsheet program. Issue 2) prevents that import to that program.

Additionally SELECT INTO OUTFILE and LOAD DATA INFILE should work together with MySQL server and recreate data exactly!
[26 Oct 2008 9:33] Sveta Smirnova
Thank you for the report.

Verified as described.
[26 Oct 2008 12:26] Peter Laursen
Now as 4.1(.22) is also affected and 4.1.x is out of active maintenaince, am I right in assuming that there will never be released updated 4.1 binaries with a fix for this?
[30 Oct 2008 17:55] MySQL Verification Team
There is a nice workaround for this bug. Escape and termination string should be choosed in such a manner that this bug would be avoided.
[30 Oct 2008 18:02] Peter Laursen
Please note what I wrote:

"A customer tells that the escapes etc. here are required to import the
data into a spreadsheet program."  So there is absolutely *no option* to select escape, delimiter and enclose character otherwise! 

BTW: Refer to: 
http://www.rfc-editor.org/rfc/rfc4180.txt
[3 May 2023 10:47] MySQL Verification Team
Bug #110815 marked as duplicate of this one.