Sometimes, I can see that there are some misleading guides of max_allowed_packet in the net. In MySQL Reference Manual, it is saying like this.
The maximum size of one packet or any generated/intermediate string, or any parameter sent by the mysqlstmtsendlongdata() C API function. The default is 64MB.
The parameter is basically about the maximum size of a single packet or generated/intermediate string. And most of DBAs know that we should care about the parameter when dealing with LOB type.
By the way, is the constraint related to READ operation or WRITE operation, or both?
Simply put, it is only applied to WRITE operation. When you execute a SELECT query that returning large size of data, the large data is split and passed to the client in multiple times if the data size exceeds the defined packet size. This is the reason why READ operation doesn’t have to worry about it.
Here is a simple test scenario to check the behaviour. To do this, we need 2 clients — one has small size of max_allowed_packet, and the other has large size of max_allowed_packet. Let me name the small one as session S and the large one as session L.
Firstly, set max_allowed_packet in global and open a new client.
# Session S
-- max_allowed_packet,67108864
SHOW VARIABLES LIKE 'max_allowed_packet';
SET GLOBAL max_allowed_packet = 1*1024*1024*1024;
-- max_allowed_packet,67108864
-- still has default value because this session was established before changing the session variable
SHOW VARIABLES LIKE 'max_allowed_packet';# Session L
# After finishing the commands above, create a new session
-- max_allowed_packet,1073741824
SHOW VARIABLES LIKE 'max_allowed_packet';
Secondly, create a test table and put some data.
# Session S
CREATE TABLE T1 (A LONGTEXT);
-- small data, succeed
INSERT INTO T1 VALUES (REPEAT('가', 10000000));
-- large data, fail
-- [HY000][1301] Result of repeat() was larger than max_allowed_packet (67108864) - truncated
INSERT INTO T1 VALUES (REPEAT('가', 100000000));The error is expected. Let’s do the failed query in the session L.
# Session L
-- large data, succeed
INSERT INTO T1 VALUES (REPEAT('가', 100000000));Okay, it’s good. We actually set huge size of max_allowed_packet so that the insertion succeeds in the session. Let’s check the size of the two rows.
# Session L
-- 30000000
-- 300000000
SELECT LENGTH(A) FROM T1;Lastly, select all the data in both sessions. If max_allowed_packet is also related to READ operation, the query in session S will fail. If not, if my assumption is true, the query in both sessions will succeed. Let’s check it out.
# Session S
SELECT * FROM T1;# Session L
SELECT * FROM T1;
The query in both sessions actually work very well, which means we only need to care about the parameter when we insert large object, not when reading.
