Rechercher dans le manuel MySQL
22.5.14 Online Operations with ALTER TABLE in NDB Cluster
MySQL NDB Cluster 8.0 supports online table schema changes using
the standard ALTER TABLE
syntax
employed by the MySQL Server
(ALGORITHM=DEFAULT|INPLACE|COPY
), and described
elsewhere.
Some older releases of NDB Cluster used a syntax specific to
NDB
for online ALTER
TABLE
operations. That syntax has since been removed.
Operations that add and drop indexes on variable-width columns of
NDB
tables occur online. Online
operations are noncopying; that is, they do not require that
indexes be re-created. They do not lock the table being altered
from access by other API nodes in an NDB Cluster (but see
Limitations of NDB online operations, later in this
section). Such operations do not require single user mode for
NDB
table alterations made in an NDB
cluster with multiple API nodes; transactions can continue
uninterrupted during online DDL operations.
ALGORITHM=INPLACE
can be used to perform online
ADD COLUMN
, ADD INDEX
(including CREATE INDEX
statements), and
DROP INDEX
operations on
NDB
tables. Online renaming of
NDB
tables is also supported.
Currently you cannot add disk-based columns to
NDB
tables online. This means that,
if you wish to add an in-memory column to an
NDB
table that uses a table-level
STORAGE DISK
option, you must declare the new
column as using memory-based storage explicitly. For
example—assuming that you have already created tablespace
ts1
—suppose that you create table
t1
as follows:
- > )
- > TABLESPACE ts1 STORAGE DISK
- Query OK, 0 rows affected (1.73 sec)
You can add a new in-memory column to this table online as shown here:
- Query OK, 0 rows affected (1.25 sec)
This statement fails if the STORAGE MEMORY
option is omitted:
- ALGORITHM=COPY.
If you omit the COLUMN_FORMAT DYNAMIC
option,
the dynamic column format is employed automatically, but a warning
is issued, as shown here:
The STORAGE
and
COLUMN_FORMAT
keywords are supported only in
NDB Cluster; in any other version of MySQL, attempting to use
either of these keywords in a CREATE
TABLE
or ALTER TABLE
statement results in an error.
It is also possible to use the statement ALTER TABLE ...
REORGANIZE PARTITION, ALGORITHM=INPLACE
with no
option on partition_names
INTO
(partition_definitions
)NDB
tables. This can be
used to redistribute NDB Cluster data among new data nodes that
have been added to the cluster online. This does
not perform any defragmentation, which
requires an OPTIMIZE TABLE
or null
ALTER TABLE
statement. For more
information, see Section 22.5.15, “Adding NDB Cluster Data Nodes Online”.
Limitations of NDB online operations
Online DROP COLUMN
operations are not
supported.
Online ALTER TABLE
,
CREATE INDEX
, or
DROP INDEX
statements that add
columns or add or drop indexes are subject to the following
limitations:
A given online
ALTER TABLE
can use only one ofADD COLUMN
,ADD INDEX
, orDROP INDEX
. One or more columns can be added online in a single statement; only one index may be created or dropped online in a single statement.The table being altered is not locked with respect to API nodes other than the one on which an online
ALTER TABLE
ADD COLUMN
,ADD INDEX
, orDROP INDEX
operation (orCREATE INDEX
orDROP INDEX
statement) is run. However, the table is locked against any other operations originating on the same API node while the online operation is being executed.The table to be altered must have an explicit primary key; the hidden primary key created by the
NDB
storage engine is not sufficient for this purpose.The storage engine used by the table cannot be changed online.
When used with NDB Cluster Disk Data tables, it is not possible to change the storage type (
DISK
orMEMORY
) of a column online. This means, that when you add or drop an index in such a way that the operation would be performed online, and you want the storage type of the column or columns to be changed, you must useALGORITHM=COPY
in the statement that adds or drops the index.
Columns to be added online cannot use the
BLOB
or
TEXT
type, and must meet the
following criteria:
The columns must be dynamic; that is, it must be possible to create them using
COLUMN_FORMAT DYNAMIC
. If you omit theCOLUMN_FORMAT DYNAMIC
option, the dynamic column format is employed automatically.The columns must permit
NULL
values and not have any explicit default value other thanNULL
. Columns added online are automatically created asDEFAULT NULL
, as can be seen here:- Query OK, 0 rows affected (1.44 sec)
- *************************** 1. row ***************************
- Table: t1
The columns must be added following any existing columns. If you attempt to add a column online before any existing columns or using the
FIRST
keyword, the statement fails with an error.Existing table columns cannot be reordered online.
For online ALTER TABLE
operations
on NDB
tables, fixed-format columns
are converted to dynamic when they are added online, or when
indexes are created or dropped online, as shown here (repeating
the CREATE TABLE
and ALTER
TABLE
statements just shown for the sake of clarity):
- Query OK, 0 rows affected (1.44 sec)
- *************************** 1. row ***************************
- Level: Warning
- Code: 1478
- *************************** 2. row ***************************
- Level: Warning
- Code: 1478
Only the column or columns to be added online must be dynamic.
Existing columns need not be; this includes the table's
primary key, which may also be FIXED
, as shown
here:
- Query OK, 0 rows affected (2.10 sec)
- Query OK, 0 rows affected, 1 warning (0.78 sec)
- *************************** 1. row ***************************
- Level: Warning
- Code: 1478
Columns are not converted from FIXED
to
DYNAMIC
column format by renaming operations.
For more information about COLUMN_FORMAT
, see
Section 13.1.20, “CREATE TABLE Syntax”.
The KEY
, CONSTRAINT
, and
IGNORE
keywords are supported in
ALTER TABLE
statements using
ALGORITHM=INPLACE
.
Setting MAX_ROWS
to 0 using an online
ALTER TABLE
statement is disallowed. You must
use a copying ALTER TABLE
to perform this
operation. (Bug #21960004)
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-mysql-cluster-online-operations.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.