📋

Description

MariaDB Error 1301 indicates that a data operation, such as a query result or a statement, generated data larger than the server's configured `max_allowed_packet` limit. As a result, MariaDB truncated the data to fit, which can lead to data loss or unexpected application behavior.

💬

Error Message

Result of %s() was larger than max_allowed_packet (%ld) - truncated

🔍

Known Causes

3 known causes

⚠️

Large Data Transfer Operations

The application attempted to send or receive a single SQL statement, result set, or data block (e.g., `INSERT`, `UPDATE`, `SELECT` with large BLOBs/TEXT) that exceeded the `max_allowed_packet` limit.

⚠️

Stored Function Output Exceeded Limit

A user-defined or built-in stored function or procedure returned a value or result set larger than the `max_allowed_packet` variable allowed, leading to truncation.

⚠️

Insufficient `max_allowed_packet` Setting

The `max_allowed_packet` system variable on the MariaDB server is configured to a value that is too low for the common data sizes handled by the application or specific queries.

🛠️

Solutions

3 solutions available

1. Temporarily Increase max_allowed_packet for Current Session easy

Quickly resolve the issue for the current connection without server-wide changes.

1

Connect to your MariaDB server using a client (e.g., `mysql` command-line client).

mysql -u your_user -p

2

Set the `max_allowed_packet` variable for the current session to a larger value. A common starting point is 64MB (64M).

SET SESSION max_allowed_packet = 64 * 1024 * 1024;

3

Re-run the query or operation that caused the error.

your_original_query_here;

2. Permanently Increase max_allowed_packet in my.cnf medium

Make a persistent change to the server's configuration to avoid future occurrences.

1

Locate your MariaDB configuration file. Common locations include `/etc/my.cnf`, `/etc/mysql/my.cnf`, or files within `/etc/mysql/conf.d/`.

ls -l /etc/my.cnf /etc/mysql/my.cnf /etc/mysql/conf.d/

2

Edit the configuration file using a text editor with superuser privileges (e.g., `sudo nano`, `sudo vi`).

sudo nano /etc/my.cnf

3

Add or modify the `max_allowed_packet` setting under the `[mysqld]` section. Increase the value as needed. For example, to set it to 128MB:

[mysqld]
max_allowed_packet = 128M

4

Save the changes and exit the editor.

5

Restart the MariaDB service for the changes to take effect.

sudo systemctl restart mariadb

6

Verify the new setting by connecting to MariaDB and checking the variable.

SHOW VARIABLES LIKE 'max_allowed_packet';

3. Optimize Data Transfer or Query Structure advanced

Address the root cause by reducing the size of the data being transferred.

1

Analyze the query or operation that is generating the large packet. Identify if it's a large `INSERT` or `UPDATE` with large data (e.g., BLOBs, TEXTs), or a complex `SELECT` returning many rows or large columns.

EXPLAIN your_query_here;

2

If dealing with large data insertions/updates, consider breaking down the operation into smaller chunks. This can involve processing data in batches.

For example, instead of inserting millions of rows at once, insert them in batches of 1000 or 10000.

3

If the issue is with `SELECT` statements, review the columns being selected. Only select the necessary columns and consider if large BLOB or TEXT fields are always required in the result set.

SELECT col1, col2, SUBSTRING(large_text_col, 1, 100) AS truncated_text FROM your_table;

4

For stored procedures or functions that return large result sets, evaluate if the output can be processed differently or if the intermediate data structures can be managed more efficiently.

Consider using temporary tables or cursors more strategically if applicable.

🔗

Related Errors

5 related errors

MariaDB Error 1301: Packet Size Exceeded Truncated