Changes and Improvements in MariaDB 10.5

MariaDB 10.5 is a previous major stable version The first stable release was in June 2020, and it will be maintained until June 2025.

Upgrading

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

Implemented Features

See the Differences in MariaDB Enterprise Server 10.5 page for items that are different between MariaDB Community Server 10.5 and MariaDB Enterprise Server 10.5.

ColumnStore

• This release of MariaDB Server includes the MariaDB ColumnStore storage engine. Note, that plugins have independent maturity levels and MariaDB ColumnStore in 10.5.4 has Beta maturity.

Binaries Named mariadb (mysql Symlinked)

• All binaries previously beginning with mysql now begin with mariadb, with symlinks for the corresponding mysql command. (MDEV-21303)
• When starting the MariaDB server via the systemd service it will be started using the mariadbd binary name, so this will now show up in the system process list instead of mysqld
• Same for the mariadbd-safe wrapper script. Even when called via the mysqld_safe symlink, it will start the actual server process as mariadbd, not mysqld now. This also affects startup via system service init scripts on platforms that don't yet have switched to SystemD

INET 6 Data Type

• New INET6 data type for storing IPv6 addresses (MDEV-274)

Amazon S3

• 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 ( MDEV-22606)
• Both S3 tables and partitioned S3 tables are discoverable. This means that if you create a partitioned S3 table, both the partitioned table and its partitions can be directly used by another server that has access to the S3 storage. (MDEV-22088)

Privileges Made More Granular

• 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 (required 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)

InnoDB: Performance Improvements etc.

• 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)
  • 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.
• 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)
• Improve InnoDB redo log group commit performance (MDEV-21534)
• 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)
• Avoid writing freed InnoDB pages (MDEV-15528)

InnoDB New Defaults for Variables

• innodb_adaptive_hash_index now defaults to OFF (MDEV-20487)
• innodb_checksum_algorithm now defaults to full_crc32 (MDEV-19534)

InnoDB Removed or Deprecated Variables

• innodb_buffer_pool_instances
• innodb_checksums (MDEV-19534)
• innodb_locks_unsafe_for_binlog (MDEV-19544)
• innodb_log_checksums (MDEV-19543)
• innodb_log_files_in_group (MDEV-14425 & MDEV-20907)
• innodb_log_optimize_ddl (MDEV-19747)
• innodb_rollback_segments (MDEV-19570)
• 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
• innodb-background-scrub-data-check-interval (MDEV-15528)
• innodb_stats_sample_pages (MDEV-19551)
• innodb_undo_logs (MDEV-19570)
• innodb_thread_concurrency
• innodb_commit_concurrency
• innodb_replication_delay
• innodb_concurrency_tickets
• innodb_thread_sleep_delay
• innodb_adaptive_max_sleep_delay (MDEV-23379)

Performance Schema Updates to Match MySQL 5.7 Instrumentation and Tables

• Memory (MDEV-16431)
• Meta data locking (MDL) (MDEV-16432)
• Prepared statements (ps) (MDEV-16433)
• [show] status instrumentation and tables (MDEV-16438)
• Stored procedures (MDEV-16434)
• Sxlocks (MDEV-16436)
• Transactions (MDEV-16435)
• User variables (MDEV-16439)

Galera: Full GTID Support

• Add full GTID support to Galera cluster (commit). With this feature all nodes in a cluster will have the same GTID for replicated events originating from the cluster. Also added a new variable, wsrep_gtid_seq_no, to manually update the WSREP GTID sequence number in the cluster (similar to how the gtid_seq_no variable is used for non-WSREP transactions).
• Add new mode to wsrep_OSU_method in which Galera checks storage engine of the affected table (MDEV-20051)
• Galera: Replicate MariaDB GTID to other nodes in the cluster (MDEV-20720)

Binary Log and Replication: More Metadata

• slave_parallel_mode now defaults to optimistic (MDEV-18648).
• Make REPLICA a synonym for SLAVE in SQL statements (MDEV-20601)
• ENFORCE option for slave_run_triggers_for_rbr (MDEV-21833)
• Extended binlog metadata (MDEV-20477) to include new fields. This was done to solve replication issues when the Master and Slave table had different definitions for a column which could lead to data loss (MDEV-19708). It also enables us to do better replication with pluggable data types in the future.
  • The new metadata fields are:
    • Signedness of Numeric Columns
    • Character Set of Character Columns and Binary Columns
    • Column Name
    • String Value of SET Columns
    • String Value of ENUM Columns
    • Primary Key
    • Character Set of SET Columns and ENUM Columns
    • Geometry Type
  • Also added a new global variable, binlog_row_metadata to control the amount of metadata logged. Possible values are:
    • FULL - all metadata is logged
    • MINIMAL - only metadata required by a worker is logged
    • NO_LOG - No metadata is logged (default)
• Binary log DDL entries can now be marked that they should be ignored if the target table doesn't exist (implicit IF EXISTS).
• mariadb-binlog output is extended to show all replication flags. Example of output: SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0/*!*/.
• SHOW BINLOG EVENTS and SHOW RELAYLOG EVENTS are extended to show replication flags.

Syntax

• INSERT ... RETURNING (MDEV-10014) - returns SELECT of inserted rows (analogous to DELETE ... RETURNING)
• REPLACE ... RETURNING (MDEV-10014)
• EXCEPT ALL and INTERSECT ALL (MDEV-18844)
• Application period tables: WITHOUT OVERLAPS (MDEV-16978)
• Setup default partitions for system versioning (MDEV-19903)
• Database comments in CREATE DATABASE and ALTER DATABASE statements (MDEV-307)
• ALTER TABLE ... RENAME INDEX / KEY (MDEV-7318)
• ALTER TABLE ... RENAME COLUMN (MDEV-16290)
• ALTER TABLE and RENAME TABLE now support IF EXISTS.
• Add VISIBLE attribute for indexes in CREATE TABLE (MDEV-22199)
• Recursive CTE cycle detection using CYCLE clause (MDEV-20632)
• RELEASE_ALL_LOCKS hold by GET_LOCK() (MDEV-10569)
• Fix REFERENCES constraint in column definition (MDEV-20729)

JSON

• Added JSON_ARRAYAGG. This returns a JSON array containing an element for each value in a given set of JSON or SQL values. It acts on a column or an expression that evaluates to a single value.
• Added JSON_OBJECTAGG. This returns a JSON object containing key-value pairs. It takes two expressions that evaluate to a single value, or two column names, as arguments, the first used as a key, and the second as a value.

Thread Pool

• Information Schema tables (THREAD_POOL_GROUPS, THREAD_POOL_QUEUES, THREAD_POOL_STATS and THREAD_POOL_WAITS) for internals of generic thread_pool (MDEV-19313).

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.
• Improve connect speed (up to 25%). (MDEV-19515)

Query Optimizer

• Improve Protocol performance for numeric data by avoiding unnecessary character string conversions (MDEV-23162, MDEV-23478)
• 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 sort keys and values of non-sorted fields in the sort buffer (MDEV-21263 & MDEV-21580)
  • Makes filesort temporary files much smaller when VARCHAR, CHAR or BLOBs are used!

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.
• DROP TABLE now reliably deletes table remnants inside a storage engine even if the .frm file is missing (MDEV-11412)
• Accelerated crc32() function for AMD64, ARMv8, POWER 8 (MDEV-22669)
• Binary tarball size has been reduced (MDEV-21943)

PCRE (Perl Compatible Regular Expressions)

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

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). If set, old temporal data types (created with a pre-10.0 version of MariaDB) are displayed with a /* mariadb-5.3 */ comment.
• Numerous deprecated variables removed (MDEV-18650)
  • multi_range_count
  • thread_concurrency
  • timed_mutexes

Security Vulnerabilities Fixed in MariaDB 10.5

Resources

• 10.5 and beyond (video presentation by Sergei Golubchik)

List of All MariaDB 10.5 Releases