
operation, and that table's storage engine does not support that locking
strategy for that specific operation, then an error will be raised.

If the LOCK clause is not explicitly set, then the operation uses LOCK=DEFAULT.

ALTER ONLINE TABLE is equivalent to LOCK=NONE. Therefore, the ALTER ONLINE
TABLE statement can be used to ensure that your ALTER TABLE operation allows
all concurrent DML.

See InnoDB Online DDL Overview: LOCK for information on how the LOCK clause
affects InnoDB.

Progress Reporting
------------------

MariaDB provides progress reporting for ALTER TABLE statement for clients that
support the new progress reporting protocol. For example, if you were using
the mysql client, then the progress report might look like this::

ALTER TABLE test ENGINE=Aria;
Stage: 1 of 2 'copy to tmp table'    46% of stage

The progress report is also shown in the output of the SHOW PROCESSLIST
statement and in the contents of the information_schema.PROCESSLIST table.

See Progress Reporting for more information.

Aborting ALTER TABLE Operations
-------------------------------

If an ALTER TABLE operation is being performed and the connection is killed,
the changes will be rolled back in a controlled manner. The rollback can be a
slow operation as the time it takes is relative to how far the operation has
progressed.

MariaDB starting with 10.2.13
-----------------------------
Aborting ALTER TABLE ... ALGORITHM=COPY was made faster by removing excessive
undo logging (MDEV-11415). This significantly shortens the time it takes to
abort a running ALTER TABLE operation.

Atomic ALTER TABLE
------------------

MariaDB starting with 10.6.1
----------------------------
From MariaDB 10.6, ALTER TABLE is atomic for most engines, including InnoDB,
MyRocks, MyISAM and Aria (MDEV-25180). This means that if there is a crash
(server down or power outage) during an ALTER TABLE operation, after recovery,
either the old table and associated triggers and status will be intact, or the
new table will be active.

In older MariaDB versions one could get leftover #sql-alter..',
'#sql-backup..' or 'table_name.frm˝' files if the system crashed during the
ALTER TABLE operation.

See Atomic DDL for more information.

Replication
-----------

MariaDB starting with 10.8.1
----------------------------
Before MariaDB 10.8.1, ALTER TABLE got fully executed on the primary first,
and only then was it replicated and started executing on replicas. From
MariaDB 10.8.1, ALTER TABLE gains an option to replicate sooner and begin
executing on replicas when it merely starts executing on the primary, not when
it finishes. This way the replication lag caused by a heavy ALTER TABLE can be
completely eliminated (MDEV-11675).

Examples
--------

Adding a new column:

ALTER TABLE t1 ADD x INT;

Dropping a column:

ALTER TABLE t1 DROP x;

Modifying the type of a column:

ALTER TABLE t1 MODIFY x bigint unsigned;

Changing the name and type of a column:

ALTER TABLE t1 CHANGE a b bigint unsigned auto_increment;

Combining multiple clauses in a single ALTER TABLE statement, separated by
commas:

ALTER TABLE t1 DROP x, ADD x2 INT,  CHANGE y y2 INT;

Changing the storage engine and adding a comment:

ALTER TABLE t1 
 ENGINE = InnoDB
 COMMENT = 'First of three tables containing usage info';

Rebuilding the table (the previous example will also rebuild the table if it
was already InnoDB):

ALTER TABLE t1 FORCE;

Dropping an index:

ALTER TABLE rooms DROP INDEX u;

Adding a unique index:

ALTER TABLE rooms ADD UNIQUE INDEX u(room_number);

From MariaDB 10.5.3, adding a primary key for an application-time period table
with a WITHOUT OVERLAPS constraint:

ALTER TABLE rooms ADD PRIMARY KEY(room_number, p WITHOUT OVERLAPS);

From MariaDB 10.8.1, ALTER query can be replicated faster with the setting of


prior the ALTER query. Binlog would contain two event groups

| master-bin.000001 | 495 | Gtid              |         1 |         537 | GTID
0-1-2 START ALTER                                        |
| master-bin.000001 | 537 | Query             |         1 |         655 | use
`test`; alter table t add column b int, algorithm=inplace |
| master-bin.000001 | 655 | Gtid              |         1 |         700 | GTID
0-1-3 COMMIT ALTER id=2                                  |
| master-bin.000001 | 700 | Query             |         1 |         835 | use
`test`; alter table t add column b int, algorithm=inplace |

of which the first one gets delivered to replicas before ALTER is taken to
actual execution on the primary.

URL: https://mariadb.com/kb/en/alter-table/