13.1.7.2 ALTER TABLE Online Operations in MySQL Cluster

Beginning with MySQL 5.1.17, operations that add and drop indexes on variable-width columns of NDBCLUSTER 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 a MySQL Cluster (but see Limitations later in this section). Such operations do not require single user mode for NDBCLUSTER table alterations made in a cluster with multiple API nodes; transactions can continue uninterrupted during online DDL operations.

The ONLINE keyword can be used to perform online ADD COLUMN, ADD INDEX (including CREATE INDEX statements), and DROP INDEX operations on NDBCLUSTER tables. Online renaming of NDBCLUSTER tables is also supported.

The ONLINE and OFFLINE keywords are supported only in MySQL Cluster NDB 6.2 (beginning with 6.2.5) and 6.3 (beginning with 6.3.2) or later. For earlier MySQL Cluster releases or standard MySQL 5.1 releases:

Currently you cannot add disk-based columns to NDBCLUSTER tables online. This means that, if you wish to add an in-memory column to an NDBCLUSTER 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:

mysql> CREATE TABLE t1 (
     >     c1 INT NOT NULL PRIMARY KEY,
     >     c2 VARCHAR(30)
     >     )
     >     TABLESPACE ts1 STORAGE DISK
     >     ENGINE NDBCLUSTER;
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 ONLINE TABLE t1 ADD COLUMN c3 INT COLUMN_FORMAT DYNAMIC STORAGE MEMORY;
Query OK, 0 rows affected (1.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

This statement fails if the STORAGE MEMORY option is omitted:

mysql> ALTER ONLINE TABLE t1 ADD COLUMN c3 INT COLUMN_FORMAT DYNAMIC;
ERROR 1235 (42000): This version of MySQL doesn't yet support
'ALTER ONLINE TABLE t1 ADD COLUMN c3 INT COLUMN_FORMAT DYNAMIC'

If you omit the COLUMN_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 c3 INT STORAGE MEMORY;
Query OK, 0 rows affected, 1 warning (1.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1478 | Converted FIXED field to DYNAMIC to enable on-line ADD COLUMN |
+---------+------+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int(11) NOT NULL,
  `c2` varchar(30) DEFAULT NULL,
  `c3` int(11) /*!50120 STORAGE MEMORY */ /*!50120 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL,
  `t4` int(11) /*!50120 STORAGE MEMORY */ DEFAULT NULL,
  PRIMARY KEY (`c1`)
) /*!50100 TABLESPACE ts_1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.03 sec)

Prior to MySQL Cluster NDB 6.2.17, 6.3.23, and 6.4.3, adding in-memory columns to tables that were created using a table-level or column-level STORAGE DISK option did not work correctly. (Bug #42549)

Note

The STORAGE and COLUMN_FORMAT keywords are supported only in MySQL 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.

In MySQL Cluster NDB 7.0 and later, it is also possible to use the statement ALTER ONLINE TABLE ... REORGANIZE PARTITION with no partition_names INTO (partition_definitions) option on NDBCLUSTER tables. This can be used to redistribute MySQL Cluster data among new data nodes that have been added to the cluster online. For more information about this statement, see Section 13.1.7.1, “ALTER TABLE Partition Operations” For more information about adding data nodes online to a MySQL Cluster, see Section 17.5.13, “Adding MySQL Cluster Data Nodes Online”.

Prior to MySQL Cluster NDB 6.4.3, ALTER ONLINE TABLE ... REORGANIZE PARTITION with no partition_names INTO (partition_definitions) option did not work correctly with Disk Data tables or with in-memory NDBCLUSTER tables having one or more disk-based columns. (Bug #42549)

Limitations of NDBCLUSTER 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:

Columns to be added online cannot use the BLOB or TEXT type, and must meet the following criteria:

The preceding limitations do not apply to operations that merely rename tables or columns.

For online ALTER TABLE operations on NDBCLUSTER tables, fixed-format columns are converted to dynamic when they are added online, or when indexes are created or dropped online, as shown here:

mysql> CREATE TABLE t1 (
     >     c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY
     >     ) ENGINE=NDBCLUSTER;
Query OK, 0 rows affected (1.44 sec)

mysql> ALTER ONLINE TABLE t1 ADD COLUMN c2 INT, ADD COLUMN c3 INT;
Query OK, 0 rows affected, 2 warnings (0.93 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1475 | Converted FIXED field to DYNAMIC to enable on-line ADD COLUMN |
| Warning | 1475 | Converted FIXED field to DYNAMIC to enable on-line ADD COLUMN |
+---------+------+---------------------------------------------------------------+
2 rows in set (0.00 sec)
Note

Existing columns, including the table's primary key, need not be dynamic; only the column or columns to be added online must be dynamic.

mysql> CREATE TABLE t2 (
     >     c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY COLUMN_FORMAT FIXED
     >     ) ENGINE=NDBCLUSTER;
Query OK, 0 rows affected (2.10 sec)

mysql> ALTER ONLINE TABLE t2 ADD COLUMN c2 INT;
Query OK, 0 rows affected, 1 warning (0.78 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1475 | Converted FIXED field to DYNAMIC to enable on-line ADD COLUMN |
+---------+------+---------------------------------------------------------------+
1 row in set (0.00 sec)

Columns are not converted from FIXED to DYNAMIC column format by renaming operations. For more information about COLUMN_FORMAT, see Section 13.1.17, “CREATE TABLE Syntax”.

The KEY, CONSTRAINT, and IGNORE keywords are supported in ALTER TABLE statements using the ONLINE keyword.