An overview of supported online schema change operations in InnoDB, detailing which DDL statements can be performed without locking the table.
In MariaDB Enterprise Server, InnoDB supports many different schema change operations. Many of the operations can be performed online with concurrent DML using little or no locking.
InnoDB schema changes are performed using the following DDL statements:
InnoDB schema changes and online DDL are performed with a wide range of statements:
Each operation supports a subset of the following algorithms: INSTANT, NOCOPY, INPLACE, or COPY.
By default, InnoDB will use the most efficient algorithm supported by an operation. This behavior can be changed by using the ALGORITHM clause with the statement or by changing the value of the alter_algorithm system variable.
Each operation supports a subset of the following locking strategies: NONE, SHARED, or EXCLUSIVE.
By default, InnoDB will use the most permissive locking strategy supported by an operation. This behavior can be changed by using the LOCK clause with the statement.
Dropping a column
Yes
Reordering columns
Yes
Changing a column to NULL
Yes
Changing a column to NOT NULL
Yes
Adding a new ENUM option
Yes
Adding a new SET option
Yes
Adding system versioning
Yes
Removing system versioning
Yes
Setting a column's DEFAULT
Yes
Removing a column's DEFAULT
Yes
Adding a primary key
Yes
Dropping a primary key
Yes
Adding an index
Yes
Dropping an index
Yes
Adding a foreign key
Yes
Dropping a foreign key
Yes
Setting the next AUTO_INCREMENT value
Yes
Setting the row format
Yes
Setting the block size for the Compressed row format
Yes
Enabling page compression
Yes
Setting the page compression level
Yes
Disabling page compression
Yes
Enabling data-at-rest encryption
Yes
Setting the encryption key ID
Yes
Disabling data-at-rest encryption
Yes
Adding a constraint
Yes
Dropping a constraint
Yes
Rebuilding the table
Yes
Renaming the table
Yes
Operations support instant algorithm
Yes
Operations support no-copy algorithm
Yes
Operations support in-place algorithm
Yes
InnoDB Schema Changes with the INPLACE Algorithm
Adding a column
Yes
A detailed reference list of specific schema change operations (like adding columns or indexes) and their compatibility with INSTANT, INPLACE, and NOCOPY algorithms.
Changes in enterprise databases and applications are inevitable. Schema changes often block other workloads, incur unique production situations that cannot be fully simulated for testing, and may have unpredictable execution times.
MariaDB Enterprise Server includes the In-place ALTER functionality for the InnoDB storage engine, such that:
When possible, schema change operations are performed INPLACE, minimizing impact on other workloads.
When the alter_algorithm system variable is set to INPLACE, schema change operations will not run unless they can be performed INPLACE, minimizing the risk of unpredictable behavior.
Additional information is available .
Changes in enterprise databases and applications are inevitable. Schema changes often block other workloads, incur unique production situations that cannot be fully simulated for testing, and may have unpredictable execution times.
MariaDB Enterprise Server includes the Instant ALTER functionality for the InnoDB storage engine, such that:
When possible, schema change operations are performed INSTANT, minimizing impact on other workloads.
When the alter_algorithm system variable is set to INSTANT, schema change operations will not run unless they can be performed INSTANT, minimizing the risk of unpredictable behavior.
Additional information is available .
Changes in enterprise databases and applications are inevitable. Schema changes often block other workloads, incur unique production situations that cannot be fully simulated for testing, and may have unpredictable execution times.
MariaDB Enterprise Server includes the No-copy ALTER functionality for the InnoDB storage engine, such that:
When possible, schema change operations are performed NOCOPY, minimizing impact on other workloads.
When the alter_algorithm system variable is set to NOCOPY, schema change operations will not run unless they can be performed NOCOPY, minimizing the risk of unpredictable behavior.
Additional information is available .
This page is: Copyright © 2025 MariaDB. All rights reserved.