Modify table structures. This guide covers adding columns, changing data types, managing indexes, and other schema changes for existing tables.
Generally speaking, “online” refers to the ability to update the table schema without blocking concurrent DML for the duration of the copy.
Suppose we have a table item featuring columns (id SERIAL, name TEXT).
Now, it can be ALTERed by adding a TIMESTAMP field in one connection, while having a concurrent connection inserting a new row.
If the INSERT statement begins its execution after is issued, it will not be blocked and will proceed normally, and thus it may finish before ALTER TABLE.
ALTER TABLE always allows concurrent statements. If the LOCK=NONE locking strategy is chosen, it will allow concurrent modifications (INSERT/DELETE/UPDATE). Namely, LOCK=NONE was supported by InnoDB and the Partition engine when ALGORITHM=NOCOPY is chosen and is a default locking strategy when available.
With the new release, LOCK=NONE support is added for ALGORITHM=COPY, thus almost all ALTER TABLE operations (a few exceptions are given below) now allow concurrent DML.
LOCK=NONE adds one extra step to the copy algorithm. It introduces a new internal entity, the online change buffer.
First, a new table is created using the old table content as it was at the beginning of the ALTER TABLE statement. For InnoDB and other transactional engines it means copying in REPEATABLE READ isolation mode.
In the meantime, every concurrent change is written in the old table and is duplicated to the online change buffer.
Then, ALTER TABLE applies the changes accumulated in that
Note that while all copying and online changes application happens without blocking concurrent DML, in the end it acquires an EXCLUSIVE lock on the table for a short amount of time, to synchronize with all parallel operations that are not yet finished.
The requirement for an engine is to allow concurrent writes, while another connection can read and have a repeatable-read behavior.
MyISAM is capable of concurrent and also supports concurrent inserts while reading: we can’t say it supports transaction isolation layer, but the newly inserted data is not seen by the readers until the end of the statement, so it satisfies the criterion, however it is not transactional.
An attempt to invoke any statement that can update or delete a row will be blocked until the end of ALTER TABLE (or will evaluate before ALTER TABLE acquires a lock, if it started earlier).
InnoDB supports REPEATABLE READ isolation layer and allows concurrent writes, so it is also supported by Online schema change, broadly enhancing its online ability represented by INPLACE and INSTANT algorithms. By default, an engine-native algorithm will be chosen whenever possible. If InnoDB will not be capable of holding the schema change natively, then, if possible, a COPY algorithm with Online schema change will be applied. A more detailed comparison follows below.
MariaDB ColumnStore does not support REPEATABLE READ isolation layer, therefore it cannot support online schema change.
LOCK=NONE is naturally disabled for engines like , as well as for SEQUENCE engine, and sequences, and for read-only engines like S3.
CONNECT cannot read concurrently to writes, so it is also not capable of making ALTER TABLE online.
SPIDER storage engine does not really do a copy, but rather just reassigns a table with a new metadata.
Everything that can be done by InnoDB’s INPLACE, can be done by online COPY, except that tables with foreign keys with cascade operations are not supported. See the limitations section.
Changing the column’s data type is now possible.
A column can be modified too NOT NULL.
A column with non-constant DEFAULT value can be added.
A STORED
We have come across a number of limitations apart from the engine support, while we were testing. Some of them just lack a proper implementation, and some are also theoretically infeasible without relaxing the rules we have defined. If you feel that some of these limitations affect you, please leave a comment and tell us about it.
Now, a complete list of limitations we are aware of follows:
Adding an column is forbidden. Same applies to DEFAULT (…NEXTVAL(…)). We found that we can end up with the undefined behavior in this case, depending on when the concurrent DML is evaluated. Changing an existing column to become AUTO_INCREMENT is allowed, though. The two following conditions should be met:
A column should be NOT NULL in the old schema
NO_AUTO_VALUE_ON_ZERO
Online copy is the default mode whenever NOCOPY does not apply. In case of any problem with it, it can be disabled by specifying LOCK=SHARED to force the usual COPY algorithm.
To better support existing workflows, a new old_mode flag was also added. The following statement disables online copy by default:
It can still be forced to use the new feature under this mode by explicitly specifying LOCK=NONE in the ALTER TABLE statement.
Server-wide online schema change expands MariaDB Server’s capability for the LOCK=NONE approach broadly. The change is made in the core Server operation, which interferes with many components, so it took quite a while to get our approach right. This is still an early version of the feature so please note the limitations that we outlined above.
A CHECK constraint can be added.
PRIMARY KEY can be dropped.
No key is required in the resulting table.
A table can be changed to partitioned, or otherwise, partitioning can be removed.
SYSTEM VERSIONING can be added.
AUTO_INCREMENT can be added to the existing column but see limitations.
At least one UNIQUE NOT NULL key should be in the old schema, and it should remain unchanged.
Support for ALTER IGNORE TABLE is not implemented. This operation can make the altered table have fewer rows than the original one (because duplicates are skipped), and the online copy algorithm doesn’t support operations that change the number of rows in the table.
ALTER TABLE DROP SYSTEM VERSIONING is not supported either, for the similar reason.
Tables with foreign keys with CASCADE/SET NULL/SET DEFAULT operations can’t go online under ALGORITHM=COPY — similar to disallowing CHECK constraints and stored generated columns, cascade operations are done internally by the storage engine, so they bypass the online changes buffer. However, most operations are allowed by InnoDB’s INPLACE algorithm. Again, for every DDL request the most optimal algorithm will be chosen automatically.
Transaction-versioned tables can also be only changed “online” under INPLACE/INSTANT ALTER TABLE algorithms.
All the constraints (CHECK, UNIQUE, FOREIGN KEY) are evaluated for each row change that is applied from the online change buffer. This means that all the changes that are made during ALTER TABLE’s main phase should not violate the final table schema at any point in time. As always, the checks can be disabled by setting check_constraint_checks and FOREIGN_KEY_CHECKS to OFF.
ALTER TABLE … ORDER BY cannot be supported, because changes from the buffer are applied at the end and they might break the strict ordering of rows.
It is not available in embedded MariaDB Server due to replication libraries that are not present in the embedded version.
Online copy is not the default mode.
ALTER TABLE items
ADD ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP; # Start ALTER TABLEINSERT INTO items(name) VALUES (“New item”);set old_mode= LOCK_ALTER_TABLE_COPY;Modify table structures. This guide covers adding columns, changing data types, managing indexes, and other schema changes for existing tables.
ALTER TABLE enables you to change the structure of an existing table. For example, you can add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself. You can also change the comment for the table and the storage engine of the table.
If another connection is using the table, a is active, and this statement will wait until the lock is released. This is also true for non-transactional tables.
When adding a UNIQUE index on a column (or a set of columns) which have duplicated values, an error will be produced and the statement will be stopped. To suppress the error and force the creation of UNIQUE indexes, discarding duplicates, the option can be specified. This can be useful if a column (or a set of columns) should be UNIQUE but it contains duplicate values; however, this technique provides no control on which rows are preserved and which are deleted. Also, note that IGNORE is accepted but ignored in ALTER TABLE ... EXCHANGE PARTITION statements.
This statement can also be used to rename a table. For details see .
When an index is created, the storage engine may use a configurable buffer in the process. Incrementing the buffer speeds up the index creation. and allocate a buffer whose size is defined by or , also used for . allocates three buffers whose size is defined by .
Executing the ALTER TABLE statement generally requires at least the privilege for the table or the database. If you are renaming a table, this also requires the , and privileges for the table or the database.
Online DDL is supported with the and clauses.
See for more information on online DDL with .
ALTER ONLINE TABLE also works for partitioned tables.
Online ALTER TABLE is available by executing the following:
See the alter specification for more information.
Set the lock wait timeout. See .
The IF EXISTS and IF NOT EXISTS clauses are available for the following clauses:
When IF EXISTS and IF NOT EXISTS are used in clauses, queries will not report errors when the condition is triggered for that clause. A warning with the same message text will be issued and the ALTER will move on to the next clause in the statement (or end if finished).
If this is directive is used after ALTER ... TABLE, you don't get an error if the table doesn't exist. Example:
See for information about column definitions.
See for information about index definitions.
The and statements can also be used to add or remove an index.
See for details on setting the .
See for information about table options.
Adds a column to the table. The syntax is the same as in . If you are using IF NOT_EXISTS the column will not be added if it was not there already. This is very useful when doing scripts to modify tables.
The FIRST and AFTER clauses affect the physical order of columns in the datafile. Use FIRST to add a column in the first (leftmost) position, or AFTER followed by a column name to add the new column in any other position. Note that, nowadays, the physical position of a column is usually irrelevant.
See also .
Drops the column from the table. If you are using IF EXISTS you will not get an error if the column didn't exist. If the column is part of any index, the column will be dropped from them, except if you add a new column with identical name at the same time. The index will be dropped if all columns from the index were dropped. If the column was used in a view or trigger, you will get an error next time the view or trigger is accessed. Dropping a column that is part of a multi-column UNIQUE constraint is not permitted. For example:
The reason is that dropping column a would result in the new constraint that all values in column b be unique. In order to drop the column, an explicit DROP PRIMARY KEY and ADD PRIMARY KEY would be required.
MariaDB supports instant DROP COLUMN. DROP COLUMN of an indexed column would imply (and in the case of a non-UNIQUE multi-column index, possibly ADD INDEX). These will not be allowed with , but unlike prior versions, they can be allowed with
RESTRICT and CASCADE are allowed to make porting from other database systems easier. In MariaDB, they do nothing.
Allows you to modify the type of a column. The column will be at the same place as the original column and all indexes on the column will be kept. Note that when modifying column, you should specify all attributes for the new column.
Works like MODIFY COLUMN except that you can also change the name of the column. The column will be at the same place as the original column and all index on the column will be kept.
This lets you change column options.
{% tabs %} {% tab title="Current" %} You can rename an index using the RENAME INDEX (or RENAME KEY) syntax, for example:
{% endtab %}
{% tab title="< 10.5.3" %} RENAME INDEX/KEY is not available. {% endtab %} {% endtabs %}
{% tabs %} {% tab title="Current" %} You can rename a column using the RENAME COLUMN syntax, for example:
{% endtab %}
{% tab title="< 10.5.3" %} RENAME COLUMN is not available. {% endtab %} {% endtabs %}
Adds a primary key. For PRIMARY KEY indexes, you can specify a name for the index, but it is silently ignored, and the name of the index is always PRIMARY. See for more information.
Drops a primary key. For PRIMARY KEY indexes, you can specify a name for the index, but it is silently ignored, and the name of the index is always PRIMARY. See for more information.
Adds a foreign key. For FOREIGN KEY indexes, a reference definition must be provided. For FOREIGN KEY indexes, you can specify a name for the constraint, using the CONSTRAINT keyword. That name will be used in error messages.
First, you have to specify the name of the target (parent) table and a column or a column list which must be indexed and whose values must match to the foreign key's values. The MATCH clause is accepted to improve the compatibility with other DBMS's, but has no meaning in MariaDB. The ON DELETE and ON UPDATE clauses specify what must be done when a DELETE (or a REPLACE) statements attempts to delete a referenced row from the parent table, and when an UPDATE statement attempts to modify the referenced foreign key columns in a parent table row, respectively. The following options are allowed:
RESTRICT: The delete/update operation is not performed. The statement terminates with a 1451 error (SQLSTATE '2300').
NO ACTION: Synonym for RESTRICT.
CASCADE: The delete/update operation is performed in both tables.
If either clause is omitted, the default behavior for the omitted clause is RESTRICT.
See for more information.
Drops a foreign key. See for more information.
Adds a plain index. Plain indexes are regular indexes that are not unique, and are not acting as a primary key or a foreign key. They are also not the "specialized" FULLTEXT or SPATIAL indexes. For limits on InnoDB indexes, see . See for more information.
Drops a plain index. Plain indexes are regular indexes that are not unique, and are not acting as a primary key or a foreign key. They are also not the "specialized" FULLTEXT or SPATIAL indexes. See for more information.
Adds a unique index. The UNIQUE keyword means that the index will not accept duplicated values, except for NULLs. An error will raise if you try to insert duplicate values in a UNIQUE index. For UNIQUE indexes, you can specify a name for the constraint, using the CONSTRAINT keyword. That name will be used in error messages. See for more information.
Drops a unique index. The UNIQUE keyword means that the index will not accept duplicated values, except for NULLs. An error will raise if you try to insert duplicate values in a UNIQUE index. For UNIQUE indexes, you can specify a name for the constraint, using the CONSTRAINT keyword. That name will be used in error messages. See for more information.
Adds a FULLTEXT index. See for more information.
Drops a FULLTEXT index. See for more information.
Adds a SPATIAL index. See for more information.
Drops a SPATIAL index. See for more information.
DISABLE KEYS disables all non unique keys for the table for storage engines that support this (at least MyISAM and Aria). This can be used to into empty tables. ENABLE KEYS enables all disabled keys.
Renames a table. See also .
Modifies the table adding a on a particular column or columns.
Before a row is inserted or updated, all constraints are evaluated in the order they are defined. If any constraint fails, then the row will not be updated. One can use most deterministic functions in a constraint, including .
The constraint_name is optional. If you don't provide one in the ALTER TABLE statement, MariaDB auto-generates a name for you. This is done so that you can remove it later using clause.
You can disable all constraint expression checks by setting the variable to OFF. You may find this useful when loading a table that violates some constraints that you want to later find and fix in SQL.
To view constraints on a table, query :
DROP CONSTRAINT for UNIQUE and FOREIGN KEY
and DROP CONSTRAINT for CHECK constraints were introduced in an earlier version of MariaDB.
Modifies the table, removing the given constraint.
When you add a constraint to a table, whether through a or statement, you can either set a constraint_name yourself, or allow MariaDB to auto-generate one for you. To view constraints on a table, query . For instance,
To remove a constraint from the table, issue an ALTER TABLE...DROP CONSTRAINT statement. For example:
Adds system versioning. See .
Drops system versioning. See .
See , or .
ALTER TABLE ... FORCE forces MariaDB to rebuild the table.
With InnoDB, the table rebuild only reclaims unused space (i.e. the space previously used for deleted rows) if the system variable is set to ON (the default). If the system variable is OFF, the space will not be reclaimed, but it will be re-used for new data that's later added.
The rebuild may fail if conditions are violated due to a change in the . For example:
See for details.
See for details.
See for details.
Reduces the number of HASH or KEY partitions in a table. See .
{% tabs %} {% tab title="Current" %} CONVERT PARTITION can be used to remove a partition from a table and make this an ordinary table. For example:
CONVERT TABLE will take an existing table and move this to another table as its own partition with a specified . For example the following moves normal_table to a partition of partitioned_table with a definition that its values, based on the PARTITION BY of the partitioned_table, are less than 12345.
The optional [{WITH | WITHOUT} VALIDATION] is permitted.
See for more details. {% endtab %}
{% tab title="< 11.4" %} CONVERT PARTITION can be used to remove a partition from a table and make this an ordinary table. For example:
CONVERT TABLE will take an existing table and move this to another table as its own partition with a specified . For example the following moves normal_table to a partition of partitioned_table with a definition that its values, based on the PARTITION BY of the partitioned_table, are less than 12345.
The optional clause [{WITH | WITHOUT} VALIDATION] is not available.
See for more details. {% endtab %}
{% tab title="< 10.7.1" %} CONVERT PARTITION and CONVERT TABLE are not available. {% endtab %} {% endtabs %}
Used to drop specific partitions (and discard all data within the specified partitions) for and partitions. See .
{% tabs %} {% tab title="Current" %} This clause is used to exchange the contents of a partition with another table. This is performed by swapping the tablespaces of the partition with the other table.
The optional [{WITH | WITHOUT} VALIDATION] is permitted.
See for more details.
See also . {% endtab %}
{% tab title="< 11.4" %} This clause is used to exchange the contents of a partition with another table. This is performed by swapping the tablespaces of the partition with the other table.
The optional [{WITH | WITHOUT} VALIDATION] is not permitted.
See for more details.
See also . {% endtab %} {% endtabs %}
See for details.
See .
See .
See .
This is used to discard an InnoDB table's tablespace.
See for more information.
This is used to import an InnoDB table's tablespace. The tablespace should have been copied from its original server after executing .
See for more information.
ALTER TABLE ... IMPORT only applies to InnoDB tables. Most other popular storage engines, such as Aria and MyISAM, will recognize their data files as soon as they've been placed in the proper directory under the datadir, and no special DDL is required to import them.
The ALTER TABLE statement supports the ALGORITHM clause. This clause is one of the clauses that is used to implement online DDL. ALTER TABLE supports several different algorithms. An algorithm can be explicitly chosen for an ALTER TABLE operation by setting the ALGORITHM clause. The supported values are:
ALGORITHM=DEFAULT - This implies the default behavior for the specific statement, such as if no ALGORITHM clause is specified.
ALGORITHM=COPY
ALGORITHM=INPLACE
See for information on how the ALGORITHM clause affects InnoDB.
The default behavior, which occurs if ALGORITHM=DEFAULT is specified, or if ALGORITHM is not specified at all, usually only makes a copy if the operation doesn't support being done in-place at all. In this case, the most efficient available algorithm will usually be used.
The system variable is deprecated. Instead, the system variable defines the default algorithm for ALTER TABLE operations. This was removed in for the following reasons:
alter_algorithm was introduced as a replacement for the old_alter_table that was used to force the usage of the original alter table algorithm (copy) in cases where the new alter algorithm did not work. The new option was added as a way to force the usage of a specific algorithm when it should instead have made it possible to disable algorithms that would not work for some reason.
alter_algorithm introduced some cases where ALTER TABLE would not work without specifying the ALGORITHM=XXX option together with ALTER TABLE.
Having different values of alter_algorithm on the primary and replica could cause replicas to stop unexpectedly.
ALGORITHM=COPY is the name for the original algorithm from early MariaDB versions.
When ALGORITHM=COPY is set, MariaDB essentially does the following operations:
This algorithm is very inefficient, but it is generic, so it works for all storage engines.
If ALGORITHM=COPY is specified, then the copy algorithm will be used even if it is not necessary. This can result in a lengthy table copy. If multiple operations are required that each require the table to be rebuilt, then it is best to specify all operations in a single statement, so that the table is only rebuilt once.
{% tabs %} {% tab title="Current" %} ALTER TABLE can perform most operations with ALGORITHM=COPY, LOCK=NONE. See . {% endtab %}
{% tab title="< 11.2" %} ALTER TABLE cannot perform operations with ALGORITHM=COPY, LOCK=NONE. {% endtab %} {% endtabs %}
ALGORITHM=COPY can be incredibly slow, because the whole table has to be copied and rebuilt. ALGORITHM=INPLACE was introduced as a way to avoid this by performing operations in-place and avoiding the table copy and rebuild, when possible.
When ALGORITHM=INPLACE is set, the underlying storage engine uses optimizations to perform the operation while avoiding the table copy and rebuild. However, INPLACE is a bit of a misnomer, since some operations may still require the table to be rebuilt for some storage engines. Regardless, several operations can be performed without a full copy of the table for some storage engines.
A more accurate name would have been ALGORITHM=ENGINE, where ENGINE refers to an "engine-specific" algorithm.
If an operation supports ALGORITHM=INPLACE, then it can be performed using optimizations by the underlying storage engine, but it may rebuilt.
See for more.
ALGORITHM=INPLACE can sometimes be surprisingly slow in instances where it has to rebuild the clustered index, because when the clustered index has to be rebuilt, the whole table has to be rebuilt. ALGORITHM=NOCOPY was introduced as a way to avoid this.
If an ALTER TABLE operation supports ALGORITHM=NOCOPY, then it can be performed without rebuilding the clustered index.
If ALGORITHM=NOCOPY is specified for an ALTER TABLE operation that does not support ALGORITHM=NOCOPY, then an error will be raised. In this case, raising an error is preferable, if the alternative is for the operation to rebuild the clustered index, and perform unexpectedly slowly.
See for more.
ALGORITHM=INPLACE can sometimes be surprisingly slow in instances where it has to modify data files. ALGORITHM=INSTANT was introduced as a way to avoid this.
If an ALTER TABLE operation supports ALGORITHM=INSTANT, then it can be performed without modifying any data files.
If ALGORITHM=INSTANT is specified for an ALTER TABLE operation that does not support ALGORITHM=INSTANT, then an error will be raised. In this case, raising an error is preferable, if the alternative is for the operation to modify data files, and perform unexpectedly slowly.
See for more.
The ALTER TABLE statement supports the LOCK clause. This clause is one of the clauses that is used to implement online DDL. ALTER TABLE supports several different locking strategies. A locking strategy can be explicitly chosen for an ALTER TABLE operation by setting the LOCK clause. The supported values are:
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.
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 , 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 that will make ALGORITHM=COPY use LOCK=SHARED by default (but still allowing LOCK=NONE to be specified explicitly).
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.
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.
is equivalent to LOCK=NONE. Therefore, the statement can be used to ensure that your ALTER TABLE operation allows all concurrent DML.
See for information on how the LOCK clause affects InnoDB.
See page for meaning of the index options.
MariaDB provides progress reporting for ALTER TABLE statement for clients that support the new progress reporting protocol. For example, if you were using the client, then the progress report might look like this::
The progress report is also shown in the output of the statement and in the contents of the table.
See for more information.
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 by removing excessive undo logging (). This significantly shortened the time it takes to abort a running ALTER TABLE operation, compared with earlier releases.
{% tabs %} {% tab title="Current" %} ALTER TABLE is atomic for most engines, including InnoDB, MyRocks, MyISAM and Aria (). 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 for more information. {% endtab %}
{% tab title="< 10.6.1" %} Atomic ALTER TABLE is not available. {% endtab %} {% endtabs %}
{% tabs %} {% tab title="Current" %} ALTER TABLE got fully executed on the primary first, and only then was it replicated and started executing on replicas. was added to replicate sooner and begin executing on replicas, directly when it starts executing on the primary, not when it finishes. This way the replication lag caused by a heavy ALTER TABLE can be completely eliminated (). {% endtab %}
{% tab title="< 10.8.1" %} The option is not available. {% endtab %} {% endtabs %}
Adding a new column:
Dropping a column:
Modifying the type of a column:
Changing the name and type of a column:
Combining multiple clauses in a single ALTER TABLE statement, separated by commas:
Changing the storage engine and adding a comment:
Rebuilding the table (the previous example will also rebuild the table if it was already InnoDB):
Dropping an index:
Adding a unique index:
Adding a primary key for an with a constraint:
{% tabs %} {% tab title="Current" %} An ALTER query can be replicated faster with this statement, which must be run before the ALTER statement:
Binlog would contain two event groups, of which the first one gets delivered to replicas before ALTER is taken to actual execution on the primary:
{% endtab %}
{% tab title="< 10.8.1" %} This statement is not available:
This page is licensed: GPLv2, originally from
SET NULL: The update or delete goes ahead in the parent table, and the corresponding foreign key fields in the child table are set to NULL. (They must not be defined as NOT NULL for this to succeed).
SET DEFAULT: This option is implemented only for the legacy PBXT storage engine, which is disabled by default and no longer maintained. It sets the child table's foreign key fields to their DEFAULT values when the referenced parent table key entries are updated or deleted.
ALGORITHM=NOCOPY
ALGORITHM=INSTANT
As part of MDEV-33449 "improving repair of tables" it became clear that alter-algorithm made it harder to provide a better and more consistent ALTER TABLE FORCE and REPAIR TABLE, and it would be better to remove it.
ALTER [ONLINE] [IGNORE] TABLE [IF EXISTS] tbl_name
[WAIT n | NOWAIT]
alter_specification [, alter_specification] ...
alter_specification:
table_option ...
| ADD [COLUMN] [IF NOT EXISTS] col_name column_definition
[FIRST | AFTER col_name ]
| ADD [COLUMN] [IF NOT EXISTS] (col_name column_definition, ...)
| ADD {INDEX|KEY} [IF NOT EXISTS] [index_name]
[index_type] (index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]] PRIMARY KEY [IF NOT EXISTS]
[index_type] (index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]]
UNIQUE [INDEX|KEY] [IF NOT EXISTS] [index_name]
[index_type] (index_col_name,...) [index_option] ...
| ADD FULLTEXT [INDEX|KEY] [IF NOT EXISTS [index_name]
(index_col_name,...) [index_option] ...
| ADD SPATIAL [INDEX|KEY] [IF NOT EXISTS [index_name]
(index_col_name,...) [index_option] ...
| ADD VECTOR [INDEX|KEY] [IF NOT EXISTS [index_name]
(index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]]
FOREIGN KEY [IF NOT EXISTS] [index_name] (index_col_name,...)
reference_definition
| ADD PERIOD FOR [time_period_name|SYSTEM_TIME] (start_column_name, end_column_name)
| ALTER [COLUMN] col_name SET DEFAULT literal | (expression)
| ALTER [COLUMN] col_name DROP DEFAULT
| ALTER {INDEX|KEY} [IF EXISTS] index_name [NOT] IGNORED
| CHANGE [COLUMN] [IF EXISTS] old_col_name new_col_name column_definition
[FIRST|AFTER col_name]
| MODIFY [COLUMN] [IF EXISTS] col_name column_definition
[FIRST | AFTER col_name]
| DROP [COLUMN] [IF EXISTS] col_name [RESTRICT|CASCADE]
| DROP [CONSTRAINT] PRIMARY KEY
| DROP {INDEX|KEY} [IF EXISTS] index_name
| DROP FOREIGN KEY [IF EXISTS] fk_symbol
| DROP CONSTRAINT [IF EXISTS] constraint_name
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO] new_tbl_name
| ORDER BY col_name [, col_name] ...
| RENAME COLUMN [IF EXISTS] old_col_name TO new_col_name
| RENAME {INDEX|KEY} old_index_name TO new_index_name
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
| DISCARD TABLESPACE
| IMPORT TABLESPACE
| ALGORITHM [=] {DEFAULT|INPLACE|COPY|NOCOPY|INSTANT}
| LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
| FORCE
| partition_options
| CONVERT TABLE normal_table TO partition_definition [{WITH | WITHOUT} VALIDATION]
| CONVERT PARTITION partition_name TO TABLE tbl_name
| ADD PARTITION [IF NOT EXISTS] (partition_definition)
| DROP PARTITION [IF EXISTS] partition_names
| TRUNCATE PARTITION partition_names
| COALESCE PARTITION number
| REORGANIZE PARTITION [partition_names INTO (partition_definitions)]
| ANALYZE PARTITION partition_names
| CHECK PARTITION partition_names
| OPTIMIZE PARTITION partition_names
| REBUILD PARTITION partition_names
| REPAIR PARTITION partition_names
| EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH | WITHOUT} VALIDATION]
| REMOVE PARTITIONING
| ADD SYSTEM VERSIONING
| DROP SYSTEM VERSIONING
index_col_name:
col_name [(length)] [ASC | DESC]
index_type:
USING {BTREE | HASH | RTREE}
index_option:
[ KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| VISIBLE
| COMMENT 'string'
| CLUSTERING={YES| NO} ]
[ IGNORED | NOT IGNORED ]
table_option [[,] table_option] ...
ALTER ONLINE TABLE ...;
</code></pre>
This statement is equivalent to the following:
```sql
ALTER TABLE ... LOCK=NONE;ADD COLUMN [IF NOT EXISTS]
ADD INDEX [IF NOT EXISTS]
ADD FOREIGN KEY [IF NOT EXISTS]
ADD PARTITION [IF NOT EXISTS]
CREATE INDEX [IF NOT EXISTS]
DROP COLUMN [IF EXISTS]
DROP INDEX [IF EXISTS]
DROP FOREIGN KEY [IF EXISTS]
DROP PARTITION [IF EXISTS]
CHANGE COLUMN [IF EXISTS]
MODIFY COLUMN [IF EXISTS]
DROP INDEX [IF EXISTS]CREATE TABLE t (i INT);
ALTER TABLE IF EXISTS t RENAME COLUMN IF EXISTS i TO k;CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
[DEFAULT] CHARACTER SET [=] charset_name
[DEFAULT] COLLATE [=] collation_name... ADD COLUMN [IF NOT EXISTS] (col_name [column_definition](../create/create-table.md#column-definitions),...)... DROP COLUMN [IF EXISTS] col_name [CASCADE|RESTRICT]CREATE TABLE a (
a int,
b int,
primary key (a,b)
);
ALTER TABLE x DROP COLUMN a;
[42000][1072] Key column 'A' doesn't exist in tableCREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY((a));
ALTER TABLE t1 MODIFY a BIGINT UNSIGNED AUTO_INCREMENT;CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(a));
ALTER TABLE t1 CHANGE a b BIGINT UNSIGNED AUTO_INCREMENT;CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT, b varchar(50), PRIMARY KEY(a));
ALTER TABLE t1 ALTER b SET DEFAULT 'hello';ALTER TABLE t1 RENAME INDEX i_old TO i_new;ALTER TABLE t1 RENAME COLUMN c_old TO c_new;ALTER TABLE table_name
ADD CONSTRAINT [constraint_name] CHECK(expression);CREATE TABLE account_ledger (
id INT PRIMARY KEY AUTO_INCREMENT,
transaction_name VARCHAR(100),
credit_account VARCHAR(100),
credit_amount INT,
debit_account VARCHAR(100),
debit_amount INT);
ALTER TABLE account_ledger
ADD CONSTRAINT is_balanced
CHECK((debit_amount + credit_amount) = 0);SELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'account_ledger';
+-----------------+----------------+-----------------+
| CONSTRAINT_NAME | TABLE_NAME | CONSTRAINT_TYPE |
+-----------------+----------------+-----------------+
| is_balanced | account_ledger | CHECK |
+-----------------+----------------+-----------------+ALTER TABLE table_name
DROP CONSTRAINT constraint_name;CREATE TABLE t (
a INT,
b INT,
c INT,
CONSTRAINT CHECK(a > b),
CONSTRAINT check_equals CHECK(a = c));
SELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 't';
+-----------------+----------------+-----------------+
| CONSTRAINT_NAME | TABLE_NAME | CONSTRAINT_TYPE |
+-----------------+----------------+-----------------+
| check_equals | t | CHECK |
| CONSTRAINT_1 | t | CHECK |
+-----------------+----------------+-----------------+ALTER TABLE t DROP CONSTRAINT is_unique;ALTER TABLE tab_name FORCE;CREATE OR REPLACE TABLE x (d DATE DEFAULT '0000-00-00');
SET SQL_MODE='NO_ZERO_DATE';
ALTER TABLE x FORCE;
ERROR 1067 (42000): Invalid default value for 'd'ALTER TABLE partitioned_table CONVERT PARTITION part1 TO TABLE normal_table;ALTER TABLE partitioned_table CONVERT TABLE normal_table
TO PARTITION part1 VALUES LESS THAN (12345);ALTER TABLE partitioned_table CONVERT PARTITION part1 TO TABLE normal_table;ALTER TABLE partitioned_table CONVERT TABLE normal_table
TO PARTITION part1 VALUES LESS THAN (12345);-- Create a temporary table with the new definition
CREATE TEMPORARY TABLE tmp_tab (
...
);
-- Copy the data from the original table
INSERT INTO tmp_tab
SELECT * FROM original_tab;
-- Drop the original table
DROP TABLE original_tab;
-- Rename the temporary table, so that it replaces the original one
RENAME TABLE tmp_tab TO original_tab;ALTER TABLE test ENGINE=Aria;
Stage: 1 of 2 'copy to tmp table' 46% of stageALTER TABLE t1 ADD x INT;ALTER TABLE t1 DROP x;ALTER TABLE t1 MODIFY x bigint unsigned;ALTER TABLE t1 CHANGE a b bigint unsigned auto_increment;ALTER TABLE t1 DROP x, ADD x2 INT, CHANGE y y2 INT;ALTER TABLE t1
ENGINE = InnoDB
COMMENT = 'First of three tables containing usage info';ALTER TABLE t1 FORCE;ALTER TABLE rooms DROP INDEX u;ALTER TABLE rooms ADD UNIQUE INDEX u(room_number);ALTER TABLE rooms ADD PRIMARY KEY(room_number, p WITHOUT OVERLAPS);SET @@SESSION.binlog_alter_two_phase = TRUE;| 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 |
sql
SET @@SESSION.binlog_alter_two_phase = true;