Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

pudge (1)

pudge
  (email not shown publicly)
http://pudge.net/
AOL IM: Crimethnk (Add Buddy, Send Message)

I run this joint, see?

Journal of pudge (1)

Thursday February 03, 2005
06:49 PM

MySQL Packet Size

[ #23012 ]

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.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.