Changes and Improvements in MariaDB 11.4

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 features from 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)

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)
• mariabackup --innobackupex mode has been deprecated (MDEV-31505, MariaDB 11.3)
• Rename Mariabackup’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)

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)

Miscellaneous

• Added support for packages (CREATE PACKAGE) outside of ORACLE sql_mode (MDEV-32101)
• Setting a non-default old_mode value will now always issue a deprecation warning (MDEV-31811, MariaDB 11.3)
• ALTER TABLE IMPORT enhancement (MDEV-26137, MariaDB 11.2)
• Temporary tables are now displayed in the Information Schema TABLES Table, SHOW TABLES and SHOW TABLE STATUS (MDEV-12459, MariaDB 11.2)
• Stored programs: validation of stored program statements (MDEV-5816, MariaDB 11.2)
• Extend AES_ENCRYPT() and AES_DECRYPT() to support an initialization vector and algorithm (MDEV-9069, MariaDB 11.2)
• The transaction_isolation option is now a system variable, and the tx_isolation system variable is deprecated (MDEV-21921, MariaDB 11.1)

Variables

• For a list of all new variables, see System Variables Added in MariaDB 11.4.

Removed

The following deprecated features and system variables have been removed (MDEV-32104, MariaDB 11.3):

• sr_YU locale (deprecated since MariaDB 10.0.11)
• "engine_condition_pushdown" in optimizer_switch (deprecated since MariaDB 10.1.1)
• date_format, datetime_format, time_format, max_tmp_tables (deprecated since MariaDB 10.1.2)
• wsrep_causal_reads (deprecated since MariaDB 10.1.3)
• "parser" in mroonga table comment (deprecated since MariaDB 10.2.11)
• old_alter_table variable (MDEV-30905, MariaDB 11.3)
• innodb_defragment and related parameters (MDEV-30545, MariaDB 11.1)
• The InnoDB Change Buffer has been removed (MDEV-29694, MariaDB 11.0)

List of All MariaDB 11.4 Releases