MySQL vs. MariaDB: Reality Check | Hacker News Reader

MySQL vs. MariaDB: Reality Check

percona.com

161 pointstpetry64 comments

who says MariaDB doesn't work with ProxySQL?

Also, this statement is total BS:

"The risk of moving to MariaDB Server if you aren’t using newer MySQL features may be minimal, but the risk of moving out of MariaDB Server to MySQL is very prevalent."

Here is the correct statement:

"The risk of moving to MariaDB Server if you aren’t using newer MySQL features may be minimal, and the risk of moving to MySQL server if you aren't using newer MariaDB features is also minimal."

I switch between multiple versions of MySQL and MariaDB all day long. If you aren't using specific things like MySQL's JSON type or NDB storage engine or expecting CHECK constraints to enforce on MySQL (oddly omitted from this feature comparison!), there is nothing different at all from a developer point of view, beyond the default values of flags which honestly change more between MySQL releases than anything else.

As the maintainer of SQLAlchemy I think MySQL more often makes backwards-incompatible changes more often than MariaDB, such as just from 5.7.19 to 5.7.20 they decided to change the name of "@tx_isolation" to "@transaction_isolation" (and no, "@transaction_isolation" is not in 5.7.19, they just changed the name on a point release) and start emitting a prominent, scary warning when you use @tx_isolation that is breaking people's CI builds - on a point release.

The reason a vendor like Percona or a linux distro chooses to favor MySQL or MariaDB is mostly about that vendor's relationship with Oracle (or lack thereof) more than anything else. I'm stuck using both all the time but the MariaDB community certainly feels more accessible than trying to get through to people working at Oracle.

"The reason a vendor like Percona or a linux distro chooses to favor MySQL or MariaDB is mostly about that vendor's relationship"

Close. Percona is a consulting firm specializing in MySQL (expanding to other things). Percona's race horse is MySQL the actual company and actual maintainers. They view MariaDB as a type of competition against their friends at MySQL. Good luck spotting the differences between Maria vs Mysql. I have gathered that from the many SFMysql meetups I have attended. The Mysql guys would show off their newest feature that beats MariaDB.

Percona is amazing and without the pt-online-schema-change tool, I would probably be a bar tender. If you ever get in a bad database bind they are worth every penny.

Looking from the outside as a user and package maintainer, there is no "MySQL" company. There's just Oracle now. You can't even download MySQL's official Python driver (mysql-connector-python) from Pypi because Oracle legal is not comfortable with it. If you are looking to do business with MySQL over MariaDB, it means you are looking to get in with Oracle. Is that not accurate?

This is btw. changing very soon. All lawyers agreed and mysql-trunk for-profit will be back on pypi as soon as we fix a few a tiny technical issues (I lead the team working on Python and other things in MySQL)

That would be amazing because we've been asking for this for literally years.

Not only you ... :-/

> Percona is amazing and without the pt-online-schema-change tool, I would probably be a bar tender

Without the pt-online-schema-change, you would likely use gh-ost :-)

Product Manager for the MySQL server here.

w.r.t. tx_isolation:

In MySQL 8.0, it is called transaction_isolation which is the name used by configuration files. MySQL 5.7 supports both names, but now warns when using the old name.

Supporting old+new names is required to support distributed apps which may upgrade a shard at a time (i.e. you want your code to work on both MySQL 5.7 and 8.0). But there is a deprecation warning to let you know which version is preferred.

Calling it an incompatible change is perhaps a little strong. The idea behind the deprecation warning is to give you as much notice as possible.

> Calling it an incompatible change is perhaps a little strong.

Definitions of backwards incompatibility are either tautological or disingenuous (provided we talk about documented behavior).

The right way to do that change would have been to allow both names without warnings in a point/minor release (with the docs stating where the project is headed), add the depreciation warning with the next semver major one, and remove the old name on the next next semver major release.

That though is a really long lifecycle for a product that may have a six month qualification and burn-in periods for a production enterprise implementation . You’re basically saying a change of config file parameter should take a year-plus?

MySQL releases are 2-3 years. So it would be 4-6 years wait in this case :-)

Just so you know, that warning broke Openstack builds in CI which fail for warnings like these. Other users have reported it elsewhere and I have users begging me to release SQLAlchemy 1.1.15 so that it can fix "the bug" ASAP for them.

Unfortunately, "database suddenly emits a prominent, unconditional warning upon upgrade to 5.7.20" is a breaking change in many situations.

We are looking to add something to filter warnings in the future. Thx for the feedback.

Sorry for my unnecessarily harsh comment yesterday. A great course of action would be to put those deprecation warnings behind an opt-in flag. That would let curious people discover what they must change in order to migrate to future versions without breaking anything out of the box.

Mike, given all your experience, do you have a preferred relational db? I'm assuming the answer is "it depends" but I feel like, say, MySQL and Postgres are fairly indentical in terms of usecase.

I'm assuming you know pretty much every dark corner of the dbs you have engines for in sqla. It must be a little hard forever supporting strange breakages like your example above when it's a db you don't care for as much.

If I was doing brand new development somewhere I'm sure I'd use Postgresql, since from a developer point of view it's the most consistent and flexible. While for the last few years I've worked way more with MySQL / MariaDB and at the moment the MySQL side of things is a bit more familiar to me, I still appreciate PG's vastly superior query planner and index features. Certainly in the SQLAlchemy world when i want to see how a database does something "correctly", Postgresql is where you go to see the "correct" way to do something (such as, the correct answer for "SELECT NULL IN (SELECT 1 WHERE 1=0)" - the answer is false, not NULL, or when I had to convince MariaDB developers, who just added real CHECK constraints in 10.2, that I should be able to drop a column that has a single-column CHECK constraint on it without first dropping the constraint, PG serves as the canonical example for "yes this is supposed to work").

I'm personally a little annoyed at PG's efforts to turn SQL into half an OO programming language (see http://ledgersmbdev.blogspot.com/2012/08/postgresql-or-model... for examples), but for selfish reasons; features like these are meant as replacements for what you get from a tool like SQLAlchemy Core and the unusual syntaxes they have are also really hard to implement yet I continually get asked to implement more of them. These aren't reasons I wouldn't use Postgresql it's just this little cloud of "ugh" that hangs over me when I really have to dig into PG :).

It also makes me cringe a bit the way Postgresql spawns a child process on the server for every database connection still. MySQL has very cheap connections and that's still a pretty compelling reason to keep it in mind for some kinds of applications (applications called Openstack which eat up thousands of connections...).

In the closed source world, MS SQL Server has really been getting a lot easier to use and I'm very impressed at their linux version, I have it running in docker and it is 100% exactly the same as the windows version as far as SQL / client interaction. None of this "MySQL default casing conventions are OS-dependent" stuff (see https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sens... , and prepare to be amazed what hacky cruft still lives on within the MySQL ecosystem).

> I'm personally a little annoyed at PG's efforts to turn SQL into half an OO programming language (see http://ledgersmbdev.blogspot.com/2012/08/postgresql-or-model.... for examples)

I read that link and didn't get it - has anyone a fuller explanation or other links to recommend?

I guess this is one of the more interesting examples to look at:

    select * from inventory_item i where (i.cogs_account).control_code = '5500';

You're only referencing 1 table in the query (inventory_item), but then i.cogs_account runs a query on a related table.

It doesn't seem like a great idea to me. You can't really tell what's happening and there's a huge performance impact in doing it. The query planner is going to be a bit limited in what it can do.

The example following where you have a save procedure against a column is a bit baffling.

    SELECT (i.save).*
    FROM (SELECT (row(null, 3, 1, 2, 'TEST124', 
                'Inventory testing item 2', 1, 2, 
                true)::inventory_item).save) i;

Wow, I'd never seen that style of defining the relationships as computed columns inside a table before — it's somewhat....terrifying.

Think I'll stick to SQLA on that front thanks!

Forgive me for being blunt but I am sorry, can you please tell me who zzzeek is? Why does everyone know him but first name?

Click on his username to read his profile which includes a link to his website http://techspot.zzzeek.org

> I switch between multiple versions of MySQL and MariaDB all day long.

Mike, maybe I misundertood this, but I think they meant actually moving /var/lib/mysql between maria/mysql.

Oh you might be right about that. I'd be pretty nervous going in either direction on top of constant datafiles. Just mysqldump and be done with it.

My understanding is that MariaDB is using an updated replication format which is not readable by Oracle MySQL or the Percona fork of MySQL. So you could not maintain a MySQL slave of a MariaDB instance, and would have to take downtime to switch from MariaDB to MySQL.

(This is only be true if you're using GTIDs in MariaDB, the old file:position format is still compatible. MariaDB does understand MySQL GTIDs, so replication from MySQL to MariaDB does work when using GTIDs in MySQL.)

I stopped moving live files around in MySQL as a migration strategy decades ago. Why would you want to deal with anything but data?

There is definitely a difficulty in using dumpfiles if your database is 50G in size.

I pipe dumpfiles into pbzip2, which shrinks 1TB+ dumpfiles down to a very manageable 30-40GB. scp to the new host, import, start replication, and it's good.

The problem with this approach is often that the dump restore takes a week to complete.

Actually, from experience, you are wrong. If your database if 50G, chances are most of that is index space. So no, mysqldump has absolutely no problem dealing with database that size.

Use mydumper/myloader. I've had issues using mysqldump over 50g databases with three or four figure tables and has to write a script that dumps every table. To get a consistent snapshot you have to close all the client connections.

I think --single-transaction flag solves this.

We have 500GB to 900GB, redumping that is fun :-)

Are you sure that you actually installed mysql? A apt-get install mysql would install mariadb.

It could be pretty difficult to get and install mysql. I recall times where you needed to go to the Oracle website and check some EULA manually before you could get a temporary download link. Quite a stand against automation.

Your question is whether the person who maintains SQLAlchemy knows how to install mysql?

The forking war between mariadb and mysql does not mind who you are. It affects everyone.

Your parent's point is that zzzeek' is highly likely to understand the difference. He is the maintainer of https://www.sqlalchemy.org/

I think you are looking for apt-get install mysql-server

docker run mysql -d

Oh, and we „accidentally“ forgot to mention in the introduction paragraph, that MariaDB is the „fork“ of the people who actually invented MySQL and made all that, including our own business, possible in the first place. A fork that‘s created because Oracle gives a shit about really open community development but pushes their secrecy and self centered view and processes into the open source world wherever they can for their own financial gain and nothing else.

Just saying, cause to somebody not aware of history it might sound like there are some crazy open source folks making their own thing at mariadb. (And im only referring to the introduction, it might be better explained somewhere else in the text, but i didn’t see that browsing through)

Most of the statements in here are biased by Percona's relationship with MySQL.

I mean you could complain that it's a fork; but who are you to bitch it's free software. If you paid for MySQL enterprise and they forked to something else and your license is worthless then you have a case.

So it's not MySQL 8.0 and it doesn't have the same features as MySQL; it's obvious that the developers of MariaDB don't have the same mindset (and possibly same user audience) as MySQL does.

Lastly most people use MySQL because they don't know better. You read a book about LAMP and it tells you to install MySQL; you really didn't make a choice. Lots of applications depend on MySQL and don't work on $othersql so you get forced dependencies.

You could say MySQL used their clout to try and force their world vision of dominance. This wouldn't be far from the truth.

I see several comments here that accuse Percona of bias against Maria for commercial reasons. If you look at Percona's services:

https://www.percona.com/services/support/support-tiers-mysql...

They will happily take your money to support MariaDB. Percona Toolkit and Percona XtraBackup both support MariaDB.

I have no dog in this fight -- I've left MySQL behind years ago and don't really miss it. I've hired Percona to help with some urgent DB problems I couldn't solve on my own and I was pleased with their work, but I have no stake in standing up for them. But the idea that they have some vested interest in pushing for MySQL over MariaDB doesn't seem born out by what they're selling.

Percona will take your money and support just about anything. They have an excellent set of DBA's for consulting with customers

That said their bias is clear. I'm sure they are not happy that the innodb plugin has the xtradb patches.

Hard to sell your product when others have brought it into their product I guess

This is a blog post on the official Percona blog by its "Chief Evangelist", and quite far from a neutral evaluation.

> Linux distributions have chosen MariaDB Server... It is the “default” MySQL in many Linux distributions (such as Red Hat Enterprise Linux, SUSE and Debian). However, Ubuntu...

I think this is a key point. The major Linux distros use mariadb. Ubuntu does not by default, but "apt-get install mariadb-server" will get it for you. Plus the creator of MySQL and much of the original MySQL team actively develops MariaDB.

There's no way you can trust this to be unbiased, and the same can be said about the MariaDB comment they're responding to but don't directly reference (I've been unable to find it.)

Indeed, parts of this come across as more of a knee-jerk rant than anything.

My reality check was called Postgres some years ago...

I pretty much agree, except for the cases where you need multi-master databases. In those cases MySQL/MariaDB is pretty much your only option, unless you can afford huge license fees.

Last sentence...

We look forward to supporting your deployments of MySQL or MariaDB Server, whichever option is right for you! If you need assistance on migrations between servers, or further information, don’t hesitate to contact your friendly Percona sales associate.

have you worked with percona before?

OpenStack has been mentioned here a few times which caught my eye.

Our DBA (is that still a thing most places?) has requested that we prefer Percona over MariaDB in future infrastructure roll outs. I'm currently wrestling with Percona and it seems to be a confusing mess to me. Admittedly, I'm only a casual database consumer, but I've never had problems like this with postgres

  ERROR! mysql pid file /run/mysqld/mysql.pid /var/run/mariadb/mariadb.pid empty or not readable

Installation has been a pain with conflicting/incompatible versions of galera. I just want a simple ha database; is that so much to ask for?

`I just want a simple ha database; is that so much to ask for?`

Yep! HA (and related, CAP) necessarily comes with trade offs. Databases being our store where we expect ACID compliance adds an extra layer of needs that makes multi-master (and similar concepts) "hard AF".

I've been growing increasingly concerned by MariaDB bugs. The latest Debian versions from mariadb.org had annoying libmysqlclient symbol conflicts with Debian packages and it still isn't fixed in the latest stable release.

I also recently found out that MariaDB still ships the query cache built in (which is now completely removed from MySQL) and even with query_cache_type = off, it somehow still enabled itself and is in a broken state that was causing simple SELECT statements to return empty results.

This article is making me consider the switch to Percona Server quite seriously.

The tire-fire of a relationship between MySQL vendors, is still very entertaining.

I personally blame the use of GPL licensing. I hate to be yet another commentor that drops the “P”-word, so I will drop the “F”-word instead; the Freebsd licensing allows for a kind of community growth, collaboration and mind share the likes of which the MySQL forks could only dream of.

I wasn't aware the client connectors (like MySQL-python) I'm using with MariaDB everyday are not "official". I'm just using them as usual.

What do people here think of the (BSL) Business Source License? The MariaDB folks are using it for their MaxScale product. The BSL is intended to be a sustainable way to pay for open source software development. (BSL's tl;dr is that it's a proprietary license that reverts to an open source license after a period of time.)

The JSON comparison seems a bit disenginous considering Maria has JSON specific functions but just not a JSON type

Edit: nevermind, I was skimming and read that totally wrong, they're pretty explicit about that

That's literally what it says, doesn't it? "No JSON Data Type, 26 functions"

Lots of love for MariaDB in the comments here..

Tell me about it. It's almost as if it's a good product that works well for most people (myself included).

Not knocking the DB. I use it myself. Just noticing a trend.

I have not read the article but let met give a reality check. Innovation has stopped around Mysql 5.0 which supported binary logs and multi master replication. What features have actually been developed after this version that is useful? Json?

Multithreaded replication in 5.6, row based replication in 5.1, performance schema

Binary logs were introduced on 3.23.

I'm not sure what you mean by multi master replication. If it's support for a single replica to receive events from more than one master at the same time, that was introduced on 5.7 on MySQL while MariaDB introduced its own, different implementation before that, on 10.0.1. If it's the possibility of setting up two nodes as co masters in circular replication, you could do that on 3.23 too.

A lot was done on MySQL after 5.0, like GTID, for example. You can see list of all that was introduced on 5.7 alone here: http://www.thecompletelistoffeatures.com

Reply on news.ycombinator.com