How to set the maximum data packet size with MariaDB max_allowed_packet

MariaDB max_allowed_packet is a system variable that defines the maximum size a single data packet can be during communication between the database server and an application. Without adjusting this limit, large file transfers, bulk inserts or long SQL commands can quickly fail.

What is MariaDB max_allowed_packet?

The max_allowed_packet system variable is a key configuration setting in both MariaDB and MySQL. It specifies the largest single data packet that a client, such as an application, tool or SQL script, can send to or receive from the MariaDB server. The default value depends on the operating system, distribution and MariaDB version.

A data packet in MariaDB contains SQL statements and related content exchanged between the client and server. If a command — for instance, a large INSERT statement with many values or a BLOB (Binary Large Object) — exceeds the allowed size, the server rejects it. In such cases, you’ll often see the error:

ERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet' bytes

This limit prevents oversized requests from consuming excessive resources or destabilizing the server. At the same time, administrators can adjust it to suit their workload.

Both the server and the client have their own max_allowed_packet value. For a transfer to succeed, both sides must allow packets of the required size. For example, if the server is set to 64 MB but the client is limited to 16 MB, any packet over 16 MB will still fail.

What does the syntax for MariaDB max_allowed_packet look like?

You can change max_allowed_packet either temporarily or permanently. The syntax depends on the scope of the adjustment:

Temporary change for the current session

To change the value only for the active connection, set it at the session level:

SET SESSION max_allowed_packet = 67108864;

This increases the limit to 64 MB, but only for the current client connection.

Global change at runtime

To apply the change for all new connections until the server restarts, set it globally:

SET GLOBAL max_allowed_packet = 67108864;

This updates the limit for all future connections, but existing sessions remain unchanged.

What are some typical use cases for max_allowed_packet?

In many scenarios, the default value of max_allowed_packet is too low. Setting it higher can prevent errors and interruptions, especially in data-heavy operations. The following use cases show where an adjustment is advisable:

• Importing large SQL dumps: Restoring backups or dumps with substantial amounts of data often requires a higher packet size to avoid failures.
• Processing large BLOBs: Storing binary files such as PDFs, images or archives in the database often exceeds the default limit.
• Bulk INSERTs with many values: ETL processes or mass uploads that insert large datasets in a single statement can trigger the packet limit.
• Web applications with large forms or uploads: Frameworks that automatically generate large SQL statements often hit the packet limit without adjustment.

Avoid setting max_allowed_packet unnecessarily high, as it increases memory usage and can impact stability. For most production environments, a value between 16M and 64M is sufficient. Higher values are typically only needed for tasks like migrations.

Example use of max_allowed_packet

Suppose your web application allows users to upload PDF files up to 64 MB into the database. If the default is 4 MB, the upload will fail. To ensure reliability, you’ll need to increase the value:

On Linux, open the file /etc/mysql/my.cnf or my.ini if you are using Windows. Under [mysqld], add the following entry:

max_allowed_packet=64M

Restart the MariaDB server:

sudo systemctl restart mariadb

Check the new value:

SHOW VARIABLES LIKE 'max_allowed_packet';

The expected output:

+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_allowed_packet  | 67108864 |
+---------------------+----------+

Your application can now handle data packets up to 64 MB without errors, improving stability and allowing smooth operation even with larger transfers.