Announcing MariaDB Community Server 11.3 GA and 11.4 RC
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