Learn about the newest features available in MariaDB Community Server 11.2 (GA) and 11.3 (RC).

Posted on November 21, 2023 by
spacer

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

MariaDB Community Server 11.2 now GA

MariaDB Community Server 11.2 adds the new built-in Online Schema Change, non blocking ALTER TABLE commands for all storage engines. Our software engineer Nikita Malyavin explains more details in his blog post.  This new release series also includes other changes like new JSON functions JSON_KEY_VALUE, JSON_ARRAY_INTERSECT, JSON_ARRAY_INTERSECT, enhancements to the encryption function, simplified imports of InnoDB table spaces and more.

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

MariaDB Community Server 11.3 now RC

Our next release series, MariaDB Community Server 11.3 provides a new security privilege, enhancements to SQL functions, InnoDB and replication. It also removes some features which have been deprecated in the now EOLed release series.

NEW FOR THE PRIVILEGE SYSTEM

New Privilege “SHOW CREATE ROUTINE”

Before MariaDB Community Server 11.3 a user only could see the definition of a routine, a stored feature or function, when:

  • SELECT privilege exists for the mysql.procs table
  • The user is the definer of the Stored Procedure

Otherwise, a user only sees NULL for the definition of the routine.

With some use cases, a user should be provided access to the definition even if the user was not the one who has defined the routine. It is certainly not good practice to give the user read access to the mysql.procs table, which otherwise would give the user the possibility of accessing all the stored routine’s definitions.

We’ve solved this use case with MariaDB Community Server 11.3 and the new privilege SHOW CREATE ROUTINE. It has been introduced to enable any user with this privilege to view the definition of a stored routine.

Example without privilege SHOW CREATE ROUTINE:

MariaDB [test]> show grants;
+--------------------------------------------------+
| Grants for user1@%                               |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO `user1`@`%`                |
| GRANT SELECT, EXECUTE ON `test`.* TO `user1`@`%` |
+--------------------------------------------------+

MariaDB [test]> show create procedure myProc \G
*************************** 1. row ***************************
           Procedure: myProc
            sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: NULL
character_set_client: utf8mb3
collation_connection: utf8mb3_general_ci
  Database Collation: utf8mb4_general_ci

Example with the new privilege SHOW CREATE ROUTINE:

MariaDB [test]> show grants;
+-----------------------------------------------------------------------+
| Grants for user1@%                                                    |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `user1`@`%`                                     |
| GRANT SELECT, EXECUTE, SHOW CREATE ROUTINE ON `test`.* TO `user1`@`%` |
+-----------------------------------------------------------------------+
MariaDB [test]> show create procedure myProc \G
*************************** 1. row ***************************
           Procedure: myProc
            sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `myProc`()
BEGIN
    SELECT "My Definiton of a Stored Procedure";
END
character_set_client: utf8mb3
collation_connection: utf8mb3_general_ci
  Database Collation: utf8mb4_general_ci

SHOW CREATE ROUTINE privilege can be granted globally, per schema, or on individual routines.

SQL FUNCTIONS

Time zone option for DATE_FORMAT

The new options %Z and %z can be used for the format string of the function

DATE_FORMAT(date, format)

for adding time zone information to the date string.

  • %Z     Time zone abbreviation
  • %z     Numeric time zone +hhmm or -hhmm presenting the hour and minute offset from UTC

Example:

SELECT DATE_FORMAT(NOW(), '%W %d %M %Y %H:%i:%s %Z %z');
+--------------------------------------------------+
| DATE_FORMAT(NOW(), '%W %d %M %Y %H:%i:%s %Z %z') |
+--------------------------------------------------+
| Tuesday 21 November 2023 13:28:34 EST -0500      |
+--------------------------------------------------+

SQL function for key derivation

The SQL function KDF () is a key derivation function, useful for generating encryption keys from a user provided password or a passphrase. It can be used to generate encryption keys for encryption functions such as AES_ENCRYPT.

KDF(key_str, salt [, {info | iterations} [, kdf_name [, width ]]])

  • kdf_name is “hkdf” or “pbkdf2_hmac”
  • width (in bits) can be any number divisible by 8
  • info is a non-secret parameter of the hkdf method, it allows to generate different encryption keys for different purposes from the same secret password
  • iterations is a positive numeric parameter of the pbkdf2_hmac method, larger values make the password more difficult to brute-force.

Example:

select hex(kdf('foo', 'bar', 'info', 'hkdf'));
+----------------------------------------+
| hex(kdf('foo', 'bar', 'info', 'hkdf')) |
+----------------------------------------+
| 710583081D40A55F0B573A76E02D8975       |
+----------------------------------------+

insert into tbl values (aes_encrypt(@secret_data, kdf("Passw0rd", "NaCl", "info", 'hkdf'), "iv"));

STORAGE ENGINE INNODB

Shrink temporary InnoDB tablespaces without restart

After MariaDB Community Server 11.2 added InnoDB System Tablespace Reclaiming, MariaDB Community Server 11.3 added a new option to shrink temporary InnoDB tablespaces.

Before MariaDB Community Server 11.3 the only way to reclaim disk space used by temporary InnoDB tablespaces was to restart the server, as temporary tablespaces are deleted when you stop the server and are recreated with their configured size.

Restarting the server is not always possible, while you still need to reclaim disk space. MariaDB Community Server 11.3 can solve this problem by executing

SET GLOBAL innodb_truncate_temporary_tablespace_now=1;

This triggers to reclaim the disk space, but existing tables will not be removed.

Example:

CREATE TEMPORARY TABLE t1(f1 INT NOT NULL, f2 INT NOT NULL)ENGINE=InnoDB;
INSERT INTO t1 SELECT seq, seq FROM seq_1_to_65536;
DROP TABLE t1;
SELECT NAME, FILE_SIZE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE NAME="innodb_temp
orary"; 
+------------------+-----------+
| NAME             | FILE_SIZE |
+------------------+-----------+
| innodb_temporary |  79691776 |
+------------------+-----------+
SET GLOBAL INNODB_TRUNCATE_TEMPORARY_TABLESPACE_NOW= 1;
SELECT NAME, FILE_SIZE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE NAME="innodb_temp
orary"; 
+------------------+-----------+
| NAME             | FILE_SIZE |
+------------------+-----------+
| innodb_temporary |  12582912 |
+------------------+-----------+

REPLICATION

New “SQL_BEFORE_GTIDS” and “SQL_AFTER_GTIDS” for START REPLICA UNTIL

The new options SQL_BEFORE_GTIDS and SQL_AFTER_GTIDS for START REPLICA UNTIL allow user control of whether the replica stops before or after a provided GTID state. Its syntax is:

START SLAVE UNTIL (SQL_BEFORE_GTIDS|SQL_AFTER_GTIDS)=”<gtid_list>”

When providing SQL_BEFORE_GTIDS=”<gtid_list>”, for each domain specified in the gtid_list, the replica will execute transactions up to the GTID found, and immediately stop processing events in that domain without executing the transaction of the specified GTID.

Once all domains have stopped, the replica will stop. Events originating from domains that are not specified in the list are not replicated.

START SLAVE UNTIL SQL_AFTER_GTIDS=”<gtid_list>” is an alias to the default behavior of START SLAVE UNTIL master_gtid_pos=”<gtid_list>”, the known behavior before MariaDB Community Server 11.3.

The replica will execute transactions originating from domain ids provided in the list, and will stop once all transactions provided in the UNTIL list have all been executed.

Example:

=========

If a primary server has a binary log consisting of the following GTIDs:

0-1-1
1-1-1
0-1-2
1-1-2
0-1-3
1-1-3

Given a fresh replica (i.e. one with an empty GTID position, @@gtid_slave_pos=”) is started with SQL_BEFORE_GTIDS, i.e.

START SLAVE UNTIL SQL_BEFORE_GTIDS=”1-1-2”

the resulting gtid_slave_pos of the replica will be “1-1-1” because the replica will execute only events from domain 1. When it sees the transaction with sequence number 2 it immediately stops without executing it.

If the replica is started with SQL_AFTER_GTIDS, i.e.

START SLAVE UNTIL SQL_AFTER_GTIDS=”1-1-2”

the resulting gtid_slave_pos of the replica will be “1-1-2” because the replica will execute only events from domain 1. But in this case it stops after executing the provided GTID.

MORE FOR DEVOPS AND DBAS

Process list now includes number of rows sent by the statement

The new value SENT_ROWS in the information schema table PROCESSLIST includes the number of rows sent by the current statement, shown in the processlist.

  • Selects with functions show the total number of rows sent by the main statement and all functions
  • Stored procedures show the total number of rows sent per stored procedure statement
  • INSERT RETURNING and DELETE RETURNING show the total number of rows sent for the returning data set

Example:

 select * from processlist\G
*************************** 1. row ***************************
...
*************************** 2. row ***************************
             ID: 6
           USER: root
           HOST: localhost
             DB: test
        COMMAND: Query
           TIME: 1
          STATE: Sending data
           INFO: select * from t1
        TIME_MS: 1340.406
          STAGE: 0
      MAX_STAGE: 0
       PROGRESS: 0.000
    MEMORY_USED: 89856
MAX_MEMORY_USED: 392544
  EXAMINED_ROWS: 0
      SENT_ROWS: 3895737
       QUERY_ID: 436
    INFO_BINARY: select * from t1
            TID: 100

 

REMOVED AND DEPRECATED OPTIONS

Removed Features

System Variables date_format, datetime_format, time_format, wsrep_causal_reads have been removed.

Deprecated Options
  • Innobackupex mode for mariabackup is now deprecated and will be removed in a further release
  • Non-default values of old_mode are now deprecated and will be removed in a further release
    • LOCK_ALTER_TABLE_COPY
    • ZERO_DATE_TIME_CAST
    • NO_DUP_KEY_WARNINGS_WITH_IGNORE
    • NO_PROGRESS_INFO
    • IGNORE_INDEX_ONLY_FOR_JOIN
    • COMPAT_5_1_CHECKSUM

 

Resources

MariaDB Community Server 11.2 RC (blog)

ALTER TABLE is now universally online

Download MariaDB Community Server 11.2 GA or 11.3 RC at mariadb.com/downloads