26 ms·

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_

by DCoder 9y ago

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-mode-strict https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mo...

eric_h 9y ago
> 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!
- rimliu 9y ago
  Long live reading documentation. Otherwise you risk data loss no matter the DB.
  - eric_h 9y ago
    At the time silent truncation was an undocumented "feature", IIRC. I think I found a description of the issue buried on some forum somewhere.
Piskvorrr 9y ago
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.