How to Change the max_allowed_packet Size in the MySQL Server

1. Change the max_allowed_packet Size in the MySQL Server
2. Change the max_allowed_packet Size in the MySQL Server Using Windows OS
3. Change the max_allowed_packet Size in the MySQL Server Using Ubuntu OS

This tutorial educates how to change the max_allowed_packet size in the MySQL server. To learn that, we will be using two operating systems, Windows 10 and Linux (Ubuntu).

Change the max_allowed_packet Size in the MySQL Server

If we try to upload files bigger than the default value of max_allowed_packet, we get an error saying Packets larger than max_allowed_packet are not allowed.

To eliminate this error, we need to change the size of max_allowed_packet. But before that, let’s check its default value as follows.

mysql> SHOW VARIABLES LIKE 'max_allowed_packet';

Output:

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

Right now, the size of max_allowed_packet is 4MB, equal to 4194304 Bytes. Considering the MySQL Docs, MySQL Client and Server have their own max_allowed_packet size.

The value we see above using the SHOW VARIABLES LIKE 'max_allowed_packet'; query is the value on the MySQL Server side. It is necessary to increase the value of max_allowed_packet if we want to handle the bigger packets.

Suppose we want to change it to 50MB. We can do that by updating the configuration file on the server-side (a section named [mysqld] in the my.ini file ) as well as on the client-side (a section named [mysql] or [client] in the my.ini file).

We can also change this setting using an SQL query if we have a SUPER privilege (permission). How? Let’s see both solutions below.

Change the max_allowed_packet Size in the MySQL Server Using Windows OS

1. Open the Windows Command Line and navigate the installation path. MySQL Server is installed at C:\Program Files\MySQL\MySQL Server 8.0 if you didn’t change the default location.

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 getting in as a root user; you may use your username and password.

4. Once we are in, 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 change.

   mysql> SHOW VARIABLES LIKE 'max_allowed_packet';
   

   Output:

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

The solution given below is using the configuration file located at the default path if you have installed MySQL Server at the default location. The configuration file’s 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 one following line under this section.

   max_allowed_packet=50M
   

3. Save and close the file.

4. Restart the MySQL server to view the change.

Change the max_allowed_packet Size in the MySQL Server Using Ubuntu OS

After getting into MySQL Server, we can use the same queries on Ubuntu that we used for Windows OS. The steps are given below.

1. Open Ubuntu Terminal, and use sudo su to log in as a superuser.

2. Further, log in to MySQL Server.

3. It is good to check the default or previous value of a variable before making any changes. For that, 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)
   

4. 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;
   

5. We can run the SHOW VARIABLES LIKE 'max_allowed_packet'; query to confirm if the change happens.

If you are the one who is very comfortable editing the configuration files, the following solution is particularly for you.

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

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

2. Search for the max_allowed_packet under a section named [mysqld] and change its value to your choice. If it is not there, add the following line under [mysqld].

   Remember, we are changing it to 70M. But, you may write your number.

   max_allowed_packet=70M
   

3. Save and exit the file.

4. Use systemctl restart mysql to restart the MySQL Server and execute the following 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)