MS SQL Blob truncated

There are times when I wish I had a third hand, just so I could to a triple face palm. This is one of those times.

I’m accessing a blob in a MS SQL database that is pretty much nothing more than a JPG dumped straight into the field. I read them and echo them with a jpg header in PHP. All is well. The script runs and parses all the images, doing some caching magic with them. The result is that about a third of the images are broken, seemingly random. Mostly they just “stop” at a random spot over the whole set, but in one image it always stops at the same spot.

I suspected data corruption on input and a refresh on what inserts them in the first place worked, sometimes at least.

Digging down, I saw that the result I get back for the broken images is always 64512 bytes, exactly. That’s 2 to the power of 16. Can’t be a coincidence. Turns out, it isn’t.

I’m using freetds to access the database and there is an entry in freetds.conf that reads something like this:
text size = 64512

Upping that number (and restarting the webserver, apache or IIS) did the trick. The images stored in the database were all around that point, so it looked like a random bug, but thankfully it wasn’t random.

If you don’t use freetds but the mssql extension instead, then this line would be of interest:
mssql.textsize = 4096

Yes the default is only 4 kilobytes.

Maybe the desk will work as a third hand for the triple face palm.