ALTER TABLESPACEtablespace_name
{ADD|DROP} DATAFILE 'file_name
' [INITIAL_SIZE [=]size
] [WAIT] ENGINE [=]engine_name
This statement can be used either to add a new data file, or to drop a data file from a tablespace.
The ADD DATAFILE
variant allows you to specify
an initial size using an INITIAL_SIZE
clause,
where size
is measured in bytes; the
default value is 128M
(128 megabytes). You may
optionally follow this integer value 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
(for megabytes) or G
(for
gigabytes).
All MySQL 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 an data file with the same name, or an undo log file and a with the same name.
Prior to MySQL Cluster NDB 6.2.17, 6.3.23, and 6.4.3, path and file names for data files could not be longer than 128 characters. (Bug#31770)
On 32-bit systems, the maximum supported value for
INITIAL_SIZE
is 4G
.
(Bug#29186)
INITIAL_SIZE
is rounded as for
CREATE TABLESPACE
. Beginning with
MySQL Cluster NDB 6.2.19, MySQL Cluster NDB 6.3.32, MySQL Cluster
NDB 7.0.13, and MySQL Cluster NDB 7.1.2, this rounding is done
explicitly (also as with 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. This
file must already have been added to the tablespace using
CREATE TABLESPACE
or ALTER
TABLESPACE
; otherwise an error will result.
Both ALTER TABLESPACE ... ADD DATAFILE
and
ALTER TABLESPACE ... DROP DATAFILE
require an
ENGINE
clause which specifies the storage
engine used by the tablespace. In MySQL 5.1, 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.1. 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 20.21, “The INFORMATION_SCHEMA FILES
Table”.
ALTER TABLESPACE
was added in MySQL 5.1.6. In
MySQL 5.1, it is useful only with Disk Data storage for MySQL
Cluster. See Section 17.5.10, “MySQL Cluster Disk Data Tables”.
User Comments
Add your own comment.