
One Giant Leap for SQL: MySQL 8.0 Released - dhd415
https://modern-sql.com/blog/2018-04/mysql-8.0
======
macdice
PostgreSQL 9.5 manual: "Skipping locked rows provides an inconsistent view of
the data, so this is not suitable for general purpose work, but can be used to
avoid lock contention with multiple consumers accessing a queue-like table."

MySQL 8.0 manual: "Queries that skip locked rows return an inconsistent view
of the data. SKIP LOCKED is therefore not suitable for general transactional
work. However, it may be used to avoid lock contention when multiple sessions
access the same queue-like table."

Flattering (I wrote those words). Of course we borrow the SKIP LOCKED syntax
from yet another database (though the idea is much older, under different
names).

------
Digital-Citizen
The article says MySQL "the most popular free SQL database" but gives no
reference for this claim. It seems to me that every system I know of has at
least one install of SQLite somewhere (be it as something that was installed
with the OS or in some commonly installed application). SQLite seems to agree
with my assessment in
[https://sqlite.org/mostdeployed.html](https://sqlite.org/mostdeployed.html)
so I wonder where did the quoted statement above come from, and what's the
basis for it?

~~~
MarkusWinand
Full quote.

> MySQL is very popular. According to db-engines.com, it’s the second most
> popular SQL database overall. More importantly: it is, by a huge margin, the
> most popular free SQL database.

The second sentence is linked: [https://db-engines.com/en/ranking](https://db-
engines.com/en/ranking)

The third sentence refers to the same source.

~~~
Digital-Citizen
Thanks for the link, but I find this highly unlikely to be what the article or
db-engines.com claims. The data comes from vague sources that don't jibe with
number of installations. The word "popular" is used in such a perverse way it
renders the db-engines.com ranking and that claim in the article impossible to
believe.

That ranking site claims "The DB-Engines Ranking ranks database management
systems according to their popularity." but then includes concepts such as
"Number of mentions of the system on websites", "General interest in the
system", and "Relevance in social networks" as part of the ranking (see
[https://db-engines.com/en/ranking_definition](https://db-
engines.com/en/ranking_definition) for details). In fact, the one thing one
would typically consider to be "popularity" \-- installed base size -- is not
considered at all ("The DB-Engines Ranking does not measure the number of
installations of the systems, or their use within IT systems."). In fact the
paragraph from which I drew that quote highlights how ridiculous this site is:
to understand that paragraph's use of the word "popular" you have to use the
common definition, the definition they won't use in computing their so-called
"popularity" ranking. This tells me that db-engine.com's ranking is not to be
taken seriously.

As SQLite's page I pointed to claims, virtually every modern operating system
and many popular programs come with at least one copy of SQLite. But then
again, understanding the previous sentence requires using the word "popular"
in a way that most people use that word most of the time, not the perverse way
db-engines.com used the word. Put differently, Oracle, db-engines.com #1 pick,
doesn't run on iOS, MacOS, GNU/Linux, and some variants of Windows. That means
there are a considerable number of systems that host at least one copy of
SQLite and will never host Oracle.

I find it very difficult to believe that there are more running or installed
instances of Oracle on the world's computers than there are SQLite. The
original article is plainly wrong. MySQL is nowhere nearly as "popular" as is
claimed and the backing to believe otherwise hinges on a highly dubious
source.

------
ocdtrekkie
This looks like a great place to tell you about the burning white hot rage I
have for Debian after my difficulties with MySQL this past weekend.

I was working on script that builds a LEMP stack with Vagrant on a Debian box.
And when the script was upgraded to use Debian Stretch, everything we did to
customize MySQL configuration broke, and I spent many hours of anguish not
knowing why.

Until I stumbled onto the fact that Debian decided that in version 9, they'd
start installing MariaDB when you typed "apt-get install mysql-server". I
cannot fully explain the anger I have for this dark pattern: When I say
"install x", you should either install x or say you don't have it, not install
a competitor you think is close enough.

~~~
astrodust
The way MySQL was being handled as a hybrid of open-source and closed-source
for tests was causing a lot of trouble for organizations like Debian that
wanted to ship truly open-source software. MariaDB was, at the time, a pretty
close fork with the right license model so it was a good drop-in substitute.

These situations are rare, but it's not without precedent.

~~~
ocdtrekkie
To be clear: I have no issue with Debian shipping MariaDB. I have an issue
with Debian pretending MariaDB is MySQL.

If I run "apt-get install mysql-server" and get something other than MySQL, we
now have a trust issue: The Debian repository cannot be trusted.

~~~
kasabali
MySQL is not available in Debian repository since MariaDB has replaced it.

mysql-server package installs mariadb so that they can handle upgrades
gracefully in a way that won't leave existing users in cold.

They would choose to do it the way you said but then the people who are
upgrading their systems would be annoyed instead of you. Tough choice but
either way it's a minor annoyance to be honest.

Addition:

Also it's not as if it isn't _very_ clear what you're installing:

    
    
      $ apt install mysql-server
      ....
      The following NEW packages will be installed:
        default-mysql-server galera-3 libdbd-mysql-perl libhtml-template-perl libterm-readkey-perl mariadb-client-10.1
        mariadb-client-core-10.1 mariadb-common mariadb-server-10.1 mariadb-server-core-10.1 mysql-server
      ....
    
      $ man mysql
      MYSQL(1)                                                               MariaDB Database System                                                              MYSQL(1)
    
      NAME
             mysql - the MariaDB command-line tool
    
      ....
    
    

It is not aim of distribution maintainers to hide facts or make you use
counterfeit programs, they just want to help users with transition.

~~~
adrianmonk
> _Also it 's not as if it isn't very clear what you're installing_

I certainly disagree that this qualifies as "very clear". The user asked to
have mysql installed and the very first item on the list of stuff-to-be-
installed is called "default-mysql-server". That certainly sounds like
positive confirmation that you are indeed getting what you asked for.

Now, if you keep reading, you will see "mariadb-server-10.1" and stuff on that
list, and if you also know two pieces of information (that mariadb is a fork
of mysql, and that someone at Debian contemplated the idea of substituting one
for the other), then you can see that you _may_ have concluded the wrong thing
and you can backtrack from there. Though really, at this point you still have
conflicting information and it isn't conclusive.

So, I wouldn't say "very clear" is accurate. More like "possible to decipher
if you have all the relevant context and are paying close attention".

As UIs go, this definitely violates the principle of least astonishment.

~~~
kasabali
Okay, these are all fair points regarding package installation

(though I don't buy this was an enough excuse for GP in this specific case
because it becomes clear at the moment you run `mysql' command)

------
ausjke
I'm having a hard time to pick between mysql8 and postgresql10.

MySQL is under Oracle is one of the major concern, plus it's said to be good
for less-write-more-read workload, which is fine.

PostgreSQL is more on the open source side, however bugs like this really
concerns me:
[https://lwn.net/Articles/752063/](https://lwn.net/Articles/752063/) \--
basically, PostgreSQL does not do its own DIO instead it depends on file
system and it can lead to data corruption, and there is no quick fix...

tried to install mysql8.0 on ubuntu16.04 failed so far, need a vm to test it
out sometime.

~~~
neuronexmachina
From MySQL's documentation:

[https://dev.mysql.com/doc/refman/8.0/en/innodb-
parameters.ht...](https://dev.mysql.com/doc/refman/8.0/en/innodb-
parameters.html)

> Caution: Many operating systems and some disk hardware fool the flush-to-
> disk operation. They may tell mysqld that the flush has taken place, even
> though it has not. In this case, the durability of transactions is not
> guaranteed even with the setting 1, and in the worst case, a power outage
> can corrupt InnoDB data. Using a battery-backed disk cache in the SCSI disk
> controller or in the disk itself speeds up file flushes, and makes the
> operation safer. You can also try to disable the caching of disk writes in
> hardware caches.

~~~
stronglikedan
Pardon my ignorance, but do you know if this would affect PG as well, since
it's an OS/hardware sourced issue?

~~~
ddorian43
It's same issue grandparent mentioned.

------
bovermyer
Oh. Apparently I don't know SQL nearly as well as I thought.

~~~
astrodust
You could write an entire book on SQL and have the same thought.

~~~
m_samuel_l
The more you know SQL, the more you don't know SQL.

~~~
emmelaich
As your circle of knowledge increases, so does your circumference of
ignorance.

------
weberc2
SQL seems to be evolving by tacking on features willy-nilly, with no clear
holistic philosophy, and worse, the standards which emerged aren't followed by
all major implementations and even standards which are followed differ starkly
in terms of performance across implementations. The product is that we all
write implementation-specific SQL, which largely defeats the purpose of a
standard language altogether.

I think if someone came along and fixed these major issues and gave it a
modern syntax without significantly degrading performance, it would be
sufficiently different that the XKCD "competing standards" meme would not
apply.

I would love to hear from someone with database specialization.

~~~
greymeister
Browsing the featured added by SQL standards on Wikipedia:

SQL:2003 added XML functions

SQL:2006 more XML!

SQL:2008 XQuery!

SQL:2016 JSON!

My guess it's not so much "willy-nilly" but more a sign of "what was popular."

~~~
keketi
Calling it now: in 2020 they will add YAML support.

~~~
FranOntanaya
WASM for SQL

~~~
zbentley
Definitely blockchain.

------
vermaden
Do not be mislead, MySQL is the PHP of the database world.

[https://slideshare.net/anandology/ten-reasons-to-prefer-
post...](https://slideshare.net/anandology/ten-reasons-to-prefer-postgresql-
to-mysql)

------
misterbowfinger
_The reason I think a single database release has a dramatic effect on the
entire SQL ecosystem is simple: MySQL is the weakest link in the chain._

That's a heckuva deep cut.

I wonder why/how MySQL got so popular, when in my head the go-to is Postgres
(perhaps because I worked on Django/Rails stuff early on). Is it because of
Oracle and their marketing/sales work?

~~~
masklinn
> I wonder why/how MySQL got so popular, when in my head the go-to is Postgres
> (perhaps because I worked on Django/Rails stuff early on). Is it because of
> Oracle and their marketing/sales work?

MySQL's popularity long predates the Oracle acquisition. MySQL got popular
because it was usually available (alongside PHP) on shared hosting and had
very fast read performances (using the default MyISAM storage, at the cost of
write performances or actually caring about your data). What with the web
being front-loaded on read, that made it a pretty good fit.

Meanwhile historically postgres was byzantine to set up and _slow_. It cared
much more for your data and had plenty of features (custom types! procedural
languages! transactional DDL!), but it didn't really work OOTB (still doesn't,
IIRC the base configuration still assumes rotating rust and 128MB RAM or some
shit) and wasn't available on most hosting platforms, especially not the
really cheap shared hosts.

Also while Django defaulted to postgres, I'm pretty sure Rails used to kinda-
sorta default to mysql (I'm talking 10 years back, many plugins/extensions
didn't work properly on pg).

~~~
misterbowfinger
Makes sense, thanks for the thorough answer! The popularity of the LAMP stack
predates me a bit, so I didn't know as much about the history.

------
mwexler
Celso's quote applies equally well to SQLite, which is SQL compliant until
it's not. Glad to see MySQL improvements, but wish SQLite was also getting
similar upgrades. It was a bit depressing to see the solid column of Xs by
SQLite for all these great new features the author was relishing.

~~~
MarkusWinand
> It was a bit depressing to see the solid column of Xs by SQLite

Except WITH [RECURSIVE] which is in SQLite since early 2014. Damn, I forgot to
use that trivia in my MySQL bashing... ;)

Other than that, I met Richard Hipp in person some years back. He is a VERY
pragmatic guy. I wrote an article about it:

[https://use-the-index-luke.com/blog/2014-05/what-i-learned-a...](https://use-
the-index-luke.com/blog/2014-05/what-i-learned-about-sqlite-at-a-postgresql-
conference)

------
dhd415
I'm happy to see that MySQL is progressing, but I think that enumerating its
adherence to the latest SQL standards overlooks some of its other serious
flaws from the perspective of operations and reliability. Below are two of the
worst operational situations I've encountered with MySQL. I've run several
other databases in high-volume production environments and never encountered
those kinds of problems. That MySQL's developers would have made these choices
that result in those kinds of problems is truly mind-boggling to me.

* If you have pre-5.6.4 date/time columns in your table and perform any kind of ALTER TABLE statement on that table (even one that doesn't involve the date/time columns), it will TAKE A TABLE LOCK AND REWRITE YOUR ENTIRE TABLE to upgrade to the new date/time types. In other words, your carefully-crafted online DDL statement will become fully offline and blocking for the entirety of the operation. To add insult to injury, the full table upgrade was UNAVOIDABLE until 5.6.24 when an option (still defaulted to off!) was added to decline the automatic upgrade of date/time columns. If you couldn't upgrade to 5.6.24, you had two choices with any table with pre-5.6.4 types: make no DDL changes of any kind to it or accept downtime while the full table rewrite was performed. To be as fair as possible, this is documented in the MySQL docs, but it is mind-blowing to me that any database team would release this behavior into production. In other words, in what world is the upgrade of date/time types to add a bit more fractional precision so important that all online DDL operations on that table will be silently, automatically, and unavoidably converted to offline operations in order to perform the upgrade? To me, this is indicative of the same mindset that released MySQL for so many years with the unsafe and silent downgrading of data as the default mode of operation.

* Dropping a table takes a global lock that prevents the execution of ANY QUERY until the underlying files for the table are removed from the filesystem. Under many circumstances, this would go unnoticed, but I experienced a 7-minute production outage when I dropped a 700GB table that was no longer used. Apparently, this delay is due to the time it takes the underlying Linux filesystems to delete the large table file. This was an RDS instance, so I had no visibility into the filesystem used and it was probably exacerbated by the EBS backing for the RDS instance, but still, what database takes out a GLOBAL LOCK TO DROP A TABLE? After the incident, I googled for and found this description of the problem ([https://www.percona.com/blog/2009/06/16/slow-drop-table/](https://www.percona.com/blog/2009/06/16/slow-drop-table/)) which isn't well-documented. It's almost as if you have to anticipate every possible way in which MySQL could screw you and then google for it if you want to avoid production downtime.

------
gremlinsinc
Anyone know when this will make it to AUR on Arch? I've been waiting for CTEs
for a long time!

------
phurley
I have one in my backpack I am parking now.

------
verandaguy_alt
Are the feature comparison tables in this article supposed to reflect the
state of SQL databases as of several years ago?

I've been using Postgres very consistently for the past half decade (as a dev,
not a DBA) and it's had a `GROUP BY` and `WHERE` clause for at least as long.

I'm not totally sure what " _base table_ `UNIQUE`" is supposed to mean in this
context, but if it's just the bare `UNIQUE` constraint, Postgres has also had
that for a long, long while.

~~~
Klathmon
Yeah, later on in the article it has an "X" for PostgreSQL for
"JSON_OBJECTAGG" and "JSON_ARRAYAGG" and I'm 100% sure postgres has the former
(I used it like 10 minutes ago), and like 99% sure it has the latter.

~~~
MarkusWinand
[https://www.postgresql.org/search/?q=json_objectagg](https://www.postgresql.org/search/?q=json_objectagg)

But there is a patch pending for this. Not sure if this gonna be included in
11.

~~~
Klathmon
It's json_object_agg in PostgreSQL

[https://www.postgresql.org/docs/current/static/functions-
agg...](https://www.postgresql.org/docs/current/static/functions-
aggregate.html)

~~~
MarkusWinand
Those gory details I've mentioned in the article.

This is how standard SQL JSON_OBJECTAGG works: [https://modern-
sql.com/blog/2017-06/whats-new-in-sql-2016#js...](https://modern-
sql.com/blog/2017-06/whats-new-in-sql-2016#json_objectagg)

It can actually do much more:

<JSON object aggregate constructor> ::= JSON_OBJECTAGG <left paren> <JSON name
and value> [ <JSON constructor null clause> ] [ <JSON key uniqueness
constraint> ] [ <JSON output clause> ] <right paren>

This free technical report of ISO has some examples:
[http://standards.iso.org/ittf/PubliclyAvailableStandards/c06...](http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip)

~~~
Klathmon
I haven't done any exact test, but it looks identical to how the
json_object_agg function in postgres works, but with a different syntax.

Is it really that different to not even warrant a note in the table?

Clearly you know better than I do, but the big red X surprised me as the
fantastic JSON support is one of the reasons why I tend to reach for Postgres
so often over other DB engines.

~~~
MarkusWinand
A big red X doesn't mean the database doesn't have other, similar but
proprietary functionality. It just means: it doesn't support this function in
a standard confirming way yet.

There is nothing against using non-standard functionality in that case. In
this particular case. PostgreSQL got JSON support (~2012?) long before it was
added to the standard (2016). Obviously, their JSON functions differ from the
standard (as they didn't lobby their functions into the standard).

However, slowly but surely database will add the standard functionality, which
makes life easier.

Having that said, the standard JSON_OBJECTAGG is more powerful as it gives you
control over null handling (NULL ON NULL -vs- ABSENT ON NULL) and allows
checking for duplicate keys ([(WITH|WITHOUT) UNIQUE KEYS]).

~~~
masklinn
Maybe add some little footnotes to the X? It took me a few minutes of thinking
"Of course postgres has json object aggregation here it's working perfectly
and it's documented" before I realised that sure it existed but it's not the
standard version, at all, which is probably why it's marked as a no, because
it's not Troel's page and Markus really really cares about the standard.

