Release Notes for MariaDB Xpand 6.1.0


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.

When an online group change occurs:

  • 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_locks and lockman_max_transaction_locks system 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 unavailability 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.

  • If the number of nodes being added to the cluster is greater than or equal to the number of non-late nodes currently in the cluster.


  • The cluster is restarted via clx dbrestart


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.nodeinfo and 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.


  • To use BACKUP with AWS S3:

    BACKUP *.* TO
  • To use RESTORE with AWS S3:


GCP Cloud Storage

  • To use BACKUP with GCP Cloud Storage:

    BACKUP *.* TO
  • To use RESTORE with GCP Cloud Storage:


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.

Create Roles

  • To create one or more roles, use the CREATE ROLE statement:

    CREATE ROLE account_managers, customer_support, contractors;

Grant Privileges and Roles

  • To grant privileges to one or more roles, use the GRANT statement:

    GRANT SELECT ON *.* TO account_managers, customer_support, contractors;
  • To grant a role to one or more users, use the GRANT statement:

    GRANT account_managers TO alice, sofia;

Activate Roles

  • To activate one or more roles for a user, use the SET ROLE statement:

    SET ROLE account_managers, customer_support;
  • To activate all roles available for a user, use the SET ROLE ALL statement:


Default Roles

  • To specify the default roles for a user, use the SET DEFAULT ROLE statement:

    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 ALL statement:


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.







Aggregation Performance Enhancements

Xpand 6.1 contains performance enhancements for aggregation queries, such as the following:

FROM sales

Streaming Aggregations

In Xpand 6.1, streaming aggregation operations are enabled by default. (XPAND-36040)

The following system variables are enabled by default:

  • enable_rule_scalar_stream_aggregate_impl

  • enable_rule_scalar_stream_aggregate_over_partial

  • enable_rule_groupby_stream_aggregate_impl

  • enable_rule_dist_groupby_stream_aggregate_impl

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_skiplist system variable has changed from OFF to ON.

Predictive Governor

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)

Set Threshold

  • To set a threshold for the predictive governor, use the sierra_predictive_governor_threshold system variable:

    SET GLOBAL sierra_predictive_governor_threshold=<number_of_rows>;


  • To disable the predictive governor, use the sierra_predictive_governor system variable:

    SET GLOBAL sierra_predictive_governor=OFF;

For additional information about the predictive governor, see "Configure the Predictive Governor with MariaDB Xpand".

Proxy Protocol

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_networks system variable to a space-separated list of IPv4 networks:

    SET GLOBAL proxy_protocol_networks='';
  • Xpand does not support a mix of proxy and non-proxy traffic on the same network.

Enable in MaxScale

Disable in Xpand

  • To disable the proxy protocol in Xpand, set the proxy_protocol_networks system variable to the empty string:

    SET GLOBAL proxy_protocol_networks='';

For additional information about the proxy protocol, see "Proxy Protocol".

Tools and Utilities

In Xpand 6.1, the clx_import and clx utilities have some enhancements and new features.


  • The clx_import utility can detect and alert to non-retriable errors. (XPAND-35649)

  • clx_import supports concurrent imports with the --allow-multi command-line option. (XPAND-34613)

    • The --allow-multi command-line option implies --no-globals, so that the concurrent imports don't leave system variables set incorrectly.

    • The first clx_import process can be run as usual without the --allow-multi command-line option, with the normal global system variable changes. The --allow-multi command-line option is only required for subsequent clx_import processes.


  • The clx utility is converted to Python 3. (XPAND-34219)

Enhanced Compatibility

Character Sets and Collations

Data Types

Strict Mode

DDL (Data Definition Language) Statements

Show Statements

Information Schema

  • In Xpand 6.1, the information_schema.COLUMNS table has been modified to be more similar to MySQL and MariaDB Enterprise Server. (XPAND-35574)

    • Starting with this release, the DATA_TYPE column is no longer suffixed with unsigned for columns using the FLOAT and DOUBLE data types.

  • In Xpand 6.1, the information_schema.TABLES table has been modified to avoid returning inconsistent numbers of table rows on different cluster nodes. (XPAND-26352)

    • Starting with this release, the TABLE_ROWS column always reports 0

System Variables

Stored Functions

  • Xpand 6.1 throws an error when a stored function parameter is declared as an UNSIGNED DOUBLE and 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:

    • When the sierra_new_card_estimation system 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_JOIN semantics for queries that contain a lot of joins. (XPAND-35338)

    • The straight_join_threshold system 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 NULL predicate with a column that can't be NULL (XPAND-35947)

    • For queries with a LEFT JOIN that can be eliminated (XPAND-36368)

    • When rule_minmax_limit is enabled and a query selects MIN() or MAX() of an index prefix column with an index hint (XPAND-35127)

    • For queries that contain an aggregation with DISTINCT (XPAND-35138)

    • 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 system.partition_sizes view. (XPAND-33451)

  • A histogram tracks I/O latency for each vdev in the system.vdev_io_latency_histogram table. (XPAND-34812)


  • When statd is used in Prometheus exporter mode, it removes cached stats for nodes that have been dropped. (XPAND-35943)

  • When statd is used in Prometheus exporter mode, it can translate nanoseconds to seconds. (XPAND-36396)

  • The total_busy metric is classified as a counter. (XPAND-36403)

  • statd overrides the value of the session_log_slow_threshold_ms system variable, so that its queries are not logged to query.log. (XPAND-36418)

  • The Com_commit and Com_rollback metrics can be used to monitor commits and rollbacks. (XPAND-24585)


  • When the -hostname option 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.


  • When the --storage-allocate option 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_limits configuration file that it creates in the /etc/security/limits.d directory. (XPAND-35975)

  • 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.conf configuration file. (XPAND-35152)

Upgrade Script

  • When the upgrade script fails, logs are left behind in the /tmp/xpand_upgrade_XXX directory, 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)

Interface Changes

Character sets

  • cp850 character set added


Data Types


  • CAST_NULL_ON_ERR function added

Information Schema

  • APPLICABLE_ROLES information schema table added

  • ENABLED_ROLES information schema table added


SQL Statements

Status Variables

  • Com_commit status variable added

  • Com_create_role status variable added

  • Com_drop_role status variable added

  • Com_grant_role status variable added

  • Com_revoke_role status variable added

  • Com_rollback status variable added

  • qpc_recompile_total_factor status variable removed

System Tables

  • columnar_containers system table added

  • gmp system table added

  • http_requests system table added

  • http_socks system table added

  • mysql_db_replication_policy system table removed

  • roles system table added

  • roles_mapping system table added

  • session_acl system table added

  • session_roles system table added

  • session_routine_acl system table added

  • session_variables system table added

  • user_role_links system table added

  • users_and_roles system table added

  • vdev_io_latency_histogram system table added

System Variables

  • alter_hold_until_checkpoint system variable added

  • alter_lock system variable added

  • barrier_row_cost system variable added

  • barrier_setup_cost system variable added

  • blocked_fibers_to_dump system variable added

  • cost_sigma_wait system variable added

  • dbstart_flip_empty_queues system variable added

  • enable_rule_filter_isnull_from_or_removal system variable added

  • http_idle_timeout_secs system variable added

  • http_low_speed_limit system variable added

  • http_low_speed_timeout_secs system variable added

  • jdbcCompliantTruncation system variable removed

  • join_swap_threshold system variable added

  • performance_schema system variable added

  • proxy_protocol_networks system variable added

  • qpc_max_cost_factor system variable removed

  • qpc_scale_tot_factor system variable removed

  • row_limit_multiplier system variable added

  • sierra_or_num_inputs_threshold system variable added

  • sierra_predictive_governor system variable added

  • sierra_predictive_governor_threshold system variable added

  • sierra_remove_empty_filter_above_join system variable added

  • sierra_skip_magic_formula_no_pd system variable added

  • sierra_table_def_card system variable added

  • straight_join_threshold system variable added

  • task_tcp_arp_interval_ms system variable removed

  • tx_sync_commit system variable removed

  • union_all_rule_threshold system variable added

  • url_redact system variable added


Installation Instructions

Upgrade Instructions