Movatterモバイル変換

MySQL NDB Cluster supports online table schema changes usingALTER TABLE ... ALGORITHM=DEFAULT|INPLACE|COPY. NDB Cluster handlesCOPY andINPLACE as described in the next few paragraphs.

ForALGORITHM=COPY, themysqld NDB Cluster handler performs the following actions:

We sometimes refer to this as a“copying” or“offline”ALTER TABLE.

DML operations are not permitted concurrently with a copyingALTER TABLE.

Themysqld on which the copyingALTER TABLE statement is issued takes a metadata lock, but this is in effect only on thatmysqld. OtherNDB clients can modify row data during a copyingALTER TABLE, resulting in inconsistency.

ForALGORITHM=INPLACE, the NDB Cluster handler tells the data nodes to make the required changes, and does not perform any copying of data.

We also refer to this as a“non-copying” or“online”ALTER TABLE.

A non-copyingALTER TABLE allows concurrent DML operations.

ALGORITHM=INSTANT is not supported by NDB 9.0.

Regardless of the algorithm used, themysqld takes a Global Schema Lock (GSL) while executingALTER TABLE; this prevents execution of any (other) DDL or backups concurrently on this or any other SQL node in the cluster. This is normally not problematic, unless theALTER TABLE takes a very long time.

Operations that add and drop indexes on variable-width columns ofNDB 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 seeLimitations of NDB online operations, later in this section). Such operations do not require single user mode forNDB 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 onlineADD COLUMN,ADD INDEX (includingCREATE INDEX statements), andDROP INDEX operations onNDB tables. Online renaming ofNDB tables is also supported.

Disk-based columns cannot be added toNDB tables online. This means that, if you wish to add an in-memory column to anNDB table that uses a table-levelSTORAGE DISK option, you must declare the new column as using memory-based storage explicitly. For example—assuming that you have already created tablespacets1—suppose that you create tablet1 as follows:

mysql> CREATE TABLE t1 (     >     c1 INT NOT NULL PRIMARY KEY,     >     c2 VARCHAR(30)     >     )     >     TABLESPACE ts1 STORAGE DISK     >     ENGINE NDB;Query OK, 0 rows affected (1.73 sec)Records: 0  Duplicates: 0  Warnings: 0

You can add a new in-memory column to this table online as shown here:

mysql> ALTER TABLE t1     >     ADD COLUMN c3 INT COLUMN_FORMAT DYNAMIC STORAGE MEMORY,     >     ALGORITHM=INPLACE;Query OK, 0 rows affected (1.25 sec)Records: 0  Duplicates: 0  Warnings: 0

This statement fails if theSTORAGE MEMORY option is omitted:

mysql> ALTER TABLE t1     >     ADD COLUMN c4 INT COLUMN_FORMAT DYNAMIC,     >     ALGORITHM=INPLACE;ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason:Adding column(s) or add/reorganize partition not supported online. TryALGORITHM=COPY.

If you omit theCOLUMN_FORMAT DYNAMIC option, the dynamic column format is employed automatically, but a warning is issued, as shown here:

mysql> ALTER ONLINE TABLE t1 ADD COLUMN c4 INT STORAGE MEMORY;Query OK, 0 rows affected, 1 warning (1.17 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> SHOW WARNINGS\G*************************** 1. row ***************************  Level: Warning   Code: 1478Message: DYNAMIC column c4 with STORAGE DISK is not supported, column willbecome FIXEDmysql> SHOW CREATE TABLE t1\G*************************** 1. row ***************************       Table: t1Create Table: CREATE TABLE `t1` (  `c1` int(11) NOT NULL,  `c2` varchar(30) DEFAULT NULL,  `c3` int(11) /*!50606 STORAGE MEMORY */ /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL,  `c4` int(11) /*!50606 STORAGE MEMORY */ DEFAULT NULL,  PRIMARY KEY (`c1`)) /*!50606 TABLESPACE ts_1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.03 sec)

It is also possible to use the statementALTER TABLE ... REORGANIZE PARTITION, ALGORITHM=INPLACE with nopartition_names INTO (partition_definitions) option onNDB tables. This can be used to redistribute NDB Cluster data among new data nodes that have been added to the cluster online. This doesnot perform any defragmentation, which requires anOPTIMIZE TABLE or nullALTER TABLE statement. For more information, seeSection 25.6.7, “Adding NDB Cluster Data Nodes Online”.

Limitations of NDB online operations

OnlineDROP COLUMN operations are not supported.

OnlineALTER TABLE,CREATE INDEX, orDROP INDEX statements that add columns or add or drop indexes are subject to the following limitations:

Columns to be added online cannot use theBLOB orTEXT type, and must meet the following criteria:

For onlineALTER TABLE operations onNDB 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 theCREATE TABLE andALTER TABLE statements just shown for the sake of clarity):

mysql> CREATE TABLE t2 (     >     c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY     >     ) ENGINE=NDB;Query OK, 0 rows affected (1.44 sec)mysql> ALTER TABLE t2     >     ADD COLUMN c2 INT,     >     ADD COLUMN c3 INT,     >     ALGORITHM=INPLACE;Query OK, 0 rows affected, 2 warnings (0.93 sec)mysql> SHOW WARNINGS;*************************** 1. row ***************************  Level: Warning   Code: 1478Message: Converted FIXED field 'c2' to DYNAMIC to enable online ADD COLUMN*************************** 2. row ***************************  Level: Warning   Code: 1478Message: Converted FIXED field 'c3' to DYNAMIC to enable online ADD COLUMN2 rows in set (0.00 sec)

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 beFIXED, as shown here:

mysql> CREATE TABLE t3 (     >     c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY COLUMN_FORMAT FIXED     >     ) ENGINE=NDB;Query OK, 0 rows affected (2.10 sec)mysql> ALTER TABLE t3 ADD COLUMN c2 INT, ALGORITHM=INPLACE;Query OK, 0 rows affected, 1 warning (0.78 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> SHOW WARNINGS;*************************** 1. row ***************************  Level: Warning   Code: 1478Message: Converted FIXED field 'c2' to DYNAMIC to enable online ADD COLUMN1 row in set (0.00 sec)

Columns are not converted fromFIXED toDYNAMIC column format by renaming operations. For more information aboutCOLUMN_FORMAT, seeSection 15.1.20, “CREATE TABLE Statement”.

TheKEY,CONSTRAINT, andIGNORE keywords are supported inALTER TABLE statements usingALGORITHM=INPLACE.

SettingMAX_ROWS to 0 using an onlineALTER TABLE statement is disallowed. You must use a copyingALTER TABLE to perform this operation. (Bug #21960004)