--- title: "Announcing MariaDB Community Server 11.1 GA and 11.2 RC" publish_date: 2023-08-21 author: "Ralf Gebhardt" channel: - name: "Product" url: "/resources/blog/channel/product.md" tags: - name: "Community Server" url: "/resources/blog/tag/community-server.md" - name: "InnoDB" url: "/resources/blog/tag/innodb.md" - name: "JSON" url: "/resources/blog/tag/json.md" - name: "Online Schema Change" url: "/resources/blog/tag/online-schema-change.md" - name: "Replication" url: "/resources/blog/tag/replication.md" --- # Announcing MariaDB Community Server 11.1 GA and 11.2 RC 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](https://staging-mdb.com/resources/blog/announcing-mariadb-community-server-11-0-ga-and-11-1-rc/) 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(,)` 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(, )` 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()` 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(,)` 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)](https://staging-mdb.com/resources/blog/announcing-mariadb-community-server-11-0-ga-and-11-1-rc/) [Download MariaDB Community Server](https://staging-mdb.com/downloads/community/community-server/) 11.1 GA or 11.2 RC at [mariadb.com/downloads](https://staging-mdb.com/downloads/community/community-server/)