Online Schema Changes in MariaDB: Safe Use of ALGORITHM=INPLACE and LOCK=NONE

Desired Outcome

Change a table definition—such as adding a nullable column or dropping a secondary index—without stopping application traffic. MariaDB's online DDL allows concurrent reads and writes during the operation when you specify ALGORITHM=INPLACE and LOCK=NONE. The session that runs the ALTER TABLE statement blocks until the change finishes, but other client sessions can continue inserting, updating, and deleting rows.

This guide assumes MariaDB 10.2 or later. Online DDL behavior varies by exact version and by the type of change, so confirm support for your specific operation in the MariaDB knowledge base before running it in production.

Prerequisites

• Storage engine: The table must use InnoDB. Online DDL is an InnoDB feature.
• Version: MariaDB 10.2+. Earlier versions do not support the same online DDL syntax.
• Privileges: The executing user needs ALTER, CREATE, and DROP privileges on the target table. No SUPER or RELOAD privilege is required just to run the ALTER.
• Disk space: ALGORITHM=INPLACE generally uses less temporary space than a full table copy, but index rebuilds and sort operations may still consume space in the temporary directory or data directory. Ensure the volume hosting your data directory has free space; monitor it during the operation.
• Replication awareness: If the server is a replication source, the ALTER statement is written to the binary log and replayed on replicas. Verify replica health after the change.

Do not assume every table qualifies. Some features—such as certain foreign key constraints, partitioned tables, or specific index types—can cause MariaDB to fall back to ALGORITHM=COPY, which rebuilds the table and holds writes longer. Test the exact ALTER on a staging copy first.

Focused Procedure

Run all SQL commands from a MariaDB client (for example, the mariadb or mysql command-line client) using an account that has the required privileges on the target database.

1. Inspect the current table

SHOW CREATE TABLE <database_name>.<table_name>\G

Confirm the engine is InnoDB and note the existing columns and indexes. This gives you a baseline definition to compare against later.

2. Capture a baseline row count

SELECT COUNT(*) FROM <database_name>.<table_name>;

Record the result. A matching count after the change is a basic sanity check, though it does not prove every row is intact.

3. Run the online ALTER

The following example adds a nullable VARCHAR column. Replace the table and column names with your own.

ALTER TABLE <database_name>.<table_name>
  ADD COLUMN phone_number VARCHAR(20) NULL,
  ALGORITHM=INPLACE,
  LOCK=NONE;

Expected behavior: The statement does not return until the schema change is complete. During that time, other sessions can continue to read and write the table. If MariaDB cannot perform the operation in place, it returns an error rather than silently switching to a copy algorithm, because ALGORITHM=INPLACE is an explicit request.

Risk: If the client timeout is shorter than the ALTER duration, the client may disconnect while the server continues the work. Set a generous timeout or run the ALTER from a session that can wait.

4. Monitor from a second session

Because the ALTER session is blocked, open a separate MariaDB client to watch progress.

SHOW ENGINE INNODB STATUS\G

Look for sections describing the active DDL operation. The output includes status information about InnoDB internals; you are checking that the operation is progressing and that no error messages appear. If the server is under heavy write load, the operation may pause while the online DDL log is applied.

5. Verify the new definition

SHOW CREATE TABLE <database_name>.<table_name>\G

Confirm the column or index appears exactly as intended.

6. Check data integrity

SELECT COUNT(*) FROM <database_name>.<table_name>;

Compare this with the baseline. If the counts differ, stop and investigate before allowing traffic. For a stronger check on smaller tables, you can also run CHECKSUM TABLE <database_name>.<table_name>; before and after, but be aware that checksum computation can be expensive on large tables.

7. Verify replicas (if applicable)

If this server replicates to one or more replicas, the ALTER is replicated as part of the binary log. On each replica, check that the change has been applied and that the replication SQL thread is healthy.

On MariaDB 10.5 and later, you can use:

SHOW REPLICA STATUS\G

On earlier versions, use:

SHOW SLAVE STATUS\G

In the output, verify that the SQL thread is running (field names differ by version: Replica_SQL_Running or Slave_SQL_Running) and that Last_SQL_Error is empty. If an error is present, the replica has not applied the ALTER and must be repaired before it can diverge safely.

Expected Checks

• SHOW CREATE TABLE shows the new schema.
• The row count matches the pre-ALTER baseline (or a checksum matches, if used).
• SHOW ENGINE INNODB STATUS shows no ongoing errors related to the DDL.
• On replicas, the SQL thread is running and Last_SQL_Error is blank.
• Application-level queries against the new column or index return expected results.

Recovery Options

An ALTER TABLE changes state. If the statement is interrupted—by a kill signal, server crash, or disk-full condition—MariaDB attempts to roll back the incomplete DDL during crash recovery. You do not need to manually delete temporary files from the data directory, and doing so can corrupt InnoDB.

1. If the ALTER fails or is interrupted: Check the MariaDB error log for details. If the server stopped unexpectedly, restart it and allow crash recovery to finish. InnoDB will roll back the uncommitted DDL.
2. If disk space was exhausted: Free space on the volume hosting the data directory and temporary directory, then retry the ALTER after recovery completes.
3. If the ALTER succeeded but the change is wrong: There is no automatic undo. Issue a compensating ALTER TABLE (for example, DROP COLUMN) or restore the table from a backup taken before the change.

Limitations and Considerations

• Not all operations are INPLACE: Some changes—such as adding certain FULLTEXT or spatial indexes, or changing column order—may require a table rebuild. Support varies by MariaDB version. Always test on a copy.
• Brief metadata locks: LOCK=NONE minimizes blocking, but a short exclusive lock may still be taken at the very beginning or end of the operation to swap metadata.
• Online DDL log limits: Heavy concurrent DML can fill the online DDL log. If the log exceeds innodb_online_alter_log_max_size, the ALTER fails. Monitor this variable if your table receives constant writes.
• Row counts are not proof: A matching COUNT(*) does not detect row-level corruption or partial updates. Use application-level validation or checksums where data integrity is critical.
• Version differences: Replication status commands and field names changed around MariaDB 10.5. Use the syntax that matches your server version.