Author: jstephens
Date: 2007-01-04 08:04:33 +0100 (Thu, 04 Jan 2007)
New Revision: 4374
Log:
Moved 5.0/5.1 SPs, Triggers, and Replication FAQs into FAQs appendices.
Synched telco branch to 5.1 changes.
Updated renamed-nodes files.
Reformat.
Modified:
branches/telcos/refman-5.1/faqs.xml
branches/telcos/refman-5.1/renamed-nodes.txt
branches/telcos/refman-5.1/replication.xml
branches/telcos/refman-5.1/stored-procedures.xml
trunk/refman-5.0/faqs.xml
trunk/refman-5.0/renamed-nodes.txt
trunk/refman-5.0/stored-procedures.xml
trunk/refman-5.1/faqs.xml
trunk/refman-5.1/renamed-nodes.txt
trunk/refman-5.1/replication.xml
trunk/refman-5.1/stored-procedures.xml
Modified: branches/telcos/refman-5.1/faqs.xml
===================================================================
--- branches/telcos/refman-5.1/faqs.xml 2007-01-04 05:08:58 UTC (rev 4373)
+++ branches/telcos/refman-5.1/faqs.xml 2007-01-04 07:04:33 UTC (rev 4374)
Changed blocks: 3, Lines Added: 361, Lines Deleted: 3; 11191 bytes
@@ -1406,6 +1406,364 @@
</section>
+ <section id="faqs-stored-routines-triggers-replication">
+
+ <title>MySQL ¤t-series; FAQ — Stored Routines, Triggers, and
+ Replication</title>
+
+ <remark role="todo">
+ [js] This section needs to be rewritten for 5.1 to reflect
+ differences bewteen statement-based and row-based replication.
+ </remark>
+
+ <qandaset defaultlabel="qanda">
+
+ <qandaentry>
+
+ <question>
+
+ <para>
+ Do MySQL ¤t-series; stored procedures and functions
+ work with replication?
+ </para>
+
+ </question>
+
+ <answer>
+
+ <para>
+ Yes, standard actions carried out in stored procedures and
+ functions are replicated from a master MySQL server to a
+ slave server. There are a few limitations that are described
+ in detail in <xref linkend="stored-procedure-logging"/>.
+ </para>
+
+ </answer>
+
+ </qandaentry>
+
+ <qandaentry>
+
+ <question>
+
+ <para>
+ Are stored procedures and functions created on a master
+ server replicated to a slave?
+ </para>
+
+ </question>
+
+ <answer>
+
+ <para>
+ Yes, creation of stored procedures and functions carried out
+ through normal DDL statements on a master server are
+ replicated to a slave, so the objects will exist on both
+ servers. <literal>ALTER</literal> and
+ <literal>DROP</literal> statements for stored procedures and
+ functions are also replicated.
+ </para>
+
+ </answer>
+
+ </qandaentry>
+
+ <qandaentry>
+
+ <question>
+
+ <para>
+ How are actions that take place inside stored procedures and
+ functions replicated?
+ </para>
+
+ </question>
+
+ <answer>
+
+ <para>
+ MySQL records each DML event that occurs in a stored
+ procedure and replicates those individual actions to a slave
+ server. The actual calls made to execute stored procedures
+ are not replicated.
+ </para>
+
+ <para>
+ Stored functions that change data are logged as function
+ invocations, not as the DML events that occur inside each
+ function.
+ </para>
+
+ </answer>
+
+ </qandaentry>
+
+ <qandaentry>
+
+ <question>
+
+ <para>
+ Are there special security requirements for using stored
+ procedures and functions together with replication?
+ </para>
+
+ </question>
+
+ <answer>
+
+ <para>
+ Yes. Because a slave server has authority to execute any
+ statement read from a master's binary log, special security
+ constraints exist for using stored functions with
+ replication. If replication or binary logging in general
+ (for the purpose of point-in-time recovery) is active, then
+ MySQL DBAs have two security options open to them:
+
+ <orderedlist>
+
+ <listitem>
+ <para>
+ Any user wishing to create stored functions must be
+ granted the <literal>SUPER</literal> privilege.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Alternatively, a DBA can set the
+ <literal>log_bin_trust_function_creators</literal>
+ system variable to 1, which enables anyone with the
+ standard <literal>CREATE ROUTINE</literal> privilege
+ to create stored functions.
+ </para>
+ </listitem>
+
+ </orderedlist>
+ </para>
+
+ </answer>
+
+ </qandaentry>
+
+ <qandaentry>
+
+ <question>
+
+ <para>
+ What limitations exist for replicating stored procedure and
+ function actions?
+ </para>
+
+ </question>
+
+ <answer>
+
+ <para>
+ Non-deterministic (random) or time-based actions embedded in
+ stored procedures may not replicate properly. By their very
+ nature, randomly produced results are not predictable and
+ cannot be exactly reproduced, and therefore, random actions
+ replicated to a slave will not mirror those performed on a
+ master. Note that declaring stored functions to be
+ <literal>DETERMINISTIC</literal> or setting the
+ <literal>log_bin_trust_function_creators</literal> system
+ variable to 0 will not allow random-valued operations to be
+ invoked.
+ </para>
+
+ <para>
+ In addition, time-based actions cannot be reproduced on a
+ slave because the timing of such actions in a stored
+ procedure is not reproducible through the binary log used
+ for replication. It records only DML events and does not
+ factor in timing constraints.
+ </para>
+
+ <para>
+ Finally, non-transactional tables for which errors occur
+ during large DML actions (such as bulk inserts) may
+ experience replication issues in that a master may be
+ partially updated from DML activity, but no updates are done
+ to the slave because of the errors that occurred. A
+ workaround is for a function's DML actions to be carried out
+ with the <literal>IGNORE</literal> keyword so that updates
+ on the master that cause errors are ignored and updates that
+ do not cause errors are replicated to the slave.
+ </para>
+
+ </answer>
+
+ </qandaentry>
+
+ <qandaentry>
+
+ <question>
+
+ <para>
+ Do the preceding limitations affect MySQL's ability to do
+ point-in-time recovery?
+ </para>
+
+ </question>
+
+ <answer>
+
+ <para>
+ The same limitations that affect replication do affect
+ point-in-time recovery.
+ </para>
+
+ </answer>
+
+ </qandaentry>
+
+ <qandaentry>
+
+ <question>
+
+ <para>
+ What will MySQL do to correct the aforementioned
+ limitations?
+ </para>
+
+ </question>
+
+ <answer>
+
+ <para>
+ As of MySQL 5.1.5, you can choose either statement-based
+ replication or row-based replication. The original
+ replication implementation is based on statement-based
+ binary logging. Row-based binary logging resolves the
+ limitations mentioned earlier.
+ </para>
+
+ <para>
+ Beginning with MySQL 5.1.8, <firstterm>mixed</firstterm>
+ replication is also available (by starting the server with
+ <option>--binlog-format=mixed</option>). This hybrid,
+ <quote>smart</quote> form of replication
+ <quote>knows</quote> whether statement-level replication can
+ safely be used, or row-level replication is required.
+ </para>
+
+ <para>
+ For additional information, see
+ <xref linkend="replication-row-based"/>.
+ </para>
+
+ </answer>
+
+ </qandaentry>
+
+ <qandaentry>
+
+ <question>
+
+ <para>
+ Do triggers work with replication?
+ </para>
+
+ </question>
+
+ <answer>
+
+ <para>
+ Triggers and replication in MySQL ¤t-series; work in
+ the same wasy as in most other database engines: Actions
+ carried out through triggers on a master are not replicated
+ to a slave server. Instead, triggers that exist on tables
+ that reside on a MySQL master server need to be created on
+ the corresponding tables on any MySQL slave servers so that
+ the triggers activate on the slaves as well as the master.
+ </para>
+
+ </answer>
+
+ </qandaentry>
+
+ <qandaentry>
+
+ <question>
+
+ <para>
+ How are actions carried out through triggers on a master
+ replicated to a slave?
+ </para>
+
+ </question>
+
+ <answer>
+
+ <para>
+ First, the triggers that exist on a master must be
+ re-created on the slave server. Once this is done, the
+ replication flow works as any other standard DML statement
+ that participates in replication. For example, consider a
+ table <literal>EMP</literal> that has an
+ <literal>AFTER</literal> insert trigger, which exists on a
+ master MySQL server. The same <literal>EMP</literal> table
+ and <literal>AFTER</literal> insert trigger exist on the
+ slave server as well. The replication flow would be:
+ </para>
+
+ <orderedlist>
+
+ <listitem>
+ <para>
+ An <literal>INSERT</literal> statement is made to
+ <literal>EMP</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The <literal>AFTER</literal> trigger on
+ <literal>EMP</literal> activates.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The <literal>INSERT</literal> statement is written to
+ the binary log.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The replication slave picks up the
+ <literal>INSERT</literal> statement to
+ <literal>EMP</literal> and executes it.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The <literal>AFTER</literal> trigger on
+ <literal>EMP</literal> that exists on the slave
+ activates.
+ </para>
+ </listitem>
+
+ </orderedlist>
+
+ </answer>
+
+ </qandaentry>
+
+ </qandaset>
+
+ <para>
+ For answers to some general questions about MySQL stored
+ procedures and stored functions, see
+ <xref linkend="faqs-stored-procs"/>. Some common questions
+ concerning MySQL triggers are adressed in
+ <xref linkend="faqs-triggers"/>.
+ </para>
+
+ </section>
+
<section id="faqs-views">
<title>MySQL ¤t-series; FAQ — Views</title>
@@ -1417,7 +1775,7 @@
<question>
<para>
- Where can I find documentation for MySQL Views?
+ Where can I find documentation covering MySQL Views?
</para>
</question>
@@ -4365,8 +4723,8 @@
<secondary>frequently asked questions</secondary>
</indexterm>
</section>
--->
-
+-->
+
<section id="faqs-cjk">
<title>MySQL ¤t-series; FAQ — MySQL Chinese, Japanese, and Korean
Modified: branches/telcos/refman-5.1/renamed-nodes.txt
===================================================================
--- branches/telcos/refman-5.1/renamed-nodes.txt 2007-01-04 05:08:58 UTC (rev 4373)
+++ branches/telcos/refman-5.1/renamed-nodes.txt 2007-01-04 07:04:33 UTC (rev 4374)
Changed blocks: 1, Lines Added: 2, Lines Deleted: 0; 447 bytes
@@ -349,3 +349,5 @@
mysql-cluster-faq faqs-mysql-cluster
faq-security faqs-security
+
+stored-procedure-replication-faq faqs-stored-routines-triggers-replication
Modified: branches/telcos/refman-5.1/replication.xml
===================================================================
--- branches/telcos/refman-5.1/replication.xml 2007-01-04 05:08:58 UTC (rev 4373)
+++ branches/telcos/refman-5.1/replication.xml 2007-01-04 07:04:33 UTC (rev 4374)
Changed blocks: 2, Lines Added: 8, Lines Deleted: 8; 1725 bytes
@@ -221,14 +221,14 @@
<para>
Replication capabilities in MySQL originally were based on
propagation of SQL statements from master to slave. This is called
- <emphasis>statement-based replication</emphasis>. As of MySQL
+ <firstterm>statement-based replication</firstterm>. As of MySQL
5.1.5, another basis for replication is available called
- <emphasis>row-based replication</emphasis>. Instead of sending SQL
- statements to the slave, the master writes events to its binary
- log that indicate how individual table rows are affected. As of
- MySQL 5.1.8, a third option is available: mixed. This will use
- statement-based replication by default, and only switch to
- row-based replication in particular cases as described below.
+ <firstterm>row-based replication</firstterm>. Instead of sending
+ SQL statements to the slave, the master writes events to its
+ binary log that indicate how individual table rows are affected.
+ As of MySQL 5.1.8, a third option is available: mixed. This uses
+ statement-based replication by default, and switches to row-based
+ replication only in particular cases as described below.
</para>
<para>
@@ -266,7 +266,7 @@
</para>
<para>
- To use the mixed format, start the server with the
+ To use mixed format, start the server with the
<option>--binlog-format=mixed</option> option.
</para>
Modified: branches/telcos/refman-5.1/stored-procedures.xml
===================================================================
--- branches/telcos/refman-5.1/stored-procedures.xml 2007-01-04 05:08:58 UTC (rev 4373)
+++ branches/telcos/refman-5.1/stored-procedures.xml 2007-01-04 07:04:33 UTC (rev 4374)
Changed blocks: 2, Lines Added: 4, Lines Deleted: 256; 9643 bytes
@@ -35,7 +35,10 @@
<para>
Answers to some questions that are commonly asked regarding stored
routines in MySQL can be found in
- <xref linkend="faqs-stored-procs"/>.
+ <xref linkend="faqs-stored-procs"/>. In addition, see
+ <xref linkend="faqs-stored-routines-triggers-replication"/>, for
+ answers to some common questions concerning stored routines and
+ replication.
</para>
<para>
@@ -2147,261 +2150,6 @@
</section>
- <section id="stored-procedure-replication-faq">
-
- <title>Stored Procedures, Functions, Triggers, and Replication: Frequently
- Asked Questions</title>
-
- <remark role="todo">
- [js] This section needs to be rewritten - or possibly even cut
- altogether - once row-based replication is implemented.
- </remark>
-
- <itemizedlist>
-
- <listitem>
- <para>
- Do MySQL ¤t-series; stored procedures and functions work
- with replication?
- </para>
-
- <para>
- Yes, standard actions carried out in stored procedures and
- functions are replicated from a master MySQL server to a slave
- server. There are a few limitations that are described in
- detail in <xref linkend="stored-procedure-logging"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Are stored procedures and functions created on a master server
- replicated to a slave?
- </para>
-
- <para>
- Yes, creation of stored procedures and functions carried out
- through normal DDL statements on a master server are
- replicated to a slave, so the objects will exist on both
- servers. <literal>ALTER</literal> and <literal>DROP</literal>
- statements for stored procedures and functions are also
- replicated.
- </para>
- </listitem>
-
- <listitem>
- <para>
- How are actions that take place inside stored procedures and
- functions replicated?
- </para>
-
- <para>
- MySQL records each DML event that occurs in a stored procedure
- and replicates those individual actions to a slave server. The
- actual calls made to execute stored procedures are not
- replicated.
- </para>
-
- <para>
- Stored functions that change data are logged as function
- invocations, not as the DML events that occur inside each
- function.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Are there special security requirements for using stored
- procedures and functions together with replication?
- </para>
-
- <para>
- Yes. Because a slave server has authority to execute any
- statement read from a master's binary log, special security
- constraints exist for using stored functions with replication.
- If replication or binary logging in general (for the purpose
- of point-in-time recovery) is active, then MySQL DBAs have two
- security options open to them:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- Any user wishing to create stored functions must be
- granted the <literal>SUPER</literal> privilege.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Alternatively, a DBA can set the
- <literal>log_bin_trust_function_creators</literal> system
- variable to 1, which enables anyone with the standard
- <literal>CREATE ROUTINE</literal> privilege to create
- stored functions.
- </para>
- </listitem>
-
- </itemizedlist>
- </listitem>
-
- <listitem>
- <para>
- What limitations exist for replicating stored procedure and
- function actions?
- </para>
-
- <para>
- Non-deterministic (random) or time-based actions embedded in
- stored procedures may not replicate properly. By their very
- nature, randomly produced results are not predictable and
- cannot be exactly reproduced, and therefore, random actions
- replicated to a slave will not mirror those performed on a
- master. Note that declaring stored functions to be
- <literal>DETERMINISTIC</literal> or setting the
- <literal>log_bin_trust_function_creators</literal> system
- variable to 0 will not allow random-valued operations to be
- invoked.
- </para>
-
- <para>
- In addition, time-based actions cannot be reproduced on a
- slave because the timing of such actions in a stored procedure
- is not reproducible through the binary log used for
- replication. It records only DML events and does not factor in
- timing constraints.
- </para>
-
- <para>
- Finally, non-transactional tables for which errors occur
- during large DML actions (such as bulk inserts) may experience
- replication issues in that a master may be partially updated
- from DML activity, but no updates are done to the slave
- because of the errors that occurred. A workaround is for a
- function's DML actions to be carried out with the
- <literal>IGNORE</literal> keyword so that updates on the
- master that cause errors are ignored and updates that do not
- cause errors are replicated to the slave.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Do the preceding limitations affect MySQL's ability to do
- point-in-time recovery?
- </para>
-
- <para>
- The same limitations that affect replication do affect
- point-in-time recovery.
- </para>
- </listitem>
-
- <listitem>
- <para>
- What will MySQL do to correct the aforementioned limitations?
- </para>
-
- <para>
- As of MySQL 5.1.5, you can choose either statement-based
- replication or row-based replication. The original replication
- implementation is based on statement-based binary logging.
- Row-based binary logging resolves the limitations mentioned
- earlier. For additional information, see
- <xref linkend="replication-row-based"/>.
- </para>
-
-<!-- this hybrid form isn't implemented yet
- <para>
- A hybrid form of replication that will utilize a
- <quote>smart</quote> form of both statement-based and
- row-level replication. This form of replication will
- <quote>know</quote> when it can use statement-level
- replication and when row-level will be required.
- </para>
--->
- </listitem>
-
- <listitem>
- <para>
- Do triggers work with replication?
- </para>
-
- <para>
- Triggers and replication in MySQL ¤t-series; work the
- same as in most other database engines: Actions carried out
- through triggers on a master are not replicated to a slave
- server. Instead, triggers that exist on tables that reside on
- a MySQL master server need to be created on the corresponding
- tables on any MySQL slave servers so that the triggers
- activate on the slaves as well as the master.
- </para>
- </listitem>
-
- <listitem>
- <para>
- How are actions carried out through triggers on a master
- replicated to a slave?
- </para>
-
- <para>
- First, the triggers that exist on a master must be re-created
- on the slave server. Once this is done, the replication flow
- works as any other standard DML statement that participates in
- replication. For example, consider a table
- <literal>EMP</literal> that has an <literal>AFTER</literal>
- insert trigger, which exists on a master MySQL server. The
- same <literal>EMP</literal> table and <literal>AFTER</literal>
- insert trigger exist on the slave server as well. The
- replication flow would be:
- </para>
-
- <orderedlist>
-
- <listitem>
- <para>
- An <literal>INSERT</literal> statement is made to
- <literal>EMP</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The <literal>AFTER</literal> trigger on
- <literal>EMP</literal> activates.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The <literal>INSERT</literal> statement is written to the
- binary log.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The replication slave picks up the
- <literal>INSERT</literal> statement to
- <literal>EMP</literal> and executes it.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The <literal>AFTER</literal> trigger on
- <literal>EMP</literal> that exists on the slave activates.
- </para>
- </listitem>
-
- </orderedlist>
- </listitem>
-
- </itemizedlist>
-
- </section>
-
<section id="stored-procedure-logging">
<title>Binary Logging of Stored Routines and Triggers</title>
Modified: trunk/refman-5.0/faqs.xml
===================================================================
--- trunk/refman-5.0/faqs.xml 2007-01-04 05:08:58 UTC (rev 4373)
+++ trunk/refman-5.0/faqs.xml 2007-01-04 07:04:33 UTC (rev 4374)
Changed blocks: 2, Lines Added: 347, Lines Deleted: 2; 10732 bytes
@@ -80,12 +80,12 @@
see <xref linkend="update"/>; for that required to perform
multi-table deletes, see <xref linkend="delete"/>.
</para>
-
+
<para>
A multi-table insert can be accomplished using a trigger
whose <literal>FOR EACH ROW</literal> clause contains
multiple <literal>INSERT</literal> statements within a
- <literal>BEGIN ... END</literal> block. See
+ <literal>BEGIN ... END</literal> block. See
<xref linkend="using-triggers"/>.
</para>
@@ -1429,6 +1429,351 @@
</section>
+ <section id="faqs-stored-routines-triggers-replication">
+
+ <title>MySQL ¤t-series; FAQ — Stored Routines, Triggers, and
+ Replication</title>
+
+ <qandaset defaultlabel="qanda">
+
+ <qandaentry>
+
+ <question>
+
+ <para>
+ Do MySQL ¤t-series; stored procedures and functions
+ work with replication?
+ </para>
+
+ </question>
+
+ <answer>
+
+ <para>
+ Yes, standard actions carried out in stored procedures and
+ functions are replicated from a master MySQL server to a
+ slave server. There are a few limitations that are described
+ in detail in <xref linkend="stored-procedure-logging"/>.
+ </para>
+
+ </answer>
+
+ </qandaentry>
+
+ <qandaentry>
+
+ <question>
+
+ <para>
+ Are stored procedures and functions created on a master
+ server replicated to a slave?
+ </para>
+
+ </question>
+
+ <answer>
+
+ <para>
+ Yes, creation of stored procedures and functions carried out
+ through normal DDL statements on a master server are
+ replicated to a slave, so the objects will exist on both
+ servers. <literal>ALTER</literal> and
+ <literal>DROP</literal> statements for stored procedures and
+ functions are also replicated.
+ </para>
+
+ </answer>
+
+ </qandaentry>
+
+ <qandaentry>
+
+ <question>
+
+ <para>
+ How are actions that take place inside stored procedures and
+ functions replicated?
+ </para>
+
+ </question>
+
+ <answer>
+
+ <para>
+ MySQL records each DML event that occurs in a stored
+ procedure and replicates those individual actions to a slave
+ server. The actual calls made to execute stored procedures
+ are not replicated.
+ </para>
+
+ <para>
+ Stored functions that change data are logged as function
+ invocations, not as the DML events that occur inside each
+ function.
+ </para>
+
+ </answer>
+
+ </qandaentry>
+
+ <qandaentry>
+
+ <question>
+
+ <para>
+ Are there special security requirements for using stored
+ procedures and functions together with replication?
+ </para>
+
+ </question>
+
+ <answer>
+
+ <para>
+ Yes. Because a slave server has authority to execute any
+ statement read from a master's binary log, special security
+ constraints exist for using stored functions with
+ replication. If replication or binary logging in general
+ (for the purpose of point-in-time recovery) is active, then
+ MySQL DBAs have two security options open to them:
+
+ <orderedlist>
+
+ <listitem>
+ <para>
+ Any user wishing to create stored functions must be
+ granted the <literal>SUPER</literal> privilege.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Alternatively, a DBA can set the
+ <literal>log_bin_trust_function_creators</literal>
+ system variable to 1, which enables anyone with the
+ standard <literal>CREATE ROUTINE</literal> privilege
+ to create stored functions.
+ </para>
+ </listitem>
+
+ </orderedlist>
+ </para>
+
+ </answer>
+
+ </qandaentry>
+
+ <qandaentry>
+
+ <question>
+
+ <para>
+ What limitations exist for replicating stored procedure and
+ function actions?
+ </para>
+
+ </question>
+
+ <answer>
+
+ <para>
+ Non-deterministic (random) or time-based actions embedded in
+ stored procedures may not replicate properly. By their very
+ nature, randomly produced results are not predictable and
+ cannot be exactly reproduced, and therefore, random actions
+ replicated to a slave will not mirror those performed on a
+ master. Note that declaring stored functions to be
+ <literal>DETERMINISTIC</literal> or setting the
+ <literal>log_bin_trust_function_creators</literal> system
+ variable to 0 will not allow random-valued operations to be
+ invoked.
+ </para>
+
+ <para>
+ In addition, time-based actions cannot be reproduced on a
+ slave because the timing of such actions in a stored
+ procedure is not reproducible through the binary log used
+ for replication. It records only DML events and does not
+ factor in timing constraints.
+ </para>
+
+ <para>
+ Finally, non-transactional tables for which errors occur
+ during large DML actions (such as bulk inserts) may
+ experience replication issues in that a master may be
+ partially updated from DML activity, but no updates are done
+ to the slave because of the errors that occurred. A
+ workaround is for a function's DML actions to be carried out
+ with the <literal>IGNORE</literal> keyword so that updates
+ on the master that cause errors are ignored and updates that
+ do not cause errors are replicated to the slave.
+ </para>
+
+ </answer>
+
+ </qandaentry>
+
+ <qandaentry>
+
+ <question>
+
+ <para>
+ Do the preceding limitations affect MySQL's ability to do
+ point-in-time recovery?
+ </para>
+
+ </question>
+
+ <answer>
+
+ <para>
+ The same limitations that affect replication do affect
+ point-in-time recovery.
+ </para>
+
+ </answer>
+
+ </qandaentry>
+
+ <qandaentry>
+
+ <question>
+
+ <para>
+ What is being done to correct the aforementioned
+ limitations?
+ </para>
+
+ </question>
+
+ <answer>
+
+ <para>
+ MySQL 5.1 implements <firstterm>row-based
+ replication</firstterm>, which resolves the limitations
+ mentioned earlier.
+ </para>
+
+ <para>
+ We do not plan to backport row-based replication to MySQL
+ 5.0. For additional information, see
+ <ulink url="http://dev.mysql.com/doc/refman"><citetitle>Row-Based
+ Replication</citetitle>, in the <citetitle>MySQL 5.1
+ Manual</citetitle></ulink>.
+ </para>
+
+ </answer>
+
+ </qandaentry>
+
+ <qandaentry>
+
+ <question>
+
+ <para>
+ Do triggers work with replication?
+ </para>
+
+ </question>
+
+ <answer>
+
+ <para>
+ Triggers and replication in MySQL ¤t-series; work in
+ the same wasy as in most other database engines: Actions
+ carried out through triggers on a master are not replicated
+ to a slave server. Instead, triggers that exist on tables
+ that reside on a MySQL master server need to be created on
+ the corresponding tables on any MySQL slave servers so that
+ the triggers activate on the slaves as well as the master.
+ </para>
+
+ </answer>
+
+ </qandaentry>
+
+ <qandaentry>
+
+ <question>
+
+ <para>
+ How are actions carried out through triggers on a master
+ replicated to a slave?
+ </para>
+
+ </question>
+
+ <answer>
+
+ <para>
+ First, the triggers that exist on a master must be
+ re-created on the slave server. Once this is done, the
+ replication flow works as any other standard DML statement
+ that participates in replication. For example, consider a
+ table <literal>EMP</literal> that has an
+ <literal>AFTER</literal> insert trigger, which exists on a
+ master MySQL server. The same <literal>EMP</literal> table
+ and <literal>AFTER</literal> insert trigger exist on the
+ slave server as well. The replication flow would be:
+ </para>
+
+ <orderedlist>
+
+ <listitem>
+ <para>
+ An <literal>INSERT</literal> statement is made to
+ <literal>EMP</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The <literal>AFTER</literal> trigger on
+ <literal>EMP</literal> activates.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The <literal>INSERT</literal> statement is written to
+ the binary log.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The replication slave picks up the
+ <literal>INSERT</literal> statement to
+ <literal>EMP</literal> and executes it.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The <literal>AFTER</literal> trigger on
+ <literal>EMP</literal> that exists on the slave
+ activates.
+ </para>
+ </listitem>
+
+ </orderedlist>
+
+ </answer>
+
+ </qandaentry>
+
+ </qandaset>
+
+ <para>
+ For answers to some general questions about MySQL stored
+ procedures and stored functions, see
+ <xref linkend="faqs-stored-procs"/>. Some common questions
+ concerning MySQL triggers are adressed in
+ <xref linkend="faqs-triggers"/>.
+ </para>
+
+ </section>
+
<section id="faqs-views">
<title>MySQL ¤t-series; FAQ — Views</title>
Modified: trunk/refman-5.0/renamed-nodes.txt
===================================================================
--- trunk/refman-5.0/renamed-nodes.txt 2007-01-04 05:08:58 UTC (rev 4373)
+++ trunk/refman-5.0/renamed-nodes.txt 2007-01-04 07:04:33 UTC (rev 4374)
Changed blocks: 1, Lines Added: 4, Lines Deleted: 0; 430 bytes
@@ -637,3 +637,7 @@
general-installation-issues installing-cs
faq-security faqs-security
+
+stored-procedure-replication-faq faqs-stored-routines-triggers-replication
+
+
Modified: trunk/refman-5.0/stored-procedures.xml
===================================================================
--- trunk/refman-5.0/stored-procedures.xml 2007-01-04 05:08:58 UTC (rev 4373)
+++ trunk/refman-5.0/stored-procedures.xml 2007-01-04 07:04:33 UTC (rev 4374)
Changed blocks: 1, Lines Added: 0, Lines Deleted: 276; 9592 bytes
@@ -2175,282 +2175,6 @@
</section>
- <section id="stored-procedure-replication-faq">
-
- <title>Stored Procedures, Functions, Triggers, and Replication: Frequently
- Asked Questions</title>
-
- <remark role="todo">
- [js] This section needs to be rewritten - or possibly even cut
- altogether - once row-based replication is implemented.
- </remark>
-
- <itemizedlist>
-
- <listitem>
- <para>
- Do MySQL ¤t-series; stored procedures and functions work
- with replication?
- </para>
-
- <para>
- Yes, standard actions carried out in stored procedures and
- functions are replicated from a master MySQL server to a slave
- server. There are a few limitations that are described in
- detail in <xref linkend="stored-procedure-logging"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Are stored procedures and functions created on a master server
- replicated to a slave?
- </para>
-
- <para>
- Yes, creation of stored procedures and functions carried out
- through normal DDL statements on a master server are
- replicated to a slave, so the objects will exist on both
- servers. <literal>ALTER</literal> and <literal>DROP</literal>
- statements for stored procedures and functions are also
- replicated.
- </para>
- </listitem>
-
- <listitem>
- <para>
- How are actions that take place inside stored procedures and
- functions replicated?
- </para>
-
- <para>
- MySQL records each DML event that occurs in a stored procedure
- and replicates those individual actions to a slave server. The
- actual calls made to execute stored procedures are not
- replicated.
- </para>
-
- <para>
- Stored functions that change data are logged as function
- invocations, not as the DML events that occur inside each
- function.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Are there special security requirements for using stored
- procedures and functions together with replication?
- </para>
-
- <para>
- Yes. Because a slave server has authority to execute any
- statement read from a master's binary log, special security
- constraints exist for using stored functions with replication.
- If replication or binary logging in general (for the purpose
- of point-in-time recovery) is active, then MySQL DBAs have two
- security options open to them:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- Any user wishing to create stored functions must be
- granted the <literal>SUPER</literal> privilege.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Alternatively, a DBA can set the
- <literal>log_bin_trust_function_creators</literal> system
- variable to 1, which enables anyone with the standard
- <literal>CREATE ROUTINE</literal> privilege to create
- stored functions.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- Note: Before MySQL 5.0.16, these restrictions also apply to
- stored procedures and the system variable is named
- <literal>log_bin_trust_routine_creators</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- What limitations exist for replicating stored procedure and
- function actions?
- </para>
-
- <para>
- Non-deterministic (random) or time-based actions embedded in
- stored procedures may not replicate properly. By their very
- nature, randomly produced results are not predictable and
- cannot be exactly reproduced, and therefore, random actions
- replicated to a slave will not mirror those performed on a
- master. Note that declaring stored functions to be
- <literal>DETERMINISTIC</literal> or setting the
- <literal>log_bin_trust_function_creators</literal> system
- variable to 0 will not allow random-valued operations to be
- invoked.
- </para>
-
- <para>
- In addition, time-based actions cannot be reproduced on a
- slave because the timing of such actions in a stored procedure
- is not reproducible through the binary log used for
- replication. It records only DML events and does not factor in
- timing constraints.
- </para>
-
- <para>
- Finally, non-transactional tables for which errors occur
- during large DML actions (such as bulk inserts) may experience
- replication issues in that a master may be partially updated
- from DML activity, but no updates are done to the slave
- because of the errors that occurred. A workaround is for a
- function's DML actions to be carried out with the
- <literal>IGNORE</literal> keyword so that updates on the
- master that cause errors are ignored and updates that do not
- cause errors are replicated to the slave.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Do the preceding limitations affect MySQL's ability to do
- point-in-time recovery?
- </para>
-
- <para>
- The same limitations that affect replication do affect
- point-in-time recovery.
- </para>
- </listitem>
-
- <listitem>
- <para>
- What will MySQL do to correct the aforementioned limitations?
- </para>
-
- <para>
- A future release of MySQL is expected to feature a choice in
- how replication should be handled:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- Statement-based replication (current implementation).
- </para>
- </listitem>
-
- <listitem>
- <para>
- Row-level replication (that will solve all the limitations
- described earlier).
- </para>
- </listitem>
-
-<!-- this hybrid form isn't implemented yet
- <listitem>
- <para>
- A hybrid form of replication that will utilize a
- <quote>smart</quote> form of both statement-based and
- row-level replication. This form of replication will
- <quote>know</quote> when it can use statement-level
- replication and when row-level will be required.
- </para>
- </listitem>
--->
-
- </itemizedlist>
- </listitem>
-
- <listitem>
- <para>
- Do triggers work with replication?
- </para>
-
- <para>
- Triggers and replication in MySQL ¤t-series; work the
- same as in most other database engines: Actions carried out
- through triggers on a master are not replicated to a slave
- server. Instead, triggers that exist on tables that reside on
- a MySQL master server need to be created on the corresponding
- tables on any MySQL slave servers so that the triggers
- activate on the slaves as well as the master.
- </para>
- </listitem>
-
- <listitem>
- <para>
- How are actions carried out through triggers on a master
- replicated to a slave?
- </para>
-
- <para>
- First, the triggers that exist on a master must be re-created
- on the slave server. Once this is done, the replication flow
- works as any other standard DML statement that participates in
- replication. For example, consider a table
- <literal>EMP</literal> that has an <literal>AFTER</literal>
- insert trigger, which exists on a master MySQL server. The
- same <literal>EMP</literal> table and <literal>AFTER</literal>
- insert trigger exist on the slave server as well. The
- replication flow would be:
- </para>
-
- <orderedlist>
-
- <listitem>
- <para>
- An <literal>INSERT</literal> statement is made to
- <literal>EMP</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The <literal>AFTER</literal> trigger on
- <literal>EMP</literal> activates.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The <literal>INSERT</literal> statement is written to the
- binary log.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The replication slave picks up the
- <literal>INSERT</literal> statement to
- <literal>EMP</literal> and executes it.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The <literal>AFTER</literal> trigger on
- <literal>EMP</literal> that exists on the slave activates.
- </para>
- </listitem>
-
- </orderedlist>
- </listitem>
-
- </itemizedlist>
-
- </section>
-
<section id="stored-procedure-logging">
<title>Binary Logging of Stored Routines and Triggers</title>
Modified: trunk/refman-5.1/faqs.xml
===================================================================
--- trunk/refman-5.1/faqs.xml 2007-01-04 05:08:58 UTC (rev 4373)
+++ trunk/refman-5.1/faqs.xml 2007-01-04 07:04:33 UTC (rev 4374)
Changed blocks: 4, Lines Added: 364, Lines Deleted: 5; 11490 bytes
@@ -340,8 +340,9 @@
&falcon-warning;
- <para>
- For information about the Falcon storage engine, see <xref linkend="se-falcon"/>.
+ <para>
+ For information about the Falcon storage engine, see
+ <xref linkend="se-falcon"/>.
</para>
<para>
@@ -1445,6 +1446,364 @@
</section>
+ <section id="faqs-stored-routines-triggers-replication">
+
+ <title>MySQL ¤t-series; FAQ — Stored Routines, Triggers, and
+ Replication</title>
+
+ <remark role="todo">
+ [js] This section needs to be rewritten for 5.1 to reflect
+ differences bewteen statement-based and row-based replication.
+ </remark>
+
+ <qandaset defaultlabel="qanda">
+
+ <qandaentry>
+
+ <question>
+
+ <para>
+ Do MySQL ¤t-series; stored procedures and functions
+ work with replication?
+ </para>
+
+ </question>
+
+ <answer>
+
+ <para>
+ Yes, standard actions carried out in stored procedures and
+ functions are replicated from a master MySQL server to a
+ slave server. There are a few limitations that are described
+ in detail in <xref linkend="stored-procedure-logging"/>.
+ </para>
+
+ </answer>
+
+ </qandaentry>
+
+ <qandaentry>
+
+ <question>
+
+ <para>
+ Are stored procedures and functions created on a master
+ server replicated to a slave?
+ </para>
+
+ </question>
+
+ <answer>
+
+ <para>
+ Yes, creation of stored procedures and functions carried out
+ through normal DDL statements on a master server are
+ replicated to a slave, so the objects will exist on both
+ servers. <literal>ALTER</literal> and
+ <literal>DROP</literal> statements for stored procedures and
+ functions are also replicated.
+ </para>
+
+ </answer>
+
+ </qandaentry>
+
+ <qandaentry>
+
+ <question>
+
+ <para>
+ How are actions that take place inside stored procedures and
+ functions replicated?
+ </para>
+
+ </question>
+
+ <answer>
+
+ <para>
+ MySQL records each DML event that occurs in a stored
+ procedure and replicates those individual actions to a slave
+ server. The actual calls made to execute stored procedures
+ are not replicated.
+ </para>
+
+ <para>
+ Stored functions that change data are logged as function
+ invocations, not as the DML events that occur inside each
+ function.
+ </para>
+
+ </answer>
+
+ </qandaentry>
+
+ <qandaentry>
+
+ <question>
+
+ <para>
+ Are there special security requirements for using stored
+ procedures and functions together with replication?
+ </para>
+
+ </question>
+
+ <answer>
+
+ <para>
+ Yes. Because a slave server has authority to execute any
+ statement read from a master's binary log, special security
+ constraints exist for using stored functions with
+ replication. If replication or binary logging in general
+ (for the purpose of point-in-time recovery) is active, then
+ MySQL DBAs have two security options open to them:
+
+ <orderedlist>
+
+ <listitem>
+ <para>
+ Any user wishing to create stored functions must be
+ granted the <literal>SUPER</literal> privilege.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Alternatively, a DBA can set the
+ <literal>log_bin_trust_function_creators</literal>
+ system variable to 1, which enables anyone with the
+ standard <literal>CREATE ROUTINE</literal> privilege
+ to create stored functions.
+ </para>
+ </listitem>
+
+ </orderedlist>
+ </para>
+
+ </answer>
+
+ </qandaentry>
+
+ <qandaentry>
+
+ <question>
+
+ <para>
+ What limitations exist for replicating stored procedure and
+ function actions?
+ </para>
+
+ </question>
+
+ <answer>
+
+ <para>
+ Non-deterministic (random) or time-based actions embedded in
+ stored procedures may not replicate properly. By their very
+ nature, randomly produced results are not predictable and
+ cannot be exactly reproduced, and therefore, random actions
+ replicated to a slave will not mirror those performed on a
+ master. Note that declaring stored functions to be
+ <literal>DETERMINISTIC</literal> or setting the
+ <literal>log_bin_trust_function_creators</literal> system
+ variable to 0 will not allow random-valued operations to be
+ invoked.
+ </para>
+
+ <para>
+ In addition, time-based actions cannot be reproduced on a
+ slave because the timing of such actions in a stored
+ procedure is not reproducible through the binary log used
+ for replication. It records only DML events and does not
+ factor in timing constraints.
+ </para>
+
+ <para>
+ Finally, non-transactional tables for which errors occur
+ during large DML actions (such as bulk inserts) may
+ experience replication issues in that a master may be
+ partially updated from DML activity, but no updates are done
+ to the slave because of the errors that occurred. A
+ workaround is for a function's DML actions to be carried out
+ with the <literal>IGNORE</literal> keyword so that updates
+ on the master that cause errors are ignored and updates that
+ do not cause errors are replicated to the slave.
+ </para>
+
+ </answer>
+
+ </qandaentry>
+
+ <qandaentry>
+
+ <question>
+
+ <para>
+ Do the preceding limitations affect MySQL's ability to do
+ point-in-time recovery?
+ </para>
+
+ </question>
+
+ <answer>
+
+ <para>
+ The same limitations that affect replication do affect
+ point-in-time recovery.
+ </para>
+
+ </answer>
+
+ </qandaentry>
+
+ <qandaentry>
+
+ <question>
+
+ <para>
+ What will MySQL do to correct the aforementioned
+ limitations?
+ </para>
+
+ </question>
+
+ <answer>
+
+ <para>
+ As of MySQL 5.1.5, you can choose either statement-based
+ replication or row-based replication. The original
+ replication implementation is based on statement-based
+ binary logging. Row-based binary logging resolves the
+ limitations mentioned earlier.
+ </para>
+
+ <para>
+ Beginning with MySQL 5.1.8, <firstterm>mixed</firstterm>
+ replication is also available (by starting the server with
+ <option>--binlog-format=mixed</option>). This hybrid,
+ <quote>smart</quote> form of replication
+ <quote>knows</quote> whether statement-level replication can
+ safely be used, or row-level replication is required.
+ </para>
+
+ <para>
+ For additional information, see
+ <xref linkend="replication-row-based"/>.
+ </para>
+
+ </answer>
+
+ </qandaentry>
+
+ <qandaentry>
+
+ <question>
+
+ <para>
+ Do triggers work with replication?
+ </para>
+
+ </question>
+
+ <answer>
+
+ <para>
+ Triggers and replication in MySQL ¤t-series; work in
+ the same wasy as in most other database engines: Actions
+ carried out through triggers on a master are not replicated
+ to a slave server. Instead, triggers that exist on tables
+ that reside on a MySQL master server need to be created on
+ the corresponding tables on any MySQL slave servers so that
+ the triggers activate on the slaves as well as the master.
+ </para>
+
+ </answer>
+
+ </qandaentry>
+
+ <qandaentry>
+
+ <question>
+
+ <para>
+ How are actions carried out through triggers on a master
+ replicated to a slave?
+ </para>
+
+ </question>
+
+ <answer>
+
+ <para>
+ First, the triggers that exist on a master must be
+ re-created on the slave server. Once this is done, the
+ replication flow works as any other standard DML statement
+ that participates in replication. For example, consider a
+ table <literal>EMP</literal> that has an
+ <literal>AFTER</literal> insert trigger, which exists on a
+ master MySQL server. The same <literal>EMP</literal> table
+ and <literal>AFTER</literal> insert trigger exist on the
+ slave server as well. The replication flow would be:
+ </para>
+
+ <orderedlist>
+
+ <listitem>
+ <para>
+ An <literal>INSERT</literal> statement is made to
+ <literal>EMP</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The <literal>AFTER</literal> trigger on
+ <literal>EMP</literal> activates.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The <literal>INSERT</literal> statement is written to
+ the binary log.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The replication slave picks up the
+ <literal>INSERT</literal> statement to
+ <literal>EMP</literal> and executes it.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The <literal>AFTER</literal> trigger on
+ <literal>EMP</literal> that exists on the slave
+ activates.
+ </para>
+ </listitem>
+
+ </orderedlist>
+
+ </answer>
+
+ </qandaentry>
+
+ </qandaset>
+
+ <para>
+ For answers to some general questions about MySQL stored
+ procedures and stored functions, see
+ <xref linkend="faqs-stored-procs"/>. Some common questions
+ concerning MySQL triggers are adressed in
+ <xref linkend="faqs-triggers"/>.
+ </para>
+
+ </section>
+
<section id="faqs-views">
<title>MySQL ¤t-series; FAQ — Views</title>
@@ -1456,7 +1815,7 @@
<question>
<para>
- Where can I find documentation for MySQL Views?
+ Where can I find documentation covering MySQL Views?
</para>
</question>
@@ -4404,8 +4763,8 @@
<secondary>frequently asked questions</secondary>
</indexterm>
</section>
--->
-
+-->
+
<section id="faqs-cjk">
<title>MySQL ¤t-series; FAQ — MySQL Chinese, Japanese, and Korean
Modified: trunk/refman-5.1/renamed-nodes.txt
===================================================================
--- trunk/refman-5.1/renamed-nodes.txt 2007-01-04 05:08:58 UTC (rev 4373)
+++ trunk/refman-5.1/renamed-nodes.txt 2007-01-04 07:04:33 UTC (rev 4374)
Changed blocks: 1, Lines Added: 2, Lines Deleted: 0; 417 bytes
@@ -349,3 +349,5 @@
mysql-cluster-faq faqs-mysql-cluster
faq-security faqs-security
+
+stored-procedure-replication-faq faqs-stored-routines-triggers-replication
Modified: trunk/refman-5.1/replication.xml
===================================================================
--- trunk/refman-5.1/replication.xml 2007-01-04 05:08:58 UTC (rev 4373)
+++ trunk/refman-5.1/replication.xml 2007-01-04 07:04:33 UTC (rev 4374)
Changed blocks: 2, Lines Added: 8, Lines Deleted: 8; 1695 bytes
@@ -221,14 +221,14 @@
<para>
Replication capabilities in MySQL originally were based on
propagation of SQL statements from master to slave. This is called
- <emphasis>statement-based replication</emphasis>. As of MySQL
+ <firstterm>statement-based replication</firstterm>. As of MySQL
5.1.5, another basis for replication is available called
- <emphasis>row-based replication</emphasis>. Instead of sending SQL
- statements to the slave, the master writes events to its binary
- log that indicate how individual table rows are affected. As of
- MySQL 5.1.8, a third option is available: mixed. This will use
- statement-based replication by default, and only switch to
- row-based replication in particular cases as described below.
+ <firstterm>row-based replication</firstterm>. Instead of sending
+ SQL statements to the slave, the master writes events to its
+ binary log that indicate how individual table rows are affected.
+ As of MySQL 5.1.8, a third option is available: mixed. This uses
+ statement-based replication by default, and switches to row-based
+ replication only in particular cases as described below.
</para>
<para>
@@ -266,7 +266,7 @@
</para>
<para>
- To use the mixed format, start the server with the
+ To use mixed format, start the server with the
<option>--binlog-format=mixed</option> option.
</para>
Modified: trunk/refman-5.1/stored-procedures.xml
===================================================================
--- trunk/refman-5.1/stored-procedures.xml 2007-01-04 05:08:58 UTC (rev 4373)
+++ trunk/refman-5.1/stored-procedures.xml 2007-01-04 07:04:33 UTC (rev 4374)
Changed blocks: 2, Lines Added: 4, Lines Deleted: 256; 9613 bytes
@@ -35,7 +35,10 @@
<para>
Answers to some questions that are commonly asked regarding stored
routines in MySQL can be found in
- <xref linkend="faqs-stored-procs"/>.
+ <xref linkend="faqs-stored-procs"/>. In addition, see
+ <xref linkend="faqs-stored-routines-triggers-replication"/>, for
+ answers to some common questions concerning stored routines and
+ replication.
</para>
<para>
@@ -2147,261 +2150,6 @@
</section>
- <section id="stored-procedure-replication-faq">
-
- <title>Stored Procedures, Functions, Triggers, and Replication: Frequently
- Asked Questions</title>
-
- <remark role="todo">
- [js] This section needs to be rewritten - or possibly even cut
- altogether - once row-based replication is implemented.
- </remark>
-
- <itemizedlist>
-
- <listitem>
- <para>
- Do MySQL ¤t-series; stored procedures and functions work
- with replication?
- </para>
-
- <para>
- Yes, standard actions carried out in stored procedures and
- functions are replicated from a master MySQL server to a slave
- server. There are a few limitations that are described in
- detail in <xref linkend="stored-procedure-logging"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Are stored procedures and functions created on a master server
- replicated to a slave?
- </para>
-
- <para>
- Yes, creation of stored procedures and functions carried out
- through normal DDL statements on a master server are
- replicated to a slave, so the objects will exist on both
- servers. <literal>ALTER</literal> and <literal>DROP</literal>
- statements for stored procedures and functions are also
- replicated.
- </para>
- </listitem>
-
- <listitem>
- <para>
- How are actions that take place inside stored procedures and
- functions replicated?
- </para>
-
- <para>
- MySQL records each DML event that occurs in a stored procedure
- and replicates those individual actions to a slave server. The
- actual calls made to execute stored procedures are not
- replicated.
- </para>
-
- <para>
- Stored functions that change data are logged as function
- invocations, not as the DML events that occur inside each
- function.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Are there special security requirements for using stored
- procedures and functions together with replication?
- </para>
-
- <para>
- Yes. Because a slave server has authority to execute any
- statement read from a master's binary log, special security
- constraints exist for using stored functions with replication.
- If replication or binary logging in general (for the purpose
- of point-in-time recovery) is active, then MySQL DBAs have two
- security options open to them:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- Any user wishing to create stored functions must be
- granted the <literal>SUPER</literal> privilege.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Alternatively, a DBA can set the
- <literal>log_bin_trust_function_creators</literal> system
- variable to 1, which enables anyone with the standard
- <literal>CREATE ROUTINE</literal> privilege to create
- stored functions.
- </para>
- </listitem>
-
- </itemizedlist>
- </listitem>
-
- <listitem>
- <para>
- What limitations exist for replicating stored procedure and
- function actions?
- </para>
-
- <para>
- Non-deterministic (random) or time-based actions embedded in
- stored procedures may not replicate properly. By their very
- nature, randomly produced results are not predictable and
- cannot be exactly reproduced, and therefore, random actions
- replicated to a slave will not mirror those performed on a
- master. Note that declaring stored functions to be
- <literal>DETERMINISTIC</literal> or setting the
- <literal>log_bin_trust_function_creators</literal> system
- variable to 0 will not allow random-valued operations to be
- invoked.
- </para>
-
- <para>
- In addition, time-based actions cannot be reproduced on a
- slave because the timing of such actions in a stored procedure
- is not reproducible through the binary log used for
- replication. It records only DML events and does not factor in
- timing constraints.
- </para>
-
- <para>
- Finally, non-transactional tables for which errors occur
- during large DML actions (such as bulk inserts) may experience
- replication issues in that a master may be partially updated
- from DML activity, but no updates are done to the slave
- because of the errors that occurred. A workaround is for a
- function's DML actions to be carried out with the
- <literal>IGNORE</literal> keyword so that updates on the
- master that cause errors are ignored and updates that do not
- cause errors are replicated to the slave.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Do the preceding limitations affect MySQL's ability to do
- point-in-time recovery?
- </para>
-
- <para>
- The same limitations that affect replication do affect
- point-in-time recovery.
- </para>
- </listitem>
-
- <listitem>
- <para>
- What will MySQL do to correct the aforementioned limitations?
- </para>
-
- <para>
- As of MySQL 5.1.5, you can choose either statement-based
- replication or row-based replication. The original replication
- implementation is based on statement-based binary logging.
- Row-based binary logging resolves the limitations mentioned
- earlier. For additional information, see
- <xref linkend="replication-row-based"/>.
- </para>
-
-<!-- this hybrid form isn't implemented yet
- <para>
- A hybrid form of replication that will utilize a
- <quote>smart</quote> form of both statement-based and
- row-level replication. This form of replication will
- <quote>know</quote> when it can use statement-level
- replication and when row-level will be required.
- </para>
--->
- </listitem>
-
- <listitem>
- <para>
- Do triggers work with replication?
- </para>
-
- <para>
- Triggers and replication in MySQL ¤t-series; work the
- same as in most other database engines: Actions carried out
- through triggers on a master are not replicated to a slave
- server. Instead, triggers that exist on tables that reside on
- a MySQL master server need to be created on the corresponding
- tables on any MySQL slave servers so that the triggers
- activate on the slaves as well as the master.
- </para>
- </listitem>
-
- <listitem>
- <para>
- How are actions carried out through triggers on a master
- replicated to a slave?
- </para>
-
- <para>
- First, the triggers that exist on a master must be re-created
- on the slave server. Once this is done, the replication flow
- works as any other standard DML statement that participates in
- replication. For example, consider a table
- <literal>EMP</literal> that has an <literal>AFTER</literal>
- insert trigger, which exists on a master MySQL server. The
- same <literal>EMP</literal> table and <literal>AFTER</literal>
- insert trigger exist on the slave server as well. The
- replication flow would be:
- </para>
-
- <orderedlist>
-
- <listitem>
- <para>
- An <literal>INSERT</literal> statement is made to
- <literal>EMP</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The <literal>AFTER</literal> trigger on
- <literal>EMP</literal> activates.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The <literal>INSERT</literal> statement is written to the
- binary log.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The replication slave picks up the
- <literal>INSERT</literal> statement to
- <literal>EMP</literal> and executes it.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The <literal>AFTER</literal> trigger on
- <literal>EMP</literal> that exists on the slave activates.
- </para>
- </listitem>
-
- </orderedlist>
- </listitem>
-
- </itemizedlist>
-
- </section>
-
<section id="stored-procedure-logging">
<title>Binary Logging of Stored Routines and Triggers</title>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r4374 - branches/telcos/refman-5.1 trunk/refman-5.0 trunk/refman-5.1 | jon | 4 Jan |
