List:Commits« Previous MessageNext Message »
From:jon.stephens Date:March 23 2011 11:02am
Subject: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
View as plain text  
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&mdash;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.0jon.stephens23 Mar