Nicolas Le Manchet

MySQL got a packet bigger than max_allowed_packet

MySQL server has a setting called max_allowed_packet that basically limits the size of each query. The default value varies but can be as low as 4 MB, meaning that trying to fill a mediumtext column, which can support up to 16 MB, will fail.

The current value in bytes can be retrieved by the client by executing:

show variables where Variable_name = 'max_allowed_packet';

Unfortunately when receiving a query that is too big, the MySQL server will randomly either:

This means that in Python, the client application needs to correctly handle both:

pymysql.OperationalError((1153, "Got a packet bigger than 'max_allowed_packet' bytes"))
pymysql.OperationalError((2006, "MySQL server has gone away (BrokenPipeError(32, 'Broken pipe'))"))

This is problematic because the BrokenPipeError can happen for other reasons than a query that is too big, which means that the application cannot immediately identify the issue.

The documentation suggests that there is no real downside to setting max_allowed_packet to its maximum value of 1 GB on the server. Some clients also need to be aware of this change, so changing it in both places is recommended, however pymysql does not seem to care.