alter_algorithm

This page is part of MariaDB's MariaDB Documentation.

The parent of this page is: System Variables for MariaDB Enterprise Server

Topics on this page:

Overview
USAGE
DETAILS
EXAMPLES
CHANGE HISTORY

Overview

In 10.6 ES, 10.6 CS, 10.5 ES, 10.5 CS, 10.4 ES, 10.4 CS, 10.3 ES, 10.3 CS:: Configures the server to use specific algorithms for ALTER TABLE operations.

In 10.6 ES, 10.5 ES, 10.4 ES:: Configures the server to use specific algorithms for ALTER TABLE operations.

In 10.2 ES, 10.2 CS:: Not present

USAGE

The alter_algorithm system variable can be set in a configuration file:

[mariadb]
alter_algorithm=INPLACE

The global value of the alter_algorithm system variable can also be set dynamically at runtime by executing SET GLOBAL:

SET GLOBAL alter_algorithm='INPLACE';

The session value of the alter_algorithm system variable can also be set dynamically at runtime by executing SET SESSION:

SET SESSION alter_algorithm='INPLACE';

When the alter_algorithm system variable is set dynamically at runtime, its value will be reset the next time the server restarts. To make the value persist on restart, set it in a configuration file too.

DETAILS

This system variable can configure the server to use specific algorithms for ALTER TABLE operations:

Alter Algorithm	Description
DEFAULT	In this mode, the server uses the most efficient algorithm for a given operation.
COPY	In this mode, the server will only use the `COPY` algorithm for any operation. This algorithm is very slow and inefficient, so it is not generally desirable to set this value.
INPLACE	In this mode, the server will only use the `INPLACE`, `NOCOPY`, or `INSTANT` algorithms. Operations that only support the `COPY` algorithm will be rejected.
NOCOPY	In this mode, the server will only use the `NOCOPY` or `INSTANT` algorithms. Operations that only support the `COPY` or `INPLACE` algorithms will be rejected.
INSTANT	In this mode, the server will only use the `INSTANT` algorithm. Operations that only support the `COPY`, `INPLACE`, or `NOCOPY` algorithms will be rejected.

PARAMETERS

Command-line	--alter_algorithm[={DEFAULT\|COPY\|INPLACE\|NOCOPY\|INSTANT}]
Configuration file	Supported
Dynamic	Yes
Scope	Global, Session
Data Type	ENUM (DEFAULT, COPY, INPLACE, NOCOPY, INSTANT)
Default Value	DEFAULT

SKYSQL

Cloud	SkySQL Topology	ES Ver	Default
AWS	Multi-Node Analytics	10.6	DEFAULT
	Single Node Analytics	10.6	DEFAULT
	Distributed Transactions	10.5	Not present
	Distributed Transactions	10.6	Not present
	Replicated Transactions	10.4	DEFAULT
		10.5	DEFAULT
		10.6	DEFAULT
	Single Node Transactions	10.4	DEFAULT
		10.5	DEFAULT
		10.6	DEFAULT
GCP	Multi-Node Analytics	10.6	DEFAULT
	Single Node Analytics	10.6	DEFAULT
	Distributed Transactions	10.5	Not present
	Distributed Transactions	10.6	Not present
	Replicated Transactions	10.4	DEFAULT
		10.5	DEFAULT
		10.6	DEFAULT
	Single Node Transactions	10.4	DEFAULT
		10.5	DEFAULT
		10.6	DEFAULT

Cloud	SkySQL Topology	ES Ver	Default
AWS	ColumnStore Data Warehouse	10.6	DEFAULT
	Xpand Distributed SQL	10.5	Not present
	Xpand Distributed SQL	10.6	Not present
	Enterprise Server With Replica(s)	10.4	DEFAULT
		10.5	DEFAULT
		10.6	DEFAULT
	Enterprise Server Single Node	10.4	DEFAULT
		10.5	DEFAULT
		10.6	DEFAULT
GCP	ColumnStore Data Warehouse	10.6	DEFAULT
	Xpand Distributed SQL	10.5	Not present
	Xpand Distributed SQL	10.6	Not present
	Enterprise Server With Replica(s)	10.4	DEFAULT
		10.5	DEFAULT
		10.6	DEFAULT
	Enterprise Server Single Node	10.4	DEFAULT
		10.5	DEFAULT
		10.6	DEFAULT

PRIVILEGES

The alter_algorithm system variable requires the SUPER privilege to dynamically set its global value at runtime.

The alter_algorithm system variable does not require any special privilege to dynamically set its session value at runtime.

EXAMPLES

Disambiguating Default Values

When you set the session value of the alter_algorithm system variable, the value DEFAULT has two different meanings, depending on whether the value is quoted.

Let's say that the value is quoted:

SET SESSION alter_algorithm='DEFAULT';

SHOW SESSION VARIABLES LIKE 'alter_algorithm';

+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| alter_algorithm | DEFAULT |
+-----------------+---------+

This sets the session value to the literal value of DEFAULT, which tells the server to use the default alter algorithm for all ALTER TABLE operations.

Let's say that the value is not quoted:

SET SESSION alter_algorithm=DEFAULT;

SHOW GLOBAL VARIABLES LIKE 'alter_algorithm';

+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| alter_algorithm | INPLACE |
+-----------------+---------+

SHOW SESSION VARIABLES LIKE 'alter_algorithm';

+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| alter_algorithm | INPLACE |
+-----------------+---------+

This sets the session value to the global value.

In contrast, when you set the global value of the alter_algorithm system variable, the value DEFAULT only has a single meaning:

SET GLOBAL alter_algorithm='DEFAULT';

SHOW GLOBAL VARIABLES LIKE 'alter_algorithm';

+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| alter_algorithm | DEFAULT |
+-----------------+---------+

SET GLOBAL alter_algorithm=DEFAULT;

SHOW GLOBAL VARIABLES LIKE 'alter_algorithm';

+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| alter_algorithm | DEFAULT |
+-----------------+---------+

Dynamically Resetting Global Value to Default

The global value of the alter_algorithm system variable can be dynamically reset to the default value by setting it to DEFAULT:

SET GLOBAL alter_algorithm='INPLACE';

SHOW GLOBAL VARIABLES LIKE 'alter_algorithm';

+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| alter_algorithm | INPLACE |
+-----------------+---------+

SET GLOBAL alter_algorithm=DEFAULT;

SHOW GLOBAL VARIABLES LIKE 'alter_algorithm';

+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| alter_algorithm | DEFAULT |
+-----------------+---------+

Resetting Session Value to Global Value

The session value of the alter_algorithm system variable can be reset to the global value by setting it to DEFAULT:

SET SESSION alter_algorithm='INPLACE';

SHOW GLOBAL VARIABLES LIKE 'alter_algorithm';

+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| alter_algorithm | DEFAULT |
+-----------------+---------+

SHOW SESSION VARIABLES LIKE 'alter_algorithm';

+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| alter_algorithm | INPLACE |
+-----------------+---------+

SET SESSION alter_algorithm=DEFAULT;

SHOW SESSION VARIABLES LIKE 'alter_algorithm';

+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| alter_algorithm | DEFAULT |
+-----------------+---------+

Invalid Value for Variable

If a user tries to set the alter_algorithm system variable to an invalid value, then the operation will fail with the ER_WRONG_VALUE_FOR_VAR error code:

SET GLOBAL alter_algorithm=-1;

ERROR 1231 (42000): Variable 'alter_algorithm' can't be set to the value of '-1'

Invalid Type for Variable

If a user tries to set the alter_algorithm system variable to an invalid argument, then the operation will fail with the ER_WRONG_TYPE_FOR_VAR error code:

SET GLOBAL alter_algorithm=1.0;

ERROR 1232 (42000): Incorrect argument type to variable 'alter_algorithm'

CHANGE HISTORY

Release Series	History
10.6 Enterprise	Present starting in MariaDB Enterprise Server 10.6.4-1.
10.6 Community	Present starting in MariaDB Community Server 10.6.0.
10.5 Enterprise	Present starting in MariaDB Enterprise Server 10.5.3-1.
10.5 Community	Present starting in MariaDB Community Server 10.5.0.
10.4 Enterprise	Present starting in MariaDB Enterprise Server 10.4.6-1.
10.4 Community	Present starting in MariaDB Community Server 10.4.0.
10.3 Enterprise	Present starting in MariaDB Enterprise Server 10.3.16-1.
10.3 Community	Added in MariaDB Community Server 10.3.7.
10.2 Enterprise	Not present.
10.2 Community	Not present.

Release Series	History
10.6 Enterprise	Present starting in MariaDB Enterprise Server 10.6.4-1.
10.5 Enterprise	Present starting in MariaDB Enterprise Server 10.5.3-1.
10.4 Enterprise	Present starting in MariaDB Enterprise Server 10.4.6-1.