Release Notes for MariaDB Enterprise Server 23.08.0

Overview

MariaDB Enterprise Server 23.08.0 is the second Technical Preview release of MariaDB Enterprise Server 23.08. This release contains a variety of new features.

This release note is cumulative and includes the changes present in MariaDB Enterprise Server 23.07.0. Those changes introduced since the first Technical Preview (MariaDB Enterprise Server 23.07.0) are denoted with (new).

The changes listed here are relative to MariaDB Enterprise Server 10.6.14-9.

Software in technical preview is not recommended for production workloads.

MariaDB Enterprise Server 23.08.0 was released on 2023-08-30.

Changes in Storage Engines

  • This release incorporates MariaDB Enterprise ColumnStore 23.02.4.

  • With InnoDB storage engine, the process to import an InnoDB tablespace has been simplified. (new) (MDEV-26137)

    • Prior to this release, the process was to create a table, discard the tablespace, then execute ALTER TABLE IMPORT TABLESPACE.

    • Starting with this release, ALTER TABLE IMPORT TABLESPACE is the only command needed.

    • For example:

      FLUSH TABLES t1 FOR EXPORT;
--copy_file $MYSQLD_DATADIR/test/t1.cfg $MYSQLD_DATADIR/test/t2.cfg
--copy_file $MYSQLD_DATADIR/test/t1.frm $MYSQLD_DATADIR/test/t2.frm
--copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/test/t2.ibd
UNLOCK TABLES;
ALTER TABLE t2 IMPORT TABLESPACE;

  • With InnoDB storage engine, space occupied by freed pages within the InnoDB system tablespace can be reclaimed. (new) (MDEV-14795)

    • Prior to this release, InnoDB data files would never shrink during normal operation. .ibd files could be shrunk by rebuilding tables with OPTIMIZE TABLE and undo tablespace files using SET GLOBAL innodb_undo_log_truncate=ON

    • Starting with this release, an :autoshrink attribute has been added for the innodb_data_file_path system variable.

    • With :autoshrink, the InnoDB system tablespace can be truncated after the last allocated page within it, down to the specified minimum size.

    • For example, with this configuration the InnoDB system tablespace can be shrunk down to 12MiB:

      [mariadb]
...
innodb_data_file_path=ibdata1:12M;ibdata2:50M:autoextend:autoshrink

  • With InnoDB storage engine, system variable changes provide improved control of log files and data files: (MDEV-30136)

  • With InnoDB storage engine, performance of bulk inserts is improved. (MDEV-25036)

  • With InnoDB storage engine, changes to the InnoDB redo log format reduce write amplification, which can result in better performance. (MDEV-14425)

  • With InnoDB storage engine, the InnoDB change buffer has been removed: (MDEV-29694)

  • With InnoDB storage engine, the Prefix Index Queries Optimization is always used: (MDEV-28540)

  • With InnoDB storage engine, multiple undo tablespaces are now enabled by default, so that the default configuration enables undo logs to be truncated while the server is running: (MDEV-29986)

    • Truncation does not apply to undo logs in the system tablespace.

    • innodb_undo_tablespaces default changed from 0 to 3.

    • To reclaim space, innodb_undo_log_truncate=ON must be set

    • innodb_undo_log_truncate=ON can have a performance impact for some workloads. In those cases, undo truncation can be enabled by temporarily setting the following:

      SET GLOBAL innodb_undo_log_truncate=ON;

  • With Spider storage engine, engine-defined attributes (table options) are accepted. Previously, Spider required parameters to be provided via COMMENT for a table: (MDEV-27106)

    New Table Option

    Old COMMENT Option

    Description

    REMOTE_DATABASE

    database

    The remote database that contains the remote table

    REMOTE_SERVER

    srv

    The IP address or hostname of the remote server that contains the remote table

    REMOTE_TABLE

    tbl

    The remote table

  • Spider storage engine system variable defaults have changed.

  • With MyRocks storage engine, log files can be stored in a user-defined directory specified by the rocksdb_log_dir system variable. (MDEV-27791)

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

  • Online Schema Change (OSC) is new server internal functionality which makes all schema changes (ALTER TABLE commands) non-blocking. (new) (MDEV-16329)

    • OSC targets a problem previously solvable using third-party solutions, in a way which reduces operational impact. Some aspects of the OSC implementation are operationally significant:

      • OSC performs internal Copy-Apply-Replace: First, the altered table gets copied, then the online changes get applied. A short table lock occurs when applying last changes and renaming the tables. The binary log is not used in this process. This is significant because some third-party approaches to this problem depend on client connections which can be subject to connection timeouts and similar factors.

      • OSC is asynchronous: Changes from applications are first stored in an online change buffer. This is significant because some third-party approaches to this problem are synchronous and as result impact the execution of other transactions.

      • OSC is trigger-less: Only server internal handlers for a DML-side check if ALTER TABLE is in progress are used. INSERT, UPDATE, or DELETE triggers based on stored routines are not used. This is significant because some third-party approaches to this problem depend on triggers.

    • By default, when an ALTER operation cannot be executed INSTANT, OSC will be used. If OSC cannot be used, another algorithm will be used.

    • If the LOCK=NONE option is explicitly specified in the ALTER statement, or if the equivalent statement ALTER ONLINE TABLE is used, the operation will be performed if it can be done as OSC and fails otherwise.

    • As an override to this new behavior, if the old_mode system variable is set with LOCK_ALTER_TABLE_COPY, the old behavior is preferred when LOCK=NONE is not explicitly set. (MDEV-31812)

  • Stored routine calls reflect all changes to metadata for objects the stored routine depends on. (new) (MDEV-5816)

    • Prior to this release, a reconnect was needed before a stored routine was able to update its metadata from altered objects. For example, absent a reconnect:

      CREATE TABLE t1 (id INT);
INSERT INTO t1 VALUES (100);
CREATE PROCEDURE p1() SELECT * FROM t1;
CALL p1;

      +------+
| id   |
+------+
|  100 |
+------+

      ALTER TABLE t1 ADD COLUMN b INT DEFAULT 0;
CALL p1;

      +------+
| id   |
+------+
|  100 |
+------+

    • Starting with this release, metadata changes are reflected without a reconnect:

      CREATE TABLE t1 (id INT);
INSERT INTO t1 VALUES (100);
CREATE PROCEDURE p1() SELECT * FROM t1;
CALL p1;

      +------+
| id   |
+------+
|  100 |
+------+

      ALTER TABLE t1 ADD COLUMN b INT DEFAULT 0;
CALL p1;

      +------+------+
| id   | b    |
+------+------+
|  100 |    0 |
+------+------+

  • Temporary tables are now included in information_schema.TABLES, in SHOW TABLES output, and in SHOW TABLE STATUS output. (new) (MDEV-12459)

    • For example:

      CREATE DATABASE test;
USE test;
CREATE TABLE t1 (id int);
CREATE TEMPORARY TABLE t2_temp (id int);
SHOW FULL TABLE;

      +----------------+-----------------+
| Tables_in_test | Table_type      |
+----------------+-----------------+
| t2_temp        | TEMPORARY TABLE |
| t1             | BASE TABLE      |
+----------------+-----------------+

      SELECT table_schema, table_name, table_type FROM information_schema.TABLES WHERE table_schema='test';

      +--------------+------------+------------+
| table_schema | table_name | table_type |
+--------------+------------+------------+
| test         | t2_temp    | TEMPORARY  |
| test         | t1         | BASE TABLE |
+--------------+------------+------------+

  • For INSERT operations that insert multiple rows, error reporting has been improved: (MDEV-10075)

    • In GET DIAGNOSTICS, the ROW_NUMBER property allows retrieval of the row number that caused the error or warning:

      GET DIAGNOSTICS CONDITION 1 @failed_row=ROW_NUMBER;

  • Information Schema system table optimizations: (MDEV-20609)

    • When PARAMETERS is queried and the WHERE clause filters on SPECIFIC_SCHEMA and SPECIFIC_NAME, an index is used to avoid a full table scan.

    • When ROUTINES is queried and the WHERE clause filters on ROUTINE_SCHEMA and ROUTINE_NAME, an index is used to avoid a full table scan.

  • SHOW EXPLAIN FOR CONNECTION_ID can show the query plan for a query running in another connection: (MDEV-25956)

    SHOW EXPLAIN FOR 1;

    • The statement returns the query itself as a warning, which can be obtained via SHOW WARNINGS.

  • SHOW ANALYZE [FORMAT=JSON] FOR CONNECTION_ID can analyze a query running in another connection: (MDEV-27021)

    SHOW ANALYZE FOR 1;

  • ANALYZE FORMAT=JSON now shows the time spent in the query optimizer. (MDEV-28926)

  • With mariadb-dump, the new --order-by-size command-line option allows tables to be dumped in order of size (smallest tables first): (MDEV-28074)

    $ mariadb-dump \
   --user=USER \
   --password='PASSWORD' \
   --all-databases \
   --single-transaction \
   --order-by-size

  • The transaction_isolation system variable can now be used to set the transaction isolation: (MDEV-21921)

    • The tx_isolation system variable is still available as an alias, but it has been deprecated and will be removed in a later release.

  • The transaction_read_only system variable can now be used to set a transaction to read-only. (MDEV-21921)

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_KEY_VALUE() extracts key/value pairs from a JSON object. (new) (MDEV-30145)

    • Syntax: JSON_KEY_VALUE(<json_doc>, <json_path>)

    • The <json_path> specifies the JSON objects whose key/value pairs should be returned. For example:

      SELECT JSON_KEY_VALUE('[[1, {"key1":"val1", "key2":"val2"}, 3], 2, 3]', '$[0][1]');

      +-----------------------------------------------------------------------------+
| JSON_KEY_VALUE('[[1, {"key1":"val1", "key2":"val2"}, 3], 2, 3]', '$[0][1]') |
+-----------------------------------------------------------------------------+
| [{"key": "key1", "value": "val1"}, {"key": "key2", "value": "val2"}]        |
+-----------------------------------------------------------------------------+

    • JSON_KEY_VALUE() can be used as an argument to JSON_TABLE(), adding the key to a result set. For example:

      SELECT jt.* FROM JSON_TABLE(
  JSON_KEY_VALUE('[[1, {"key1":"val1", "key2":"val2"}, 3], 2, 3]', '$[0][1]'),'$[*]'
      COLUMNS (
         k VARCHAR(20) PATH '$.key',
         v VARCHAR(20) PATH '$.value',
        id FOR ORDINALITY )) AS jt;

      +------+------+------+
| k    | v    | id   |
+------+------+------+
| key1 | val1 |    1 |
| key2 | val2 |    2 |
+------+------+------+

  • JSON_ARRAY_INTERSECT() finds the intersection between two JSON arrays. (new) (MDEV-26182)

    • Syntax: JSON_ARRAY_INTERSECT(<array1>, <array2>)

    • For example:

      SET @array1= '[1,2,3]';
SET @array2= '[1,2,4]';
SELECT json_array_intersect(@array1, @array2) as result;

      +--------+
| result |
+--------+
| [1, 2] |
+--------+

      SET @json1= '[[1,2,3],[4,5,6],[1,1,1]]';
SET @json2= '[[1,2,3],[4,5,6],[1,3,2]]';
SELECT json_array_intersect(@json1, @json2) as result;

      +------------------------+
| result                 |
+------------------------+
| [[1, 2, 3], [4, 5, 6]] |
+------------------------+

  • JSON_OBJECT_TO_ARRAY() converts all JSON objects found in a JSON document to JSON arrays. (new) (MDEV-26182)

    • Syntax: JSON_OBJECT_TO_ARRAY(<json_doc>)

    • For example:

      SET @json1= '{ "a" : [1,2,3] , "b": {"key1": "val1", "key2": {"key3": "val3"}} }';
SELECT JSON_OBJECT_TO_ARRAY(@json1) as result;

      +-----------------------------------------------------------------------+
| result                                                                |
+-----------------------------------------------------------------------+
| [["a", [1, 2, 3]], ["b", {"key1": "val1", "key2": {"key3": "val3"}}]] |
+-----------------------------------------------------------------------+

    • Resulting arrays can be compared using JSON_ARRAY_INTERSECT(). For example:

      SET @json1='{"a":[1,2,3],"b":{"key1":"val1","key2":{"key3":"val3"}}}';
SET @json2='{"a":[1,2,3]}';
SELECT JSON_OBJECT_TO_ARRAY(@json1) into @array1;
SELECT JSON_OBJECT_TO_ARRAY(@json2) into @array2;
SELECT JSON_ARRAY_INTERSECT(@array1,@array2) as result;

      +--------------------+
| result             |
+--------------------+
| [["a", [1, 2, 3]]] |
+--------------------+

  • JSON_OBJECT_FILTER_KEYS() returns key/value pairs from a JSON string for keys in an array. (new) (MDEV-26182)

    • Syntax: JSON_OBJECT_FILTER_KEYS(<json_doc>,<array_keys>)

    • For example:

      SET @json1= '{ "a": 1, "b": 2, "c": 3}';
SELECT JSON_OBJECT_FILTER_KEYS (@json1, ' ["b", "c"] ') as result;

      +------------------+
| result           |
+------------------+
| {"b": 2, "c": 3} |
+------------------+

    • Using JSON_ARRAY_INTERSECT() and JSON_KEY() as arguments for JSON_OBJECT_FILTER_KEYS(), a comparison of two JSON strings is possible where only the same keys are compared, not the key/value pairs. For example:

      SET @json1= '{ "a": 1, "b": 2, "c": 3}';
SET @json2= '{"b" : 10, "c": 20, "d": 30}';
SELECT JSON_OBJECT_FILTER_KEYS (@json1, json_array_intersect(json_keys(@json1), json_keys(@json2))) as result;

      +------------------+
| result           |
+------------------+
| {"b": 2, "c": 3} |
+------------------+

  • 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

  • AES_ENCRYPT() and AES_DECRYPT() allow specifying the initialization vector (iv) and block encryption mode (mode). (new) (MDEV-9069)

    • AES_ENCRYPT syntax prior to this release: AES_ENCRYPT(<str>, <key_str>)

    • AES_ENCRYPT syntax starting with this release: AES_ENCRYPT(<str>, <key>, [, iv [, mode]])

    • AES_DECRYPT syntax prior to this release: AES_DECRYPT(<str>, <key_str>)

    • AES_DECRYPT syntax starting with to this release: AES_DECRYPT(<str>, <key>, [, iv [, mode]])

    • The block_encryption_mode system variable specifies which mode will be used if the mode is not specified as a function argument.

    • For example, using the default mode from the block_encryption_mode:

      SELECT @@block_encryption_mode;

      +-------------------------+
| @@block_encryption_mode |
+-------------------------+
| aes-128-ecb             |
+-------------------------+

      SELECT HEX(AES_ENCRYPT('MariaDB','mykey','vector')) as result;

      +----------------------------------+
| result                           |
+----------------------------------+
| CD0352A4B2FB18A592C04FF8CDA6C2F2 |
+----------------------------------+

      SELECT AES_DECRYPT(x'CD0352A4B2FB18A592C04FF8CDA6C2F2','mykey','vector') as result;

      +---------+
| result  |
+---------+
| MariaDB |
+---------+

    • For example, specifying the mode as an argument:

      SELECT HEX(AES_ENCRYPT('MariaDB','mykey','thisismy256vector','aes-256-cbc')) as result;

      +----------------------------------+
| result                           |
+----------------------------------+
| CD6C47183B89A813557BFD639A893CE3 |
+----------------------------------+

      SELECT AES_DECRYPT(x'CD6C47183B89A813557BFD639A893CE3','mykey','thisismy256vector','aes-256-cbc') as result;

      +---------+
| result  |
+---------+
| MariaDB |
+---------+

  • 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

  • The character_set_collations system variable allows the default collation for a character set to be changed globally or for a session. (new) (MDEV-30164)

    • Starting with this release, the default collation is used whenever a character set is defined for a database object but the collation is not defined.

    • Starting with this release, when the character set is not defined, the default collation is as specified by the collation_server system variable.

    SET @@character_set_collations='utf8mb4=uca1400_ai_ci';
CREATE DATABASE test_with_charset CHARACTER SET utf8mb4;
CREATE DATABASE test;
SELECT SCHEMA_NAME,DEFAULT_COLLATION_NAME FROM SCHEMATA WHERE SCHEMA_NAME LIKE "test%";

    +-------------------+------------------------+
| SCHEMA_NAME       | DEFAULT_COLLATION_NAME |
+-------------------+------------------------+
| test_with_charset | utf8mb4_uca1400_ai_ci  |
| test              | utf8mb4_general_ci     |
+-------------------+------------------------+

  • 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

  • Binary log filter options binlog-do-db, binlog-ignore-db, and binlog-row-event-max-size are now visible as system variables. (new) (MDEV-30188)

    • For example:

      SHOW GLOBAL VARIABLES WHERE
  Variable_name LIKE 'binlog_do_db' OR
  Variable_name LIKE 'binlog_ignore_db' OR
  Variable_name LIKE 'binlog_row_event_max_size';

      +---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| binlog_do_db              |       |
| binlog_ignore_db          |       |
| binlog_row_event_max_size | 8192  |
+---------------------------+-------+

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

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