Changing max_allowed_packet value in MySQL in Windows

I was recently tasked with updating a MySQL database that was using longblob fields to store binary file data. Over the years the database was getting extremely large and less efficient. That said, I needed to import a sql dump file into a development environment so I could begin extracting the data out and putting it into a file system.


When I did the import, I created a temporary database and used the "source" command to reference the file I wanted imported into the database.


Example:

create database mydatabase;
use mydatabase;
source C:/mydumpfile.sql;

When I ran the import above, I was getting "Got a packet bigger than 'max_allowed_packet'bytes" and MySQL Server would go away and restart.

The outcome was a database that was missing a good amount of records.

The solution was to go into C:/Program Files/MySQL/MySQL Server 5.1 (your version will vary here)
and edit my.ini.

I added this line to the end of my.ini and then restarted the MySQL service:

max_allowed_packet=1000M

This gave me the following output in the mysql console:

mysql> SHOW VARIABLES LIKE 'max_allowed_packet'
    -> ;
+--------------------+------------+
| Variable_name      | Value      |
+--------------------+------------+
| max_allowed_packet | 1048576000 |
+--------------------+------------+
1 row in set (0.00 sec)

After doing so I was able to run my import again without error. 1000M is obviously way too much for a production environment, use what you think is acceptable if you are using this in production.

#adsense#

Things to note:

  1. I did this in a development environment on a Windows XP machine running MySQL 5.1
  2. I made the max_allowed_packet size extremely large which is not something I'd recommend in a production environment, this was simply used to get the binary data out properly.
  3. After updating my.ini, I restarted MySQL's service by going to Control Panel > Administrative Tools > Services
Category: