# InnoDB Online DDL Operations with the NOCOPY Alter Algorithm

## Supported Operations by Inheritance

When the [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) clause is set to `NOCOPY`, the supported operations are a superset of the operations that are supported when the [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) clause is set to `INSTANT`.

Therefore, when the [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) clause is set to `NOCOPY`, some operations are supported by inheritance. See the following additional pages for more information about these supported operations:

* [InnoDB Online DDL Operations with ALGORITHM=INSTANT](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-online-ddl/innodb-online-ddl-operations-with-the-instant-alter-algorithm)

## Column Operations

### `ALTER TABLE ... ADD COLUMN`

In [MariaDB 10.3.2](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.3/10.3.2) and later, InnoDB supports adding columns to a table with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY` in the cases where the operation supports having the [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) clause set to `INSTANT`.

See [InnoDB Online DDL Operations with ALGORITHM=INSTANT: ALTER TABLE ... ADD COLUMN](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-online-ddl/innodb-online-ddl-operations-with-the-instant-alter-algorithm) for more information.

This applies to [ALTER TABLE ... ADD COLUMN](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#add-column) for [InnoDB](https://mariadb.com/docs/server/server-usage/storage-engines/innodb) tables.

### `ALTER TABLE ... DROP COLUMN`

In [MariaDB 10.4](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.4/what-is-mariadb-104) and later, InnoDB supports dropping columns from a table with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY` in the cases where the operation supports having the [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) clause set to `INSTANT`.

See [InnoDB Online DDL Operations with ALGORITHM=INSTANT: ALTER TABLE ... DROP COLUMN](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-online-ddl/innodb-online-ddl-operations-with-the-instant-alter-algorithm) for more information.

This applies to [ALTER TABLE ... DROP COLUMN](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#drop-column) for [InnoDB](https://mariadb.com/docs/server/server-usage/storage-engines/innodb) tables.

### `ALTER TABLE ... MODIFY COLUMN`

This applies to [ALTER TABLE ... MODIFY COLUMN](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#modify-column) for [InnoDB](https://mariadb.com/docs/server/server-usage/storage-engines/innodb) tables.

#### Reordering Columns

In [MariaDB 10.4](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.4/what-is-mariadb-104) and later, InnoDB supports reordering columns within a table with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY` in the cases where the operation supports having the [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) clause set to `INSTANT`.

See [InnoDB Online DDL Operations with ALGORITHM=INSTANT: Reordering Columns](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-online-ddl/innodb-online-ddl-operations-with-the-instant-alter-algorithm) for more information.

#### Changing the Data Type of a Column

InnoDB does **not** support modifying a column's data type with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY` in most cases. There are a few exceptions in the cases where the operation supports having the [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) clause set to `INSTANT`.

See [InnoDB Online DDL Operations with ALGORITHM=INSTANT: Changing the Data Type of a Column](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-online-ddl/innodb-online-ddl-operations-with-the-instant-alter-algorithm) for more information.

#### Changing a Column to NULL

In [MariaDB 10.4.3](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.4/10.4.3) and later, InnoDB supports modifying a column to allow [NULL](https://mariadb.com/docs/server/tables/create-table#null-and-not-null) values with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY` in the cases where the operation supports having the [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) clause set to `INSTANT`.

See [InnoDB Online DDL Operations with ALGORITHM=INSTANT: Changing a Column to NULL](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-online-ddl/innodb-online-ddl-operations-with-the-instant-alter-algorithm) for more information.

#### Changing a Column to NOT NULL

InnoDB does **not** support modifying a column to **not** allow [NULL](https://mariadb.com/docs/server/tables/create-table#null-and-not-null) values with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY`.

For example:

```sql
CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50)
) ROW_FORMAT=REDUNDANT;

SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab MODIFY COLUMN c VARCHAR(50) NOT NULL;
ERROR 1845 (0A000): ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE
```

#### Adding a New `ENUM` Option

InnoDB supports adding a new [ENUM](https://mariadb.com/docs/server/reference/data-types/string-data-types/enum) option to a column with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY` in the cases where the operation supports having the [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) clause set to `INSTANT`.

See [InnoDB Online DDL Operations with ALGORITHM=INSTANT: Adding a New ENUM Option](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-online-ddl/innodb-online-ddl-operations-with-the-instant-alter-algorithm) for more information.

#### Adding a New `SET` Option

InnoDB supports adding a new [SET](https://mariadb.com/docs/server/reference/data-types/string-data-types/set-data-type) option to a column with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY` in the cases where the operation supports having the [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) clause set to `INSTANT`.

See [InnoDB Online DDL Operations with ALGORITHM=INSTANT: Adding a New SET Option](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-online-ddl/innodb-online-ddl-operations-with-the-instant-alter-algorithm) for more information.

#### Removing System Versioning from a Column

In [MariaDB 10.3.8](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.3/10.3.8) and later, InnoDB supports removing [system versioning](https://mariadb.com/docs/server/reference/sql-structure/temporal-tables/system-versioned-tables) from a column with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY` in the cases where the operation supports having the [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) clause set to `INSTANT`.

See [InnoDB Online DDL Operations with ALGORITHM=INSTANT: Removing System Versioning from a Column](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-online-ddl/innodb-online-ddl-operations-with-the-instant-alter-algorithm) for more information.

### `ALTER TABLE ... ALTER COLUMN`

This applies to [ALTER TABLE ... ALTER COLUMN](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#alter-column) for [InnoDB](https://mariadb.com/docs/server/server-usage/storage-engines/innodb) tables.

#### Setting a Column's Default Value

InnoDB supports modifying a column's [DEFAULT](https://mariadb.com/docs/server/tables/create-table#default-column-option) value with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY` in the cases where the operation supports having the [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) clause set to `INSTANT`.

See [InnoDB Online DDL Operations with ALGORITHM=INSTANT: Setting a Column's Default Value](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-online-ddl/innodb-online-ddl-operations-with-the-instant-alter-algorithm) for more information.

#### Removing a Column's Default Value

InnoDB supports removing a column's [DEFAULT](https://mariadb.com/docs/server/tables/create-table#default-column-option) value with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY` in the cases where the operation supports having the [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) clause set to `INSTANT`.

See [InnoDB Online DDL Operations with ALGORITHM=INSTANT: Removing a Column's Default Value](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-online-ddl/innodb-online-ddl-operations-with-the-instant-alter-algorithm) for more information.

### `ALTER TABLE ... CHANGE COLUMN`

InnoDB supports renaming a column with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY` in the cases where the operation supports having the [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) clause set to `INSTANT`.

See [InnoDB Online DDL Operations with ALGORITHM=INSTANT: ALTER TABLE ... CHANGE COLUMN](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-online-ddl/innodb-online-ddl-operations-with-the-instant-alter-algorithm) for more information.

This applies to [ALTER TABLE ... CHANGE COLUMN](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#change-column) for [InnoDB](https://mariadb.com/docs/server/server-usage/storage-engines/innodb) tables.

## Index Operations

### `ALTER TABLE ... ADD PRIMARY KEY`

InnoDB does **not** support adding a primary key to a table with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY`.

For example:

```sql
CREATE OR REPLACE TABLE tab (
   a INT,
   b VARCHAR(50),
   c VARCHAR(50)
);

SET SESSION sql_mode='STRICT_TRANS_TABLES';
SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab ADD PRIMARY KEY (a);
ERROR 1845 (0A000): ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE
```

This applies to [ALTER TABLE ... ADD PRIMARY KEY](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#add-primary-key) for [InnoDB](https://mariadb.com/docs/server/server-usage/storage-engines/innodb) tables.

### `ALTER TABLE ... DROP PRIMARY KEY`

InnoDB does **not** support dropping a primary key with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY`.

For example:

```sql
CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50)
);

SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab DROP PRIMARY KEY;
ERROR 1846 (0A000): ALGORITHM=NOCOPY is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try ALGORITHM=COPY
```

This applies to [ALTER TABLE ... DROP PRIMARY KEY](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#drop-primary-key) for [InnoDB](https://mariadb.com/docs/server/server-usage/storage-engines/innodb) tables.

### `ALTER TABLE ... ADD INDEX` and `CREATE INDEX`

This applies to [ALTER TABLE ... ADD INDEX](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#add-index) and [CREATE INDEX](https://mariadb.com/docs/server/reference/sql-statements/data-definition/create/create-index) for [InnoDB](https://mariadb.com/docs/server/server-usage/storage-engines/innodb) tables.

#### Adding a Plain Index

InnoDB supports adding a plain index to a table with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY`.

This operation supports the non-locking strategy. This strategy can be explicitly chosen by setting the [LOCK](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#lock) clause to `NONE`. When this strategy is used, all concurrent DML is permitted.

For example, this succeeds:

```sql
CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50)
);

SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab ADD INDEX b_index (b);
Query OK, 0 rows affected (0.009 sec)
```

And this succeeds:

```sql
CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50)
);

SET SESSION alter_algorithm='NOCOPY';
CREATE INDEX b_index ON tab (b);
Query OK, 0 rows affected (0.009 sec)
```

#### Adding a Fulltext Index

InnoDB supports adding a [FULLTEXT](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/optimization-and-indexes/full-text-indexes) index to a table with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY`.

However, there are some limitations, such as:

* Adding a [FULLTEXT](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/optimization-and-indexes/full-text-indexes) index to a table that does not have a user-defined `FTS_DOC_ID` column will require the table to be rebuilt once. When the table is rebuilt, the system adds a hidden `FTS_DOC_ID` column. This initial operation will have to be performed with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `INPLACE`.From that point forward, adding additional [FULLTEXT](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/optimization-and-indexes/full-text-indexes) indexes to the same table will not require the table to be rebuilt, and [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) can be set to `NOCOPY`.
* Only one [FULLTEXT](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/optimization-and-indexes/full-text-indexes) index may be added at a time when [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) is set to `NOCOPY`.

This operation supports a read-only locking strategy. This strategy can be explicitly chosen by setting the [LOCK](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#lock) clause to `SHARED`. When this strategy is used, read-only concurrent DML is permitted.

For example, this succeeds, but the first operation requires the table to be rebuilt [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `INPLACE`, so that the hidden `FTS_DOC_ID` column can be added:

```sql
CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab ADD FULLTEXT INDEX b_index (b);
Query OK, 0 rows affected (0.043 sec)

SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab ADD FULLTEXT INDEX c_index (c);
Query OK, 0 rows affected (0.017 sec)
```

And this succeeds in the same way as above:

```sql
CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50)
);

SET SESSION alter_algorithm='INPLACE';
CREATE FULLTEXT INDEX b_index ON tab (b);
Query OK, 0 rows affected (0.048 sec)

SET SESSION alter_algorithm='NOCOPY';
CREATE FULLTEXT INDEX c_index ON tab (c);
Query OK, 0 rows affected (0.016 sec)
```

But this second command fails, because only one [FULLTEXT](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/optimization-and-indexes/full-text-indexes) index can be added at a time:

```sql
CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50),
   d VARCHAR(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab ADD FULLTEXT INDEX b_index (b);
Query OK, 0 rows affected (0.041 sec)

SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab ADD FULLTEXT INDEX c_index (c), ADD FULLTEXT INDEX d_index (d);
ERROR 1846 (0A000): ALGORITHM=NOCOPY is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPY
```

#### Adding a Spatial Index

InnoDB supports adding a [SPATIAL](https://mariadb.com/docs/server/reference/sql-structure/geometry/spatial-index) index to a table with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY`.

This operation supports a read-only locking strategy. This strategy can be explicitly chosen by setting the [LOCK](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#lock) clause to `SHARED`. When this strategy is used, read-only concurrent DML is permitted.

For example, this succeeds:

```sql
CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c GEOMETRY NOT NULL
);

SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab ADD SPATIAL INDEX c_index (c);
Query OK, 0 rows affected (0.005 sec)
```

And this succeeds in the same way as above:

```sql
CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c GEOMETRY NOT NULL
);

SET SESSION alter_algorithm='NOCOPY';
CREATE SPATIAL INDEX c_index ON tab (c);
Query OK, 0 rows affected (0.005 sec)
```

### `ALTER TABLE ... DROP INDEX` and `DROP INDEX`

InnoDB supports dropping indexes from a table with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY` in the cases where the operation supports having the [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) clause set to `INSTANT`.

See [InnoDB Online DDL Operations with ALGORITHM=INSTANT: ALTER TABLE ... DROP INDEX and DROP INDEX](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-online-ddl/innodb-online-ddl-operations-with-the-instant-alter-algorithm) for more information.

This applies to [ALTER TABLE ... DROP INDEX](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#drop-index) and [DROP INDEX](https://mariadb.com/docs/server/reference/sql-statements/data-definition/drop/drop-index) for [InnoDB](https://mariadb.com/docs/server/server-usage/storage-engines/innodb) tables.

### `ALTER TABLE ... ADD FOREIGN KEY`

InnoDB does supports adding foreign key constraints to a table with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY`. In order to add a new foreign key constraint to a table with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY`, the [foreign\_key\_checks](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#foreign_key_checks) system variable needs to be set to `OFF`. If it is set to `ON`, then `ALGORITHM=COPY` is required.

This operation supports the non-locking strategy. This strategy can be explicitly chosen by setting the [LOCK](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#lock) clause to `NONE`. When this strategy is used, all concurrent DML is permitted.

For example, this fails:

```sql
CREATE OR REPLACE TABLE tab1 (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50),
   d INT
);

CREATE OR REPLACE TABLE tab2 (
   a INT PRIMARY KEY,
   b VARCHAR(50)
);

SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab1 ADD FOREIGN KEY tab2_fk (d) REFERENCES tab2 (a);
ERROR 1846 (0A000): ALGORITHM=NOCOPY is not supported. Reason: Adding foreign keys needs foreign_key_checks=OFF. Try ALGORITHM=COPY
```

But this succeeds:

```sql
CREATE OR REPLACE TABLE tab1 (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50),
   d INT
);

CREATE OR REPLACE TABLE tab2 (
   a INT PRIMARY KEY,
   b VARCHAR(50)
);

SET SESSION foreign_key_checks=OFF;
SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab1 ADD FOREIGN KEY tab2_fk (d) REFERENCES tab2 (a);
Query OK, 0 rows affected (0.011 sec)
```

This applies to [ALTER TABLE ... ADD FOREIGN KEY](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#add-foreign-key) for [InnoDB](https://mariadb.com/docs/server/server-usage/storage-engines/innodb) tables.

### `ALTER TABLE ... DROP FOREIGN KEY`

InnoDB supports dropping foreign key constraints from a table with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY` in the cases where the operation supports having the [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) clause set to `INSTANT`.

See [InnoDB Online DDL Operations with ALGORITHM=INSTANT: ALTER TABLE ... DROP FOREIGN KEY](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-online-ddl/innodb-online-ddl-operations-with-the-instant-alter-algorithm) for more information.

This applies to [ALTER TABLE ... DROP FOREIGN KEY](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#drop-foreign-key) for [InnoDB](https://mariadb.com/docs/server/server-usage/storage-engines/innodb) tables.

## Table Operations

### `ALTER TABLE ... AUTO_INCREMENT=...`

InnoDB supports changing a table's [AUTO\_INCREMENT](https://mariadb.com/docs/server/reference/data-types/auto_increment) value with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY` in the cases where the operation supports having the [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) clause set to `INSTANT`.

See [InnoDB Online DDL Operations with ALGORITHM=INSTANT: ALTER TABLE ... AUTO\_INCREMENT=...](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-online-ddl/innodb-online-ddl-operations-with-the-instant-alter-algorithm) for more information.

This applies to [ALTER TABLE ... AUTO\_INCREMENT=...](https://mariadb.com/docs/server/tables/create-table#auto_increment) for [InnoDB](https://mariadb.com/docs/server/server-usage/storage-engines/innodb) tables.

### `ALTER TABLE ... ROW_FORMAT=...`

InnoDB does **not** support changing a table's [row format](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-row-formats/innodb-row-formats-overview) with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY`.

For example:

```sql
CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50)
) ROW_FORMAT=DYNAMIC;

SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab ROW_FORMAT=COMPRESSED;
ERROR 1846 (0A000): ALGORITHM=NOCOPY is not supported. Reason: Changing table options requires the table to be rebuilt. Try ALGORITHM=INPLACE
```

This applies to [ALTER TABLE ... ROW\_FORMAT=...](https://mariadb.com/docs/server/tables/create-table#row_format) for [InnoDB](https://mariadb.com/docs/server/server-usage/storage-engines/innodb) tables.

### `ALTER TABLE ... KEY_BLOCK_SIZE=...`

InnoDB does **not** support changing a table's [KEY\_BLOCK\_SIZE](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-row-formats/innodb-row-formats-overview) with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY`.

For example:

```sql
CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50)
) ROW_FORMAT=COMPRESSED
  KEY_BLOCK_SIZE=4;

SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab KEY_BLOCK_SIZE=2;
ERROR 1846 (0A000): ALGORITHM=NOCOPY is not supported. Reason: Changing table options requires the table to be rebuilt. Try ALGORITHM=INPLACE
```

This applies to [KEY\_BLOCK\_SIZE=...](https://mariadb.com/docs/server/tables/create-table#key_block_size) for [InnoDB](https://mariadb.com/docs/server/server-usage/storage-engines/innodb) tables.

### `ALTER TABLE ... PAGE_COMPRESSED=1` and `ALTER TABLE ... PAGE_COMPRESSION_LEVEL=...`

In [MariaDB 10.3.10](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.3/10.3.10) and later, InnoDB supports setting a table's [PAGE\_COMPRESSED](https://mariadb.com/docs/server/tables/create-table#page_compressed) value to `1` with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY` in the cases where the operation supports having the [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) clause set to `INSTANT`.

InnoDB does **not** support changing a table's [PAGE\_COMPRESSED](https://mariadb.com/docs/server/tables/create-table#page_compressed) value from `1` to `0` with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY`.

In these versions, InnoDB also supports changing a table's [PAGE\_COMPRESSION\_LEVEL](https://mariadb.com/docs/server/tables/create-table#page_compression_level) value with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY` in the cases where the operation supports having the [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) clause is set to `INSTANT`.

See [InnoDB Online DDL Operations with ALGORITHM=INSTANT: ALTER TABLE ... PAGE\_COMPRESSED=1 and ALTER TABLE ... PAGE\_COMPRESSION\_LEVEL=...](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-online-ddl/innodb-online-ddl-operations-with-the-instant-alter-algorithm) for more information.

This applies to [ALTER TABLE ... PAGE\_COMPRESSED=...](https://mariadb.com/docs/server/tables/create-table#page_compressed) and [ALTER TABLE ... PAGE\_COMPRESSION\_LEVEL=...](https://mariadb.com/docs/server/tables/create-table#page_compression_level) for [InnoDB](https://mariadb.com/docs/server/server-usage/storage-engines/innodb) tables.

### `ALTER TABLE ... DROP SYSTEM VERSIONING`

InnoDB does **not** support dropping [system versioning](https://mariadb.com/docs/server/reference/sql-structure/temporal-tables/system-versioned-tables) from a table with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY`.

For example:

```sql
CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50)
) WITH SYSTEM VERSIONING;

SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab DROP SYSTEM VERSIONING;
ERROR 1845 (0A000): ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE
```

This applies to [ALTER TABLE ... DROP SYSTEM VERSIONING](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#drop-system-versioning) for [InnoDB](https://mariadb.com/docs/server/server-usage/storage-engines/innodb) tables.

### `ALTER TABLE ... DROP CONSTRAINT`

In [MariaDB 10.3.6](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.3/10.3.6) and later, InnoDB supports dropping a [CHECK](https://mariadb.com/docs/server/reference/sql-statements/data-definition/constraint#check-constraints) constraint from a table with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY` in the cases where the operation supports having the [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) clause set to `INSTANT`.

See [InnoDB Online DDL Operations with ALGORITHM=INSTANT: ALTER TABLE ... DROP CONSTRAINT](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-online-ddl/innodb-online-ddl-operations-with-the-instant-alter-algorithm) for more information.

This applies to [ALTER TABLE ... DROP CONSTRAINT](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#drop-constraint) for [InnoDB](https://mariadb.com/docs/server/server-usage/storage-engines/innodb) tables.

### `ALTER TABLE ... FORCE`

InnoDB does **not** support forcing a table rebuild with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY`.

For example:

```sql
CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50)
);

SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab FORCE;
ERROR 1845 (0A000): ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE
```

This applies to [ALTER TABLE ... FORCE](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#force) for [InnoDB](https://mariadb.com/docs/server/server-usage/storage-engines/innodb) tables.

### `ALTER TABLE ... ENGINE=InnoDB`

InnoDB does **not** support forcing a table rebuild with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY`.

For example:

```sql
CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50)
);

SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab ENGINE=InnoDB;
ERROR 1845 (0A000): ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE
```

This applies to [ALTER TABLE ... ENGINE=InnoDB](https://mariadb.com/docs/server/tables/create-table#storage-engine) for [InnoDB](https://mariadb.com/docs/server/server-usage/storage-engines/innodb) tables.

### `OPTIMIZE TABLE ...`

InnoDB does **not** support optimizing a table with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY`.

For example:

```sql
CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50)
);

SHOW GLOBAL VARIABLES WHERE Variable_name IN('innodb_defragment', 'innodb_optimize_fulltext_only');
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_defragment             | OFF   |
| innodb_optimize_fulltext_only | OFF   |
+-------------------------------+-------+
2 rows in set (0.001 sec)

SET SESSION alter_algorithm='NOCOPY';
OPTIMIZE TABLE tab;
+---------+----------+----------+-----------------------------------------------------------------------------+
| Table   | Op       | Msg_type | Msg_text                                                                    |
+---------+----------+----------+-----------------------------------------------------------------------------+
| db1.tab | optimize | note     | Table does not support optimize, doing recreate + analyze instead           |
| db1.tab | optimize | error    | ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE |
| db1.tab | optimize | status   | Operation failed                                                            |
+---------+----------+----------+-----------------------------------------------------------------------------+
3 rows in set, 1 warning (0.002 sec)
```

This applies to [OPTIMIZE TABLE](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/optimizing-tables/optimize-table) for [InnoDB](https://mariadb.com/docs/server/server-usage/storage-engines/innodb) tables.

### `ALTER TABLE ... RENAME TO` and `RENAME TABLE ...`

InnoDB supports renaming a table with [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) set to `NOCOPY` in the cases where the operation supports having the [ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#algorithm) clause set to `INSTANT`.

See [InnoDB Online DDL Operations with ALGORITHM=INSTANT: ALTER TABLE ... RENAME TO and RENAME TABLE ...](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-online-ddl/innodb-online-ddl-operations-with-the-instant-alter-algorithm) for more information.

This applies to [ALTER TABLE ... RENAME TO](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table#rename-to) and [RENAME TABLE](https://mariadb.com/docs/server/reference/sql-statements/data-definition/rename-table) for [InnoDB](https://mariadb.com/docs/server/server-usage/storage-engines/innodb) tables.

## Limitations

### Limitations Related to Generated (Virtual and Persistent/Stored) Columns

[Generated columns](https://mariadb.com/docs/server/reference/sql-statements/data-definition/create/generated-columns) do not currently support online DDL for all of the same operations that are supported for "real" columns.

See [Generated (Virtual and Persistent/Stored) Columns: Statement Support](https://mariadb.com/docs/server/reference/sql-statements/data-definition/create/generated-columns#statement-support) for more information on the limitations.

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-online-ddl/innodb-online-ddl-operations-with-the-nocopy-alter-algorithm.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.