MySQL - Error 2013: Lost Connection to MySQL server during Query - Localhost

Question

I just installed an SQL server on my computer and pointed the data storage towards a other non-C drive. I have been attempting to import Kaggle data as a test after creating a table via:

LOAD DATA LOCAL INFILE 'G:\\Extract_large\\user_logs.csv'
INTO TABLE user_logs
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
ignore 1 rows; 

Naturally for the data is fairly large and during each attempted import I am consistently losing connection to the server via Error Code: 2013 Lost connection to MYSQL server during Query. I did some searching and came across a few other similar threads in stackoverflow (not specifically for localhost) which lead to me increase the DBMS Connection parameters, specifically I set:

DBMS connection Keep-alive interval (in seconds): 10,000
DBMS connection read timeout (in seconds): 200
DBMS connection timeout (in seconds): 10,000

This did not resolve the issue, so I further edited the my.ini information to set max_allowed_packet=100000M. Once I restarted the server, this still did not work.

Finally, I tried set global max_allowed_packet=500000000000 in SQL. Again with no success. I am out ideas... Help?

Here are the other threads I managed to find useful on this topic: here,here, and here.

Note: I am running Windows 7 with MySQL 5.7. The error seems to occur at either 30.5 or 1573.6 seconds (give or take a few milliseconds).

Answer 1

The largest permitted value for max_allowed_packet is 1G according to https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_allowed_packet

I tried a test with MySQL 5.6.31 to change it to 500G as you did, but the change is ignored, and the previous value is still in effect.

Also note that in addition to changing the server-side max_allowed_packet, you may need to change the option of the same name in some clients, specifically mysql and mysqldump. Other command-line clients use the client default of 1GB.

So if you're running LOAD DATA using the mysql client, you need to tell the client to use a larger packet size.

mysql --max_allowed_packet=1073741824 ...