Author: pd221994
Date: 2011-06-09 19:16:22 +0200 (Thu, 09 Jun 2011)
New Revision: 26478
Log:
r48789@dhcp-adc-twvpn-1-vpnpool-10-154-24-148: paul | 2011-06-09 12:09:33 -0500
General ALTER TABLE revisions
Modified:
svk:merge
trunk/dynamic-docs/command-optvars/mysqld.xml
trunk/refman-5.0/sql-syntax-data-definition.xml
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/dynamic-docs/command-optvars/mysqld.xml
===================================================================
--- trunk/dynamic-docs/command-optvars/mysqld.xml 2011-06-09 17:16:14 UTC (rev 26477)
+++ trunk/dynamic-docs/command-optvars/mysqld.xml 2011-06-09 17:16:22 UTC (rev 26478)
Changed blocks: 1, Lines Added: 1, Lines Deleted: 1; 648 bytes
@@ -19289,7 +19289,7 @@
<types>
<optype class="cmdline" format="--old-alter-table" setvar="old_alter_table"/>
<optype class="mycnf"/>
- <vartype class="system" scope="both" isdynamic="yes"/>
+ <vartype class="system" scope="both" isdynamic="yes" format="old_alter_table"/>
</types>
<values vartype="boolean" platform="all">
Modified: trunk/refman-5.0/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-5.0/sql-syntax-data-definition.xml 2011-06-09 17:16:14 UTC (rev 26477)
+++ trunk/refman-5.0/sql-syntax-data-definition.xml 2011-06-09 17:16:22 UTC (rev 26478)
Changed blocks: 3, Lines Added: 30, Lines Deleted: 23; 3935 bytes
@@ -288,10 +288,10 @@
<para>
<literal role="stmt">ALTER TABLE</literal> enables you to change
- the structure of an existing table. For example, you can add or
- delete columns, create or destroy indexes, change the type of
- existing columns, or rename columns or the table itself. You can
- also change the comment for the table and type of the table.
+ the structure of a table. For example, you can add or delete
+ columns, create or destroy indexes, change the type of existing
+ columns, or rename columns or the table itself. You can also
+ change the comment for the table and type of the table.
</para>
<remark role="help-description-end"/>
@@ -320,18 +320,25 @@
</para>
<para>
- In most cases, <literal role="stmt">ALTER TABLE</literal> works by
- making a temporary copy of the original table. The alteration is
- performed on the copy, and then the original table is deleted and
- the new one is renamed. While <literal role="stmt">ALTER
+ For information on troubleshooting <literal role="stmt">ALTER
+ TABLE</literal>, see <xref linkend="alter-table-problems"/>.
+ </para>
+
+ <para>
+ In most cases, <literal role="stmt">ALTER TABLE</literal> makes a
+ temporary copy of the original table. MySQL waits for other
+ operations that are modifying the table, then proceeds. It
+ incorporates the alteration into the copy, deletes the original
+ table, and renames the new one. While <literal role="stmt">ALTER
TABLE</literal> is executing, the original table is readable by
- other sessions. Updates and writes to the table are stalled until
- the new table is ready, and then are automatically redirected to
- the new table without any failed updates. The temporary table is
- created in the database directory of the new table. This can be
- different from the database directory of the original table if
- <literal role="stmt">ALTER TABLE</literal> is renaming the table
- to a different database.
+ other sessions. Updates and writes to the table that begin after
+ the <literal role="stmt">ALTER TABLE</literal> operation begins
+ are stalled until the new table is ready, and then are
+ automatically redirected to the new table without any failed
+ updates. The temporary table is created in the database directory
+ of the new table. This can differ from the database directory of
+ the original table for <literal role="stmt">ALTER TABLE</literal>
+ operations that rename the table to a different database.
</para>
<para>
@@ -358,20 +365,20 @@
variable to a high value.
</para>
- <para>
- For information on troubleshooting <literal role="stmt">ALTER
- TABLE</literal>, see <xref linkend="alter-table-problems"/>.
- </para>
-
<itemizedlist>
<listitem>
<para>
To use <literal role="stmt">ALTER TABLE</literal>, you need
<literal role="priv">ALTER</literal>,
- <literal role="priv">INSERT</literal>, and
- <literal role="priv">CREATE</literal> privileges for the
- table.
+ <literal role="priv">CREATE</literal>, and
+ <literal role="priv">INSERT</literal> privileges for the
+ table. Renaming a table requires
+ <literal role="priv">ALTER</literal> and
+ <literal role="priv">DROP</literal> on the old table,
+ <literal role="priv">ALTER</literal>,
+ <literal role="priv">CREATE</literal>, and
+ <literal role="priv">INSERT</literal> on the new table.
</para>
</listitem>
Modified: trunk/refman-5.1/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-5.1/sql-syntax-data-definition.xml 2011-06-09 17:16:14 UTC (rev 26477)
+++ trunk/refman-5.1/sql-syntax-data-definition.xml 2011-06-09 17:16:22 UTC (rev 26478)
Changed blocks: 9, Lines Added: 90, Lines Deleted: 82; 13284 bytes
@@ -827,10 +827,10 @@
<para>
<literal role="stmt">ALTER TABLE</literal> enables you to change
- the structure of an existing table. For example, you can add or
- delete columns, create or destroy indexes, change the type of
- existing columns, or rename columns or the table itself. You can
- also change the comment for the table and type of the table.
+ the structure of a table. For example, you can add or delete
+ columns, create or destroy indexes, change the type of existing
+ columns, or rename columns or the table itself. You can also
+ change the comment for the table and type of the table.
</para>
<para>
@@ -844,11 +844,9 @@
</para>
<para>
- Indexes on variable-width columns
- of <literal role="se">NDBCLUSTER</literal> tables can be added and
- dropped without
- any table copying.
- For more information, see
+ Indexes on variable-width columns of
+ <literal role="se">NDBCLUSTER</literal> tables can be added and
+ dropped without any table copying. For more information, see
<xref linkend="alter-table-online-operations"/>.
</para>
@@ -869,18 +867,25 @@
</para>
<para>
+ For information on troubleshooting <literal role="stmt">ALTER
+ TABLE</literal>, see <xref linkend="alter-table-problems"/>.
+ </para>
+
+ <para>
In most cases, <literal role="stmt">ALTER TABLE</literal> makes a
- temporary copy of the original table. MySQL incorporates the
- alteration into the copy, then deletes the original table and
- renames the new one. While <literal role="stmt">ALTER
+ temporary copy of the original table. MySQL waits for other
+ operations that are modifying the table, then proceeds. It
+ incorporates the alteration into the copy, deletes the original
+ table, and renames the new one. While <literal role="stmt">ALTER
TABLE</literal> is executing, the original table is readable by
- other sessions. Updates and writes to the table are stalled until
- the new table is ready, and then are automatically redirected to
- the new table without any failed updates. The temporary table is
- created in the database directory of the new table. This can
- differ from the database directory of the original table for
- <literal role="stmt">ALTER TABLE</literal> operations that rename
- the table to a different database.
+ other sessions. Updates and writes to the table that begin after
+ the <literal role="stmt">ALTER TABLE</literal> operation begins
+ are stalled until the new table is ready, and then are
+ automatically redirected to the new table without any failed
+ updates. The temporary table is created in the database directory
+ of the new table. This can differ from the database directory of
+ the original table for <literal role="stmt">ALTER TABLE</literal>
+ operations that rename the table to a different database.
</para>
<para>
@@ -912,9 +917,8 @@
<listitem>
<para>
Changing the default value of a column (except for
- <literal role="se">NDB</literal> tables;
- see <xref linkend="alter-table-online-limitations"/>).
- </para>
+ <literal role="se">NDB</literal> tables; see
+ <xref linkend="alter-table-online-limitations"/>).
</para>
</listitem>
@@ -924,9 +928,14 @@
<literal role="type">ENUM</literal> or
<literal role="type">SET</literal> column by adding new
enumeration or set members to the <emphasis>end</emphasis>
- of the list of valid member values. (Adding members in the
- middle of the list causes renumbering of existing members,
- which requires a table copy.)
+ of the list of valid member values, as long as the storage
+ side of the data type does not change. For example, adding
+ a member to a <literal role="type">SET</literal> column
+ that has 8 members changes the required storage per value
+ from 1 byte to 2 bytes; this will require a table copy.
+ Adding members in the middle of the list causes
+ renumbering of existing members, which requires a table
+ copy.
</para>
</listitem>
@@ -970,36 +979,36 @@
</itemizedlist>
<para>
- In other cases, MySQL creates a temporary table, even if the data
- wouldn't strictly need to be copied. For <literal>MyISAM</literal>
- tables, you can speed up the index re-creation operation (which is
- the slowest part of the alteration process) by setting the
+ In other cases, MySQL creates a temporary table. For
+ <literal>MyISAM</literal> tables, you can speed up the index
+ re-creation operation (which is the slowest part of the alteration
+ process) by setting the
<literal role="sysvar">myisam_sort_buffer_size</literal> system
variable to a high value.
</para>
<para>
You can force an <literal>ALTER TABLE</literal> operation that
- would otherwise require a table copy to use the temporary table
- method (as supported in MySQL 5.0) by setting the
+ would otherwise not require a table copy to use the temporary
+ table method (as supported in MySQL 5.0) by setting the
<literal role="sysvar">old_alter_table</literal> system variable
to <literal>ON</literal>.
</para>
- <para>
- For information on troubleshooting <literal role="stmt">ALTER
- TABLE</literal>, see <xref linkend="alter-table-problems"/>.
- </para>
-
<itemizedlist>
<listitem>
<para>
To use <literal role="stmt">ALTER TABLE</literal>, you need
<literal role="priv">ALTER</literal>,
- <literal role="priv">INSERT</literal>, and
- <literal role="priv">CREATE</literal> privileges for the
- table.
+ <literal role="priv">CREATE</literal>, and
+ <literal role="priv">INSERT</literal> privileges for the
+ table. Renaming a table requires
+ <literal role="priv">ALTER</literal> and
+ <literal role="priv">DROP</literal> on the old table,
+ <literal role="priv">ALTER</literal>,
+ <literal role="priv">CREATE</literal>, and
+ <literal role="priv">INSERT</literal> on the new table.
</para>
</listitem>
@@ -3943,33 +3952,32 @@
<para>
Beginning with MySQL 5.1.17, indexes on variable-width columns of
<literal role="se">NDBCLUSTER</literal> tables are created without
- any table copying.
- The table is not locked against access from other MySQL
- Cluster API nodes, although it is locked against other operations
- on the <emphasis>same</emphasis> API node for the duration of the
- operation. 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.
+ any table copying. The table is not locked against access from
+ other MySQL Cluster API nodes, although it is locked against other
+ operations on the <emphasis>same</emphasis> API node for the
+ duration of the operation. 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>
In standard MySQL ¤t-series; releases, it is not possible to
override the server when it determines that an index is to be
- created without table copying. In MySQL Cluster, beginning with MySQL Cluster NDB
- 6.2.5 and MySQL Cluster NDB 6.3.3, you can create indexes offline
- (which causes the table to be locked to all API nodes in the
- cluster) using the <literal>OFFLINE</literal> keyword. The rules
- and limitations governing <literal>CREATE OFFLINE
- INDEX</literal> and <literal>CREATE ONLINE INDEX</literal> are the
- same as for <literal>ALTER OFFLINE TABLE ... ADD INDEX</literal>
- and <literal>ALTER ONLINE TABLE ... ADD INDEX</literal>. You
- cannot cause the noncopying creation of an index that would normally
- be created offline by using the <literal>ONLINE</literal> keyword
- (if it is not possible to perform the <literal role="stmt">CREATE
- INDEX</literal> operation without table copying, the
- <literal>ONLINE</literal> keyword is ignored). For more
- information, see <xref linkend="alter-table"/>.
+ created without table copying. In MySQL Cluster, beginning with
+ MySQL Cluster NDB 6.2.5 and MySQL Cluster NDB 6.3.3, you can
+ create indexes offline (which causes the table to be locked to all
+ API nodes in the cluster) using the <literal>OFFLINE</literal>
+ keyword. The rules and limitations governing <literal>CREATE
+ OFFLINE INDEX</literal> and <literal>CREATE ONLINE INDEX</literal>
+ are the same as for <literal>ALTER OFFLINE TABLE ... ADD
+ INDEX</literal> and <literal>ALTER ONLINE TABLE ... ADD
+ INDEX</literal>. You cannot cause the noncopying creation of an
+ index that would normally be created offline by using the
+ <literal>ONLINE</literal> keyword (if it is not possible to
+ perform the <literal role="stmt">CREATE INDEX</literal> operation
+ without table copying, the <literal>ONLINE</literal> keyword is
+ ignored). For more information, see <xref linkend="alter-table"/>.
</para>
<note>
@@ -5716,8 +5724,8 @@
<important>
<para>
You cannot set or change a table column's default value
- using a noncopying <literal role="stmt">ALTER TABLE</literal>
- operation.
+ using a noncopying <literal role="stmt">ALTER
+ TABLE</literal> operation.
</para>
</important>
@@ -9624,33 +9632,33 @@
<para>
Beginning with MySQL 5.1.17, indexes on variable-width columns of
- <literal role="se">NDBCLUSTER</literal> tables are dropped without any table
-copying.
- The table is not locked against access from other MySQL
- Cluster API nodes, although it is locked against other operations
- on the <emphasis>same</emphasis> API node for the duration of the
- operation. 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.
+ <literal role="se">NDBCLUSTER</literal> tables are dropped without
+ any table copying. The table is not locked against access from
+ other MySQL Cluster API nodes, although it is locked against other
+ operations on the <emphasis>same</emphasis> API node for the
+ duration of the operation. 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>
In standard MySQL ¤t-series; releases, it is not possible to
override the server when it determines that an index is to be
- dropped without table copying. In MySQL Cluster, beginning with MySQL Cluster NDB
- 6.2.5 and MySQL Cluster NDB 6.3.3, you can drop indexes offline
- (which causes the table to be locked for all API nodes in the
- cluster) using the <literal>OFFLINE</literal> keyword. The rules
- and limitations governing <literal>DROP OFFLINE
+ dropped without table copying. In MySQL Cluster, beginning with
+ MySQL Cluster NDB 6.2.5 and MySQL Cluster NDB 6.3.3, you can drop
+ indexes offline (which causes the table to be locked for all API
+ nodes in the cluster) using the <literal>OFFLINE</literal>
+ keyword. The rules and limitations governing <literal>DROP OFFLINE
INDEX</literal> and <literal>DROP ONLINE INDEX</literal> are the
same as for <literal>ALTER OFFLINE TABLE ... DROP INDEX</literal>
and <literal>ALTER ONLINE TABLE ... DROP INDEX</literal>. You
- cannot cause the noncopying dropping of an index that would normally
- be dropped offline by using the <literal>ONLINE</literal> keyword
- (if it is not possible to perform the <literal>DROP</literal>
- operation without table copying, the <literal>ONLINE</literal> keyword is
- ignored). For more information, see <xref linkend="alter-table"/>.
+ cannot cause the noncopying dropping of an index that would
+ normally be dropped offline by using the <literal>ONLINE</literal>
+ keyword (if it is not possible to perform the
+ <literal>DROP</literal> operation without table copying, the
+ <literal>ONLINE</literal> keyword is ignored). For more
+ information, see <xref linkend="alter-table"/>.
</para>
<note>
Modified: trunk/refman-5.5/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-5.5/sql-syntax-data-definition.xml 2011-06-09 17:16:14 UTC (rev 26477)
+++ trunk/refman-5.5/sql-syntax-data-definition.xml 2011-06-09 17:16:22 UTC (rev 26478)
Changed blocks: 5, Lines Added: 43, Lines Deleted: 31; 5899 bytes
@@ -650,10 +650,10 @@
<para>
<literal role="stmt">ALTER TABLE</literal> enables you to change
- the structure of an existing table. For example, you can add or
- delete columns, create or destroy indexes, change the type of
- existing columns, or rename columns or the table itself. You can
- also change the comment for the table and type of the table.
+ the structure of a table. For example, you can add or delete
+ columns, create or destroy indexes, change the type of existing
+ columns, or rename columns or the table itself. You can also
+ change the comment for the table and type of the table.
</para>
<para>
@@ -682,18 +682,25 @@
</para>
<para>
+ For information on troubleshooting <literal role="stmt">ALTER
+ TABLE</literal>, see <xref linkend="alter-table-problems"/>.
+ </para>
+
+ <para>
In most cases, <literal role="stmt">ALTER TABLE</literal> makes a
- temporary copy of the original table. MySQL incorporates the
- alteration into the copy, then deletes the original table and
- renames the new one. While <literal role="stmt">ALTER
+ temporary copy of the original table. MySQL waits for other
+ operations that are modifying the table, then proceeds. It
+ incorporates the alteration into the copy, deletes the original
+ table, and renames the new one. While <literal role="stmt">ALTER
TABLE</literal> is executing, the original table is readable by
- other sessions. Updates and writes to the table are stalled until
- the new table is ready, and then are automatically redirected to
- the new table without any failed updates. The temporary table is
- created in the database directory of the new table. This can
- differ from the database directory of the original table for
- <literal role="stmt">ALTER TABLE</literal> operations that rename
- the table to a different database.
+ other sessions. Updates and writes to the table that begin after
+ the <literal role="stmt">ALTER TABLE</literal> operation begins
+ are stalled until the new table is ready, and then are
+ automatically redirected to the new table without any failed
+ updates. The temporary table is created in the database directory
+ of the new table. This can differ from the database directory of
+ the original table for <literal role="stmt">ALTER TABLE</literal>
+ operations that rename the table to a different database.
</para>
<para>
@@ -734,9 +741,14 @@
<literal role="type">ENUM</literal> or
<literal role="type">SET</literal> column by adding new
enumeration or set members to the <emphasis>end</emphasis>
- of the list of valid member values. (Adding members in the
- middle of the list causes renumbering of existing members,
- which requires a table copy.)
+ of the list of valid member values, as long as the storage
+ side of the data type does not change. For example, adding
+ a member to a <literal role="type">SET</literal> column
+ that has 8 members changes the required storage per value
+ from 1 byte to 2 bytes; this will require a table copy.
+ Adding members in the middle of the list causes
+ renumbering of existing members, which requires a table
+ copy.
</para>
</listitem>
@@ -778,18 +790,18 @@
</itemizedlist>
<para>
- In other cases, MySQL creates a temporary table, even if the data
- wouldn't strictly need to be copied. For <literal>MyISAM</literal>
- tables, you can speed up the index re-creation operation (which is
- the slowest part of the alteration process) by setting the
+ In other cases, MySQL creates a temporary table. For
+ <literal>MyISAM</literal> tables, you can speed up the index
+ re-creation operation (which is the slowest part of the alteration
+ process) by setting the
<literal role="sysvar">myisam_sort_buffer_size</literal> system
variable to a high value.
</para>
<para>
You can force an <literal>ALTER TABLE</literal> operation that
- would otherwise require a table copy to use the temporary table
- method (as supported in MySQL 5.0) by setting the
+ would otherwise not require a table copy to use the temporary
+ table method (as supported in MySQL 5.0) by setting the
<literal role="sysvar">old_alter_table</literal> system variable
to <literal>ON</literal>.
</para>
@@ -803,20 +815,20 @@
ignored.
</para>
- <para>
- For information on troubleshooting <literal role="stmt">ALTER
- TABLE</literal>, see <xref linkend="alter-table-problems"/>.
- </para>
-
<itemizedlist>
<listitem>
<para>
To use <literal role="stmt">ALTER TABLE</literal>, you need
<literal role="priv">ALTER</literal>,
- <literal role="priv">INSERT</literal>, and
- <literal role="priv">CREATE</literal> privileges for the
- table.
+ <literal role="priv">CREATE</literal>, and
+ <literal role="priv">INSERT</literal> privileges for the
+ table. Renaming a table requires
+ <literal role="priv">ALTER</literal> and
+ <literal role="priv">DROP</literal> on the old table,
+ <literal role="priv">ALTER</literal>,
+ <literal role="priv">CREATE</literal>, and
+ <literal role="priv">INSERT</literal> on the new table.
</para>
</listitem>
Modified: trunk/refman-5.6/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-5.6/sql-syntax-data-definition.xml 2011-06-09 17:16:14 UTC (rev 26477)
+++ trunk/refman-5.6/sql-syntax-data-definition.xml 2011-06-09 17:16:22 UTC (rev 26478)
Changed blocks: 5, Lines Added: 43, Lines Deleted: 31; 5899 bytes
@@ -651,10 +651,10 @@
<para>
<literal role="stmt">ALTER TABLE</literal> enables you to change
- the structure of an existing table. For example, you can add or
- delete columns, create or destroy indexes, change the type of
- existing columns, or rename columns or the table itself. You can
- also change the comment for the table and type of the table.
+ the structure of a table. For example, you can add or delete
+ columns, create or destroy indexes, change the type of existing
+ columns, or rename columns or the table itself. You can also
+ change the comment for the table and type of the table.
</para>
<para>
@@ -683,18 +683,25 @@
</para>
<para>
+ For information on troubleshooting <literal role="stmt">ALTER
+ TABLE</literal>, see <xref linkend="alter-table-problems"/>.
+ </para>
+
+ <para>
In most cases, <literal role="stmt">ALTER TABLE</literal> makes a
- temporary copy of the original table. MySQL incorporates the
- alteration into the copy, then deletes the original table and
- renames the new one. While <literal role="stmt">ALTER
+ temporary copy of the original table. MySQL waits for other
+ operations that are modifying the table, then proceeds. It
+ incorporates the alteration into the copy, deletes the original
+ table, and renames the new one. While <literal role="stmt">ALTER
TABLE</literal> is executing, the original table is readable by
- other sessions. Updates and writes to the table are stalled until
- the new table is ready, and then are automatically redirected to
- the new table without any failed updates. The temporary table is
- created in the database directory of the new table. This can
- differ from the database directory of the original table for
- <literal role="stmt">ALTER TABLE</literal> operations that rename
- the table to a different database.
+ other sessions. Updates and writes to the table that begin after
+ the <literal role="stmt">ALTER TABLE</literal> operation begins
+ are stalled until the new table is ready, and then are
+ automatically redirected to the new table without any failed
+ updates. The temporary table is created in the database directory
+ of the new table. This can differ from the database directory of
+ the original table for <literal role="stmt">ALTER TABLE</literal>
+ operations that rename the table to a different database.
</para>
<para>
@@ -735,9 +742,14 @@
<literal role="type">ENUM</literal> or
<literal role="type">SET</literal> column by adding new
enumeration or set members to the <emphasis>end</emphasis>
- of the list of valid member values. (Adding members in the
- middle of the list causes renumbering of existing members,
- which requires a table copy.)
+ of the list of valid member values, as long as the storage
+ side of the data type does not change. For example, adding
+ a member to a <literal role="type">SET</literal> column
+ that has 8 members changes the required storage per value
+ from 1 byte to 2 bytes; this will require a table copy.
+ Adding members in the middle of the list causes
+ renumbering of existing members, which requires a table
+ copy.
</para>
</listitem>
@@ -779,18 +791,18 @@
</itemizedlist>
<para>
- In other cases, MySQL creates a temporary table, even if the data
- wouldn't strictly need to be copied. For <literal>MyISAM</literal>
- tables, you can speed up the index re-creation operation (which is
- the slowest part of the alteration process) by setting the
+ In other cases, MySQL creates a temporary table. For
+ <literal>MyISAM</literal> tables, you can speed up the index
+ re-creation operation (which is the slowest part of the alteration
+ process) by setting the
<literal role="sysvar">myisam_sort_buffer_size</literal> system
variable to a high value.
</para>
<para>
You can force an <literal>ALTER TABLE</literal> operation that
- would otherwise require a table copy to use the temporary table
- method (as supported in MySQL 5.0) by setting the
+ would otherwise not require a table copy to use the temporary
+ table method (as supported in MySQL 5.0) by setting the
<literal role="sysvar">old_alter_table</literal> system variable
to <literal>ON</literal>.
</para>
@@ -804,20 +816,20 @@
ignored.
</para>
- <para>
- For information on troubleshooting <literal role="stmt">ALTER
- TABLE</literal>, see <xref linkend="alter-table-problems"/>.
- </para>
-
<itemizedlist>
<listitem>
<para>
To use <literal role="stmt">ALTER TABLE</literal>, you need
<literal role="priv">ALTER</literal>,
- <literal role="priv">INSERT</literal>, and
- <literal role="priv">CREATE</literal> privileges for the
- table.
+ <literal role="priv">CREATE</literal>, and
+ <literal role="priv">INSERT</literal> privileges for the
+ table. Renaming a table requires
+ <literal role="priv">ALTER</literal> and
+ <literal role="priv">DROP</literal> on the old table,
+ <literal role="priv">ALTER</literal>,
+ <literal role="priv">CREATE</literal>, and
+ <literal role="priv">INSERT</literal> on the new table.
</para>
</listitem>
Modified: trunk/refman-6.0/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-6.0/sql-syntax-data-definition.xml 2011-06-09 17:16:14 UTC (rev 26477)
+++ trunk/refman-6.0/sql-syntax-data-definition.xml 2011-06-09 17:16:22 UTC (rev 26478)
Changed blocks: 4, Lines Added: 43, Lines Deleted: 31; 5851 bytes
@@ -649,10 +649,10 @@
<para>
<literal role="stmt">ALTER TABLE</literal> enables you to change
- the structure of an existing table. For example, you can add or
- delete columns, create or destroy indexes, change the type of
- existing columns, or rename columns or the table itself. You can
- also change the comment for the table and type of the table.
+ the structure of a table. For example, you can add or delete
+ columns, create or destroy indexes, change the type of existing
+ columns, or rename columns or the table itself. You can also
+ change the comment for the table and type of the table.
</para>
<para>
@@ -681,18 +681,25 @@
</para>
<para>
+ For information on troubleshooting <literal role="stmt">ALTER
+ TABLE</literal>, see <xref linkend="alter-table-problems"/>.
+ </para>
+
+ <para>
In most cases, <literal role="stmt">ALTER TABLE</literal> makes a
- temporary copy of the original table. MySQL incorporates the
- alteration into the copy, then deletes the original table and
- renames the new one. While <literal role="stmt">ALTER
+ temporary copy of the original table. MySQL waits for other
+ operations that are modifying the table, then proceeds. It
+ incorporates the alteration into the copy, deletes the original
+ table, and renames the new one. While <literal role="stmt">ALTER
TABLE</literal> is executing, the original table is readable by
- other sessions. Updates and writes to the table are stalled until
- the new table is ready, and then are automatically redirected to
- the new table without any failed updates. The temporary table is
- created in the database directory of the new table. This can
- differ from the database directory of the original table for
- <literal role="stmt">ALTER TABLE</literal> operations that rename
- the table to a different database.
+ other sessions. Updates and writes to the table that begin after
+ the <literal role="stmt">ALTER TABLE</literal> operation begins
+ are stalled until the new table is ready, and then are
+ automatically redirected to the new table without any failed
+ updates. The temporary table is created in the database directory
+ of the new table. This can differ from the database directory of
+ the original table for <literal role="stmt">ALTER TABLE</literal>
+ operations that rename the table to a different database.
</para>
<para>
@@ -732,9 +739,14 @@
<literal role="type">ENUM</literal> or
<literal role="type">SET</literal> column by adding new
enumeration or set members to the <emphasis>end</emphasis>
- of the list of valid member values. (Adding members in the
- middle of the list causes renumbering of existing members,
- which requires a table copy.)
+ of the list of valid member values, as long as the storage
+ side of the data type does not change. For example, adding
+ a member to a <literal role="type">SET</literal> column
+ that has 8 members changes the required storage per value
+ from 1 byte to 2 bytes; this will require a table copy.
+ Adding members in the middle of the list causes
+ renumbering of existing members, which requires a table
+ copy.
</para>
</listitem>
@@ -776,36 +788,36 @@
</itemizedlist>
<para>
- In other cases, MySQL creates a temporary table, even if the data
- wouldn't strictly need to be copied. For <literal>MyISAM</literal>
- tables, you can speed up the index re-creation operation (which is
- the slowest part of the alteration process) by setting the
+ In other cases, MySQL creates a temporary table. For
+ <literal>MyISAM</literal> tables, you can speed up the index
+ re-creation operation (which is the slowest part of the alteration
+ process) by setting the
<literal role="sysvar">myisam_sort_buffer_size</literal> system
variable to a high value.
</para>
<para>
You can force an <literal>ALTER TABLE</literal> operation that
- would otherwise require a table copy to use the temporary table
- method (as supported in MySQL 5.0) by setting the
+ would otherwise not require a table copy to use the temporary
+ table method (as supported in MySQL 5.0) by setting the
<literal role="sysvar">old_alter_table</literal> system variable
to <literal>ON</literal>.
</para>
- <para>
- For information on troubleshooting <literal role="stmt">ALTER
- TABLE</literal>, see <xref linkend="alter-table-problems"/>.
- </para>
-
<itemizedlist>
<listitem>
<para>
To use <literal role="stmt">ALTER TABLE</literal>, you need
<literal role="priv">ALTER</literal>,
- <literal role="priv">INSERT</literal>, and
- <literal role="priv">CREATE</literal> privileges for the
- table.
+ <literal role="priv">CREATE</literal>, and
+ <literal role="priv">INSERT</literal> privileges for the
+ table. Renaming a table requires
+ <literal role="priv">ALTER</literal> and
+ <literal role="priv">DROP</literal> on the old table,
+ <literal role="priv">ALTER</literal>,
+ <literal role="priv">CREATE</literal>, and
+ <literal role="priv">INSERT</literal> on the new table.
</para>
</listitem>
| Thread |
|---|
| • svn commit - mysqldoc@oter02: r26478 - in trunk: . dynamic-docs/command-optvars refman-5.0 refman-5.1 refman-5.5 refman-5.6 refman-6.0 | paul.dubois | 9 Jun |
