An overview of changes, improvements, and what's new in MariaDB Enterprise Server 10.6
MariaDB Enterprise Server 10.6 introduces the new features listed below.
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 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 . 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 option can be used.
As of this release, the following storage engines fully support atomic DDL:
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.
allows database-specific and table-specific filters.
For example:
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 and 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 against a JSON_TABLE().
enables extraction of nested data from JSON arrays and objects.
sys schema provides a set of views, functions, and stored procedures to aid DBA analysis of the .
Additional syntax is supported for
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):
For example, the following query specifies ONLY instead of WITH TIES, so the query can't return more than 10 rows:
When , 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.
Expanded compatibility with Oracle through new functions:
Added function
Added function
Added function
Added function
Expanded compatibility with Oracle through 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
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 list and WHERE clause.
With sql_mode=EXTENDED_ALIASES, support use of an alias in the 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.
See also:
With sql_mode=ORACLE subqueries in a FROM clause do not require the AS clause.
SELECT *
FROM ticketing
WHERE claimed = 0 AND section = 'B'
ORDER BY row DESC
LIMIT 10
FOR UPDATE SKIP LOCKED;{
"connect_event" : "ALL",
"table_event" : ["READ","WRITE",{"ignore_tables" : "mysql.*"}],
"query_event" : ["DDL",{"tables" : "test.t2"}]
}SELECT username, purchase
FROM user_purchases
ORDER BY username
OFFSET 305 ROWS
FETCH NEXT 10 ROW WITH TIES;SELECT username, purchase
FROM user_purchases
ORDER BY username, purchase
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;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 ()
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)
has been backported. ()
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.
has been backported. ()
Two new fields are available via SHOW REPLICA STATUS ()
Connects_Tried, which provides the number of attempts the replica has made to connect to the primary, and
Master_Retry_Count, which provides the number of times the replica will attempt to connect to a primary before giving up.
The UUID data type has been backported from MariaDB Community Server 10.7 for more efficient storage of UUID values. ()
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.
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)
+---------------------+
| is_overlap |
+---------------------+
| 1 |
+---------------------+SELECT JSON_OVERLAPS('{"A": 1, "B": {"C":2}}', '{"A": 2, "B": {"C":2}}') AS is_overlap;The new 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 statement supports new clauses to convert tables to partitions and partitions to tables. ()
To convert a partition to a table, use the CONVERT PARTITION .. TO TABLE .. clause:
To convert a table to a partition, use the CONVERT TABLE .. TO PARTITION .. clause:
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.
ALTER TABLE partitioned_table
CONVERT PARTITION part1
TO TABLE normal_table;ALTER TABLE partitioned_table
CONVERT TABLE normal_table
TO PARTITION part1 VALUES LESS THAN (12345);This page is: Copyright © 2025 MariaDB. All rights reserved.