MySQL Bugs: #70082: Bad Allocation eror when importing CSV data

Bug #70082	Bad Allocation eror when importing CSV data
Submitted:	19 Aug 2013 6:32	Modified:	24 Mar 2014 15:56
Reporter:	James Dickson	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Workbench	Severity:	S1 (Critical)
Version:	5.2.47 CE	OS:	Windows (Win 7 Pro SP1 64bit)
Assigned to:		CPU Architecture:	Any
Tags:	Apply SQL statements, Bad Allocation, csv, error, import

Description:
I have created some data for import in CSV format for import into MySQL. there are 36751 rows in the import file, a typical line is represented below...

ID, CATEGORY, DATE, FILEREF, TEXTFROMFILE
384,1,0000-00-00 00:00:00,A-2_2-2.pdf,"[TEXT CONTENT - MULTIPLE LINES]"

when i select the table and import CSV, after a very long wait (1 hour plus), the data to import appears. When i choose apply, i get 36751 SQL statements but as soon as i run them, i get a bad allocation error.

Now taking the same 36751 line CSV file and splitting it into 4 roughtly equaly pieces and importanting and appying 4 times does not clause the error and the same data is imported.

How to repeat:
Happens every time with the data set i have. I can email CSV files to allow a test at your end.

Suggested fix:
i presume this is a memory problem and that aftr a large import from file, not enough memory is freed to allow the SQL statements to run. As the problem is prevented by splitting the import file and repeating the process on smaller files, memory handling is suspected.

CSV file is 80Mb, so can send by yousendit or similar only idf needed.

I have a very old report here also involving multiline strings with LOAD DATA:
http://bugs.mysql.com/bug.php?id=40320

Thank you for the bug report. Please provide the files (private if you wish) Thanks.

@Peter Laursen 

thanks for the report, i checked and it is not apparently relevant to my issue. The import of 36,000 rows of multi line data works perfectly if i split the original 36,000 line CSV into 4 files of 9000 rows each and then import 4 files in sequence into MySQL. Doing it in one go is the issue, but i can get it to work on the same data in chunks, hence the bug report. Later, i have a lot more data to import, so while i can work around for now, it will be an issue later.

regards

James

@Godofredo 

thanks, i have created a .rar file with the orignal CSV and one of the 4 chunks that works fine. I am having a few issues with your FTP, i can see it, but cannot login as not asked for credentials. i will load an FTP client onto my machine and try again tomorrow.

regards

James

What's the file size in bytes? And where do you do the import? In a model or a SQL connection?

Hi @Alfredo 

The CSV file is 81,135 Kb and 36,751 lines (records) of data.

I am trying to import in a connection, in the SQL editor. After selecting my table and browsing the first 1000 rows, a screen control appears above the tabular data (grid with green arrow pointing up: "Import records from an external file"). After waiting an age for the file to process, i then get a screen with 36,751 SQL statements and the instant i press apply, the error messge "bad allocation" appears. 

importing the exact same data in 4 smaller chunks does not result in the error, thus i know the data is fine and suspect memory management is the cause. the reference data field contains ASCII text and is up to 12,000 characters in size, hence the size of the CSV.

hope this helps,

regards

James

Please try version 6.0.9 or 6.1.1. Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".