New MariaDB Enterprise Server 23 Release, Now Available as Technical Preview

Posted on July 27, 2023 by and

We are pleased to announce the first technical preview of MariaDB Enterprise Server 23 for our subscription customers. This is the newest release for our Enterprise Server product, and incorporates new features and capabilities that we have worked on since our last release of Enterprise Server 10.6 in August of 2021.

New Enterprise Server Release Numbering

When we launched MariaDB Enterprise Server, we anticipated that there would be a time when we would need to separate the version numbers between MariaDB Community Server and Enterprise Server as the goals of the two are very different. Some of the key differences between Enterprise Server and Community Server include:

  • Community Server is on a quarterly release cadence in order to get new innovations and features out to users faster. It is maintained for one year.
  • Enterprise Server focuses on stability and much longer term maintenance for enterprise customers who are more concerned with having the most stable and secure product.
  • Enterprise Server includes additional enterprise features such as audit capabilities and MaxScale for high availability, scalability and security.
  • Enterprise Server receives frequent backports of new features to older release versions so customers don’t have to qualify an entirely new version in order to gain access to next generation features.

We intend to release new Enterprise Server versions approximately every 2 years (and maintained for 8 years). To make it easy to identify when an Enterprise Server release came out, we are using the year and month of the release as the version numbers (similar to  Ubuntu style version numbering: yy.mm). Thus the new Enterprise Server will have a version number 23.xx, where 23 represents the year and xx stands for the month of the release.

The first Tech Preview of Enterprise Server 23 will have the version number 23.07 as it is released in July of 2023. The new release comes with a lot of exciting new features, many of which are explained in more detail below. For a more detailed list, please read our release notes.

MariaDB Query Optimizer Adopts a Cost-based Model

MariaDB Enterprise Server 23 introduces a new optimizer cost model, a change from a more rule-based to a cost-based model. Huge effort went into improving the calculations of the optimizer costs, taking into account state of the art SSD disks. The new implementation also takes the different characteristics of a storage engine into account.

If a key lookup cannot be used, the optimizer can now make better choices when to use index scan, table scan, index merges or other methods to join data.

While one model may work well for a specific use case, it may not be the right model for other use cases. With the changes we’ve made, it’s now possible to fine-tune the optimizer by changing costs for different metrics.

Enhancements for Working with JSON

The new JSON function JSON_SCHEMA_VALID 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 only JSON documents are stored in the database which include required items and that the values are within a given range, length, etcetera.

The new JSON_OVERLAPS() function can be used to compare 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                   |
+---------------------+

The functions JSON_EQUALS() and JSON_NORMALIZE() help to compare two JSON documents and to normalize JSON objects to be comparable, for example when a unique key based on JSON data is needed.

To define the position in a JSON array from the end to the beginning, negative indexes or last can be used as the last element of an array for an JSON array of a JSON path, where the JSON path is used as a parameter in a JSON function.

  • The keyword last can be used instead to define the last element of an Array.
  • The keyword to can be used to define a range of elements
SELECT JSON_REMOVE(@json, '$.A[-10]');
SELECT JSON_REMOVE(@json, '$.A[last]');
SELECT JSON_REMOVE(@json, '$.A[1 to 3]');

Descending Indexes

MariaDB Enterprise Server supported the SQL Syntax DESC for ordering a result set, but did not use descending indexes internally, resulting in some limitations in processing and performance for the request.

MariaDB Enterprise Server 23 now supports true descending indexes. Composite indexes can now be used with differently ordered columns to get a significant performance boost in the corresponding ORDER BY use cases.

 

Privilege System

Changes to the SUPER Privilege

The SUPER privilege became an alias of other privileges for compatibility reasons when the fine grained privileges were introduced with MariaDB Enterprise Server 10.5. With MariaDB Enterprise Server 23, all fine grained privileges have been removed from the SUPER alias to enhance the security aspect by preventing the too global usage of the SUPER privilege.

By removing the READ ONLY ADMIN privilege from the SUPER privilege, changes were made to privileges to allow real read only replicas. The READ ONLY ADMIN privileges now needs to be granted to a user with the SUPER privilege explicitly, or any other user, if this user should have write access to a read only replica (a replica having read_only=1 set).

The SUPER privilege is still used for some special cases, like using DES_ENCRYPT and DES_DECRYPT without an explicit key, for debug settings and some system variables for changing them with SET GLOBAL.

 

GRANT TO PUBLIC

The new SQL syntax GRANT .. TO PUBLIC can now be used to grant privileges to databases or tables for any user, who has access to the server.

SHOW GRANTS FOR PUBLIC is an enhancement to the existing SHOW GRANTS syntax to retrieve all privileges granted to public.

 

Password Reuse Restriction

All supported release series of MariaDB Enterprise Server include plugins to validate that user passwords meet certain minimal security requirements, when users are created or passwords are changed. Validation of a password does not include tracking a history of used passwords.

A new plugin password_reuse_check can now be used to prevent password reuse based on a retention period, by keeping a history of the passwords per user.

Combining password validation, password reuse and password expiration allows to define required security policies for user accounts.

 

System Versioned Tables

Beginning with MariaDB Enterprise Server 10.3 it was possible to define tables to be system versioned. That way old data will be stored as historical data whenever data is changed or even deleted.

For a backup and restore it has been required to use MariaDB Enterprise Backup to be able to also restore the historical data, as some fields are invisible for the user.

With MariaDB Enterprise Server 23, system versioned tables can now be dumped and restored by mariadb-dump.

The new mariadb-dump parameter –dump-history dumps all historical data.

To restore from a dump file the new parameter system_versioning_insert_history needs to be enabled to allow direct inserts into ROW_START and ROW_END columns.

The existing parameter secure_timestamp needs to be set to a value which allows changing session timestamps.

The command line tool mariadb-dump now also allows to dump data as of a date in the past using the  option –as-of.

System-Versioned Tables can now automate the creation of new HISTORY partitions partitioned by INTERVAL/LIMIT using the keyword AUTO when creating a table.

CREATE TABLE t1 (x int) WITH SYSTEM VERSIONING
PARTITION BY system_time INTERVAL 1 months AUTO;

In this case a new HISTORY partition will be created on a monthly basis, storing old versions of the table data.

MariaDB Replication

Schema changes can take time when an ALTER cannot be an instant operation, as data needs to be copied for the altering process. In a topology with a primary server and a replica such a process creates a huge replication lag, as a replica only starts its ALTER after the same was committed by the Primary.

MariaDB Enterprise Server 23 is introducing a new “optimistic” ALTER TABLE for replicas. When enabled by binlog_alter_two_phase=1 (not default), an ALTER TABLE is executed on the primary server and is replicated and “started” on the replica server more or less in parallel to the primary server. Thus, the possibly huge replication lag between a primary and replica server due to a long running ALTER TABLE on the primary can be avoided. In case of a failing ALTER on the primary server a rollback will be triggered on the replica.

The command line tool mariadb-binlog now supports the use of global transaction IDs (GTID) for the options start-position and stop-position. mariadb-binlog now also can be used to produce results filtered by the defined GTIDs, domain IDs or server IDs. The command-line tool mariadb-binlog now supports the new options –do-domain-ids, –ignore-domain-ids and –ignore-server-ids.

Monitoring MariaDB Enterprise Cluster (Galera)

MariaDB Enterprise Cluster (Galera) has new status monitoring features which can be used to track changes to the status of a cluster or its nodes.

The wsrep node status changes are now sorted in a dedicated machine readable file in JSON format. This allows an easier way for reading and interpreting the status file by an external monitoring tool. This file also includes details about a node eviction status, to report that a Galera node needs to be restarted to join the cluster.

A progress reporting of MariaDB Enterprise Backup based State Snapshot Transfer (SST) can be enabled to verify the progress of an SST. The SST progress report is written into the server log.

New thread states in PROCESSLIST for MariaDB Enterprise Cluster now allow better tracking of a session status via the states:

  • “waiting to execute in isolation”
  • “waiting for TOI DDL”
  • “waiting for flow control”
  • “waiting for certification”

A new allowlist for MariaDB Enterprise Cluster can be used to restrict the nodes which can join a cluster.

A new system variable wsrep_allowlist can be used to define a list of IP addresses. Only nodes from these IP addresses can join a running MariaDB Enterprise Cluster to request a SST or IST to sync the data from the donor.

In addition to the features discussed above, there are many new usability features, such as enhanced ANALYZE and EXPLAIN, and more. For information on all the new features in the tech preview of MariaDB Enterprise Server 23.07, take a look at the release notes.

Features available as Backports

As already mentioned, Enterprise Server receives frequent backports of new features to older release versions. Features mentioned in this blog post, which have already been backported are:

  • The –as-of command-line option for mariadb-dump, backported to MariaDB Enterprise Server 10.4.25-16, 10.5.16-11, and 10.6.8-4.
  • The JSON_EQUALS() function was previously backported to MariaDB Enterprise Server 10.4.25-16, 10.5.16-11, and 10.6.8-4.
  • The JSON_NORMALIZE() function was previously backported to MariaDB Enterprise Server 10.4.25-16, 10.5.16-11, and 10.6.8-4.
  • The password_reuse_check plugin was previously backported to MariaDB Enterprise Server 10.4.25-16, 10.5.16-11, and 10.6.8-4.

MariaDB customers can try the tech preview of MariaDB Enterprise Server 23.07 today by going to staging-mdb.com/downloads/enterprise for official MariaDB database downloads.