What's New in MariaDB Enterprise Server 10.6?

An overview of changes, improvements, and what's new in MariaDB Enterprise Server 10.6

The most recent release of MariaDB Enterprise Server 10.6 is:

MariaDB Enterprise Server 10.6.23-19 Download Now

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 ROW 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:

Added function ADD_MONTHS()
Added function ROWNUM()
Added function SYS_GUID()
Added function TO_CHAR()

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 from MariaDB Community Server 10.7 for more efficient storage of UUID values. (MENT-1459)

MariaDB Enterprise Server 10.6.11-6

The new slave_max_statement_time system variable is available to set the maximum execution time for queries on replica nodes. (MENT-1566, MDEV-27161)
- When a query takes more than slave_max_statement_time seconds to run on the replica (slave) node, the query is aborted, and replication stops with an error.
- The system variable can be set to a decimal value, where the decimal component has microsecond precision.
- When set to 0, there is no timeout.
- The default value is 0.
To simplify maintenance, the ALTER TABLE statement supports new clauses to convert tables to partitions and partitions to tables. (MENT-1454)
- To convert a partition to a table, use the CONVERT PARTITION .. TO TABLE .. clause:

ALTER TABLE partitioned_table
 CONVERT PARTITION part1
 TO TABLE normal_table;

To convert a table to a partition, use the CONVERT TABLE .. TO PARTITION .. clause:

ALTER TABLE partitioned_table
 CONVERT TABLE normal_table
 TO PARTITION part1 VALUES LESS THAN (12345);

The new CONVERT PARTITION and CONVERT TABLE clauses are crash-safe operations.
The EXCHANGE PARTITION clause can still be used, but it is not a crash-safe operation.

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.
- The number of undo logs is configured by the InnoDB system variable --innodb-undo-tablespaces.
- Splitting undo logs over multiple tablespaces can reduce the size of a single tablespace, especially the InnoDB system tablespace.
- In previous releases, the number of undo logs had to be configured before InnoDB was initialized, so changing the number of undo logs would require setting up a new server instance. With this backport from MariaDB Community Server 10.11, the number of undo logs can be changed so it will take effect with the next server start. (MENT-1650)

MariaDB Enterprise Server 10.6.15-10

JSON_OVERLAPS() has been backported. (MENT-1853)
- The JSON_OVERLAPS() function can be used to compare two JSON documents to determine if they have any key-value pairs or array elements in common.

SELECT JSON_OVERLAPS('{"A": 1, "B": {"C":2}}', '{"A": 2, "B": {"C":2}}') AS is_overlap;

+---------------------+
| is_overlap          |
+---------------------+
| 1                   |
+---------------------+

JSON_SCHEMA_VALID() has been backported. (MENT-1796)
- The JSON_SCHEMA_VALID() function can be used to validate a JSON document against a JSON schema, as documented by the JSON Schema Draft 2020.
- This function can also be used in a CHECK constraint to verify that JSON documents are only stored in the database if they include required items and that the values are within a given range and length.

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 (MENT-2007)
Option s3_debug can now be changed without the need to restart the server (MENT-2001)
New Time Zone Options %Z and %z for DATE_FORMAT (MENT-1902)
Server Audit Log with Milliseconds Precision Timestamps (MENT-1744)

MariaDB Enterprise Server 10.6.24-20

Two new fields are available via SHOW REPLICA STATUS (MENT-2129)
1. Connects_Tried, which provides the number of attempts the replica has made to connect to the primary, and
2. Master_Retry_Count, which provides the number of times the replica will attempt to connect to a primary before giving up.
Add the server option --replicate-rewrite-db to system variables and SHOW REPLICA STATUS (backport of MDEV-15530) (MENT-2421)
The authentication plugin caching_sha2_password has been added, not loaded by default (MDEV-37600)

Available Versions

Installation Instructions

Upgrade Instructions

Upgrade to MariaDB Enterprise Server 10.6

PreviousMariaDB Enterprise Server 10.6 NextRelease Notes for MariaDB Enterprise Server 10.6.24-20

Last updated 1 day ago

Was this helpful?

Good evening