Does the max_allowed_packet variable have any effect on LOAD LOCAL INFILE?

As the question states, what (if any) is the effect of max_allowed_packet on LOAD LOCAL INFILE?

Does setting it to a large value load data files of large size such as 700MB into the db quickly?

LOAD DATA INFILE can be thrown off if read_buffer_size is larger than max_allowed_packet. This is the case when it comes to replication.

See the following bug reports

• http://bugs.mysql.com/bug.php?id=15937

• http://bugs.mysql.com/bug.php?id=30435

• http://lists.mysql.com/mysql/168070

Outside of MySQL Replication, I can't really tell.

One thing is for sure: Whatever mysql had as the setting for max_allowed_packet on a server that performed SELECT ... INTO OUTFILE, the server into which you import data via LOAD DATA INFILE better have the same setting for max_allowed_packet or greater.

The max value for max_allowed_packet is 1G. It couldn't hurt to just set that in my.cnf.

[mysqld]
max-allowed-packet=1G

You do not have to restart mysql. Just run this:

SET GLOBAL max_allowed_packet = 1024 * 1024 * 1024;

then you can perform the LOAD DATA INFILE.

StackExchange Database Administrators Q#13160, answer score: 2