ALTER TABLE with MariaDB Xpand

ALTER TABLE .. ADD COLUMNAR INDEX

With MariaDB Xpand, the ALTER TABLE statement can be used to create a new Columnar index on an existing table using the ADD COLUMNAR INDEX clause:

ALTER TABLE hq_sales.invoices
   ADD COLUMNAR INDEX idx_invoices_date_total (invoice_date, invoice_total);

For best performance, MariaDB recommends loading data into the table prior to adding a Columnar index.

ALTER TABLE .. ADD INDEX

With MariaDB Xpand, the ALTER TABLE statement can be used to create a new secondary index on an existing table using the ADD INDEX clause:

ALTER TABLE hq_sales.invoices
   ADD INDEX idx_invoices_date_total (invoice_date, invoice_total);

ALTER TABLE .. ADD UNIQUE INDEX

With MariaDB Xpand, the ALTER TABLE statement can be used to create a new unique index on an existing table using the ADD UNIQUE INDEX clause:

ALTER TABLE hq_sales.invoices
   ADD UNIQUE INDEX idx_invoices_date_total (invoice_date, invoice_total);

ALTER TABLE .. ALTER INDEX

With MariaDB Xpand, the ALTER TABLE statement can be used to alter an existing index using the ALTER INDEX clause:

ALTER TABLE hq_sales.invoices
   ALTER COLUMNAR INDEX idx_invoices_date_total (invoice_date, invoice_total)
   INVISIBLE;

ALTER TABLE .. DROP INDEX

With MariaDB Xpand, the ALTER TABLE statement can be used to drop an index from an existing table using the DROP INDEX clause:

ALTER TABLE hq_sales.invoices
   DROP INDEX idx_invoices_date_total;

ALTER TABLE .. RENAME INDEX

With MariaDB Xpand, the ALTER TABLE statement can be used to rename an index using the RENAME INDEX clause:

ALTER TABLE hq_sales.invoices
   RENAME INDEX idx_invoices_date_total
   TO idx_invoices_date_total_old;

ALTER TABLE .. ADD PRIMARY KEY

With MariaDB Xpand, the ALTER TABLE statement can be used to create a primary key on an existing table using the ADD PRIMARY KEY clause:

ALTER TABLE hq_sales.invoices
   ADD PRIMARY KEY (invoice_id);

ALTER TABLE .. DROP PRIMARY KEY

With MariaDB Xpand, the ALTER TABLE statement can be used to drop a primary key from an existing table using the DROP PRIMARY KEY clause:

ALTER TABLE hq_sales.invoices
   DROP PRIMARY KEY, LOCK=SHARED;

Locking for the operation can be configured using the LOCK clause.

ALTER TABLE .. PRIMARY KEY

With MariaDB Xpand, the ALTER TABLE statement can be used to alter a primary key on an existing table using the PRIMARY KEY clause:

ALTER TABLE hq_sales.invoices
   PRIMARY KEY DISTRIBUTE=3;

Replace a Primary Key

With MariaDB Xpand, the ALTER TABLE statement can be used to replace a primary key in an existing table by combining the DROP PRIMARY KEY and ADD PRIMARY KEY clauses:

ALTER TABLE hq_sales.invoices
   DROP PRIMARY KEY,
   ADD PRIMARY KEY (invoice_id, branch_id);

ALTER TABLE .. ADD CONSTRAINT .. FOREIGN KEY

With MariaDB Xpand, the ALTER TABLE statement can be used to add a foreign key using the ADD FOREIGN KEY clause with an optional constraint name:

ALTER TABLE hq_sales.invoices
   ADD CONSTRAINT fk_invoices_customers
      FOREIGN KEY (customer_id)
      REFERENCES hq_sales.customers (customer_id)
      ON DELETE RESTRICT
      ON UPDATE RESTRICT;

ALTER TABLE .. DROP FOREIGN KEY

With MariaDB Xpand, the ALTER TABLE statement can be used to drop a foreign key using the DROP FOREIGN KEY clause:

ALTER TABLE hq_sales.invoices
   DROP FOREIGN KEY fk_invoices_customers;

Storage for Full Table Copy

When MariaDB Xpand executes an ALTER TABLE statement, it should have sufficient storage to create a full copy of the table, including replicas.

The size of a table can be estimated from system.table_sizes:

SELECT * FROM system.table_sizes;

Undo Log Growth

When MariaDB Xpand executes an ALTER TABLE statement, it should have sufficient storage to allow the Undo Log to grow due to concurrent queries:

• Xpand pauses Undo Log trim until the ALTER TABLE statement completes

• The required space depends on the current Undo Log size and expected growth from concurrent INSERT, UPDATE, and DELETE queries

Binary Log Growth

When MariaDB Xpand executes an ALTER TABLE statement, it should have sufficient storage to allow the binary logs to grow from concurrent queries:

• Xpand does not pause binlog trim during the ALTER TABLE statement, but Xpand does not free the corresponding storage until Undo Log trim resumes after the ALTER TABLE operation completes

Minimum Available Space

When MariaDB Xpand executes an ALTER TABLE statement, it should generally have a minimum of 10% free storage after accounting for all of the above factors:

• Xpand requires at least 5% of free storage, because Xpand will kill long-running transactions (including the ALTER TABLE operation) if free storage falls below that

Sequential Execution with Inbound Replication

When MariaDB Xpand is configured for inbound replication, replicated ALTER TABLE statements are executed sequentially within the replication stream:

• Replicated writes are paused until the replicated replicated ALTER TABLE statement completes

• If the ALTER TABLE statement takes a long time, slave lag can occur

• Before performing large ALTER TABLE operations over inbound replication, check if your cluster's binary log retention is sufficient to save the binary logs until replication has caught up

Row-Based Replication with Inbound Replication

When MariaDB Xpand is configured for inbound replication with row-based replication (RBR), the table columns in the binary log must match the table columns in the table's schema in Xpand:

• When a table's schema changes, but Xpand tries to apply binary log events that reference the old schema, replication errors can occur

• If a schema change operation will modify a column when inbound row-based replication is configured, the schema change operation can be executed in the replication stream to force sequential execution with replicated writes

• The following schema change operations do not impact row-based replication:

  • Adding indexes

  • Changing the storage type

  • Changing the number of slices