List:Commits« Previous MessageNext Message »
From:paul.dubois Date:September 10 2010 4:29pm
Subject:svn commit - mysqldoc@docsrva: r22679 - in trunk: . dynamic-docs/changelog refman-4.1 refman-5.0 refman-5.1 refman-5.5 refman-5.6 refman-6.0
View as plain text  
Author: paul
Date: 2010-09-10 18:29:58 +0200 (Fri, 10 Sep 2010)
New Revision: 22679

Log:
 r63167@frost:  paul | 2010-09-10 11:25:53 -0500
 Update create-table-select section with info about WL#5370 changes


Modified:
   trunk/dynamic-docs/changelog/mysqld-2.xml
   trunk/refman-4.1/sql-syntax-data-definition.xml
   trunk/refman-5.0/sql-syntax-data-definition.xml
   trunk/refman-5.1/installing-updowngrade.xml
   trunk/refman-5.1/replication-notes.xml
   trunk/refman-5.1/sql-syntax-data-definition.xml
   trunk/refman-5.5/installing-updowngrade.xml
   trunk/refman-5.5/replication-notes.xml
   trunk/refman-5.5/sql-syntax-data-definition.xml
   trunk/refman-5.6/replication-notes.xml
   trunk/refman-5.6/sql-syntax-data-definition.xml
   trunk/refman-6.0/sql-syntax-data-definition.xml

Property changes on: trunk
___________________________________________________________________
Name: svk:merge
   - 07c7e7b4-24e3-4b51-89d0-6dc09fec6bec:/mysqldoc-local/mysqldoc/trunk:35498
07c7e7b4-24e3-4b51-89d0-6dc09fec6bec:/mysqldoc-local/trunk:42410
4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:43968
4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/trunk:44480
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:63162
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:39036
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/trunk:39546
   + 07c7e7b4-24e3-4b51-89d0-6dc09fec6bec:/mysqldoc-local/mysqldoc/trunk:35498
07c7e7b4-24e3-4b51-89d0-6dc09fec6bec:/mysqldoc-local/trunk:42410
4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:43968
4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/trunk:44480
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:63167
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:39036
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/trunk:39546


Modified: trunk/dynamic-docs/changelog/mysqld-2.xml
===================================================================
--- trunk/dynamic-docs/changelog/mysqld-2.xml	2010-09-10 15:58:24 UTC (rev 22678)
+++ trunk/dynamic-docs/changelog/mysqld-2.xml	2010-09-10 16:29:58 UTC (rev 22679)
Changed blocks: 1, Lines Added: 2, Lines Deleted: 2; 812 bytes

@@ -26183,8 +26183,8 @@
 
       <para>
         Along with the change just described, the following related
-        change was made: Previously, if an existing updatable view was
-        named as the destination table for
+        change was made: Previously, if an existing view was named as
+        the destination table for
         <literal role="stmt" condition="create-table-select">CREATE
         TABLE IF NOT EXISTS ... SELECT</literal>, rows were inserted
         into the underlying base table and the statement was written to


Modified: trunk/refman-4.1/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-4.1/sql-syntax-data-definition.xml	2010-09-10 15:58:24 UTC (rev 22678)
+++ trunk/refman-4.1/sql-syntax-data-definition.xml	2010-09-10 16:29:58 UTC (rev 22679)
Changed blocks: 2, Lines Added: 4, Lines Deleted: 3; 1131 bytes

@@ -3201,8 +3201,8 @@
       <para>
         For <literal role="stmt" condition="create-table">CREATE TABLE
         ... SELECT</literal>, if <literal>IF NOT EXISTS</literal> is
-        given and the table already exists, MySQL handles the statement
-        as follows:
+        given and the destination table already exists, MySQL handles
+        the statement as follows:
       </para>
 
       <itemizedlist>

@@ -3212,7 +3212,8 @@
             The table definition given in the
             <literal role="stmt">CREATE TABLE</literal> part is ignored.
             No error occurs, even if the definition does not match that
-            of the existing table.
+            of the existing table. MySQL attempts to insert the rows
+            from the <literal role="stmt">SELECT</literal> part anyway.
           </para>
         </listitem>
 


Modified: trunk/refman-5.0/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-5.0/sql-syntax-data-definition.xml	2010-09-10 15:58:24 UTC (rev 22678)
+++ trunk/refman-5.0/sql-syntax-data-definition.xml	2010-09-10 16:29:58 UTC (rev 22679)
Changed blocks: 2, Lines Added: 4, Lines Deleted: 3; 1131 bytes

@@ -4122,8 +4122,8 @@
       <para>
         For <literal role="stmt" condition="create-table">CREATE TABLE
         ... SELECT</literal>, if <literal>IF NOT EXISTS</literal> is
-        given and the table already exists, MySQL handles the statement
-        as follows:
+        given and the destination table already exists, MySQL handles
+        the statement as follows:
       </para>
 
       <itemizedlist>

@@ -4133,7 +4133,8 @@
             The table definition given in the
             <literal role="stmt">CREATE TABLE</literal> part is ignored.
             No error occurs, even if the definition does not match that
-            of the existing table.
+            of the existing table. MySQL attempts to insert the rows
+            from the <literal role="stmt">SELECT</literal> part anyway.
           </para>
         </listitem>
 


Modified: trunk/refman-5.1/installing-updowngrade.xml
===================================================================
--- trunk/refman-5.1/installing-updowngrade.xml	2010-09-10 15:58:24 UTC (rev 22678)
+++ trunk/refman-5.1/installing-updowngrade.xml	2010-09-10 16:29:58 UTC (rev 22679)
Changed blocks: 1, Lines Added: 2, Lines Deleted: 2; 847 bytes

@@ -879,8 +879,8 @@
 
           <para>
             Along with the change just described, the following related
-            change was made: Previously, if an existing updatable view
-            was named as the destination table for
+            change was made: Previously, if an existing view was named
+            as the destination table for
             <literal role="stmt" condition="create-table-select">CREATE
             TABLE IF NOT EXISTS ... SELECT</literal>, rows were inserted
             into the underlying base table and the statement was written


Modified: trunk/refman-5.1/replication-notes.xml
===================================================================
--- trunk/refman-5.1/replication-notes.xml	2010-09-10 15:58:24 UTC (rev 22678)
+++ trunk/refman-5.1/replication-notes.xml	2010-09-10 16:29:58 UTC (rev 22679)
Changed blocks: 1, Lines Added: 11, Lines Deleted: 0; 894 bytes

@@ -547,6 +547,17 @@
 
       </itemizedlist>
 
+      <para>
+        These version dependencies arise due to a change in MySQL 5.5.6
+        in handling of
+        <literal role="stmt" condition="create-table-select">CREATE
+        TABLE ... SELECT</literal> not to insert rows if the destination
+        table already exists, and a change made in MySQL 5.1.51 to
+        preserve forward compatibility in replication of such statements
+        from a 5.1 master to a 5.5 slave. For details, see
+        <xref linkend="create-table-select"/>.
+      </para>
+
     </section>
 
     <section id="replication-features-current-user">


Modified: trunk/refman-5.1/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-5.1/sql-syntax-data-definition.xml	2010-09-10 15:58:24 UTC (rev 22678)
+++ trunk/refman-5.1/sql-syntax-data-definition.xml	2010-09-10 16:29:58 UTC (rev 22679)
Changed blocks: 3, Lines Added: 136, Lines Deleted: 3; 6893 bytes

@@ -7536,8 +7536,9 @@
       <para>
         For <literal role="stmt" condition="create-table">CREATE TABLE
         ... SELECT</literal>, if <literal>IF NOT EXISTS</literal> is
-        given and the table already exists, MySQL handles the statement
-        as follows:
+        given and the destination table already exists, the result is
+        version dependent. Before MySQL 5.5.6, MySQL handles the
+        statement as follows:
       </para>
 
       <itemizedlist>

@@ -7547,7 +7548,8 @@
             The table definition given in the
             <literal role="stmt">CREATE TABLE</literal> part is ignored.
             No error occurs, even if the definition does not match that
-            of the existing table.
+            of the existing table. MySQL attempts to insert the rows
+            from the <literal role="stmt">SELECT</literal> part anyway.
           </para>
         </listitem>
 

@@ -7612,6 +7614,137 @@
 </programlisting>
 
       <para>
+        As of MySQL 5.5.6, handling of
+        <literal role="stmt" condition="create-table-select">CREATE
+        TABLE IF NOT EXISTS ... SELECT</literal> statements was changed
+        for the case that the destination table already exists. This
+        change also involves a change in MySQL 5.1 beginning with
+        5.1.51.
+      </para>
+
+      <itemizedlist>
+
+        <listitem>
+          <para>
+            Previously, for
+            <literal role="stmt" condition="create-table-select">CREATE
+            TABLE IF NOT EXISTS ... SELECT</literal>, MySQL produced a
+            warning that the table exists, but inserted the rows and
+            wrote the statement to the binary log anyway. By contrast,
+            <literal role="stmt" condition="create-table-select">CREATE
+            TABLE ... SELECT</literal> (without <literal>IF NOT
+            EXISTS</literal>) failed with an error, but MySQL inserted
+            no rows and did not write the statement to the binary log.
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            MySQL now handles both statements the same way when the
+            destination table exists, in that neither statement inserts
+            rows or is written to the binary log. The difference between
+            them is that MySQL produces a warning when <literal>IF NOT
+            EXISTS</literal> is present and an error when it is not.
+          </para>
+        </listitem>
+
+      </itemizedlist>
+
+      <para>
+        This change means that, for the preceding example, the
+        <literal role="stmt" condition="create-table-select">CREATE
+        TABLE IF NOT EXISTS ... SELECT</literal> statement inserts
+        nothing into the destination table as of MySQL 5.5.6.
+      </para>
+
+      <para>
+        This change in handling of <literal>IF NOT EXISTS</literal>
+        results in an incompatibility for statement-based replication
+        from a MySQL 5.1 master with the original behavior and a MySQL
+        5.5 slave with the new behavior. Suppose that
+        <literal role="stmt" condition="create-table-select">CREATE
+        TABLE IF NOT EXISTS ... SELECT</literal> is executed on the
+        master and the destination table exists. The result is that rows
+        are inserted on the master but not on the slave. (Row-based
+        replication does not have this problem.)
+      </para>
+
+      <para>
+        To address this issue, statement-based binary logging for
+        <literal role="stmt" condition="create-table-select">CREATE
+        TABLE IF NOT EXISTS ... SELECT</literal> is changed in MySQL 5.1
+        as of 5.1.51:
+      </para>
+
+      <itemizedlist>
+
+        <listitem>
+          <para>
+            If the destination table does not exist, there is no change:
+            The statement is logged as is.
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            If the destination table does exist, the statement is logged
+            as the equivalent pair of
+            <literal role="stmt" condition="create-table-select">CREATE
+            TABLE IF NOT EXISTS</literal> and
+            <literal role="stmt" condition="insert-select">INSERT ...
+            SELECT</literal> statements. (If the
+            <literal role="stmt">SELECT</literal> in the original
+            statement is preceded by <literal>IGNORE</literal> or
+            <literal role="stmt">REPLACE</literal>, the
+            <literal role="stmt">INSERT</literal> becomes
+            <literal role="stmt" condition="insert">INSERT
+            IGNORE</literal> or <literal role="stmt">REPLACE</literal>,
+            respectively.)
+          </para>
+        </listitem>
+
+      </itemizedlist>
+
+      <para>
+        This change provides forward compatibility for statement-based
+        replication from MySQL 5.1 to 5.5 because when the destination
+        table exists, the rows will be inserted on both the master and
+        slave. To take advantage of this compatibility measure, the 5.1
+        server must be at least 5.1.51 and the 5.5 server must be at
+        least 5.5.6.
+      </para>
+
+      <para>
+        To upgrade an existing 5.1-to-5.5 replication scenario, upgrade
+        the master first to 5.1.51 or higher. Note that this differs
+        from the usual replication upgrade advice of upgrading the slave
+        first.
+      </para>
+
+      <para>
+        A workaround for applications that wish to achieve the original
+        effect (rows inserted regardless of whether the destination
+        table exists) is to use
+        <literal role="stmt" condition="create-table-select">CREATE
+        TABLE IF NOT EXISTS</literal> and
+        <literal role="stmt" condition="insert-select">INSERT ...
+        SELECT</literal> statements rather than
+        <literal role="stmt" condition="create-table-select">CREATE
+        TABLE IF NOT EXISTS ... SELECT</literal> statements.
+      </para>
+
+      <para>
+        Along with the change just described, the following related
+        change was made: Previously, if an existing view was named as
+        the destination table for
+        <literal role="stmt" condition="create-table-select">CREATE
+        TABLE IF NOT EXISTS ... SELECT</literal>, rows were inserted
+        into the underlying base table and the statement was written to
+        the binary log. As of MySQL 5.1.51 and 5.5.6, nothing is
+        inserted or logged.
+      </para>
+
+      <para>
         To ensure that the binary log can be used to re-create the
         original tables, MySQL does not permit concurrent inserts during
         <literal role="stmt" condition="create-table">CREATE TABLE ...


Modified: trunk/refman-5.5/installing-updowngrade.xml
===================================================================
--- trunk/refman-5.5/installing-updowngrade.xml	2010-09-10 15:58:24 UTC (rev 22678)
+++ trunk/refman-5.5/installing-updowngrade.xml	2010-09-10 16:29:58 UTC (rev 22679)
Changed blocks: 1, Lines Added: 2, Lines Deleted: 2; 847 bytes

@@ -620,8 +620,8 @@
 
           <para>
             Along with the change just described, the following related
-            change was made: Previously, if an existing updatable view
-            was named as the destination table for
+            change was made: Previously, if an existing view was named
+            as the destination table for
             <literal role="stmt" condition="create-table-select">CREATE
             TABLE IF NOT EXISTS ... SELECT</literal>, rows were inserted
             into the underlying base table and the statement was written


Modified: trunk/refman-5.5/replication-notes.xml
===================================================================
--- trunk/refman-5.5/replication-notes.xml	2010-09-10 15:58:24 UTC (rev 22678)
+++ trunk/refman-5.5/replication-notes.xml	2010-09-10 16:29:58 UTC (rev 22679)
Changed blocks: 1, Lines Added: 11, Lines Deleted: 0; 896 bytes

@@ -506,6 +506,17 @@
 
       </itemizedlist>
 
+      <para>
+        These version dependencies arise due to a change in MySQL 5.5.6
+        in handling of
+        <literal role="stmt" condition="create-table-select">CREATE
+        TABLE ... SELECT</literal> not to insert rows if the destination
+        table already exists, and a change made in MySQL 5.1.51 to
+        preserve forward compatibility in replication of such statements
+        from a 5.1 master to a 5.5 slave. For details, see
+        <xref linkend="create-table-select"/>.
+      </para>
+
     </section>
 
     <section id="replication-features-drop-if-exists">


Modified: trunk/refman-5.5/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-5.5/sql-syntax-data-definition.xml	2010-09-10 15:58:24 UTC (rev 22678)
+++ trunk/refman-5.5/sql-syntax-data-definition.xml	2010-09-10 16:29:58 UTC (rev 22679)
Changed blocks: 3, Lines Added: 136, Lines Deleted: 3; 6893 bytes

@@ -6491,8 +6491,9 @@
       <para>
         For <literal role="stmt" condition="create-table">CREATE TABLE
         ... SELECT</literal>, if <literal>IF NOT EXISTS</literal> is
-        given and the table already exists, MySQL handles the statement
-        as follows:
+        given and the destination table already exists, the result is
+        version dependent. Before MySQL 5.5.6, MySQL handles the
+        statement as follows:
       </para>
 
       <itemizedlist>

@@ -6502,7 +6503,8 @@
             The table definition given in the
             <literal role="stmt">CREATE TABLE</literal> part is ignored.
             No error occurs, even if the definition does not match that
-            of the existing table.
+            of the existing table. MySQL attempts to insert the rows
+            from the <literal role="stmt">SELECT</literal> part anyway.
           </para>
         </listitem>
 

@@ -6567,6 +6569,137 @@
 </programlisting>
 
       <para>
+        As of MySQL 5.5.6, handling of
+        <literal role="stmt" condition="create-table-select">CREATE
+        TABLE IF NOT EXISTS ... SELECT</literal> statements was changed
+        for the case that the destination table already exists. This
+        change also involves a change in MySQL 5.1 beginning with
+        5.1.51.
+      </para>
+
+      <itemizedlist>
+
+        <listitem>
+          <para>
+            Previously, for
+            <literal role="stmt" condition="create-table-select">CREATE
+            TABLE IF NOT EXISTS ... SELECT</literal>, MySQL produced a
+            warning that the table exists, but inserted the rows and
+            wrote the statement to the binary log anyway. By contrast,
+            <literal role="stmt" condition="create-table-select">CREATE
+            TABLE ... SELECT</literal> (without <literal>IF NOT
+            EXISTS</literal>) failed with an error, but MySQL inserted
+            no rows and did not write the statement to the binary log.
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            MySQL now handles both statements the same way when the
+            destination table exists, in that neither statement inserts
+            rows or is written to the binary log. The difference between
+            them is that MySQL produces a warning when <literal>IF NOT
+            EXISTS</literal> is present and an error when it is not.
+          </para>
+        </listitem>
+
+      </itemizedlist>
+
+      <para>
+        This change means that, for the preceding example, the
+        <literal role="stmt" condition="create-table-select">CREATE
+        TABLE IF NOT EXISTS ... SELECT</literal> statement inserts
+        nothing into the destination table as of MySQL 5.5.6.
+      </para>
+
+      <para>
+        This change in handling of <literal>IF NOT EXISTS</literal>
+        results in an incompatibility for statement-based replication
+        from a MySQL 5.1 master with the original behavior and a MySQL
+        5.5 slave with the new behavior. Suppose that
+        <literal role="stmt" condition="create-table-select">CREATE
+        TABLE IF NOT EXISTS ... SELECT</literal> is executed on the
+        master and the destination table exists. The result is that rows
+        are inserted on the master but not on the slave. (Row-based
+        replication does not have this problem.)
+      </para>
+
+      <para>
+        To address this issue, statement-based binary logging for
+        <literal role="stmt" condition="create-table-select">CREATE
+        TABLE IF NOT EXISTS ... SELECT</literal> is changed in MySQL 5.1
+        as of 5.1.51:
+      </para>
+
+      <itemizedlist>
+
+        <listitem>
+          <para>
+            If the destination table does not exist, there is no change:
+            The statement is logged as is.
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            If the destination table does exist, the statement is logged
+            as the equivalent pair of
+            <literal role="stmt" condition="create-table-select">CREATE
+            TABLE IF NOT EXISTS</literal> and
+            <literal role="stmt" condition="insert-select">INSERT ...
+            SELECT</literal> statements. (If the
+            <literal role="stmt">SELECT</literal> in the original
+            statement is preceded by <literal>IGNORE</literal> or
+            <literal role="stmt">REPLACE</literal>, the
+            <literal role="stmt">INSERT</literal> becomes
+            <literal role="stmt" condition="insert">INSERT
+            IGNORE</literal> or <literal role="stmt">REPLACE</literal>,
+            respectively.)
+          </para>
+        </listitem>
+
+      </itemizedlist>
+
+      <para>
+        This change provides forward compatibility for statement-based
+        replication from MySQL 5.1 to 5.5 because when the destination
+        table exists, the rows will be inserted on both the master and
+        slave. To take advantage of this compatibility measure, the 5.1
+        server must be at least 5.1.51 and the 5.5 server must be at
+        least 5.5.6.
+      </para>
+
+      <para>
+        To upgrade an existing 5.1-to-5.5 replication scenario, upgrade
+        the master first to 5.1.51 or higher. Note that this differs
+        from the usual replication upgrade advice of upgrading the slave
+        first.
+      </para>
+
+      <para>
+        A workaround for applications that wish to achieve the original
+        effect (rows inserted regardless of whether the destination
+        table exists) is to use
+        <literal role="stmt" condition="create-table-select">CREATE
+        TABLE IF NOT EXISTS</literal> and
+        <literal role="stmt" condition="insert-select">INSERT ...
+        SELECT</literal> statements rather than
+        <literal role="stmt" condition="create-table-select">CREATE
+        TABLE IF NOT EXISTS ... SELECT</literal> statements.
+      </para>
+
+      <para>
+        Along with the change just described, the following related
+        change was made: Previously, if an existing view was named as
+        the destination table for
+        <literal role="stmt" condition="create-table-select">CREATE
+        TABLE IF NOT EXISTS ... SELECT</literal>, rows were inserted
+        into the underlying base table and the statement was written to
+        the binary log. As of MySQL 5.1.51 and 5.5.6, nothing is
+        inserted or logged.
+      </para>
+
+      <para>
         To ensure that the binary log can be used to re-create the
         original tables, MySQL does not permit concurrent inserts during
         <literal role="stmt" condition="create-table">CREATE TABLE ...


Modified: trunk/refman-5.6/replication-notes.xml
===================================================================
--- trunk/refman-5.6/replication-notes.xml	2010-09-10 15:58:24 UTC (rev 22678)
+++ trunk/refman-5.6/replication-notes.xml	2010-09-10 16:29:58 UTC (rev 22679)
Changed blocks: 1, Lines Added: 11, Lines Deleted: 0; 896 bytes

@@ -506,6 +506,17 @@
 
       </itemizedlist>
 
+      <para>
+        These version dependencies arise due to a change in MySQL 5.5.6
+        in handling of
+        <literal role="stmt" condition="create-table-select">CREATE
+        TABLE ... SELECT</literal> not to insert rows if the destination
+        table already exists, and a change made in MySQL 5.1.51 to
+        preserve forward compatibility in replication of such statements
+        from a 5.1 master to a 5.5 slave. For details, see
+        <xref linkend="create-table-select"/>.
+      </para>
+
     </section>
 
     <section id="replication-features-drop-if-exists">


Modified: trunk/refman-5.6/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-5.6/sql-syntax-data-definition.xml	2010-09-10 15:58:24 UTC (rev 22678)
+++ trunk/refman-5.6/sql-syntax-data-definition.xml	2010-09-10 16:29:58 UTC (rev 22679)
Changed blocks: 3, Lines Added: 136, Lines Deleted: 3; 6893 bytes

@@ -6523,8 +6523,9 @@
       <para>
         For <literal role="stmt" condition="create-table">CREATE TABLE
         ... SELECT</literal>, if <literal>IF NOT EXISTS</literal> is
-        given and the table already exists, MySQL handles the statement
-        as follows:
+        given and the destination table already exists, the result is
+        version dependent. Before MySQL 5.5.6, MySQL handles the
+        statement as follows:
       </para>
 
       <itemizedlist>

@@ -6534,7 +6535,8 @@
             The table definition given in the
             <literal role="stmt">CREATE TABLE</literal> part is ignored.
             No error occurs, even if the definition does not match that
-            of the existing table.
+            of the existing table. MySQL attempts to insert the rows
+            from the <literal role="stmt">SELECT</literal> part anyway.
           </para>
         </listitem>
 

@@ -6599,6 +6601,137 @@
 </programlisting>
 
       <para>
+        As of MySQL 5.5.6, handling of
+        <literal role="stmt" condition="create-table-select">CREATE
+        TABLE IF NOT EXISTS ... SELECT</literal> statements was changed
+        for the case that the destination table already exists. This
+        change also involves a change in MySQL 5.1 beginning with
+        5.1.51.
+      </para>
+
+      <itemizedlist>
+
+        <listitem>
+          <para>
+            Previously, for
+            <literal role="stmt" condition="create-table-select">CREATE
+            TABLE IF NOT EXISTS ... SELECT</literal>, MySQL produced a
+            warning that the table exists, but inserted the rows and
+            wrote the statement to the binary log anyway. By contrast,
+            <literal role="stmt" condition="create-table-select">CREATE
+            TABLE ... SELECT</literal> (without <literal>IF NOT
+            EXISTS</literal>) failed with an error, but MySQL inserted
+            no rows and did not write the statement to the binary log.
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            MySQL now handles both statements the same way when the
+            destination table exists, in that neither statement inserts
+            rows or is written to the binary log. The difference between
+            them is that MySQL produces a warning when <literal>IF NOT
+            EXISTS</literal> is present and an error when it is not.
+          </para>
+        </listitem>
+
+      </itemizedlist>
+
+      <para>
+        This change means that, for the preceding example, the
+        <literal role="stmt" condition="create-table-select">CREATE
+        TABLE IF NOT EXISTS ... SELECT</literal> statement inserts
+        nothing into the destination table as of MySQL 5.5.6.
+      </para>
+
+      <para>
+        This change in handling of <literal>IF NOT EXISTS</literal>
+        results in an incompatibility for statement-based replication
+        from a MySQL 5.1 master with the original behavior and a MySQL
+        5.5 slave with the new behavior. Suppose that
+        <literal role="stmt" condition="create-table-select">CREATE
+        TABLE IF NOT EXISTS ... SELECT</literal> is executed on the
+        master and the destination table exists. The result is that rows
+        are inserted on the master but not on the slave. (Row-based
+        replication does not have this problem.)
+      </para>
+
+      <para>
+        To address this issue, statement-based binary logging for
+        <literal role="stmt" condition="create-table-select">CREATE
+        TABLE IF NOT EXISTS ... SELECT</literal> is changed in MySQL 5.1
+        as of 5.1.51:
+      </para>
+
+      <itemizedlist>
+
+        <listitem>
+          <para>
+            If the destination table does not exist, there is no change:
+            The statement is logged as is.
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            If the destination table does exist, the statement is logged
+            as the equivalent pair of
+            <literal role="stmt" condition="create-table-select">CREATE
+            TABLE IF NOT EXISTS</literal> and
+            <literal role="stmt" condition="insert-select">INSERT ...
+            SELECT</literal> statements. (If the
+            <literal role="stmt">SELECT</literal> in the original
+            statement is preceded by <literal>IGNORE</literal> or
+            <literal role="stmt">REPLACE</literal>, the
+            <literal role="stmt">INSERT</literal> becomes
+            <literal role="stmt" condition="insert">INSERT
+            IGNORE</literal> or <literal role="stmt">REPLACE</literal>,
+            respectively.)
+          </para>
+        </listitem>
+
+      </itemizedlist>
+
+      <para>
+        This change provides forward compatibility for statement-based
+        replication from MySQL 5.1 to 5.5 because when the destination
+        table exists, the rows will be inserted on both the master and
+        slave. To take advantage of this compatibility measure, the 5.1
+        server must be at least 5.1.51 and the 5.5 server must be at
+        least 5.5.6.
+      </para>
+
+      <para>
+        To upgrade an existing 5.1-to-5.5 replication scenario, upgrade
+        the master first to 5.1.51 or higher. Note that this differs
+        from the usual replication upgrade advice of upgrading the slave
+        first.
+      </para>
+
+      <para>
+        A workaround for applications that wish to achieve the original
+        effect (rows inserted regardless of whether the destination
+        table exists) is to use
+        <literal role="stmt" condition="create-table-select">CREATE
+        TABLE IF NOT EXISTS</literal> and
+        <literal role="stmt" condition="insert-select">INSERT ...
+        SELECT</literal> statements rather than
+        <literal role="stmt" condition="create-table-select">CREATE
+        TABLE IF NOT EXISTS ... SELECT</literal> statements.
+      </para>
+
+      <para>
+        Along with the change just described, the following related
+        change was made: Previously, if an existing view was named as
+        the destination table for
+        <literal role="stmt" condition="create-table-select">CREATE
+        TABLE IF NOT EXISTS ... SELECT</literal>, rows were inserted
+        into the underlying base table and the statement was written to
+        the binary log. As of MySQL 5.1.51 and 5.5.6, nothing is
+        inserted or logged.
+      </para>
+
+      <para>
         To ensure that the binary log can be used to re-create the
         original tables, MySQL does not permit concurrent inserts during
         <literal role="stmt" condition="create-table">CREATE TABLE ...


Modified: trunk/refman-6.0/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-6.0/sql-syntax-data-definition.xml	2010-09-10 15:58:24 UTC (rev 22678)
+++ trunk/refman-6.0/sql-syntax-data-definition.xml	2010-09-10 16:29:58 UTC (rev 22679)
Changed blocks: 2, Lines Added: 4, Lines Deleted: 3; 1131 bytes

@@ -6399,8 +6399,8 @@
       <para>
         For <literal role="stmt" condition="create-table">CREATE TABLE
         ... SELECT</literal>, if <literal>IF NOT EXISTS</literal> is
-        given and the table already exists, MySQL handles the statement
-        as follows:
+        given and the destination table already exists, MySQL handles
+        the statement as follows:
       </para>
 
       <itemizedlist>

@@ -6410,7 +6410,8 @@
             The table definition given in the
             <literal role="stmt">CREATE TABLE</literal> part is ignored.
             No error occurs, even if the definition does not match that
-            of the existing table.
+            of the existing table. MySQL attempts to insert the rows
+            from the <literal role="stmt">SELECT</literal> part anyway.
           </para>
         </listitem>
 


Thread
svn commit - mysqldoc@docsrva: r22679 - in trunk: . dynamic-docs/changelog refman-4.1 refman-5.0 refman-5.1 refman-5.5 refman-5.6 refman-6.0paul.dubois10 Sep