Author: pd221994
Date: 2011-06-08 21:37:09 +0200 (Wed, 08 Jun 2011)
New Revision: 26464
Log:
r48768@dhcp-adc-twvpn-1-vpnpool-10-154-0-199: paul | 2011-06-08 14:30:25 -0500
Move the references to the ALTER TABLE subsections earlier in the section
Reverse order of partitioning and ONLINE subsections
Modified:
svk:merge
trunk/refman-5.1/sql-syntax-data-definition.xml
trunk/refman-5.5/sql-syntax-data-definition.xml
trunk/refman-5.6/sql-syntax-data-definition.xml
trunk/refman-6.0/sql-syntax-data-definition.xml
Property changes on: trunk
___________________________________________________________________
Modified: svk:merge
===================================================================
Changed blocks: 0, Lines Added: 0, Lines Deleted: 0; 1277 bytes
Modified: trunk/refman-5.1/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-5.1/sql-syntax-data-definition.xml 2011-06-08 19:36:59 UTC (rev 26463)
+++ trunk/refman-5.1/sql-syntax-data-definition.xml 2011-06-08 19:37:09 UTC (rev 26464)
Changed blocks: 5, Lines Added: 422, Lines Deleted: 426; 35482 bytes
@@ -833,6 +833,28 @@
also change the comment for the table and type of the table.
</para>
+ <para>
+ A number of partitioning-related extensions to
+ <literal role="stmt">ALTER TABLE</literal> were added in MySQL
+ 5.1.5. These can be used with partitioned tables for
+ repartitioning, for adding, dropping, merging, and splitting
+ partitions, and for performing partitioning maintenance. For more
+ information, see
+ <xref linkend="alter-table-partition-operations"/>.
+ </para>
+
+ <para>
+ The <literal>ONLINE</literal> keyword can be used to perform
+ online <literal>ADD COLUMN</literal>, <literal>ADD INDEX</literal>
+ (including <literal>CREATE INDEX</literal> statements), and
+ <literal>DROP INDEX</literal> operations on
+ <literal role="se">NDBCLUSTER</literal> tables beginning with
+ MySQL Cluster NDB 6.2.5 and MySQL Cluster NDB 6.3.3. Online
+ renaming of <literal role="se">NDBCLUSTER</literal> tables is also
+ supported. For more information, see
+ <xref linkend="alter-table-online-operations"/>.
+ </para>
+
<remark role="help-description-end"/>
<para>
@@ -991,20 +1013,6 @@
<listitem>
<para>
- The <literal>ONLINE</literal> keyword can be used to perform
- online <literal>ADD COLUMN</literal>, <literal>ADD
- INDEX</literal> (including <literal>CREATE INDEX</literal>
- statements), and <literal>DROP INDEX</literal> operations on
- <literal role="se">NDBCLUSTER</literal> tables beginning with
- MySQL Cluster NDB 6.2.5 and MySQL Cluster NDB 6.3.3. Online
- renaming of <literal role="se">NDBCLUSTER</literal> tables is
- also supported. For more information, see
- <xref linkend="alter-table-online-operations"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
<literal>IGNORE</literal> is a MySQL extension to standard
SQL. It controls how <literal role="stmt">ALTER
TABLE</literal> works if there are duplicates on unique keys
@@ -1736,18 +1744,6 @@
</para>
</listitem>
- <listitem>
- <para>
- A number of partitioning-related extensions to
- <literal role="stmt">ALTER TABLE</literal> were added in MySQL
- 5.1.5. These can be used with partitioned tables for
- repartitioning, for adding, dropping, merging, and splitting
- partitions, and for performing partitioning maintenance. For
- more information, see
- <xref linkend="alter-table-partition-operations"/>.
- </para>
- </listitem>
-
</itemizedlist>
<indexterm>
@@ -1762,406 +1758,6 @@
<xref linkend="mysql-info"/>.
</para>
- <section id="alter-table-online-operations">
-
- <title><literal role="stmt">ALTER TABLE</literal> <literal>ONLINE</literal> and
- <literal>OFFLINE</literal> Operations</title>
-
- <para>
- Beginning with MySQL 5.1.7, <literal>ADD INDEX</literal> and
- <literal>DROP INDEX</literal> operations are performed online
- when the indexes are on variable-width columns only.
- </para>
-
- <para>
- The <literal>ONLINE</literal> keyword can be used to perform
- online <literal>ADD COLUMN</literal>, <literal>ADD
- INDEX</literal> (including <literal>CREATE INDEX</literal>
- statements), and <literal>DROP INDEX</literal> operations on
- <literal role="se">NDBCLUSTER</literal> tables beginning with
- MySQL Cluster NDB 6.2.5 and MySQL Cluster NDB 6.3.3. Online
- renaming of <literal role="se">NDBCLUSTER</literal> tables is
- also supported.
- </para>
-
- <para>
- Currently you cannot add disk-based columns to
- <literal role="se">NDBCLUSTER</literal> tables online. This
- means that, if you wish to add an in-memory column to an
- <literal role="se">NDBCLUSTER</literal> table that uses a
- table-level <literal>STORAGE DISK</literal> option, you must
- declare the new column as using memory-based storage explicitly.
- For example—assuming that you have already created
- tablespace <literal>ts1</literal>—suppose that you create
- table <literal>t1</literal> as follows:
- </para>
-
-<programlisting>
-mysql> <userinput>CREATE TABLE t1 (</userinput>
- > <userinput>c1 INT NOT NULL PRIMARY KEY,</userinput>
- > <userinput>c2 VARCHAR(30)</userinput>
- > <userinput>)</userinput>
- > <userinput>TABLESPACE ts1 STORAGE DISK</userinput>
- > <userinput>ENGINE NDBCLUSTER;</userinput>
-Query OK, 0 rows affected (1.73 sec)
-Records: 0 Duplicates: 0 Warnings: 0
-</programlisting>
-
- <para>
- You can add a new in-memory column to this table online as shown
- here:
- </para>
-
-<programlisting>
-mysql> <userinput>ALTER ONLINE TABLE t1 ADD COLUMN c3 INT COLUMN_FORMAT DYNAMIC STORAGE MEMORY;</userinput>
-Query OK, 0 rows affected (1.25 sec)
-Records: 0 Duplicates: 0 Warnings: 0
-</programlisting>
-
- <para>
- This statement fails if the <literal>STORAGE MEMORY</literal>
- option is omitted:
- </para>
-
-<programlisting>
-mysql> <userinput>ALTER ONLINE TABLE t1 ADD COLUMN c3 INT COLUMN_FORMAT DYNAMIC;</userinput>
-<errortext>ERROR 1235 (42000): This version of MySQL doesn't yet support
-'ALTER ONLINE TABLE t1 ADD COLUMN c3 INT COLUMN_FORMAT DYNAMIC'</errortext>
-</programlisting>
-
- <para>
- If you omit the <literal>COLUMN_FORMAT DYNAMIC</literal> option,
- the dynamic column format is employed automatically, but a
- warning is issued, as shown here:
- </para>
-
-<programlisting>
-mysql> <userinput>ALTER ONLINE TABLE t1 ADD COLUMN c3 INT STORAGE MEMORY;</userinput>
-Query OK, 0 rows affected, 1 warning (1.17 sec)
-Records: 0 Duplicates: 0 Warnings: 0
-
-mysql> <userinput>SHOW WARNINGS;</userinput>
-+---------+------+---------------------------------------------------------------+
-| Level | Code | Message |
-+---------+------+---------------------------------------------------------------+
-| Warning | 1478 | Converted FIXED field to DYNAMIC to enable on-line ADD COLUMN |
-+---------+------+---------------------------------------------------------------+
-1 row in set (0.00 sec)
-
-mysql> <userinput>SHOW CREATE TABLE t1\G</userinput>
-*************************** 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)
-</programlisting>
-
- <para>
- 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 <literal>STORAGE DISK</literal>
- option did not work correctly. (Bug#42549)
- </para>
-
- <para>
- It is also possible to rename
- <literal role="se">MyISAM</literal> tables and columns online.
- However, you cannot use <literal>ONLINE</literal> with
- operations that add or drop columns or indexes of
- <literal role="se">MyISAM</literal> tables.
- </para>
-
- <para>
- 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 my other API nodes in a MySQL Cluster (but
- see <citetitle>Limitations</citetitle> later in this section).
- Such operations do not require single user mode for
- <literal role="se">NDBCLUSTER</literal> table alterations made
- in a cluster with multiple API nodes; transactions can continue
- uninterrupted during online DDL operations.
- </para>
-
- <para>
- In MySQL Cluster NDB 7.0 and later, it is also possible to use
- the statement <literal>ALTER ONLINE TABLE ... REORGANIZE
- PARTITION</literal> with no
- <literal><replaceable>partition_names</replaceable> INTO
- (<replaceable>partition_definitions</replaceable>)</literal>
- option on <literal role="se">NDBCLUSTER</literal> tables. This
- can be used to redistribute MySQL Cluster data among new data
- nodes that have been added to the cluster online. More
- information about this statement is given later in this section.
- For more information about adding data nodes online to a MySQL
- Cluster, see <xref linkend="mysql-cluster-online-add-node"/>.
- </para>
-
- <para>
- Prior to MySQL Cluster NDB 6.4.3, <literal>ALTER ONLINE TABLE
- ... REORGANIZE PARTITION</literal> with no
- <literal><replaceable>partition_names</replaceable> INTO
- (<replaceable>partition_definitions</replaceable>)</literal>
- option did not work correctly with Disk Data tables or with
- in-memory <literal role="se">NDBCLUSTER</literal> tables having
- one or more disk-based columns. (Bug#42549)
- </para>
-
- <para>
- The <literal>ONLINE</literal> and <literal>OFFLINE</literal>
- keywords are supported only in MySQL Cluster NDB 6.2, 6.3, 7.0
- (beginning with versions 6.2.5, 6.3.3, and 6.4.0), and later
- MySQL Cluster release series. In other versions of MySQL (5.1.17
- and later):
-
- <orderedlist>
-
- <listitem>
- <para>
- The server determines automatically whether an
- <literal>ADD INDEX</literal> or <literal>DROP
- INDEX</literal> operation can be (and is) performed online
- or offline; if the column is of a variable-width data
- type, then the operation is performed online. It is not
- possible to override the server behavior in this regard.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Attempting to use the <literal>ONLINE</literal> or
- <literal>OFFLINE</literal> keyword in an
- <literal role="stmt">ALTER TABLE</literal> statement
- results in an error.
- </para>
- </listitem>
-
- </orderedlist>
- </para>
-
- <formalpara id="alter-table-online-limitations">
-
- <title>Limitations of online operations</title>
-
- <para>
- Online <literal role="stmt">ALTER TABLE</literal> operations
- that add columns are subject to the following limitations:
- </para>
-
- </formalpara>
-
- <itemizedlist>
-
- <listitem>
- <para>
- The table being altered is not locked with respect to API
- nodes other than the one on which an online
- <literal role="stmt">ALTER TABLE</literal>, <literal>ADD
- COLUMN</literal>, <literal>CREATE INDEX</literal> or
- <literal>DROP INDEX</literal> statement is run. However, the
- table is locked against any other operations originating on
- the <emphasis>same</emphasis> API node while the online
- operation is being executed.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The table to be altered must have an explicit primary key;
- the hidden primary key created by the
- <literal role="se">NDBCLUSTER</literal> storage engine is
- not sufficient for this purpose. Columns to be added online
- must meet the following criteria:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- Such columns must be dynamic; that is, it must be
- possible to create them using <literal>COLUMN_FORMAT
- DYNAMIC</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Such columns must be nullable, and not have any explicit
- default value other than <literal>NULL</literal>.
- Columns added online are automatically created as
- <literal>DEFAULT NULL</literal>, as can be seen here:
- </para>
-
-<programlisting>
-mysql> <userinput>CREATE TABLE t1 (</userinput>
- > <userinput>c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY</userinput>
- > <userinput>) ENGINE=NDBCLUSTER;</userinput>
-Query OK, 0 rows affected (1.44 sec)
-
-mysql> <userinput>ALTER ONLINE TABLE t1</userinput>
- > <userinput>ADD COLUMN c2 INT,</userinput>
- > <userinput>ADD COLUMN c3 INT;</userinput>
-Query OK, 0 rows affected, 2 warnings (0.93 sec)
-
-mysql> <userinput>SHOW CREATE TABLE t2\G</userinput>
-*************************** 1. row ***************************
- Table: t2
-Create Table: CREATE TABLE `t2` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- `c2` int(11) DEFAULT NULL,
- `c3` int(11) DEFAULT NULL,
- PRIMARY KEY (`c1`)
-) ENGINE=ndbcluster DEFAULT CHARSET=latin1
-1 row in set (0.00 sec)
-</programlisting>
- </listitem>
-
- <listitem>
- <para>
- Columns must be added following any existing columns. If
- you attempt to add a column online before any existing
- columns, the statement fails with an error. Trying to
- add a column online using the <literal>FIRST</literal>
- keyword also fails.
- </para>
-
- <para>
- In addition, existing table columns cannot be reordered
- online.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The storage engine used by the table cannot be changed
- online.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- The preceding limitations do not apply to operations that
- merely rename tables or columns.
- </para>
-
- <para>
- If the storage engine supports online
- <literal role="stmt">ALTER TABLE</literal>, then
- fixed-format columns will be converted to dynamic when
- columns are added online, or when indexes are created or
- dropped online, as shown here:
- </para>
-
-<programlisting>
-mysql> <userinput>CREATE TABLE t1 (</userinput>
- > <userinput>c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY</userinput>
- > <userinput>) ENGINE=NDBCLUSTER;</userinput>
-Query OK, 0 rows affected (1.44 sec)
-
-mysql> <userinput>ALTER ONLINE TABLE t1 ADD COLUMN c2 INT, ADD COLUMN c3 INT;</userinput>
-Query OK, 0 rows affected, 2 warnings (0.93 sec)
-Records: 0 Duplicates: 0 Warnings: 0
-
-mysql> <userinput>SHOW WARNINGS;</userinput>
-+---------+------+---------------------------------------------------------------+
-| 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)
-</programlisting>
-
- <note>
- <para>
- Existing columns, including the table's primary key, need
- not be dynamic; only the column or columns to be added
- online must be dynamic.
- </para>
- </note>
-
-<programlisting>
-mysql> <userinput>CREATE TABLE t2 (</userinput>
- > <userinput>c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY COLUMN_FORMAT FIXED</userinput>
- > <userinput>) ENGINE=NDBCLUSTER;</userinput>
-Query OK, 0 rows affected (2.10 sec)
-
-mysql> <userinput>ALTER ONLINE TABLE t2 ADD COLUMN c2 INT;</userinput>
-Query OK, 0 rows affected, 1 warning (0.78 sec)
-Records: 0 Duplicates: 0 Warnings: 0
-
-mysql> <userinput>SHOW WARNINGS;</userinput>
-+---------+------+---------------------------------------------------------------+
-| Level | Code | Message |
-+---------+------+---------------------------------------------------------------+
-| Warning | 1475 | Converted FIXED field to DYNAMIC to enable on-line ADD COLUMN |
-+---------+------+---------------------------------------------------------------+
-1 row in set (0.00 sec)
-</programlisting>
-
- <para>
- Columns are not converted from <literal>FIXED</literal> to
- <literal>DYNAMIC</literal> column format by renaming
- operations. For more information about
- <literal>COLUMN_FORMAT</literal>, see
- <xref linkend="create-table"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- When used with MySQL Cluster Disk Data tables, changing the
- storage type (<literal>DISK</literal> or
- <literal>MEMORY</literal>) of a column cannot be performed
- 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 <literal>OFFLINE</literal> keyword
- in the statement that adds or drops the index.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Online <literal>DROP COLUMN</literal> operations are not
- supported.
- </para>
- </listitem>
-
- <listitem>
- <para>
- A given online <literal role="stmt">ALTER TABLE</literal>
- can use only one of <literal>ADD COLUMN</literal>,
- <literal>ADD INDEX</literal>, or <literal>DROP
- INDEX</literal>. 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.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- The <literal>KEY</literal>, <literal>CONSTRAINT</literal>, and
- <literal>IGNORE</literal> keywords are supported in
- <literal role="stmt">ALTER TABLE</literal> statements using the
- <literal>ONLINE</literal> keyword.
- </para>
-
- <para>
- The <literal>ONLINE</literal> and <literal>OFFLINE</literal>
- keywords are also supported in <literal>ALTER TABLE ... CHANGE
- ...</literal> statements that rename columns of
- <literal role="se">MyISAM</literal> tables.
- </para>
-
- </section>
-
<section id="alter-table-partition-operations">
<title><literal role="stmt">ALTER TABLE</literal> Partition Operations</title>
@@ -2566,6 +2162,406 @@
</section>
+ <section id="alter-table-online-operations">
+
+ <title><literal role="stmt">ALTER TABLE</literal> <literal>ONLINE</literal> and
+ <literal>OFFLINE</literal> Operations</title>
+
+ <para>
+ Beginning with MySQL 5.1.7, <literal>ADD INDEX</literal> and
+ <literal>DROP INDEX</literal> operations are performed online
+ when the indexes are on variable-width columns only.
+ </para>
+
+ <para>
+ The <literal>ONLINE</literal> keyword can be used to perform
+ online <literal>ADD COLUMN</literal>, <literal>ADD
+ INDEX</literal> (including <literal>CREATE INDEX</literal>
+ statements), and <literal>DROP INDEX</literal> operations on
+ <literal role="se">NDBCLUSTER</literal> tables beginning with
+ MySQL Cluster NDB 6.2.5 and MySQL Cluster NDB 6.3.3. Online
+ renaming of <literal role="se">NDBCLUSTER</literal> tables is
+ also supported.
+ </para>
+
+ <para>
+ Currently you cannot add disk-based columns to
+ <literal role="se">NDBCLUSTER</literal> tables online. This
+ means that, if you wish to add an in-memory column to an
+ <literal role="se">NDBCLUSTER</literal> table that uses a
+ table-level <literal>STORAGE DISK</literal> option, you must
+ declare the new column as using memory-based storage explicitly.
+ For example—assuming that you have already created
+ tablespace <literal>ts1</literal>—suppose that you create
+ table <literal>t1</literal> as follows:
+ </para>
+
+<programlisting>
+mysql> <userinput>CREATE TABLE t1 (</userinput>
+ > <userinput>c1 INT NOT NULL PRIMARY KEY,</userinput>
+ > <userinput>c2 VARCHAR(30)</userinput>
+ > <userinput>)</userinput>
+ > <userinput>TABLESPACE ts1 STORAGE DISK</userinput>
+ > <userinput>ENGINE NDBCLUSTER;</userinput>
+Query OK, 0 rows affected (1.73 sec)
+Records: 0 Duplicates: 0 Warnings: 0
+</programlisting>
+
+ <para>
+ You can add a new in-memory column to this table online as shown
+ here:
+ </para>
+
+<programlisting>
+mysql> <userinput>ALTER ONLINE TABLE t1 ADD COLUMN c3 INT COLUMN_FORMAT DYNAMIC STORAGE MEMORY;</userinput>
+Query OK, 0 rows affected (1.25 sec)
+Records: 0 Duplicates: 0 Warnings: 0
+</programlisting>
+
+ <para>
+ This statement fails if the <literal>STORAGE MEMORY</literal>
+ option is omitted:
+ </para>
+
+<programlisting>
+mysql> <userinput>ALTER ONLINE TABLE t1 ADD COLUMN c3 INT COLUMN_FORMAT DYNAMIC;</userinput>
+<errortext>ERROR 1235 (42000): This version of MySQL doesn't yet support
+'ALTER ONLINE TABLE t1 ADD COLUMN c3 INT COLUMN_FORMAT DYNAMIC'</errortext>
+</programlisting>
+
+ <para>
+ If you omit the <literal>COLUMN_FORMAT DYNAMIC</literal> option,
+ the dynamic column format is employed automatically, but a
+ warning is issued, as shown here:
+ </para>
+
+<programlisting>
+mysql> <userinput>ALTER ONLINE TABLE t1 ADD COLUMN c3 INT STORAGE MEMORY;</userinput>
+Query OK, 0 rows affected, 1 warning (1.17 sec)
+Records: 0 Duplicates: 0 Warnings: 0
+
+mysql> <userinput>SHOW WARNINGS;</userinput>
++---------+------+---------------------------------------------------------------+
+| Level | Code | Message |
++---------+------+---------------------------------------------------------------+
+| Warning | 1478 | Converted FIXED field to DYNAMIC to enable on-line ADD COLUMN |
++---------+------+---------------------------------------------------------------+
+1 row in set (0.00 sec)
+
+mysql> <userinput>SHOW CREATE TABLE t1\G</userinput>
+*************************** 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)
+</programlisting>
+
+ <para>
+ 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 <literal>STORAGE DISK</literal>
+ option did not work correctly. (Bug#42549)
+ </para>
+
+ <para>
+ It is also possible to rename
+ <literal role="se">MyISAM</literal> tables and columns online.
+ However, you cannot use <literal>ONLINE</literal> with
+ operations that add or drop columns or indexes of
+ <literal role="se">MyISAM</literal> tables.
+ </para>
+
+ <para>
+ 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 my other API nodes in a MySQL Cluster (but
+ see <citetitle>Limitations</citetitle> later in this section).
+ Such operations do not require single user mode for
+ <literal role="se">NDBCLUSTER</literal> table alterations made
+ in a cluster with multiple API nodes; transactions can continue
+ uninterrupted during online DDL operations.
+ </para>
+
+ <para>
+ In MySQL Cluster NDB 7.0 and later, it is also possible to use
+ the statement <literal>ALTER ONLINE TABLE ... REORGANIZE
+ PARTITION</literal> with no
+ <literal><replaceable>partition_names</replaceable> INTO
+ (<replaceable>partition_definitions</replaceable>)</literal>
+ option on <literal role="se">NDBCLUSTER</literal> tables. This
+ can be used to redistribute MySQL Cluster data among new data
+ nodes that have been added to the cluster online. More
+ information about this statement is given later in this section.
+ For more information about adding data nodes online to a MySQL
+ Cluster, see <xref linkend="mysql-cluster-online-add-node"/>.
+ </para>
+
+ <para>
+ Prior to MySQL Cluster NDB 6.4.3, <literal>ALTER ONLINE TABLE
+ ... REORGANIZE PARTITION</literal> with no
+ <literal><replaceable>partition_names</replaceable> INTO
+ (<replaceable>partition_definitions</replaceable>)</literal>
+ option did not work correctly with Disk Data tables or with
+ in-memory <literal role="se">NDBCLUSTER</literal> tables having
+ one or more disk-based columns. (Bug#42549)
+ </para>
+
+ <para>
+ The <literal>ONLINE</literal> and <literal>OFFLINE</literal>
+ keywords are supported only in MySQL Cluster NDB 6.2, 6.3, 7.0
+ (beginning with versions 6.2.5, 6.3.3, and 6.4.0), and later
+ MySQL Cluster release series. In other versions of MySQL (5.1.17
+ and later):
+
+ <orderedlist>
+
+ <listitem>
+ <para>
+ The server determines automatically whether an
+ <literal>ADD INDEX</literal> or <literal>DROP
+ INDEX</literal> operation can be (and is) performed online
+ or offline; if the column is of a variable-width data
+ type, then the operation is performed online. It is not
+ possible to override the server behavior in this regard.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Attempting to use the <literal>ONLINE</literal> or
+ <literal>OFFLINE</literal> keyword in an
+ <literal role="stmt">ALTER TABLE</literal> statement
+ results in an error.
+ </para>
+ </listitem>
+
+ </orderedlist>
+ </para>
+
+ <formalpara id="alter-table-online-limitations">
+
+ <title>Limitations of online operations</title>
+
+ <para>
+ Online <literal role="stmt">ALTER TABLE</literal> operations
+ that add columns are subject to the following limitations:
+ </para>
+
+ </formalpara>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ The table being altered is not locked with respect to API
+ nodes other than the one on which an online
+ <literal role="stmt">ALTER TABLE</literal>, <literal>ADD
+ COLUMN</literal>, <literal>CREATE INDEX</literal> or
+ <literal>DROP INDEX</literal> statement is run. However, the
+ table is locked against any other operations originating on
+ the <emphasis>same</emphasis> API node while the online
+ operation is being executed.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The table to be altered must have an explicit primary key;
+ the hidden primary key created by the
+ <literal role="se">NDBCLUSTER</literal> storage engine is
+ not sufficient for this purpose. Columns to be added online
+ must meet the following criteria:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Such columns must be dynamic; that is, it must be
+ possible to create them using <literal>COLUMN_FORMAT
+ DYNAMIC</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Such columns must be nullable, and not have any explicit
+ default value other than <literal>NULL</literal>.
+ Columns added online are automatically created as
+ <literal>DEFAULT NULL</literal>, as can be seen here:
+ </para>
+
+<programlisting>
+mysql> <userinput>CREATE TABLE t1 (</userinput>
+ > <userinput>c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY</userinput>
+ > <userinput>) ENGINE=NDBCLUSTER;</userinput>
+Query OK, 0 rows affected (1.44 sec)
+
+mysql> <userinput>ALTER ONLINE TABLE t1</userinput>
+ > <userinput>ADD COLUMN c2 INT,</userinput>
+ > <userinput>ADD COLUMN c3 INT;</userinput>
+Query OK, 0 rows affected, 2 warnings (0.93 sec)
+
+mysql> <userinput>SHOW CREATE TABLE t2\G</userinput>
+*************************** 1. row ***************************
+ Table: t2
+Create Table: CREATE TABLE `t2` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ `c2` int(11) DEFAULT NULL,
+ `c3` int(11) DEFAULT NULL,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1
+1 row in set (0.00 sec)
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ Columns must be added following any existing columns. If
+ you attempt to add a column online before any existing
+ columns, the statement fails with an error. Trying to
+ add a column online using the <literal>FIRST</literal>
+ keyword also fails.
+ </para>
+
+ <para>
+ In addition, existing table columns cannot be reordered
+ online.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The storage engine used by the table cannot be changed
+ online.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ The preceding limitations do not apply to operations that
+ merely rename tables or columns.
+ </para>
+
+ <para>
+ If the storage engine supports online
+ <literal role="stmt">ALTER TABLE</literal>, then
+ fixed-format columns will be converted to dynamic when
+ columns are added online, or when indexes are created or
+ dropped online, as shown here:
+ </para>
+
+<programlisting>
+mysql> <userinput>CREATE TABLE t1 (</userinput>
+ > <userinput>c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY</userinput>
+ > <userinput>) ENGINE=NDBCLUSTER;</userinput>
+Query OK, 0 rows affected (1.44 sec)
+
+mysql> <userinput>ALTER ONLINE TABLE t1 ADD COLUMN c2 INT, ADD COLUMN c3 INT;</userinput>
+Query OK, 0 rows affected, 2 warnings (0.93 sec)
+Records: 0 Duplicates: 0 Warnings: 0
+
+mysql> <userinput>SHOW WARNINGS;</userinput>
++---------+------+---------------------------------------------------------------+
+| 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)
+</programlisting>
+
+ <note>
+ <para>
+ Existing columns, including the table's primary key, need
+ not be dynamic; only the column or columns to be added
+ online must be dynamic.
+ </para>
+ </note>
+
+<programlisting>
+mysql> <userinput>CREATE TABLE t2 (</userinput>
+ > <userinput>c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY COLUMN_FORMAT FIXED</userinput>
+ > <userinput>) ENGINE=NDBCLUSTER;</userinput>
+Query OK, 0 rows affected (2.10 sec)
+
+mysql> <userinput>ALTER ONLINE TABLE t2 ADD COLUMN c2 INT;</userinput>
+Query OK, 0 rows affected, 1 warning (0.78 sec)
+Records: 0 Duplicates: 0 Warnings: 0
+
+mysql> <userinput>SHOW WARNINGS;</userinput>
++---------+------+---------------------------------------------------------------+
+| Level | Code | Message |
++---------+------+---------------------------------------------------------------+
+| Warning | 1475 | Converted FIXED field to DYNAMIC to enable on-line ADD COLUMN |
++---------+------+---------------------------------------------------------------+
+1 row in set (0.00 sec)
+</programlisting>
+
+ <para>
+ Columns are not converted from <literal>FIXED</literal> to
+ <literal>DYNAMIC</literal> column format by renaming
+ operations. For more information about
+ <literal>COLUMN_FORMAT</literal>, see
+ <xref linkend="create-table"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ When used with MySQL Cluster Disk Data tables, changing the
+ storage type (<literal>DISK</literal> or
+ <literal>MEMORY</literal>) of a column cannot be performed
+ 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 <literal>OFFLINE</literal> keyword
+ in the statement that adds or drops the index.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Online <literal>DROP COLUMN</literal> operations are not
+ supported.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ A given online <literal role="stmt">ALTER TABLE</literal>
+ can use only one of <literal>ADD COLUMN</literal>,
+ <literal>ADD INDEX</literal>, or <literal>DROP
+ INDEX</literal>. 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.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ The <literal>KEY</literal>, <literal>CONSTRAINT</literal>, and
+ <literal>IGNORE</literal> keywords are supported in
+ <literal role="stmt">ALTER TABLE</literal> statements using the
+ <literal>ONLINE</literal> keyword.
+ </para>
+
+ <para>
+ The <literal>ONLINE</literal> and <literal>OFFLINE</literal>
+ keywords are also supported in <literal>ALTER TABLE ... CHANGE
+ ...</literal> statements that rename columns of
+ <literal role="se">MyISAM</literal> tables.
+ </para>
+
+ </section>
+
<section id="alter-table-examples">
<title><literal role="stmt">ALTER TABLE</literal> Examples</title>
Modified: trunk/refman-5.5/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-5.5/sql-syntax-data-definition.xml 2011-06-08 19:36:59 UTC (rev 26463)
+++ trunk/refman-5.5/sql-syntax-data-definition.xml 2011-06-08 19:37:09 UTC (rev 26464)
Changed blocks: 2, Lines Added: 9, Lines Deleted: 11; 1357 bytes
@@ -656,6 +656,15 @@
also change the comment for the table and type of the table.
</para>
+ <para>
+ Partitioning-related clauses for <literal role="stmt">ALTER
+ TABLE</literal> can be used with partitioned tables for
+ repartitioning, for adding, dropping, merging, and splitting
+ partitions, and for performing partitioning maintenance. For more
+ information, see
+ <xref linkend="alter-table-partition-operations"/>.
+ </para>
+
<remark role="help-description-end"/>
<para>
@@ -1536,17 +1545,6 @@
</para>
</listitem>
- <listitem>
- <para>
- Partitioning-related clauses for <literal role="stmt">ALTER
- TABLE</literal> can be used with partitioned tables for
- repartitioning, for adding, dropping, merging, and splitting
- partitions, and for performing partitioning maintenance. For
- more information, see
- <xref linkend="alter-table-partition-operations"/>.
- </para>
- </listitem>
-
</itemizedlist>
<indexterm>
Modified: trunk/refman-5.6/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-5.6/sql-syntax-data-definition.xml 2011-06-08 19:36:59 UTC (rev 26463)
+++ trunk/refman-5.6/sql-syntax-data-definition.xml 2011-06-08 19:37:09 UTC (rev 26464)
Changed blocks: 2, Lines Added: 9, Lines Deleted: 11; 1357 bytes
@@ -657,6 +657,15 @@
also change the comment for the table and type of the table.
</para>
+ <para>
+ Partitioning-related clauses for <literal role="stmt">ALTER
+ TABLE</literal> can be used with partitioned tables for
+ repartitioning, for adding, dropping, merging, and splitting
+ partitions, and for performing partitioning maintenance. For more
+ information, see
+ <xref linkend="alter-table-partition-operations"/>.
+ </para>
+
<remark role="help-description-end"/>
<para>
@@ -1537,17 +1546,6 @@
</para>
</listitem>
- <listitem>
- <para>
- Partitioning-related clauses for <literal role="stmt">ALTER
- TABLE</literal> can be used with partitioned tables for
- repartitioning, for adding, dropping, merging, and splitting
- partitions, and for performing partitioning maintenance. For
- more information, see
- <xref linkend="alter-table-partition-operations"/>.
- </para>
- </listitem>
-
</itemizedlist>
<indexterm>
Modified: trunk/refman-6.0/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-6.0/sql-syntax-data-definition.xml 2011-06-08 19:36:59 UTC (rev 26463)
+++ trunk/refman-6.0/sql-syntax-data-definition.xml 2011-06-08 19:37:09 UTC (rev 26464)
Changed blocks: 2, Lines Added: 9, Lines Deleted: 11; 1357 bytes
@@ -655,6 +655,15 @@
also change the comment for the table and type of the table.
</para>
+ <para>
+ Partitioning-related clauses for <literal role="stmt">ALTER
+ TABLE</literal> can be used with partitioned tables for
+ repartitioning, for adding, dropping, merging, and splitting
+ partitions, and for performing partitioning maintenance. For more
+ information, see
+ <xref linkend="alter-table-partition-operations"/>.
+ </para>
+
<remark role="help-description-end"/>
<para>
@@ -1556,17 +1565,6 @@
</para>
</listitem>
- <listitem>
- <para>
- Partitioning-related clauses for <literal role="stmt">ALTER
- TABLE</literal> can be used with partitioned tables for
- repartitioning, for adding, dropping, merging, and splitting
- partitions, and for performing partitioning maintenance. For
- more information, see
- <xref linkend="alter-table-partition-operations"/>.
- </para>
- </listitem>
-
</itemizedlist>
<indexterm>
| Thread |
|---|
| • svn commit - mysqldoc@oter02: r26464 - in trunk: . refman-5.1 refman-5.5 refman-5.6 refman-6.0 | paul.dubois | 9 Jun |
