wcked
07-13-2024, 01:43 AM
New Features & Improvements
Atomic DDL
CREATE TABLE (https://mariadb.com/kb/en/create-table/), ALTER TABLE (https://mariadb.com/kb/en/alter-table/), RENAME TABLE (https://mariadb.com/kb/en/rename-table/), DROP TABLE (https://mariadb.com/kb/en/drop-table/), DROP DATABASE (https://mariadb.com/kb/en/drop-database/) and related DDL statements are now atomic (https://mariadb.com/kb/en/atomic-ddl/). Either the statement is fully completed, or everything is reverted to it's original state. Note that when deleting multiple tables with DROP TABLE, only each individual drop is atomic, not the full list of tables). (MDEV-23842 (https://jira.mariadb.org/browse/MDEV-23842)).
SQL Syntax
Implement SQL-standard SELECT ... OFFSET ... FETCH (https://mariadb.com/kb/en/select-offset-fetch/) (MDEV-23908 (https://jira.mariadb.org/browse/MDEV-23908))
Add SELECT ... SKIP LOCKED (https://mariadb.com/kb/en/select/#skip-locked) syntax (InnoDB only) (MDEV-13115 (https://jira.mariadb.org/browse/MDEV-13115))
Indexes can be ignored (https://mariadb.com/kb/en/ignored-indexes/) (MDEV-7317 (https://jira.mariadb.org/browse/MDEV-7317), MDEV-25075 (https://jira.mariadb.org/browse/MDEV-25075))
JSON_TABLE (https://mariadb.com/kb/en/json_table/), used to extract JSON data based on a JSON path expression and to return it as a relational table (MDEV-17399 (https://jira.mariadb.org/browse/MDEV-17399))
Oracle Compatibility
Anonymous subqueries in a FROM clause (https://mariadb.com/kb/en/subqueries-in-a-from-clause/) (no AS clause) are permitted in ORACLE mode (https://mariadb.com/kb/en/sql_modeoracle-from-mariadb-103/) (MDEV-19162 (https://jira.mariadb.org/browse/MDEV-19162))
ADD_MONTHS() (https://mariadb.com/kb/en/add_months/) added (MDEV-20025 (https://jira.mariadb.org/browse/MDEV-20025))
TO_CHAR() (https://mariadb.com/kb/en/to_char/) added (MDEV-20017 (https://jira.mariadb.org/browse/MDEV-20017))
SYS_GUID() (https://mariadb.com/kb/en/sys_guid/) added (MDEV-24285 (https://jira.mariadb.org/browse/MDEV-24285))
MINUS is mapped to EXCEPT (https://mariadb.com/kb/en/except/) in UNION (MDEV-20021 (https://jira.mariadb.org/browse/MDEV-20021))
ROWNUM (https://mariadb.com/kb/en/rownum/) function returns the current number of accepted rows in the current context (MDEV-24089 (https://jira.mariadb.org/browse/MDEV-24089))
InnoDB
Optimization to speed up inserts into an empty table (MDEV-515 (https://jira.mariadb.org/browse/MDEV-515))
We intended to deprecate and eventually remove the InnoDB's COMPRESSED row format (https://mariadb.com/kb/en/innodb-compressed-row-format/). The first step was to make the tables read-only by default (https://mariadb.com/kb/en/innodb-compressed-row-format/#read-only), but this plan was abandoned from MariaDB 10.6.6 (https://mariadb.com/kb/en/mariadb-1066-release-notes/) (MDEV-23497 (https://jira.mariadb.org/browse/MDEV-23497)) (MDEV-27736 (https://jira.mariadb.org/browse/MDEV-27736))
Information Schema SYS_TABLESPACES (https://mariadb.com/kb/en/information-schema-innodb_sys_tablespaces-table/) now directly reflects the filesystem, and SYS_DATAFILES (https://mariadb.com/kb/en/information-schema-innodb_sys_datafiles-table/) has been removed (MDEV-22343 (https://jira.mariadb.org/browse/MDEV-22343))
Defer writes to the InnoDB temporary tablespace (MDEV-12227 (https://jira.mariadb.org/browse/MDEV-12227))
The old MariaDB 5.5 (https://mariadb.com/kb/en/what-is-mariadb-55/)-compatible innodb checksum is no longer supported, only crc32. Removed the *innodb and *none options from innodb_checksum_algorithm (https://mariadb.com/kb/en/innodb-system-variables/#innodb_checksum_algorithm), and the --strict-check/-C and --write/-w options from innochecksum (https://mariadb.com/kb/en/innochecksum/) (MDEV-25105 (https://jira.mariadb.org/browse/MDEV-25105))
Replication, Galera and Binlog
Increase master_host (https://mariadb.com/kb/en/change-master-to/#master_host) limit to 255, user to 128 (MDEV-24312 (https://jira.mariadb.org/browse/MDEV-24312))
The wsrep_mode (https://mariadb.com/kb/en/galera-cluster-system-variables/#wsrep_mode) system variable, for turning on WSREP features which are not part of default behavior (including the experimental Aria replication) (MDEV-20008 (https://jira.mariadb.org/browse/MDEV-20008), MDEV-20715 (https://jira.mariadb.org/browse/MDEV-20715), MDEV-24946 (https://jira.mariadb.org/browse/MDEV-24946))
The delay between binary log purges can now be specified with much greater precision. The system variable binlog_expire_logs_seconds (https://mariadb.com/kb/en/replication-and-binary-log-system-variables/#binlog_expire_logs_seconds) is introduced as a form of alias for expire_logs_days (https://mariadb.com/kb/en/replication-and-binary-log-system-variables/#expire_logs_days), which now accepts a precision of 1/1000000 days (MDEV-19371 (https://jira.mariadb.org/browse/MDEV-19371))
Allow transition from unencrypted to TLS Galera (https://mariadb.com/kb/en/galera/) cluster communication without cluster downtime (MDEV-22131 (https://jira.mariadb.org/browse/MDEV-22131))
Sys Schema
Bundle sys-schema (https://mariadb.com/kb/en/sys-schema/), a collection of views, functions and procedures to help administrators get insight into database usage. (MDEV-9077 (https://jira.mariadb.org/browse/MDEV-9077))
Performance Schema
Merged replication instrumentation and tables (MDEV-16437 (https://jira.mariadb.org/browse/MDEV-16437), MDEV-20220 (https://jira.mariadb.org/browse/MDEV-20220))
Information Schema
The views INFORMATION_SCHEMA.KEYWORDS (https://mariadb.com/kb/en/information-schema-keywords-table/) and INFORMATION_SCHEMA.SQL_FUNCTIONS (https://mariadb.com/kb/en/information-schema-sql_functions-table/) have been added to the information schema (MDEV-25129 (https://jira.mariadb.org/browse/MDEV-25129))
Storage Engines
TokuDB (https://mariadb.com/kb/en/tokudb/) has been removed (MDEV-19780 (https://jira.mariadb.org/browse/MDEV-19780))
CassandraSE (https://mariadb.com/kb/en/cassandra/) has been removed (MDEV-23024 (https://jira.mariadb.org/browse/MDEV-23024))
Character Sets
The utf8 character set (https://mariadb.com/kb/en/character-sets/) (and related collations) is now by default an alias for utf8mb3 rather than the other way around. It can be set to imply utf8mb4 by changing the value of the old_mode (https://mariadb.com/kb/en/server-system-variables/#old_mode) system variable (MDEV-8334 (https://jira.mariadb.org/browse/MDEV-8334))
General
Bundle sys schema (MDEV-9077 (https://jira.mariadb.org/browse/MDEV-9077))
Do not resend unchanged resultset metadata for prepared statements (MDEV-19237 (https://jira.mariadb.org/browse/MDEV-19237))
--bind-address=hostname (https://mariadb.com/kb/en/server-system-variables/#bind_address) now listens on both IPv6 and IPv4 addresses (MDEV-6536 (https://jira.mariadb.org/browse/MDEV-6536))
Support systemd socket activation (MDEV-5536 (https://jira.mariadb.org/browse/MDEV-5536))
For the GSSAPI plugin (https://mariadb.com/kb/en/authentication-plugin-gssapi/), support AD or local group name, and SIDs on Windows (MDEV-23959 (https://jira.mariadb.org/browse/MDEV-23959))
Check for $MARIADB_HOME/my.cnf (MDEV-21365 (https://jira.mariadb.org/browse/MDEV-21365))
Variables
For a list of all new variables, see System Variables Added in MariaDB 10.6 (https://mariadb.com/kb/en/system-variables-added-in-mariadb-106/) and Status Variables Added in MariaDB 10.6 (https://mariadb.com/kb/en/status-variables-added-in-mariadb-106/).
max_recursive_iterations (https://mariadb.com/kb/en/server-system-variables/#max_recursive_iterations) has been reduced to 1000 (MDEV-17239 (https://jira.mariadb.org/browse/MDEV-17239))
InnoDB Variables
The following deprecated variables have been removed (MDEV-23397 (https://jira.mariadb.org/browse/MDEV-23397)):
innodb_adaptive_max_sleep_delay (https://mariadb.com/kb/en/innodb-system-variables/#innodb_adaptive_max_sleep_delay)
innodb_background_scrub_data_check_interval (https://mariadb.com/kb/en/innodb-system-variables/#innodb_background_scrub_data_check_interval)
innodb_background_scrub_data_compressed (https://mariadb.com/kb/en/innodb-system-variables/#innodb_background_scrub_data_compressed)
innodb_background_scrub_data_interval (https://mariadb.com/kb/en/innodb-system-variables/#innodb_background_scrub_data_interval)
innodb_background_scrub_data_uncompressed (https://mariadb.com/kb/en/innodb-system-variables/#innodb_background_scrub_data_uncompressed)
innodb_buffer_pool_instances (https://mariadb.com/kb/en/innodb-system-variables/#innodb_buffer_pool_instances)
innodb_commit_concurrency (https://mariadb.com/kb/en/innodb-system-variables/#innodb_commit_concurrency)
innodb_concurrency_tickets (https://mariadb.com/kb/en/innodb-system-variables/#innodb_concurrency_tickets)
innodb_file_format (https://mariadb.com/kb/en/innodb-system-variables/#innodb_file_format)
innodb_large_prefix (https://mariadb.com/kb/en/innodb-system-variables/#innodb_large_prefix)
innodb_lock_schedule_algorithm (https://mariadb.com/kb/en/innodb-system-variables/#innodb_lock_schedule_algorithm)
innodb_log_checksums (https://mariadb.com/kb/en/innodb-system-variables/#innodb_log_checksums)
innodb_log_compressed_pages (https://mariadb.com/kb/en/innodb-system-variables/#innodb_log_compressed_pages)
innodb_log_files_in_group (https://mariadb.com/kb/en/innodb-system-variables/#innodb_log_files_in_group)
innodb_log_optimize_ddl (https://mariadb.com/kb/en/innodb-system-variables/#innodb_log_optimize_ddl)
innodb_page_cleaners (https://mariadb.com/kb/en/innodb-system-variables/#innodb_page_cleaners)
innodb_replication_delay (https://mariadb.com/kb/en/innodb-system-variables/#innodb_replication_delay)
innodb_scrub_log (https://mariadb.com/kb/en/innodb-system-variables/#innodb_scrub_log)
innodb_scrub_log_speed (https://mariadb.com/kb/en/innodb-system-variables/#innodb_scrub_log_speed)
innodb_sync_array_size (https://mariadb.com/kb/en/innodb-system-variables/#innodb_sync_array_size)
innodb_thread_concurrency (https://mariadb.com/kb/en/innodb-system-variables/#innodb_thread_concurrency)
innodb_thread_sleep_delay (https://mariadb.com/kb/en/innodb-system-variables/#innodb_thread_sleep_delay)
innodb_undo_log (https://mariadb.com/kb/en/innodb-system-variables/#innodb_undo_logs)s
Atomic DDL
CREATE TABLE (https://mariadb.com/kb/en/create-table/), ALTER TABLE (https://mariadb.com/kb/en/alter-table/), RENAME TABLE (https://mariadb.com/kb/en/rename-table/), DROP TABLE (https://mariadb.com/kb/en/drop-table/), DROP DATABASE (https://mariadb.com/kb/en/drop-database/) and related DDL statements are now atomic (https://mariadb.com/kb/en/atomic-ddl/). Either the statement is fully completed, or everything is reverted to it's original state. Note that when deleting multiple tables with DROP TABLE, only each individual drop is atomic, not the full list of tables). (MDEV-23842 (https://jira.mariadb.org/browse/MDEV-23842)).
SQL Syntax
Implement SQL-standard SELECT ... OFFSET ... FETCH (https://mariadb.com/kb/en/select-offset-fetch/) (MDEV-23908 (https://jira.mariadb.org/browse/MDEV-23908))
Add SELECT ... SKIP LOCKED (https://mariadb.com/kb/en/select/#skip-locked) syntax (InnoDB only) (MDEV-13115 (https://jira.mariadb.org/browse/MDEV-13115))
Indexes can be ignored (https://mariadb.com/kb/en/ignored-indexes/) (MDEV-7317 (https://jira.mariadb.org/browse/MDEV-7317), MDEV-25075 (https://jira.mariadb.org/browse/MDEV-25075))
JSON_TABLE (https://mariadb.com/kb/en/json_table/), used to extract JSON data based on a JSON path expression and to return it as a relational table (MDEV-17399 (https://jira.mariadb.org/browse/MDEV-17399))
Oracle Compatibility
Anonymous subqueries in a FROM clause (https://mariadb.com/kb/en/subqueries-in-a-from-clause/) (no AS clause) are permitted in ORACLE mode (https://mariadb.com/kb/en/sql_modeoracle-from-mariadb-103/) (MDEV-19162 (https://jira.mariadb.org/browse/MDEV-19162))
ADD_MONTHS() (https://mariadb.com/kb/en/add_months/) added (MDEV-20025 (https://jira.mariadb.org/browse/MDEV-20025))
TO_CHAR() (https://mariadb.com/kb/en/to_char/) added (MDEV-20017 (https://jira.mariadb.org/browse/MDEV-20017))
SYS_GUID() (https://mariadb.com/kb/en/sys_guid/) added (MDEV-24285 (https://jira.mariadb.org/browse/MDEV-24285))
MINUS is mapped to EXCEPT (https://mariadb.com/kb/en/except/) in UNION (MDEV-20021 (https://jira.mariadb.org/browse/MDEV-20021))
ROWNUM (https://mariadb.com/kb/en/rownum/) function returns the current number of accepted rows in the current context (MDEV-24089 (https://jira.mariadb.org/browse/MDEV-24089))
InnoDB
Optimization to speed up inserts into an empty table (MDEV-515 (https://jira.mariadb.org/browse/MDEV-515))
We intended to deprecate and eventually remove the InnoDB's COMPRESSED row format (https://mariadb.com/kb/en/innodb-compressed-row-format/). The first step was to make the tables read-only by default (https://mariadb.com/kb/en/innodb-compressed-row-format/#read-only), but this plan was abandoned from MariaDB 10.6.6 (https://mariadb.com/kb/en/mariadb-1066-release-notes/) (MDEV-23497 (https://jira.mariadb.org/browse/MDEV-23497)) (MDEV-27736 (https://jira.mariadb.org/browse/MDEV-27736))
Information Schema SYS_TABLESPACES (https://mariadb.com/kb/en/information-schema-innodb_sys_tablespaces-table/) now directly reflects the filesystem, and SYS_DATAFILES (https://mariadb.com/kb/en/information-schema-innodb_sys_datafiles-table/) has been removed (MDEV-22343 (https://jira.mariadb.org/browse/MDEV-22343))
Defer writes to the InnoDB temporary tablespace (MDEV-12227 (https://jira.mariadb.org/browse/MDEV-12227))
The old MariaDB 5.5 (https://mariadb.com/kb/en/what-is-mariadb-55/)-compatible innodb checksum is no longer supported, only crc32. Removed the *innodb and *none options from innodb_checksum_algorithm (https://mariadb.com/kb/en/innodb-system-variables/#innodb_checksum_algorithm), and the --strict-check/-C and --write/-w options from innochecksum (https://mariadb.com/kb/en/innochecksum/) (MDEV-25105 (https://jira.mariadb.org/browse/MDEV-25105))
Replication, Galera and Binlog
Increase master_host (https://mariadb.com/kb/en/change-master-to/#master_host) limit to 255, user to 128 (MDEV-24312 (https://jira.mariadb.org/browse/MDEV-24312))
The wsrep_mode (https://mariadb.com/kb/en/galera-cluster-system-variables/#wsrep_mode) system variable, for turning on WSREP features which are not part of default behavior (including the experimental Aria replication) (MDEV-20008 (https://jira.mariadb.org/browse/MDEV-20008), MDEV-20715 (https://jira.mariadb.org/browse/MDEV-20715), MDEV-24946 (https://jira.mariadb.org/browse/MDEV-24946))
The delay between binary log purges can now be specified with much greater precision. The system variable binlog_expire_logs_seconds (https://mariadb.com/kb/en/replication-and-binary-log-system-variables/#binlog_expire_logs_seconds) is introduced as a form of alias for expire_logs_days (https://mariadb.com/kb/en/replication-and-binary-log-system-variables/#expire_logs_days), which now accepts a precision of 1/1000000 days (MDEV-19371 (https://jira.mariadb.org/browse/MDEV-19371))
Allow transition from unencrypted to TLS Galera (https://mariadb.com/kb/en/galera/) cluster communication without cluster downtime (MDEV-22131 (https://jira.mariadb.org/browse/MDEV-22131))
Sys Schema
Bundle sys-schema (https://mariadb.com/kb/en/sys-schema/), a collection of views, functions and procedures to help administrators get insight into database usage. (MDEV-9077 (https://jira.mariadb.org/browse/MDEV-9077))
Performance Schema
Merged replication instrumentation and tables (MDEV-16437 (https://jira.mariadb.org/browse/MDEV-16437), MDEV-20220 (https://jira.mariadb.org/browse/MDEV-20220))
Information Schema
The views INFORMATION_SCHEMA.KEYWORDS (https://mariadb.com/kb/en/information-schema-keywords-table/) and INFORMATION_SCHEMA.SQL_FUNCTIONS (https://mariadb.com/kb/en/information-schema-sql_functions-table/) have been added to the information schema (MDEV-25129 (https://jira.mariadb.org/browse/MDEV-25129))
Storage Engines
TokuDB (https://mariadb.com/kb/en/tokudb/) has been removed (MDEV-19780 (https://jira.mariadb.org/browse/MDEV-19780))
CassandraSE (https://mariadb.com/kb/en/cassandra/) has been removed (MDEV-23024 (https://jira.mariadb.org/browse/MDEV-23024))
Character Sets
The utf8 character set (https://mariadb.com/kb/en/character-sets/) (and related collations) is now by default an alias for utf8mb3 rather than the other way around. It can be set to imply utf8mb4 by changing the value of the old_mode (https://mariadb.com/kb/en/server-system-variables/#old_mode) system variable (MDEV-8334 (https://jira.mariadb.org/browse/MDEV-8334))
General
Bundle sys schema (MDEV-9077 (https://jira.mariadb.org/browse/MDEV-9077))
Do not resend unchanged resultset metadata for prepared statements (MDEV-19237 (https://jira.mariadb.org/browse/MDEV-19237))
--bind-address=hostname (https://mariadb.com/kb/en/server-system-variables/#bind_address) now listens on both IPv6 and IPv4 addresses (MDEV-6536 (https://jira.mariadb.org/browse/MDEV-6536))
Support systemd socket activation (MDEV-5536 (https://jira.mariadb.org/browse/MDEV-5536))
For the GSSAPI plugin (https://mariadb.com/kb/en/authentication-plugin-gssapi/), support AD or local group name, and SIDs on Windows (MDEV-23959 (https://jira.mariadb.org/browse/MDEV-23959))
Check for $MARIADB_HOME/my.cnf (MDEV-21365 (https://jira.mariadb.org/browse/MDEV-21365))
Variables
For a list of all new variables, see System Variables Added in MariaDB 10.6 (https://mariadb.com/kb/en/system-variables-added-in-mariadb-106/) and Status Variables Added in MariaDB 10.6 (https://mariadb.com/kb/en/status-variables-added-in-mariadb-106/).
max_recursive_iterations (https://mariadb.com/kb/en/server-system-variables/#max_recursive_iterations) has been reduced to 1000 (MDEV-17239 (https://jira.mariadb.org/browse/MDEV-17239))
InnoDB Variables
The following deprecated variables have been removed (MDEV-23397 (https://jira.mariadb.org/browse/MDEV-23397)):
innodb_adaptive_max_sleep_delay (https://mariadb.com/kb/en/innodb-system-variables/#innodb_adaptive_max_sleep_delay)
innodb_background_scrub_data_check_interval (https://mariadb.com/kb/en/innodb-system-variables/#innodb_background_scrub_data_check_interval)
innodb_background_scrub_data_compressed (https://mariadb.com/kb/en/innodb-system-variables/#innodb_background_scrub_data_compressed)
innodb_background_scrub_data_interval (https://mariadb.com/kb/en/innodb-system-variables/#innodb_background_scrub_data_interval)
innodb_background_scrub_data_uncompressed (https://mariadb.com/kb/en/innodb-system-variables/#innodb_background_scrub_data_uncompressed)
innodb_buffer_pool_instances (https://mariadb.com/kb/en/innodb-system-variables/#innodb_buffer_pool_instances)
innodb_commit_concurrency (https://mariadb.com/kb/en/innodb-system-variables/#innodb_commit_concurrency)
innodb_concurrency_tickets (https://mariadb.com/kb/en/innodb-system-variables/#innodb_concurrency_tickets)
innodb_file_format (https://mariadb.com/kb/en/innodb-system-variables/#innodb_file_format)
innodb_large_prefix (https://mariadb.com/kb/en/innodb-system-variables/#innodb_large_prefix)
innodb_lock_schedule_algorithm (https://mariadb.com/kb/en/innodb-system-variables/#innodb_lock_schedule_algorithm)
innodb_log_checksums (https://mariadb.com/kb/en/innodb-system-variables/#innodb_log_checksums)
innodb_log_compressed_pages (https://mariadb.com/kb/en/innodb-system-variables/#innodb_log_compressed_pages)
innodb_log_files_in_group (https://mariadb.com/kb/en/innodb-system-variables/#innodb_log_files_in_group)
innodb_log_optimize_ddl (https://mariadb.com/kb/en/innodb-system-variables/#innodb_log_optimize_ddl)
innodb_page_cleaners (https://mariadb.com/kb/en/innodb-system-variables/#innodb_page_cleaners)
innodb_replication_delay (https://mariadb.com/kb/en/innodb-system-variables/#innodb_replication_delay)
innodb_scrub_log (https://mariadb.com/kb/en/innodb-system-variables/#innodb_scrub_log)
innodb_scrub_log_speed (https://mariadb.com/kb/en/innodb-system-variables/#innodb_scrub_log_speed)
innodb_sync_array_size (https://mariadb.com/kb/en/innodb-system-variables/#innodb_sync_array_size)
innodb_thread_concurrency (https://mariadb.com/kb/en/innodb-system-variables/#innodb_thread_concurrency)
innodb_thread_sleep_delay (https://mariadb.com/kb/en/innodb-system-variables/#innodb_thread_sleep_delay)
innodb_undo_log (https://mariadb.com/kb/en/innodb-system-variables/#innodb_undo_logs)s