Spec-Zone .ru
спецификации, руководства, описания, API
|
|
Spec-Zone .ru
спецификации, руководства, описания, API
|
This section discusses online table schema changes as implemented in MySQL Cluster. The InnoDB storage engine can also perform such operations online, using a different
syntax from that supported by MySQL Cluster; for more information, see Section
5.5, "Online DDL for InnoDB Tables".
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 a
MySQL Cluster (but see Limitations later in this section). Such operations do not
require single user mode for NDB
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 NDB tables. Online renaming of NDB tables is also supported.
The ONLINE and OFFLINE keywords are
supported only in MySQL Cluster. In standard MySQL Server 5.6 releases, attempting to use the ONLINE or OFFLINE keyword in an ALTER TABLE, CREATE INDEX, or DROP INDEX statement results in an error.
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:
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 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: 0mysql>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: t1Create 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=latin11 row in set (0.03 sec)
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.
It is also possible to use the statement ALTER ONLINE TABLE ... REORGANIZE
PARTITION with no partition_names INTO
(partition_definitions)NDB 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".
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 of ADD COLUMN, ADD
INDEX, or DROP 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, or DROP INDEX operation (or CREATE INDEX or DROP 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 MySQL Cluster Disk Data tables, it is not possible to change the
storage type (DISK or MEMORY) 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 use the
OFFLINE keyword 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 the COLUMN_FORMAT
DYNAMIC option, the dynamic column format is employed automatically.
The columns must permit NULL values and not have any
explicit default value other than NULL. Columns added online are
automatically created as DEFAULT NULL, as can be seen here:
mysql>CREATE TABLE t1 (>c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY>) ENGINE=NDB;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)mysql>SHOW CREATE TABLE t1\G*************************** 1. row *************************** Table: t1Create Table: CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` int(11) DEFAULT NULL, `c3` int(11) DEFAULT NULL, PRIMARY KEY (`c1`)) ENGINE=ndbcluster DEFAULT CHARSET=latin11 row in set (0.00 sec)
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.
The preceding limitations do not apply to operations that merely rename tables or columns.
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:
mysql>CREATE TABLE t1 (>c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY>) ENGINE=NDB;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: 0mysql>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)
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=NDB;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: 0mysql>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.