2

We have a MySQL server with multiple databases in the same instance. Can I change the max_allowed_packet value for just one of the database?

From looking at this, it seems SET GLOBAL max_allowed_packet=xx will affect the entire server while SET max_allowed_packet=xx will only affect the current session

1

According to the MySQL Documentation on max_allowed_packet

The maximum size of one packet or any generated/intermediate string.

The packet message buffer is initialized to net_buffer_length bytes, but can grow up to max_allowed_packet bytes when needed. This value by default is small, to catch large (possibly incorrect) packets.

You must increase this value if you are using large BLOB columns or long strings. It should be as big as the largest BLOB you want to use. The protocol limit for max_allowed_packet is 1GB. The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple.

When you change the message buffer size by changing the value of the max_allowed_packet variable, you should also change the buffer size on the client side if your client program permits it. The default max_allowed_packet value built in to the client library is 1GB, but individual client programs might override this. For example, mysql and mysqldump have defaults of 16MB and 24MB, respectively. They also enable you to change the client-side value by setting max_allowed_packet on the command line or in an option file.

Based on this, your only chance to set it for your connection would be when starting the client program:

mysql -Dmydb --max-allowed-packet=1073741824

After all

You cannot set it for the connection because it is a global option for how packets are used.

I just tried to run this in MySQL 5.6.15 for Windows and MySQL in CentOS release 6.5 (Final) and I got this message

mysql> set max_allowed_packet = 1024 * 1024 * 1024;
ERROR 1621 (HY000): SESSION variable 'max_allowed_packet' is read-only. Use SET GLOBAL to assign the value

Please read my posts on the MySQL Packet and its global effects

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.