Hacker News Reader

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

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".

The UTF8 thing is NOT fud.. the fact that "utf8" still doesn't default to REAL utf8 across major version changes is ridiculous.

utf8mb4 is the default in mysql 8.

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.

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.

Just insert some emoji into an older mysql database that doesn't have the proper collation and UTF8 support setup.

> 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.

> 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.

isn't this comment non-sarcastically why all servers run on linux and not windows?

Hate to break it to you, but there is this thing called Windows Server. It's quite prolific. Estimated around 20% or more.

Curious which many hidden gems the open source BSDs contain in the way of mature file systems, since I haven't encountered any in my travels so far.

The only things I've had luck interoperating between the BSDs have been ISO9660, FAT32, and ext2. Windows has full support for the former two, and to be honest all of those are less-than-ideal.

> 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.

This is very unfair to innodb which is a mature enterprise level relational engine.

InnoDB is very nice, especially when compared to myISAM. If this post only contained information regarding myISAM then there would be a lot to unpack and it would look like flaming.

I think I’m being fair to innodb though. It’s not necessarily InnoDBs fault that it cannot handle schema changes in a transaction, or munges data if the column isn’t the right size, or that it fails a commit but overwrites data anyway.

These are generally MySQL problems, and it doesn’t matter which storage engine you use.

> or munges data if the column isn’t the right size

Same behaviour as Ms sql server or sybase. Would you be so harsh with them?

> it cannot handle schema changes in a transaction.

Same behaviour for some cases on sql server and sybase. Also in MySQL 8 some schema changes are indeed atomic. You are being unfair as those limitations are perfectly fine to live with.

> it fails a commit but overwrites data anyway.

Please provide more information

Ugh, this is going to get tedious, you're obviously feeling defensive for some reason. Please try not to be.

MSSQL does not munge data if the column is not equipped for it, I have no idea about sybase. You can test this easily by making an int column and putting a maxint+1 in.. it will tell you "NO" and not insert anything.

MSSQL supports schema changes in transactions, fully, again, not sure about sybase. MySQL 8 might support it /sometimes/ but the major concern I had with this fact is that MySQL doesn't tell you it's going to break its transaction isolation. -- it just commits in the middle of your transaction and moves on.

My final point is mitigated somewhat by MySQL "Strict" mode, which nobody enables.

I have very limited internet but this video should explain/show the behaviour I'm referencing: https://www.youtube.com/watch?v=emgJtr9tIME

> MSSQL does not munge data if the column is not equipped for it, I have no idea about sybase. You can test this easily by making an int column and putting a maxint+1 in.. it will tell you "NO" and not insert anything.

It does truncate strings.so does MySQL. But you need to enable strict mode. Easy.

Mssql does allow ddl in transactions, but do not do it, you will have huge locking issues. It will also not work in snapshot isolation.

My point stands: all major enterprise dbms have limitations. And you are dismissing MySQL, but by your standards Mssql and sybase would also be dismissed.

This is unfair.

You just need a semi competent dev to know those limitations. And MySQL with innodb is not that far from sql server or sybase.

> My point stands: all major enterprise dbms have limitations. And you are dismissing MySQL, but by your standards Mssql and sybase would also be dismissed.

You are refuting a claim I never made. My issue is not with the limitations, these are a fact of life with any and all technology.

My issue is with silent data corruption and subtle issues that break expectations

Thus, requiring any user of the system to be fully versed in all the documentation and to be prescient enough at all times when interacting with the database.

I can’t make such guarantees, and PostgreSQL follows the principle of least surprise much better. If I have two options and one of them has odd silent failure modes and the other holds your feet to the fire to ensure correctness. I will consistently choose the latter.

> it shouldn’t be treated as anything more than a nosql document store with mature replication.

This is the outrageous statement I was replying too. I get it, postgresql is your thing (I'm a postgresql dba BTW). However you are being excessive and unfair.

You are just repeating a meme without having administered databases professionally. You need to study your database of choice. Postgresql or Mysql. Are you aware of postgresql's fsync bug ? Is postgresql more than a nosql database because of that ?

> My issue is with silent data corruption and subtle issues that break expectations

SQL server and sybase do truncate data too. Silently. Are they nothing more than nosql databases because of that ?

You've made a few comments about me here which I feel are undeserved, if you knew me you would not assume I am "repeating a meme", I am a classically trained sysadmin, not historically a coder, and I've been the bastion of data consistency in a few very high transaction/heavy database driven companies and I've been in the industry close to 15 years now. -- incidentally the only time I lost data was due to silent corruption bugs in the application or as a discovery made after inheriting a MySQL 5.1 cluster.

My choice of database technology is driven by industry experience, not fanboyism (many who know me, know that I fought very hard against the fad of mongodb, for instance). And it's true I prefer PostgreSQL these days, mostly because the only time it's ever bitten me was with the autovacuum and that was all the way back in postgresql 8.2!

It's possible I'm incompetent, but I'd rather not go into a slinging match about competency right now.

I am aware of postgresql's fsync bug, but that's not _at all_ comparable to: defined, documented behaviour in a database engine.

Yes, bugs happen and bugs are bad, but what the grandparent stated was absolutely not a bug, it's documented behaviour, it's known behavior and it's only _just_ becoming addressed and only in the loosest of terms (incidentally as programmer mindshare is starting to focus on alternatives).

FWIW, I personally believe that MySQL and its ilk should be relegated to legacy applications, I do not hold it as fact that there's a cojent reason to choose it for a new project even as a NOSQL solution unless a few things are true:

1) All your developers only know MySQL and MySQL specifics (as in, you're a pure mysql shop and you know it very well)

2) You already have a product built on MySQL, it's costly to move.

3) You are the people who are building/designing mysql and trying to compete with more competent database engines.

You bring up truncation of strings, but I was talking primarily about ints/floats.

As a person who has 'dba' in his name you've made a lot of claims disparaging MSSQL, some of them I told you that you were wrong about and you agreed; but I am fairly certain that MSSQL does not truncate a string on insert. I'm going to test this claim.

EDIT::

Sorry it took me an hour to install MSSQL on my laptop, I'm on vacation in Russia and internet here is hard to come by.

Anyway: MSSQL does not silently insert varchars.

    #> create table #sometable(acolumn varchar(8))
    #> insert into #sometable(acolumn) values('blah blah blah way more than 8 chars')
    Msg 8152, Level 16, State 14, Line 7
    String or binary data would be truncated.

`select *` shows no new rows, which is what I would expect.

It all depends on the ANSI_WARNINGS switch. Some clients set it, some don't. Thus you have to be explicit or you might have a NoSQL database.

You should also read about arithabort and arithignore.

As I told you, you need to study the database engine you are using. For Mssql too. So you assumed it's always on. The type of mystake some non diligent devs do with MySQL. Gives it an undeserved bad rep. A dba can help you and teach you the nuances. Ask them at your company.

There have been a considerable amount of efforts made to improve innodb. It's plenty fast and properly used, it's well behaved. Just like Mssql.

You are talking past the person you are replying to. Parent comment points out that MySQL has quirks that helps you shoot yourself in the foot moreso than other engines. Eventually you will slip up, no matter how diligent you are. The argument is that it's easier to slip up and with more frequency in MySQL, so when faced with a choice there are safer alternatives.

> Parent comment points out that MySQL has quirks that helps you shoot yourself in the foot moreso than other engines.

It used to be true. Not so much anymore. Study the defaults on mysql 8.

To summarise: Broadly the grandparent is correct, but some of his problems have been addressed in a very new version of the database in question. Is that fair?

Most of the "problems" also exist in enteprise scale database server such as sql server and sybase.

If parent meant that sql server and sybase are no more than nosql data store, I beg to differ.

Those issues have been "fixable" in innodb for years using flags. This is getting old. I think people come in here to have accurate information.

> Most of the "problems" also exist in enteprise scale database server such as sql server and sybase.

You've repeatedly dumped a long string of personal attacks based on statements of fact that were fundamented rather well, and in spite of your repeated appeals to authority you've failed or refused to comment on the technical aspects and decided to react with attacks and repeated assertions that in your eyes other alternatives are not perfect. Perhaps its high tine for you to step away from the keyboard and think about what you've been doing in this thread and how you've decided to portay yourself in this discussion.

Please don't do flamewar or post personal attacks, even if another account is doing that.

https://news.ycombinator.com/newsguidelines.html

SQL Server does not truncate data silently, it errors out with "String or binary data would be truncated."

> Ugh, this is going to get tedious, you're obviously feeling defensive for some reason.

Please edit this sort of thing out of your posts to HN, regardless of how defensive someone else is being or how provoked you feel. If they're really being so defensive, nothing good will come of arguing and it's best to let go anyhow.

https://news.ycombinator.com/newsguidelines.html

Fair. I’ll be better.

Your comments in this thread crossed repeatedly into getting personal (taking the form 'you are this', 'you need to that', etc., and impugning someone else's level of knowledge). Would you please not do this when posting here? It isn't necessary, it's provocative and distracting, and indeed it provoked and distracted.

https://news.ycombinator.com/newsguidelines.html

Sorry but it seems necessary, PC principal.

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 anything that you could run against both databases to quantitatively say that postgres is better in this regard than mysql ?

or that would somehow highlight that it's more complicated to get compliance from one that the other, eg because the required SQL is more complicated

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.

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://dev.mysql.com/doc/refman/5.7/en/added-deprecated-rem...

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-collatio...

Edit: Also forgot to mention, switching the default character set broke over 600 tests. It's not as easy as it sounds!

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?

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-ut...

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.

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.

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 ?

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

There's pgAdmin

There is also pgweb.

https://www.adminer.org/ for both

https://dbeaver.io/

Good to know they still support MariaDB, then.