I was recently migrating some data from one application to another and came across a stumbling block in MySQL. If you are trying to run a large SQL file backup restoration and the source file is quite large you may encounter the error : 'Packet too large' or 'MySQL server has gone away'. This is cuased by a MySQL server memory error.

If you look in the mySQLserver installation path directory you should see a 'my.ini' file. This controls the default settings for SQL server when you start the service.

Add in a line of code like this:

under the value of [mysqld]. Save the file and then restart mySQL server.

You should now be able to run:

show variables like 'max_allowed_packet';

And have it return your ini value.

As long as the value you have set it to is greater than the file size it should run successfully.

You can also run it as a set session variable command in your SQL environment, like this:

This will set the same value for that instance of the sql server.

Update:

There also seems to be a massive difference in performance in running the imports directly into a GUI environment, or through a shell command. I've had a 600mb file fail several times in the GUI, but running:

It has imported within around 10 to 15 minutes.