| List: | Commits | « Previous MessageNext Message » | |
| From: | paul | Date: | June 17 2008 5:56pm |
| Subject: | svn commit - mysqldoc@docsrva: r10970 - in trunk: . it/refman-5.1 pt/refman-5.1 refman-5.0 refman-5.1 refman-6.0 | ||
| View as plain text | |||
Author: paul Date: 2008-06-17 17:56:29 +0200 (Tue, 17 Jun 2008) New Revision: 10970 Log: r32088@frost: paul | 2008-06-17 10:55:17 -0500 Revise stored-routine binlogging section. (Bug#37370) Modified: trunk/it/refman-5.1/replication-notes.xml trunk/it/refman-5.1/stored-procedures.xml trunk/pt/refman-5.1/replication-notes.xml trunk/pt/refman-5.1/stored-procedures.xml trunk/refman-5.0/replication-notes.xml trunk/refman-5.0/stored-procedures.xml trunk/refman-5.1/replication-notes.xml trunk/refman-5.1/stored-procedures.xml trunk/refman-6.0/replication-notes.xml trunk/refman-6.0/stored-procedures.xml Property changes on: trunk ___________________________________________________________________ Name: svk:merge - 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:35828 7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:32084 b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218 bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:31698 + 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:35828 7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:32088 b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218 bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:31698 Modified: trunk/it/refman-5.1/replication-notes.xml =================================================================== --- trunk/it/refman-5.1/replication-notes.xml 2008-06-17 14:33:06 UTC (rev 10969) +++ trunk/it/refman-5.1/replication-notes.xml 2008-06-17 15:56:29 UTC (rev 10970) Changed blocks: 2, Lines Added: 13, Lines Deleted: 0; 1304 bytes @@ -834,6 +834,11 @@ <listitem> <para> + For <function role="sql">NOW()</function>, the binary log + includes the timestamp and replicates correctly. + </para> + + <para> Unlike <function role="sql">NOW()</function>, the <function role="sql">SYSDATE()</function> function is not replication-safe because it is not affected by <literal>SET @@ -920,6 +925,14 @@ </programlisting> <para> + Within a stored function, <function role="sql">RAND()</function> + replicates correctly as long as it is invoked only once within + the function. (You can consider the function execution timestamp + and random number seed as implicit inputs that are identical on + the master and slave.) + </para> + + <para> The <function role="sql">FOUND_ROWS()</function> and <function role="sql">ROW_COUNT()</function> functions are not replicated reliably using statement-based replication. A Modified: trunk/it/refman-5.1/stored-procedures.xml =================================================================== --- trunk/it/refman-5.1/stored-procedures.xml 2008-06-17 14:33:06 UTC (rev 10969) +++ trunk/it/refman-5.1/stored-procedures.xml 2008-06-17 15:56:29 UTC (rev 10970) Changed blocks: 10, Lines Added: 65, Lines Deleted: 80; 11285 bytes @@ -2589,33 +2589,38 @@ <para> However, there are certain binary logging issues that apply with respect to stored routines (procedures and functions) and - triggers: + triggers, if logging occurs at the statement level: </para> <itemizedlist> <listitem> <para> - Logging occurs at the statement level. In some cases, it is - possible that a statement will affect different sets of rows - on a master and a slave. + In some cases, it is possible that a statement will affect + different sets of rows on a master and a slave. </para> </listitem> <listitem> <para> - For replication, statements executed on a slave are processed - by the slave SQL thread which has full privileges. It is - possible for a procedure to follow different execution paths - on master and slave servers, so although a user must have the - <literal>CREATE ROUTINE</literal> privilege to create a - routine, the user can write a routine containing a dangerous - statement that will execute only on the slave where the - statement is processed by the SQL thread that has full - privileges. + Replicated statements executed on a slave are processed by the + slave SQL thread, which has full privileges. It is possible + for a procedure to follow different execution paths on master + and slave servers, so a user can write a routine containing a + dangerous statement that will execute only on the slave where + it is processed by a thread that has full privileges. </para> </listitem> + <listitem> + <para> + If a routine that modifies data is non-deterministic, it is + not repeatable. This can result in different data on a master + and slave, or cause restored data to differ from the original + data. + </para> + </listitem> + </itemizedlist> <para> @@ -2630,20 +2635,16 @@ In general, the issues described here result when binary logging occurs at the SQL statement level. If you use row-based binary logging, the log contains changes made to individual rows as a - result of executing SQL statements. For general information about - row-based logging, see <xref linkend="replication-formats"/>. - </para> - - <para> - When using row-based logging, definitions of stored routines and - triggers are replicated as statements. When routines or triggers - execute, row changes are logged, not the statements that execute - them. For stored procedures, this means that the + result of executing SQL statements. When routines or triggers + execute, row changes are logged, not the statements that make the + changes. For stored procedures, this means that the <literal>CALL</literal> statement is not logged. For stored functions, row changes made within the function are logged, not the function invocation. For triggers, row changes made by the trigger are logged. On the slave side, only the row changes are - seen, not the routine or trigger invocation. + seen, not the routine or trigger invocation. For general + information about row-based logging, see + <xref linkend="replication-formats"/>. </para> <para> @@ -2682,9 +2683,10 @@ <para> By default, for a <literal>CREATE FUNCTION</literal> statement - to be accepted, <literal>DETERMINISTIC</literal>, or one of - <literal>NO SQL</literal> or <literal>READS SQL DATA</literal> - must be specified explicitly. Otherwise an error occurs: + to be accepted, at least one of + <literal>DETERMINISTIC</literal>, <literal>NO SQL</literal>, + or <literal>READS SQL DATA</literal> must be specified + explicitly. Otherwise an error occurs: </para> <programlisting> @@ -2710,7 +2712,9 @@ </programlisting> <para> - This function is not deterministic, so it may not be safe: + This function uses <function role="sql">UUID()</function>, + which is not deterministic, so the function also is not + deterministic and it may not be safe: </para> <programlisting> @@ -2774,6 +2778,12 @@ </itemizedlist> <para> + For information about built-in functions that may be unsafe for + replication (and thus cause stored functions that use them to be + unsafe as well), see <xref linkend="replication-features"/>. + </para> + + <para> Triggers are similar to stored functions, so the preceding remarks regarding functions also apply to triggers with the following exception: <literal>CREATE TRIGGER</literal> does not have an @@ -2796,9 +2806,11 @@ <para> The rest of this section provides additional detail about the - logging implementation and its implications. This discussion - applies only for statement-based logging, and not for row-based - logging, with the exception of the first item: + logging implementation and its implications. You need not read it + unless you are interested in the background on the rationale for + the current logging-related conditions on stored routine use. This + discussion applies only for statement-based logging, and not for + row-based logging, with the exception of the first item: <literal>CREATE</literal> and <literal>DROP</literal> statements are logged as statements regardless of the logging mode. </para> @@ -2910,12 +2922,12 @@ The implication is that although a user must have the <literal>CREATE ROUTINE</literal> privilege to create a function, the user can write a function containing a dangerous - statement that will execute only on the slave where the - statement is processed by the SQL thread that has full - privileges. For example, if the master and slave servers have - server ID values of 1 and 2, respectively, a user on the - master server could create and invoke an unsafe function - <literal>unsafe_func()</literal> as follows: + statement that will execute only on the slave where it is + processed by a thread that has full privileges. For example, + if the master and slave servers have server ID values of 1 and + 2, respectively, a user on the master server could create and + invoke an unsafe function <literal>unsafe_func()</literal> as + follows: </para> <programlisting> @@ -3013,31 +3025,6 @@ function is deterministic, you must specify <literal>DETERMINISTIC</literal> explicitly. </para> - - <para> - Use of the <function role="sql">NOW()</function> function - (or its synonyms) or - <function role="sql">RAND()</function> does not - necessarily make a function non-deterministic. For - <function role="sql">NOW()</function>, the binary log - includes the timestamp and replicates correctly. - <function role="sql">RAND()</function> also replicates - correctly as long as it is invoked only once within a - function. (You can consider the function execution - timestamp and random number seed as implicit inputs that - are identical on the master and slave.) - </para> - - <para> - <function role="sql">SYSDATE()</function> is not affected - by the timestamps in the binary log, so it causes stored - routines to be non-deterministic if statement-based - logging is used. This does not occur if row-based logging - is used, or if the server is started with the - <option>--sysdate-is-now</option> option to cause - <function role="sql">SYSDATE()</function> to be an alias - for <function role="sql">NOW()</function>. - </para> </listitem> <listitem> @@ -3058,9 +3045,10 @@ <para> By default, for a <literal>CREATE FUNCTION</literal> statement - to be accepted, <literal>DETERMINISTIC</literal>, or one of - <literal>NO SQL</literal> or <literal>READS SQL DATA</literal> - must be specified explicitly. Otherwise an error occurs: + to be accepted, at least one of + <literal>DETERMINISTIC</literal>, <literal>NO SQL</literal>, + or <literal>READS SQL DATA</literal> must be specified + explicitly. Otherwise an error occurs: </para> <programlisting> @@ -3164,22 +3152,19 @@ <listitem> <para> Procedure calls can occur within a committed or - rolled-back transaction. Previously, - <literal>CALL</literal> statements were logged even if - they occurred within a rolled-back transaction. As of - MySQL 5.0.12, transactional context is accounted for so - that the transactional aspects of procedure execution are - replicated correctly. That is, the server logs those - statements within the procedure that actually execute and - modify data, and also logs <literal>BEGIN</literal>, - <literal>COMMIT</literal>, and <literal>ROLLBACK</literal> - statements as necessary. For example, if a procedure - updates only transactional tables and is executed within a - transaction that is rolled back, those updates are not - logged. If the procedure occurs within a committed - transaction, <literal>BEGIN</literal> and - <literal>COMMIT</literal> statements are logged with the - updates. For a procedure that executes within a + rolled-back transaction. Transactional context is + accounted for so that the transactional aspects of + procedure execution are replicated correctly. That is, the + server logs those statements within the procedure that + actually execute and modify data, and also logs + <literal>BEGIN</literal>, <literal>COMMIT</literal>, and + <literal>ROLLBACK</literal> statements as necessary. For + example, if a procedure updates only transactional tables + and is executed within a transaction that is rolled back, + those updates are not logged. If the procedure occurs + within a committed transaction, <literal>BEGIN</literal> + and <literal>COMMIT</literal> statements are logged with + the updates. For a procedure that executes within a rolled-back transaction, its statements are logged using the same rules that would apply if the statements were executed in standalone fashion: Modified: trunk/pt/refman-5.1/replication-notes.xml =================================================================== --- trunk/pt/refman-5.1/replication-notes.xml 2008-06-17 14:33:06 UTC (rev 10969) +++ trunk/pt/refman-5.1/replication-notes.xml 2008-06-17 15:56:29 UTC (rev 10970) Changed blocks: 2, Lines Added: 13, Lines Deleted: 0; 1304 bytes @@ -834,6 +834,11 @@ <listitem> <para> + For <function role="sql">NOW()</function>, the binary log + includes the timestamp and replicates correctly. + </para> + + <para> Unlike <function role="sql">NOW()</function>, the <function role="sql">SYSDATE()</function> function is not replication-safe because it is not affected by <literal>SET @@ -920,6 +925,14 @@ </programlisting> <para> + Within a stored function, <function role="sql">RAND()</function> + replicates correctly as long as it is invoked only once within + the function. (You can consider the function execution timestamp + and random number seed as implicit inputs that are identical on + the master and slave.) + </para> + + <para> The <function role="sql">FOUND_ROWS()</function> and <function role="sql">ROW_COUNT()</function> functions are not replicated reliably using statement-based replication. A Modified: trunk/pt/refman-5.1/stored-procedures.xml =================================================================== --- trunk/pt/refman-5.1/stored-procedures.xml 2008-06-17 14:33:06 UTC (rev 10969) +++ trunk/pt/refman-5.1/stored-procedures.xml 2008-06-17 15:56:29 UTC (rev 10970) Changed blocks: 10, Lines Added: 65, Lines Deleted: 80; 11285 bytes @@ -2589,33 +2589,38 @@ <para> However, there are certain binary logging issues that apply with respect to stored routines (procedures and functions) and - triggers: + triggers, if logging occurs at the statement level: </para> <itemizedlist> <listitem> <para> - Logging occurs at the statement level. In some cases, it is - possible that a statement will affect different sets of rows - on a master and a slave. + In some cases, it is possible that a statement will affect + different sets of rows on a master and a slave. </para> </listitem> <listitem> <para> - For replication, statements executed on a slave are processed - by the slave SQL thread which has full privileges. It is - possible for a procedure to follow different execution paths - on master and slave servers, so although a user must have the - <literal>CREATE ROUTINE</literal> privilege to create a - routine, the user can write a routine containing a dangerous - statement that will execute only on the slave where the - statement is processed by the SQL thread that has full - privileges. + Replicated statements executed on a slave are processed by the + slave SQL thread, which has full privileges. It is possible + for a procedure to follow different execution paths on master + and slave servers, so a user can write a routine containing a + dangerous statement that will execute only on the slave where + it is processed by a thread that has full privileges. </para> </listitem> + <listitem> + <para> + If a routine that modifies data is non-deterministic, it is + not repeatable. This can result in different data on a master + and slave, or cause restored data to differ from the original + data. + </para> + </listitem> + </itemizedlist> <para> @@ -2630,20 +2635,16 @@ In general, the issues described here result when binary logging occurs at the SQL statement level. If you use row-based binary logging, the log contains changes made to individual rows as a - result of executing SQL statements. For general information about - row-based logging, see <xref linkend="replication-formats"/>. - </para> - - <para> - When using row-based logging, definitions of stored routines and - triggers are replicated as statements. When routines or triggers - execute, row changes are logged, not the statements that execute - them. For stored procedures, this means that the + result of executing SQL statements. When routines or triggers + execute, row changes are logged, not the statements that make the + changes. For stored procedures, this means that the <literal>CALL</literal> statement is not logged. For stored functions, row changes made within the function are logged, not the function invocation. For triggers, row changes made by the trigger are logged. On the slave side, only the row changes are - seen, not the routine or trigger invocation. + seen, not the routine or trigger invocation. For general + information about row-based logging, see + <xref linkend="replication-formats"/>. </para> <para> @@ -2682,9 +2683,10 @@ <para> By default, for a <literal>CREATE FUNCTION</literal> statement - to be accepted, <literal>DETERMINISTIC</literal>, or one of - <literal>NO SQL</literal> or <literal>READS SQL DATA</literal> - must be specified explicitly. Otherwise an error occurs: + to be accepted, at least one of + <literal>DETERMINISTIC</literal>, <literal>NO SQL</literal>, + or <literal>READS SQL DATA</literal> must be specified + explicitly. Otherwise an error occurs: </para> <programlisting> @@ -2710,7 +2712,9 @@ </programlisting> <para> - This function is not deterministic, so it may not be safe: + This function uses <function role="sql">UUID()</function>, + which is not deterministic, so the function also is not + deterministic and it may not be safe: </para> <programlisting> @@ -2774,6 +2778,12 @@ </itemizedlist> <para> + For information about built-in functions that may be unsafe for + replication (and thus cause stored functions that use them to be + unsafe as well), see <xref linkend="replication-features"/>. + </para> + + <para> Triggers are similar to stored functions, so the preceding remarks regarding functions also apply to triggers with the following exception: <literal>CREATE TRIGGER</literal> does not have an @@ -2796,9 +2806,11 @@ <para> The rest of this section provides additional detail about the - logging implementation and its implications. This discussion - applies only for statement-based logging, and not for row-based - logging, with the exception of the first item: + logging implementation and its implications. You need not read it + unless you are interested in the background on the rationale for + the current logging-related conditions on stored routine use. This + discussion applies only for statement-based logging, and not for + row-based logging, with the exception of the first item: <literal>CREATE</literal> and <literal>DROP</literal> statements are logged as statements regardless of the logging mode. </para> @@ -2910,12 +2922,12 @@ The implication is that although a user must have the <literal>CREATE ROUTINE</literal> privilege to create a function, the user can write a function containing a dangerous - statement that will execute only on the slave where the - statement is processed by the SQL thread that has full - privileges. For example, if the master and slave servers have - server ID values of 1 and 2, respectively, a user on the - master server could create and invoke an unsafe function - <literal>unsafe_func()</literal> as follows: + statement that will execute only on the slave where it is + processed by a thread that has full privileges. For example, + if the master and slave servers have server ID values of 1 and + 2, respectively, a user on the master server could create and + invoke an unsafe function <literal>unsafe_func()</literal> as + follows: </para> <programlisting> @@ -3013,31 +3025,6 @@ function is deterministic, you must specify <literal>DETERMINISTIC</literal> explicitly. </para> - - <para> - Use of the <function role="sql">NOW()</function> function - (or its synonyms) or - <function role="sql">RAND()</function> does not - necessarily make a function non-deterministic. For - <function role="sql">NOW()</function>, the binary log - includes the timestamp and replicates correctly. - <function role="sql">RAND()</function> also replicates - correctly as long as it is invoked only once within a - function. (You can consider the function execution - timestamp and random number seed as implicit inputs that - are identical on the master and slave.) - </para> - - <para> - <function role="sql">SYSDATE()</function> is not affected - by the timestamps in the binary log, so it causes stored - routines to be non-deterministic if statement-based - logging is used. This does not occur if row-based logging - is used, or if the server is started with the - <option>--sysdate-is-now</option> option to cause - <function role="sql">SYSDATE()</function> to be an alias - for <function role="sql">NOW()</function>. - </para> </listitem> <listitem> @@ -3058,9 +3045,10 @@ <para> By default, for a <literal>CREATE FUNCTION</literal> statement - to be accepted, <literal>DETERMINISTIC</literal>, or one of - <literal>NO SQL</literal> or <literal>READS SQL DATA</literal> - must be specified explicitly. Otherwise an error occurs: + to be accepted, at least one of + <literal>DETERMINISTIC</literal>, <literal>NO SQL</literal>, + or <literal>READS SQL DATA</literal> must be specified + explicitly. Otherwise an error occurs: </para> <programlisting> @@ -3164,22 +3152,19 @@ <listitem> <para> Procedure calls can occur within a committed or - rolled-back transaction. Previously, - <literal>CALL</literal> statements were logged even if - they occurred within a rolled-back transaction. As of - MySQL 5.0.12, transactional context is accounted for so - that the transactional aspects of procedure execution are - replicated correctly. That is, the server logs those - statements within the procedure that actually execute and - modify data, and also logs <literal>BEGIN</literal>, - <literal>COMMIT</literal>, and <literal>ROLLBACK</literal> - statements as necessary. For example, if a procedure - updates only transactional tables and is executed within a - transaction that is rolled back, those updates are not - logged. If the procedure occurs within a committed - transaction, <literal>BEGIN</literal> and - <literal>COMMIT</literal> statements are logged with the - updates. For a procedure that executes within a + rolled-back transaction. Transactional context is + accounted for so that the transactional aspects of + procedure execution are replicated correctly. That is, the + server logs those statements within the procedure that + actually execute and modify data, and also logs + <literal>BEGIN</literal>, <literal>COMMIT</literal>, and + <literal>ROLLBACK</literal> statements as necessary. For + example, if a procedure updates only transactional tables + and is executed within a transaction that is rolled back, + those updates are not logged. If the procedure occurs + within a committed transaction, <literal>BEGIN</literal> + and <literal>COMMIT</literal> statements are logged with + the updates. For a procedure that executes within a rolled-back transaction, its statements are logged using the same rules that would apply if the statements were executed in standalone fashion: Modified: trunk/refman-5.0/replication-notes.xml =================================================================== --- trunk/refman-5.0/replication-notes.xml 2008-06-17 14:33:06 UTC (rev 10969) +++ trunk/refman-5.0/replication-notes.xml 2008-06-17 15:56:29 UTC (rev 10970) Changed blocks: 2, Lines Added: 13, Lines Deleted: 0; 1272 bytes @@ -385,6 +385,11 @@ <listitem> <para> + For <function role="sql">NOW()</function>, the binary log + includes the timestamp and replicates correctly. + </para> + + <para> As of MySQL 5.0.13, the <function role="sql">SYSDATE()</function> function is no longer equivalent to <function role="sql">NOW()</function>. @@ -480,6 +485,14 @@ </para> <para> + Within a stored function, <function role="sql">RAND()</function> + replicates correctly as long as it is invoked only once within + the function. (You can consider the function execution timestamp + and random number seed as implicit inputs that are identical on + the master and slave.) + </para> + + <para> The <function role="sql">FOUND_ROWS()</function> and <function role="sql">ROW_COUNT()</function> functions are also not replicated reliably. A workaround is to store the result of Modified: trunk/refman-5.0/stored-procedures.xml =================================================================== --- trunk/refman-5.0/stored-procedures.xml 2008-06-17 14:33:06 UTC (rev 10969) +++ trunk/refman-5.0/stored-procedures.xml 2008-06-17 15:56:29 UTC (rev 10970) Changed blocks: 8, Lines Added: 37, Lines Deleted: 46; 6441 bytes @@ -2645,18 +2645,24 @@ <listitem> <para> - For replication, statements executed on a slave are processed - by the slave SQL thread which has full privileges. It is - possible for a procedure to follow different execution paths - on master and slave servers, so although a user must have the - <literal>CREATE ROUTINE</literal> privilege to create a - routine, the user can write a routine containing a dangerous - statement that will execute only on the slave where the - statement is processed by the SQL thread that has full - privileges. + Replicated statements executed on a slave are processed by the + slave SQL thread, which has full privileges. It is possible + for a procedure to follow different execution paths on master + and slave servers, so a user can write a routine containing a + dangerous statement that will execute only on the slave where + it is processed by a thread that has full privileges. </para> </listitem> + <listitem> + <para> + If a routine that modifies data is non-deterministic, it is + not repeatable. This can result in different data on a master + and slave, or cause restored data to differ from the original + data. + </para> + </listitem> + </itemizedlist> <para> @@ -2669,7 +2675,7 @@ that provide information about when and why various changes were made. These details show how several aspects of the current logging behavior were implemented in response to shortcomings - identified in earlier versions. + identified in earlier versions of MySQL. </para> <para> @@ -2716,9 +2722,10 @@ <para> By default, for a <literal>CREATE FUNCTION</literal> statement - to be accepted, <literal>DETERMINISTIC</literal>, or one of - <literal>NO SQL</literal> or <literal>READS SQL DATA</literal> - must be specified explicitly. Otherwise an error occurs: + to be accepted, at least one of + <literal>DETERMINISTIC</literal>, <literal>NO SQL</literal>, + or <literal>READS SQL DATA</literal> must be specified + explicitly. Otherwise an error occurs: </para> <programlisting> @@ -2744,7 +2751,9 @@ </programlisting> <para> - This function is not deterministic, so it may not be safe: + This function uses <function role="sql">UUID()</function>, + which is not deterministic, so the function also is not + deterministic and it may not be safe: </para> <programlisting> @@ -2808,6 +2817,12 @@ </itemizedlist> <para> + For information about built-in functions that may be unsafe for + replication (and thus cause stored functions that use them to be + unsafe as well), see <xref linkend="replication-features"/>. + </para> + + <para> Triggers are similar to stored functions, so the preceding remarks regarding functions also apply to triggers with the following exception: <literal>CREATE TRIGGER</literal> does not have an @@ -2999,12 +3014,12 @@ The implication is that although a user must have the <literal>CREATE ROUTINE</literal> privilege to create a routine, the user can write a routine containing a dangerous - statement that will execute only on the slave where the - statement is processed by the SQL thread that has full - privileges. For example, if the master and slave servers have - server ID values of 1 and 2, respectively, a user on the - master server could create and invoke an unsafe procedure - <literal>unsafe_sp()</literal> as follows: + statement that will execute only on the slave where it is + processed by a thread that has full privileges. For example, + if the master and slave servers have server ID values of 1 and + 2, respectively, a user on the master server could create and + invoke an unsafe procedure <literal>unsafe_sp()</literal> as + follows: </para> <programlisting> @@ -3112,30 +3127,6 @@ is deterministic, you must specify <literal>DETERMINISTIC</literal> explicitly. </para> - - <para> - Use of the <function role="sql">NOW()</function> function - (or its synonyms) or - <function role="sql">RAND()</function> does not - necessarily make a function non-deterministic. For - <function role="sql">NOW()</function>, the binary log - includes the timestamp and replicates correctly. - <function role="sql">RAND()</function> also replicates - correctly as long as it is invoked only once within a - function. (You can consider the function execution - timestamp and random number seed as implicit inputs that - are identical on the master and slave.) - </para> - - <para> - <function role="sql">SYSDATE()</function> is not affected - by the timestamps in the binary log, so it causes stored - routines to be non-deterministic if statement-based - logging is used. This does not occur if the server is - started with the <option>--sysdate-is-now</option> option - to cause <function role="sql">SYSDATE()</function> to be - an alias for <function role="sql">NOW()</function>. - </para> </listitem> <listitem> @@ -3157,8 +3148,8 @@ <para> By default, for a <literal>CREATE PROCEDURE</literal> or <literal>CREATE FUNCTION</literal> statement to be accepted, - <literal>DETERMINISTIC</literal>, or one of <literal>NO - SQL</literal> or <literal>READS SQL DATA</literal> must be + at least one of <literal>DETERMINISTIC</literal>, <literal>NO + SQL</literal>, or <literal>READS SQL DATA</literal> must be specified explicitly. Otherwise an error occurs: </para> Modified: trunk/refman-5.1/replication-notes.xml =================================================================== --- trunk/refman-5.1/replication-notes.xml 2008-06-17 14:33:06 UTC (rev 10969) +++ trunk/refman-5.1/replication-notes.xml 2008-06-17 15:56:29 UTC (rev 10970) Changed blocks: 2, Lines Added: 13, Lines Deleted: 0; 1295 bytes @@ -834,6 +834,11 @@ <listitem> <para> + For <function role="sql">NOW()</function>, the binary log + includes the timestamp and replicates correctly. + </para> + + <para> Unlike <function role="sql">NOW()</function>, the <function role="sql">SYSDATE()</function> function is not replication-safe because it is not affected by <literal>SET @@ -920,6 +925,14 @@ </programlisting> <para> + Within a stored function, <function role="sql">RAND()</function> + replicates correctly as long as it is invoked only once within + the function. (You can consider the function execution timestamp + and random number seed as implicit inputs that are identical on + the master and slave.) + </para> + + <para> The <function role="sql">FOUND_ROWS()</function> and <function role="sql">ROW_COUNT()</function> functions are not replicated reliably using statement-based replication. A Modified: trunk/refman-5.1/stored-procedures.xml =================================================================== --- trunk/refman-5.1/stored-procedures.xml 2008-06-17 14:33:06 UTC (rev 10969) +++ trunk/refman-5.1/stored-procedures.xml 2008-06-17 15:56:29 UTC (rev 10970) Changed blocks: 10, Lines Added: 65, Lines Deleted: 80; 11276 bytes @@ -2589,33 +2589,38 @@ <para> However, there are certain binary logging issues that apply with respect to stored routines (procedures and functions) and - triggers: + triggers, if logging occurs at the statement level: </para> <itemizedlist> <listitem> <para> - Logging occurs at the statement level. In some cases, it is - possible that a statement will affect different sets of rows - on a master and a slave. + In some cases, it is possible that a statement will affect + different sets of rows on a master and a slave. </para> </listitem> <listitem> <para> - For replication, statements executed on a slave are processed - by the slave SQL thread which has full privileges. It is - possible for a procedure to follow different execution paths - on master and slave servers, so although a user must have the - <literal>CREATE ROUTINE</literal> privilege to create a - routine, the user can write a routine containing a dangerous - statement that will execute only on the slave where the - statement is processed by the SQL thread that has full - privileges. + Replicated statements executed on a slave are processed by the + slave SQL thread, which has full privileges. It is possible + for a procedure to follow different execution paths on master + and slave servers, so a user can write a routine containing a + dangerous statement that will execute only on the slave where + it is processed by a thread that has full privileges. </para> </listitem> + <listitem> + <para> + If a routine that modifies data is non-deterministic, it is + not repeatable. This can result in different data on a master + and slave, or cause restored data to differ from the original + data. + </para> + </listitem> + </itemizedlist> <para> @@ -2630,20 +2635,16 @@ In general, the issues described here result when binary logging occurs at the SQL statement level. If you use row-based binary logging, the log contains changes made to individual rows as a - result of executing SQL statements. For general information about - row-based logging, see <xref linkend="replication-formats"/>. - </para> - - <para> - When using row-based logging, definitions of stored routines and - triggers are replicated as statements. When routines or triggers - execute, row changes are logged, not the statements that execute - them. For stored procedures, this means that the + result of executing SQL statements. When routines or triggers + execute, row changes are logged, not the statements that make the + changes. For stored procedures, this means that the <literal>CALL</literal> statement is not logged. For stored functions, row changes made within the function are logged, not the function invocation. For triggers, row changes made by the trigger are logged. On the slave side, only the row changes are - seen, not the routine or trigger invocation. + seen, not the routine or trigger invocation. For general + information about row-based logging, see + <xref linkend="replication-formats"/>. </para> <para> @@ -2682,9 +2683,10 @@ <para> By default, for a <literal>CREATE FUNCTION</literal> statement - to be accepted, <literal>DETERMINISTIC</literal>, or one of - <literal>NO SQL</literal> or <literal>READS SQL DATA</literal> - must be specified explicitly. Otherwise an error occurs: + to be accepted, at least one of + <literal>DETERMINISTIC</literal>, <literal>NO SQL</literal>, + or <literal>READS SQL DATA</literal> must be specified + explicitly. Otherwise an error occurs: </para> <programlisting> @@ -2710,7 +2712,9 @@ </programlisting> <para> - This function is not deterministic, so it may not be safe: + This function uses <function role="sql">UUID()</function>, + which is not deterministic, so the function also is not + deterministic and it may not be safe: </para> <programlisting> @@ -2774,6 +2778,12 @@ </itemizedlist> <para> + For information about built-in functions that may be unsafe for + replication (and thus cause stored functions that use them to be + unsafe as well), see <xref linkend="replication-features"/>. + </para> + + <para> Triggers are similar to stored functions, so the preceding remarks regarding functions also apply to triggers with the following exception: <literal>CREATE TRIGGER</literal> does not have an @@ -2796,9 +2806,11 @@ <para> The rest of this section provides additional detail about the - logging implementation and its implications. This discussion - applies only for statement-based logging, and not for row-based - logging, with the exception of the first item: + logging implementation and its implications. You need not read it + unless you are interested in the background on the rationale for + the current logging-related conditions on stored routine use. This + discussion applies only for statement-based logging, and not for + row-based logging, with the exception of the first item: <literal>CREATE</literal> and <literal>DROP</literal> statements are logged as statements regardless of the logging mode. </para> @@ -2910,12 +2922,12 @@ The implication is that although a user must have the <literal>CREATE ROUTINE</literal> privilege to create a function, the user can write a function containing a dangerous - statement that will execute only on the slave where the - statement is processed by the SQL thread that has full - privileges. For example, if the master and slave servers have - server ID values of 1 and 2, respectively, a user on the - master server could create and invoke an unsafe function - <literal>unsafe_func()</literal> as follows: + statement that will execute only on the slave where it is + processed by a thread that has full privileges. For example, + if the master and slave servers have server ID values of 1 and + 2, respectively, a user on the master server could create and + invoke an unsafe function <literal>unsafe_func()</literal> as + follows: </para> <programlisting> @@ -3013,31 +3025,6 @@ function is deterministic, you must specify <literal>DETERMINISTIC</literal> explicitly. </para> - - <para> - Use of the <function role="sql">NOW()</function> function - (or its synonyms) or - <function role="sql">RAND()</function> does not - necessarily make a function non-deterministic. For - <function role="sql">NOW()</function>, the binary log - includes the timestamp and replicates correctly. - <function role="sql">RAND()</function> also replicates - correctly as long as it is invoked only once within a - function. (You can consider the function execution - timestamp and random number seed as implicit inputs that - are identical on the master and slave.) - </para> - - <para> - <function role="sql">SYSDATE()</function> is not affected - by the timestamps in the binary log, so it causes stored - routines to be non-deterministic if statement-based - logging is used. This does not occur if row-based logging - is used, or if the server is started with the - <option>--sysdate-is-now</option> option to cause - <function role="sql">SYSDATE()</function> to be an alias - for <function role="sql">NOW()</function>. - </para> </listitem> <listitem> @@ -3058,9 +3045,10 @@ <para> By default, for a <literal>CREATE FUNCTION</literal> statement - to be accepted, <literal>DETERMINISTIC</literal>, or one of - <literal>NO SQL</literal> or <literal>READS SQL DATA</literal> - must be specified explicitly. Otherwise an error occurs: + to be accepted, at least one of + <literal>DETERMINISTIC</literal>, <literal>NO SQL</literal>, + or <literal>READS SQL DATA</literal> must be specified + explicitly. Otherwise an error occurs: </para> <programlisting> @@ -3164,22 +3152,19 @@ <listitem> <para> Procedure calls can occur within a committed or - rolled-back transaction. Previously, - <literal>CALL</literal> statements were logged even if - they occurred within a rolled-back transaction. As of - MySQL 5.0.12, transactional context is accounted for so - that the transactional aspects of procedure execution are - replicated correctly. That is, the server logs those - statements within the procedure that actually execute and - modify data, and also logs <literal>BEGIN</literal>, - <literal>COMMIT</literal>, and <literal>ROLLBACK</literal> - statements as necessary. For example, if a procedure - updates only transactional tables and is executed within a - transaction that is rolled back, those updates are not - logged. If the procedure occurs within a committed - transaction, <literal>BEGIN</literal> and - <literal>COMMIT</literal> statements are logged with the - updates. For a procedure that executes within a + rolled-back transaction. Transactional context is + accounted for so that the transactional aspects of + procedure execution are replicated correctly. That is, the + server logs those statements within the procedure that + actually execute and modify data, and also logs + <literal>BEGIN</literal>, <literal>COMMIT</literal>, and + <literal>ROLLBACK</literal> statements as necessary. For + example, if a procedure updates only transactional tables + and is executed within a transaction that is rolled back, + those updates are not logged. If the procedure occurs + within a committed transaction, <literal>BEGIN</literal> + and <literal>COMMIT</literal> statements are logged with + the updates. For a procedure that executes within a rolled-back transaction, its statements are logged using the same rules that would apply if the statements were executed in standalone fashion: Modified: trunk/refman-6.0/replication-notes.xml =================================================================== --- trunk/refman-6.0/replication-notes.xml 2008-06-17 14:33:06 UTC (rev 10969) +++ trunk/refman-6.0/replication-notes.xml 2008-06-17 15:56:29 UTC (rev 10970) Changed blocks: 2, Lines Added: 13, Lines Deleted: 0; 1295 bytes @@ -803,6 +803,11 @@ <listitem> <para> + For <function role="sql">NOW()</function>, the binary log + includes the timestamp and replicates correctly. + </para> + + <para> Unlike <function role="sql">NOW()</function>, the <function role="sql">SYSDATE()</function> function is not replication-safe because it is not affected by <literal>SET @@ -889,6 +894,14 @@ </programlisting> <para> + Within a stored function, <function role="sql">RAND()</function> + replicates correctly as long as it is invoked only once within + the function. (You can consider the function execution timestamp + and random number seed as implicit inputs that are identical on + the master and slave.) + </para> + + <para> The <function role="sql">FOUND_ROWS()</function> and <function role="sql">ROW_COUNT()</function> functions are not replicated reliably using statement-based replication. A Modified: trunk/refman-6.0/stored-procedures.xml =================================================================== --- trunk/refman-6.0/stored-procedures.xml 2008-06-17 14:33:06 UTC (rev 10969) +++ trunk/refman-6.0/stored-procedures.xml 2008-06-17 15:56:29 UTC (rev 10970) Changed blocks: 9, Lines Added: 52, Lines Deleted: 64; 8998 bytes @@ -2587,33 +2587,38 @@ <para> However, there are certain binary logging issues that apply with respect to stored routines (procedures and functions) and - triggers: + triggers, if logging occurs at the statement level: </para> <itemizedlist> <listitem> <para> - Logging occurs at the statement level. In some cases, it is - possible that a statement will affect different sets of rows - on a master and a slave. + In some cases, it is possible that a statement will affect + different sets of rows on a master and a slave. </para> </listitem> <listitem> <para> - For replication, statements executed on a slave are processed - by the slave SQL thread which has full privileges. It is - possible for a procedure to follow different execution paths - on master and slave servers, so although a user must have the - <literal>CREATE ROUTINE</literal> privilege to create a - routine, the user can write a routine containing a dangerous - statement that will execute only on the slave where the - statement is processed by the SQL thread that has full - privileges. + Replicated statements executed on a slave are processed by the + slave SQL thread, which has full privileges. It is possible + for a procedure to follow different execution paths on master + and slave servers, so a user can write a routine containing a + dangerous statement that will execute only on the slave where + it is processed by a thread that has full privileges. </para> </listitem> + <listitem> + <para> + If a routine that modifies data is non-deterministic, it is + not repeatable. This can result in different data on a master + and slave, or cause restored data to differ from the original + data. + </para> + </listitem> + </itemizedlist> <para> @@ -2628,20 +2633,16 @@ In general, the issues described here result when binary logging occurs at the SQL statement level. If you use row-based binary logging, the log contains changes made to individual rows as a - result of executing SQL statements. For general information about - row-based logging, see <xref linkend="replication-formats"/>. - </para> - - <para> - When using row-based logging, definitions of stored routines and - triggers are replicated as statements. When routines or triggers - execute, row changes are logged, not the statements that execute - them. For stored procedures, this means that the + result of executing SQL statements. When routines or triggers + execute, row changes are logged, not the statements that make the + changes. For stored procedures, this means that the <literal>CALL</literal> statement is not logged. For stored functions, row changes made within the function are logged, not the function invocation. For triggers, row changes made by the trigger are logged. On the slave side, only the row changes are - seen, not the routine or trigger invocation. + seen, not the routine or trigger invocation. For general + information about row-based logging, see + <xref linkend="replication-formats"/>. </para> <para> @@ -2680,9 +2681,10 @@ <para> By default, for a <literal>CREATE FUNCTION</literal> statement - to be accepted, <literal>DETERMINISTIC</literal>, or one of - <literal>NO SQL</literal> or <literal>READS SQL DATA</literal> - must be specified explicitly. Otherwise an error occurs: + to be accepted, at least one of + <literal>DETERMINISTIC</literal>, <literal>NO SQL</literal>, + or <literal>READS SQL DATA</literal> must be specified + explicitly. Otherwise an error occurs: </para> <programlisting> @@ -2708,7 +2710,9 @@ </programlisting> <para> - This function is not deterministic, so it may not be safe: + This function uses <function role="sql">UUID()</function>, + which is not deterministic, so the function also is not + deterministic and it may not be safe: </para> <programlisting> @@ -2772,6 +2776,12 @@ </itemizedlist> <para> + For information about built-in functions that may be unsafe for + replication (and thus cause stored functions that use them to be + unsafe as well), see <xref linkend="replication-features"/>. + </para> + + <para> Triggers are similar to stored functions, so the preceding remarks regarding functions also apply to triggers with the following exception: <literal>CREATE TRIGGER</literal> does not have an @@ -2794,9 +2804,11 @@ <para> The rest of this section provides additional detail about the - logging implementation and its implications. This discussion - applies only for statement-based logging, and not for row-based - logging, with the exception of the first item: + logging implementation and its implications. You need not read it + unless you are interested in the background on the rationale for + the current logging-related conditions on stored routine use. This + discussion applies only for statement-based logging, and not for + row-based logging, with the exception of the first item: <literal>CREATE</literal> and <literal>DROP</literal> statements are logged as statements regardless of the logging mode. </para> @@ -2898,12 +2910,12 @@ The implication is that although a user must have the <literal>CREATE ROUTINE</literal> privilege to create a function, the user can write a function containing a dangerous - statement that will execute only on the slave where the - statement is processed by the SQL thread that has full - privileges. For example, if the master and slave servers have - server ID values of 1 and 2, respectively, a user on the - master server could create and invoke an unsafe function - <literal>unsafe_func()</literal> as follows: + statement that will execute only on the slave where it is + processed by a thread that has full privileges. For example, + if the master and slave servers have server ID values of 1 and + 2, respectively, a user on the master server could create and + invoke an unsafe function <literal>unsafe_func()</literal> as + follows: </para> <programlisting> @@ -3001,31 +3013,6 @@ function is deterministic, you must specify <literal>DETERMINISTIC</literal> explicitly. </para> - - <para> - Use of the <function role="sql">NOW()</function> function - (or its synonyms) or - <function role="sql">RAND()</function> does not - necessarily make a function non-deterministic. For - <function role="sql">NOW()</function>, the binary log - includes the timestamp and replicates correctly. - <function role="sql">RAND()</function> also replicates - correctly as long as it is invoked only once within a - function. (You can consider the function execution - timestamp and random number seed as implicit inputs that - are identical on the master and slave.) - </para> - - <para> - <function role="sql">SYSDATE()</function> is not affected - by the timestamps in the binary log, so it causes stored - routines to be non-deterministic if statement-based - logging is used. This does not occur if row-based logging - is used, or if the server is started with the - <option>--sysdate-is-now</option> option to cause - <function role="sql">SYSDATE()</function> to be an alias - for <function role="sql">NOW()</function>. - </para> </listitem> <listitem> @@ -3046,9 +3033,10 @@ <para> By default, for a <literal>CREATE FUNCTION</literal> statement - to be accepted, <literal>DETERMINISTIC</literal>, or one of - <literal>NO SQL</literal> or <literal>READS SQL DATA</literal> - must be specified explicitly. Otherwise an error occurs: + to be accepted, at least one of + <literal>DETERMINISTIC</literal>, <literal>NO SQL</literal>, + or <literal>READS SQL DATA</literal> must be specified + explicitly. Otherwise an error occurs: </para> <programlisting>
| Thread | ||
|---|---|---|
| • svn commit - mysqldoc@docsrva: r10970 - in trunk: . it/refman-5.1 pt/refman-5.1 refman-5.0 refman-5.1 refman-6.0 | paul | 17 Jun |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 0.973 sec. using MySQL 5.1.14-beta-log