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

Question

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?

Answer 1

There is no constraint like "the loaded file must be < max_allowed_packet".

You can load a GBs file.

Depending on the size of the rows, you'll probably exceed this.One way would be to split it up into several chunks. This is probably a good idea anyway.

LOAD DATA isn't subject to the max_allowed_packet limit.

If you are adjusting your max_allowed_packet (default is 1M).

Set it as

  SET GLOBAL max_allowed_packet= 128 * 1024;

otherwise you may face the issue like

ERROR 1153 (08S01) at line : Got a packet bigger than 'max_allowed_packet' bytes 

If you face the above error set max_allowed_packet to some higher value.

Otherwise in my opinion increasing it to some higher value will not allow you to quickly load a 700MB file.

Answer 2

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.