Changes and Improvements in MariaDB 10.5
You are viewing an old version of this article. View
the current version here.
The most recent release of MariaDB 10.5 is:
MariaDB 10.5.26 Stable (GA) Download Now
Alternate download from mariadb.org
Do not use non-stable (non-GA) releases in production!
Contents
- Upgrading
- Implemented Features
- Performance improvements
- List of All MariaDB 10.5 Releases
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.
Upgrading
Implemented Features
Binaries named mariadb (mysql symlinked)
- All binaries previously beginning with
mysql
now begin withmariadb
, with symlinks for the correspondingmysql
command. (MDEV-21303)
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)
- 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)
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.
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:
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 toBINLOG MONITOR
. The old syntax is understood for compatibility (MDEV-21743). - The
SHOW MASTER STATUS
statement was renamed toSHOW 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 theBINLOG MONITOR
privilege (requredREPLICATION SLAVE
prior to 10.5.2).SHOW SLAVE HOSTS
now requires theREPLICATION MASTER ADMIN
privilege (requiredREPLICATION SLAVE
prior to 10.5.2).SHOW SLAVE STATUS
now requires theREPLICATION SLAVE ADMIN
or theSUPER
privilege (requiredREPLICATION CLIENT
orSUPER
prior to 10.5.2).SHOW RELAYLOG EVENTS
now requires theREPLICATION SLAVE ADMIN
privilege (requiredREPLICATION 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 asDelete versioning rows
. (MDEV-20382)
Thread Pool
- 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)
- 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
.
- 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
- 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 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)
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)
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 thegtid_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 loggedMINIMAL
- only metadata required by a worker is loggedNO_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
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.
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 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!
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.
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)
List of All MariaDB 10.5 Releases
Date | Release | Status | Release Notes | Changelog |
---|---|---|---|---|
12 May 2020 | MariaDB 10.5.3 | RC | Release Notes | Changelog |
26 Mar 2020 | MariaDB 10.5.2 | Beta | Release Notes | Changelog |
14 Feb 2020 | MariaDB 10.5.1 | Beta | Release Notes | Changelog |
3 Dec 2019 | MariaDB 10.5.0 | Alpha | Release Notes | Changelog |
Comments
Comments loading...
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.