Creating MariaDB Xpand Tables

MariaDB Xpand tables are created using the CREATE TABLE statement.

Overview

When using the Xpand Performance topology:

  • Every table uses Xpand.

  • Xpand tables are created using the CREATE TABLE statement. The ENGINE table option is not required.

When using the Xpand Storage Engine topology:

Create Xpand Tables

Xpand Performance Topology

When using the Xpand Performance topology, Xpand tables must be created on one Xpand node using the CREATE TABLE statement:

CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
   branch_id INT NOT NULL,
   customer_id INT,
   invoice_date DATETIME(6),
   invoice_total DECIMAL(13, 2),
   payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
   PRIMARY KEY(invoice_id)
);

CREATE TABLE inventory.products (
   product_name varchar(11) NOT NULL DEFAULT '',
   supplier varchar(128) NOT NULL DEFAULT '',
   quantity varchar(128) NOT NULL DEFAULT '',
   unit_cost varchar(128) NOT NULL DEFAULT ''
);

The table automatically replicates to all Xpand nodes.

The ENGINE table option is not required.

Xpand Storage Engine Topology

When using the Xpand Storage Engine topology, Xpand tables must be created on one ES node using the CREATE TABLE statement with the ENGINE=Xpand table option:

CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
   branch_id INT NOT NULL,
   customer_id INT,
   invoice_date DATETIME(6),
   invoice_total DECIMAL(13, 2),
   payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
   PRIMARY KEY(invoice_id)
) ENGINE=Xpand;

CREATE TABLE inventory.products (
   product_name varchar(11) NOT NULL DEFAULT '',
   supplier varchar(128) NOT NULL DEFAULT '',
   quantity varchar(128) NOT NULL DEFAULT '',
   unit_cost varchar(128) NOT NULL DEFAULT ''
) ENGINE=Xpand;

If the database already exists on all ES nodes, the table automatically replicates to all ES nodes through the Xpand storage engine.

Note

When using the Xpand Storage Engine topology, MariaDB Enterprise Server does not automatically synchronize databases and other objects on all ES nodes. ES nodes require a replication solution, such as MariaDB Replication to synchronize databases and other objects. Without replication, databases need to be created separately on each ES node.

Create Xpand Tables using Session Default

Xpand Performance Topology

When using the Xpand Performance topology, Xpand is always the session default engine for new tables.

Xpand Storage Engine Topology

When using the Xpand Storage Engine topology, Xpand can be set as the session default engine for new tables by setting the session value of the default_storage_engine system variable to Xpand. When the CREATE TABLE statement is executed without the ENGINE table option, the table will use the Xpand storage engine:

SET SESSION default_storage_engine='Xpand';

CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
   branch_id INT NOT NULL,
   customer_id INT,
   invoice_date DATETIME(6),
   invoice_total DECIMAL(13, 2),
   payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
   PRIMARY KEY(invoice_id)
);

CREATE TABLE inventory.products (
   product_name varchar(11) NOT NULL DEFAULT '',
   supplier varchar(128) NOT NULL DEFAULT '',
   quantity varchar(128) NOT NULL DEFAULT '',
   unit_cost varchar(128) NOT NULL DEFAULT ''
);

If the database already exists on all ES nodes, the table automatically replicates to all ES nodes through the Xpand storage engine.

Note

When using the Xpand Storage Engine topology, MariaDB Enterprise Server does not automatically synchronize databases and other objects on all ES nodes. ES nodes require a replication solution, such as MariaDB Replication to synchronize databases and other objects. Without replication, databases need to be created separately on each ES node.

Configure Xpand as Global Default

Xpand Performance Topology

When using the Xpand Performance topology, Xpand is always the global default engine for new tables.

Xpand Storage Engine Topology

When using the Xpand Storage Engine topology, Xpand can be set as the global default engine for new tables by setting the global value of the default_storage_engine system variable to Xpand on each ES node. When the CREATE TABLE statement is executed without the ENGINE table option, the table will use the Xpand storage engine.

The default engine can be configured dynamically. To ensure that the change survives server restarts, the change should also be made in a configuration file.

Perform the following procedure on each ES node to configure Xpand as the global default engine:

  1. Choose a configuration file for custom changes to system variables and options.

    It is not recommended to make custom changes to Enterprise Server's default configuration files, because your custom changes can be overwritten by other default configuration files that are loaded after.

    Ensure that your custom changes will be read last by creating a custom configuration file in one of the included directories. Configuration files in included directories are read in alphabetical order. Ensure that your custom configuration file is read last by using the z- prefix in the file name.

    Some example configuration file paths for different distributions are shown in the following table:

    Distributions

    Example configuration file path

    CentOS
    RHEL
    SLES

    /etc/my.cnf.d/z-custom-mariadb.cnf

    Debian
    Ubuntu

    /etc/mysql/mariadb.conf.d/z-custom-mariadb.cnf

  1. Set the default_storage_engine system variable in the configuration file.

    It must be set in a group that will be read by MariaDB Server, such as [mariadb] or [server].

    For example:

    [mariadb]
    ...
    default_storage_engine=Xpand
    
  2. Connect to the node using MariaDB Client as the root@localhost user account or another user account with the SUPER privilege:

    $ mariadb --user=root
    
  3. Set the default_storage_engine system variable to Xpand using the SET GLOBAL statement:

    SET GLOBAL default_storage_engine='Xpand';
    

Table Options

Xpand Performance Topology

When using the Xpand Performance topology, MariaDB Xpand supports the following table options:

Table Option

Description

CONTAINER

Specifies whether the table is in-memory or on-disk.

  • Set to SKIPLIST to create an in-memory table.

  • Set to LAYERED (default) to create an on-disk table.

REPLICAS

Specifies the number of replicas Xpand maintains across the deployment:

  • Set to a number to maintain a specific number of replicas (defaults to 2)

  • Set to ALLNODES to maintain a replica of all data on each node.

SLICES

Specifies the number of slices Xpand maintains for each representation of table data for distribution through the deployment.

Xpand Storage Engine Topology

When using the Xpand Storage Engine topology, MariaDB Enterprise Server supports the following table options with the Xpand storage engine:

Table Option

Description

AUTO_INCREMENT

  • Specifies the starting value for columns defined as AUTO_INCREMENT.

COMMENT

  • Specifies a user-readable comment for the table.

DEFAULT CHARACTER SET

  • Specifies the default character set for columns which do not have a different character set explicitly specified. Note, that Xpand does not support all character sets available to the Server.

DEFAULT COLLATE

  • Specifies the default collation for columns which do not have a different collation explicitly specified. Note that Xpand does not support all collations available to the Server.

Resources

MariaDB Knowledge Base

Reference

SQL Statements