Release Notes for MariaDB Xpand 6.1.0
This page is part of MariaDB's MariaDB Documentation.
The parent of this page is: Release Notes for MariaDB Xpand 6.1
Topics on this page:
MariaDB Xpand is a distributed SQL database. This is the first General Availability (GA) release in the MariaDB Xpand 6.1 series.
MariaDB Xpand 6.1.0 was released on 2023-02-02.
Online Group Changes
Xpand 6.1 adds support for online group changes, which allows the cluster to remain fully online and available while a node is being added. (Xpand-4809)
Xpand uses a distributed group membership protocol to maintain the static set of all nodes known to the cluster and checks that the nodes maintain active communication between each other. Xpand refers to this as a group. When the set of nodes changes, there is a change in the group of nodes in the cluster, which is referred to as a group change.
In Xpand 6.1, depending on the specific state of the cluster and the operation being performed, Xpand goes through either a full group change or an online group change:
During an online group change, the cluster remains fully online and available.
During a full group change, there is a brief period of unavailability, typically lasting a few seconds.
Starting with Xpand 6.1, when a node is added to a cluster, Xpand attempts to perform an online group change. When nodes are added as part of an online group change, the rest of the cluster is online and available for the duration of the group change.
During the online group change:
The new node(s) are marked with the "late" sub-state
The Rebalancer copies data to the new node(s)
Existing nodes continue processing transactions
The new node(s) are fully able to service transactions, queries
MaxScale can detect the new nodes and begin routing connections to the new nodes
The new node(s) do not participate in some internal operations, such as the sequence manager and lock manager. The
lockman_max_transaction_lockssystem variables apply to non-late nodes only, and late nodes do not manage any locks.
Full group changes are equivalent to the group changes from previous Xpand releases, but these types of group changes are known as full group changes going forward.
Full group changes cause the cluster to experience a brief period of availability during certain operations, such as the following:
A node is removed from the cluster, due to a scale-in operation or an unexpected node or zone failure.
A number of nodes is being added to the cluster, and the number of nodes is greater than or equal to the number of nodes currently in the cluster.
ALTER CLUSTER ADD .. COORDINATEis executed.
The cluster is restarted via
As a part of this change in Xpand 6.1, the procedure to add nodes, which is known as the scale-out procedure, is overhauled to avoid a full group change when zones are not in use.
During scale-out, when zones are not in use, Xpand 6.1 adds the new nodes with the "late" sub-state. Administrators can join the
system.membership system tables or execute
clx status to determine which nodes are "late".
For additional information about the scale-out procedure, see "Scale-Out with MariaDB Xpand 6.1".
For additional information about group changes, see "Group Changes with MariaDB Xpand 6.1".
Backup to Object Storage in the Cloud
Xpand 6.1 adds support for backing up to object storage in the cloud. (Xpand-20742)
Storing backups in cloud-based object storage can result in cost reductions and can simplify administration:
Economical: Cloud-based object storage is often very low cost.
Flexible: Cloud-based object storage is available for both cloud and on-premises deployments.
Limitless: Cloud-based object storage is often virtually limitless.
Resilient: Cloud-based object storage is often low maintenance and highly available, since many services use resilient cloud infrastructure.
Scalable: Cloud-based object storage is often highly optimized for read and write scaling.
Secure: Cloud-based object storage is often encrypted-at-rest.
Cloud backups have been tested with AWS S3 and GCP Cloud Storage. Many cloud-based object storage services are compatible with AWS S3. MariaDB cannot make guarantees about all S3-compatible object storage services, because different services provide different functionality. If you have any questions about using specific S3-compatible object storage with Xpand, contact MariaDB Support.
BACKUPwith AWS S3:
BACKUP *.* TO 's3://AWS_BUCKET_NAME/OBJECT_NAME?access_key_id=AWS_ACCESS_KEY_ID&secret_access_key=AWS_SECRET_ACCESS_KEY®ion=AWS_REGION&session_token=AWS_SESSION_TOKEN'"
RESTOREwith AWS S3:
RESTORE *.* FROM 's3://AWS_BUCKET_NAME/OBJECT_NAME?access_key_id=AWS_ACCESS_KEY_ID&secret_access_key=AWS_SECRET_ACCESS_KEY®ion=AWS_REGION&session_token=AWS_SESSION_TOKEN'"
GCP Cloud Storage
BACKUPwith GCP Cloud Storage:
BACKUP *.* TO 'gs://GCP_BUCKET_NAME/OBJECT_NAME?credentials=GCP_CREDENTIALS_BASE64'
RESTOREwith GCP Cloud Storage:
RESTORE *.* FROM 'gs://GCP_BUCKET_NAME/OBJECT_NAME?credentials=GCP_CREDENTIALS_BASE64'
For additional information about cloud-based backup and restore, see "Fast Backup and Restore with MariaDB Xpand".
Xpand 6.1 adds support for roles, which are named sets of privileges. (Xpand-34842)
Privileges can be granted to and revoked from roles.
In Xpand 6.1, a user can be granted multiple roles, and the user can activate multiple roles or even all available roles at the same time.
When a role is granted to a user, the user can use all privileges held by the role.
When a role is granted to another role, the role inherits all privileges held by the granted role, and users with the role can use all privileges held by both roles.
To create one or more roles, use the
CREATE ROLE account_managers, customer_support, contractors;
Grant Privileges and Roles
To activate one or more roles for a user, use the
SET ROLE account_managers, customer_support;
To activate all roles available for a user, use the
SET ROLE ALLstatement:
SET ROLE ALL;
To specify the default roles for a user, use the
SET DEFAULT ROLEstatement:
SET DEFAULT ROLE customer_support FOR alice;
To specify that all roles available for the user should be activated by default, use the
SET DEFAULT ROLE ALLstatement:
SET DEFAULT ROLE ALL FOR alice;
For additional information about roles, see "Roles for MariaDB Xpand".
Granular Administrative Privileges
In Xpand 6.1, administrative privileges can be configured at a more granular level, so that privileges to perform some administrative operations can be granted without granting the
SUPER privilege. (Xpand-35290)
The following new privileges are supported:
In Xpand 6.1, the
SUPER privilege is still a superset of the new privileges.
Before upgrading production environments to Xpand 6.1, MariaDB recommends testing the new version in a development environment to ensure that all requisite grants are in place, including for infrastructure users.
BINLOG ADMINprivilege grants permissions for the following operations:
SHOW BINLOG POSITION
SHOW BINLOG SEGMENTS
CLUSTER ADMINprivilege grants permissions for the following operations:
CONNECTION ADMINprivilege grants permissions for the following operations:
REPAIR ADMINprivilege grants permissions for the following operations:
REPLICATION ADMINprivilege grants permissions for the following operations:
SET USERprivilege grants permissions for the following operations:
SYSTEM VARIABLES ADMIN
SYSTEM VARIABLES ADMINprivilege grants permissions for the following operations:
Aggregation Performance Enhancements
Xpand 6.1 contains performance enhancements for aggregation queries, such as the following:
SELECT ACCT_ID, SUM(SALES_AMT) FROM sales GROUP BY ACCT_ID;
In Xpand 6.1, streaming aggregation operations are enabled by default. (Xpand-36040)
The following system variables are enabled by default:
In-Memory Skip-lists for Aggregations
By default, Xpand 6.1 uses in-memory skip-lists for temporary data structures during execution of aggregation queries. (Xpand-35186)
This change allows aggregation queries to be executed more efficiently.
The default value of the
sigma_skiplistsystem variable has changed from
Xpand 6.1 adds support for a predictive governor, which prevents execution of queries that are estimated to process an excessive number of rows as a result of a join. This may be the result of a join query that is poorly indexed, or that is written such that it does not use available indexes. (Xpand-35834)
To set a threshold for the predictive governor, use the
SET GLOBAL sierra_predictive_governor_threshold=<number_of_rows>;
To disable the predictive governor, use the
SET GLOBAL sierra_predictive_governor=OFF;
For additional information about the predictive governor, see "Configure the Predictive Governor with MariaDB Xpand".
Xpand 6.1 adds support for the proxy protocol, which can simplify authentication when used with a load balancer, such as MariaDB MaxScale.. (Xpand-30980)
When the proxy protocol is used with MaxScale, MaxScale transmits the original client IP address to Xpand, so that Xpand can use the client IP address as the origin address for the purposes of authentication, rather than using MaxScale's IP address.
Xpand supports both the v1 and v2 versions of the proxy protocol, including a mix of both versions.
Enable in Xpand
To enable the proxy protocol in Xpand for a specific network, set the
proxy_protocol_networkssystem variable to a space-separated list of IPv4 networks:
SET GLOBAL proxy_protocol_networks='192.0.2.0/24 198.51.100.0/24';
Xpand does not support a mix of proxy and non-proxy traffic on the same network.
Enable in MaxScale
To enable the proxy protocol in MariaDB MaxScale, set the
Disable in Xpand
To disable the proxy protocol in Xpand, set the
proxy_protocol_networkssystem variable to the empty string:
SET GLOBAL proxy_protocol_networks='';
For additional information about the proxy protocol, see "Proxy Protocol for MariaDB Xpand".
Tools and Utilities
In Xpand 6.1, the
clx utilities have some enhancements and new features.
clx_importutility can detect and alert to non-retriable errors. (Xpand-35649)
clx_importsupports concurrent imports with the
--allow-multicommand-line option. (Xpand-34613)
--allow-multicommand-line option implies
--no-globals, so that the concurrent imports don't leave system variables set incorrectly.
clx_importprocess can be run as usual without the
--allow-multicommand-line option, with the normal global system variable changes. The
--allow-multicommand-line option is only required for subsequent
clxutility is converted to Python 3. (Xpand-34219)
Character Sets and Collations
Xpand 6.1 adds support for the
cp850character set and the
Xpand 6.1 adds support for using prepared statements with the
utf8mb4character set. (Xpand-30737)
Xpand 6.1 adds support for the
CLOBdata type. (Xpand-36307) (XPT-511)
Xpand 6.1 adds support for the
NVARCHARdata type. (Xpand-36306)
In Xpand 6.1, when strict mode is enabled using the
STRICT_ALL_TABLESSQL mode, data type and range checks are performed for more data types. (Xpand-894) (Xpand-35266) (Xpand-35362) (Xpand-35491)
Validation checks are added for the
Validation checks are added for the
Validation checks are added for the
UNSIGNED INTdata type.
DDL (Data Definition Language) Statements
Xpand 6.1 adds support for
IF NOT EXISTSto some
Xpand 6.1 adds support for a primary key that references the same column multiple times. (Xpand-32948)
Xpand 6.1 adds an option to make transaction processing wait for
ALTER TABLEoperations to complete before continuing. (Xpand-13104)
To make transaction processing wait, set the global value of the
alter_hold_until_checkpointsystem variable to
This new setting is useful for
ALTER TABLEoperations that modify a column to be
NOT NULLwhere inserts do not provide a value.
Xpand 6.1 adds an option to control the default implicit locking behavior for
ALTER TABLEoperations. (Xpand-36409)
To control the implicit locking behavior, set the session or global value of the
alter_locksystem variable to
The default value of
NONEcauses operations to fail when a lock is needed, which helps prevent accidental table locking.
To override the system variable for a single statement, set the
ALTER TABLE hq_sales.invoices DROP PRIMARY KEY, LOCK=SHARED;
REPAIR TABLEstatement can repair tables that have corrupt unique indexes. (Xpand-35095)
In Xpand 6.1, when the
SHOW CREATE PROCEDUREstatement is executed, the
Create Procedurecolumn is now a string instead of a blob. (Xpand-35696)
In Xpand 6.1, the
information_schema.COLUMNStable has been modified to be more similar to MySQL and MariaDB Enterprise Server. (Xpand-35574)
In Xpand 6.1, the
information_schema.TABLEStable has been modified to avoid returning inconsistent numbers of table rows on different cluster nodes. (Xpand-26352)
Starting with this release, the
TABLE_ROWScolumn always reports
Xpand 6.1 adds dummy support for some system variables that are queried by MariaDB Connectors:
Xpand 6.1 throws an error when a stored function parameter is declared as an
UNSIGNED DOUBLEand a negative value is provided. (Xpand-35401)
Query Planning and Compilation
Xpand 6.1 has numerous improvements to the query optimizer in reducing compile time as well as statement costing to select more efficient plans.
Xpand 6.1 has multiple improvements to cardinality estimation:
sierra_new_card_estimationsystem variable is enabled (Xpand-35855)
For join conditions involving expressions, unions, and multiple columns (Xpand-36232)
Xpand 6.1 reduces the time spent compiling
IN(..)lists with hundreds of entries. (Xpand-35364)
Xpand 6.1 automatically uses
STRAIGHT_JOINsemantics for queries that contain a lot of joins. (Xpand-35338)
straight_join_thresholdsystem variable can be used to configure the threshold.
Xpand 6.1 can choose more optimal plans in multiple situations:
For queries that can use Columnar indexes (Xpand-35451)
For queries involving empty user tables. (Xpand-35393)
For queries that use an
IS NULLpredicate with a column that can't be
For queries with a
LEFT JOINthat can be eliminated (Xpand-36368)
rule_minmax_limitis enabled and a query selects
MAX()of an index prefix column with an index hint (Xpand-35127)
For queries that contain an aggregation with
For queries that contain an aggregation on columns with no probability distributions (PDs) (Xpand-35231)
Xpand 6.1 is better at determining when a query plan in the query plan cache is sub-optimal and should be re-compiled. (Xpand-34851)
The changes listed below are related to monitoring.
The sizes of individual partitions can be monitored using the
A histogram tracks I/O latency for each vdev in the
statdis used in Prometheus exporter mode, it removes cached stats for nodes that have been dropped. (Xpand-35943)
statdis used in Prometheus exporter mode, it can translate nanoseconds to seconds. (Xpand-36396)
total_busymetric is classified as a counter. (Xpand-36403)
statdoverrides the value of the
session_log_slow_threshold_mssystem variable, so that its queries are not logged to
Com_rollbackmetrics can be used to monitor commits and rollbacks. (Xpand-24585)
-hostnameoption is used to manually specify a hostname, Xpand writes the specified hostname to log files instead of the system hostname. (Xpand-35853)
Installation and Upgrade Improvements
The changes listed below are improvements to installation and upgrade.
--storage-allocateoption is specified to the installer, the wizard is skipped. (Xpand-35642)
The installer decreases the TCP SYNACK retry count, so that the wait to detect a failed connection is reduced from 127 seconds to 15 seconds. (Xpand-35842)
When installing Xpand 6.1, the installer tries to detect existing installations of Xpand or ClustrixDB. (Xpand-35957)
The installer sets proper ownership of the
pam_limitsconfiguration file that it creates in the
The installer is better at matching IP addresses to interfaces by parsing subnet masks. (Xpand-35967)
The installer sets the correct permissions on the
/etc/xpand/xpdnode.confconfiguration file. (Xpand-35152)
When the upgrade script fails, logs are left behind in the
/tmp/xpand_upgrade_XXXdirectory, so that they can be inspected to diagnose the failure. (Xpand-30597)
The upgrade script checks the MD5 checksum of the upgrade package earlier in the upgrade process, so that a corrupt package can be detected before the script tries to use the package. (Xpand-35935)
cp850 character set added
general_collation added ci
APPLICABLE_ROLESinformation schema table added
ENABLED_ROLESinformation schema table added
BINLOG ADMIN Privilege user privileges added
CLUSTER ADMIN Privilege user privileges added
CONNECTION ADMIN Privilege user privileges added
REPAIR ADMIN Privilege user privileges added
REPLICATION ADMIN Privilege user privileges added
SYSTEM VARIABLES ADMIN Privilege user privileges added
SET USER Privilege user privileges added
CREATE ROLE SQL statement added
DROP ROLE SQL statement added
SET DEFAULT ROLE SQL statement added
SET ROLE SQL statement added
Com_commitstatus variable added
Com_create_rolestatus variable added
Com_drop_rolestatus variable added
Com_grant_rolestatus variable added
Com_revoke_rolestatus variable added
Com_rollbackstatus variable added
qpc_recompile_total_factorstatus variable removed
columnar_containerssystem table added
gmpsystem table added
http_requestssystem table added
http_sockssystem table added
mysql_db_replication_policysystem table removed
rolessystem table added
roles_mappingsystem table added
session_aclsystem table added
session_rolessystem table added
session_routine_aclsystem table added
session_variablessystem table added
user_role_linkssystem table added
users_and_rolessystem table added
vdev_io_latency_histogramsystem table added
alter_hold_until_checkpointsystem variable added
alter_locksystem variable added
barrier_row_costsystem variable added
barrier_setup_costsystem variable added
blocked_fibers_to_dumpsystem variable added
cost_sigma_waitsystem variable added
dbstart_flip_empty_queuessystem variable added
enable_rule_filter_isnull_from_or_removalsystem variable added
http_idle_timeout_secssystem variable added
http_low_speed_limitsystem variable added
http_low_speed_timeout_secssystem variable added
jdbcCompliantTruncationsystem variable removed
join_swap_thresholdsystem variable added
performance_schemasystem variable added
proxy_protocol_networkssystem variable added
qpc_max_cost_factorsystem variable removed
qpc_scale_tot_factorsystem variable removed
row_limit_multipliersystem variable added
sierra_or_num_inputs_thresholdsystem variable added
sierra_predictive_governorsystem variable added
sierra_predictive_governor_thresholdsystem variable added
sierra_remove_empty_filter_above_joinsystem variable added
sierra_skip_magic_formula_no_pdsystem variable added
sierra_table_def_cardsystem variable added
straight_join_thresholdsystem variable added
task_tcp_arp_interval_mssystem variable removed
tx_sync_commitsystem variable removed
union_all_rule_thresholdsystem variable added
url_redactsystem variable added
XpandGUI is deprecated and will be removed in a future release.
As a replacement for XpandGUI, use the Remote Observability Service.