Release Notes for MariaDB Enterprise Server 23.07.0

Overview

MariaDB Enterprise Server 23.07.0 is the first Technical Preview release of MariaDB Enterprise Server 23.07. This release contains a variety of new features.

Software in technical preview is not recommended for production workloads.

MariaDB Enterprise Server 23.07.0 was released on 2023-07-27.

Changes in Storage Engines

Compatibility Enhancements

  • Stored function parameters can be qualified with IN, OUT, INOUT, and IN OUT: (MDEV-10654)

    • When a parameter is qualified with IN, a value is passed to the function.

    • When a parameter is qualified with OUT, the function returns a value to the caller.

    • When a parameter is qualified with INOUT or IN OUT, a value is passed to the function, and the function also returns a value to the caller.

    • OUT, INOUT, and INOUT can only be used when called from SET and not when called from SELECT

    • OUT, INOUT, and INOUT allow a function to return more than one value, which allows for more complex and nested functions.

    • In previous releases, the qualifiers were supported for stored procedures, but not for stored functions.

    • Starting with this release, the qualifiers are accepted in stored functions using the same syntax previously used for stored procedures.

    • When sql_mode=ORACLE is set, the behavior is adjusted to match the behavior of Oracle.

  • Changed default behavior for TIMESTAMP field properties: (MDEV-28632)

    • Previous to this release, implementation-specific behavior was present by default for the first TIMESTAMP column in a table. This behavior added DEFAULT current_timestamp() ON UPDATE current_timestamp() to the TIMESTAMP field properties.

    • Starting this release, the implementation-specific behavior for TIMESTAMP field properties is disabled by default. The default value of the explicit_defaults_for_timestamp system variable is changed to OFF

    • As a result of this change, new TIMESTAMP columns without explicit default values will be created with DEFAULT NULL

Operational Enhancements

Optimizer

  • MariaDB Query Optimizer performs cost-based optimizations with an understanding of storage engine-specific costs: (MDEV-26974)

    • The query optimizer now defaults to assume SSD storage is used. Costs for disk access can be overridden.

    • Optimizer costs can be tuned by setting the following system variables via configuration file, command-line parameter, or the SET SQL statement:

      System Variable

      Type

      Description

      optimizer_disk_read_cost

      Engine

      Sets the time in microseconds required to read a 4K block from storage. The default value is tuned for an SSD reading at 400 MB/second.

      optimizer_index_block_copy_cost

      Engine

      Sets the cost to lock a block in the global cache and copy it to the local cache. The cost applies to every block accessed, regardless of whether the block is already cached.

      optimizer_key_compare_cost

      Engine

      Sets the cost to compare two key values.

      optimizer_key_copy_cost

      Engine

      Sets the cost to copy a key value from the index to the local buffer while searching for a key value.

      optimizer_key_lookup_cost

      Engine

      Sets the cost to find a key entry in the index.

      optimizer_row_copy_cost

      Engine

      Sets the cost to find the next key entry in the index.

      optimizer_rowid_compare_cost

      Engine

      Sets the cost to compare two rowid values.

      optimizer_rowid_copy_cost

      Engine

      Sets the cost to copy a rowid from the index.

      optimizer_row_lookup_cost

      Engine

      Sets the cost to find a row based on the rowid. The rowid is stored in the index with the key.

      optimizer_row_next_find_cost

      Engine

      Sets the cost to find the next row.

      optimizer_scan_setup_cost

      Session

      Sets the cost to start a table or index scan. The default low value configures the optimizer to use index lookups for tables with very few rows.

      optimizer_where_cost

      Session

      Sets the cost to execute the WHERE clause for every row found. As this value is increases, the optimizer is more likely to choose plans which read fewer rows.

    • Optimizer costs can be tuned per storage engine by prefixing the system variable with the storage engine name.

    • Current optimizer costs for each storage engine can be queried via information_schema.OPTIMIZER_COSTS

  • For JOIN with many eq_ref tables, query performance is improved: (MDEV-28852)

  • An index can now be used when comparing the return value of the DATE() function to a constant value. (MDEV-8320)

  • Single-table UPDATE and DELETE can now benefit from semi-join optimization. (MDEV-7487)

  • JSON histograms with detailed histogram collection: (MDEV-26519)

    • Enabled when histogram_type=JSON_HB is set, which is now the default.

    • JSON histograms result in more precise data statistics for string data types or when columns have highly-uneven data distribution.

    • With more precise data statistics the optimizer can create better query plans, resulting in faster queries.

Partitioning

  • A table can be converted into a partition with ALTER TABLE .. CONVERT TABLE .. TO PARTITION: (MDEV-22165)

    ALTER TABLE partitioned_tab
   CONVERT TABLE tab1
   TO PARTITION part_name VALUES LESS THAN (1000000);

  • A partition can be converted into a table with ALTER TABLE .. CONVERT PARTITION .. TO TABLE: (MDEV-22166)

    ALTER TABLE partitioned_tab
   CONVERT PARTITION part_name
   TO TABLE tab1;

  • CREATE TABLE syntax has been extended, so the PARTITION keyword is optional in each partition definition: (MDEV-26471)

    CREATE TABLE partitioned_tab (
   col1 int
)
PARTITION BY RANGE(col1) (
   part1 VALUES LESS THAN (1000000),
   part2 VALUES LESS THAN (2000000),
   part3 VALUES LESS THAN (3000000),
   part4 VALUES LESS THAN (4000000),
   part5 VALUES LESS THAN (5000000),
   part_end VALUES LESS THAN MAXVALUE
);

  • Engine-defined attributes can be defined per-partition: (MDEV-5271)

    CREATE TABLE remote_spider_tab (
   id INT,
   str VARCHAR(255),
   PRIMARY KEY(id)
) ENGINE=Spider
PARTITION BY RANGE(id) (
   PARTITION east_part VALUES LESS THAN (100) REMOTE_SERVER="mdb-east.example.org" REMOTE_TABLE="tab1",
   PARTITION west_part VALUES LESS THAN MAXVALUE REMOTE_SERVER="mdb-west.example.org", REMOTE_TABLE="tab1"
);

System Versioning

SQL Level Enhancements

Indexes

  • Descending indexes are supported: (MDEV-13756)

    • When used with a composite index, can be used to get a significant performance boost for queries that perform ORDER BY operations on columns in different orders than the defined order.

    • In previous releases, MariaDB Enterprise Server already supported the DESC option for ORDER BY, but the optimizer would use the ascending index. For a composite index, the optimizer would have to use an index and perform a file sort.

    • For example, using the following example table:

      CREATE TABLE sections (
   top_level int,
   sub_level int,
   index top_asc_sub_asc (top_level ASC, sub_level ASC),
   index top_asc_sub_desc (top_level ASC, sub_level DESC),
   index top_desc_sub_asc (top_level DESC, sub_level ASC),
   index top_desc_sub_desc (top_level DESC, sub_level DESC)
);
INSERT INTO sections VALUES
   (1, 1), (1, 2), (2, 1), (2, 2),
   (3, 1), (3, 2), (3, 3);

    • Performing an ORDER BY .. ASC on multiple columns still shows "Using index" in the EXPLAIN output:

      EXPLAIN SELECT * FROM sections
   ORDER BY top_level ASC, sub_level ASC;

      +------+-------------+----------+-------+---------------+-----------------+---------+------+------+-------------+
| id   | select_type | table    | type  | possible_keys | key             | key_len | ref  | rows | Extra       |
+------+-------------+----------+-------+---------------+-----------------+---------+------+------+-------------+
|    1 | SIMPLE      | sections | index | NULL          | top_asc_sub_asc | 10      | NULL | 7    | Using index |
+------+-------------+----------+-------+---------------+-----------------+---------+------+------+-------------+

    • With this change, performing a mix of ORDER BY .. ASC, .. DESC on multiple columns also shows "Using index" in the EXPLAIN output:

      EXPLAIN SELECT * FROM sections
   ORDER BY top_level ASC, sub_level DESC;

      +------+-------------+----------+-------+---------------+------------------+---------+------+------+-------------+
| id   | select_type | table    | type  | possible_keys | key              | key_len | ref  | rows | Extra       |
+------+-------------+----------+-------+---------------+------------------+---------+------+------+-------------+
|    1 | SIMPLE      | sections | index | NULL          | top_asc_sub_desc | 10      | NULL | 7    | Using index |
+------+-------------+----------+-------+---------------+------------------+---------+------+------+-------------+

JSON

  • JSON_EQUALS() can be used to compare two documents and determine if they are equal. (MDEV-16375)

  • JSON_NORMALIZE() can be used to normalize two JSON documents to make them more comparable. (MDEV-23143)

  • JSON_OVERLAPS() can be used to compare two JSON documents to determine if they have any key-value pairs or array elements in common. (MDEV-27677)

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

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

  • JSON_SCHEMA_VALID() can be used to validate a JSON document against a JSON schema, as documented by the JSON Schema Draft 2020. (MDEV-27128)

    • 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.

  • Negative indexes can be used to access values in JSON arrays relative to the end of the array when a JSON Path expression is used as a parameter to a JSON function. (MDEV-22224)

    SELECT JSON_REMOVE(@json, '$.A[-10]')

  • The last index can be used to access the last value in a JSON array when a JSON Path expression is used as a parameter to a JSON function. (MDEV-22224)

    SELECT JSON_REMOVE(@json, '$.A[last]');

  • A range of indexes can be used to access the values in that range in a JSON array when a JSON Path expression is used as a parameter to a JSON function. (MDEV-27911)

    SELECT JSON_REMOVE(@json, '$.A[1 to 3]');

Data Types

  • UUID data type is added to store UUIDs more efficiently. (MDEV-4958)

  • INET4 data type is added to store IPv4 addresses as BINARY(4), where each byte stores one octet. (MDEV-23287)

    • The data type provides the following functionality:

      • Validation of incorrect values

      • Comparisons

      • Sorting

      • Functions like CAST()

Functions

  • RANDOM_BYTES() returns a binary string of a length between 1 and 1024 bytes. (MDEV-25704)

    • This nondeterministic value is generated by the cryptographically secure pseudo random generator (CSPRNG) of the SSL library, so it generates an arbitrary length string of cryptographic random bytes that are suitable for cryptographic use.

  • NATURAL_SORT_KEY() can be used to perform a natural sort of strings. (MDEV-4742)

    • Characters are sorted in alphabetical order, whereas numbers are sorted, such that "10" is greater than "9".

    • For example, "v10" would appear after the string "v9".

  • SFORMAT() formats strings based on the specified options and values to generate a custom formatted string. (MDEV-25015)

    SELECT SFORMAT("MariaDB version {}", VERSION());

    • This function uses the fmtlib library for string formatting similar to Python, Rust, C++20.

  • CRC32() computes a cyclic redundancy check (CRC) as a 32-bit unsigned value using the ISO 3309 polynomial. (MDEV-27208)

    • The CRC can now be computed in pieces, using an optional second parameter: CRC32('String') is equal to CRC32(CRC32('Str','ing')).

    • CRC32C() can be used to compute checksums using the alternate Castagnoli polynomial.

Character Sets and Collations

  • Collations based on the Unicode Collation Algorithm (UCA) 14.0.0 have been added for the character sets utf8mb3, utf8mb4, ucs2, utf16, and utf32: (MDEV-27009)

    • One neutral and 22 language specific collations have been added.

    • Accent sensitive, accent insensitive, case sensitive, case insensitive, no-pad variants have been added.

    • Collation names for new UCA 14.0.0 collations can be specified without the character set prefix, because the character set prefix can be automatically detected from the context. For example, uca1400_german_as_ci can be specified instead of utf8mb4_uca1400_german_as_ci. Collation names with character set prefixes are accepted for the new UCA 14.0.0 collations, but they are optional.

    • When the information_schema.COLLATIONS table is queried for metadata about the new UCA 14.0.0 collations, the COLLATION_NAME column contains the collation name without a character set prefix, and the CHARACTER_SET_NAME column contains NULL, which indicates that these collations can apply to multiple character sets.

    • When the information_schema.COLLATION_CHARACTER_SET_APPLICABILITY table is queried for character set applicability of the new UCA 14.0.0 collations, the COLLATION_NAME column also contains the collation name without a character set prefix. A new FULL_COLLATION_NAME column has been added, which contains the full collation name (with a character set prefix) for all collations, including new UCA 14.0.0 collations.

    • Improved contraction performance in UCA collations.

    • Improved UCA collation performance for the utf8mb3 and utf8mb4 character sets.

  • On Microsoft Windows, MariaDB command-line tools now include full Unicode support. (MDEV-26713)

    • Unicode support is available on Microsoft Windows 10 1909 or later, Microsoft Windows 11, and Microsoft Windows Server 2020.

    • The my.ini configuration file is now UTF-8 encoded.

    • The mariadb.exe command-line client uses utf8mb4 as the default character set.

Security Features

MariaDB Replication

  • For all storage engines, ALTER TABLE is divided into two phases to prevent long-running DDL statements from causing replication lag on replicas: (MDEV-11675)

    • Enabled by setting binlog_alter_two_phase=1, which is not the default.

    • Two-phase ALTER TABLE is optimistic, so the operation begins on the replica server before it finishes on the primary server.

    • Two events are written to the binary log on the primary server: a START ALTER event when the operation starts, and either a COMMIT ALTER event or a ROLLBACK ALTER event when the operation finishes depending on whether it succeeds or fails.

  • By default, replication uses Global Transaction IDs (GTID), which makes replicas crash-safe. (MDEV-19801)

    • This change impacts backward compatibility.

    • In previous releases, when CHANGE MASTER TO was executed without explicitly specifying MASTER_USE_GTID, it would default to MASTER_USE_GTID=no.

    • Starting with this release, when CHANGE MASTER TO is executed without explicitly specifying MASTER_USE_GTID, it defaults to MASTER_USE_GTID=slave_pos. With MASTER_USE_GTID=slave_pos, the replica server uses the gtid_slave_pos system variable as the GTID position.

    • This change can cause new behavior to occur when performing the following operations:

      • Setting up a new replica with CHANGE MASTER TO without specifying MASTER_USE_GTID

      • Freshly starting a stopped replica with START REPLICA if the replica configuration does not explicitly have MASTER_USE_GTID set.

      • Resetting a replica with RESET REPLICA

    • When MASTER_LOG_FILE and MASTER_LOG_POS are explicitly set, MASTER_USE_GTID=no is implicitly set.

  • Maximum allowed execution time for a replicated query can be specified by setting the slave_max_statement_time system variable. (MDEV-27161)

    • The new system variable can be used to decrease replication lag on a replica, but it can also cause the replica to become inconsistent with the primary server. Therefore, the system variable is disabled by default to prevent inconsistencies.

    • The slave_max_statement_time system variable was previously backported to MariaDB Enterprise Server 10.5.18-13 and 10.6.11-6.

    • When a replicated query times out, the ER_SLAVE_STATEMENT_TIMEOUT error code is raised.

    • When multiple replicas are configured, all replicas use the same slave_max_statement_time value.

  • For mariadb-binlog, the start and stop positions can be specified as GTIDs: (MDEV-4989)

    $ mariadb-binlog --start-position='0-1-1001,1-2-1000' \
   --stop-position='0-1-2000,1-2-1050' \
   mariadb-bin.000001

    • The --start-position command-line option can set the starting GTID position as a comma-separated list of GTIDs. For each specified GTID domain, the GTID position is exclusive, so an event is only printed if its sequence number is greater than the sequence number specified for that domain.

    • The --stop-position command-line option can set the ending GTID position as a comma-separated list of GTIDs. For each specified GTID domain, the GTID position is inclusive, so an event is only printed if its sequence number is greater than or equal to the sequence number specified for that domain.

    • Both command-line options require each GTID in the comma-separated list to have a different domain ID.

  • For mariadb-binlog, events can be filtered based on the domain IDs and server IDs in the event's GTID: (MDEV-4989, MDEV-20119)

    $ mariadb-binlog --do-domain-ids='0,1' \
   --do-server-ids='1,2' \
   mariadb-bin.000001

    • The --do-domain-ids command-line option can set domain IDs that should be read as a comma-separated list of domain IDs. If an event's GTID does not have one of the specified domain IDs, the event is not printed.

    • The --ignore-domain-ids command-line option can set domain IDs that should be ignored as a comma-separated list of domain IDs. If an event's GTID has one of the specified domain IDs, the event is not printed.

    • The --do-server-ids command-line option can set server IDs that should be read as a comma-separated list of server IDs. If an event's GTID does not have one of the specified server IDs, the event is not printed. This is an alias for the --server-id command-line option, and it allows for a list of server IDs instead of a single server ID.

    • The --ignore-server-ids command-line option can set server IDs that should be ignored as a comma-separated list of server IDs. If an event's GTID has one of the specified server IDs, the event is not printed.

    • When events are filtered on server IDs and domain IDs, an event is only printed when its GTID meets both conditions

  • replicate_rewrite_db is now a system variable and can be changed dynamically. (MDEV-15530)

    • Run STOP REPLICA to stop running replicas before changing the value dynamically.

    • If the slave thread is running when the value is changed, an error is raised with the ER_SLAVE_MUST_STOP error code and the following error message:

      ERROR 1198 (HY000): This operation cannot be performed as you have a running slave

Galera Cluster

The following changes pertain to Galera Cluster with MariaDB Enterprise Server 23.07:

  • New connection states in SHOW [FULL] PROCESSLIST and information_schema.PROCESSLIST better reflect the state of the connection: (MDEV-26352)

    Connection State

    Description

    waiting to execute in isolation

    The connection is executing a DDL statement with wsrep_osu_method=TOI, but the operation requires other concurrent operations to finish first, so the DDL statement can be executed in isolation.

    waiting for TOI DDL

    Another connection is executing a DDL statement with wsrep_osu_method=TOI, so this connection must wait for the DDL statement to finish.

    waiting for flow control

    The connection is committing a transaction, but transactions are currently paused due to flow control, so the connection is waiting for the cluster to catch up and unpause transactions.

    waiting for certification

    The connection is committing a transaction, but it is waiting for the other cluster nodes to certify the transaction.

  • Node state changes can be saved to a machine-readable JSON file configured by the wsrep_status_file system variable: (MDEV-26971)

    [mariadb]
wsrep_status_file=galera_status.json

    • The JSON file can be read by monitoring tools.

    • When wsrep_status_file is set to a path, the node state changes are written to the specified file.

    • When wsrep_status_file is set to none, this functionality is disabled.

  • Progress reporting for MariaDB Enterprise Backup-based SST by configuring the progress option in the [sst] option group: (MDEV-26971)

    [mariadb]
wsrep_sst_method=mariabackup
wsrep_debug=1

[sst]
progress=1
rlimit=100m

    • Progress reporting is only supported for MariaDB Enterprise Backup-based SST, so wsrep_sst_method=mariabackup must be set.

    • Progress reporting is only enabled when wsrep_debug=1 is set.

    • When progress=1 is set, progress reporting goes to standard error (stderr).

    • When progress is set to a path, progress reporting is written to the specified file.

    • When progress is set to none, progress reporting is disabled.

    • rlimit can be used to set a rate limit in bytes. The value can use a suffix to represent a unit: k for kilobytes, m for megabytes, g for gigabytes, and t for terabytes.

    • The pv utility must be installed for SST progress reporting.

    • When progress reporting is enabled, the following SST progress message is written to the MariaDB log during an SST:

      [Note] WSREP: REPORTING SST PROGRESS: '{ "from": DONOR_NODE_ID, "to": JOINER_NODE_ID, "total": TOTAL_BYES, "done": DONE_BYTES, "indefinite": -1 }'

  • An IP allowlist defines the IP addresses permitted to join a running Galera Cluster: (MENT-425)

    [mariadb]
wsrep_allowlist='192.0.2.2,192.0.2.3,192.0.2.4'

    • The wsrep_allowlist system variable can be used to set a comma-separated list of IP addresses to allow.

    • Only nodes from IP addresses in the IP allowlist can request an SST or IST to sync cluster data from a donor node in the cluster.

    • The mysql.wsrep_allowlist system table can be used to view the IP allowlist. The system table only exists when Galera is loaded.

  • When plugin-wsrep-provider=ON is set, the wsrep_provider_options can be set as individual options: (MDEV-22570)

    [mariadb]
plugin-wsrep-provider=ON
gcache.size='4G'
gcache.recover='YES'

    • The wsrep_provider REPLICATION plugin is added as part of this change.

Interface Changes

Collations interface changes

Galera Cluster interface changes

MariaDB Query Optimizer interface changes

InnoDB Storage Engine interface changes

Spider Storage Engine interface changes

Platforms

In alignment to the enterprise lifecycle, MariaDB Enterprise Server 23.07.0 is provided for:

  • Debian 10 (x86_64, ARM64)

  • Debian 11 (x86_64, ARM64)

  • Debian 12 (x86_64, ARM64)

  • Microsoft Windows (x86_64)

  • Red Hat Enterprise Linux 8 (x86_64, ARM64)

  • Red Hat Enterprise Linux 9 (x86_64, ARM64)

  • Rocky Linux 8 (x86_64, ARM64)

  • Rocky Linux 9 (x86_64, ARM64)

  • SUSE Linux Enterprise Server 15 (x86_64, ARM64)

  • Ubuntu 20.04 (x86_64, ARM64)

  • Ubuntu 22.04 (x86_64, ARM64)

Some components of MariaDB Enterprise Server are supported on a subset of platforms. See MariaDB Engineering Policies for details. Additionally, MariaDB Enterprise ColumnStore is not currently available for Debian 12.

Installation Instructions