In Which I Try to Save Someone from a Rails/MySQL BLOB Debugging Hassle

I was recently debugging an app and spent a bunch of time on a MySQL error symptom. I'd like to save the next developer from the same searches, since there are lots of documented MySQL connection issues with Rails, and few (any?) posts mentioning the following particular gotcha:

If you're getting the Lost connection to MySQL error, or its cousin, MySQL server has gone away (depending on whether you're using the Ruby driver or the Ruby-C driver), and you happen to be working with BLOBs (images, file uploads, streaming docs from a DB, etc.), there is a reasonable chance that the problem is you're sending too large a "packet" to MySQL. The "server has gone away" doc mentions this and refers to more detail at the "packet too large" error detail page.

In the current MySQL version, the default max packet size is 1 MB and by packet, they don't mean the TCP packet you might be using under your connection, they mean "a single SQL statement sent to the MySQL server, a single row that is sent to the client, or a binary log event sent from a master replication server to a slave." So it's actually quite easy to hit the default limit if you're working with BLOBs.

The limit can be raised in various ways (command-line, service config, etc.) and the doc points out that raising the limit should not in itself incur a performance penalty, as the additional memory is allocated only when needed.

Before cranking the max_allowed_packet setting up and calling it a night, though, it's also worth a quick sanity check as to whether you really mean to be reading and writing BLOBs this big. If you're storing videos or RAW data from a DSLR, then maybe yes.

In my case, I was using a heuristic for converting and compressing smallish photos and logos. I didn't intend to create a larger image than what the user was uploading (the largest test item was around 250kb), so the 1 MB default limit should have been fine. On closer inspection, a bug in my logic flow was incorrectly deciding to convert some 250K JPEGs into 4MB PNGs. Doh!