
an ALTER TABLE operation by setting the LOCK clause. The supported values are:

DEFAULT
-------

Acquire the least restrictive lock on the table that is supported for the
specific operation. Permit the maximum amount of concurrency that is supported
for the specific operation.

NONE
----

Acquire no lock on the table. Permit all concurrent DML. If this locking
strategy is not permitted for an operation, then an error is raised. From
MariaDB 11.2, ALTER TABLE can do most operations with ALGORITHM=COPY,
LOCK=NONE, that is, in most cases, unless the algorithm and lock level are
explicitly specified, ALTER TABLE will be performed using the COPY algorithm
while simultaneously allowing concurrent DML statements on the altered table.
If this is not desired, one can explicitly specify a different lock level or
set old_mode to LOCK_ALTER_TABLE_COPY that will make ALGORITHM=COPY use
LOCK=SHARED by default (but still allowing LOCK=NONE to be specified
explicitly).

SHARED
------

Acquire a read lock on the table. Permit read-only concurrent DML. If this
locking strategy is not permitted for an operation, then an error is raised.

EXCLUSIVE
---------

Acquire a write lock on the table. Do not permit concurrent DML.

Different storage engines support different locking strategies for different
operations. If a specific locking strategy is chosen for an ALTER TABLE
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 mariadb 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.

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

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/