How to change mySql max_allowed_packet ?

Packet in MySql – Do you know why the packets are more important in MySql? The mechanism follows the simple technique – if the client or the server wants to transfer some information at times they will use the packets to send the information’s. The data’s are splits according the packet size of bytes. It has the unique header in each packet.

Why there is the limitation to set the value?

In this article, we are going to discuss about how to increase the mysql max_allowed_packet for client. When it comes to the SQL, it has only one line command which sends the information to the server. There is the limitation to transfer the packet and sometimes they would have the default variables too. Maximum you can send MySQL server or to the client with 1GB.

If at all, if the server or the client receives the packet more than the variable setup then you will have the error saying ER_NET_PACKET_TOO_LARGE or else you would dropped out from the connection saying Lost connection to MySQL server during query.

Eg: If you set the variable of max_allowed_packet = 3 and also if you run the below code;



In the above code, you have increased the packets to 7 but you have set the packets as 3 only. Once if you run the code, you will receive the error such as:

SELECT * FROM test WHERE id =3;
ERROR 2020 (HY000): Got package bigger than ‘max_allowed_packet’ bytes

SELECT CURRENT_USER();
ERROR 2020 (HY000): Mysql server has gone away
No connection. Trying to reconnect….
Connection id: 7
Current database: test

Click to expand...

Steps to increase the packet size:

You can’t further proceed if you got this type of error. You need a solution isn’t it? Yes, we are here to help you. Let we give the perfect solution to increase the MySQL packets.

In default, you would have max_allowed_packet = 16MB. To further increase the value, you have increase the packet size as:
The above code sets the value of packet to be 32M.