Announcing MariaDB Community Server 11.1 GA and 11.2 RC

Posted on August 21, 2023 by Ralf Gebhardt

We are pleased to announce the general availability (GA) of MariaDB Community Server 11.1 and the first release candidate (RC) of MariaDB Community Server 11.2.

MariaDB Community Server 11.1 now GA

MariaDB Community Server 11.1 adds features such as the new JSON function JSON_SCHEMA_VALID to validate a JSON document against a JSON schema, the optimizer changes for a comparison of a DATE function to a constant value and to allow single-table UPDATE and DELETE to take advantage of semi-join optimization and the new release series 23.02 of the MariaDB ColumnStore engine.

Read this blog for a full list of all new features in the 11.1 release series. The MariaDB Community Server 11.1 release series started with Preview Releases, then an RC release in June 2023. MariaDB Community Server 11.1.2 marks the first GA release in this series.

MariaDB Community Server 11.2 now RC

Our next release series, 11.2 provides a built-in Online Schema Change (OSC) feature, new JSON functions to extract, convert and compare JSON strings, enhancements for InnoDB and for comparison and more.

Built-in Online Schema Change

MariaDB Community Server 11.2 includes 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.

Having an internal OSC in the server eliminates the need for using external command line tools in order to reduce table locks. The OSC implementation in MariaDB Community Server 11.2 is:

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.

In MariaDB Server 11.2 a default ALTER operation will be an OSC operation if possible. If the operation cannot be performed as OSC then another algorithm will be used. If the option LOCK=NONE is explicitly specified in the ALTER statement, or the equivalent statement ALTER ONLINE TABLE is used, the operation will only be performed if it can be done as OSC and fails otherwise.

New JSON Functions

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

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

INNODB

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.

REPLICATION

The binary log filter options binlog-do-db, binlog-ignore-db, binlog-row-event-max-size are now visible as system variables.

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

MORE FOR DEVOPS AND DBAS

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.

Temporary Tables

Temporary tables are now included in information_schema.tables and included in SHOW TABLES and SHOW TABLE STATUS

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

Character Sets and Collations

The default collation used for a character set can now be changed globally or for a session via the new system variable character_set_collations. The default collation will be used whenever a character set is defined for a database object without defining the collation.

When not defining a character set the default collation is still the one defined with the system variable collation_server.

This is also preparatory work for changing default collations to use the UCA 14.0.0 standard. In particular, this variable will allow replication between servers with different default collations.

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

We encourage you to try MariaDB Community Server 11.2 RC today. Please note that we do not recommend running RC releases in production.

Resources

MariaDB Community Server 11.1 RC (blog)

Download MariaDB Community Server 11.1 GA or 11.2 RC at staging-mdb.com/downloads