Announcing MariaDB Community Server 11.3 GA and 11.4 RC

Posted on February 16, 2024 by Ralf Gebhardt

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

MariaDB Community Server 11.3 now GA

MariaDB Community Server 11.3 provides a new security privilege, and enhancements to SQL functions, InnoDB and replication. Some of the specific highlights of MariaDB Community Server 11.3 include:

Shrinking temporary InnoDB tablespaces is now possible without restarting the database.
To execute transactions of the replication relay log up to a provided global transaction ID (GTID), it can be defined if the provided GTID should be included or not, by using the new options SQL_BEFORE_GTIDS and SQL_AFTER_GTIDS for START REPLICA UNTIL.
A new privilege SHOW CREATE ROUTINE has been introduced to enable any user with this privilege to view the definition of a stored routine without needing to give read access to the system table mysql.procs.
A new time zone option has been added to the function DATE_FORMAT.

Read this blog for a full list of all new features in the 11.3.2 release, including some examples. The MariaDB Community Server 11.3 release series started with Preview Releases, then a RC release in November 2023.

MariaDB Community Server 11.4 now RC

Our next release series, MariaDB Community Server 11.4 provides performance enhancements to partitioning, a simplified way to use SSL, using packages for stored routines independent of the used sql mode, and more.

Privileges/Security Enhancements

SSL Encrypted by Default

Using SSL (a more correct term would be TLS, but in reality SSL is more commonly used) has been simplified with MariaDB Server 11.4. Before version 11.4, proper SSL configuration required multiple manual steps for the server and all the clients connecting to it.

Now the client can verify the server self-signed certificate without any configuration whatsoever. The server completely automatically generates the SSL certificate and the client automatically verifies it as needed.

This simplification allows the server to now require SSL encrypted connections by default and to refuse unencrypted connections.

For more details and limitations please read the blog post Mission Impossible: Zero-Configuration SSL, written by Sergei Golubchik, which has been published when the enhancements have been added to the 11.3 preview release. More testing was needed for this critical feature, which therefore was added only to this release.

Additionally, 11.4 allows users to verify SSL certificates using their fingerprints.

Retrieve Users Privileges on a Specific Table

MariaDB Server provides information about privileges a user has to a table in different views in the INFORMATION_SCHEMA, split between global, schema and table privileges. Before 11.4, there was no easy way to list all the tables a user has access to, this information has to be queried from several tables.

MariaDB Community Server 11.4 now provides a new view privileges_by_table_by_level in the SYS schema that lists the privilege and privilege level per user, schema and table.

Example:

CREATE DATABASE test;
use test;
CREATE TABLE t1 (id int);
CREATE USER user1;
GRANT SELECT, UPDATE ON *.* TO user1;
CREATE USER user2;
GRANT SELECT ON test.* TO user2;
CREATE USER user3;
GRANT SELECT ON test.t1 TO user3; 

SELECT * FROM sys.privileges_by_table_by_level WHERE GRANTEE NOT LIKE "'root'@'%'";
+--------------+------------+-------------+-----------+--------+
| TABLE_SCHEMA | TABLE_NAME | GRANTEE     | PRIVILEGE | LEVEL  |
+--------------+------------+-------------+-----------+--------+
| test         | t1         | 'user1'@'%' | SELECT    | GLOBAL |
| test         | t1         | 'user1'@'%' | UPDATE    | GLOBAL |
| test         | t1         | 'user2'@'%' | SELECT    | SCHEMA |
| test         | t1         | 'user3'@'%' | SELECT    | TABLE  |
+--------------+------------+-------------+-----------+--------+

Partitioning

Exchange a Partition or Convert a Table Without Validation

The process of exchanging a partition with a table or converting a table to a partition can be a very slow operation, especially for larger tables because for each new data row, the partitioning definitions need to be verified to validate that the new row should indeed be in this partition.

This process can now be sped up by disabling this validation. This new feature should be used with care, as it can lead to inconsistencies if the partitioning rules are not met.

The new addition to ALTER TABLE is:

EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH | WITHOUT} VALIDATION]

CONVERT TABLE normal_table TO partition_definition [{WITH | WITHOUT} VALIDATION]

Replication

Global Limitation of Space Used by Binary Logs

The new system variable max_binlog_total_size (alias binlog_space_limit) enables binary log purging when the total size of all binary logs exceeds the specified threshold. The default for max_binlog_total_size is 0, meaning that there is no limit. The system variable can be changed without restarting the server.

The new system variable --slave-connections-needed-for-purge, set to 1 by default, assures that binary log purging will not happen until at least that many replicas are connected and do not need purged binary logs anymore.

The new status variable Binlog_disk_use can be used to query the disk space currently used by the binary logs.

Index for Binary Log on GTIDs

An index is now created on the GTIDs of the binary log, which allows a connecting replica to find the position it should start from without the need to scan the whole binary log.

The new system variable binlog_gtid_index (default ON) can be used to disable the creation of indexes.
The new system variable binlog_gtid_index_page_size (default 4096) defines the page size to use for the binary log GTID index.
The new system variable binlog_gtid_index_span_min (default 65536) controls the sparseness of the binary log GTID index.
The new status variables binlog_gtid_index_hit and binlog_gtid_index_miss can be used for monitoring purposes. A miss is an indication that the index file is missing.

SQL Level Enhancements

General Support of Packages for Stored Routines

Before MariaDB Community Server 11.4, the CREATE PACKAGE feature, as well as CREATE PACKAGE BODY, were only supported with sql_mode = ORACLE. They can now be used with any SQL mode.

Example:

DELIMITER $$

CREATE OR REPLACE PACKAGE myPkg
  PROCEDURE p1();
  FUNCTION f1() RETURNS INT;
END;

$$

CREATE OR REPLACE PACKAGE BODY myPkg

  -- variable declarations
  DECLARE v1 INT DEFAULT 1;
  DECLARE v2 INT DEFAULT 10;

  -- routine declarations
  PROCEDURE p1()
  BEGIN
    SELECT v1, v2;
  END;

  FUNCTION f1() RETURNS INT
  BEGIN
    RETURN v1;
  END;

  -- package initialization
  SET v1=v1 + 2;
END;
$$

DELIMITER ;

SELECT myPkg.f1();
+------------+
| myPkg.f1() |
+------------+
|          3 |
+------------+

CALL myPkg.p1();
+------+------+
| v1   | v2   |
+------+------+
|    3 |   10 |
+------+------+

Enhancement to Function CONV()

The function CONV() , which converts a number between numeric base systems, now supports conversions up to base 62. This allows conversions to encodings to capital letters A-Z, lower case letters a-z and numbers 0-9. The old limit was 36, not including lower case letters.

Example:

SELECT CONV(61,10,36);
+----------------+
| CONV(61,10,36) |
+----------------+
| 1P             |
+----------------+
SELECT CONV(61,10,62);
+----------------+
| CONV(61,10,62) |
+----------------+
| z              |
+----------------+

Backup and Restore

Parallelism for mariadb-dump

When mariadb-dump is used with the option -T / --tab= to produce tab-separated text-format data files per table, the new option --parallel (synonym --use-threads) can be used to use several threads in parallel to dump the table data to their .txt files. Parallelism also works if the option --single-tansaction is used.

The option --parallel has been added to mariadb-import as a synonym to --use-threads, which has been available before.

Miscellaneous

Information About Application-time Period Tables

New views PERIOD and KEY_PERIOD_USAGE are added to information_schema.

View PERIODS includes the columns

TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
PERIOD_NAME
START_COLUMN_NAME
END_COLUMN_NAME

to list Application-time period tables, the name defined for a period and the columns used for start and end timestamps.

View KEY_PERIOD_USAGE includes the columns

CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
PERIOD_NAME

Two new columns are added to the COLUMNS view of information_schema

IS_SYSTEM_TIME_PERIOD_START
IS_SYSTEM_TIME_PERIOD_END

Enhancements SQL Error Log Plugin

The SQL Error Log Plugin can be used to log errors sent to clients for later analysis. When option sql_error_log_with_db_and_thread_info=ON is set, the log file is now also showing thread id and the current default schema for the error.

Please review the release notes of MariaDB Server 11.4.0 and 11.4.1 for more changes.

DEPRECATED OPTIONS

Options in Storage Engine Spider

There are dedicated table options in the SPIDER Storage Engine as of MariaDB Community Server 11.3, so using the COMMENT clause for providing options to Spider is now deprecated and will be removed in a later release series.

Resources

MariaDB Community Server 11.3 RC (Blog Post)

Mission Impossible: Zero-Configuration SSL (Blog Post)

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