Author: jstephens
Date: 2009-09-14 11:30:12 +0200 (Mon, 14 Sep 2009)
New Revision: 16594
Log:
Fixes Docs Bug #47266 (Thanks, Sven!)
Rebuilt deps
Modified:
trunk/refman-5.1/dba-log-files.xml
trunk/refman-5.1/replication-notes.xml
trunk/refman-5.4/Makefile.depends
trunk/refman-5.4/dba-log-files.xml
trunk/refman-5.4/replication-notes.xml
Modified: trunk/refman-5.1/dba-log-files.xml
===================================================================
--- trunk/refman-5.1/dba-log-files.xml 2009-09-14 01:21:18 UTC (rev 16593)
+++ trunk/refman-5.1/dba-log-files.xml 2009-09-14 09:30:12 UTC (rev 16594)
Changed blocks: 8, Lines Added: 235, Lines Deleted: 79; 16424 bytes
@@ -475,7 +475,7 @@
<listitem>
<para>
It is not recommended to partition log tables, and doing so is
- not allowed beginning with MySQL 5.1.20.
+ not permitted beginning with MySQL 5.1.20.
</para>
</listitem>
@@ -1208,6 +1208,14 @@
<para>
<literal role="sysvar">sql_mode</literal>
</para>
+
+ <note>
+ <para>
+ The <literal role="sqlmode">NO_DIR_IN_CREATE</literal> mode
+ is not replicated; see
+ <xref linkend="replication-features-variables"/>.
+ </para>
+ </note>
</listitem>
<listitem>
@@ -1695,6 +1703,140 @@
Beginning with MySQL 5.1.24, when a statement refers to one
or more system variables. (Bug#31168)
</para>
+
+ <formalpara>
+
+ <title>Exception</title>
+
+ <para>
+ The following system variables, when used with session
+ scope (only), do not cause the logging format to switch:
+ </para>
+
+ </formalpara>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">auto_increment_increment</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">auto_increment_offset</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">character_set_client</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">character_set_connection</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">character_set_database</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">character_set_server</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">collation_connection</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">collation_database</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">collation_server</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">foreign_key_checks</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">identity</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">last_insert_id</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">lc_time_names</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">pseudo_thread_id</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">sql_auto_is_null</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">time_zone</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">timestamp</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">unique_checks</literal>
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ For information about determining system variable scope, see
+ <xref linkend="using-system-variables"/>.
+ </para>
+
+ <para>
+ For information about how replication treats
+ <literal role="sysvar">sql_mode</literal>, see
+ <xref linkend="replication-features-variables"/>.
+ </para>
</listitem>
<listitem>
@@ -1757,8 +1899,8 @@
</row>
<row>
<entry><literal>BLACKHOLE</literal></entry>
- <entry>No</entry>
<entry>Yes</entry>
+ <entry>Yes</entry>
</row>
<row>
<entry><literal>CSV</literal></entry>
@@ -1783,7 +1925,10 @@
<row>
<entry><literal>InnoDB</literal></entry>
<entry>Yes</entry>
- <entry>Yes</entry>
+ <entry>Yes, when the transaction isolation level is
+ <literal role="isolevel">REPEATABLE READ</literal> or
+ <literal role="isolevel">SERIALIZABLE</literal>;
+ otherwise, No.</entry>
</row>
<row>
<entry><literal>MyISAM</literal></entry>
@@ -1863,21 +2008,21 @@
is raised. In the table, a safe operation is defined as one that
is deterministic. A number of rules decide whether the statement
is deterministic or not, as shown in the following table (where
- <emphasis role="bold">RLC</emphasis> stands for
- <quote>row-logging capable</quote> and
- <emphasis role="bold">SLC</emphasis> stands for
- <quote>statement-logging capable</quote>).
+ <emphasis role="bold">RLR</emphasis> stands for
+ <quote>row-logging restricted</quote> and
+ <emphasis role="bold">SLR</emphasis> stands for
+ <quote>statement-logging restricted</quote>).
</para>
<informaltable>
<tgroup cols="6">
<colspec colwidth="10*" colname="safe"/>
<colspec colwidth="25*" colname="format"/>
- <colspec colwidth="10*" colname="RLC"/>
- <colspec colwidth="10*" colname="SLC"/>
+ <colspec colwidth="10*" colname="RLR"/>
+ <colspec colwidth="10*" colname="SLR"/>
<colspec colwidth="20*" colname="error"/>
<colspec colwidth="25*" colname="logas"/>
- <spanspec namest="safe" nameend="SLC" spanname="condition"/>
+ <spanspec namest="safe" nameend="SLR" spanname="condition"/>
<spanspec namest="error" nameend="logas" spanname="action"/>
<thead>
<row>
@@ -1887,8 +2032,8 @@
<row>
<entry>Safe/unsafe</entry>
<entry><literal role="sysvar">binlog_format</literal></entry>
- <entry>RLC</entry>
- <entry>SLC</entry>
+ <entry>RLR</entry>
+ <entry>SLR</entry>
<entry>Error/Warning</entry>
<entry>Logged as</entry>
</row>
@@ -1896,185 +2041,196 @@
<tbody>
<row>
<entry>Safe</entry>
- <entry>STATEMENT</entry>
- <entry>N</entry>
- <entry>N</entry>
- <entry>Error: not loggable</entry>
- </row>
- <row>
- <entry>Safe</entry>
- <entry>STATEMENT</entry>
- <entry>N</entry>
+ <entry><literal>STATEMENT</literal></entry>
<entry>Y</entry>
+ <entry>Y</entry>
+ <entry><errortext>Error: not loggable</errortext></entry>
<entry/>
- <entry>STATEMENT</entry>
</row>
<row>
<entry>Safe</entry>
- <entry>STATEMENT</entry>
+ <entry><literal>STATEMENT</literal></entry>
<entry>Y</entry>
<entry>N</entry>
- <entry>Error: not loggable</entry>
+ <entry/>
+ <entry><literal>STATEMENT</literal></entry>
</row>
<row>
<entry>Safe</entry>
- <entry>STATEMENT</entry>
+ <entry><literal>STATEMENT</literal></entry>
+ <entry>N</entry>
<entry>Y</entry>
- <entry>Y</entry>
+ <entry><errortext>Error: not loggable</errortext></entry>
<entry/>
- <entry>STATEMENT</entry>
</row>
<row>
<entry>Safe</entry>
- <entry>MIXED</entry>
+ <entry><literal>STATEMENT</literal></entry>
<entry>N</entry>
<entry>N</entry>
- <entry>Error: not loggable</entry>
+ <entry/>
+ <entry><literal>STATEMENT</literal></entry>
</row>
<row>
<entry>Safe</entry>
- <entry>MIXED</entry>
- <entry>N</entry>
+ <entry><literal>MIXED</literal></entry>
<entry>Y</entry>
+ <entry>Y</entry>
+ <entry><errortext>Error: not loggable</errortext></entry>
<entry/>
- <entry>STATEMENT</entry>
</row>
<row>
<entry>Safe</entry>
- <entry>MIXED</entry>
+ <entry><literal>MIXED</literal></entry>
<entry>Y</entry>
<entry>N</entry>
<entry/>
- <entry>ROW</entry>
+ <entry><literal>STATEMENT</literal></entry>
</row>
<row>
<entry>Safe</entry>
- <entry>MIXED</entry>
+ <entry><literal>MIXED</literal></entry>
+ <entry>N</entry>
<entry>Y</entry>
- <entry>Y</entry>
<entry/>
- <entry>STATEMENT</entry>
+ <entry><literal>ROW</literal></entry>
</row>
<row>
<entry>Safe</entry>
- <entry>ROW</entry>
+ <entry><literal>MIXED</literal></entry>
<entry>N</entry>
<entry>N</entry>
- <entry>Error: not loggable</entry>
+ <entry/>
+ <entry><literal>STATEMENT</literal></entry>
</row>
<row>
<entry>Safe</entry>
- <entry>ROW</entry>
- <entry>N</entry>
+ <entry><literal>ROW</literal></entry>
<entry>Y</entry>
- <entry>Error: not loggable</entry>
+ <entry>Y</entry>
+ <entry><errortext>Error: not loggable</errortext></entry>
+ <entry/>
</row>
<row>
<entry>Safe</entry>
- <entry>ROW</entry>
+ <entry>R<literal></literal>OW</entry>
<entry>Y</entry>
<entry>N</entry>
+ <entry><errortext>Error: not loggable</errortext></entry>
<entry/>
- <entry>ROW</entry>
</row>
<row>
<entry>Safe</entry>
- <entry>ROW</entry>
+ <entry><literal>ROW</literal></entry>
+ <entry>N</entry>
<entry>Y</entry>
- <entry>Y</entry>
<entry/>
- <entry>ROW</entry>
+ <entry><literal>ROW</literal></entry>
</row>
<row>
- <entry>Unsafe</entry>
- <entry>STATEMENT</entry>
+ <entry>Safe</entry>
+ <entry><literal>ROW</literal></entry>
<entry>N</entry>
<entry>N</entry>
- <entry>Error: not loggable</entry>
+ <entry/>
+ <entry><literal>ROW</literal></entry>
</row>
<row>
<entry>Unsafe</entry>
- <entry>STATEMENT</entry>
- <entry>N</entry>
+ <entry><literal>STATEMENT</literal></entry>
<entry>Y</entry>
- <entry>Warning: unsafe</entry>
- <entry>STATEMENT</entry>
+ <entry>Y</entry>
+ <entry><errortext>Error: not loggable</errortext></entry>
+ <entry/>
</row>
<row>
<entry>Unsafe</entry>
- <entry>STATEMENT</entry>
+ <entry><literal>STATEMENT</literal></entry>
<entry>Y</entry>
<entry>N</entry>
- <entry>Error: not loggable</entry>
+ <entry><errortext>Warning: unsafe</errortext></entry>
+ <entry><literal>STATEMENT</literal></entry>
</row>
<row>
<entry>Unsafe</entry>
- <entry>STATEMENT</entry>
+ <entry><literal>STATEMENT</literal></entry>
+ <entry>N</entry>
<entry>Y</entry>
- <entry>Y</entry>
- <entry>Warning: unsafe</entry>
- <entry>STATEMENT</entry>
+ <entry><errortext>Error: not loggable</errortext></entry>
+ <entry/>
</row>
<row>
<entry>Unsafe</entry>
- <entry>MIXED</entry>
+ <entry><literal>STATEMENT</literal></entry>
<entry>N</entry>
<entry>N</entry>
- <entry>Error: not loggable</entry>
+ <entry><errortext>Warning: unsafe</errortext></entry>
+ <entry><literal>STATEMENT</literal></entry>
</row>
<row>
<entry>Unsafe</entry>
<entry>MIXED</entry>
- <entry>N</entry>
<entry>Y</entry>
- <entry>Error: not loggable</entry>
+ <entry>Y</entry>
+ <entry><errortext>Error: not loggable</errortext></entry>
+ <entry/>
</row>
<row>
<entry>Unsafe</entry>
- <entry>MIXED</entry>
+ <entry><literal>MIXED</literal></entry>
<entry>Y</entry>
<entry>N</entry>
+ <entry><errortext>Error: not loggable</errortext></entry>
<entry/>
- <entry>ROW</entry>
</row>
<row>
<entry>Unsafe</entry>
- <entry>MIXED</entry>
+ <entry><literal>MIXED</literal></entry>
+ <entry>N</entry>
<entry>Y</entry>
- <entry>Y</entry>
<entry/>
- <entry>ROW</entry>
+ <entry><literal>ROW</literal></entry>
</row>
<row>
<entry>Unsafe</entry>
- <entry>ROW</entry>
+ <entry><literal>MIXED</literal></entry>
<entry>N</entry>
<entry>N</entry>
- <entry>Error: not loggable</entry>
+ <entry/>
+ <entry><literal>ROW</literal></entry>
</row>
<row>
<entry>Unsafe</entry>
- <entry>ROW</entry>
- <entry>N</entry>
+ <entry><literal>ROW</literal></entry>
<entry>Y</entry>
- <entry>Error: not loggable</entry>
+ <entry>Y</entry>
+ <entry><errortext>Error: not loggable</errortext></entry>
+ <entry/>
</row>
<row>
<entry>Unsafe</entry>
- <entry>ROW</entry>
+ <entry><literal>ROW</literal></entry>
<entry>Y</entry>
<entry>N</entry>
+ <entry><errortext>Error: not loggable</errortext></entry>
<entry/>
- <entry>ROW</entry>
</row>
<row>
<entry>Unsafe</entry>
- <entry>ROW</entry>
+ <entry><literal>ROW</literal></entry>
+ <entry>N</entry>
<entry>Y</entry>
- <entry>Y</entry>
<entry/>
- <entry>ROW</entry>
+ <entry><literal>ROW</literal></entry>
</row>
+ <row>
+ <entry>Unsafe</entry>
+ <entry><literal>ROW</literal></entry>
+ <entry>N</entry>
+ <entry>N</entry>
+ <entry/>
+ <entry><literal>ROW</literal></entry>
+ </row>
</tbody>
</tgroup>
</informaltable>
Modified: trunk/refman-5.1/replication-notes.xml
===================================================================
--- trunk/refman-5.1/replication-notes.xml 2009-09-14 01:21:18 UTC (rev 16593)
+++ trunk/refman-5.1/replication-notes.xml 2009-09-14 09:30:12 UTC (rev 16594)
Changed blocks: 1, Lines Added: 146, Lines Deleted: 16; 6269 bytes
@@ -2644,44 +2644,174 @@
</indexterm>
<para>
- The <literal role="sysvar">foreign_key_checks</literal>,
- <literal role="sysvar">unique_checks</literal>, and
- <literal role="sysvar">sql_auto_is_null</literal> variables are
- all replicated.
+ System variables are not replicated correctly when using
+ <literal>STATEMENT</literal> mode, except for the following
+ variables when they are used with session scope:
</para>
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">auto_increment_increment</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">auto_increment_offset</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">character_set_client</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">character_set_connection</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">character_set_database</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">character_set_server</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">collation_connection</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">collation_database</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">collation_server</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">foreign_key_checks</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">identity</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">last_insert_id</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">lc_time_names</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">pseudo_thread_id</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">sql_auto_is_null</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">time_zone</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">timestamp</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">unique_checks</literal>
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
<para>
+ When <literal>MIXED</literal> mode is used, the variables in the
+ preceding list, when used with session scope, cause a switch
+ from statement-based to row-based logging. See
+ <xref linkend="binary-log-mixed"/>.
+ </para>
+
+ <para>
<literal role="sysvar">sql_mode</literal> is also replicated
except for the
- <literal role="sqlmode">NO_DIR_IN_CREATE</literal> mode.
+ <literal role="sqlmode">NO_DIR_IN_CREATE</literal> mode; the
+ slave always preserves its own value for
+ <literal role="sqlmode">NO_DIR_IN_CREATE</literal>, regardless
+ of any changes to this mode on the master. This is true for all
+ replication modes.
+ </para>
+
+ <para>
However, when <command>mysqlbinlog</command> parses a
<literal>SET @@sql_mode =
<replaceable>value</replaceable></literal> statement, the full
<replaceable>value</replaceable>, including
<literal role="sqlmode">NO_DIR_IN_CREATE</literal>, is passed to
- the receiving server.
+ the receiving server. For this reason, replication of such a
+ statement may not be safe when <literal>STATEMENT</literal> mode
+ is in use.
</para>
<para>
The <literal role="sysvar">storage_engine</literal> system
- variable is not replicated, which is a good thing for
- replication between different storage engines.
+ variable is not replicated, regardless of the logging mode; this
+ is intended to facilitate replication between different storage
+ engines.
</para>
<para>
- Session variables are not replicated properly when used in
- statements that update tables. For example, <literal>SET
- MAX_JOIN_SIZE=1000</literal> followed by <literal>INSERT INTO
- mytable VALUES(@@MAX_JOIN_SIZE)</literal> will not insert the
- same data on the master and the slave. This does not apply to
- the common sequence of <literal>SET TIME_ZONE=...</literal>
- followed by <literal>INSERT INTO mytable
+ In statement-based replication, session variables are not
+ replicated properly when used in statements that update tables.
+ For example, <literal>SET MAX_JOIN_SIZE=1000</literal> followed
+ by <literal>INSERT INTO mytable
+ VALUES(@@MAX_JOIN_SIZE)</literal> does not insert the same data
+ on the master and the slave. However, this does not apply to the
+ common sequence of <literal>SET TIME_ZONE=...</literal> followed
+ by <literal>INSERT INTO mytable
VALUES(CONVERT_TZ(...,...,@@time_zone))</literal>.
</para>
<para>
Replication of session variables is not a problem when row-based
- replication is being used. See
+ replication is being used; with row-based replication, session
+ variables are always replicated safely. See
<xref linkend="replication-formats"/>.
</para>
Modified: trunk/refman-5.4/Makefile.depends
===================================================================
--- trunk/refman-5.4/Makefile.depends 2009-09-14 01:21:18 UTC (rev 16593)
+++ trunk/refman-5.4/Makefile.depends 2009-09-14 09:30:12 UTC (rev 16594)
Changed blocks: 1, Lines Added: 0, Lines Deleted: 1; 577 bytes
@@ -552,7 +552,6 @@
dba_log_files_SOURCES = dba-log-files.xml $(dba_log_files_INCLUDES)
dba_log_files_IDMAPS = \
../refman-5.1/metadata/errors-problems-core.idmap \
- ../refman-5.1/metadata/mysql-cluster-replication.idmap \
metadata/backup.idmap \
metadata/dba-log-files.idmap \
metadata/dba-mysqld-server-core.idmap \
Modified: trunk/refman-5.4/dba-log-files.xml
===================================================================
--- trunk/refman-5.4/dba-log-files.xml 2009-09-14 01:21:18 UTC (rev 16593)
+++ trunk/refman-5.4/dba-log-files.xml 2009-09-14 09:30:12 UTC (rev 16594)
Changed blocks: 9, Lines Added: 235, Lines Deleted: 93; 16917 bytes
@@ -455,7 +455,7 @@
<listitem>
<para>
- It is not allowed to partition log tables.
+ Partitioning of log tables is not permitted.
</para>
</listitem>
@@ -1080,6 +1080,14 @@
<para>
<literal role="sysvar">sql_mode</literal>
</para>
+
+ <note>
+ <para>
+ The <literal role="sqlmode">NO_DIR_IN_CREATE</literal> mode
+ is not replicated; see
+ <xref linkend="replication-features-variables"/>.
+ </para>
+ </note>
</listitem>
<listitem>
@@ -1218,20 +1226,6 @@
5.4.
</para>
- <formalpara>
-
- <title>Exception</title>
-
- <para>
- For all MySQL Cluster releases using version 6.1 or later of
- the <literal role="se">NDBCLUSTER</literal> storage engine
- (even those releases based on MySQL 5.1.29 and later), the
- default binary log format is <literal>MIXED</literal>. See
- <xref linkend="mysql-cluster-replication"/>.
- </para>
-
- </formalpara>
-
<para>
You can force the replication format by starting the MySQL
server with
@@ -1530,6 +1524,140 @@
When a statement refers to one or more system variables.
(Bug#31168)
</para>
+
+ <formalpara>
+
+ <title>Exception</title>
+
+ <para>
+ The following system variables, when used with session
+ scope (only), do not cause the logging format to switch:
+ </para>
+
+ </formalpara>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">auto_increment_increment</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">auto_increment_offset</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">character_set_client</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">character_set_connection</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">character_set_database</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">character_set_server</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">collation_connection</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">collation_database</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">collation_server</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">foreign_key_checks</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">identity</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">last_insert_id</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">lc_time_names</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">pseudo_thread_id</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">sql_auto_is_null</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">time_zone</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">timestamp</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">unique_checks</literal>
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ For information about determining system variable scope, see
+ <xref linkend="using-system-variables"/>.
+ </para>
+
+ <para>
+ For information about how replication treats
+ <literal role="sysvar">sql_mode</literal>, see
+ <xref linkend="replication-features-variables"/>.
+ </para>
</listitem>
<listitem>
@@ -1590,8 +1718,8 @@
</row>
<row>
<entry><literal>BLACKHOLE</literal></entry>
- <entry>No</entry>
<entry>Yes</entry>
+ <entry>Yes</entry>
</row>
<row>
<entry><literal>CSV</literal></entry>
@@ -1616,7 +1744,10 @@
<row>
<entry><literal>InnoDB</literal></entry>
<entry>Yes</entry>
- <entry>Yes</entry>
+ <entry>Yes, when the transaction isolation level is
+ <literal role="isolevel">REPEATABLE READ</literal> or
+ <literal role="isolevel">SERIALIZABLE</literal>;
+ otherwise, No.</entry>
</row>
<row>
<entry><literal>MyISAM</literal></entry>
@@ -1696,21 +1827,21 @@
is raised. In the table, a safe operation is defined as one that
is deterministic. A number of rules decide whether the statement
is deterministic or not, as shown in the following table (where
- <emphasis role="bold">RLC</emphasis> stands for
- <quote>row-logging capable</quote> and
- <emphasis role="bold">SLC</emphasis> stands for
- <quote>statement-logging capable</quote>).
+ <emphasis role="bold">RLR</emphasis> stands for
+ <quote>row-logging restricted</quote> and
+ <emphasis role="bold">SLR</emphasis> stands for
+ <quote>statement-logging restricted</quote>).
</para>
<informaltable>
<tgroup cols="6">
<colspec colwidth="10*" colname="safe"/>
<colspec colwidth="25*" colname="format"/>
- <colspec colwidth="10*" colname="RLC"/>
- <colspec colwidth="10*" colname="SLC"/>
+ <colspec colwidth="10*" colname="RLR"/>
+ <colspec colwidth="10*" colname="SLR"/>
<colspec colwidth="20*" colname="error"/>
<colspec colwidth="25*" colname="logas"/>
- <spanspec namest="safe" nameend="SLC" spanname="condition"/>
+ <spanspec namest="safe" nameend="SLR" spanname="condition"/>
<spanspec namest="error" nameend="logas" spanname="action"/>
<thead>
<row>
@@ -1720,8 +1851,8 @@
<row>
<entry>Safe/unsafe</entry>
<entry><literal role="sysvar">binlog_format</literal></entry>
- <entry>RLC</entry>
- <entry>SLC</entry>
+ <entry>RLR</entry>
+ <entry>SLR</entry>
<entry>Error/Warning</entry>
<entry>Logged as</entry>
</row>
@@ -1729,185 +1860,196 @@
<tbody>
<row>
<entry>Safe</entry>
- <entry>STATEMENT</entry>
- <entry>N</entry>
- <entry>N</entry>
- <entry>Error: not loggable</entry>
- </row>
- <row>
- <entry>Safe</entry>
- <entry>STATEMENT</entry>
- <entry>N</entry>
+ <entry><literal>STATEMENT</literal></entry>
<entry>Y</entry>
+ <entry>Y</entry>
+ <entry><errortext>Error: not loggable</errortext></entry>
<entry/>
- <entry>STATEMENT</entry>
</row>
<row>
<entry>Safe</entry>
- <entry>STATEMENT</entry>
+ <entry><literal>STATEMENT</literal></entry>
<entry>Y</entry>
<entry>N</entry>
- <entry>Error: not loggable</entry>
+ <entry/>
+ <entry><literal>STATEMENT</literal></entry>
</row>
<row>
<entry>Safe</entry>
- <entry>STATEMENT</entry>
+ <entry><literal>STATEMENT</literal></entry>
+ <entry>N</entry>
<entry>Y</entry>
- <entry>Y</entry>
+ <entry><errortext>Error: not loggable</errortext></entry>
<entry/>
- <entry>STATEMENT</entry>
</row>
<row>
<entry>Safe</entry>
- <entry>MIXED</entry>
+ <entry><literal>STATEMENT</literal></entry>
<entry>N</entry>
<entry>N</entry>
- <entry>Error: not loggable</entry>
+ <entry/>
+ <entry><literal>STATEMENT</literal></entry>
</row>
<row>
<entry>Safe</entry>
- <entry>MIXED</entry>
- <entry>N</entry>
+ <entry><literal>MIXED</literal></entry>
<entry>Y</entry>
+ <entry>Y</entry>
+ <entry><errortext>Error: not loggable</errortext></entry>
<entry/>
- <entry>STATEMENT</entry>
</row>
<row>
<entry>Safe</entry>
- <entry>MIXED</entry>
+ <entry><literal>MIXED</literal></entry>
<entry>Y</entry>
<entry>N</entry>
<entry/>
- <entry>ROW</entry>
+ <entry><literal>STATEMENT</literal></entry>
</row>
<row>
<entry>Safe</entry>
- <entry>MIXED</entry>
+ <entry><literal>MIXED</literal></entry>
+ <entry>N</entry>
<entry>Y</entry>
- <entry>Y</entry>
<entry/>
- <entry>STATEMENT</entry>
+ <entry><literal>ROW</literal></entry>
</row>
<row>
<entry>Safe</entry>
- <entry>ROW</entry>
+ <entry><literal>MIXED</literal></entry>
<entry>N</entry>
<entry>N</entry>
- <entry>Error: not loggable</entry>
+ <entry/>
+ <entry><literal>STATEMENT</literal></entry>
</row>
<row>
<entry>Safe</entry>
- <entry>ROW</entry>
- <entry>N</entry>
+ <entry><literal>ROW</literal></entry>
<entry>Y</entry>
- <entry>Error: not loggable</entry>
+ <entry>Y</entry>
+ <entry><errortext>Error: not loggable</errortext></entry>
+ <entry/>
</row>
<row>
<entry>Safe</entry>
- <entry>ROW</entry>
+ <entry>R<literal></literal>OW</entry>
<entry>Y</entry>
<entry>N</entry>
+ <entry><errortext>Error: not loggable</errortext></entry>
<entry/>
- <entry>ROW</entry>
</row>
<row>
<entry>Safe</entry>
- <entry>ROW</entry>
+ <entry><literal>ROW</literal></entry>
+ <entry>N</entry>
<entry>Y</entry>
- <entry>Y</entry>
<entry/>
- <entry>ROW</entry>
+ <entry><literal>ROW</literal></entry>
</row>
<row>
- <entry>Unsafe</entry>
- <entry>STATEMENT</entry>
+ <entry>Safe</entry>
+ <entry><literal>ROW</literal></entry>
<entry>N</entry>
<entry>N</entry>
- <entry>Error: not loggable</entry>
+ <entry/>
+ <entry><literal>ROW</literal></entry>
</row>
<row>
<entry>Unsafe</entry>
- <entry>STATEMENT</entry>
- <entry>N</entry>
+ <entry><literal>STATEMENT</literal></entry>
<entry>Y</entry>
- <entry>Warning: unsafe</entry>
- <entry>STATEMENT</entry>
+ <entry>Y</entry>
+ <entry><errortext>Error: not loggable</errortext></entry>
+ <entry/>
</row>
<row>
<entry>Unsafe</entry>
- <entry>STATEMENT</entry>
+ <entry><literal>STATEMENT</literal></entry>
<entry>Y</entry>
<entry>N</entry>
- <entry>Error: not loggable</entry>
+ <entry><errortext>Warning: unsafe</errortext></entry>
+ <entry><literal>STATEMENT</literal></entry>
</row>
<row>
<entry>Unsafe</entry>
- <entry>STATEMENT</entry>
+ <entry><literal>STATEMENT</literal></entry>
+ <entry>N</entry>
<entry>Y</entry>
- <entry>Y</entry>
- <entry>Warning: unsafe</entry>
- <entry>STATEMENT</entry>
+ <entry><errortext>Error: not loggable</errortext></entry>
+ <entry/>
</row>
<row>
<entry>Unsafe</entry>
- <entry>MIXED</entry>
+ <entry><literal>STATEMENT</literal></entry>
<entry>N</entry>
<entry>N</entry>
- <entry>Error: not loggable</entry>
+ <entry><errortext>Warning: unsafe</errortext></entry>
+ <entry><literal>STATEMENT</literal></entry>
</row>
<row>
<entry>Unsafe</entry>
<entry>MIXED</entry>
- <entry>N</entry>
<entry>Y</entry>
- <entry>Error: not loggable</entry>
+ <entry>Y</entry>
+ <entry><errortext>Error: not loggable</errortext></entry>
+ <entry/>
</row>
<row>
<entry>Unsafe</entry>
- <entry>MIXED</entry>
+ <entry><literal>MIXED</literal></entry>
<entry>Y</entry>
<entry>N</entry>
+ <entry><errortext>Error: not loggable</errortext></entry>
<entry/>
- <entry>ROW</entry>
</row>
<row>
<entry>Unsafe</entry>
- <entry>MIXED</entry>
+ <entry><literal>MIXED</literal></entry>
+ <entry>N</entry>
<entry>Y</entry>
- <entry>Y</entry>
<entry/>
- <entry>ROW</entry>
+ <entry><literal>ROW</literal></entry>
</row>
<row>
<entry>Unsafe</entry>
- <entry>ROW</entry>
+ <entry><literal>MIXED</literal></entry>
<entry>N</entry>
<entry>N</entry>
- <entry>Error: not loggable</entry>
+ <entry/>
+ <entry><literal>ROW</literal></entry>
</row>
<row>
<entry>Unsafe</entry>
- <entry>ROW</entry>
- <entry>N</entry>
+ <entry><literal>ROW</literal></entry>
<entry>Y</entry>
- <entry>Error: not loggable</entry>
+ <entry>Y</entry>
+ <entry><errortext>Error: not loggable</errortext></entry>
+ <entry/>
</row>
<row>
<entry>Unsafe</entry>
- <entry>ROW</entry>
+ <entry><literal>ROW</literal></entry>
<entry>Y</entry>
<entry>N</entry>
+ <entry><errortext>Error: not loggable</errortext></entry>
<entry/>
- <entry>ROW</entry>
</row>
<row>
<entry>Unsafe</entry>
- <entry>ROW</entry>
+ <entry><literal>ROW</literal></entry>
+ <entry>N</entry>
<entry>Y</entry>
- <entry>Y</entry>
<entry/>
- <entry>ROW</entry>
+ <entry><literal>ROW</literal></entry>
</row>
+ <row>
+ <entry>Unsafe</entry>
+ <entry><literal>ROW</literal></entry>
+ <entry>N</entry>
+ <entry>N</entry>
+ <entry/>
+ <entry><literal>ROW</literal></entry>
+ </row>
</tbody>
</tgroup>
</informaltable>
Modified: trunk/refman-5.4/replication-notes.xml
===================================================================
--- trunk/refman-5.4/replication-notes.xml 2009-09-14 01:21:18 UTC (rev 16593)
+++ trunk/refman-5.4/replication-notes.xml 2009-09-14 09:30:12 UTC (rev 16594)
Changed blocks: 1, Lines Added: 146, Lines Deleted: 16; 6269 bytes
@@ -2473,44 +2473,174 @@
</indexterm>
<para>
- The <literal role="sysvar">foreign_key_checks</literal>,
- <literal role="sysvar">unique_checks</literal>, and
- <literal role="sysvar">sql_auto_is_null</literal> variables are
- all replicated.
+ System variables are not replicated correctly when using
+ <literal>STATEMENT</literal> mode, except for the following
+ variables when they are used with session scope:
</para>
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">auto_increment_increment</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">auto_increment_offset</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">character_set_client</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">character_set_connection</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">character_set_database</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">character_set_server</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">collation_connection</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">collation_database</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">collation_server</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">foreign_key_checks</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">identity</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">last_insert_id</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">lc_time_names</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">pseudo_thread_id</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">sql_auto_is_null</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">time_zone</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">timestamp</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="sysvar">unique_checks</literal>
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
<para>
+ When <literal>MIXED</literal> mode is used, the variables in the
+ preceding list, when used with session scope, cause a switch
+ from statement-based to row-based logging. See
+ <xref linkend="binary-log-mixed"/>.
+ </para>
+
+ <para>
<literal role="sysvar">sql_mode</literal> is also replicated
except for the
- <literal role="sqlmode">NO_DIR_IN_CREATE</literal> mode.
+ <literal role="sqlmode">NO_DIR_IN_CREATE</literal> mode; the
+ slave always preserves its own value for
+ <literal role="sqlmode">NO_DIR_IN_CREATE</literal>, regardless
+ of any changes to this mode on the master. This is true for all
+ replication modes.
+ </para>
+
+ <para>
However, when <command>mysqlbinlog</command> parses a
<literal>SET @@sql_mode =
<replaceable>value</replaceable></literal> statement, the full
<replaceable>value</replaceable>, including
<literal role="sqlmode">NO_DIR_IN_CREATE</literal>, is passed to
- the receiving server.
+ the receiving server. For this reason, replication of such a
+ statement may not be safe when <literal>STATEMENT</literal> mode
+ is in use.
</para>
<para>
The <literal role="sysvar">storage_engine</literal> system
- variable is not replicated, which is a good thing for
- replication between different storage engines.
+ variable is not replicated, regardless of the logging mode; this
+ is intended to facilitate replication between different storage
+ engines.
</para>
<para>
- Session variables are not replicated properly when used in
- statements that update tables. For example, <literal>SET
- MAX_JOIN_SIZE=1000</literal> followed by <literal>INSERT INTO
- mytable VALUES(@@MAX_JOIN_SIZE)</literal> will not insert the
- same data on the master and the slave. This does not apply to
- the common sequence of <literal>SET TIME_ZONE=...</literal>
- followed by <literal>INSERT INTO mytable
+ In statement-based replication, session variables are not
+ replicated properly when used in statements that update tables.
+ For example, <literal>SET MAX_JOIN_SIZE=1000</literal> followed
+ by <literal>INSERT INTO mytable
+ VALUES(@@MAX_JOIN_SIZE)</literal> does not insert the same data
+ on the master and the slave. However, this does not apply to the
+ common sequence of <literal>SET TIME_ZONE=...</literal> followed
+ by <literal>INSERT INTO mytable
VALUES(CONVERT_TZ(...,...,@@time_zone))</literal>.
</para>
<para>
Replication of session variables is not a problem when row-based
- replication is being used. See
+ replication is being used; with row-based replication, session
+ variables are always replicated safely. See
<xref linkend="replication-formats"/>.
</para>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r16594 - in trunk: refman-5.1 refman-5.4 | jon.stephens | 14 Sep |
