Author: js221926
Date: 2011-03-23 12:02:23 +0100 (Wed, 23 Mar 2011)
New Revision: 25482
Log:
Documentation for WL#5576 (CREATE TABLE ... SELECT with side effects disallowed in 5.6)
Synced some wording changes (to make language more generic) to 5.0/5.1/6.0 as well
See also BUG#11749792, BUG#11745361, BUG#39804, BUG#55876, BUG#47899
Modified:
trunk/dynamic-docs/changelog/mysqld-2.xml
trunk/refman-5.0/replication-notes.xml
trunk/refman-5.1/replication-notes.xml
trunk/refman-5.5/installing-updowngrade.xml
trunk/refman-5.5/replication-notes.xml
trunk/refman-5.6/replication-notes.xml
trunk/refman-5.6/sql-syntax-data-definition.xml
trunk/refman-5.6/sql-syntax-data-manipulation.xml
trunk/refman-6.0/replication-notes.xml
Modified: trunk/dynamic-docs/changelog/mysqld-2.xml
===================================================================
--- trunk/dynamic-docs/changelog/mysqld-2.xml 2011-03-22 22:36:53 UTC (rev 25481)
+++ trunk/dynamic-docs/changelog/mysqld-2.xml 2011-03-23 11:02:23 UTC (rev 25482)
Changed blocks: 1, Lines Added: 65, Lines Deleted: 0; 2686 bytes
@@ -9,6 +9,71 @@
<logentry entrytype="bug">
<tags>
+ <highlight type="replication"/>
+ <highlight type="incompatiblechange"/>
+ <manual type="CREATE ... SELECT"/>
+ <manual type="SELECT ... FOR UPDATE"/>
+ </tags>
+
+ <bugs>
+ <fixes bugid="11749792"/>
+ <fixes bugid="11745361"/>
+ <fixes bugid="39804"/>
+ <fixes bugid="55876"/>
+ <fixes wlid="5576"/>
+ <seealsobug bugid="47899"/>
+ </bugs>
+
+ <versions>
+ <fixedin ver="5.6.2"/>
+ </versions>
+
+ <message>
+
+ <para>
+ It is no longer possible to issue a
+ <literal role="stmt" condition="create-table-select">CREATE
+ TABLE ... SELECT</literal> statement which changes any tables
+ other than the table being created. Any such statement is not
+ executed and instead fails with an error.
+ </para>
+
+ <para>
+ One consequence of this change is that <literal>FOR
+ UPDATE</literal> may no longer be used at all with the
+ <literal role="stmt">SELECT</literal> portion of a
+ <literal role="stmt" condition="create-table-select">CREATE
+ TABLE ... SELECT</literal>.
+ </para>
+
+ <para>
+ This means that, prior to upgrading from a previous release, you
+ should rewrite any
+ <literal role="stmt" condition="create-table-select">CREATE
+ TABLE ... SELECT</literal> statements that cause changes in
+ other tables so that the statements no longer do so.
+ </para>
+
+ <para>
+ This change also has implications for statement-based
+ replication between a MySQL 5.6 (or later slave) and a master
+ running a previous version of MySQL. In such a case, if a
+ <literal role="stmt" condition="create-table-select">CREATE
+ TABLE ... SELECT</literal> statement on the master that causes
+ changes in other tables succeeds on the master, the statement
+ nonetheless fails on the slave, causing replication to stop. To
+ keep this from happening, you should either use row-based
+ replication, or rewrite the offending statement before running
+ it on the master.
+ </para>
+
+ </message>
+
+ </logentry>
+
+ <logentry entrytype="bug">
+
+ <tags>
<highlight type="cluster" ver="5.1.51-ndb-6.3.42,5.1.51-ndb-7.0.23,5.1.51-ndb-7.1.12"/>
<highlight type="replication"/>
<manual type="ER_SLAVE_INCIDENT"/>
Modified: trunk/refman-5.0/replication-notes.xml
===================================================================
--- trunk/refman-5.0/replication-notes.xml 2011-03-22 22:36:53 UTC (rev 25481)
+++ trunk/refman-5.0/replication-notes.xml 2011-03-23 11:02:23 UTC (rev 25482)
Changed blocks: 2, Lines Added: 10, Lines Deleted: 4; 1434 bytes
@@ -1557,10 +1557,9 @@
between MySQL Server versions, if you are using columns that are
defined using the old
<literal role="type">TIMESTAMP(N)</literal> syntax. See
- <xref linkend="upgrading-from-previous-series"/>, for
- more information about the differences, how they can impact
- MySQL replication, and what you can do if you encounter such
- problems.
+ <xref linkend="upgrading-from-previous-series"/>, for more
+ information about the differences, how they can impact MySQL
+ replication, and what you can do if you encounter such problems.
</para>
</section>
@@ -1992,6 +1991,13 @@
</para>
<para>
+ However, one may encounter difficulties when replicating from an
+ older master to a newer slave if the master uses statements or
+ relies on behavior no longer supported in the version of MySQL
+ used on the slave.
+ </para>
+
+ <para>
The use of more than 2 MySQL Server versions is not supported in
replication setups involving multiple masters, regardless of the
number of master or slave MySQL servers. This restriction applies
Modified: trunk/refman-5.1/replication-notes.xml
===================================================================
--- trunk/refman-5.1/replication-notes.xml 2011-03-22 22:36:53 UTC (rev 25481)
+++ trunk/refman-5.1/replication-notes.xml 2011-03-23 11:02:23 UTC (rev 25482)
Changed blocks: 1, Lines Added: 7, Lines Deleted: 0; 789 bytes
@@ -3954,6 +3954,13 @@
</para>
<para>
+ However, one may encounter difficulties when replicating from an
+ older master to a newer slave if the master uses statements or
+ relies on behavior no longer supported in the version of MySQL
+ used on the slave.
+ </para>
+
+ <para>
The use of more than 2 MySQL Server versions is not supported in
replication setups involving multiple masters, regardless of the
number of master or slave MySQL servers. This restriction applies
Modified: trunk/refman-5.5/installing-updowngrade.xml
===================================================================
--- trunk/refman-5.5/installing-updowngrade.xml 2011-03-22 22:36:53 UTC (rev 25481)
+++ trunk/refman-5.5/installing-updowngrade.xml 2011-03-23 11:02:23 UTC (rev 25482)
Changed blocks: 1, Lines Added: 2, Lines Deleted: 3; 790 bytes
@@ -976,9 +976,8 @@
M. Gay. In MySQL, this library provides the basis for
improved conversion between string or
<literal role="type">DECIMAL</literal> values and
- approximate-value
- (<literal role="type">FLOAT</literal>/<literal role="type">DOUBLE</literal>)
- numbers.
+ approximate-value (<literal role="type">FLOAT</literal> or
+ <literal role="type">DOUBLE</literal>) numbers.
</para>
<para>
Modified: trunk/refman-5.5/replication-notes.xml
===================================================================
--- trunk/refman-5.5/replication-notes.xml 2011-03-22 22:36:53 UTC (rev 25481)
+++ trunk/refman-5.5/replication-notes.xml 2011-03-23 11:02:23 UTC (rev 25482)
Changed blocks: 3, Lines Added: 44, Lines Deleted: 4; 3637 bytes
@@ -517,6 +517,35 @@
<xref linkend="create-table-select"/>.
</para>
+ <para>
+ When using statement-based replication between a MySQL 5.6 or
+ later slave and a master running a previous version of MySQL, a
+ <literal role="stmt" condition="create-table-select">CREATE
+ TABLE ... SELECT</literal> statement causing changes in other
+ tables on the master fails on the slave, causing replication to
+ stop. This is due to the fact that MySQL 5.6 does not allow a
+ <literal role="stmt" condition="create-table-select">CREATE
+ TABLE ... SELECT</literal> statement to make any changes in
+ tables other than the table that is created by the
+ statement—a change in behavior from previous versions of
+ MySQL, which permitted these statements to do so. To keep this
+ from happening, you should use row-based replication, rewrite
+ the offending statement before running it on the master, or
+ upgrade the master to MySQL 5.6 (or later). (If you choose to
+ upgrade the master, keep in mind that such a
+ <literal role="stmt" condition="create-table-select">CREATE
+ TABLE ... SELECT</literal> statement will fail there as well,
+ following the upgrade, unless the statement is rewritten to
+ remove any side effects on other tables.) This is not an issue
+ when using row-based replication, because the statement is
+ logged as a <literal role="stmt">CREATE TABLE</literal>
+ statement with any changes to table data logged as row-insert
+ events (or possibly row-update events), rather than as the
+ entire
+ <literal role="stmt" condition="create-table-select">CREATE
+ TABLE ... SELECT</literal> statement.
+ </para>
+
</section>
<section id="replication-features-drop-if-exists">
@@ -3039,10 +3068,9 @@
between MySQL Server versions, if you are using columns that are
defined using the old
<literal role="type">TIMESTAMP(N)</literal> syntax. See
- <xref linkend="upgrading-from-previous-series"/>, for
- more information about the differences, how they can impact
- MySQL replication, and what you can do if you encounter such
- problems.
+ <xref linkend="upgrading-from-previous-series"/>, for more
+ information about the differences, how they can impact MySQL
+ replication, and what you can do if you encounter such problems.
</para>
</section>
@@ -3764,6 +3792,18 @@
</para>
<para>
+ However, one may encounter difficulties when replicating from an
+ older master to a newer slave if the master uses statements or
+ relies on behavior no longer supported in the version of MySQL
+ used on the slave. For example, in MySQL 5.5,
+ <literal role="stmt" condition="create-table-select">CREATE TABLE
+ ... SELECT</literal> statements are permitted to change tables
+ other than the one being created, but are no longer allowed to do
+ so in MySQL 5.6 (see
+ <xref linkend="replication-features-create-select"/>).
+ </para>
+
+ <para>
The use of more than 2 MySQL Server versions is not supported in
replication setups involving multiple masters, regardless of the
number of master or slave MySQL servers. This restriction applies
Modified: trunk/refman-5.6/replication-notes.xml
===================================================================
--- trunk/refman-5.6/replication-notes.xml 2011-03-22 22:36:53 UTC (rev 25481)
+++ trunk/refman-5.6/replication-notes.xml 2011-03-23 11:02:23 UTC (rev 25482)
Changed blocks: 2, Lines Added: 40, Lines Deleted: 0; 2876 bytes
@@ -386,6 +386,34 @@
</para>
<para>
+ MySQL 5.6 does not allow a
+ <literal role="stmt" condition="create-table-select">CREATE
+ TABLE ... SELECT</literal> statement to make any changes in
+ tables other than the table that is created by the statement.
+ This is a change in behavior from previous versions of MySQL,
+ which permitted these statements to do so. This means that, when
+ using statement-based replication between a MySQL 5.6 or later
+ slave and a master running a previous version of MySQL, a
+ <literal role="stmt" condition="create-table-select">CREATE
+ TABLE ... SELECT</literal> statement causing changes in other
+ tables on the master fails on the slave, causing replication to
+ stop. To keep this from happening, you should use row-based
+ replication, rewrite the offending statement before running it
+ on the master, or upgrade the master to MySQL 5.6 (or later).
+ (If you choose to upgrade the master, keep in mind that such a
+ <literal role="stmt" condition="create-table-select">CREATE
+ TABLE ... SELECT</literal> statement will fail following the
+ upgrade unless it is rewritten to remove any side effects on
+ other tables.) This is not an issue when using row-based
+ replication, because the statement is logged as a
+ <literal role="stmt">CREATE TABLE</literal> statement with any
+ changes to table data logged as row-insert events, rather than
+ as the entire
+ <literal role="stmt" condition="create-table-select">CREATE
+ TABLE ... SELECT</literal>.
+ </para>
+
+ <para>
These behaviors are not dependent on MySQL version:
</para>
@@ -3706,6 +3734,18 @@
</para>
<para>
+ However, one may encounter difficulties when replicating from an
+ older master to a newer slave if the master uses statements or
+ relies on behavior no longer supported in the version of MySQL
+ used on the slave. For example, in MySQL 5.5,
+ <literal role="stmt" condition="create-table-select">CREATE TABLE
+ ... SELECT</literal> statements are permitted to change tables
+ other than the one being created, but are no longer allowed to do
+ so in MySQL 5.6 (see
+ <xref linkend="replication-features-create-select"/>).
+ </para>
+
+ <para>
The use of more than 2 MySQL Server versions is not supported in
replication setups involving multiple masters, regardless of the
number of master or slave MySQL servers. This restriction applies
Modified: trunk/refman-5.6/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-5.6/sql-syntax-data-definition.xml 2011-03-22 22:36:53 UTC (rev 25481)
+++ trunk/refman-5.6/sql-syntax-data-definition.xml 2011-03-23 11:02:23 UTC (rev 25482)
Changed blocks: 1, Lines Added: 23, Lines Deleted: 0; 1451 bytes
@@ -6772,6 +6772,29 @@
SELECT</literal>.
</para>
+ <important>
+ <para>
+ You cannot use <literal>FOR UPDATE</literal> as part of the
+ <literal role="stmt">SELECT</literal> in a statement such as
+ <literal role="stmt" condition="create-table-select">CREATE
+ TABLE <replaceable>new_table</replaceable> SELECT ... FROM
+ <replaceable>old_table</replaceable> ...</literal>. If you
+ attempt to do so, the statement fails. This represents a
+ change in behavior from MySQL 5.5 and earlier, which permitted
+ <literal role="stmt" condition="create-table-select">CREATE
+ TABLE ... SELECT</literal> statements to make changes in
+ tables other than the table being created.
+ </para>
+
+ <para>
+ This change can also have implications for statement-based
+ replication from an older master to a MySQL 5.6 or newer
+ slave. See
+ <xref linkend="replication-features-create-select"/>, for more
+ information.
+ </para>
+ </important>
+
</section>
<section id="silent-column-changes">
Modified: trunk/refman-5.6/sql-syntax-data-manipulation.xml
===================================================================
--- trunk/refman-5.6/sql-syntax-data-manipulation.xml 2011-03-22 22:36:53 UTC (rev 25481)
+++ trunk/refman-5.6/sql-syntax-data-manipulation.xml 2011-03-23 11:02:23 UTC (rev 25482)
Changed blocks: 1, Lines Added: 18, Lines Deleted: 0; 1403 bytes
@@ -5320,6 +5320,24 @@
to update or delete them. See
<xref linkend="innodb-locking-reads"/>.
</para>
+
+ <para>
+ In addition, you cannot use <literal>FOR UPDATE</literal> as
+ part of the <literal role="stmt">SELECT</literal> in a
+ statement such as
+ <literal role="stmt" condition="create-table-select">CREATE
+ TABLE <replaceable>new_table</replaceable> SELECT ... FROM
+ <replaceable>old_table</replaceable> ...</literal>. (If you
+ attempt to do so, the statement is rejected with the error
+ <errortext>Can't update table
+ '<replaceable>old_table</replaceable>' while
+ '<replaceable>new_table</replaceable>' is being
+ created</errortext>.) This is a change in behavior from MySQL
+ 5.5 and earlier, which permitted
+ <literal role="stmt" condition="create-table-select">CREATE
+ TABLE ... SELECT</literal> statements to make changes in
+ tables other than the table being created.
+ </para>
</listitem>
</itemizedlist>
Modified: trunk/refman-6.0/replication-notes.xml
===================================================================
--- trunk/refman-6.0/replication-notes.xml 2011-03-22 22:36:53 UTC (rev 25481)
+++ trunk/refman-6.0/replication-notes.xml 2011-03-23 11:02:23 UTC (rev 25482)
Changed blocks: 1, Lines Added: 7, Lines Deleted: 0; 789 bytes
@@ -3370,6 +3370,13 @@
</para>
<para>
+ However, one may encounter difficulties when replicating from an
+ older master to a newer slave if the master uses statements or
+ relies on behavior no longer supported in the version of MySQL
+ used on the slave.
+ </para>
+
+ <para>
The use of more than 2 MySQL Server versions is not supported in
replication setups involving multiple masters, regardless of the
number of master or slave MySQL servers. This restriction applies
| Thread |
|---|
| • svn commit - mysqldoc@oter02: r25482 - in trunk: dynamic-docs/changelog refman-5.0 refman-5.1 refman-5.5 refman-5.6 refman-6.0 | jon.stephens | 23 Mar |
