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: | |
| 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 | ||
[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.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.056 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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!