Author: jstephens
Date: 2008-07-11 18:43:52 +0200 (Fri, 11 Jul 2008)
New Revision: 11217
Log:
Fix for Docs Bug #37063, Part 3 (last part):
Fixed ALTER TABLE Syntax and CREATE TABLE Syntax
Also removed or commented out mention of ONLINE|OFFLINE and
COLUMN_FORMAT in 6.0 version for now since there are no current
6.0-based MySQL Cluster releases
Modified:
trunk/refman-5.1/sql-syntax.xml
trunk/refman-6.0/sql-syntax.xml
Modified: trunk/refman-5.1/sql-syntax.xml
===================================================================
--- trunk/refman-5.1/sql-syntax.xml 2008-07-11 16:35:59 UTC (rev 11216)
+++ trunk/refman-5.1/sql-syntax.xml 2008-07-11 16:43:52 UTC (rev 11217)
Changed blocks: 8, Lines Added: 71, Lines Deleted: 26; 7813 bytes
@@ -408,10 +408,10 @@
<remark role="help-keywords">
ALTER TABLE ADD AFTER AVG_ROW_LENGTH BY COLUMN FIRST INDEX
PRIMARY KEY KEYS UNIQUE FULLTEXT SPATIAL CONSTRAINT DROP DISABLE
- ENABLE RENAME ORDER MODIFY CHANGE DEFAULT SET FOREIGN IGNORE
- ENGINE TYPE WITH PARSER DISCARD IMPORT TABLESPACE PARTITION
- COALESCE REORGANIZE ANALYZE CHECK OFFLINE ONLINE OPTIMIZE
- REBUILD REPAIR REMOVE
+ ENABLE RENAME ONLINE OFFLINE ORDER MODIFY CHANGE DEFAULT SET
+ FOREIGN IGNORE ENGINE TYPE WITH PARSER DISCARD IMPORT TABLESPACE
+ PARTITION COALESCE REORGANIZE ANALYZE CHECK OFFLINE ONLINE
+ OPTIMIZE REBUILD REPAIR REMOVE
</remark>
<remark role="help-syntax"/>
@@ -635,11 +635,18 @@
<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>, and <literal>DROP INDEX</literal>
- operations on <literal>NDB</literal> tables, as well as
- online renaming of tables and columns. Online renaming
+ operations on <literal>NDB</literal> tables beginning with
+ MySQL Cluster NDB 6.2.5 and MySQL Cluster NDB 6.3.3, as well
+ as online renaming of tables and columns. Online renaming
operations (but not online adding or dropping of columns or
indexes) are also supported for <literal>MyISAM</literal>
tables. Online operations are non-copying; that is, they do
@@ -651,14 +658,45 @@
operations.
</para>
+ <para>
+ The <literal>ONLINE</literal> and <literal>OFFLINE</literal>
+ keywords are supported only in MySQL Cluster NDB 6.2 and 6.3
+ (beginning with versions 6.2.5 and 6.3.3). 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>ALTER TABLE</literal> statement results in an
+ error.
+ </para>
+ </listitem>
+
+ </orderedlist>
+ </para>
+
<formalpara>
<title>Limitations</title>
<para>
Online <literal>ALTER TABLE</literal> operations that add
- columns or that add or drop indexes are subject to the
- following limitations:
+ columns are subject to the following limitations:
<itemizedlist>
@@ -675,26 +713,30 @@
<listitem>
<para>
- They must be dynamic; that is, it must be
- possible to create them using
+ Such columns must be dynamic; that is, it must
+ be possible to create them using
<literal>COLUMN_FORMAT DYNAMIC</literal>.
</para>
</listitem>
<listitem>
<para>
- They must be nullable, and not have any explicit
- default value other than
+ 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 (c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=NDB;</userinput>
+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>
+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>
@@ -775,7 +817,9 @@
</note>
<programlisting>
-mysql> <userinput>CREATE TABLE t2 (c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY COLUMN_FORMAT FIXED) ENGINE=NDB;</userinput>
+mysql> <userinput>CREATE TABLE t2 (</userinput>
+ > <userinput>c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY COLUMN_FORMAT FIXED</userinput>
+ > <userinput>) ENGINE=NDB;</userinput>
Query OK, 0 rows affected (2.10 sec)
mysql> <userinput>ALTER ONLINE TABLE t2 ADD COLUMN c2 INT;</userinput>
@@ -832,8 +876,9 @@
</para>
<para>
- The <literal>ONLINE</literal> keyword was added in MySQL
- Cluster NDB 6.2.5 and MySQL Cluster NDB 6.3.2.
+ The <literal>ONLINE</literal> and <literal>OFFLINE</literal>
+ keywords are also supported in <literal>ALTER TABLE ...
+ CHANGE ...</literal> statements that rename columns.
</para>
<note>
@@ -2978,13 +3023,13 @@
<remark role="help-keywords">
CREATE TABLE ENGINE KEY DATA ACTION ARCHIVE AVG_ROW_LENGTH BY
- CASCADE CHECK CHECKSUM COMMENT COMPACT CONSTRAINT COMPRESSED
- CONNECTION CSV FEDERATED FIXED DYNAMIC DEFAULT DELAY_KEY_WRITE
- DELETE DIRECTORY INDEX FIRST FOREIGN ROW_FORMAT FULL FULLTEXT
- HEAP INNOBASE INNODB INSERT_METHOD KEY_BLOCK_SIZE MAX_ROWS
- MIN_ROWS PACK_KEYS PARTIAL PARTITION MERGE MRG_MYISAM MYISAM NDB
- NDBCLUSTER NO REDUNDANT REFERENCES SERIAL WITH PARSER CHARACTER
- SET COLLATE
+ CASCADE CHECK CHECKSUM COLUMN_FORMAT COMMENT COMPACT CONSTRAINT
+ COMPRESSED CONNECTION CSV FEDERATED FIXED DYNAMIC DEFAULT
+ DELAY_KEY_WRITE DELETE DIRECTORY INDEX FIRST FOREIGN ROW_FORMAT
+ FULL FULLTEXT HEAP INNOBASE INNODB INSERT_METHOD KEY_BLOCK_SIZE
+ MAX_ROWS MIN_ROWS PACK_KEYS PARTIAL PARTITION MERGE MRG_MYISAM
+ MYISAM NDB NDBCLUSTER NO REDUNDANT REFERENCES SERIAL WITH PARSER
+ CHARACTER SET COLLATE
</remark>
<remark role="help-syntax-begin"/>
@@ -3498,8 +3543,8 @@
</para>
<para>
- <literal>COLUMN_FORMAT</literal> has no effect on columns of
- tables using storage engines other than
+ <literal>COLUMN_FORMAT</literal> currently has no effect on
+ columns of tables using storage engines other than
<literal>NDB</literal>.
</para>
</listitem>
Modified: trunk/refman-6.0/sql-syntax.xml
===================================================================
--- trunk/refman-6.0/sql-syntax.xml 2008-07-11 16:35:59 UTC (rev 11216)
+++ trunk/refman-6.0/sql-syntax.xml 2008-07-11 16:43:52 UTC (rev 11217)
Changed blocks: 13, Lines Added: 37, Lines Deleted: 199; 14402 bytes
@@ -399,14 +399,13 @@
PRIMARY KEY KEYS UNIQUE FULLTEXT SPATIAL CONSTRAINT DROP DISABLE
ENABLE RENAME ORDER MODIFY CHANGE DEFAULT SET FOREIGN IGNORE
ENGINE TYPE WITH PARSER DISCARD IMPORT TABLESPACE PARTITION
- COALESCE REORGANIZE ANALYZE CHECK OFFLINE ONLINE OPTIMIZE
- REBUILD REPAIR REMOVE
+ COALESCE REORGANIZE ANALYZE CHECK OPTIMIZE REBUILD REPAIR REMOVE
</remark>
<remark role="help-syntax"/>
<programlisting>
-ALTER [ONLINE | OFFLINE] [IGNORE] TABLE <replaceable>tbl_name</replaceable>
+ALTER [IGNORE] TABLE <replaceable>tbl_name</replaceable>
<replaceable>alter_specification</replaceable> [, <replaceable>alter_specification</replaceable>] ...
<replaceable>alter_specification</replaceable>:
@@ -624,189 +623,30 @@
<listitem>
<para>
- The <literal>ONLINE</literal> keyword can be used to perform
- online <literal>ADD COLUMN</literal>, <literal>ADD
- INDEX</literal>, and <literal>DROP INDEX</literal>
- operations on <literal>NDB</literal> tables. Online adding
- and dropping of indexes is also supported for
- <literal>MyISAM</literal> and <literal>Falcon</literal>
- tables. Online operations are non-copying; that is, they do
- not require that indexes be re-created. Such operations do
- not require single user mode for <literal>NDB</literal>
- table alterations made in a cluster with multiple API nodes;
- transactions can continue uninterrupted during online DDL
- operations.
+ <literal>ADD INDEX</literal> and <literal>DROP
+ INDEX</literal> operations are performed online when the
+ indexes are on variable-width columns only.
</para>
- <formalpara>
-
- <title>Limitations</title>
-
- <para>
- Online <literal>ALTER TABLE</literal> operations are
- subject to the following limitations:
-
- <itemizedlist>
-
- <listitem>
- <para>
- The table to be altered must have an explicit
- primary key; the hidden primary key created by the
- <literal>NDB</literal> storage engine is not
- sufficient for this purpose. Columns to be added
- online must meet the following criteria:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- They must be dynamic; that is, it must be
- possible to create them using
- <literal>COLUMN_FORMAT DYNAMIC</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- They 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 (c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=NDB;</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)
-
-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>
- </listitem>
-
- </itemizedlist>
-
- <para>
- If the storage engine supports online <literal>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 (c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=NDB;</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>
-
- <para>
- (Note that existing columns, including the table's
- primary key, need not be dynamic; only a column to
- be added online must be dynamic.)
- </para>
-
-<programlisting>
-mysql> <userinput>CREATE TABLE t2 (c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY COLUMN_FORMAT FIXED) ENGINE=NDB;</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>
- For more information about
- <literal>COLUMN_FORMAT</literal>, see
- <xref linkend="create-table"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Online <literal>DROP COLUMN</literal> operations are
- not supported.
- </para>
- </listitem>
-
- <listitem>
- <para>
- A given online <literal>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>
-
- </formalpara>
-
<para>
- The <literal>KEY</literal>, <literal>CONSTRAINT</literal>,
- and <literal>IGNORE</literal> keywords are supported in
- <literal>ALTER TABLE</literal> statements using the
- <literal>ONLINE</literal> keyword.
+ Online operations are non-copying; that is, they do not
+ require that indexes be re-created.
</para>
<para>
- The <literal>ONLINE</literal> keyword was added in MySQL
- 5.2.6.
+ 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>
<note>
<para>
The <literal>CREATE INDEX</literal> and <literal>DROP
- INDEX</literal> statements also support online operations,
- as well as the <literal>ONLINE</literal> and
- <literal>OFFLINE</literal> keywords. See
- <xref linkend="create-index"/>, and
+ INDEX</literal> statements also support online operations.
+ See <xref linkend="create-index"/>, and
<xref linkend="drop-index"/>, for more information.
</para>
</note>
@@ -2348,17 +2188,6 @@
<xref linkend="alter-table"/>.
</para>
- <note>
- <para>
- The <literal>ONLINE</literal> and <literal>OFFLINE</literal>
- keywords are available only in MySQL Cluster NDB 6.2 and MySQL
- Cluster NDB 6.3 releases beginning with versions 6.2.5 and
- 6.3.3, respectively; attempting to use them in earlier MySQL
- Cluster NDB 6.2 or 6.3 releases, standard MySQL 5.1 releases,
- or MySQL Cluster NDB 6.1 releases results in a syntax error.
- </para>
- </note>
-
<para>
A <literal>UNIQUE</literal> index creates a constraint such that
all values in the index must be distinct. An error occurs if you
@@ -2957,8 +2786,6 @@
<replaceable>data_type</replaceable> [NOT NULL | NULL] [DEFAULT <replaceable>default_value</replaceable>]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT '<replaceable>string</replaceable>'] [<replaceable>reference_definition</replaceable>]
- [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
- [STORAGE {DISK|MEMORY}]
<replaceable>data_type</replaceable>:
BIT[(<replaceable>length</replaceable>)]
@@ -3024,7 +2851,7 @@
RESTRICT | CASCADE | SET NULL | NO ACTION
<replaceable>table_option</replaceable>:
- TABLESPACE <replaceable>tablespace_name</replaceable> STORAGE DISK
+ TABLESPACE <replaceable>tablespace_name</replaceable>
ENGINE [=] <replaceable>engine_name</replaceable>
| AUTO_INCREMENT [=] <replaceable>value</replaceable>
| AVG_ROW_LENGTH [=] <replaceable>value</replaceable>
@@ -3392,6 +3219,10 @@
</para>
</listitem>
+<!--
+ # [js] Next 2 items commented out until we have
+ # Cluster releases based on 6.0
+
<listitem>
<para>
<indexterm>
@@ -3418,12 +3249,11 @@
</para>
<para>
- <literal>COLUMN_FORMAT</literal> has no effect on columns of
+ <literal>COLUMN_FORMAT</literal> currently has no effect on columns of
tables using storage engines other than
<literal>NDB</literal>.
</para>
</listitem>
-
<listitem>
<para>
<indexterm>
@@ -3450,6 +3280,7 @@
<literal>NDB</literal>.
</para>
</listitem>
+-->
<listitem>
<para>
@@ -3796,6 +3627,9 @@
</itemizedlist>
+<!--
+ # [js] Commented out until we have
+ # Cluster releases based on 6.0
<para>
The <literal>TABLESPACE ... STORAGE DISK</literal> table option
is used only with <literal>NDBCLUSTER</literal> tables. It
@@ -3805,6 +3639,7 @@
TABLESPACE</literal>. See
<xref linkend="mysql-cluster-disk-data"/>.
</para>
+-->
<para>
The <literal>ENGINE</literal> table option specifies the storage
@@ -3878,12 +3713,16 @@
used by MySQL. See
<xref linkend="myisam-storage-engine"/>.</entry>
</row>
+<!--
+ # [js] Commented out until we have
+ # Cluster releases based on 6.0
<row>
<entry><literal>NDBCLUSTER</literal></entry>
<entry>Clustered, fault-tolerant, memory-based tables. Also known as
<literal>NDB</literal>. See
<xref linkend="mysql-cluster"/>.</entry>
</row>
+-->
</tbody>
</tgroup>
</informaltable>
@@ -5595,13 +5434,13 @@
<remark role="help-topic" condition="DROP INDEX"/>
<remark role="help-keywords">
- DROP INDEX ONLINE OFFLINE
+ DROP INDEX
</remark>
<remark role="help-syntax"/>
<programlisting>
-DROP [ONLINE|OFFLINE] INDEX <replaceable>index_name</replaceable> ON <replaceable>tbl_name</replaceable>
+DROP INDEX <replaceable>index_name</replaceable> ON <replaceable>tbl_name</replaceable>
</programlisting>
<remark role="help-description-begin"/>
@@ -5617,12 +5456,11 @@
<remark role="help-description-end"/>
<para>
- Beginning with MySQL 5.1.7, indexes on variable-width columns
- are dropped online; that is, dropping the indexes does not
- require any copying or locking of the table. This is done
- automatically by the server whenever it determines that it is
- possible to do so; you do not have to use any special SQL syntax
- or server options to cause it to happen.
+ Indexes on variable-width columns are dropped online; that is,
+ dropping the indexes does not require any copying or locking of
+ the table. This is done automatically by the server whenever it
+ determines that it is possible to do so; you do not have to use
+ any special SQL syntax or server options to cause it to happen.
</para>
<para>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r11217 - in trunk: refman-5.1 refman-6.0 | jon | 11 Jul |
