MySQL Error when importing LARGE files.

<quote> 
   Hi,

   I got the MySQL Error Nr.2006-MySQL server has gone away when trying 
   to restore a 580MB file with mySQL administrator 1.1.9. However when
    I set the max_allowed_packet=100M in the my.ini file everything works
    fine. I don't know if this helps anyone.
</quote> 

Yes Frank, years later it still helps.

I was getting a  
ERROR 2006 (HY000) at line #: MySQL server has gone away
message from MySQL and nothing in the error logs!
Very frustrating to not be able to SEE that it was hitting a limit.
 

Simply increase your max_allowed_packet while doing the import.

This will increase the limit temporarily (until mysql restarted).
<SQL>
   set global max_allowed_packet = 100*1024*1024;
   show global variables like "max_allowed_packet";
</SQL>
Note that the 100 in the SQL above is equivalent to 100M in bytes.
If you want to update your ini file to make this permanent,
 then just add this to your my.cnf
 
   max_allowed_packet = 100M 

Happy Coding!

Popular Posts