3 ms·

While MySQL being able to scale is nice and all, it's had serious problems with even keeping basic data integrity for the data people hand it... for decades.

bombcar 3y ago
This is the root of it all - for years and years MySQL was perfectly capable of doing the needful, but it shipped with default configurations that would do things like use "not_quite_really_UTF8" and "myisam_tables_please_no_acid". If you knew enough to switch to innodb and "mysql_real_utf8_string" or whatever it was, you had a quite performant and stable system.
- justinclift 3y ago
  There have also been numerous horror stories where people discovered (after the fact) that MySQL wasn't storing their data as given, but instead internally silently converting it to some other format/type and storing that. People that directly experienced this very thing will probably chime in to give examples (etc). :)
  - jarym 3y ago
    Yep - ME! Happened to ME! Now some will blame bad development, should have caught it in the application code via testing, etc. A 'true' DBMS will provide some guarantees and rather fail a transaction than do a 'best endeavours' job with it.
- anonacct37 3y ago
  You hit the nail on the head, those were my top two complaints, but the rabbit hole goes deep. It's not an exaggeration to say that trying to keep mysql from corrupting your data feels like a full-time job. Mysql in many ways, especially as used by massive social media companies early on was closer to a SQLish DSL for a non-transactional kv store than a "real" ACID database. That's why "but it can do 70k rps" is just noise to someone. I don't care how fast it can lose my data. I'm all for people needing to read the docs but man a database burns trust pretty fast when it's so easy to have it pretend to support transactions or pretend to support utf8.
jarym 3y ago
That was my biggest gripe with it - at least back in the day. Back then it really felt like MySQL vs Postgres was a trade-off between 'easy but no guarantees on data integrity' and 'correct but operationally harder to manage'. The gap has for sure closed in many areas but I still feel safer with Postgres today because there's been little reason for me to try MySQL again.
karmakaze 3y ago
Can we restrict our discussion to the situation now. If default configs result in data corruption, that's an issue. If an older version in the past decade does, that's of less interest.
- justinclift 3y ago
  [flagged]
  - evanelias 3y ago
    MySQL's default storage engine has been InnoDB -- a high-quality OLTP storage engine which is fully ACID compliant -- for thirteen years now. Meanwhile MySQL's default settings have included strict sql_mode (giving a hard error on data type mismatches, overflows, etc) for eight years. These have been available as non-default options for 20+ years, and larger MySQL users have long enabled them before they were defaults. Yes, the defaults certainly should have changed earlier, especially re: strict sql_mode, I won't dispute that. However on the default storage engine side, the historical reasoning makes perfect sense: InnoDB and MySQL were owned by different companies. Oracle purchased the makers of InnoDB in 2005 and didn't purchase Sun (incl MySQL) until 2010. Once both products were owned by Oracle, they made InnoDB the default storage engine in the next major MySQL release that same year. In any case, your claims of "decades of misleading marketing" about transactions is completely fictional. This simply did not happen for "decades" and has not been a discussion point of any sort for a very, very long time. Serious MySQL shops were never using MyISAM at scale in the first place.
    - justinclift 3y ago
      [flagged]
      - evanelias 3y ago
        > Yeah, good luck with that. That's a completely nonsensical reply to the contents of my comment. I'm not the same person you were originally responding to up-thread.
rjh29 3y ago
But now strict mode is on by default, the situation has improved substantially.

bombcar 3y ago

This is the root of it all - for years and years MySQL was perfectly capable of doing the needful, but it shipped with default configurations that would do things like use "not_quite_really_UTF8" and "myisam_tables_please_no_acid". If you knew enough to switch to innodb and "mysql_real_utf8_string" or whatever it was, you had a quite performant and stable system.

justinclift 3y ago
There have also been numerous horror stories where people discovered (after the fact) that MySQL wasn't storing their data as given, but instead internally silently converting it to some other format/type and storing that. People that directly experienced this very thing will probably chime in to give examples (etc). :)
- jarym 3y ago
  Yep - ME! Happened to ME! Now some will blame bad development, should have caught it in the application code via testing, etc. A 'true' DBMS will provide some guarantees and rather fail a transaction than do a 'best endeavours' job with it.
anonacct37 3y ago
You hit the nail on the head, those were my top two complaints, but the rabbit hole goes deep. It's not an exaggeration to say that trying to keep mysql from corrupting your data feels like a full-time job. Mysql in many ways, especially as used by massive social media companies early on was closer to a SQLish DSL for a non-transactional kv store than a "real" ACID database. That's why "but it can do 70k rps" is just noise to someone. I don't care how fast it can lose my data. I'm all for people needing to read the docs but man a database burns trust pretty fast when it's so easy to have it pretend to support transactions or pretend to support utf8.

jarym 3y ago

That was my biggest gripe with it - at least back in the day. Back then it really felt like MySQL vs Postgres was a trade-off between 'easy but no guarantees on data integrity' and 'correct but operationally harder to manage'. The gap has for sure closed in many areas but I still feel safer with Postgres today because there's been little reason for me to try MySQL again.

karmakaze 3y ago

Can we restrict our discussion to the situation now. If default configs result in data corruption, that's an issue. If an older version in the past decade does, that's of less interest.

justinclift 3y ago
[flagged]
- evanelias 3y ago
  MySQL's default storage engine has been InnoDB -- a high-quality OLTP storage engine which is fully ACID compliant -- for thirteen years now. Meanwhile MySQL's default settings have included strict sql_mode (giving a hard error on data type mismatches, overflows, etc) for eight years. These have been available as non-default options for 20+ years, and larger MySQL users have long enabled them before they were defaults. Yes, the defaults certainly should have changed earlier, especially re: strict sql_mode, I won't dispute that. However on the default storage engine side, the historical reasoning makes perfect sense: InnoDB and MySQL were owned by different companies. Oracle purchased the makers of InnoDB in 2005 and didn't purchase Sun (incl MySQL) until 2010. Once both products were owned by Oracle, they made InnoDB the default storage engine in the next major MySQL release that same year. In any case, your claims of "decades of misleading marketing" about transactions is completely fictional. This simply did not happen for "decades" and has not been a discussion point of any sort for a very, very long time. Serious MySQL shops were never using MyISAM at scale in the first place.
  - justinclift 3y ago
    [flagged]
    - evanelias 3y ago
      > Yeah, good luck with that. That's a completely nonsensical reply to the contents of my comment. I'm not the same person you were originally responding to up-thread.

rjh29 3y ago

But now strict mode is on by default, the situation has improved substantially.