BLOBs in MySQL: Binary Laughable OBjects?!
Bah, MySQL keeps insisting on being a fun way to waste your time.
One of my clients hosts its systems at Dreamhost, a quite nice hosting company, which has… Well, a couple of strange details :-/
Anyway, people who work with databases know that a BLOB (or its equivalent) is the right datatype for storing images or, in general, files, right? After all, BLOB is just an acronym for Binary Large OBject. And if somebody has a BLOB field in the DB and searches on it, most RDBMS-bound programmers will at very least chuckle at a design flaw - BLOBs are just to be stored and retrieved, that’s it.
Well, the system I wrote for this client uses a BLOB field - Ok, to be clear: I just declared it as a :binary in the corresponding migration. That should do the trick, and should allow me to keep my system RDBMS-independent, right? So I can still work on my development system using nice-and-trusty PostgreSQL
Anyway… My client said some documents were being corrupted. And that is Not NiceTM… Documents were being truncated at 63Kb. The bug was consistent across the three instances of my system in Dreamhost, but could not be reproduced at my machines - It didn’t take too long to find out it must be the DB (and that’s partly because I don’t really trust MySQL :) ).
Turns out that, in MySQL speak, there are four types of blobs: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. Forgoodnessake, WHY?! The stupidiest one is the tinyblob: A Binary Large OBject limited to 8 bits (255 characters). Regular blobs are 16 bit long (65535 characters - Aha! That’s your 63.99Kb limit!). Mediumblobs are 24 bits (16 million), and longblobs are 32 bits (4000 million). And, of course, the blob datatype is not large at all.
Bah.
Anyway… At least this is an open and known issue already to the Rails people, and I do expect them to change the migration equivalencies to something saner.
Bah again.
Comments
garaged 2007-09-13 08:17:08
Re: BLOBs in MySQL: Binary Laughable OBjects?!
from mysql manual :)
“If strict SQL mode is not enabled and you assign a value to a BLOB or TEXT column that exceeds the column’s maximum length, the value is truncated to fit and a warning is generated. For truncation of non-space characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode. See Section 5.2.6, «SQL Modes».”
Gunnar 2007-09-13 08:22:48
Re: Re: BLOBs in MySQL: Binary Laughable OBjects?!
Yes, very nice - but why isn’t this the default? MySQL is often seen as “a RDBMS for those who don’t care about RDBMSs”. We should not have to specially configure it to complain about our mistakes! :-/ (specially not if living under a hosted environment)
Sam Morris 2007-09-13 07:15:59
MySQL: laughable database
Nice to see MySQL continuing the fine tradition of truncating your data rather than doing the right thing and throwing an error back to the application…
skoob 2007-09-14 03:07:43
Re: BLOBs in MySQL: Binary Laughable OBjects?!
It’s usually better to let relational databases do what they do best: store relationships between data. For huge blobs of binary data, it better to use a database that has been designed for that type of usage (aka as a file system).
If you need to store relationships between files and other data, just store the filename in the db.
(But you’re right. Silently truncating data is unacceptable.)