Changes and Improvements in MariaDB 10.5
Do not use non-stable (non-GA) releases in production!
- Implemented Features
- Binaries named mariadb (mysql symlinked)
- Storage Engines: Amazon S3 added
- Privileges made more granular
- Thread Pool
- InnoDB: Performance improvements etc.
- Performance Schema updates to match MySQL 5.7 instrumentation and tables
- Performance improvements
- Galera: Full GTID support
- Binary Log and Replication: More metadata
- Query Optimizer
- PCRE (Perl Compatible Regular Expressions)
- List of All MariaDB 10.5 Releases
Binaries named mariadb (mysql symlinked)
- All binaries previously beginning with
mysqlnow begin with
mariadb, with symlinks for the corresponding
- INSERT ... RETURNING (MDEV-10014) - returns SELECT of inserted rows (analogous to DELETE ... RETURNING)
- REPLACE ... RETURNING (MDEV-10014)
- EXCEPT ALL and INTERSECT ALL (MDEV-18844)
- 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
- 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)
- 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.
Storage Engines: Amazon S3 added
- 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:
REPLICATION MASTER ADMIN
REPLICATION SLAVE ADMIN
REPLICATION CLIENTprivilege was renamed to
BINLOG MONITOR. The old syntax is understood for compatibility (MDEV-21743).
SHOW MASTER STATUSstatement 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 EVENTSnow requires the
BINLOG MONITORprivilege (requred
REPLICATION SLAVEprior to 10.5.2).
SHOW SLAVE HOSTSnow requires the
REPLICATION MASTER ADMINprivilege (required
REPLICATION SLAVEprior to 10.5.2).
SHOW SLAVE STATUSnow requires the
REPLICATION SLAVE ADMINor the
SUPERprior to 10.5.2).
SHOW RELAYLOG EVENTSnow requires the
REPLICATION SLAVE ADMINprivilege (required
REPLICATION SLAVEprior 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,
- SHOW PRIVILEGES now correctly lists the
Delete historyprivilege, rather than displaying it as
Delete versioning rows. (MDEV-20382)
- Information Schema tables (THREADPOOL_GROUPS, THREADPOOL_QUEUES and THREADPOOL_STATS) for internals of generic threadpool (MDEV-19313).
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)
- 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
- innodb_checksum_algorithm now defaults to
InnoDB removed or deprecated variables
- 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 and innodb-background-scrub-data-check-interval (MDEV-15528)
- innodb_stats_sample_pages (MDEV-19551)
- innodb_undo_logs (MDEV-19570)
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)
- 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)
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_novariable 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
- Make REPLICA a synonym for SLAVE in SQL statements (MDEV-20601)
ENFORCEoption 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)
- The new metadata fields are:
- Binary log DDL entries can now be marked that they should be ignored if the target table doesn't exist (implicit
- 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.
- 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 BLOB’s are used!
- 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.
PCRE (Perl Compatible Regular Expressions)
- 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)
List of All MariaDB 10.5 Releases
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.