Thursday, January 24, 2008

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!

5 comments: said...

everything you do is just so mystical and beautiful. loved this.

Runescape Gold

Diablo 3 Gold

Seacanoeist Mark said...

I liked your article, I will share your article to everyone!!

WoW gold|Diablo 3 Gold|RS Gold|Cheap Diablo 3 Gold

china tours said...

I feel my ideas and methods may be a problem, but very confused and do not know how to do, so ask the experts how to grow up.You can learn more: China tour packages | China travel packages | China Travel Agency

Learn Chinese said...

AllMandarin is the only company that can give you an excellent alternative to learn Mandarin Chinese that is not only better, but also more efficient in terms of cost, flexibility and learning methods.I am a Chinese teacher,you can learn more about Chinese language info: Learn Chinese | Learn mandarin | Chinese teachers

Study mandarin online said...

I'm learning Chinese language because I believe it's the only way to really learn about China.When I was searching for a place to learn to speak Chinese, I called several schools. Hanbridge was the best because they had excellent teachers and a very friendly and welcoming spirit. ?I really appreciate the opportunity to learn here and would recommend Hanbridge to others.