5 ms·

It might just be me but recently I have found myself a bit uncertain or nervous about the true compatibility of MySQL and MariaDB. I also find I sometimes don't

It might just be me but recently I have found myself a bit uncertain or nervous about the true compatibility of MySQL and MariaDB. I also find I sometimes don't know which I'm connecting to between all the different systems I'm running...which I used to be quite relaxed about, but now makes me a tad jittery.

evanelias 5y ago
They're not drop-in replacements for each other, haven't been for years. Each has some unique features that the other lacks.
- eatonphil 5y ago
  The Alpine Wiki has an informal blurb about it. The wiki doesn't seem to think there's a huge difference. https://wiki.alpinelinux.org/wiki/Mysql#MariaDB_vs_MySQL https://wiki.alpinelinux.org/wiki/Mysql#MariaDB_vs_MySQL
  - evanelias 5y ago
    The Alpine Wiki isn't written by MySQL/MariaDB experts, and is focused on an installation / very basic administrative perspective; from that angle they're not too different. But they've substantially diverged in terms of internals, advanced administrative functionality, and overall focus. source: I've been working in this space for 19 years, at scales ranging from tiny startups, to largest known mysql installation in the world.
    - eatonphil 5y ago
      Yeah true. Any good comparisons you can point us at?
      - evanelias 5y ago
        This is from a very reliable source but is 4 years old (pre-MySQL 8): https://www.percona.com/blog/2017/11/02/mysql-vs-mariadb-reality-check/ https://www.percona.com/blog/2017/11/02/mysql-vs-mariadb-rea... Separately you can search for "What's new in MySQL 8" type posts, nearly everything there is unique to MySQL. Some of the 5.7 stuff too, like native json column type. Ditto for recent MariaDB releases, btw! To be fair they have some cool stuff that MySQL lacks, such as system versioned tables, Oracle DB compatibility layer, native column types for uuid and inet6 (IPv6 addresses), etc.
        eatonphil 5y ago
        Mariadb has an oracle compatibility layer??? Ah but it's at the query layer not the wire protocol. Still very cool.
        lillecarl 5y ago
        Now that apis aren't patentable we might see something some day. I imagine reverse engineering it might be hard though.
    - pizza234 5y ago
      The amount of informations around is surprisingly poor; it's hard to find technical information. At least a significant difference is MariadDB's addition of several specialized engines - see https://mariadb.com/kb/en/storage-engines https://mariadb.com/kb/en/storage-engines: - MariaDB ColumnStore: Column-based - Mroonga: Full text search - OQGRAPH: Graph storage - SphinxSE: Full text search Where the above use cases are required, it's actually a big deal. A common use case may be FT searches - MySQL's support is a trainwreck, and I have the suspicion that Oracle has little to no interest in it.
      - tehbeard 5y ago
        Curious to learn more about Mroonga. FT has been a sticking point for several sites we've built, and the search daemons (typesense, meilisearch) tend to not as accurate as we'd like.
        sroussey 5y ago
        Same. I’ll have to take a look as well. The built-in FT going back to MyISAM and later was ok for small things, but needed to remain in memory wand dint handle lots of updates well. At one point we split a table into shards on the same database to ease locking issues. One thing I wish were available was a hybrid btree and inverted index. If you are multi tenant then you want your search to only happen within the one user’s data — not across all accounts and then filter for the one user.
- deleted 5y ago
  [deleted]
pipeweed 5y ago
https://mariadb.com/kb/en/incompatibilities-and-feature-differences-between-mariadb-106-and-mysql-80/ https://mariadb.com/kb/en/incompatibilities-and-feature-diff... tries to give a thorough breakdown of the differences.
mike22 5y ago
Among other things that have been incompatible for a while: - many system tables use the Aria engine; has been impossible to start a MariaDB server over a data directory created in MySQL for many versions - mysql.user table has some completely different columns and authentication plugins diverged greatly as of MySQL 8.0, ie no caching_sha2_password (makes writing a user migration script challenging) - Even the data dir initialization scripts/programs are very far removed

evanelias 5y ago

They're not drop-in replacements for each other, haven't been for years. Each has some unique features that the other lacks.

eatonphil 5y ago
The Alpine Wiki has an informal blurb about it. The wiki doesn't seem to think there's a huge difference. https://wiki.alpinelinux.org/wiki/Mysql#MariaDB_vs_MySQL https://wiki.alpinelinux.org/wiki/Mysql#MariaDB_vs_MySQL
- evanelias 5y ago
  The Alpine Wiki isn't written by MySQL/MariaDB experts, and is focused on an installation / very basic administrative perspective; from that angle they're not too different. But they've substantially diverged in terms of internals, advanced administrative functionality, and overall focus. source: I've been working in this space for 19 years, at scales ranging from tiny startups, to largest known mysql installation in the world.
  - eatonphil 5y ago
    Yeah true. Any good comparisons you can point us at?
    - evanelias 5y ago
      This is from a very reliable source but is 4 years old (pre-MySQL 8): https://www.percona.com/blog/2017/11/02/mysql-vs-mariadb-reality-check/ https://www.percona.com/blog/2017/11/02/mysql-vs-mariadb-rea... Separately you can search for "What's new in MySQL 8" type posts, nearly everything there is unique to MySQL. Some of the 5.7 stuff too, like native json column type. Ditto for recent MariaDB releases, btw! To be fair they have some cool stuff that MySQL lacks, such as system versioned tables, Oracle DB compatibility layer, native column types for uuid and inet6 (IPv6 addresses), etc.
      - eatonphil 5y ago
        Mariadb has an oracle compatibility layer??? Ah but it's at the query layer not the wire protocol. Still very cool.
        lillecarl 5y ago
        Now that apis aren't patentable we might see something some day. I imagine reverse engineering it might be hard though.
  - pizza234 5y ago
    The amount of informations around is surprisingly poor; it's hard to find technical information. At least a significant difference is MariadDB's addition of several specialized engines - see https://mariadb.com/kb/en/storage-engines https://mariadb.com/kb/en/storage-engines: - MariaDB ColumnStore: Column-based - Mroonga: Full text search - OQGRAPH: Graph storage - SphinxSE: Full text search Where the above use cases are required, it's actually a big deal. A common use case may be FT searches - MySQL's support is a trainwreck, and I have the suspicion that Oracle has little to no interest in it.
    - tehbeard 5y ago
      Curious to learn more about Mroonga. FT has been a sticking point for several sites we've built, and the search daemons (typesense, meilisearch) tend to not as accurate as we'd like.
      - sroussey 5y ago
        Same. I’ll have to take a look as well. The built-in FT going back to MyISAM and later was ok for small things, but needed to remain in memory wand dint handle lots of updates well. At one point we split a table into shards on the same database to ease locking issues. One thing I wish were available was a hybrid btree and inverted index. If you are multi tenant then you want your search to only happen within the one user’s data — not across all accounts and then filter for the one user.
deleted 5y ago
[deleted]

pipeweed 5y ago

https://mariadb.com/kb/en/incompatibilities-and-feature-differences-between-mariadb-106-and-mysql-80/ https://mariadb.com/kb/en/incompatibilities-and-feature-diff... tries to give a thorough breakdown of the differences.

mike22 5y ago

Among other things that have been incompatible for a while: - many system tables use the Aria engine; has been impossible to start a MariaDB server over a data directory created in MySQL for many versions - mysql.user table has some completely different columns and authentication plugins diverged greatly as of MySQL 8.0, ie no caching_sha2_password (makes writing a user migration script challenging) - Even the data dir initialization scripts/programs are very far removed