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.0 | paul.dubois | 10 Sep |
