13.1.9 ALTER TABLESPACE Statement
ALTER TABLESPACE tablespace_name
{ADD | DROP} DATAFILE 'file_name'
[INITIAL_SIZE [=] size]
[WAIT]
ENGINE [=] engine_name
This statement is used either to add a new data file, or to drop a data file from a tablespace.
The ADD DATAFILE
variant enables you to specify an initial size 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).
All NDB Cluster Disk Data objects share the same namespace. This means that each Disk Data object must be uniquely named (and not merely each Disk Data object of a given type). For example, you cannot have a tablespace and a data file with the same name, or an undo log file and a tablespace with the same name.
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 the tablespace using additional ALTER TABLESPACE ... ADD DATAFILE
statements.
Using DROP DATAFILE
with ALTER TABLESPACE
drops the data file 'file_name
' from the 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 21.5.10.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
.
Both ALTER TABLESPACE ... ADD DATAFILE
and ALTER TABLESPACE ... DROP DATAFILE
require an ENGINE
clause which specifies the storage engine used by the tablespace. Currently, the only accepted values for engine_name
are NDB
and NDBCLUSTER
.
WAIT
is parsed but otherwise ignored, and so has no effect in MySQL 5.7. It is intended for future expansion.
When ALTER TABLESPACE ... ADD DATAFILE
is used with ENGINE = NDB
, a data file is created on each Cluster data node. You can verify that the data files were created and obtain information about them by querying the INFORMATION_SCHEMA.FILES
table. For example, the following query shows all data files belonging to the tablespace named newts
:
mysql> SELECT LOGFILE_GROUP_NAME, FILE_NAME, EXTRA
-> FROM INFORMATION_SCHEMA.FILES
-> WHERE TABLESPACE_NAME = 'newts' AND FILE_TYPE = 'DATAFILE';
+--------------------+--------------+----------------+
| LOGFILE_GROUP_NAME | FILE_NAME | EXTRA |
+--------------------+--------------+----------------+
| lg_3 | newdata.dat | CLUSTER_NODE=3 |
| lg_3 | newdata.dat | CLUSTER_NODE=4 |
| lg_3 | newdata2.dat | CLUSTER_NODE=3 |
| lg_3 | newdata2.dat | CLUSTER_NODE=4 |
+--------------------+--------------+----------------+
2 rows in set (0.03 sec)
See Section 24.9, “The INFORMATION_SCHEMA FILES Table”.
ALTER TABLESPACE
is useful only with Disk Data storage for NDB Cluster. See Section 21.5.10, “NDB Cluster Disk Data Tables”.