A summary of MariaDB 10.10: INET4 type, RANDOM_BYTES() and more

In my first post in 2023, I want to give you a summary of MariaDB 10.10. Some may argue that I’m a bit late because it was released last November, and versions 10.11 and 11.0 have been announced since then… All that is correct, but 10.10 is the latest stable version. My usual friendly reminder: even if it’s stable it shouldn’t be used in production – it is a Short Term Support version. Its EOL is scheduled one year after the first release date. The latest LTS version is still 10.6.

Below you will find my selection of the most important features of MariaDB 10.10. I picked them based on our customers’ problems and desires, as well as our internal discussions and my personal taste.

General thoughts and highlights

MariaDB 10.10 brings us a number of small improvements in different areas.

Developers may be interested in the following features:

• The INET4 type;
• The RANDOM_BYTES() function.

Some highlights for DBAs:

• GTID is used by default when available in the master;
• Demote a master safely and easily with CHANGE MASTER TO MASTER_DEMOTE_TO_REPLICA = 1;
• Galera has an IP allowlist for SST and IST;
• Unicode collations are faster than before.

High availability

CHANGE MASTER TO now starts GTID-based replication by default if the master supports it. GTID is a mechanism that identifies data changes regardless of their position in the local server’s physical files. It is important because it allows promoting a replica to a master (master failover) without breaking replication. For more information, see the GTID KnowledgeBase page, and task MDEV-19801.

A security improvement was implemented for Galera, the master-master, virtually synchronous replication solution that comes with MariaDB as a plugin. When a new node joins a cluster, or restarts after a long time, a donor node sends a State Snapshot Transfer (SST) to the joiner. When a node restarts after a short time, and all recent data changes are contained in the Galera Cache, an Incremental Snapshot Transfer (IST) is sent. The problem is that, until version 10.10, any server could request an SST or an IST. This was a good reason to run Galera in a VNP. In version 10.10, Galera supports an IP allowlist that can consist of IPv4, IPv6, but not hostnames. Changing the list requires a restart. See Introduction to State Snapshot Transfers in the KnowledgeBase, the wsrep_allowlist variable, and the task MDEV-27246.

Also, Galera now supports OpenSSL 3.0. See MDEV-25949.

Sequences also work with Galera now, if they’re declared with NOCACHE and ENGINE=InnoDB, see MDEV-27862.

Performance and collations

As per my summary of MariaDB 10.8, I’ve already seen very good improvements in the performance of utf8mb3_unicode_ci and utf8mb4_unicode_ci collations. Version 10.10 improves the performance of all the collations for utf8mb3 and utf8mb4 character sets. See MDEV-27266. Performance of contractions for all the UCA collations has also improved, see MDEV-27265.

Before version 10.10, optimisation of queries with many joins could take too much CPU time. This is especially true for sequences of equi-joins where many permutations were possible, because all the combinations were considered – this brought little to no benefit. In version 10.10, the optimiser doesn’t consider all combinations. Should this cause problems, we can disable it by setting optimizer_prune_level=1. See MDEV-28852.

SQL

Unicode 14 collations were added, see MDEV-27009.

The INET4 type is now supported. Support for INET6 was already added in version 10.5, and addresses could be inserted as IPv4, but, most of us still work with IPv4 mainly. Keep in mind that storing an INET4 value takes 4 bytes while storing an IPv4 address as a UTF8 string takes up to 30 bytes. This means that comparisons and searches are also faster with INET4. See MDEV-23287.

The RANDOM_BYTES(N) function has been added. It generates the specified number of bytes (up to 1024) using the installed SSL library function. The bytes sequence can easily be converted to any type. See MDEV-25704.

MDEV-28883 Re-design the upper level of handling UPDATE and DELETE statements is about refactoring some code in the SQL layer. You may think that it won’t make any difference for you, but it’s interesting to see the bugs and tasks that depend on this one.

Fixed in version 10.10:

• MDEV-17954 Assertion failure when preparing UPDATE with derived table in WHERE
• MDEV-29174 Assertion failure with UPDATE of view using MERGE table
• MDEV-29189 Crash of the second execution of stored function using DELETE/UPDATE

Fixed later or not yet fixed:

• MDEV-28883 multi-table DELETE statement with the same source and target in subselect
• MDEV-29428 Incorrect result for delete with “order by” clause

Deprecated variables and syntax, modernised defaults

The number of variables in MariaDB is big, and it tends to increase a lot over the years. I’m glad that MariaDB occasionally deprecates or removes some options from time to time.

• DES_ENCRYPT() and DES_DECRYPT() are unsafe nowadays, so they have been deprecated.
• innodb_prefix_index_cluster_optimization was deprecated because this optimisation is always enabled.
• explicit_defaults_for_timestamp finally defaults to ON, let’s hope it’s deprecated soon.
• mariadb --ssl client option is enabled by default. But no error is shown if a TLS connection can’t be established.
  • However, MariaDB 10.10 won’t start if SSL is not configured correctly.
• 8 variables set by the SPIDER storage engine.

Federico Razzoli