Error
Error Code:
2020
MySQL Error 2020: Packet Size Exceeded
Description
This error occurs when either the MySQL client or server attempts to send or receive a data packet that is larger than the configured 'max_allowed_packet' setting. It typically indicates an issue with transferring large amounts of data, such as during large inserts, updates, or when fetching extensive query results.
Error Message
Got packet bigger than 'max_allowed_packet' bytes
Known Causes
4 known causesSending Large Queries/Data
A client application attempts to send a SQL query or data (e.g., multi-row INSERTs, large BLOB/TEXT updates) whose size exceeds the 'max_allowed_packet' limit on either the client or server.
Receiving Large Result Sets
The MySQL server tries to send a result set back to the client that is larger than the 'max_allowed_packet' configured on either the server or the client side.
Insufficient 'max_allowed_packet'
The default or currently configured 'max_allowed_packet' value on either the client or server is too low for the application's data transfer requirements.
Client/Server Mismatch
The 'max_allowed_packet' settings between the client application and the MySQL server are inconsistent, causing one side to reject packets from the other.
Solutions
4 solutions available1. Temporarily Increase max_allowed_packet for the Current Session easy
Adjust the 'max_allowed_packet' setting for the current MySQL client session.
1
When connecting to MySQL using the command-line client, use the --max_allowed_packet option to specify a larger value for this session only. Replace `[your_value]` with the desired size in bytes (e.g., 64M for 64 megabytes).
mysql --max_allowed_packet=64M -u your_user -p your_database2
If you are using a GUI tool like MySQL Workbench, look for connection settings that allow you to specify 'Max Packet Size' or 'max_allowed_packet'. Enter the desired value (e.g., 64M).
2. Modify the MySQL Server Configuration File medium
Permanently increase the 'max_allowed_packet' setting by editing the MySQL server's configuration file.
1
Locate your MySQL server's configuration file. Common locations include `/etc/my.cnf`, `/etc/mysql/my.cnf`, or `/etc/mysql/mysql.conf.d/mysqld.cnf`.
2
Open the configuration file with a text editor. You'll likely need root or administrator privileges.
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf3
Find the `[mysqld]` section. If it doesn't exist, create it. Add or modify the `max_allowed_packet` directive. Set it to a value larger than the packet size you are encountering. For example, to set it to 128MB:
[mysqld]
max_allowed_packet = 128M4
Save the changes to the configuration file and exit the editor.
5
Restart the MySQL server for the changes to take effect.
sudo systemctl restart mysql3. Adjust max_allowed_packet Dynamically (Requires SUPER Privilege) medium
Change the 'max_allowed_packet' setting for the running MySQL server without restarting.
1
Connect to your MySQL server as a user with the `SUPER` privilege. This is typically the `root` user or a user with equivalent permissions.
mysql -u root -p2
Execute the `SET GLOBAL` command to change the `max_allowed_packet` value. Replace `[your_value]` with the desired size in bytes (e.g., 128M). This change will persist until the MySQL server is restarted.
SET GLOBAL max_allowed_packet = 128M;3
To make this change permanent across server restarts, you will still need to update the MySQL server's configuration file as described in Solution 2.
4. Optimize Data Transfer or Query medium
Reduce the size of the data being sent or received by the MySQL server.
1
If you are inserting or updating large amounts of data, consider breaking it down into smaller batches. Instead of one large `INSERT` or `UPDATE` statement, use multiple smaller ones.
2
If you are retrieving large result sets, review your `SELECT` queries. Only select the columns you need and consider adding `LIMIT` clauses if appropriate. Avoid `SELECT *` on tables with many columns or rows if you don't need all the data.
3
For large BLOB or TEXT data, consider if the entire data needs to be transferred in a single packet. Sometimes, it's more efficient to store such data in separate files and reference them, or process them in chunks.