Problem: in MySQL, you can only send data that is less than max_allowed_packet.
Solution: increase max_allowed_packet.
That's all well and good, and works, but for safety's sake we want to keep its size down, but occasionally we might need to save some data that is far larger than what we keep it at.
I thought, no problem: just set max_allowed_packet session variable in the current session, then send the data, then set it back (or just allow it to reset when the session finishes).
Problem: it seems the client library sets that value on initialization, and changing it in the server via a SET $var = $value doesn't change anything in the client, so you still hit the limit.
Solution: break the data down into chunks, and add it one chunk at a time, with UPDATE table SET data=CONCAT(data, $chunk).
That's all well and good, but ...
Problem: max_allowed_packet doesn't merely prevent you from sending data of a certain size: it also governs the size of an "expanded" query. So if data is 1MB and $chunk is 512K, you still hit a 1MB max_allowed_packet limit with the CONCAT(...).
Solution: increase max_allowed_packet.
Aha! I can't increase max_allowed_packet on the client side, but I can increase it on the server side. This doesn't help me send the data, but it helps me save it on the server side. So to get past the client limitation I do it in chunks, and to get past the server limitation I still need to set the session variable.
It all worked fine for a file that was 1.3MB where the limit was 1MB. But on a file that was over 30MB, it saved the file fine, but it wouldn't fetch it. update This is because of a problem saving the data to an InnoDB table, where the log group capacity is not large enough. Whatever that means.
Re:Be careful with replication, too
pudge on 2005-02-04T02:52:13
Yeah, I know. But thanks for the reminder.:-)
