Cannot import large CSV file in MariaDB

Question

I have a large CSV file that's 100G and has 180 million rows. I need to import this file into MariaDB.

I used mysqlimport, and after three hours of running I get error 2013. If I run a select on the table after the error is thrown, the table is empty.

How to upload a very large file in MariaDB? How to fix this problem?

C:\Users\pgsch\Downloads>mysqlimport --verbose --local -u user01 -p bank_0001_test fm_loans fm_loans  --fields-terminated-by="|"
Enter password: ********
Connecting to localhost
Selecting database bank_0001_test
Loading data from LOCAL file: C:/Users/pgsch/Downloads/fm_loans into fm_loans
mysqlimport: Error: 2013, Lost connection to MySQL server during query, when using table: fm_loans

increase **max_allowed_packet** & **interactive_timeout** variables and try. — ROHIT KHURANA, May 10 '21 at 05:06
The command I'm running is a windows program, can I use SET as a windows environment variable? Do I have to use the mysql sheel to set these variables? — ps0604, May 10 '21 at 12:42
yes, on you need to login on MySQL shell and set parameter globally and start importing. — ROHIT KHURANA, May 11 '21 at 03:37

Vishal K · Accepted Answer · 2021-05-10T05:49:41.980

There are three likely causes for this error message:

Usually it indicates network connectivity trouble and you should check the condition of your network if this error occurs frequently
Sometimes the “during query” form happens when millions of rows are being sent as part of one or more queries.
More rarely, it can happen when the client is attempting the initial connection to the server

For more details read

Cause 1 : set global max_allowed_packet=101000000000; (101 GB) Set maximum allowed packet size to a large byte number.The default value may throw errors for such large data files.

Cause 2 : SET GLOBAL interactive_timeout=60; from its default of 30 seconds to 60 seconds or longer

Cause 3 : SET GLOBAL connect_timeout=60;

Additional reference:

The file is local, not on the network. Also, the command I'm running is a windows program, can I use SET as a windows environment variable? — ps0604, May 10 '21 at 12:41

Cannot import large CSV file in MariaDB

1 Answers1