What's New in MariaDB Enterprise Server 10.6?

Overview

MariaDB Enterprise Server 10.6 introduces the new features listed below.

Atomic DDL

DDL (Data Definition Language) statements are now atomic operations. If the DDL statement is not fully successful, the operation will be rolled back. When the server crashes or is killed in the middle of a DDL statement, the operation is rolled back during crash recovery when the server is restarted.

During crash recovery, the server uses the DDL log to determine if an operation needs to be rolled back. When the binary log is enabled, the crash recovery process ensures that the successful operations are written to the binary log and that the unsuccessful operations are not.

By default, the DDL log is at ddl-recovery.log in the datadir. When DDL statements are being executed, the DDL log is synchronized to disk very frequently. If you want to configure a custom path for the DDL log, the log-ddl-recovery option can be used.

As of this release, the following storage engines fully support atomic DDL:

SKIP LOCKED

SELECT [ FOR UPDATE | LOCK IN SHARED MODE ] .. SKIP LOCKED ignores already-locked rows.

One use case for this feature is within applications that sell a limited resource, such as ticketing, rentals, or seat-based sales. In these applications, you need a way to display only the available inventory. This can be accomplished by querying available inventory and skipping locked rows.

SELECT *
FROM ticketing
WHERE claimed = 0 AND section = 'B'
ORDER BY row DESC
LIMIT 10
FOR UPDATE SKIP LOCKED;

Enterprise Audit Object Filters

MariaDB Enterprise Audit allows database-specific and table-specific filters.

For example:

{
  "connect_event" : "ALL",
  "table_event" : ["READ","WRITE",{"ignore_tables" : "mysql.*"}],
  "query_event" : ["DDL",{"tables" : "test.t2"}]
}

JSON_TABLE

JSON_TABLE() returns a table from JSON data.

Queryable rows and columns are produced based on the JSON input, but are not stored in a table on disk. Column mappings are defined in a JSON path expression.

Prior to this release, the JSON_VALUE() and JSON_QUERY() functions could be used to retrieve values from JSON data on a per-column basis.

With JSON_TABLE():

  • JSON data can JOIN with existing tables.

  • A table can be created from JSON data using CREATE TABLE .. AS SELECT against a JSON_TABLE().

  • NESTED PATH enables extraction of nested data from JSON arrays and objects.

Sys Schema

sys schema provides a set of views, functions, and stored procedures to aid DBA analysis of the Performance Schema.

OFFSET Syntax

Additional syntax is supported for SELECT .. OFFSET

OFFSET start { ROW | ROWS } FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES } is an alternative to LIMIT .. OFFSET

The WITH TIES option requires the use of ORDER BY and allows the number of rows to exceed the FETCH count to ensure that the final row in the chunk includes any additional rows that have the same values in the ORDER BY fields (eliminating the need to fetch the next chunk to check for spill-over).

  • For example, the following query can return more than 10 rows if there are more username rows that match the username in the 10th row (the order of the purchase values within the complete set of each username's records is non-deterministic):

    SELECT username, purchase
FROM user_purchases
ORDER BY username
OFFSET 305 ROWS
FETCH NEXT 10 ROWS WITH TIES;

  • For example, the following query specifies ONLY instead of WITH TIES, so the query can't return more than 10 rows:

    SELECT username, purchase
FROM user_purchases
ORDER BY username, purchase
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

Enhanced Consistency for Semi-sync Replication

When rpl_semi_sync_slave_enabled=ON, consistency is guaranteed for a Primary server in an HA (Primary/Replica) topology when using semi-synchronous replication.

Prior to this release, when using semi-synchronous replication, if a Primary crashed before sending a transaction to the Replica, on restart the Primary could recover incomplete InnoDB transactions when rejoining as a Replica.

With this release, when using semi-synchronous replication and with rpl_semi_sync_slave_enabled=ON, incomplete transactions will be rolled-back on the Replica, ensuring the new Primary (former Replica) and new Replica (former Primary) remain in sync.

Enhanced Compatibility with Oracle

Expanded compatibility with Oracle through new functions:

Expanded compatibility with Oracle through sql_mode=ORACLE enhancements:

  • With sql_mode=ORACLE added MINUS as an alias to EXCEPT

  • With sql_mode=ORACLE improved SYSDATE to allow use without parenthesis.

  • With sql_mode=ORACLE supports a rownum pseudo-column name as an alias for the ROWNUM() function.

  • With sql_mode=ORACLE subqueries in a FROM clause do not require the AS clause.

Enhanced Compatibility with Sybase SQL Anywhere

Enhanced compatibility with Sybase SQL Anywhere through sql_mode=EXTENDED_ALIASES:

  • With sql_mode=EXTENDED_ALIASES, alias resolution and use of column aliases in the SQL SELECT list and WHERE clause.

  • With sql_mode=EXTENDED_ALIASES, support use of an alias in the SELECT list before the alias is defined.

  • With sql_mode=EXTENDED_ALIASES, if the same label is used for an alias and a column, the alias is used.

Backported Features

MariaDB Enterprise Server 10.6.9-5

  • The UUID data type has been backported for more efficient storage of UUID values.

MariaDB Enterprise Server 10.6.11-6

MariaDB Enterprise Server 10.6.12-7

  • In previous releases, the number of undo logs was configurable before InnoDB was initialized. With this release, the number of undo logs can also be configured after install

MariaDB Enterprise Server 10.6.15-10

MariaDB Enterprise Server 10.6.16-11

  • A new view sys.privileges_by_table_by_level in the sys schema, to show privileges granted to a table on a global, schema, or table level

  • Option s3_debug can now be changed without the need to restart the server

  • New Time Zone Options %Z and %z for DATE_FORMAT

  • Server Audit Log with Milliseconds Precision Timestamps