14 ms·

One Giant Leap for SQL: MySQL 8.0 Released

136 points by dhd415 8y ago

ocdtrekkie 8y ago
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.
- liveoneggs 8y ago
  I agree. The MySQL -> MariaDB stuff is bonkers. I would like to read the discussion of it on the lists but can't be bothered to look for it.
  - RealSpamapS 8y ago
    It was a multi-year effort including us asking specifically for the policy to be spelled out at why there could be only one. IMO the Debian security team wrote a policy specifically to target Oracle in this specific instance, as a result. Digging it out is painful, as it all happened back in 2016, but you can start here: https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=793316 https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=793316
    - liveoneggs 8y ago
      I also hold the opinion that MariaDB's failure to simply rename key "mysql" files/dirs to "mariadb" was intentionally confusing and an effort to steal market share. Percona has been a much better citizen with its fork.
- torstenvl 8y ago
  It frankly ought to be a crime. Maybe someone can prosecute the responsible Debian maintained under an "exceeds authority" theory of the Computer Fraud and Abuse Act?
  - torstenvl 8y ago
    I'm always amazed at what gets downvoted. Does anyone want to explain why they think it's acceptable to install software on someone's computer without their affirmative and informed consent? Or articulate a legal argument as to why exceeding granted authority on a computer system isn't a violation of the CFAA? 18 U.S.C. § 1030(a)(5) makes it a crime to "knowingly cause[] the transmission of a program, ... code, or command, and as a result of such conduct, intentionally cause[] ['any impairment to the ... availability of ... a program...'] without authorization, to a ['computer ... which is used in or affecting interstate or foreign commerce or communication']." The argument that Debian has "authorization" to install MariaDB simply because it's in the documentation that it will do so when told to install MySQL seems pretty thin.
    - j605 8y ago
      I think you would need to read the GPL to figure out that Debian can do this and you have no recourse (no warranty clause is pretty clear on this).
      - torstenvl 8y ago
        Explain to me why you think the GPL matters here. Last I checked, the GPL pertains to the program (i.e., apt-get). That is not where the problem is. Additionally, contracts are not carte blanche to violate criminal law. To the extent that a contract provision would purport to allow violating a criminal law, it would be unenforceable.
    - ocdtrekkie 8y ago
      I am steaming mad at the Debian release team and think they made an amazingly, amazingly poor decision. I do not think they should go to jail for it. (That being said, I wouldn't mind if Oracle cease-and-desisted them for installing someone else's software using their trademark as the package name.)
- astrodust 8y ago
  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 8y ago
    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.
    - astrodust 8y ago
      Fedora made the same decision, so I don't know if you can blame Debian per-se. This was a community wide problem.
      - Conan_Kudo 8y ago
        Not exactly. Fedora maintains MySQL as "community-mysql"[1], and MariaDB uses the "mariadb"[2] package. [1]: https://apps.fedoraproject.org/packages/community-mysql https://apps.fedoraproject.org/packages/community-mysql [2]: https://apps.fedoraproject.org/packages/mariadb https://apps.fedoraproject.org/packages/mariadb
        astrodust 8y ago
        `dnf install mysql` gives you MariaDB by default.
    - kasabali 8y ago
      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.
- 0x0 8y ago
  When you upgrade to a new major release of Debian, you should take a look at the release notes. The mysql->mariadb migration has its own chapter: https://www.debian.org/releases/stable/amd64/release-notes/ch-whats-new.en.html#mariadb-replaces-mysql https://www.debian.org/releases/stable/amd64/release-notes/c... I guess the fact that mariadb now owns the "mysql-server" package name is a side-effect of how apt manages upgrades and migrations. It's probably the best way to ensure existing users end up with a supported mysql-ish server after the upgrade, instead of being silently stranded on an obsolete debian8 package without receiving security updates.
  - ocdtrekkie 8y ago
    Why does upgrading Debian need to replace my other software? If I have... Gimp installed, and I upgrade Windows from 7 to 10, I'd be understandably annoyed if Microsoft took the opportunity to replace Gimp with Adobe Photoshop. (And I'd be confused too, since neither Gimp nor Photoshop are Microsoft's. Yet here Debian has replaced software they don't own with other software they don't own, all without my permission.) mysql-server doesn't belong to MariaDB, they can't "own" it. Doesn't Oracle hold a trademark on it?
    - mprev 8y ago
      Debian is an operating system that comes with its own curated library of software. To answer why it upgrades user software when you update the underlying operating system, you need to look at how that software is compiled in a Linux distribution. Whereas Windows apps tend to standalone, bringing with them any libraries they need, Debian tends to have one copy of each library, runtime, and so on. If you upgrade Debian, you upgrade way more than just the kernel, the gnu tools etc. You also upgrade, for example, the Python, the windowing system, the networking stack. Apps that rely on these must also be upgraded in order to work with the new versions of whatever other packages they rely on. It’s a domino effect and it keeps on knocking right up to GIMP or, in this case, MySQL. My answer doesn’t explain why this particular choice was made but I hope it shows how Debian can do that sort of thing whereas Windows wouldn’t.
- jrq 8y ago
  I don't think this is entirely debian maintainers fault. Package names aren't functions, so the string doesn't necessarily map to the thing you want. Left-pad is the modern analog to what I mean here. Mariadb probably put in for the package name, tbh I can't be bothered to go find out, but I'd bet anything debian team didn't just arbitrarily make that call for users and ship it. 100% guarantee that. I know your feeling though, that's the sort of thing that should be printing a warning or something when it happens.
  - ocdtrekkie 8y ago
    Upon later research, I am not sure of the value of your guarantee: https://www.debian.org/News/2017/20170617 https://www.debian.org/News/2017/20170617 In "Stretch", the default MySQL variant is now MariaDB. The replacement of packages for MySQL 5.5 or 5.6 by the MariaDB 10.1 variant will happen automatically upon upgrade.
    - jrq 8y ago
      So then it wasn't unannounced... Seems to me this is a non issue then. I guess not everybody reads those, and I've already conceded that its something that ought to warrant a warning prompt or something. But they didn't just change it for no reason.
      - kasabali 8y ago
        Warning prompt is right at the command line tool: $ mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 32 Server version: 10.1.26-MariaDB-0+deb9u1 Debian 9.1 Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. $ mysql --version mysql Ver 15.1 Distrib 10.1.26-MariaDB, for debian-linux-gnu (i686) using readline 5.2 It is pretty clear to anybody ever run the tool that it is MariaDB.
- rlpb 8y ago
  Debian MySQL maintainer here. We wanted to maintain both MySQL and MariaDB in Debian stable. However the Debian release team forced the removal of MySQL on the basis that MariaDB only would suffice. We disagreed with this but they overruled us. mysql-server in Debian stable became a transitional package in Debian stable (only; this doesn't affect testing or unstable) in order to make the upgrade work correctly. It does force users with MySQL server to crossgrade to MariaDB server, and does lead to "mysql-server" being a misnomer in Debian stable. "default-mysql-server" being MariaDB is also misleading, IMHO, but it's the best we could come up with because there isn't any other collective term that covers both MySQL and MariaDB. We didn't want this, but it was forced upon us by the Debian release team. Sorry for the trouble. As others have said, we did our best to explain this in the release notes. We continue to maintain both MySQL and MariaDB in Debian unstable, which remains free of this mess.
  - ocdtrekkie 8y ago
    I totally understand the pain of being overruled by bad decisions, and another person here had also indicated a bit earlier that this was the case. If I could still edit the comment, I would revise "burning white hot rage I have for Debian" to "burning white hot rage I have for the Debian release team". Alas I cannot. :(
    - rlpb 8y ago
      > I would revise "burning white hot rage I have for Debian" to "burning white hot rage I have for the Debian release team". Debian, as a project, appointed the release team and granted them the authority to make this decision. Therefore, IMHO, it's perfectly fair to attribute "Debian" to the decision. As long as you understand that it's not the Debian MySQL/MariaDB maintainers :)
  - tzs 8y ago
    I can see what the Debian release team was trying to do...but doesn't Debian also have a legal team? Did the release team consult with the legal team about having a package named "mysql-server" that uses a trademark owned by Oracle and installs software that is a competitor to the Oracle software the trademark refers to? Oracle is litigious. This seems to be deep into "poke the bear" territory.
- saidmasoud 8y ago
  FWIW, this is also the case on RHEL-based systems.
  - Conan_Kudo 8y ago
    As far as I know, no one has stepped up to maintain MySQL for RHEL/CentOS. RHEL/CentOS 7 shipped with MariaDB. There's nothing stopping _anyone_ from volunteering to maintain "community-mysql" for RHEL/CentOS in EPEL.
- edwinyzh 8y ago
  Unbelievable! I must say. Is Ubuntu (which is based on Debian) affected by this issue?
- deleted 8y ago
  [deleted]
verandaguy_alt 8y ago
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.
- cwyers 8y ago
  You aren't reading that table correctly. It's about functional dependency checking, not about whether you can use a WHERE clause.
- Klathmon 8y ago
  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 8y ago
    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 8y ago
      It's json_object_agg in PostgreSQL https://www.postgresql.org/docs/current/static/functions-aggregate.html https://www.postgresql.org/docs/current/static/functions-agg...
      - irishsultan 8y ago
        Given the name difference it's possible that the semantics are slightly different.
bovermyer 8y ago
Oh. Apparently I don't know SQL nearly as well as I thought.
- astrodust 8y ago
  You could write an entire book on SQL and have the same thought.
  - m_samuel_l 8y ago
    The more you know SQL, the more you don't know SQL.
    - emmelaich 8y ago
      As your circle of knowledge increases, so does your circumference of ignorance.
    - hermitdev 8y ago
      I used to have the entire reference set for DB2, and it didn't accurately describe the actual behavior of the RDBMS. Several other documented configurations for DB2 components didnt work and after months of work with IBM support were later deemed unsupported. This was circa 2002-2004 while I was an intern. Havent used DB2 since, so dont know if the situation has changed.
misterbowfinger 8y ago
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 8y ago
  > 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).
  - deleted 8y ago
    [deleted]
  - TurningCanadian 8y ago
    MySQL makes some of the same assumptions about memory. You have to tune it if you want optimal performance. https://www.percona.com/blog/2016/05/03/best-practices-for-configuring-optimal-mysql-memory-usage/ https://www.percona.com/blog/2016/05/03/best-practices-for-c...
  - misterbowfinger 8y ago
    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.
weberc2 8y ago
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 8y ago
  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 8y ago
    Calling it now: in 2020 they will add YAML support.
    - FranOntanaya 8y ago
      WASM for SQL
      - zbentley 8y ago
        Definitely blockchain.
  - astrodust 8y ago
    Hopefully one day GIS functions and types get standardized.
  - weberc2 8y ago
    This is pretty much what I mean by "willy-nilly". Little concern seems to be paid to how well it integrates into some holistic information access theory or paradigm. I would expect some adherence to relational algebra, and if that isn't sufficiently powerful, I would like a transition to some more powerful theory rather than welding on the new hotness. I'm not saying I could do it better, mind you.
mwexler 8y ago
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 8y ago
  > 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-about-sqlite-at-a-postgresql-conference https://use-the-index-luke.com/blog/2014-05/what-i-learned-a...
ausjke 8y ago
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.
- Manozco 8y ago
  I did not investigate MySQL code (or other product) but I would be very surprised if PostgreSQL is the only one impacted by this issue.
  - ausjke 8y ago
    by reading the lwn article it seems postgresql is hit especially hard per its design and "heavy" use of fsync, I have yet to find similar impact on mysql.
    - olavgg 8y ago
      All SQL databases are "heavy" users of fsync. Though, this problem is not relevant on FreeBSD nor Illumos. Error handling for a operating system is really complex stuff. I'm surprised that this isn't working properly, fsync is not about speed, but reliability. Userland applications expect it to work and behave as intended.
      - ausjke 8y ago
        the key is error reporting
  - macdice 8y ago
    Yes that seems unlikely. Almost all Unix software uses buffered IO, including several other databases or database-like things including package managers, DNS servers, mail servers, ..., and MySQL does too by default. It even uses it when you choose O_DIRECT (though I'm not sure if that's exposed to write-back error eating risk or not). That said, as several people have said, this is a very unlikely failure mode. When filesystems die, you know about it. The type of transient failure required to lose data this way is quite unlikely.
dhd415 8y ago
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.
phurley 8y ago
I have one in my backpack I am parking now.
gremlinsinc 8y ago
Anyone know when this will make it to AUR on Arch? I've been waiting for CTEs for a long time!
macdice 8y ago
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 8y ago
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 8y ago
  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 8y ago
    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.
vermaden 8y ago
Do not be mislead, MySQL is the PHP of the database world. https://slideshare.net/anandology/ten-reasons-to-prefer-postgresql-to-mysql https://slideshare.net/anandology/ten-reasons-to-prefer-post...

136 points by dhd415 8y ago

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.

liveoneggs 8y ago
I agree. The MySQL -> MariaDB stuff is bonkers. I would like to read the discussion of it on the lists but can't be bothered to look for it.
- RealSpamapS 8y ago
  It was a multi-year effort including us asking specifically for the policy to be spelled out at why there could be only one. IMO the Debian security team wrote a policy specifically to target Oracle in this specific instance, as a result. Digging it out is painful, as it all happened back in 2016, but you can start here: https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=793316 https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=793316
  - liveoneggs 8y ago
    I also hold the opinion that MariaDB's failure to simply rename key "mysql" files/dirs to "mariadb" was intentionally confusing and an effort to steal market share. Percona has been a much better citizen with its fork.
torstenvl 8y ago
It frankly ought to be a crime. Maybe someone can prosecute the responsible Debian maintained under an "exceeds authority" theory of the Computer Fraud and Abuse Act?
- torstenvl 8y ago
  I'm always amazed at what gets downvoted. Does anyone want to explain why they think it's acceptable to install software on someone's computer without their affirmative and informed consent? Or articulate a legal argument as to why exceeding granted authority on a computer system isn't a violation of the CFAA? 18 U.S.C. § 1030(a)(5) makes it a crime to "knowingly cause[] the transmission of a program, ... code, or command, and as a result of such conduct, intentionally cause[] ['any impairment to the ... availability of ... a program...'] without authorization, to a ['computer ... which is used in or affecting interstate or foreign commerce or communication']." The argument that Debian has "authorization" to install MariaDB simply because it's in the documentation that it will do so when told to install MySQL seems pretty thin.
  - j605 8y ago
    I think you would need to read the GPL to figure out that Debian can do this and you have no recourse (no warranty clause is pretty clear on this).
    - torstenvl 8y ago
      Explain to me why you think the GPL matters here. Last I checked, the GPL pertains to the program (i.e., apt-get). That is not where the problem is. Additionally, contracts are not carte blanche to violate criminal law. To the extent that a contract provision would purport to allow violating a criminal law, it would be unenforceable.
  - ocdtrekkie 8y ago
    I am steaming mad at the Debian release team and think they made an amazingly, amazingly poor decision. I do not think they should go to jail for it. (That being said, I wouldn't mind if Oracle cease-and-desisted them for installing someone else's software using their trademark as the package name.)
astrodust 8y ago
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 8y ago
  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.
  - astrodust 8y ago
    Fedora made the same decision, so I don't know if you can blame Debian per-se. This was a community wide problem.
    - Conan_Kudo 8y ago
      Not exactly. Fedora maintains MySQL as "community-mysql"[1], and MariaDB uses the "mariadb"[2] package. [1]: https://apps.fedoraproject.org/packages/community-mysql https://apps.fedoraproject.org/packages/community-mysql [2]: https://apps.fedoraproject.org/packages/mariadb https://apps.fedoraproject.org/packages/mariadb
      - astrodust 8y ago
        `dnf install mysql` gives you MariaDB by default.
  - kasabali 8y ago
    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.
0x0 8y ago
When you upgrade to a new major release of Debian, you should take a look at the release notes. The mysql->mariadb migration has its own chapter: https://www.debian.org/releases/stable/amd64/release-notes/ch-whats-new.en.html#mariadb-replaces-mysql https://www.debian.org/releases/stable/amd64/release-notes/c... I guess the fact that mariadb now owns the "mysql-server" package name is a side-effect of how apt manages upgrades and migrations. It's probably the best way to ensure existing users end up with a supported mysql-ish server after the upgrade, instead of being silently stranded on an obsolete debian8 package without receiving security updates.
- ocdtrekkie 8y ago
  Why does upgrading Debian need to replace my other software? If I have... Gimp installed, and I upgrade Windows from 7 to 10, I'd be understandably annoyed if Microsoft took the opportunity to replace Gimp with Adobe Photoshop. (And I'd be confused too, since neither Gimp nor Photoshop are Microsoft's. Yet here Debian has replaced software they don't own with other software they don't own, all without my permission.) mysql-server doesn't belong to MariaDB, they can't "own" it. Doesn't Oracle hold a trademark on it?
  - mprev 8y ago
    Debian is an operating system that comes with its own curated library of software. To answer why it upgrades user software when you update the underlying operating system, you need to look at how that software is compiled in a Linux distribution. Whereas Windows apps tend to standalone, bringing with them any libraries they need, Debian tends to have one copy of each library, runtime, and so on. If you upgrade Debian, you upgrade way more than just the kernel, the gnu tools etc. You also upgrade, for example, the Python, the windowing system, the networking stack. Apps that rely on these must also be upgraded in order to work with the new versions of whatever other packages they rely on. It’s a domino effect and it keeps on knocking right up to GIMP or, in this case, MySQL. My answer doesn’t explain why this particular choice was made but I hope it shows how Debian can do that sort of thing whereas Windows wouldn’t.
jrq 8y ago
I don't think this is entirely debian maintainers fault. Package names aren't functions, so the string doesn't necessarily map to the thing you want. Left-pad is the modern analog to what I mean here. Mariadb probably put in for the package name, tbh I can't be bothered to go find out, but I'd bet anything debian team didn't just arbitrarily make that call for users and ship it. 100% guarantee that. I know your feeling though, that's the sort of thing that should be printing a warning or something when it happens.
- ocdtrekkie 8y ago
  Upon later research, I am not sure of the value of your guarantee: https://www.debian.org/News/2017/20170617 https://www.debian.org/News/2017/20170617 In "Stretch", the default MySQL variant is now MariaDB. The replacement of packages for MySQL 5.5 or 5.6 by the MariaDB 10.1 variant will happen automatically upon upgrade.
  - jrq 8y ago
    So then it wasn't unannounced... Seems to me this is a non issue then. I guess not everybody reads those, and I've already conceded that its something that ought to warrant a warning prompt or something. But they didn't just change it for no reason.
    - kasabali 8y ago
      Warning prompt is right at the command line tool: $ mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 32 Server version: 10.1.26-MariaDB-0+deb9u1 Debian 9.1 Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. $ mysql --version mysql Ver 15.1 Distrib 10.1.26-MariaDB, for debian-linux-gnu (i686) using readline 5.2 It is pretty clear to anybody ever run the tool that it is MariaDB.
rlpb 8y ago
Debian MySQL maintainer here. We wanted to maintain both MySQL and MariaDB in Debian stable. However the Debian release team forced the removal of MySQL on the basis that MariaDB only would suffice. We disagreed with this but they overruled us. mysql-server in Debian stable became a transitional package in Debian stable (only; this doesn't affect testing or unstable) in order to make the upgrade work correctly. It does force users with MySQL server to crossgrade to MariaDB server, and does lead to "mysql-server" being a misnomer in Debian stable. "default-mysql-server" being MariaDB is also misleading, IMHO, but it's the best we could come up with because there isn't any other collective term that covers both MySQL and MariaDB. We didn't want this, but it was forced upon us by the Debian release team. Sorry for the trouble. As others have said, we did our best to explain this in the release notes. We continue to maintain both MySQL and MariaDB in Debian unstable, which remains free of this mess.
- ocdtrekkie 8y ago
  I totally understand the pain of being overruled by bad decisions, and another person here had also indicated a bit earlier that this was the case. If I could still edit the comment, I would revise "burning white hot rage I have for Debian" to "burning white hot rage I have for the Debian release team". Alas I cannot. :(
  - rlpb 8y ago
    > I would revise "burning white hot rage I have for Debian" to "burning white hot rage I have for the Debian release team". Debian, as a project, appointed the release team and granted them the authority to make this decision. Therefore, IMHO, it's perfectly fair to attribute "Debian" to the decision. As long as you understand that it's not the Debian MySQL/MariaDB maintainers :)
- tzs 8y ago
  I can see what the Debian release team was trying to do...but doesn't Debian also have a legal team? Did the release team consult with the legal team about having a package named "mysql-server" that uses a trademark owned by Oracle and installs software that is a competitor to the Oracle software the trademark refers to? Oracle is litigious. This seems to be deep into "poke the bear" territory.
saidmasoud 8y ago
FWIW, this is also the case on RHEL-based systems.
- Conan_Kudo 8y ago
  As far as I know, no one has stepped up to maintain MySQL for RHEL/CentOS. RHEL/CentOS 7 shipped with MariaDB. There's nothing stopping _anyone_ from volunteering to maintain "community-mysql" for RHEL/CentOS in EPEL.
edwinyzh 8y ago
Unbelievable! I must say. Is Ubuntu (which is based on Debian) affected by this issue?
deleted 8y ago
[deleted]

verandaguy_alt 8y ago

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.

cwyers 8y ago
You aren't reading that table correctly. It's about functional dependency checking, not about whether you can use a WHERE clause.
Klathmon 8y ago
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 8y ago
  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 8y ago
    It's json_object_agg in PostgreSQL https://www.postgresql.org/docs/current/static/functions-aggregate.html https://www.postgresql.org/docs/current/static/functions-agg...
    - irishsultan 8y ago
      Given the name difference it's possible that the semantics are slightly different.

bovermyer 8y ago

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

astrodust 8y ago
You could write an entire book on SQL and have the same thought.
- m_samuel_l 8y ago
  The more you know SQL, the more you don't know SQL.
  - emmelaich 8y ago
    As your circle of knowledge increases, so does your circumference of ignorance.
  - hermitdev 8y ago
    I used to have the entire reference set for DB2, and it didn't accurately describe the actual behavior of the RDBMS. Several other documented configurations for DB2 components didnt work and after months of work with IBM support were later deemed unsupported. This was circa 2002-2004 while I was an intern. Havent used DB2 since, so dont know if the situation has changed.

misterbowfinger 8y ago

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 8y ago
> 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).
- deleted 8y ago
  [deleted]
- TurningCanadian 8y ago
  MySQL makes some of the same assumptions about memory. You have to tune it if you want optimal performance. https://www.percona.com/blog/2016/05/03/best-practices-for-configuring-optimal-mysql-memory-usage/ https://www.percona.com/blog/2016/05/03/best-practices-for-c...
- misterbowfinger 8y ago
  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.

weberc2 8y ago

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 8y ago
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 8y ago
  Calling it now: in 2020 they will add YAML support.
  - FranOntanaya 8y ago
    WASM for SQL
    - zbentley 8y ago
      Definitely blockchain.
- astrodust 8y ago
  Hopefully one day GIS functions and types get standardized.
- weberc2 8y ago
  This is pretty much what I mean by "willy-nilly". Little concern seems to be paid to how well it integrates into some holistic information access theory or paradigm. I would expect some adherence to relational algebra, and if that isn't sufficiently powerful, I would like a transition to some more powerful theory rather than welding on the new hotness. I'm not saying I could do it better, mind you.

mwexler 8y ago

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 8y ago
> 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-about-sqlite-at-a-postgresql-conference https://use-the-index-luke.com/blog/2014-05/what-i-learned-a...

ausjke 8y ago

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.

Manozco 8y ago
I did not investigate MySQL code (or other product) but I would be very surprised if PostgreSQL is the only one impacted by this issue.
- ausjke 8y ago
  by reading the lwn article it seems postgresql is hit especially hard per its design and "heavy" use of fsync, I have yet to find similar impact on mysql.
  - olavgg 8y ago
    All SQL databases are "heavy" users of fsync. Though, this problem is not relevant on FreeBSD nor Illumos. Error handling for a operating system is really complex stuff. I'm surprised that this isn't working properly, fsync is not about speed, but reliability. Userland applications expect it to work and behave as intended.
    - ausjke 8y ago
      the key is error reporting
- macdice 8y ago
  Yes that seems unlikely. Almost all Unix software uses buffered IO, including several other databases or database-like things including package managers, DNS servers, mail servers, ..., and MySQL does too by default. It even uses it when you choose O_DIRECT (though I'm not sure if that's exposed to write-back error eating risk or not). That said, as several people have said, this is a very unlikely failure mode. When filesystems die, you know about it. The type of transient failure required to lose data this way is quite unlikely.

dhd415 8y ago

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.

phurley 8y ago

I have one in my backpack I am parking now.

gremlinsinc 8y ago

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

macdice 8y ago

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 8y ago

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 8y ago
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 8y ago
  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.

vermaden 8y ago

Do not be mislead, MySQL is the PHP of the database world. https://slideshare.net/anandology/ten-reasons-to-prefer-postgresql-to-mysql https://slideshare.net/anandology/ten-reasons-to-prefer-post...