List:Commits« Previous MessageNext Message »
From:paul.dubois Date:June 8 2011 7:36pm
Subject:svn commit - mysqldoc@oter02: r26460 - in trunk: . refman-5.1
View as plain text  
Author: pd221994
Date: 2011-06-08 21:36:35 +0200 (Wed, 08 Jun 2011)
New Revision: 26460

Log:
 r48764@dhcp-adc-twvpn-1-vpnpool-10-154-0-199:  paul | 2011-06-08 13:54:00 -0500
 Put ONLINE/OFFLINE material for ALTER TABLE in own subsection


Modified:
   svk:merge
   trunk/refman-5.1/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:28 UTC (rev 26459)
+++ trunk/refman-5.1/sql-syntax-data-definition.xml	2011-06-08 19:36:35 UTC (rev 26460)
Changed blocks: 3, Lines Added: 402, Lines Deleted: 386; 33079 bytes

@@ -991,12 +991,6 @@
 
       <listitem>
         <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>

@@ -1004,387 +998,9 @@
           <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.
+          also supported. For more information, see
+          <xref linkend="alter-table-online"/>.
         </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&mdash;assuming that you have already
-          created tablespace <literal>ts1</literal>&mdash;suppose that
-          you create table <literal>t1</literal> as follows:
-        </para>
-
-<programlisting>
-mysql&gt; <userinput>CREATE TABLE t1 (</userinput>
-     &gt;     <userinput>c1 INT NOT NULL PRIMARY KEY,</userinput>
-     &gt;     <userinput>c2 VARCHAR(30)</userinput>
-     &gt;     <userinput>)</userinput>
-     &gt;     <userinput>TABLESPACE ts1 STORAGE DISK</userinput>
-     &gt;     <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&gt; <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&gt; <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&gt; <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&gt; <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&gt; <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&gt; <userinput>CREATE TABLE t1 (</userinput>
-     &gt;     <userinput>c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY</userinput>
-     &gt;     <userinput>) ENGINE=NDBCLUSTER;</userinput>
-Query OK, 0 rows affected (1.44 sec)
-
-mysql&gt; <userinput>ALTER ONLINE TABLE t1</userinput>
-     &gt;     <userinput>ADD COLUMN c2 INT,</userinput>
-     &gt;     <userinput>ADD COLUMN c3 INT;</userinput>
-Query OK, 0 rows affected, 2 warnings (0.93 sec)
-
-mysql&gt; <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&gt; <userinput>CREATE TABLE t1 (</userinput>
-     &gt;     <userinput>c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY</userinput>
-     &gt;     <userinput>) ENGINE=NDBCLUSTER;</userinput>
-Query OK, 0 rows affected (1.44 sec)
-
-mysql&gt; <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&gt; <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&gt; <userinput>CREATE TABLE t2 (</userinput>
-     &gt;     <userinput>c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY COLUMN_FORMAT FIXED</userinput>
-     &gt;     <userinput>) ENGINE=NDBCLUSTER;</userinput>
-Query OK, 0 rows affected (2.10 sec)
-
-mysql&gt; <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&gt; <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>
       </listitem>
 
       <listitem>

@@ -2523,6 +2139,406 @@
       <xref linkend="mysql-info"/>.
     </para>
 
+    <section id="alter-table-online-operations">
+
+      <title><literal>ONLINE</literal> and <literal>OFFLINE</literal>
+        <literal role="stmt">ALTER TABLE</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&mdash;assuming that you have already created
+        tablespace <literal>ts1</literal>&mdash;suppose that you create
+        table <literal>t1</literal> as follows:
+      </para>
+
+<programlisting>
+mysql&gt; <userinput>CREATE TABLE t1 (</userinput>
+     &gt;     <userinput>c1 INT NOT NULL PRIMARY KEY,</userinput>
+     &gt;     <userinput>c2 VARCHAR(30)</userinput>
+     &gt;     <userinput>)</userinput>
+     &gt;     <userinput>TABLESPACE ts1 STORAGE DISK</userinput>
+     &gt;     <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&gt; <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&gt; <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&gt; <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&gt; <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&gt; <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&gt; <userinput>CREATE TABLE t1 (</userinput>
+     &gt;     <userinput>c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY</userinput>
+     &gt;     <userinput>) ENGINE=NDBCLUSTER;</userinput>
+Query OK, 0 rows affected (1.44 sec)
+
+mysql&gt; <userinput>ALTER ONLINE TABLE t1</userinput>
+     &gt;     <userinput>ADD COLUMN c2 INT,</userinput>
+     &gt;     <userinput>ADD COLUMN c3 INT;</userinput>
+Query OK, 0 rows affected, 2 warnings (0.93 sec)
+
+mysql&gt; <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&gt; <userinput>CREATE TABLE t1 (</userinput>
+     &gt;     <userinput>c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY</userinput>
+     &gt;     <userinput>) ENGINE=NDBCLUSTER;</userinput>
+Query OK, 0 rows affected (1.44 sec)
+
+mysql&gt; <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&gt; <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&gt; <userinput>CREATE TABLE t2 (</userinput>
+     &gt;     <userinput>c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY COLUMN_FORMAT FIXED</userinput>
+     &gt;     <userinput>) ENGINE=NDBCLUSTER;</userinput>
+Query OK, 0 rows affected (2.10 sec)
+
+mysql&gt; <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&gt; <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>


Thread
svn commit - mysqldoc@oter02: r26460 - in trunk: . refman-5.1paul.dubois9 Jun