MariaDB: max_allowed_packet explained – IONOS

MariaDB max_allowed_packet is a system variable that indicates the maximum size that a data packet can reach when communicating between the database and the application. Users transferring large files, many records, or long SQL commands quickly run into limitations.

What is MariaDB max_allowed_packet ?

The system variable max_allowed_packet is one of the most important configuration settings in MariaDB and MySQL when it comes to the stability and reliability of data transfers. It defines the maximum size of a single data packet that a client (for example an application or SQL script) sends to or receives from a MariaDB server. The preconfigured default varies by system, distribution, or MariaDB version.

A data packet in MariaDB includes SQL statements and content exchanged by the server or client via the network. If an SQL command like INSERT with many values ​​or a BLOB (Binary Large Object), exceeds this maximum size, the server refuses execution. In such cases, the following error message often appears:

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

This setting protects the server from oversized requests that can potentially overload resources or cause instability. It also allows administrators to adjust the allowed size to the requirements of their environment.

Both the server and the client each have their own value max_allowed_packet. Transmission only works if both parties accept packets of the defined size. So, if the server is configured to 64 MB, but the client to only 16 MB, sending a packet exceeding 16 MB may fail.

MariaDB command syntax max_allowed_packet

MariaDB allows both temporary and permanent adjustments of max_allowed_packet. The syntax varies depending on usage:

Change temporarily max_allowed_packet for a session

If you want to adjust the value of max_allowed_packet only for a single connection, change it temporarily at the session level.

SET SESSION max_allowed_packet = 67108864;

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

To change max_allowed_packet for the entire running server

To adjust max_allowed_packet on the entire server, set the value globally.

SET GLOBAL max_allowed_packet = 67108864;

So MariaDB sets the value for all future connections. Existing sessions remain unchanged. The setting remains until the server is restarted, and is suitable for permanent adjustments.

Typical use cases of max_allowed_packet

In many scenarios, the default value of max_allowed_packet quickly reaches its limits. Especially during data-intensive processes or automated operations, a setting that is too low can lead to errors or failures. The following cases represent typical situations where an adjustment may be useful:

• Importing large SQL files : When you restore a backup or database dump containing large amounts of data, the default configuration can quickly become insufficient. A higher value prevents interruptions.
• Processing large BLOBs : Applications storing binary files (e.g. PDFs, images, or ZIP archives) in the database often require a value max_allowed_packet bigger.
• Many values ​​in INSERT : When large amounts of data are written in a single instruction, such as during ETL processes or bulk downloads, the packet size increases accordingly.
• Web apps with dynamic forms or uploads : Frameworks that automatically generate large SQL statements often hit the packet limit without adjustment.

Don't increase unnecessarily max_allowed_packet in MariaDB, as it may affect memory consumption and stability. A value of 16M has 64M is sufficient for most productive applications. Only special cases, such as database migrations, require larger values.

Example of use of max_allowed_packet

Suppose you operate a web application that allows users to upload PDF files up to 64 MB into the database. A standard value set to 4 MB is not enough. To operate the application reliably, adjust the value.

Open the file /etc/mysql/my.cnf under Linux or my.ini if you are using Windows. Add the following entry under [mysqld] :

max_allowed_packet=64M

Restart the MariaDB server:

sudo systemctl restart mariadb

Check the new value:

SHOW VARIABLES LIKE 'max_allowed_packet';

We obtain the following result:

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

The application can now process data packets up to 64 MB without problems. This prevents errors, improves stability and enables smooth operation even with larger data transfers.