Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Not posting as an answer b/c I don't know. But it seems like something you could easily test and report back on. –  atxdba Feb 16 '12 at 4:44

2 Answers 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

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.