From: jon
Date: July 11 2008 4:43pm
Subject: svn commit - mysqldoc@docsrva: r11217 - in trunk: refman-5.1 refman-6.0
List-Archive: http://lists.mysql.com/commits/49590
Message-Id: <200807111643.m6BGhqD6010967@docsrva.mysql.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
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 @@
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
@@ -635,11 +635,18 @@
+ Beginning with MySQL 5.1.7, ADD INDEX and
+ DROP INDEX operations are performed
+ online when the indexes are on variable-width columns only.
+
+
+
The ONLINE keyword can be used to perform
online ADD COLUMN, ADD
INDEX, and DROP INDEX
- operations on NDB tables, as well as
- online renaming of tables and columns. Online renaming
+ operations on NDB 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 MyISAM
tables. Online operations are non-copying; that is, they do
@@ -651,14 +658,45 @@
operations.
+
+ The ONLINE and OFFLINE
+ 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):
+
+
+
+
+
+ The server determines automatically whether an
+ ADD INDEX or DROP
+ INDEX 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.
+
+
+
+
+
+ Attempting to use the ONLINE or
+ OFFLINE keyword in an
+ ALTER TABLE statement results in an
+ error.
+
+
+
+
+
+
Limitations
Online ALTER TABLE operations that add
- columns or that add or drop indexes are subject to the
- following limitations:
+ columns are subject to the following limitations:
@@ -675,26 +713,30 @@
- 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
COLUMN_FORMAT DYNAMIC.
- 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
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;
+mysql> CREATE TABLE t1 (
+ > c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY
+ > ) ENGINE=NDBCLUSTER;
Query OK, 0 rows affected (1.44 sec)
-mysql> ALTER ONLINE TABLE t1 ADD COLUMN c2 INT, ADD COLUMN c3 INT;
+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 t2\G
@@ -775,7 +817,9 @@
-mysql> CREATE TABLE t2 (c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY COLUMN_FORMAT FIXED) ENGINE=NDB;
+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;
@@ -832,8 +876,9 @@
- The ONLINE keyword was added in MySQL
- Cluster NDB 6.2.5 and MySQL Cluster NDB 6.3.2.
+ The ONLINE and OFFLINE
+ keywords are also supported in ALTER TABLE ...
+ CHANGE ... statements that rename columns.
@@ -2978,13 +3023,13 @@
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
@@ -3498,8 +3543,8 @@
- COLUMN_FORMAT has no effect on columns of
- tables using storage engines other than
+ COLUMN_FORMAT currently has no effect on
+ columns of tables using storage engines other than
NDB.
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
-ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
+ALTER [IGNORE] TABLE tbl_name
alter_specification [, alter_specification] ...
alter_specification:
@@ -624,189 +623,30 @@
- The ONLINE keyword can be used to perform
- online ADD COLUMN, ADD
- INDEX, and DROP INDEX
- operations on NDB tables. Online adding
- and dropping of indexes is also supported for
- MyISAM and Falcon
- 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 NDB
- table alterations made in a cluster with multiple API nodes;
- transactions can continue uninterrupted during online DDL
- operations.
+ ADD INDEX and DROP
+ INDEX operations are performed online when the
+ indexes are on variable-width columns only.
-
-
- Limitations
-
-
- Online ALTER TABLE operations are
- subject to the following limitations:
-
-
-
-
-
- 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. Columns to be added
- online must meet the following criteria:
-
-
-
-
-
-
- They must be dynamic; that is, it must be
- possible to create them using
- COLUMN_FORMAT DYNAMIC.
-
-
-
-
-
- They must be nullable, 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 t2\G
-*************************** 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)
-
-
-
-
-
- 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 FIRST keyword also
- fails.
-
-
-
-
-
-
- If the storage engine supports online ALTER
- TABLE, 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:
-
-
-
-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: 0
-
-mysql> SHOW WARNINGS;
-+---------+------+---------------------------------------------------------------+
-| Level | Code | Message |
-+---------+------+---------------------------------------------------------------+
-| Warning | 1475 | Converted FIXED field to DYNAMIC to enable on-line ADD COLUMN |
-| Warning | 1475 | Converted FIXED field to DYNAMIC to enable on-line ADD COLUMN |
-+---------+------+---------------------------------------------------------------+
-2 rows in set (0.00 sec)
-
-
-
- (Note that existing columns, including the table's
- primary key, need not be dynamic; only a column 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: 0
-
-mysql> SHOW WARNINGS;
-+---------+------+---------------------------------------------------------------+
-| Level | Code | Message |
-+---------+------+---------------------------------------------------------------+
-| Warning | 1475 | Converted FIXED field to DYNAMIC to enable on-line ADD COLUMN |
-+---------+------+---------------------------------------------------------------+
-1 row in set (0.00 sec)
-
-
-
- For more information about
- COLUMN_FORMAT, see
- .
-
-
-
-
-
- Online DROP COLUMN operations are
- not supported.
-
-
-
-
-
- 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 KEY, CONSTRAINT,
- and IGNORE keywords are supported in
- ALTER TABLE statements using the
- ONLINE keyword.
+ Online operations are non-copying; that is, they do not
+ require that indexes be re-created.
- The ONLINE keyword was added in MySQL
- 5.2.6.
+ The server determines automatically whether an ADD
+ INDEX or DROP INDEX 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.
The CREATE INDEX and DROP
- INDEX statements also support online operations,
- as well as the ONLINE and
- OFFLINE keywords. See
- , and
+ INDEX statements also support online operations.
+ See , and
, for more information.
@@ -2348,17 +2188,6 @@
.
-
-
- The ONLINE and OFFLINE
- 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.
-
-
-
A UNIQUE index creates a constraint such that
all values in the index must be distinct. An error occurs if you
@@ -2957,8 +2786,6 @@
data_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string'] [reference_definition]
- [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
- [STORAGE {DISK|MEMORY}]
data_type:
BIT[(length)]
@@ -3024,7 +2851,7 @@
RESTRICT | CASCADE | SET NULL | NO ACTION
table_option:
- TABLESPACE tablespace_name STORAGE DISK
+ TABLESPACE tablespace_name
ENGINE [=] engine_name
| AUTO_INCREMENT [=] value
| AVG_ROW_LENGTH [=] value
@@ -3392,6 +3219,10 @@
+
@@ -3796,6 +3627,9 @@
+
The ENGINE table option specifies the storage
@@ -3878,12 +3713,16 @@
used by MySQL. See
.
+
@@ -5595,13 +5434,13 @@
- DROP INDEX ONLINE OFFLINE
+ DROP INDEX
-DROP [ONLINE|OFFLINE] INDEX index_name ON tbl_name
+DROP INDEX index_name ON tbl_name
@@ -5617,12 +5456,11 @@
- 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.