CREATE TABLESPACE

Syntax:
CREATE [UNDO] TABLESPACE tablespace_name

InnoDB and NDB:
[ADD DATAFILE 'file_name']

InnoDB only:
[FILE_BLOCK_SIZE = value]
[ENCRYPTION [=] {'Y' | 'N'}]

NDB only:
USE LOGFILE GROUP logfile_group
[EXTENT_SIZE [=] extent_size]
[INITIAL_SIZE [=] initial_size]
[AUTOEXTEND_SIZE [=] autoextend_size]
[MAX_SIZE [=] max_size]
[NODEGROUP [=] nodegroup_id]
[WAIT]
[COMMENT [=] 'string']

InnoDB and NDB:
[ENGINE [=] engine_name]

Reserved for future use:
[ENGINE_ATTRIBUTE [=] 'string']

This statement is used to create a tablespace. The precise syntax and
semantics depend on the storage engine used. In standard MySQL
releases, this is always an InnoDB tablespace. MySQL NDB Cluster also
supports tablespaces using the NDB storage engine.

o https://dev.mysql.com/doc/refman/8.0/en/create-tablespace.html#create
-tablespace-innodb

o https://dev.mysql.com/doc/refman/8.0/en/create-tablespace.html#create
-tablespace-ndb

o https://dev.mysql.com/doc/refman/8.0/en/create-tablespace.html#create
-tablespace-options

o https://dev.mysql.com/doc/refman/8.0/en/create-tablespace.html#create
-tablespace-notes

o https://dev.mysql.com/doc/refman/8.0/en/create-tablespace.html#create
-tablespace-innodb-examples

o https://dev.mysql.com/doc/refman/8.0/en/create-tablespace.html#create
-tablespace-ndb-examples

Considerations for InnoDB

CREATE TABLESPACE syntax is used to create general tablespaces or undo
tablespaces. The UNDO keyword, introduced in MySQL 8.0.14, must be
specified to create an undo tablespace.

A general tablespace is a shared tablespace. It can hold multiple
tables, and supports all table row formats. General tablespaces can be
created in a location relative to or independent of the data directory.

After creating an InnoDB general tablespace, use CREATE TABLE tbl_name
... TABLESPACE [=] tablespace_name or ALTER TABLE tbl_name TABLESPACE
[=] tablespace_name to add tables to the tablespace. For more
information, see
https://dev.mysql.com/doc/refman/8.0/en/general-tablespaces.html.

Undo tablespaces contain undo logs. Undo tablespaces can be created in
a chosen location by specifying a fully qualified data file path. For
more information, see
https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-tablespaces.html.

Considerations for NDB Cluster

This statement is used to create a tablespace, which can contain one or
more data files, providing storage space for NDB Cluster Disk Data
tables (see
https://dev.mysql.com/doc/refman/8.0/en/mysql-cluster-disk-data.html).
One data file is created and added to the tablespace using this
statement. Additional data files may be added to the tablespace by
using the ALTER TABLESPACE statement (see [HELP ALTER TABLESPACE]).

*Note*:

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 log file group with the same name, or a tablespace and
a data file with the same name.

A log file group of one or more UNDO log files must be assigned to the
tablespace to be created with the USE LOGFILE GROUP clause.
logfile_group must be an existing log file group created with CREATE
LOGFILE GROUP (see [HELP CREATE LOGFILE GROUP]). Multiple tablespaces
may use the same log file group for UNDO logging.

When setting EXTENT_SIZE or INITIAL_SIZE, you may optionally follow the
number 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).

INITIAL_SIZE and EXTENT_SIZE are subject to rounding as follows:

o EXTENT_SIZE is rounded up to the nearest whole multiple of 32K.

o INITIAL_SIZE is rounded down to the nearest whole multiple of 32K;
this result is rounded up to the nearest whole multiple of
EXTENT_SIZE (after any rounding).

*Note*:

NDB reserves 4% of a tablespace for data node restart operations. This
reserved space cannot be used for data storage.

The rounding just described is done explicitly, and a warning is issued
by the MySQL Server when any such rounding is performed. The rounded
values are also used by the NDB kernel for calculating
INFORMATION_SCHEMA.FILES column values and other purposes. However, to
avoid an unexpected result, we suggest that you always use whole
multiples of 32K in specifying these options.

When CREATE TABLESPACE is used with ENGINE [=] NDB, a tablespace and
associated data file are 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. (See the example
later in this section.)

(See
https://dev.mysql.com/doc/refman/8.0/en/information-schema-files-table.
html.)

Options

o ADD DATAFILE: Defines the name of a tablespace data file. This option
is always required when creating an NDB tablespace; for InnoDB in
MySQL 8.0.14 and later, it is required only when creating an undo
tablespace. The file_name, including any specified path, must be
quoted with single or double quotation marks. File names (not
counting the file extension) and directory names must be at least one
byte in length. Zero length file names and directory names are not
supported.

Because there are considerable differences in how InnoDB and NDB
treat data files, the two storage engines are covered separately in
the discussion that follows.

InnoDB data files An InnoDB tablespace supports only a single data
file, whose name must include a .ibd extension.

To place an InnoDB general tablespace data file in a location outside
of the data directory, include a fully qualified path or a path
relative to the data directory. Only a fully qualified path is
permitted for undo tablespaces. If you do not specify a path, a
general tablespace is created in the data directory. An undo
tablespace created without specifying a path is created in the
directory defined by the innodb_undo_directory variable. If the
innodb_undo_directory variable is undefined, undo tablespaces are
created in the data directory.

To avoid conflicts with implicitly created file-per-table
tablespaces, creating an InnoDB general tablespace in a subdirectory
under the data directory is not supported. When creating a general
tablespace or undo tablespace outside of the data directory, the
directory must exist and must be known to InnoDB prior to creating
the tablespace. To make a directory known to InnoDB, add it to the
innodb_directories value or to one of the variables whose values are
appended to the innodb_directories value. innodb_directories is a
read-only variable. Configuring it requires restarting the server.

If the ADD DATAFILE clause is not specified when creating an InnoDB
tablespace, a tablespace data file with a unique file name is created
implicitly. The unique file name is a 128 bit UUID formatted into
five groups of hexadecimal numbers separated by dashes
(aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee). A file extension is added if
required by the storage engine. An .ibd file extension is added for
InnoDB general tablespace data files. In a replication environment,
the data file name created on the replication source server is not
the same as the data file name created on the replica.

As of MySQL 8.0.17, the ADD DATAFILE clause does not permit circular
directory references when creating an InnoDB tablespace. For example,
the circular directory reference (/../) in the following statement is
not permitted:

CREATE TABLESPACE ts1 ADD DATAFILE ts1.ibd 'any_directory/../ts1.ibd';

An exception to this restriction exists on Linux, where a circular
directory reference is permitted if the preceding directory is a
symbolic link. For example, the data file path in the example above
is permitted if any_directory is a symbolic link. (It is still
permitted for data file paths to begin with '../'.)

NDB data files An NDB tablespace supports multiple data files which
can have any legal file names; more data files can be added to an NDB
Cluster tablespace following its creation by using an ALTER
TABLESPACE statement.

An NDB tablespace data file is created by default in the data node
file system directory---that is, the directory named ndb_nodeid_fs/TS
under the data node's data directory (DataDir), where nodeid is the
data node's NodeId. To place the data file in a location other than
the default, include an absolute directory path or a path relative to
the default location. If the directory specified does not exist, NDB
attempts to create it; the system user account under which the data
node process is running must have the appropriate permissions to do
so.

*Note*:

When determining the path used for a data file, NDB does not expand
the ~ (tilde) character.

When multiple data nodes are run on the same physical host, the
following considerations apply:

o You cannot specify an absolute path when creating a data file.

o It is not possible to create tablespace data files outside the data
node file system directory, unless each data node has a separate
data directory.

o If each data node has its own data directory, data files can be
created anywhere within this directory.

o If each data node has its own data directory, it may also be
possible to create a data file outside the node's data directory
using a relative path, as long as this path resolves to a unique
location on the host file system for each data node running on that
host.

o FILE_BLOCK_SIZE: This option---which is specific to InnoDB general
tablespaces, and is ignored by NDB---defines the block size for the
tablespace data file. Values can be specified in bytes or kilobytes.
For example, an 8 kilobyte file block size can be specified as 8192
or 8K. If you do not specify this option, FILE_BLOCK_SIZE defaults to
the innodb_page_size value. FILE_BLOCK_SIZE is required when you
intend to use the tablespace for storing compressed InnoDB tables
(ROW_FORMAT=COMPRESSED). In this case, you must define the tablespace
FILE_BLOCK_SIZE when creating the tablespace.

If FILE_BLOCK_SIZE is equal the innodb_page_size value, the
tablespace can contain only tables having an uncompressed row format
(COMPACT, REDUNDANT, and DYNAMIC). Tables with a COMPRESSED row
format have a different physical page size than uncompressed tables.
Therefore, compressed tables cannot coexist in the same tablespace as
uncompressed tables.

For a general tablespace to contain compressed tables,
FILE_BLOCK_SIZE must be specified, and the FILE_BLOCK_SIZE value must
be a valid compressed page size in relation to the innodb_page_size
value. Also, the physical page size of the compressed table
(KEY_BLOCK_SIZE) must be equal to FILE_BLOCK_SIZE/1024. For example,
if innodb_page_size=16K, and FILE_BLOCK_SIZE=8K, the KEY_BLOCK_SIZE
of the table must be 8. For more information, see
https://dev.mysql.com/doc/refman/8.0/en/general-tablespaces.html.

o USE LOGFILE GROUP: Required for NDB, this is the name of a log file
group previously created using CREATE LOGFILE GROUP. Not supported
for InnoDB, where it fails with an error.

o EXTENT_SIZE: This option is specific to NDB, and is not supported by
InnoDB, where it fails with an error. EXTENT_SIZE sets the size, in
bytes, of the extents used by any files belonging to the tablespace.
The default value is 1M. The minimum size is 32K, and theoretical
maximum is 2G, although the practical maximum size depends on a
number of factors. In most cases, changing the extent size does not
have any measurable effect on performance, and the default value is
recommended for all but the most unusual situations.

An extent is a unit of disk space allocation. One extent is filled
with as much data as that extent can contain before another extent is
used. In theory, up to 65,535 (64K) extents may used per data file;
however, the recommended maximum is 32,768 (32K). The recommended
maximum size for a single data file is 32G---that is, 32K extents x 1
MB per extent. In addition, once an extent is allocated to a given
partition, it cannot be used to store data from a different
partition; an extent cannot store data from more than one partition.
This means, for example that a tablespace having a single datafile
whose INITIAL_SIZE (described in the following item) is 256 MB and
whose EXTENT_SIZE is 128M has just two extents, and so can be used to
store data from at most two different disk data table partitions.

You can see how many extents remain free in a given data file by
querying the INFORMATION_SCHEMA.FILES table, and so derive an
estimate for how much space remains free in the file. For further
discussion and examples, see
https://dev.mysql.com/doc/refman/8.0/en/information-schema-files-tabl
e.html.

o INITIAL_SIZE: This option is specific to NDB, and is not supported by
InnoDB, where it fails with an error.

The INITIAL_SIZE parameter sets the total size in bytes of the data
file that was specific using ADD DATATFILE. Once this file has been
created, its size cannot be changed; however, you can add more data
files to the tablespace using ALTER TABLESPACE ... ADD DATAFILE.

INITIAL_SIZE is optional; its default value is 134217728 (128 MB).

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

o AUTOEXTEND_SIZE: Currently ignored by MySQL; reserved for possible
future use. Has no effect in any release of MySQL 8.0 or MySQL NDB
Cluster 8.0, regardless of the storage engine used.

o MAX_SIZE: Currently ignored by MySQL; reserved for possible future
use. Has no effect in any release of MySQL 8.0 or MySQL NDB Cluster
8.0, regardless of the storage engine used.

o NODEGROUP: Currently ignored by MySQL; reserved for possible future
use. Has no effect in any release of MySQL 8.0 or MySQL NDB Cluster
8.0, regardless of the storage engine used.

o WAIT: Currently ignored by MySQL; reserved for possible future use.
Has no effect in any release of MySQL 8.0 or MySQL NDB Cluster 8.0,
regardless of the storage engine used.

o COMMENT: Currently ignored by MySQL; reserved for possible future
use. Has no effect in any release of MySQL 8.0 or MySQL NDB Cluster
8.0, regardless of the storage engine used.

o The ENCRYPTION clause enables or disables page-level data encryption
for an InnoDB general tablespace. Encryption support for general
tablespaces was introduced in MySQL 8.0.13.

As of MySQL 8.0.16, if the ENCRYPTION clause is not specified, the
default_table_encryption setting controls whether encryption is
enabled. The ENCRYPTION clause overrides the default_table_encryption
setting. However, if the table_encryption_privilege_check variable is
enabled, the TABLE_ENCRYPTION_ADMIN privilege is required to use an
ENCRYPTION clause setting that differs from the
default_table_encryption setting.

A keyring plugin must be installed and configured before an
encryption-enabled tablespace can be created.

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

For more information, see
https://dev.mysql.com/doc/refman/8.0/en/innodb-data-encryption.html

o ENGINE: Defines the storage engine which uses the tablespace, where
engine_name is the name of the storage engine. Currently, only the
InnoDB storage engine is supported by standard MySQL 8.0 releases.
MySQL NDB Cluster supports both NDB and InnoDB tablespaces. The value
of the default_storage_engine system variable is used for ENGINE if
the option is not specified.

o The ENGINE_ATTRIBUTE option (available as of MySQL 8.0.21) is used to
specify tablespace attributes for primary storage engines. The option
is reserved for future use.

Permitted values are a string literal containing a valid JSON
document or an empty string (''). Invalid JSON is rejected.

CREATE TABLESPACE ts1 ENGINE_ATTRIBUTE='{"key":"value"}';

ENGINE_ATTRIBUTE values can be repeated without error. In this case,
the last specified value is used.

ENGINE_ATTRIBUTE values are not checked by the server, nor are they
cleared when the table's storage engine is changed.

Notes

o For the rules covering the naming of MySQL tablespaces, see
https://dev.mysql.com/doc/refman/8.0/en/identifiers.html. In addition
to these rules, the slash character ("/") is not permitted, nor can
you use names beginning with innodb_, as this prefix is reserved for
system use.

o Creation of temporary general tablespaces is not supported.

o General tablespaces do not support temporary tables.

o The TABLESPACE option may be used with CREATE TABLE or ALTER TABLE to
assign an InnoDB table partition or subpartition to a file-per-table
tablespace. All partitions must belong to the same storage engine.
Assigning table partitions to shared InnoDB tablespaces is not
supported. Shared tablespaces include the InnoDB system tablespace
and general tablespaces.

o General tablespaces support the addition of tables of any row format
using CREATE TABLE ... TABLESPACE. innodb_file_per_table does not
need to be enabled.

o innodb_strict_mode is not applicable to general tablespaces.
Tablespace management rules are strictly enforced independently of
innodb_strict_mode. If CREATE TABLESPACE parameters are incorrect or
incompatible, the operation fails regardless of the
innodb_strict_mode setting. When a table is added to a general
tablespace using CREATE TABLE ... TABLESPACE or ALTER TABLE ...
TABLESPACE, innodb_strict_mode is ignored but the statement is
evaluated as if innodb_strict_mode is enabled.

o Use DROP TABLESPACE to remove a tablespace. All tables must be
dropped from a tablespace using DROP TABLE prior to dropping the
tablespace. Before dropping an NDB Cluster tablespace you must also
remove all its data files using one or more ALTER TABLESPACE ... DROP
DATATFILE statements. See
https://dev.mysql.com/doc/refman/8.0/en/mysql-cluster-disk-data-objec
ts.html.

o All parts of an InnoDB table added to an InnoDB general tablespace
reside in the general tablespace, including indexes and BLOB pages.

For an NDB table assigned to a tablespace, only those columns which
are not indexed are stored on disk, and actually use the tablespace
data files. Indexes and indexed columns for all NDB tables are always
kept in memory.

o Similar to the system tablespace, truncating or dropping tables
stored in a general tablespace creates free space internally in the
general tablespace .ibd data file which can only be used for new
InnoDB data. Space is not released back to the operating system as it
is for file-per-table tablespaces.

o A general tablespace is not associated with any database or schema.

o ALTER TABLE ... DISCARD TABLESPACE and ALTER TABLE ...IMPORT
TABLESPACE are not supported for tables that belong to a general
tablespace.

o The server uses tablespace-level metadata locking for DDL that
references general tablespaces. By comparison, the server uses
table-level metadata locking for DDL that references file-per-table
tablespaces.

o A generated or existing tablespace cannot be changed to a general
tablespace.

o There is no conflict between general tablespace names and
file-per-table tablespace names. The "/" character, which is present
in file-per-table tablespace names, is not permitted in general
tablespace names.

o mysqldump and mysqlpump do not dump InnoDB CREATE TABLESPACE
statements.

InnoDB Examples

This example demonstrates creating a general tablespace and adding
three uncompressed tables of different row formats.

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' ENGINE=INNODB;

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=REDUNDANT;

mysql> CREATE TABLE t2 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=COMPACT;

mysql> CREATE TABLE t3 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=DYNAMIC;

This example demonstrates creating a general tablespace and adding a
compressed table. The example assumes a default innodb_page_size value
of 16K. The FILE_BLOCK_SIZE of 8192 requires that the compressed table
have a KEY_BLOCK_SIZE of 8.

mysql> CREATE TABLESPACE `ts2` ADD DATAFILE 'ts2.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB;

mysql> CREATE TABLE t4 (c1 INT PRIMARY KEY) TABLESPACE ts2 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

This example demonstrates creating a general tablespace without
specifying the ADD DATAFILE clause, which is optional as of MySQL
8.0.14.

mysql> CREATE TABLESPACE `ts3` ENGINE=INNODB;

This example demonstrates creating an undo tablespace.

mysql> CREATE UNDO TABLESPACE undo_003 ADD DATAFILE 'undo_003.ibu';

NDB Example

Suppose that you wish to create an NDB Cluster Disk Data tablespace
named myts using a datafile named mydata-1.dat. An NDB tablespace
always requires the use of a log file group consisting of one or more
undo log files. For this example, we first create a log file group
named mylg that contains one undo long file named myundo-1.dat, using
the CREATE LOGFILE GROUP statement shown here:

mysql> CREATE LOGFILE GROUP myg1
-> ADD UNDOFILE 'myundo-1.dat'
-> ENGINE=NDB;
Query OK, 0 rows affected (3.29 sec)

Now you can create the tablespace previously described using the
following statement:

mysql> CREATE TABLESPACE myts
-> ADD DATAFILE 'mydata-1.dat'
-> USE LOGFILE GROUP mylg
-> ENGINE=NDB;
Query OK, 0 rows affected (2.98 sec)

You can now create a Disk Data table using a CREATE TABLE statement
with the TABLESPACE and STORAGE DISK options, similar to what is shown
here:

mysql> CREATE TABLE mytable (
-> id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> lname VARCHAR(50) NOT NULL,
-> fname VARCHAR(50) NOT NULL,
-> dob DATE NOT NULL,
-> joined DATE NOT NULL,
-> INDEX(last_name, first_name)
-> )
-> TABLESPACE myts STORAGE DISK
-> ENGINE=NDB;
Query OK, 0 rows affected (1.41 sec)

It is important to note that only the dob and joined columns from
mytable are actually stored on disk, due to the fact that the id,
lname, and fname columns are all indexed.

As mentioned previously, when CREATE TABLESPACE is used with ENGINE [=]
NDB, a tablespace and associated data file are created on each NDB
Cluster data node. You can verify that the data files were created and
obtain information about them by querying the INFORMATION_SCHEMA.FILES
table, as shown here:

mysql> SELECT FILE_NAME, FILE_TYPE, LOGFILE_GROUP_NAME, STATUS, EXTRA
-> FROM INFORMATION_SCHEMA.FILES
-> WHERE TABLESPACE_NAME = 'myts';

+--------------+------------+--------------------+--------+----------------+
| file_name | file_type | logfile_group_name | status | extra |
+--------------+------------+--------------------+--------+----------------+
| mydata-1.dat | DATAFILE | mylg | NORMAL | CLUSTER_NODE=5 |
| mydata-1.dat | DATAFILE | mylg | NORMAL | CLUSTER_NODE=6 |
| NULL | TABLESPACE | mylg | NORMAL | NULL |
+--------------+------------+--------------------+--------+----------------+
3 rows in set (0.01 sec)

For additional information and examples, see
https://dev.mysql.com/doc/refman/8.0/en/mysql-cluster-disk-data-objects
.html.

URL: https://dev.mysql.com/doc/refman/8.0/en/create-tablespace.html