JIYIK CN >

This article explains how to change the max_allowed_packet size in MySQL server. To understand this, we will use two operating systems, Windows 10 and Linux (Ubuntu).

Changing max_allowed_packet Size in MySQL Server

If we try to upload a file larger than the default value of max_allowed_packet, we will receive an error message stating that packets larger than max_allowed_packet are not allowed.

To get rid of this error, we need to change the size of max_allowed_packet. But before that, let's check its default value as shown below.

mysql> SHOW VARIABLES LIKE 'max_allowed_packet';

Output:

+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+
1 row in set (0.06 sec)

Now, the max_allowed_packet size is 4MB, which is equal to 4194304 bytes. Considering the MySQL documentation, the MySQL client and server have their own max_allowed_packet size.

We saw the value above using SHOW VARIABLES LIKE 'max_allowed_packet'; query is the value on the MySQL Server side. If we want to handle larger packets, it is necessary to increase the max_allowed_packet value.

Let's say we want to change it to 50MB. We can do this by updating the configuration files on the server side (the section of the my.ini file named [mysqld]) and the client side (the section of the my.ini file named [mysql] or [client]).

If we have the SUPER privilege (permission), we can also change this using SQL queries. How? Let's look at the two solutions below.

Changing the max_allowed_packet size in MySQL server using Windows operating system

1. Open a Windows command line and browse to the installation path. If you did not change the default location, MySQL Server is installed at C:\Program Files\MySQL\MySQL Server 8.0.
2. Go to the bin folder using cd bin.
3. Type mysql -u root -p password to log in to the MySQL server. We are logging in as the root user; you can use your username and password.
4. Once inside, execute the following query to change the max_allowed_packet size in MySQL server.

   mysql> SET GLOBAL max_allowed_packet=52428800;
   

5. Execute the following query again to confirm the changes.

   mysql> SHOW VARIABLES LIKE 'max_allowed_packet';
   

   Output:

   +--------------------+---------+
   | Variable_name      | Value   |
   +--------------------+---------+
   | max_allowed_packet | 52428800|
   +--------------------+---------+
   1 row in set (0.00 sec)
   

If you have installed MySQL server in the default location, the solution given below is to use the configuration file located in the default path. The configuration file path is C:\ProgramData\MySQL\MySQL Server 8.0\my.ini.

1. Open the my.ini file.
2. Search for the [mysqld] section and add the following line under this section.

   max_allowed_packet=50M
   

3. Save and close the file.
4. Restart the MySQL server to see the changes.

Changing max_allowed_packet size in MySQL server using Ubuntu OS

Once inside the MySQL server, we can use the same queries on Ubuntu as we do on Windows OS. The steps are as follows.

1. Open the Ubuntu terminal and log in as superuser using sudo su.
2. Also, log in to the MySQL server.
3. It is always a good idea to check the default or previous value of a variable before making any changes. To do this, we can use the following query.

   mysql> SHOW VARIABLES LIKE 'max_allowed_packet';
   

   Output:

   +--------------------+---------+
   | Variable_name      | Value   |
   +--------------------+---------+
   | max_allowed_packet | 67108864|
   +--------------------+---------+
   1 row in set (0.00 sec)
   

   Execute the following query to update the value of max_allowed_packet to 70MB, which is 73400320 bytes.

   mysql> SET GLOBAL max_allowed_packet=73400320;
   

4. We can run the SHOW VARIABLES LIKE 'max_allowed_packet'; query to confirm that the change occurred.

If you are someone who is very good at editing configuration files, then the following solution is especially for you.

1. Open the configuration file located in the /etc/mysql/mysql.conf.d/mysqld.cnf path.

   $ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
   

2. Under the section named [mysqld] search for max_allowed_packet and change its value to your choice. If it does not exist, add the following line under [mysqld].

   Remember, we are changing it to 70M. However, you can write down your phone number.

   max_allowed_packet=70M
   

3. Save and exit the file.
4. Restart MySQL Server using systemctl restart mysql and execute the following command to ensure the update.

   mysql> SHOW VARIABLES LIKE 'max_allowed_packet';
   

   Output:

   +--------------------+---------+
   | Variable_name      | Value   |
   +--------------------+---------+
   | max_allowed_packet | 73400320|
   +--------------------+---------+
   1 row in set (0.03 sec)