7 ms·

MySQL is a decent nosql option with mature replication. If you care about your data, expect MySQL to be your foe rather than your friend. Do you expect your tr

MySQL is a decent nosql option with mature replication. If you care about your data, expect MySQL to be your foe rather than your friend. Do you expect your triggers to fire? You are SOL https://bugs.mysql.com/bug.php?id=11472 https://bugs.mysql.com/bug.php?id=11472 Do you expect to be able to insert utf8 into utf8 columns? Don't be naive. Read the docs and you'll discover utf8mb4 or as I like to refer to it: mysql_real_utf8. How about transactions? Surely a real SQL database supports transactions right? Or at the very least if it doesn't it would complain loudly when you use a transaction where not supported right? Once again this behavior is helpfully documented https://dev.mysql.com/doc/refman/8.0/en/commit.html https://dev.mysql.com/doc/refman/8.0/en/commit.html Do you expect to be able to put a value into MySQL and get that same value back out? You are SOL. But it's documented. I can honestly say that the only appropriate time to use MySQL is when you can't use postgres and you are willing to move from a RDBMS to something that requires significantly more work to prevent data corruption. I respect the hell out of MySQL engineers in terms of raw performance and the engineering of Innodb. I'm sure they aren't happy there's a million broken applications relying on defaults they can't change. I'm sure they aren't happy that fundamental limits in how pluggable storage engines work make ACID transactions not possible in the general case. Postgres, for all of it's issues, feels like a database that has your back.

stingraycharles 7y ago
This seems overly negative and a lot of FUD. Some of these issues are minor, and/or when aware of it are easy to work around. MySQL is much more than a "nosql with mature replication".
- tracker1 7y ago
  The UTF8 thing is NOT fud.. the fact that "utf8" still doesn't default to REAL utf8 across major version changes is ridiculous.
  - datasage 7y ago
    utf8mb4 is the default in mysql 8.
    - chipotle_coyote 7y ago
      I think the point here is that when software calls an encoding "utf8," it is reasonable to expect that it means what every other piece of software is likely to mean by "utf8." MySQL violates that expectation. It's nice that they've changed the default encoding to be "utf8_for_realsies" but that should have been what was meant by the encoding with the name "utf8" all along.
      - tracker1 7y ago
        Beyond that, I get that "utf8" meant "utf8...psych" before, but the alias for "utf8" should have been made an alias for the real utf8 several versions ago. And that's just one single quirk... no other RDBMS has so many quirks that weren't eliminated across major versions like mysql.
  - skunkworker 7y ago
    Just insert some emoji into an older mysql database that doesn't have the proper collation and UTF8 support setup.
- dijit 7y ago
  > MySQL is much more than a “nosql with mature replication” Yes. It is, and the parent is saying why (despite the fact it had more features) it shouldn’t be treated as anything more than a nosql document store with mature replication. FWIW I’ve used MySQL, MongoDB, Postgres, Cassandra and a plethora of others at huge scale and I cannot possibly be clearer: MySQL is full to the brim of foot-guns; storage specific behaviours, leaky transaction isolation and silent data corruption behaviours et al. You CAN, absolutely navigate it well enough to never lose a bit or get bitten by any of its “weird” behaviours, but it requires that every person touching the database is very well versed in the entire documentation of the database. That is why the parent is not spreading FUD, because MySQL as a technology should be approached with caution, and the fact that it looks like it’s working when it’s corrupting your data is the worst design consideration of the entire thing; which necessitates this behaviour and reinforces the myth that MySQL is not dangerous.
  - idoubtit 7y ago
    > it shouldn’t be treated as anything more than a nosql document store with mature replication. For years, Windows NT claimed to have Unicode support, but what they called Unicode was in fact the UCS subset that used 2 bytes for each character. They still haven't adopted UTF-8 which is ubiquitous on the web. Windows uses NTFS which is a mess ridden by compatibility constraints. For instance, the recent releases of NTFS at last introduce a snapshot feature, but mounting the volume may destroy the snapshot. FAT was even uglier. Linux and BSD are fully compatible with many mature file systems, while Windows has few options. Windows should not be treated as anything more than a volatile UI with mature drivers. Windows is dangerous and everyone should switch to Linux or BSD (MacOS for the hipsters). /sarcasm As inconsistent and dangerous as it may be, MySQL is more than a nosql document store.
    - jmchuster 7y ago
      isn't this comment non-sarcastically why all servers run on linux and not windows?
      - jjeaff 7y ago
tracker1 7y ago
Changing the defaults across a major release is something they've had several opportunities to do, time and again they have kept a LOT of weird behaviors.
- morgo 7y ago
  Hi! Former product manager for MySQL here. The defaults have changed a lot across major releases: https://mysqlserverteam.com/new-defaults-in-mysql-8-0/ https://mysqlserverteam.com/new-defaults-in-mysql-8-0/ https://dev.mysql.com/doc/refman/5.7/en/added-deprecated-removed.html https://dev.mysql.com/doc/refman/5.7/en/added-deprecated-rem... https://dev.mysql.com/doc/refman/5.6/en/server-default-changes.html https://dev.mysql.com/doc/refman/5.6/en/server-default-chang... One detail that is not always obvious is how much work goes into limiting regressions. The work to switch to utf8mb4 really started in MySQL 5.6 by not allocating the sort buffer in full (and then further improved in 5.7). 8.0 then added a new temptable storage engine for variable length temp tables. These are not small cases either: When you compare to latin1 because the _profile_ of queries could change from all in memory to on disk, we could be talking about 10x regressions. In MySQL 8.0 it is more like 11% https://www.percona.com/blog/2019/02/27/charset-and-collation-settings-impact-on-mysql-performance/ https://www.percona.com/blog/2019/02/27/charset-and-collatio... Edit: Also forgot to mention, switching the default character set broke over 600 tests. It's not as easy as it sounds!
  - tracker1 7y ago
    While I appreciate that it's the default now (utf8mb4)... If someone specified (by error) "utf8" as the collation, is that real utf8 or some other implementation currently?
    - morgo 7y ago
      If someone uses `utf8` in MySQL 8.0, they will get a warning suggesting they should use `utf8mb4`, because `utf8` will be deprecated. Redefining `utf8` to mean 4-byte would break the upgrade since existing tables would not be able to join against newly created tables. This is discussed here: https://mysqlserverteam.com/sushi-beer-an-introduction-of-utf8-support-in-mysql-8-0/ https://mysqlserverteam.com/sushi-beer-an-introduction-of-ut...
dano 7y ago
I moved away from MySQL in 2004 to Postgres and never looked back. Thanks for articulating these things once again for the community.
brighter2morrow 7y ago
O mainly use MySQL because of phpMyAdmin. Is there anything comparable for Postgres?
- CopyZero 7y ago
  There's pgAdmin
- muminoff 7y ago
  There is also pgweb.
- deleted 7y ago
  [deleted]
- aargh_aargh 7y ago
  https://www.adminer.org/ https://www.adminer.org/ for both
- bmn__ 7y ago
  https://dbeaver.io/ https://dbeaver.io/
BorRagnarok 7y ago
If it would only run flawlessly on Windows, I would gladly run Postgres. It doesn't though, so I chose MySQL for my project. This was two years ago however, things might have changed. *edit: I recognize a lot of what you mentioned though, and MySQL sure has its draw backs. Still it seems to work fine for a lot of people.
- dijit 7y ago
  What's the issue with running on Windows? FWIW we're running a lot of instances on Windows, and there are differences for sure, performance falls off a cliff, but for testing it's decent enough.
nqzero 7y ago
i'm looking for a test suite that is used to verify query isolation and correctness at high levels of concurrency. ie do you know of something that you could run against postgres to quantitatively say that it is "a database that has your back", and then run against mysql and see that it fails ?
msla 7y ago
Good to know they still support MariaDB, then.

stingraycharles 7y ago

This seems overly negative and a lot of FUD. Some of these issues are minor, and/or when aware of it are easy to work around. MySQL is much more than a "nosql with mature replication".

tracker1 7y ago
The UTF8 thing is NOT fud.. the fact that "utf8" still doesn't default to REAL utf8 across major version changes is ridiculous.
- datasage 7y ago
  utf8mb4 is the default in mysql 8.
  - chipotle_coyote 7y ago
    I think the point here is that when software calls an encoding "utf8," it is reasonable to expect that it means what every other piece of software is likely to mean by "utf8." MySQL violates that expectation. It's nice that they've changed the default encoding to be "utf8_for_realsies" but that should have been what was meant by the encoding with the name "utf8" all along.
    - tracker1 7y ago
      Beyond that, I get that "utf8" meant "utf8...psych" before, but the alias for "utf8" should have been made an alias for the real utf8 several versions ago. And that's just one single quirk... no other RDBMS has so many quirks that weren't eliminated across major versions like mysql.
- skunkworker 7y ago
  Just insert some emoji into an older mysql database that doesn't have the proper collation and UTF8 support setup.
dijit 7y ago
> MySQL is much more than a “nosql with mature replication” Yes. It is, and the parent is saying why (despite the fact it had more features) it shouldn’t be treated as anything more than a nosql document store with mature replication. FWIW I’ve used MySQL, MongoDB, Postgres, Cassandra and a plethora of others at huge scale and I cannot possibly be clearer: MySQL is full to the brim of foot-guns; storage specific behaviours, leaky transaction isolation and silent data corruption behaviours et al. You CAN, absolutely navigate it well enough to never lose a bit or get bitten by any of its “weird” behaviours, but it requires that every person touching the database is very well versed in the entire documentation of the database. That is why the parent is not spreading FUD, because MySQL as a technology should be approached with caution, and the fact that it looks like it’s working when it’s corrupting your data is the worst design consideration of the entire thing; which necessitates this behaviour and reinforces the myth that MySQL is not dangerous.
- idoubtit 7y ago
  > it shouldn’t be treated as anything more than a nosql document store with mature replication. For years, Windows NT claimed to have Unicode support, but what they called Unicode was in fact the UCS subset that used 2 bytes for each character. They still haven't adopted UTF-8 which is ubiquitous on the web. Windows uses NTFS which is a mess ridden by compatibility constraints. For instance, the recent releases of NTFS at last introduce a snapshot feature, but mounting the volume may destroy the snapshot. FAT was even uglier. Linux and BSD are fully compatible with many mature file systems, while Windows has few options. Windows should not be treated as anything more than a volatile UI with mature drivers. Windows is dangerous and everyone should switch to Linux or BSD (MacOS for the hipsters). /sarcasm As inconsistent and dangerous as it may be, MySQL is more than a nosql document store.
  - jmchuster 7y ago
    isn't this comment non-sarcastically why all servers run on linux and not windows?
    - jjeaff 7y ago

tracker1 7y ago

Changing the defaults across a major release is something they've had several opportunities to do, time and again they have kept a LOT of weird behaviors.

morgo 7y ago
Hi! Former product manager for MySQL here. The defaults have changed a lot across major releases: https://mysqlserverteam.com/new-defaults-in-mysql-8-0/ https://mysqlserverteam.com/new-defaults-in-mysql-8-0/ https://dev.mysql.com/doc/refman/5.7/en/added-deprecated-removed.html https://dev.mysql.com/doc/refman/5.7/en/added-deprecated-rem... https://dev.mysql.com/doc/refman/5.6/en/server-default-changes.html https://dev.mysql.com/doc/refman/5.6/en/server-default-chang... One detail that is not always obvious is how much work goes into limiting regressions. The work to switch to utf8mb4 really started in MySQL 5.6 by not allocating the sort buffer in full (and then further improved in 5.7). 8.0 then added a new temptable storage engine for variable length temp tables. These are not small cases either: When you compare to latin1 because the _profile_ of queries could change from all in memory to on disk, we could be talking about 10x regressions. In MySQL 8.0 it is more like 11% https://www.percona.com/blog/2019/02/27/charset-and-collation-settings-impact-on-mysql-performance/ https://www.percona.com/blog/2019/02/27/charset-and-collatio... Edit: Also forgot to mention, switching the default character set broke over 600 tests. It's not as easy as it sounds!
- tracker1 7y ago
  While I appreciate that it's the default now (utf8mb4)... If someone specified (by error) "utf8" as the collation, is that real utf8 or some other implementation currently?
  - morgo 7y ago
    If someone uses `utf8` in MySQL 8.0, they will get a warning suggesting they should use `utf8mb4`, because `utf8` will be deprecated. Redefining `utf8` to mean 4-byte would break the upgrade since existing tables would not be able to join against newly created tables. This is discussed here: https://mysqlserverteam.com/sushi-beer-an-introduction-of-utf8-support-in-mysql-8-0/ https://mysqlserverteam.com/sushi-beer-an-introduction-of-ut...

dano 7y ago

I moved away from MySQL in 2004 to Postgres and never looked back. Thanks for articulating these things once again for the community.

brighter2morrow 7y ago

O mainly use MySQL because of phpMyAdmin. Is there anything comparable for Postgres?

CopyZero 7y ago
There's pgAdmin
muminoff 7y ago
There is also pgweb.
deleted 7y ago
[deleted]
aargh_aargh 7y ago
https://www.adminer.org/ https://www.adminer.org/ for both
bmn__ 7y ago
https://dbeaver.io/ https://dbeaver.io/

BorRagnarok 7y ago

If it would only run flawlessly on Windows, I would gladly run Postgres. It doesn't though, so I chose MySQL for my project. This was two years ago however, things might have changed. *edit: I recognize a lot of what you mentioned though, and MySQL sure has its draw backs. Still it seems to work fine for a lot of people.

dijit 7y ago
What's the issue with running on Windows? FWIW we're running a lot of instances on Windows, and there are differences for sure, performance falls off a cliff, but for testing it's decent enough.

nqzero 7y ago

i'm looking for a test suite that is used to verify query isolation and correctness at high levels of concurrency. ie do you know of something that you could run against postgres to quantitatively say that it is "a database that has your back", and then run against mysql and see that it fails ?

msla 7y ago

Good to know they still support MariaDB, then.