Release Notes for MariaDB Enterprise Server 10.6.4-1

Overview

MariaDB Enterprise Server 10.6.4-1 is the first General Availability (GA) release of MariaDB Enterprise Server 10.6. This release contains a variety of new features.

MariaDB Enterprise Server 10.6.4-1 was released on 2021-08-26.

Fixed Security Vulnerabilities

CVE (with cve.org link)

CVSS base score

CVE-2021-46658

5.5

Notable Changes

  • Extensive internal optimizations, including a refactoring of InnoDB storage engine code.

  • Atomic DDL

    • DDL (Data Definition Language) statements are now atomic operations. If the DDL statement is not fully successful, the operation will be rolled back. When the server crashes or is killed in the middle of a DDL statement, the operation is rolled back during crash recovery when the server is restarted. (MDEV-17567)

    • During crash recovery, the server uses the DDL log to determine if an operation needs to be rolled back. When the binary log is enabled, the crash recovery process ensures that the successful operations are written to the binary log and that the unsuccessful operations are not.

    • By default, the DDL log is at ddl-recovery.log in the datadir. When DDL statements are being executed, the DDL log is synchronized to disk very frequently. If you want to configure a custom path for the DDL log, the log-ddl-recovery option can be used.

    • As of this release, the following storage engines fully support atomic DDL:

  • Default InnoDB flush method

  • UTF-8 (utf8) character set alias

    • The utf8 character set has been renamed to utf8mb3, which used to be an alias for the utf8 character set

    • The character set utf8 is now an alias that defaults to utf8mb3 but can be turned into an alias for utf8mb4 with a config change

    • The new default of old_mode=UTF8_IS_UTF8MB3 is what makes utf8mb3 default to utf8, and anything that removes this new value from old_mode changes utf8 to mean utf8mb4 (MDEV-8334)

    • In a future release series (after 10.6) the default value of old_mode will drop this new value, making utf8 default to utf8mb4

  • IPv6 by Default (MDEV-6536)

    • When --bind-address=HOSTNAME is configured, MariaDB Enterprise Server now listens on both IPv6 and IPv4 addresses.

Changes in Storage Engines

ColumnStore

  • This release incorporates MariaDB Enterprise ColumnStore version 6.1.1. Benefits include:

    • Disk-based aggregation allows larger aggregated result sets than can fit in memory

    • Increased DECIMAL precision

    • Transactional tables can be updated with data from ColumnStore tables

    • LZ4 compression

InnoDB

  • Default InnoDB flush method

  • SELECT .. SKIP LOCKED

    • SELECT [ FOR UPDATE | LOCK IN SHARED MODE ] .. SKIP LOCKED ignores already-locked rows. (MDEV-13115)

    • One use case for this feature is within applications that sell a limited resource, such as ticketing, rentals, or seat-based sales. In these applications, you need a way to display only the available inventory. This can be accomplished by querying available inventory and skipping locked rows.

      SELECT *
FROM ticketing
WHERE claimed = 0 AND section = 'B'
ORDER BY row DESC
LIMIT 10
FOR UPDATE SKIP LOCKED;

  • Compressed rows read-only by default

  • Information Schema changes for InnoDB

    • Information Schema INNODB_SYS_TABLESPACES directly reflects the filesystem. (MDEV-22343)

    • INNODB_SYS_TABLESPACES.PAGE_SIZE contains the physical page size of a page.

    • INNODB_SYS_TABLESPACES.FILENAME added as a replacement for SYS_DATAFILES.PATH

    • Information Schema INNODB_SYS_DATAFILES removed. (MDEV-22343)

  • Reduced global lock duration in InnoDB transaction deadlock checks (MDEV-24738)

  • InnoDB no longer acquires advisory file locks by default (MDEV-24393)

  • When using data-at-rest encryption with the file_key_management encryption plugin, InnoDB will automatically disable key rotation checks. (MDEV-14180)

  • Optimization added to speed up inserts into an empty InnoDB table. (MDEV-515)

  • Maximum value of the innodb_lock_wait_timeout system variable is now 100000000, which means infinite timeout.

  • Change in checksum algorithm options

    • innodb_checksum_algorithm options have changed: (MDEV-25105)

      • Supported: crc32, strict_crc32, full_crc32, strict_full_crc32

      • Eliminated: none, strict_none, innodb, strict_innodb

    • When InnoDB reads a page using an eliminated checksum algorithm after performing a physical upgrade, InnoDB will continue to accept the checksum.

    • When a query changes a page using an eliminated checksum algorithm, InnoDB will automatically switch to a supported checksum algorithm when InnoDB writes the changed page to disk.

Compatibility Enhancements

  • Expanded compatibility with Oracle through new functions:

  • Expanded compatibility with Oracle through sql_mode=ORACLE enhancements:

    • With sql_mode=ORACLE added MINUS as an alias to EXCEPT (MDEV-20021)

    • With sql_mode=ORACLE improved SYSDATE to allow use without parenthesis. (MDEV-19682)

    • With sql_mode=ORACLE supports a rownum pseudo-column name as an alias for the ROWNUM() function (MDEV-24089)

    • With sql_mode=ORACLE subqueries in a FROM clause do not require the AS clause.

  • Enhanced compatibility with Sybase SQL Anywhere through sql_mode=EXTENDED_ALIASES: (MENT-1062)

    • With sql_mode=EXTENDED_ALIASES, alias resolution and use of column aliases in the SQL SELECT list and WHERE clause.

    • With sql_mode=EXTENDED_ALIASES, support use of an alias in the SELECT list before the alias is defined.

    • With sql_mode=EXTENDED_ALIASES, if the same label is used for an alias and a column, the alias is used.

Operational Enhancements

  • sys Schema

  • Increase in host name length

    Host names in CREATE USER, GRANT, and replication CHANGE MASTER can be up to 255 bytes long. (MDEV-24312)

  • UTF8

    • (This item is also mentioned above in Notable Changes .)

    • The utf8 character set has been renamed to utf8mb3, which was formerly an alias for the utf8 character set

    • The character set utf8 is now an alias that defaults to utf8mb3 but can be turned into an alias for utf8mb4 with a config change

    • The new default of old_mode=UTF8_IS_UTF8MB3 is what makes utf8mb3 default to utf8, and anything that removes this new value from old_mode changes utf8 to mean utf8mb4 (MDEV-8334)

    • In a future release series (after 10.6) the default value of old_mode will drop this new value, making utf8 default to utf8mb4

  • Ignored indexes

    • An index can be marked with the IGNORED option, which forbids the optimizer from using the index in queries. The IGNORED option can be used to evaluate whether an index is actually helpful for performance without dropping the index. (MDEV-7317)

    • Example syntax for CREATE TABLE:

      CREATE TABLE table_name (
   id INT PRIMARY KEY,
   col_name INT,
   INDEX key_name (col_name) IGNORED
);

    • Example syntax for CREATE INDEX:

      CREATE INDEX key_name
   ON table_name
   (col_name) IGNORED;

    • Example syntax for ALTER TABLE:

      ALTER TABLE table_name
   ALTER INDEX key_name IGNORED;

    • An ignored index cannot be referenced in index hints, such as FORCE INDEX, IGNORE INDEX, or USE INDEX. When you try to reference an ignored index in an index hint, the server raises an error with the ER_KEY_DOES_NOT_EXISTS error code:

      SELECT *
FROM table_name
   FORCE INDEX (key_name)
WHERE col_name > 1;

      ERROR 1176 (42000): Key 'key_name' doesn't exist in table 'table_name'

  • Thread Pool enhancements

    • The thread pool can be configured to reshuffle connections into random thread groups periodically, which can help prevent many connections from becoming concentrated in just a few thread groups. (MENT-622)

    • The thread_pool_reshuffle_group_period system variable defines how frequently the connections are reshuffled. By default, the value is 0 which means that connections are not reshuffled.

    • The THREAD_POOL_CONNECTIONS information schema table can be used to view which connections are assigned to each thread group.

  • Systemd

    • Systemd socket activation is now supported. (MDEV-5536)

SQL Level Enhancements

  • JSON_TABLE()

    • JSON_TABLE() returns a table from JSON data. (MDEV-17399)

    • Queryable rows and columns are produced based on the JSON input, but are not stored in a table on disk. Column mappings are defined in a JSON path expression.

    • Prior to this release, the JSON_VALUE() and JSON_QUERY() functions could be used to retrieve values from JSON data on a per-column basis.

    • With JSON_TABLE():

      • JSON data can JOIN with existing tables.

      • A table can be created from JSON data using CREATE TABLE .. AS SELECT against a JSON_TABLE().

      • NESTED PATH enables extraction of nested data from JSON arrays and objects.

  • OFFSET syntax

    • Additional syntax is supported for SELECT .. OFFSET (MDEV-23908)

    • OFFSET start { ROW | ROWS } FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES } is an alternative to LIMIT .. OFFSET

    • The WITH TIES option requires the use of ORDER BY and allows the number of rows to exceed the FETCH count to ensure that the final row in the chunk includes any additional rows that have the same values in the ORDER BY fields (eliminating the need to fetch the next chunk to check for spill-over).

      • For example, the following query can return more than 10 rows if there are more username rows that match the username in the 10th row (the order of the purchase values within the complete set of each username's records is non-deterministic):

        SELECT username, purchase
FROM user_purchases
ORDER BY username
OFFSET 305 ROWS
FETCH NEXT 10 ROWS WITH TIES;

      • For example, the following query specifies ONLY instead of WITH TIES, so the query can't return more than 10 rows:

        SELECT username, purchase
FROM user_purchases
ORDER BY username, purchase
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

  • Views supported with FLUSH TABLES tbl_name [, tbl_name] .. WITH READ LOCK (MDEV-15888)

  • All SQL statements can be prepared except PREPARE, EXECUTE, DEALLOCATE / DROP PREPARE (MDEV-16708)

Security Features

  • MariaDB Enterprise Audit allows database-specific and table-specific filters. (MENT-65)

    For example:

    {
  "connect_event" : "ALL",
  "table_event" : ["READ","WRITE",{"ignore_tables" : "mysql.*"}],
  "query_event" : ["DDL",{"tables" : "test.t2"}]
}

  • The gssapi authentication plugin can now authenticate a user account by checking if the user belongs to an Active Directory group. (MDEV-23959)

    • The group is specified in the authentication string using the CREATE USER statement. The group can be specified using the group name or the SID.

    • Example syntax using a group name without specifying the domain:

      CREATE USER root
  IDENTIFIED VIA gssapi AS 'GROUP:Administrators';

    • Example syntax using a group name that specifies the domain:

      CREATE USER root
  IDENTIFIED VIA gssapi AS 'GROUP:Administrators';

    • Example syntax using a SID in the usual format:

      CREATE USER root
  IDENTIFIED VIA gssapi AS 'SID:S-1-5-32-544';

    • Example syntax using a well-known SID:

      CREATE USER everyone
  IDENTIFIED VIA gssapi AS 'SID:WD';

  • When using data-at-rest encryption with the file_key_management encryption plugin, InnoDB will automatically disable key rotation checks. (MDEV-14180)

  • With MariaDB Enterprise Cluster, TLS is required for MariaDB Enterprise Cluster by default. (MENT-1192)

    • Since TLS is required for Enterprise Cluster by default, database administrators should create TLS certificates for each node during the deployment process.

    • Database administrators can revert Enterprise Cluster to the mode used in previous releases by setting the wsrep_ssl_mode system variable to PROVIDER.

    • For additional information, see "WSREP TLS Modes".

  • TLS functionality for State Snapshot Transfers (SSTs) is enhanced when MariaDB Enterprise Backup or Rsync is the SST method. (MDEV-25359)

  • Cluster name verification is performed for Joiner nodes prior to State Snapshot Transfers (SSTs) and Incremental State Transfers (ISTs). (MDEV-25359)

  • With MariaDB Enterprise Cluster, system variable wsrep_certificate_expiration_hours_warning enables logging of a warning prior to expiration of the TLS certificate used for wsrep (Enterprise Cluster) communications. (MENT-1090)

  • With MariaDB Enterprise Cluster, communication between nodes can be changed from unencrypted to TLS without cluster downtime. (MDEV-22131)

MariaDB Replication

  • Performance Schema replication_applier_status_by_worker table provides information on replica worker threads. (MDEV-20220)

  • Fine-grained binlog expiration

  • Enhanced consistency for Semi-Sync Replication

    • When rpl_semi_sync_slave_enabled=ON, consistency is guaranteed for a Primary server in an HA (Primary/Replica) topology when using semi-synchronous replication. (MDEV-21117)

    • Prior to this release, when using semi-synchronous replication, if a Primary crashed before sending a transaction to the Replica, on restart the Primary could recover incomplete InnoDB transactions when rejoining as a Replica.

    • With this release, when using semi-synchronous replication and with rpl_semi_sync_slave_enabled=ON, incomplete transactions will be rolled-back on the Replica, ensuring the new Primary (former Replica) and new Replica (former Primary) remain in sync.

MariaDB Enterprise Cluster

MariaDB Enterprise Cluster is powered by Galera. New in this release:

  • XA Transactions are supported (MENT-690)

  • With MariaDB Enterprise Cluster, TLS is required for MariaDB Enterprise Cluster by default. (MENT-1192)

    • Since TLS is required for Enterprise Cluster by default, database administrators should create TLS certificates for each node during the deployment process.

    • Database administrators can revert Enterprise Cluster to the mode used in previous releases by setting the wsrep_ssl_mode system variable to PROVIDER.

    • For additional information, see "WSREP TLS Modes".

  • TLS functionality for State Snapshot Transfers (SSTs) is enhanced when MariaDB Enterprise Backup or Rsync is the SST method. (MDEV-25359)

  • Cluster name verification is performed for Joiner nodes prior to State Snapshot Transfers (SSTs) and Incremental State Transfers (ISTs). (MDEV-25359)

  • wsrep_certificate_expiration_hours_warning system variable enables logging of a warning prior to expiration of the TLS certificate used for wsrep (Enterprise Cluster) communications. (MENT-1090)

  • Communication between nodes can be changed from unencrypted to TLS without cluster downtime. (MDEV-22131)

  • Galera Cluster nodes can be configured to refuse statements that would generate local GTIDs. (MDEV-20715)

    • When Galera Cluster is used with MariaDB Replication, local GTIDs can cause replication errors when the primary or replica has to failover to a different cluster node. By configuring Galera Cluster nodes to refuse statements that would generate local GTIDs, replication is more likely to succeed against any available cluster node.

    • To configure a node to refuse statements that would generate local GTIDs, set wsrep_mode=DISALLOW_LOCAL_GTID.

  • wsrep_mode=STRICT_REPLICATION replaces deprecated system variable wsrep_strict_ddl (MDEV-20008)

  • wsrep_mode=REPLICATE_MYISAM replaces deprecated system variable wsrep_replicate_myisam (MDEV-24946)

  • When wsrep_debug=SERVER and wsrep_OSU_method=TOI, information about DDL queries from remote hosts is logged in the local error log, not just locally-initiated DDL queries. (MDEV-9609)

  • The script wsrep_sst_mariabackup checks all server-related configuration groups when processing a configuration file. (MDEV-25669)

    • Prior to this release, only the [mysqld] configuration group was checked when processing a configuration file.

  • Performance Schema for Enterprise Cluster

Interface Changes

The following changes are as compared to MariaDB Enterprise Server 10.5.10-7, the latest GA release on the prior release series.

Platforms

In alignment to the enterprise lifecycle, MariaDB Enterprise Server 10.6.4-1 is provided for:

  • CentOS 7

  • Debian 9

  • Debian 10

  • Microsoft Windows

  • Red Hat Enterprise Linux 7

  • Red Hat Enterprise Linux 8

  • SUSE Linux Enterprise Server 12

  • SUSE Linux Enterprise Server 15

  • Ubuntu 18.04

  • Ubuntu 20.04

Some components of MariaDB Enterprise Server might not support all platforms. For additional information, see "MariaDB Corporation Engineering Policies".