6 ms·

> MySQL is nice but will just silently eat your data from time to time. Auto truncate is evil, as is missing enums. These have both hit me multiple times in pro

> MySQL is nice but will just silently eat your data from time to time. Auto truncate is evil, as is missing enums. These have both hit me multiple times in production. Note: Not sure if this is still the case since I avoid it now for this reason. Looks like I am forced to use MySQL (or some of its variants ) in the near future. This thing about MySQL eating data is a statement I have read about occasionally. Is there any way to identify and beware use cases where this could happen? Would there be any more thorough documentation of this issue anywhere?

erulabs 9y ago
Any modern distribution of MySQL or MariaDB should come configured to throw query errors rather than truncate data on insert. See STRICT_ALL_TABLES / https://mariadb.com/kb/en/library/sql-mode/ https://mariadb.com/kb/en/library/sql-mode/ Modern MySQL is extremely well suited for data that cannot be lost - as is I'm sure, Postgres. That said, if you're pre 5.6, I _strongly_ suggest upgrading to 5.6 or all the way to MariaDB 10. The performance, safety, stability, etc have skyrocketed in recent years.
mohaine 9y ago
Older versions would silently allow you to insert 75 chars in to a 50 char column. The extra was just gone. Of course without an error, nobody notices until somebody notices the missing data. This is usually an end user in production and the data is just gone. Also watch out/avoid enums. Example: CREATE TABLE shirts ( ... size ENUM('x-small', 'small', 'medium', 'large', 'x-large')); You have to specify all the values in the alter so to add xx-small it is ('xx-small','x-small', 'small', 'medium', 'large', 'x-large') and then later if you add xx-large and forgot about the xx-small add: ('x-small', 'small', 'medium', 'large', 'x-large', 'xx-large') You just silently lost all the xx-small values, they have been promoted to different values that exist. (Unless this has been fixed as well). Migration scripts are the real issue as they don't know about any custom values that may have been added out of band.
- beefield 9y ago
  Thank you. Helpful.

erulabs 9y ago

Any modern distribution of MySQL or MariaDB should come configured to throw query errors rather than truncate data on insert. See STRICT_ALL_TABLES / https://mariadb.com/kb/en/library/sql-mode/ https://mariadb.com/kb/en/library/sql-mode/ Modern MySQL is extremely well suited for data that cannot be lost - as is I'm sure, Postgres. That said, if you're pre 5.6, I _strongly_ suggest upgrading to 5.6 or all the way to MariaDB 10. The performance, safety, stability, etc have skyrocketed in recent years.

mohaine 9y ago

Older versions would silently allow you to insert 75 chars in to a 50 char column. The extra was just gone. Of course without an error, nobody notices until somebody notices the missing data. This is usually an end user in production and the data is just gone. Also watch out/avoid enums. Example: CREATE TABLE shirts ( ... size ENUM('x-small', 'small', 'medium', 'large', 'x-large')); You have to specify all the values in the alter so to add xx-small it is ('xx-small','x-small', 'small', 'medium', 'large', 'x-large') and then later if you add xx-large and forgot about the xx-small add: ('x-small', 'small', 'medium', 'large', 'x-large', 'xx-large') You just silently lost all the xx-small values, they have been promoted to different values that exist. (Unless this has been fixed as well). Migration scripts are the real issue as they don't know about any custom values that may have been added out of band.

beefield 9y ago
Thank you. Helpful.