MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:mcbrown Date:April 26 2007 11:25am
Subject:svn commit - mysqldoc@docsrva: r6205 - trunk/refman-5.1
View as plain text  
Author: mcbrown
Date: 2007-04-26 13:25:35 +0200 (Thu, 26 Apr 2007)
New Revision: 6205

Log:
Merging the trigger/stored proc *and* replication FAQ sections into the corresponding sections for those items directly, then added link to replication specific FAQ in the main replication chapter. 



Modified:
   trunk/refman-5.1/faqs.xml
   trunk/refman-5.1/renamed-nodes.txt


Modified: trunk/refman-5.1/faqs.xml
===================================================================
--- trunk/refman-5.1/faqs.xml	2007-04-26 11:06:11 UTC (rev 6204)
+++ trunk/refman-5.1/faqs.xml	2007-04-26 11:25:35 UTC (rev 6205)
Changed blocks: 36, Lines Added: 209, Lines Deleted: 222; 24107 bytes

@@ -1158,23 +1158,13 @@
 
       </qandaentry>
 
-    </qandaset>
-
-  </section>
-
-  <section id="faqs-triggers">
-
-    <title>MySQL &current-series; FAQ &mdash; Triggers</title>
-
-    <qandaset defaultlabel="qanda">
-
       <qandaentry>
 
         <question>
 
           <para>
-            Where can I find the documentation for MySQL
-            &current-series; triggers?
+            Do MySQL &current-series; stored procedures and functions
+            work with replication?
           </para>
 
         </question>

@@ -1182,7 +1172,10 @@
         <answer>
 
           <para>
-            See <xref linkend="triggers"/>.
+            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>

@@ -1194,7 +1187,8 @@
         <question>
 
           <para>
-            Is there a discussion forum for MySQL Triggers?
+            Are stored procedures and functions created on a master
+            server replicated to a slave?
           </para>
 
         </question>

@@ -1202,8 +1196,12 @@
         <answer>
 
           <para>
-            Yes. It is available at
-            <ulink url="&base-url-forum-list;?99"/>.
+            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>

@@ -1215,8 +1213,8 @@
         <question>
 
           <para>
-            Does MySQL &current-series; have statement-level or
-            row-level triggers?
+            How are actions that take place inside stored procedures and
+            functions replicated?
           </para>
 
         </question>

@@ -1224,13 +1222,18 @@
         <answer>
 
           <para>
-            In MySQL &current-series;, all triggers are <literal>FOR
-            EACH ROW</literal> &mdash; that is, the trigger is activated
-            for each row that is inserted, updated, or deleted. MySQL
-            &current-series; does not support triggers using
-            <literal>FOR EACH STATEMENT</literal>.
+            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>

@@ -1240,7 +1243,8 @@
         <question>
 
           <para>
-            Are there any default triggers?
+            Are there special security requirements for using stored
+            procedures and functions together with replication?
           </para>
 
         </question>

@@ -1248,10 +1252,33 @@
         <answer>
 
           <para>
-            Not explicitly. MySQL does have specific special behavior
-            for some <literal>TIMESTAMP</literal> columns, as well as
-            for columns which are defined using
-            <literal>AUTO_INCREMENT</literal>.
+            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>

@@ -1263,7 +1290,8 @@
         <question>
 
           <para>
-            How are triggers managed in MySQL?
+            What limitations exist for replicating stored procedure and
+            function actions?
           </para>
 
         </question>

@@ -1271,22 +1299,38 @@
         <answer>
 
           <para>
-            In MySQL &current-series;, triggers can be created using the
-            <literal>CREATE TRIGGER</literal> statement, and dropped
-            using <literal>DROP TRIGGER</literal>. See
-            <xref
-              linkend="create-trigger"/>, and
-            <xref
-              linkend="drop-trigger"/>, for more about
-            these statements.
+            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>
-            Information about triggers can be obtained by querying the
-            <literal>INFORMATION_SCHEMA.TRIGGERS</literal> table. See
-            <xref linkend="triggers-table"/>.
+            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>

@@ -1296,7 +1340,8 @@
         <question>
 
           <para>
-            Is there a way to view all triggers in a given database?
+            Do the preceding limitations affect MySQL's ability to do
+            point-in-time recovery?
           </para>
 
         </question>

@@ -1304,31 +1349,10 @@
         <answer>
 
           <para>
-            Yes. You can obtain a listing of all triggers defined on
-            database <literal>dbname</literal> using a query on the
-            INFORMATION_SCHEMA.TRIGGERS table such as the one shown
-            here:
+            The same limitations that affect replication do affect
+            point-in-time recovery.
           </para>
 
-<programlisting>
-SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_STATEMENT 
-    FROM INFORMATION_SCHEMA.TRIGGERS 
-    WHERE TRIGGER_SCHEMA='<replaceable>dbname</replaceable>';
-</programlisting>
-
-          <para>
-            For more information about this table, see
-            <xref
-              linkend="triggers-table"/>.
-          </para>
-
-          <para>
-            You can also use the <literal>SHOW TRIGGERS</literal>
-            statement, which is specific to MySQL. See
-            <xref
-              linkend="show-triggers"/>.
-          </para>
-
         </answer>
 
       </qandaentry>

@@ -1338,7 +1362,8 @@
         <question>
 
           <para>
-            Where are triggers stored?
+            What will MySQL do to correct the aforementioned
+            limitations?
           </para>
 
         </question>

@@ -1346,31 +1371,48 @@
         <answer>
 
           <para>
-            Triggers are currently stored in <filename>.TRG</filename>
-            files, with one such file one per table. In other words, a
-            trigger belongs to a table.
+            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>
-            In the future, we plan to change this so that trigger
-            information will be included in the
-            <filename>.FRM</filename> file that defines the structure of
-            the table. We also plan to make triggers database-level
-            objects &mdash; rather than table-level objects as they are
-            now &mdash; to bring them into compliance with the SQL
-            standard.
+            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-formats"/>.
+          </para>
+
         </answer>
 
       </qandaentry>
 
+    </qandaset>
+
+  </section>
+
+  <section id="faqs-triggers">
+
+    <title>MySQL &current-series; FAQ &mdash; Triggers</title>
+
+    <qandaset defaultlabel="qanda">
+
       <qandaentry>
 
         <question>
 
           <para>
-            Can a trigger call a stored procedure?
+            Where can I find the documentation for MySQL
+            &current-series; triggers?
           </para>
 
         </question>

@@ -1378,7 +1420,7 @@
         <answer>
 
           <para>
-            Yes.
+            See <xref linkend="triggers"/>.
           </para>
 
         </answer>

@@ -1390,7 +1432,7 @@
         <question>
 
           <para>
-            Can triggers access tables?
+            Is there a discussion forum for MySQL Triggers?
           </para>
 
         </question>

@@ -1398,9 +1440,8 @@
         <answer>
 
           <para>
-            A trigger can access both old and new data in its own table.
-            Through a stored procedure, or a multiple-table update or
-            delete statement, a trigger can also affect other tables.
+            Yes. It is available at
+            <ulink url="&base-url-forum-list;?99"/>.
           </para>
 
         </answer>

@@ -1412,7 +1453,8 @@
         <question>
 
           <para>
-            Can triggers call an external application through a UDF?
+            Does MySQL &current-series; have statement-level or
+            row-level triggers?
           </para>
 
         </question>

@@ -1420,7 +1462,11 @@
         <answer>
 
           <para>
-            No, not at present.
+            In MySQL &current-series;, all triggers are <literal>FOR
+            EACH ROW</literal> &mdash; that is, the trigger is activated
+            for each row that is inserted, updated, or deleted. MySQL
+            &current-series; does not support triggers using
+            <literal>FOR EACH STATEMENT</literal>.
           </para>
 
         </answer>

@@ -1432,8 +1478,7 @@
         <question>
 
           <para>
-            Is possible for a trigger to update tables on a remote
-            server?
+            Are there any default triggers?
           </para>
 
         </question>

@@ -1441,39 +1486,22 @@
         <answer>
 
           <para>
-            Yes. A table on a remote server could be updated using the
-            <literal>FEDERATED</literal> storage engine. (See
-            <xref
-              linkend="federated-storage-engine"/>).
+            Not explicitly. MySQL does have specific special behavior
+            for some <literal>TIMESTAMP</literal> columns, as well as
+            for columns which are defined using
+            <literal>AUTO_INCREMENT</literal>.
           </para>
 
         </answer>
 
       </qandaentry>
 
-    </qandaset>
-
-  </section>
-
-  <section id="faqs-stored-routines-triggers-replication">
-
-    <title>MySQL &current-series; FAQ &mdash; 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 &current-series; stored procedures and functions
-            work with replication?
+            How are triggers managed in MySQL?
           </para>
 
         </question>

@@ -1481,12 +1509,22 @@
         <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"/>.
+            In MySQL &current-series;, triggers can be created using the
+            <literal>CREATE TRIGGER</literal> statement, and dropped
+            using <literal>DROP TRIGGER</literal>. See
+            <xref
+              linkend="create-trigger"/>, and
+            <xref
+              linkend="drop-trigger"/>, for more about
+            these statements.
           </para>
 
+          <para>
+            Information about triggers can be obtained by querying the
+            <literal>INFORMATION_SCHEMA.TRIGGERS</literal> table. See
+            <xref linkend="triggers-table"/>.
+          </para>
+
         </answer>
 
       </qandaentry>

@@ -1496,8 +1534,7 @@
         <question>
 
           <para>
-            Are stored procedures and functions created on a master
-            server replicated to a slave?
+            Is there a way to view all triggers in a given database?
           </para>
 
         </question>

@@ -1505,14 +1542,31 @@
         <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.
+            Yes. You can obtain a listing of all triggers defined on
+            database <literal>dbname</literal> using a query on the
+            INFORMATION_SCHEMA.TRIGGERS table such as the one shown
+            here:
           </para>
 
+<programlisting>
+SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_STATEMENT 
+    FROM INFORMATION_SCHEMA.TRIGGERS 
+    WHERE TRIGGER_SCHEMA='<replaceable>dbname</replaceable>';
+</programlisting>
+
+          <para>
+            For more information about this table, see
+            <xref
+              linkend="triggers-table"/>.
+          </para>
+
+          <para>
+            You can also use the <literal>SHOW TRIGGERS</literal>
+            statement, which is specific to MySQL. See
+            <xref
+              linkend="show-triggers"/>.
+          </para>
+
         </answer>
 
       </qandaentry>

@@ -1522,8 +1576,7 @@
         <question>
 
           <para>
-            How are actions that take place inside stored procedures and
-            functions replicated?
+            Where are triggers stored?
           </para>
 
         </question>

@@ -1531,16 +1584,19 @@
         <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.
+            Triggers are currently stored in <filename>.TRG</filename>
+            files, with one such file one per table. In other words, a
+            trigger belongs to a table.
           </para>
 
           <para>
-            Stored functions that change data are logged as function
-            invocations, not as the DML events that occur inside each
-            function.
+            In the future, we plan to change this so that trigger
+            information will be included in the
+            <filename>.FRM</filename> file that defines the structure of
+            the table. We also plan to make triggers database-level
+            objects &mdash; rather than table-level objects as they are
+            now &mdash; to bring them into compliance with the SQL
+            standard.
           </para>
 
         </answer>

@@ -1552,8 +1608,7 @@
         <question>
 
           <para>
-            Are there special security requirements for using stored
-            procedures and functions together with replication?
+            Can a trigger call a stored procedure?
           </para>
 
         </question>

@@ -1561,33 +1616,7 @@
         <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>
+            Yes.
           </para>
 
         </answer>

@@ -1599,8 +1628,7 @@
         <question>
 
           <para>
-            What limitations exist for replicating stored procedure and
-            function actions?
+            Can triggers access tables?
           </para>
 
         </question>

@@ -1608,38 +1636,11 @@
         <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.
+            A trigger can access both old and new data in its own table.
+            Through a stored procedure, or a multiple-table update or
+            delete statement, a trigger can also affect other tables.
           </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>

@@ -1649,8 +1650,7 @@
         <question>
 
           <para>
-            Do the preceding limitations affect MySQL's ability to do
-            point-in-time recovery?
+            Can triggers call an external application through a UDF?
           </para>
 
         </question>

@@ -1658,8 +1658,7 @@
         <answer>
 
           <para>
-            The same limitations that affect replication do affect
-            point-in-time recovery.
+            No, not at present.
           </para>
 
         </answer>

@@ -1671,8 +1670,8 @@
         <question>
 
           <para>
-            What will MySQL do to correct the aforementioned
-            limitations?
+            Is possible for a trigger to update tables on a remote
+            server?
           </para>
 
         </question>

@@ -1680,27 +1679,12 @@
         <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.
+            Yes. A table on a remote server could be updated using the
+            <literal>FEDERATED</literal> storage engine. (See
+            <xref
+              linkend="federated-storage-engine"/>).
           </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-formats"/>.
-          </para>
-
         </answer>
 
       </qandaentry>

@@ -1803,14 +1787,6 @@
 
     </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">

@@ -6972,4 +6948,15 @@
 
 -->
 
+  <section id="faqs-replication">
+
+    <title>MySQL &current-series; FAQ &mdash; Replication</title>
+
+    <para>
+      For answers to common queries and question regarding Replication
+      within MySQL, see <xref linkend="replication-faq"/>.
+    </para>
+
+  </section>
+
 </appendix>


Modified: trunk/refman-5.1/renamed-nodes.txt
===================================================================
--- trunk/refman-5.1/renamed-nodes.txt	2007-04-26 11:06:11 UTC (rev 6204)
+++ trunk/refman-5.1/renamed-nodes.txt	2007-04-26 11:25:35 UTC (rev 6205)
Changed blocks: 1, Lines Added: 1, Lines Deleted: 0; 552 bytes

@@ -382,3 +382,4 @@
 rts-threads             http://dev.mysql.com/doc/internals/en/rts-threads
 thread-packages         http://dev.mysql.com/doc/internals/en/thread-packages
 tips miscellaneous-optimization-tips
+faqs-stored-routines-triggers-replication faqs-stored-procs
\ No newline at end of file


Thread
svn commit - mysqldoc@docsrva: r6205 - trunk/refman-5.1mcbrown26 Apr