Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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
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
Apr 21, 2011 : What does the MySQL “max_allowed_packet” setting actually control?Apr 27, 2011 : Changed max_allowed_packet and still receiving 'Packet Too Large' error Aug 01, 2011 : How does max_allowed_packet affect the backup and restore of a database?Feb 16, 2012 : Does the max_allowed_packet variable have any effect on LOAD LOCAL INFILE?Aug 14, 2012 : "Mysql has gone away" heavy multiple insertJul 03, 2013 : What max_allowed_packet is big enough, and why do I need to change it?
