What’s new in MariaDB 10.6

As of January 2022, ClusterControl v1.9.2 introduced support for the latest version of MariaDB — version 10.6. MariaDB 10.6, released in July 2021, will be supported for the next four years or precisely until July 2026.

In this post, we will highlight the top features of MariaDB 10.6.

Atomic DDL (Data Definition Language)

The first feature we will cover is Atomic DDL. By definition, “atomic” means either the operation is successful and logged to the binary logs, or it is completely reversed. Starting with MariaDB 10.6.1, MariaDB has improved the DDL’s operations readability by making most of them atomic while the rest are crash-safe, even if the server crashes while executing any operation. Both Atomic and Crash-safe were developed to work with all storage engines, except for the S3 storage engine and partitioning engine, which are still a work in progress.

In this version, ALTER TABLE, RENAME TABLE, CREATE TABLE, DROP TABLE, DROP DATABASE, and their related DDL statements are now atomic ready. The complete list of other Atomic DDL operations can be found here. The great thing about the new atomic and crash-safe implementation is that the MariaDB server has become much more stable, not to mention reliable, even in unstable environments.

SQL Syntax

In terms of the SQL Syntax category, a few new features were added. The first one that we are going to see is:

SELECT … OFFSET … FETCH

The OFFSET clause will permit us to return just those segments of a resultset that arrive after a defined offset. On the other hand, the FETCH clause restricts the number of rows to return. Whether it’s a singular ROW or plural ROWS, both could be used after the OFFSET and FETCH clauses as they have no impact on the outcomes. 

SKIP LOCKED

Perhaps some of us are familiar with this syntax since it has been imported and adapted from MySQL. With SKIP LOCKED, we could skip any locked ROWS when accomplishing SELECT or UPDATE operations. It is definitely a useful feature, especially for applications that let multiple users book limited resources like hotel rooms, flight seats, concert tickets, etc. 

Ignored Indexes

Ignored Indexes is similar to the “invisible indexes” feature in MySQL 8. Any indexes that are visible and maintained but not used by the optimizer could be defined as Ignored Indexes. This can be very useful when testing to see what surfaces if we drop an index before indeed dropping it. In case any issue emerges, we could enable it again instantly (by marking the index IGNORED/NOT IGNORED).

JSON_TABLE

This table function is also imported from MySQL, where it could transform JSON data or documents into a relational form. In this version, MariaDB enabled a table view into JSON data stored in the MariaDB database, and by using SQL, all queries will be returned as a regular table.

Oracle Compatibility

MariaDB was the pioneer in the open-source database world that added PL/SQL compatibility. Starting with MariaDB 10.3, many syntaxes and functions have been added to ease the migration from Oracle to MariaDB. As for MariaDB 10.6, the following are features introduced to make MariaDB more PL/SQL compatible:

• Anonymous subqueries in a FROM clause (no AS clause) are permitted in ORACLE mode
• ADD_MONTHS() added 
  • function to add/subtract months from a given date value.
• TO_CHAR() added
  • supports NUMBER, DATE, DATETIME, TIMESTAMP, etc. as parameters and returns a formatted/converted TEXT value
• SYS_GUID() added 
  • similar to the UUID function in MariaDB
• MINUS is mapped to EXCEPT in UNION 
• ROWNUM function returns the current number of accepted rows in the current context 

Replication, Galera and Binlog

In this category, MariaDB has introduced binlog_expire_logs_seconds as a form of alias for expire_logs_days, which means any changes to any of them will automatically be reflected in the other. Besides, binlog_expire_logs_seconds accepts a precision of 1/1000000 days. This is exceptionally useful in high-volume writes on master and when the environment has limited disk space.

In addition to that, MariaDB also introduced wsrep_mode system variables. This variable enables WSREP features that are not part of the default behavior like BINLOG_ROW_FORMAT_ONLY, DISALLOW_LOCAL_GTID, REQUIRED_PRIMARY_KEY, REPLICATE_ARIA, REPLICATE_MYISAM, STRICT_REPLICATION.

Sys Schema

The next feature is sys_schema, a collection of views, functions, and procedures. There is no doubt that sys_schema helps DBAs and developers in many ways while interpreting data; a lot of information and diagnostic information could be gathered from it. sys_schema is not only used in troubleshooting performance issues but also assisting in managing the resources efficiently. Thankfully, this is now available in MariaDB 10.6.

Information Schema

The newly added feature in this category is INFORMATION_SCHEMA.KEYWORDS and INFORMATION_SCHEMA.SQL_FUNCTIONS. The KEYWORDS table contains around 694 rows of MariaDB keywords, while the SQL_FUNCTIONS table contains around 234 rows of MariaDB functions. With these two tables, we can now obtain the Information Schema related to them in case we need it.

Wrapping Up

In addition to these new features introduced in MariaDB 10.6, many other improvements were made that were not discussed in this post. While many of these features were taken from MySQL, these additions are still highly beneficial to users.

As previously mentioned, ClusterControl currently supports MariaDB 10.6. With ClusterControl, you can easily upgrade to the latest technology version stress-free. If you’re not yet familiar with ClusterControl, you can evaluate it free for 30 days — no CC required. 

To stay up to date with all the latest news and best practices for the most popular open-source databases, don’t forget to follow us on Twitter and LinkedIn, and subscribe to our newsletter for updates.