When you come across things like this it is hard to resist beating them up over it one more time. Andrew Oliver has an entry about dealing with BLOBs in MySQL. He points out that if your BLOB is too long “… then rather than like error or warn it just silently truncates the data”. I’d really prefer that my database throw an error rather than alter my data. Andrew went from Oracle to PostgreSQL to MySQL only to discover this, um, interesting feature.
It should be noted that this happens when the data is inserted, which is when it should be throwing an error instead of truncating data. This limitation is clearly spelled out in the BLOB documentation, fifth paragraph:
If you assign a value to a BLOB or TEXT column that exceeds the column type’s maximum length, the value is truncated to fit.
Because BLOBs can be very large it seems that you would be unlikely to run into this with LONGBLOBs. Unfortunately that doesn’t make my gut feel any better about how wrong it is to simply truncate data that is too big when giving an error would really be the right thing to do.
2 replies on “With Features Like This Who Needs Bugs”
It’s ok, its mySQL! Sure, it doesn’t support basic relational features, and sure, the company kinda sorta misrepresents the GPL, but it sure is fast!
If you were interested in data integrity, you wouldn’t be using mySQL anyway!
If I need integrity in mySQL I use InnoDB table type, it supports foreign keys, it’s fine for me.
Regarding truncating .. I like that feature in mysql 🙂 Recently I worked in oracle and had sql errors because data was too long, I had to truncate it manually, mysql does it for me, mysql rox 😉