Changes and Improvements in MariaDB 10.5

MariaDB 10.5 is the current major development version. MariaDB 10.4 is currently stable and feature-complete, so new features are going into 10.5.

• Plans for MariaDB 10.5
• 10.5 Features/fixes by vote

Upgrading

• See Upgrading Between Major MariaDB Versions and Upgrading from MariaDB 10.4 to MariaDB 10.5.

Implemented Features

Binaries

• All binaries previously beginning with mysql now begin with mariadb, with symlinks for the corresponding mysql command. (MDEV-21303)

Syntax

• INSERT ... RETURNING (MDEV-10014)
• REPLACE ... RETURNING (MDEV-10014)
• EXCEPT ALL and INTERSECT ALL (MDEV-18844)
• Database comments in CREATE DATABASE and ALTER DATABASE statements (MDEV-307)
• RELEASE_ALL_LOCKS (MDEV-10569)
• ALTER TABLE ... RENAME INDEX / KEY (MDEV-7318)
• ALTER TABLE ... RENAME COLUMN (MDEV-16290)
• Recursive CTE cycle detection using CYCLE clause (MDEV-20632)
• ALTER TABLE and RENAME TABLE now support IF EXISTS.

JSON

• JSON_ARRAYAGG
• JSON_OBJECTAGG

Storage Engines

• The S3 storage engine allows one to archive MariaDB tables in Amazon S3, or any third-party public or private cloud that implements S3 API.
• Partitioned S3 tables are now discoverable. This means that if you create a partitioned S3 table, both the partitioned table and it's partitions can be directly used by another server who has access to the S3 storage.

Privileges

• Split SUPER privilege to smaller privileges (MDEV-21743). New privileges were added so that more fine grained tuning of what each user can do can be applied:
  • BINLOG ADMIN
  • BINLOG REPLAY
  • CONNECTION ADMIN
  • FEDERATED ADMIN
  • READ_ONLY ADMIN
  • REPLICATION MASTER ADMIN
  • REPLICATION SLAVE ADMIN
  • SET USER
• The REPLICATION CLIENT privilege was renamed to BINLOG MONITOR. The old syntax is understood for compatibility (MDEV-21743).
• The SHOW MASTER STATUS statement was renamed to SHOW BINLOG STATUS (MDEV-21743). The old syntax is understood for compatibility.
• A number of statements changed the privileges that they require. The old privileges were historically inappropriately chosen in the upstream. 10.5.2 fixes this problem. Note, these changes are incompatible to previous versions. A number of GRANT commands might be needed after upgrade.
  • SHOW BINLOG EVENTS now requires the BINLOG MONITOR privilege (requred REPLICATION SLAVE prior to 10.5.2).
  • SHOW SLAVE HOSTS now requires the REPLICATION MASTER ADMIN privilege (required REPLICATION SLAVE prior to 10.5.2).
  • SHOW SLAVE STATUS now requires the REPLICATION SLAVE ADMIN or the SUPER privilege (required REPLICATION CLIENT or SUPER prior to 10.5.2).
  • SHOW RELAYLOG EVENTS now requires the REPLICATION SLAVE ADMIN privilege (required REPLICATION SLAVE prior to 10.5.2).
• In order to help the server understand which version a privilege record was written by, the mysql.global_priv.priv field contains a new JSON field, version_id (MDEV-21704)
• SHOW PRIVILEGES now correctly lists the Delete history privilege, rather than displaying it as Delete versioning rows. (MDEV-20382)

Thread Pool

• Information Schema tables (THREADPOOL_GROUPS, THREADPOOL_QUEUES and THREADPOOL_STATS) for internals of generic threadpool (MDEV-19313).

InnoDB

• innodb_adaptive_hash_index now defaults to OFF (MDEV-20487)
• innodb_checksum_algorithm now defaults to full_crc32 (MDEV-19534)
• innodb_checksums has been removed (MDEV-19534)
• innodb_log_checksums has been deprecated (MDEV-19543)
• innodb_locks_unsafe_for_binlog has been removed (MDEV-19544)
• innodb_stats_sample_pages has been removed (MDEV-19551)
• innodb_undo_logs has been deprecated (MDEV-19570)
• innodb_rollback_segments has been removed (MDEV-19570)
• Set innodb_log_files_in_group=1 by default (MDEV-20907)
• Extend SHOW STATUS LIKE 'Innodb_%' (MDEV-18582)
• Clean up INFORMATION_SCHEMA.INNODB_ tables (MDEV-19940)
• Doublewrite buffer is unnecessarily used for newly (re)initialized pages (MDEV-19738)
• Defer change buffer merge until pages are requested (MDEV-19514)
• Remove dummy tablespace for the redo log (MDEV-18115)
• Optimize access to InnoDB page header fields (MDEV-21133)
• Remove multiple InnoDB buffer pool instances (MDEV-15058)
  • Deprecate and ignore innodb_buffer_pool_instances and innodb_page_cleaners
  • Columns that indicated the buffer pool instance from the Information Schema innodb_buffer_page, innodb_buffer_page_lru, innodb_buffer_pool_stats, innodb_cmpmem and innodb_cmpmem_reset tables now return a dummy value of 0.
• Deprecated and ignore innodb_log_optimize_ddl (MDEV-19747)
• Remove buf_page_t::newest_modification (MDEV-21132)
• Replace recv_sys_t::addr_hash with a std::map (MDEV-19586)
• Obsolete internal parser for FK in InnoDB (MDEV-20480)
• InnoDB thread pool for background tasks (MDEV-16264)
• An upgrade will only be possible after a clean shutdown. mariabackup --prepare will not work with backups taken before version 10.5.2.
• Efficient InnoDB redo log record format (MDEV-12353)
• Deprecate and ignore innodb_scrub_log and innodb_scrub_log_speed (MDEV-21870)
• Remove INFORMATION_SCHEMA.INNODB_TABLESPACES_SCRUBBING table and deprecate and ignore innodb-background-scrub-data-uncompressed, innodb-background-scrub-data-compressed, innodb-background-scrub-data-interval and innodb-background-scrub-data-check-interval (MDEV-15528)
• Deprecate and ignore innodb_log_files_in_group (MDEV-14425)
• Do not acquire InnoDB record locks when covering table locks exist (MDEV-14479)
• Issue a message on changing deprecated innodb_log_files_in_group (MDEV-21990)
• Optimize append only files for NVDIMM (MDEV-17084)
• Improve innodb redo log group commit performance (MDEV-21534)
• Punch holes when pages are freed (MDEV-15528)

Performance Schema

• Merge 5.7 P_S transaction instrumentation and tables (MDEV-16435)
• Merge 5.7 P_S memory instrumentation and tables (MDEV-16431)
• Merge 5.7 P_S mdl instrumentation and tables (MDEV-16432)
• Merge 5.7 P_S sxlocks instrumentation and tables (MDEV-16436)
• Merge 5.7 P_S user variables instrumentation and tables (MDEV-16439)
• Merge 5.7 P_S [show] status instrumentation and tables (MDEV-16438)
• Merge 5.7 P_S ps instrumentation and tables (MDEV-16433)
• Merge 5.7 P_S sp instrumentation and tables (MDEV-16434)

Performance improvements

• Speed up binary row logging code
• Range optimizer speedups. Removed double calls to records_in_range() for some cases.
• Costs for using MEMORY tables updated to be more accurate
• Fixed that 'ref' access is preferred over 'range' for the same index.

Galera and Replication

• slave_parallel_mode now defaults to optimistic (MDEV-18648).
• Make REPLICA a synonym for SLAVE in SQL statements (MDEV-20601)
• Galera GTID support (commit)
• Add new mode to wsrep_OSU_method in which Galera checks storage engine of the effected table (MDEV-20051)
• Galera: Replicate MariaDB GTID to other nodes in the cluster (MDEV-20720)
• ENFORCE option for slave_run_triggers_for_rbr (MDEV-21833)

Binary Log

• Extended binlog metadata (MDEV-20477)

Query Optimizer

• ANALYZE for statements is improved, now it also shows the time spent checking the WHERE clause and doing other auxiliary operations (MDEV-20854)
• Inferred IS NOT NULL predicates can be used by the range optimizer (MDEV-15777)
• Allow packed values of non-sorted fields in the sort buffer (MDEV-21263)
• Allow packed sort keys in sort buffer (MDEV-21580)

General

• The Information Schema SYSTEM_VARIABLES Table has a new column showing from which config file a variable derives its value (MDEV-12684)
• Switch Perl DBI scripts from DBD::mysql to DBD::MariaDB driver (MDEV-19755)
• The Aria max key length is now 2000 bytes, compared to 1000 bytes in MyISAM.
• Migrate to PCRE2 (MDEV-14024)

PCRE (Perl Compatible Regular Expressions)

• Migrate to PCRE2 (MDEV-14024), a newer version of the pcre library.

Compatibility

• Port show_old_temporals from MySQL 5.6 (MDEV-19906)

Variables

• For a list of all new variables, see System Variables Added in MariaDB 10.5 and Status Variables Added in MariaDB 10.5.
• The Information Schema SYSTEM_VARIABLES Table has a new column showing from which config file a variable derives its value (MDEV-12684).
• Port show_old_temporals from MySQL 5.6 (MDEV-19906)
• Numerous deprecated variables removed (MDEV-18650)
  • multi_range_count
  • thread_concurrency
  • timed_mutexes

List of All MariaDB 10.5 Releases