Hacker News Reader

Comment by pram | Hacker News Reader

I recently experienced a nasty bug with BLOB in MySQL. The software vendor was storing a giant json which contained the entire config in a single cell. It ran fine for months, and then when it was restarted it totally broke. Reason was: the json had been truncated the entire time in the database, so it was gone forever. It was only working because it used the config stored in memory on the local system. Nasty!

View on HN

MySQL's silent data truncation is such a nuisance. It's off by default in 5.7, and can be disabled in earlier versions by adding STRICT_ALL_TABLES/STRICT_TRANS_TABLES to sql_mode [1].

I inherited a system where, among other things, the entire response body from a payment gateway callback is saved into a text field using utf8 character set, despite the fact that most of the supported payment gateways send data in iso-8859-x (and indicate the used charset inside the body itself, how's that for a chicken-and-egg problem). Of course when the data gets truncated due to not actually being utf8, nobody notices. Fun times.

[1]: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mo...

> MySQL's silent data truncation is such a nuisance.

Yes, yes it is - it burned me so badly (catastrophic, unrecoverable production data loss) in the early days of my career (~15 years ago as a junior level dev in a senior level role) that it has forever colored my opinion of MySQL - I will really never trust it again.

Long live PostgreSQL!

Long live reading documentation. Otherwise you risk data loss no matter the DB.

At the time silent truncation was an undocumented "feature", IIRC. I think I found a description of the issue buried on some forum somewhere.

Early web had these issues as well: server sends response with Content-Type: text/html; charset=win-1251 (or no charset), but body contains meta charset=utf-8. MSIE worked around that in IE4 by comparing the letter frequencies to a hardcoded table and guessing the charset from there. It sort of worked, 80/20.

For those, like my, that didn't recall off the top of their heads: BLOB in MySQL can usually only hold ~64KB.

EDIT: Though I am curious why MySQL doesn't throw an error when you try to store more than 64KB in BLOB?

The BLOB size limit is a nuisance, the silent truncation is the bug...

It'll give a warning. Many/most MySQL drivers won't interpret it as an error, though.

Huh, I thought BLOB was a backronym for Binary Large OBject, not Binary Medium Object.

There are LONGBLOB, TINYBLOB, etc just like the equivalent TEXT fields

Mysql's unix_timestamp() breaks on years past mid 2038...so plan for that one too.

OpenBSD[1]:

> time_t is now 64 bits on all platforms.

Linux[2]:

> The vast majority of 64-bit hosts use 64-bit time_t. This includes GNU/Linux, the BSDs, HP-UX, Microsoft Windows, Solaris, and probably others. There are one or two holdouts (Tru64 comes to mind) but they're a clear minority.

This is little help for older, already deployed systems, of course.

[1]: https://www.openbsd.org/55.html

[2]: https://mm.icann.org/pipermail/tz/2004-June/012483.html

Good to know. I was, though, specifically taking about a function inside MySQL.

Reply on news.ycombinator.com