Last updated on 17 August 2021
MariaDB 10.6 went Generally Available (production ready)!
The first GA version is 10.6.3, released on 6th July 2021. I encourage you to take a look at the 10.6 official summary, but I’ve made a quick test and I want to put my own thoughts and comments here.
To me, 10.6 is about refining some areas that needed improvements, though there are some useful new features.
As mentioned, I don’t expect the list of new features to impress you: they’re not many and some of them are merged from MySQL. But they’re useful.
Useful to test what happens if you drop an index before actually dropping it. If something goes wrong, you can “instantly” make it visible again.
See my article MariaDB Ignored Indexes for a practical, complete example of how to use this feature.
schema is now supported: “system” database with views and procedures that mainly aim to investigate performance problems. It’s not really a system database, because root can modify or drop it.
A couple of
tables related to replication have been added. The
now includes the lists of keywords and SQL functions.
Imported from MySQL, after removing or adapting some views.
rows that are not locked, skipping the ones that are locked. This addresses a typical need of applications that allow multiple users to book limited resources (flights, hotel rooms, etc).
Imported and adapted from MySQL.
Transforms a JSON document (or part of it) into a table that can be used as a subquery.
Imported from MySQL.
OFFSET … FETCH … [WITH TIES]
This syntax is equivalent to
LIMIT ... OFFSET
, but the
optional clause adds an extra functionality:
SELECT id, name, city FROM user ORDER BY city LIMIT 10; SELECT id, name, city FROM user ORDER BY city OFFSET 0 ROWS FETCH FIRST 10 ROWS WITH TIES;
The first query orders the results by city and return the first 10 rows. If the last city is
, you won’t know how many users you have from Edinburgh.
The second query will return at least 10 rows. But if the last city is
,it will also return all users from Edinburgh.
Part of the ongoing work to make MariaDB more compatible with Oracle. Some Oracle syntaxes and functions were added. This greatly eases the migration.
If you care about Oracle compatibility, you may want to know that:
- MariaDB has an ORACLE SQL_MODE, which enables some Oracle features and makes MariaDB behaviour more similar to Oracle’s.
- The main feature enabled by the afore mentioned option is a PL/SQL parser, that allows us to run many Oracle stored procedures in MariaDB.
As mentioned, MariaDB 10.6 is mostly about improving existing features. Here is a selection of notable improvements.
statements are now atomic and crash-safe. This means that, even if the server crashes during one of these operations, the change will be completely done or not done at all. You won’t have, for example, orphan files or unusable objects.
To make DDL crash-safe, MariaDB now logs DDL changes into a DDL log file. Keep this in mind if you have big tables and your disk space is limited.
See Which DDL Operations are Now Atomic. Note that DDL is not implemented yet for:
- Partitioned tables.
- Tables with Foreign Keys (MDEV-21053).
CREATE OR REPLACE TABLE: a table may still be dropped and not re-created if a crash occurs(MDEV-25292).
Like all versions, 10.6 comes with several InnoDB improvements. Some of them are:
- The first
INSERTinto an empty table is faster. This includes importing a dump that uses multi-row
- Writes to the temporary tablespace are normally avoided. Implicit and explicit temporary tables are faster (MDEV-12227).
Some improvements to Galera were made, too. The following are the most relevant:
- If encrypted connections between nodes are disabled, you can now enable them without downtime (MDEV-22131).
wsrep_modevariable now includes flags that specify if controversial Galera compatibility features should be enabled (
Cleaning up MariaDB is useful in many ways. It reduces the amount of code and use cases that the MariaDB team can maintain, helping them to develop a better product in the future. And it helps non-expert users to avoid features that nowadays it makes no sense to use, for various reasons.
- The TokuDB engine, not supported anymore by Percona, was removed.
- The Cassandra engine, that was never widely used and was still based on the Thrift protocol, was removed.
- 23 InnoDB variables were removed.
- Some innodb_checksum_algorithm were removed.
- Nowadays, InnoDB tables should be compressed using the page compression. The
COMPRESSEDrow format is read-only by default in 10.6 and write support will probably be removed in 10.7.
Is it stable? Do I need to upgrade?
Short answer: yes, with caution. And perhaps with our help.
Usually we don’t recommend to upgrade MariaDB or MySQL as soon as they are declared stable (well, in the case of MySQL there are more factors to consider, but they don’t apply to MariaDB). MariaDB 10.5 added big internal changes to InnoDB just one version before going GA, which really conflicts with our understanding of stability.
However, MariaDB 10.6 is mostly a hardening version. This doesn’t make it bug free, obviously. But suppose, for example, that there are severe bugs in DDL crash-safeness: chances are that, in most cases, DDL statements will still be safe in 10.6. Also, increased observability and performance optimisations are worth an upgrade. That said, depending on how prudent you want to be, you may still prefer to wait for a couple of maintenance releases.
Note that upgrades always need to be tested. Check that all your procedures for backup, restore, scale out, etc, work properly with 10.6. If your operations are fully automated, testing will be relatively easy.
Upgrades should be made gradually. Ideally, start by creating an unused replica, just to see if it crashes and if it can keep up with the master. Then start to send read queries to it, but initially only do this in testing.
Before sending reads to a 10.6 replica, you need to test that queries work, that they return correct results, and that they’re not slow. Be particularly careful with new features, like
. There are ways to do this, but it’s not possible to cover this topic here.