MariaDB 11.4 Changes & Improvements

An overview of changes, improvements, and what's new in MariaDB Community Server 11.4

The most recent release of MariaDB 11.4 is:

MariaDB 11.4.9 Stable (GA) Download Now

MariaDB 11.4 is a current long-term series, maintained until May 2029.

Upgrading

See Upgrading Between Major MariaDB Versions and Upgrading from MariaDB 10.11 to MariaDB 11.4.

New Features

This list includes all features since the previous long-term release, MariaDB 10.11 (those introduced in the MariaDB 11.0, MariaDB 11.1 and MariaDB 11.2 short-term releases, and the MariaDB 11.3 rolling release).

Optimizer

Major improvements to the Optimizer. See The Optimizer Cost Model from MariaDB 11.0.
Semi-join optimization for single-table UPDATE/DELETE statements. Update and delete statements that use subqueries can now use all subquery optimization strategies that MariaDB offers, so now if you use subqueries in UPDATE or DELETE, these statements will likely be much faster (MDEV-7487, MariaDB 11.1)
Queries with the DATE or YEAR functions comparing against a constant can now make use of indexes, so these will be noticeably quicker in certain instances. For example SELECT * FROM t2 WHERE YEAR(a) = 2019 or SELECT * FROM t2 WHERE DATE(a) <= '2017-01-01'. See Sargable DATE and YEAR (MDEV-8320, MariaDB 11.1)
Queries like UCASE(varchar_col)=... can now use an index on varchar_col if its collation is case insensitive. An optimizer_switch option, sargable_casefold=ON, has been added to enable this optimization. (MDEV-31496, MariaDB 11.3)
Not only ascending, but also descending indexes can now be used to optimize MIN() and MAX() (MDEV-27576)

InnoDB

Shrink temporary tablespaces without restart by setting the innodb_truncate_temporary_tablespace_now system variable. (MDEV-28699, MariaDB 11.3)
The InnoDB system tablespace is now shrunk by reclaiming unused space at startup (MDEV-14795, MariaDB 11.2)

Online Schema Change

ALTER TABLE can now do most operations with ALGORITHM=COPY, LOCK=NONE, that is, in most cases, unless the algorithm and lock level are explicitly specified, ALTER TABLE will be performed using the COPY algorithm while simultaneously allowing concurrent DML statements on the altered table. (MariaDB 11.2)

For more information, refer to, Online Schema Change.

Replication and Binary Log

Binary log writing speed was improved by moving checksum calculations out of the global binlog mutex (MDEV-31273). The binlog_legacy_event_pos system variable is available if the old behavior is desired. This is a contribution by Kristian Nielsen
New system variable max_binlog_total_size enables binary log purging when the total size of all binary logs exceeds the specified threshold. The implementation is based on the patch from Percona (MDEV-31404)
New system variable slave_connections_needed_for_purge disables binary log purging until the number of connected slaves reaches the specified threshold (MDEV-31404).
FULL_NODUP is a new value for the binlog_row_image system variable. It essentially works like FULL, that is all columns are included in the event, but it takes less space, because the after image omits columns that were not changed by the UPDATE statement, and have same values as in the before image. This is a contribution from Alibaba (MDEV-32589)
mariadb-binlog --flashback support for the FULL_NODUP mode. This is a contribution from Alibaba (MDEV-32894).
MariaDB can optionally maintain a special index of GTIDs and their location in the binary log. If enabled (the default), it allows finding very quickly where a new connecting replica should start replicating from. Without an index, this required scanning the binlog. This is a contribution by Kristian Nielsen (MDEV-4991).
Add keywords "SQL_BEFORE_GTIDS" and "SQL_AFTER_GTIDS" for START SLAVE UNTIL (MDEV-27247, MariaDB 11.3). SQL_BEFORE_GTIDS stops the replica when it sees gtids of the option's argument list, without executing them. (MariaDB 11.3)
All binlog* variables are now visible as system variables, specifically binlog_do_db, binlog_ignore_db, binlog_row_event_max_size (MDEV-30188, MariaDB 11.2)

SSL/TLS

SSL is now enabled in the server by default. No configuration necessary, if no server certificate was provided a self-signed certificate will be automatically generated by the server. See Mission Impossible: Zero-Configuration SSL for details (MDEV-31856).
Clients now can validate self-signed server certificates if the mysql_native_password or ed25519 authentication is used and account password is not empty (MDEV-31855).
Clients now require SSL and have --ssl-verify-server-cert enabled by default (MDEV-31857).
Replication clients do that too, MASTER_SSL_VERIFY_SERVER_CERT is enabled by default.
Use --disable-ssl or --disable-ssl-verify-server-cert to revert to the old behavior.
Clients can use new command line options --ssl-fp and --ssl-fplist to verify the server certificate by its fingerprint

JSON

JSON_TABLE now allows retrieval of the key when iterating on JSON objects (MDEV-30145, MariaDB 11.2)
New functions JSON_OBJECT_FILTER_KEYS, JSON_OBJECT_TO_ARRAY and JSON_ARRAY_INTERSECT to check for JSON intersection (MDEV-26182, MariaDB 11.2)
JSON_KEY_VALUE extracts key/value pairs from a JSON object (MDEV-30145, MariaDB 11.2)
JSON_SCHEMA_VALID function for validating a JSON schema (MDEV-27128, MariaDB 11.1)

Data Types

It is now possible to create partitions on tables that contain GEOMETRY types (MDEV-19177, MariaDB 11.3)
INET4 data types can now be cast into INET6 types (MDEV-31626, MariaDB 11.3)
This means INET4 values can be compared with INET6 values and can be inserted into INET6 columns; the server can automatically convert INET4 value into INET6 as needed (MariaDB 11.3)

Backup and Restore

New mariadb-dump option, -j, --parallel= for increased parallelism, specifies the number of dump table jobs executed in parallel (only for use with the --tab option). Also added to mariadb-import, with --use-threads as a synonym. (MDEV-32216)
mariadb-backup --innobackupex mode has been deprecated (MDEV-31505, MariaDB 11.3)
Rename mariadb-backup’s xtrabackup_* files to mariadb_backup_* (MDEV-18931, MariaDB 11.1)

Application-Time Periods

Add views for periods in information_schema (MDEV-22597), in particular
New view INFORMATION_SCHEMA.PERIODS
New view INFORMATION_SCHEMA.KEY_PERIOD_USAGE
New columns IS_SYSTEM_TIME_PERIOD_START and IS_SYSTEM_TIME_PERIOD_END in the INFORMATION_SCHEMA.COLUMNS view

Sys Schema

New view sys.privileges_by_table_by_level shows granted privileges broken down by table on which they allow access and level on which they were granted. For example, if a user x has SELECT privilege granted ON db.*, this view will list all tables in the db schema with the user x having SELECT privilege on them. This is different from INFORMATION_SCHEMA.TABLE_PRIVILEGES, which only lists privileges granted on the table level (MDEV-24486)

Partitioning

ALTER TABLE … EXCHANGE PARTITION and ALTER TABLE … CONVERT TABLE … TO now support the WITH VALIDATION and WITHOUT VALIDATION clauses. If neither is specified, the default behavior is WITH VALIDATION (MDEV-22164)

Collations

From MariaDB 11.4.5:

44 new collations added. These are aliases for MySQL collations to make it easier to replicate from MySQL to MariaDB (MDEV-35256)
- The Information Schema Collations table includes a new column, COMMENT which contains information about which collation the alias refers to.

Connection Redirection

Added a redirect mechanism using the redirect_url system variable (MDEV-15935, MariaDB 11.3)
At the moment client-side support is missing

Privileges

Add a new database-level privilege, SHOW CREATE ROUTINE that allows one to see the routine definition even if the user isn't the routine owner (MDEV-29167, MariaDB 11.3)

Spider

The preferred way to specify Spider parameters is to use the dedicated Spider table options (implemented in MariaDB 11.3). Abusing the table COMMENT clause is now deprecated (MDEV-28861)
The Spider storage engine now supports table options instead of having to encode them in COMMENT/CONNECTION strings. When any table option is specified, Spider will ignore COMMENT/CONNECTION strings at the same table/partition/subpartition. A new variable spider_ignore_comments is introduced to ignore them globally at all levels (table/partition/subpartition). Another variable, spider_suppress_comment_ignored_warning, is introduced to suppress warnings when Spider ignores COMMENT/CONNECTION strings. (MDEV-28856, MariaDB 11.3)

Miscellaneous Functions

CONV() function now supports conversion up to base 62 (MDEV-30879)
Values generated by the Key Derivation Function KDF are resistant against password hashing generators, so are good for strongly hashed passwords (MDEV-31474)
Given a time in picoseconds, the new function FORMAT_PICO_TIME returns a human-readable time value and unit indicator (MDEV-19629, MariaDB 11.0)

Date and Time

DATE_FORMAT function can now print the current time zone abbreviation and current time zone offset from UTC with %Z and %z format specifiers. (MDEV-31684, MariaDB 11.3)

Processlist

Added a SENT_ROWS column to the Information Schema PROCESSLIST table, as well as extended the display size for the columns in processlist to ensure that most results will fit in display (MDEV-3953, MariaDB 11.3)