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

Posted on August 30, 2023 by Ralf Gebhardt and Max Mether

We are pleased to announce the second technical preview of MariaDB Enterprise Server 23 for our subscription customers. This is the newest release for our Enterprise Server product, and incorporates additional new features and capabilities after our first technical preview launched in July 2023, such as the new powerful built-in Online Schema Change. DBAs and DevOps now do not need to work with external tools anymore.

Online Schema Change Now Built-in

New server internal Online Schema Change (OSC) which makes all schema changes (ALTER TABLE commands) non-blocking.

For instant ALTER TABLE operations (e.g. where ALGORITHM=INSTANT is used) OSC is not needed. However for all other ALTER operations OSC provides significant benefits in reducing the locking time to a bare minimum.

The Online Schema Change architecture:

Copy-Apply-Replace: First the altered table gets copied, then the online changes get applied. A short table lock for applying last changes and to rename the tables. The binary log is not needed for this process.
Asynchronous: Changes from applications are first stored in an online change buffer.
Triggerless: 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.

An ALTER operation is using the OSC operation if an instant ALTER is not possible. If the operation cannot be performed as OSC then another algorithm will be used. Specifying the option LOCK=NONE with the ALTER statement, or using the equivalent statement ALTER ONLINE TABLE, will only perform the schema change if it can be done as OSC and fails otherwise.

More Enhancements for Working with JSON

The first MariaDB Enterprise 23 technical preview already includes the new JSON functions JSON_SCHEMA_VALID, JSON_OVERLAPS, JSON_EQUALS() and JSON_NORMALIZE(). Additional functions have been added to the second preview release.

JSON_KEY_VALUE

The new JSON function JSON_KEY_VALUE(<json_doc>,<json_path>) extracts key/value pairs from a JSON object. The JSON path parameter is used to only return key/value pairs for matching JSON objects.

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"}]        |
+-----------------------------------------------------------------------------+

The function JSON_KEY_VALUE() can be used as an argument to JSON_TABLE(), which allows adding the key to a result set.

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

The new JSON function JSON_ARRAY_INTERSECT(<array1>, <array2>) is used to find the intersection between two JSON arrays.

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

The new JSON function JSON_OBJECT_TO_ARRAY(<json_doc>) is used to convert all JSON objects found in a JSON document to JSON arrays where each item in the outer array represents a single key-value pair from the object.

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():

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

The new JSON function JSON_OBJECT_FILTER_KEYS(<json_doc>,<array_keys>) returns key/value pairs from a JSON string for keys defined in <array_keys>.

Example:

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

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

Example (only show key/value pairs of json1 where the key exists in json2):

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} |
+------------------+

Enhanced Encryption Functions

The encryption functions AES_ENCRYPT() / AES_DES_ENCTYPT() now support adding the two new parameters initialization vector (iv) and block encryption mode (mode).

Syntax for older release series:

AES_ENCRYPT(str,key_str)

New syntax:

AES_ENCRYPT(str, key, [, iv [, mode]])

If no mode is provided it will be used from the new system variable block_encryption_mode.

Example (using the mode from system variable 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 |
+---------+

Example (mode provided as 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 |
+---------+

New for InnoDB Tablespaces

The InnoDB tablespace handling got several improvements in MariaDB Enterprise Server ES 23, many of them not visible to the user. The second technical preview brought changes for DBAs and DevOps, an easier way to import tablespaces and a new autoshrink option.

Import Tablespace

The process to import a InnoDB tablespace is now simplified. While the old process required to first create a table and to discard the tablespace before executing ALTER TABLE IMPORT TABLESPACE, now ALTER TABLE IMPORT TABLESPACE is the only command needed.

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;

InnoDB System Tablespace Reclaiming

Starting with MariaDB Community Server 11.2, the space occupied by freed pages within the InnoDB system tablespace can be reclaimed by adding an :autoshrink attribute to innodb_data_file_path, like:

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

This allows the system tablespace to be truncated after the last allocated page within it, all the way to the specified minimum size (here: 12MiB).
In older release series InnoDB data files never shrink in size during normal operation. One could shrink .ibd files by rebuilding tables with OPTIMIZE TABLE, or the undo tablespace files by SET GLOBAL innodb_undo_log_truncate=ON.

Metadata Validation for Stored Routines

Stored routines are now taking changes to metadata into account whenever the stored routine is called, done for any object a stored routine depends on.

In older release series a reconnect was needed before a stored routine was able to update its metadata from altered objects

Example:

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 |
+------+------+

In the above example both calls of the stored procedure p1 would have returned the same result set with the older release series.

For information on all the new features in the tech preview of MariaDB Enterprise Server 23.08, take a look at the release notes.

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