Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 45.3Mb
PDF (A4) - 45.3Mb
PDF (RPM) - 40.8Mb
HTML Download (TGZ) - 10.5Mb
HTML Download (Zip) - 10.5Mb
HTML Download (RPM) - 9.0Mb
Man Pages (TGZ) - 208.5Kb
Man Pages (Zip) - 310.7Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  ALTER TABLESPACE Syntax

13.1.10 ALTER TABLESPACE Syntax

ALTER [UNDO] TABLESPACE tablespace_name
  NDB only:  
    {ADD|DROP} DATAFILE 'file_name'
    [INITIAL_SIZE [=] size]
    [WAIT]
  InnoDB and NDB:
    [RENAME TO tablespace_name] 
  InnoDB only:
    [SET {ACTIVE|INACTIVE}]
    [ENCRYPTION [=] {'Y' | 'N'}]
  InnoDB and NDB:
    [ENGINE [=] engine_name]

This statement is used with NDB and InnoDB tablespaces. It can be used to add a new data file to, or to drop a data file from an NDB tablespace. It can also be used to rename an NDB Cluster Disk Data tablespace, rename an InnoDB general tablespace, encrypt an InnoDB general tablespace, or mark an InnoDB undo tablespace as active or inactive.

The UNDO keyword, introduced in MySQL 8.0.14, is used with the SET {ACTIVE|INACTIVE} clause to mark an InnoDB undo tablespace as active or inactive. For more information, see Section 15.6.3.4, “Undo Tablespaces”.

The ADD DATAFILE variant enables you to specify an initial size for an NDB Disk Data tablespace using an INITIAL_SIZE clause, where size is measured in bytes; the default value is 134217728 (128 MB). You may optionally follow size with a one-letter abbreviation for an order of magnitude, similar to those used in my.cnf. Generally, this is one of the letters M (megabytes) or G (gigabytes).

On 32-bit systems, the maximum supported value for INITIAL_SIZE is 4294967296 (4 GB). (Bug #29186)

INITIAL_SIZE is rounded, explicitly, as for CREATE TABLESPACE.

Once a data file has been created, its size cannot be changed; however, you can add more data files to an NDB tablespace using additional ALTER TABLESPACE ... ADD DATAFILE statements.

When ALTER TABLESPACE ... ADD DATAFILE is used with ENGINE = NDB, a data file is created on each Cluster data node, but only one row is generated in the INFORMATION_SCHEMA.FILES table. See the description of this table, as well as Section 22.5.13.1, “NDB Cluster Disk Data Objects”, for more information. ADD DATAFILE is not supported with InnoDB tablespaces.

Using DROP DATAFILE with ALTER TABLESPACE drops the data file 'file_name' from an NDB tablespace. You cannot drop a data file from a tablespace which is in use by any table; in other words, the data file must be empty (no extents used). See Section 22.5.13.1, “NDB Cluster Disk Data Objects”. In addition, any data file to be dropped must previously have been added to the tablespace with CREATE TABLESPACE or ALTER TABLESPACE. DROP DATAFILE is not supported with InnoDB tablespaces.

WAIT is parsed but otherwise ignored. It is intended for future expansion.

The ENGINE clause, which specifies the storage engine used by the tablespace, is deprecated and will be removed in a future release. The tablespace storage engine is known by the data dictionary, making the ENGINE clause obsolete. If the storage engine is specified, it must match the tablespace storage engine defined in the data dictionary. The only values for engine_name compatible with NDB tablespaces are NDB and NDBCLUSTER.

RENAME TO operations are implicitly performed in autocommit mode, regardless of the autocommit setting.

A RENAME TO operation cannot be performed while LOCK TABLES or FLUSH TABLES WITH READ LOCK is in effect for tables that reside in the tablespace.

Exclusive metadata locks are taken on tables that reside in a general tablespace while the tablespace is renamed, which prevents concurrent DDL. Concurrent DML is supported.

The CREATE TABLESPACE privilege is required to rename an InnoDB general tablespace.

The ENCRYPTION clause enables or disables page-level data encryption for an InnoDB general tablespace or the mysql system tablespace. Option values are not case-sensitive. Encryption support for general tablespaces was introduced in MySQL 8.0.13. Encryption support for the mysql system tablespace was introduced in MySQL 8.0.16.

As of MySQL 8.0.16, if the table_encryption_privilege_check variable is enabled, the TABLE_ENCRYPTION_ADMIN privilege is required to alter a tablespace with an ENCRYPTION clause that differs from the default_table_encryption setting.

Enabling encryption fails if any table in the tablespace belongs to a schema defined with DEFAULT ENCRYPTION='N'. Similarly, disabling encryption fails if any table in the tablespace belongs to a schema defined with DEFAULT ENCRYPTION='Y'. The DEFAULT ENCRYPTION schema option was introduced in MySQL 8.0.16.

If an ALTER TABLESPACE statement does not include an ENCRYPTION clause, the encryption status of the tablespace is not modified, regardless of the default_table_encryption setting.

A keyring plugin must be installed and configured before encryption can be enabled.

When a general tablespace or the mysql system tablespace is encrypted, all tables residing in the tablespace are encrypted. Likewise, a table created in an encrypted tablespace is encrypted.

The INPLACE algorithm is used when altering the ENCRYPTION attribute of a general tablespace or the mysql system tablespace. The INPLACE algorithm permits concurrent DML on tables that reside in the tablespace. Concurrent DDL is blocked.

For more information, see Section 15.6.3.9, “Tablespace Encryption”.