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:
- Return an error and close the connection.
- Just close the connection.
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.