List:Commits« Previous MessageNext Message »
From:jon.stephens Date:February 22 2009 8:51pm
Subject:svn commit - mysqldoc@docsrva: r13886 - in trunk: refman-5.1 refman-6.0
View as plain text  
Author: jstephens
Date: 2009-02-22 21:51:29 +0100 (Sun, 22 Feb 2009)
New Revision: 13886

Log:

Fixes Docs Bug #42927
  (Wanted: list of statements that are unsafe for SBR)



Modified:
   trunk/refman-5.1/replication-configuration.xml
   trunk/refman-6.0/replication-configuration.xml


Modified: trunk/refman-5.1/replication-configuration.xml
===================================================================
--- trunk/refman-5.1/replication-configuration.xml	2009-02-22 20:34:00 UTC (rev 13885)
+++ trunk/refman-5.1/replication-configuration.xml	2009-02-22 20:51:29 UTC (rev 13886)
Changed blocks: 9, Lines Added: 156, Lines Deleted: 66; 12309 bytes

@@ -1401,12 +1401,20 @@
         <para>
           The default binary logging format in all MySQL Cluster NDB
           6.1, 6.2, 6.3, and later 6.x releases is
-          <literal>MIXED</literal>. See
-          <xref linkend="mysql-cluster-replication-general"/>, for more
-          information.
+          <literal>ROW</literal>. MySQL Cluster Replication always uses
+          row-based replication, and the
+          <literal role="se">NDBCLUSTER</literal> storage engine is
+          incompatible with statement-based replication. Using
+          <literal role="se">NDBCLUSTER</literal> sets row-based logging
+          format automatically.
         </para>
 
       </formalpara>
+
+      <para>
+        See <xref linkend="mysql-cluster-replication-general"/>, for
+        more information.
+      </para>
     </note>
 
     <para>

@@ -1433,17 +1441,6 @@
       <xref linkend="replication-sbr-rbr"/>.
     </para>
 
-    <note>
-      <para>
-        MySQL Cluster Replication always uses row-based replication, and
-        the <literal role="se">NDBCLUSTER</literal> storage engine is
-        incompatible with statement-based replication. Using
-        <literal role="se">NDBCLUSTER</literal> sets row-based logging
-        format automatically. For more information, see
-        <xref linkend="mysql-cluster-replication"/>.
-      </para>
-    </note>
-
     <para>
       With statement-based replication, you may encounter issues with
       replicating stored routines or triggers. You can avoid these

@@ -1528,6 +1525,16 @@
 
       <title>Comparison of Statement-Based and Row-Based Replication</title>
 
+      <indexterm>
+        <primary>replication formats</primary>
+        <secondary>compared</secondary>
+      </indexterm>
+
+      <indexterm>
+        <primary>replication</primary>
+        <secondary>row-based vs statement-based</secondary>
+      </indexterm>
+
       <para>
         Each binary logging format has advantages and disadvantages. For
         most users, the mixed replication format should provide the best

@@ -1544,6 +1551,11 @@
         replication:</emphasis>
       </para>
 
+      <indexterm>
+        <primary>statement-based replication</primary>
+        <secondary>advantages</secondary>
+      </indexterm>
+
       <itemizedlist>
 
         <listitem>

@@ -1583,86 +1595,151 @@
         replication:</emphasis>
       </para>
 
+      <indexterm>
+        <primary>statement-based replication</primary>
+        <secondary>disadvantages</secondary>
+      </indexterm>
+
       <itemizedlist>
 
         <listitem>
-          <para>
-            Not all <literal role="stmt">UPDATE</literal> statements can
-            be replicated: Any non-deterministic behavior (for example,
-            when using random functions in an SQL statement) is hard to
-            replicate when using statement-based replication. For
-            statements that use a non-deterministic user-defined
-            function (UDF), it is not possible to replicate the result
-            using statement-based replication, whereas row-based
-            replication simply replicates the value returned by the UDF.
-          </para>
-        </listitem>
+          <indexterm>
+            <primary>replication</primary>
+            <secondary>statements incompatible with STATEMENT mode</secondary>
+          </indexterm>
 
-        <listitem>
-          <para>
-            A statement cannot be replicated properly if it uses a UDF
-            that is non-deterministic, since the value returned by such
-            a UDF depends on factors other than the parameters supplied
-            to the UDF.
-          </para>
-        </listitem>
+          <indexterm>
+            <primary>statement-based replication</primary>
+            <secondary>unsafe statements</secondary>
+          </indexterm>
 
-        <listitem>
-          <para>
-            Statements using any of the following functions cannot be
-            replicated properly:
-          </para>
+          <formalpara>
 
+            <title>Statements which are unsafe for SBR</title>
+
+            <para>
+              Not all statements which modify data (such as
+              <literal role="stmt">INSERT</literal>
+              <literal role="stmt">DELETE</literal>,
+              <literal role="stmt">UPDATE</literal>, and
+              <literal role="">REPLACE</literal> statements) can be
+              replicated using statement-based replication. Any
+              non-deterministic behavior is difficult to replicate when
+              using statement-based replication. Examples of such DML
+              (Data Modification Language) statements include the
+              following:
+            </para>
+
+          </formalpara>
+
           <itemizedlist>
 
             <listitem>
               <para>
-                <literal role="func">LOAD_FILE()</literal>
+                A statement that depends on a UDF or stored program that
+                is non-deterministic, since the value returned by such a
+                UDF or stored program or depends on factors other than
+                the parameters supplied to it. (Row-based replication,
+                however, simply replicates the value returned by the UDF
+                or stored program, so its effect on table rows and data
+                is the same on both the master and slave.) See
+                <xref linkend="replication-features-invoked"/>, for more
+                information.
               </para>
             </listitem>
 
             <listitem>
               <para>
-                <literal role="func">UUID()</literal>,
-                <literal role="func">UUID_SHORT()</literal>
+                <literal role="stmt">DELETE</literal> and
+                <literal role="stmt">UPDATE</literal> statements that
+                use a <literal>LIMIT</literal> clause without an
+                <literal>ORDER BY</literal> are also non-deterministic.
+                See <xref linkend="replication-features-limit"/>.
               </para>
             </listitem>
 
             <listitem>
               <para>
-                <literal role="func">USER()</literal>
+                Statements using any of the following functions cannot
+                be replicated properly using statement-based
+                replication:
               </para>
-            </listitem>
 
-            <listitem>
+              <itemizedlist>
+
+                <listitem>
+                  <para>
+                    <literal role="func">LOAD_FILE()</literal>
+                  </para>
+                </listitem>
+
+                <listitem>
+                  <para>
+                    <literal role="func">UUID()</literal>,
+                    <literal role="func">UUID_SHORT()</literal>
+                  </para>
+                </listitem>
+
+                <listitem>
+                  <para>
+                    <literal role="func">USER()</literal>
+                  </para>
+                </listitem>
+
+                <listitem>
+                  <para>
+                    <literal role="func">FOUND_ROWS()</literal>
+                  </para>
+                </listitem>
+
+                <listitem>
+                  <para>
+                    <literal role="func">SYSDATE()</literal> (unless the
+                    server is started with the
+                    <option>--sysdate-is-now</option> option)
+                  </para>
+                </listitem>
+
+              </itemizedlist>
+
               <para>
-                <literal role="func">FOUND_ROWS()</literal>
+                However, all other functions are replicated correctly
+                using statement-based replication, including
+                <literal role="func">RAND()</literal>,
+                <literal role="func">NOW()</literal>,
+                <literal role="stmt" condition="load-data">LOAD DATA
+                INFILE</literal>, and so forth.
               </para>
-            </listitem>
 
-            <listitem>
               <para>
-                <literal role="func">SYSDATE()</literal> (unless the
-                server is started with the
-                <option>--sysdate-is-now</option> option)
+                For more information, see
+                <xref linkend="replication-features-functions"/>.
               </para>
             </listitem>
 
           </itemizedlist>
 
           <para>
-            All other functions are replicated correctly (including
-            <literal role="func">RAND()</literal>,
-            <literal role="func">NOW()</literal>,
-            <literal role="stmt" condition="load-data">LOAD DATA
-            INFILE</literal>, and so forth).
+            When using statement-based replication, statements that
+            cannot be replicated correctly using statement-based mode
+            are logged with a warning like the one shown here:
           </para>
+
+<programlisting>
+090213 16:58:54 [Warning] Statement is not safe to log in statement format.
+</programlisting>
+
+          <para>
+            A similar warning is also issued to the client in such
+            cases.
+          </para>
         </listitem>
 
         <listitem>
           <para>
-            <literal>INSERT ... SELECT</literal> requires a greater
-            number of row-level locks than with row-based replication.
+            <literal role="stmt" condition="insert">INSERT ...
+            SELECT</literal> requires a greater number of row-level
+            locks than with row-based replication.
           </para>
         </listitem>
 

@@ -1677,7 +1754,7 @@
 
         <listitem>
           <para>
-            For <literal>InnoDB</literal>: An
+            For <literal role="se">InnoDB</literal>: An
             <literal role="stmt">INSERT</literal> statement that uses
             <literal>AUTO_INCREMENT</literal> blocks other
             non-conflicting <literal role="stmt">INSERT</literal>

@@ -1714,23 +1791,31 @@
             If there is an error in evaluation on the slave,
             particularly when executing complex statements, then using
             statement-based replication may slowly increase the margin
-            of error across the affected rows over time.
+            of error across the affected rows over time. See
+            <xref linkend="replication-features-slaveerrors"/>.
           </para>
         </listitem>
 
         <listitem>
           <para>
-            Tables have to be (almost) identical on master and slave.
+            Tables must be (nearly) identical on master and slave. See
+            <xref linkend="replication-features-differing-tables"/>, for
+            more information.
           </para>
         </listitem>
 
       </itemizedlist>
 
       <para>
-        <emphasis role="bold"> Advantages of row-based
+        <emphasis role="bold">Advantages of row-based
         replication:</emphasis>
       </para>
 
+      <indexterm>
+        <primary>row-based replication</primary>
+        <secondary>advantages</secondary>
+      </indexterm>
+
       <itemizedlist>
 
         <listitem>

@@ -1740,12 +1825,12 @@
           </para>
 
           <para>
-            For MySQL versions earlier than 5.1.14, DDL (data definition
-            language) statements such as <literal role="stmt">CREATE
+            For MySQL versions earlier than 5.1.14, DDL (Data Definition
+            Language) statements such as <literal role="stmt">CREATE
             TABLE</literal> are replicated using statement-based
-            replication, while DML (data manipulation language)
-            statements, as well as <literal role="stmt">GRANT</literal>
-            and <literal role="stmt">REVOKE</literal> statements, are
+            replication, while DML statements, as well as
+            <literal role="stmt">GRANT</literal> and
+            <literal role="stmt">REVOKE</literal> statements, are
             replicated using row-based-replication.
           </para>
 

@@ -1829,6 +1914,11 @@
         replication:</emphasis>
       </para>
 
+      <indexterm>
+        <primary>row-based replication</primary>
+        <secondary>disadvantages</secondary>
+      </indexterm>
+
       <itemizedlist>
 
         <listitem>


Modified: trunk/refman-6.0/replication-configuration.xml
===================================================================
--- trunk/refman-6.0/replication-configuration.xml	2009-02-22 20:34:00 UTC (rev 13885)
+++ trunk/refman-6.0/replication-configuration.xml	2009-02-22 20:51:29 UTC (rev 13886)
Changed blocks: 6, Lines Added: 140, Lines Deleted: 47; 9958 bytes

@@ -1498,6 +1498,16 @@
 
       <title>Comparison of Statement-Based and Row-Based Replication</title>
 
+      <indexterm>
+        <primary>replication formats</primary>
+        <secondary>compared</secondary>
+      </indexterm>
+
+      <indexterm>
+        <primary>replication</primary>
+        <secondary>row-based vs statement-based</secondary>
+      </indexterm>
+
       <para>
         Each binary logging format has advantages and disadvantages. For
         most users, the mixed replication format should provide the best

@@ -1514,6 +1524,11 @@
         replication:</emphasis>
       </para>
 
+      <indexterm>
+        <primary>statement-based replication</primary>
+        <secondary>advantages</secondary>
+      </indexterm>
+
       <itemizedlist>
 
         <listitem>

@@ -1553,86 +1568,151 @@
         replication:</emphasis>
       </para>
 
+      <indexterm>
+        <primary>statement-based replication</primary>
+        <secondary>disadvantages</secondary>
+      </indexterm>
+
       <itemizedlist>
 
         <listitem>
-          <para>
-            Not all <literal role="stmt">UPDATE</literal> statements can
-            be replicated: Any non-deterministic behavior (for example,
-            when using random functions in an SQL statement) is hard to
-            replicate when using statement-based replication. For
-            statements that use a non-deterministic user-defined
-            function (UDF), it is not possible to replicate the result
-            using statement-based replication, whereas row-based
-            replication simply replicates the value returned by the UDF.
-          </para>
-        </listitem>
+          <indexterm>
+            <primary>replication</primary>
+            <secondary>statements incompatible with STATEMENT mode</secondary>
+          </indexterm>
 
-        <listitem>
-          <para>
-            A statement cannot be replicated properly if it uses a UDF
-            that is non-deterministic, since the value returned by such
-            a UDF depends on factors other than the parameters supplied
-            to the UDF.
-          </para>
-        </listitem>
+          <indexterm>
+            <primary>statement-based replication</primary>
+            <secondary>unsafe statements</secondary>
+          </indexterm>
 
-        <listitem>
-          <para>
-            Statements using any of the following functions cannot be
-            replicated properly:
-          </para>
+          <formalpara>
 
+            <title>Statements which are unsafe for SBR</title>
+
+            <para>
+              Not all statements which modify data (such as
+              <literal role="stmt">INSERT</literal>
+              <literal role="stmt">DELETE</literal>,
+              <literal role="stmt">UPDATE</literal>, and
+              <literal role="">REPLACE</literal> statements) can be
+              replicated using statement-based replication. Any
+              non-deterministic behavior is difficult to replicate when
+              using statement-based replication. Examples of such DML
+              (Data Modification Language) statements include the
+              following:
+            </para>
+
+          </formalpara>
+
           <itemizedlist>
 
             <listitem>
               <para>
-                <literal role="func">LOAD_FILE()</literal>
+                A statement that depends on a UDF or stored program that
+                is non-deterministic, since the value returned by such a
+                UDF or stored program or depends on factors other than
+                the parameters supplied to it. (Row-based replication,
+                however, simply replicates the value returned by the UDF
+                or stored program, so its effect on table rows and data
+                is the same on both the master and slave.) See
+                <xref linkend="replication-features-invoked"/>, for more
+                information.
               </para>
             </listitem>
 
             <listitem>
               <para>
-                <literal role="func">UUID()</literal>,
-                <literal role="func">UUID_SHORT()</literal>
+                <literal role="stmt">DELETE</literal> and
+                <literal role="stmt">UPDATE</literal> statements that
+                use a <literal>LIMIT</literal> clause without an
+                <literal>ORDER BY</literal> are also non-deterministic.
+                See <xref linkend="replication-features-limit"/>.
               </para>
             </listitem>
 
             <listitem>
               <para>
-                <literal role="func">USER()</literal>
+                Statements using any of the following functions cannot
+                be replicated properly using statement-based
+                replication:
               </para>
-            </listitem>
 
-            <listitem>
+              <itemizedlist>
+
+                <listitem>
+                  <para>
+                    <literal role="func">LOAD_FILE()</literal>
+                  </para>
+                </listitem>
+
+                <listitem>
+                  <para>
+                    <literal role="func">UUID()</literal>,
+                    <literal role="func">UUID_SHORT()</literal>
+                  </para>
+                </listitem>
+
+                <listitem>
+                  <para>
+                    <literal role="func">USER()</literal>
+                  </para>
+                </listitem>
+
+                <listitem>
+                  <para>
+                    <literal role="func">FOUND_ROWS()</literal>
+                  </para>
+                </listitem>
+
+                <listitem>
+                  <para>
+                    <literal role="func">SYSDATE()</literal> (unless the
+                    server is started with the
+                    <option>--sysdate-is-now</option> option)
+                  </para>
+                </listitem>
+
+              </itemizedlist>
+
               <para>
-                <literal role="func">FOUND_ROWS()</literal>
+                However, all other functions are replicated correctly
+                using statement-based replication, including
+                <literal role="func">RAND()</literal>,
+                <literal role="func">NOW()</literal>,
+                <literal role="stmt" condition="load-data">LOAD DATA
+                INFILE</literal>, and so forth.
               </para>
-            </listitem>
 
-            <listitem>
               <para>
-                <literal role="func">SYSDATE()</literal> (unless the
-                server is started with the
-                <option>--sysdate-is-now</option> option)
+                For more information, see
+                <xref linkend="replication-features-functions"/>.
               </para>
             </listitem>
 
           </itemizedlist>
 
           <para>
-            All other functions are replicated correctly (including
-            <literal role="func">RAND()</literal>,
-            <literal role="func">NOW()</literal>,
-            <literal role="stmt" condition="load-data">LOAD DATA
-            INFILE</literal>, and so forth).
+            When using statement-based replication, statements that
+            cannot be replicated correctly using statement-based mode
+            are logged with a warning like the one shown here:
           </para>
+
+<programlisting>
+090213 16:58:54 [Warning] Statement is not safe to log in statement format.
+</programlisting>
+
+          <para>
+            A similar warning is also issued to the client in such
+            cases.
+          </para>
         </listitem>
 
         <listitem>
           <para>
-            <literal>INSERT ... SELECT</literal> requires a greater
-            number of row-level locks than with row-based replication.
+            <literal role="stmt" condition="insert">INSERT ...
+            SELECT</literal> requires a greater number of row-level
+            locks than with row-based replication.
           </para>
         </listitem>
 

@@ -1647,7 +1727,7 @@
 
         <listitem>
           <para>
-            For <literal>InnoDB</literal>: An
+            For <literal role="se">InnoDB</literal>: An
             <literal role="stmt">INSERT</literal> statement that uses
             <literal>AUTO_INCREMENT</literal> blocks other
             non-conflicting <literal role="stmt">INSERT</literal>

@@ -1684,23 +1764,31 @@
             If there is an error in evaluation on the slave,
             particularly when executing complex statements, then using
             statement-based replication may slowly increase the margin
-            of error across the affected rows over time.
+            of error across the affected rows over time. See
+            <xref linkend="replication-features-slaveerrors"/>.
           </para>
         </listitem>
 
         <listitem>
           <para>
-            Tables have to be (almost) identical on master and slave.
+            Tables must be (nearly) identical on master and slave. See
+            <xref linkend="replication-features-differing-tables"/>, for
+            more information.
           </para>
         </listitem>
 
       </itemizedlist>
 
       <para>
-        <emphasis role="bold"> Advantages of row-based
+        <emphasis role="bold">Advantages of row-based
         replication:</emphasis>
       </para>
 
+      <indexterm>
+        <primary>row-based replication</primary>
+        <secondary>advantages</secondary>
+      </indexterm>
+
       <itemizedlist>
 
         <listitem>

@@ -1788,6 +1876,11 @@
         replication:</emphasis>
       </para>
 
+      <indexterm>
+        <primary>row-based replication</primary>
+        <secondary>disadvantages</secondary>
+      </indexterm>
+
       <itemizedlist>
 
         <listitem>


Thread
svn commit - mysqldoc@docsrva: r13886 - in trunk: refman-5.1 refman-6.0jon.stephens22 Feb