Author: paul
Date: 2006-04-12 22:30:42 +0200 (Wed, 12 Apr 2006)
New Revision: 1810
Log:
r9409@frost: paul | 2006-04-12 15:28:57 -0500
The 5.0.13 change to SYSDATE() to cause it to differ from NOW()
has implications for binary logging, stored routines, replication.
Requires changes to:
- NOW() and SYSDATE() function descriptions
- Server options (--sysdate-is-now is new)
- Replication known problems
- Stored routine logging
- Changelog sections
- Upgrading-to-5.0 notes
(Bug#15101)
Modified:
trunk/
trunk/refman-5.0/database-administration.xml
trunk/refman-5.0/functions.xml
trunk/refman-5.0/installing.xml
trunk/refman-5.0/replication.xml
trunk/refman-5.0/sql-syntax.xml
trunk/refman-5.0/stored-procedures.xml
trunk/refman-5.1/database-administration.xml
trunk/refman-5.1/functions.xml
trunk/refman-5.1/replication.xml
trunk/refman-5.1/sql-syntax.xml
trunk/refman-5.1/stored-procedures.xml
trunk/refman-common/news-5.0.xml
trunk/refman-common/news-5.1.xml
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:6598
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:9407
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:4334
+ 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:6598
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:9409
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:4334
Modified: trunk/refman-5.0/database-administration.xml
===================================================================
--- trunk/refman-5.0/database-administration.xml 2006-04-12 17:03:14 UTC (rev 1809)
+++ trunk/refman-5.0/database-administration.xml 2006-04-12 20:30:42 UTC (rev 1810)
@@ -2594,11 +2594,6 @@
</listitem>
<listitem>
- <remark role="todo">
- Some of these option values currently are unimplemented.
- Need to say which ones.
- </remark>
-
<para>
<indexterm>
<primary>mysqld</primary>
@@ -2622,6 +2617,41 @@
<para>
<indexterm>
<primary>mysqld</primary>
+ <secondary>sysdate-is-now option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>sysdate-is-now option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+ <option>--sysdate-is-now</option>
+ </para>
+
+ <para>
+ As of MySQL 5.0.13, <literal>SYSDATE()</literal> by
+ default returns the time at which it executes, not the
+ time at which the statement in which it occurs begins
+ executing. This differs from the behavior of
+ <literal>NOW()</literal>. This option causes
+ <literal>SYSDATE()</literal> to be an alias for
+ <literal>NOW()</literal>. For information about the
+ implications for binary logging and replication, see the
+ description for <literal>SYSDATE()</literal> in
+ <xref linkend="date-and-time-functions"/> and for
+ <literal>SET TIMESTAMP</literal> in
+ <xref linkend="set-option"/>.
+ </para>
+
+ <para>
+ This option was added in MySQL 5.0.20.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
<secondary>temp-pool option</secondary>
</indexterm>
Modified: trunk/refman-5.0/functions.xml
===================================================================
--- trunk/refman-5.0/functions.xml 2006-04-12 17:03:14 UTC (rev 1809)
+++ trunk/refman-5.0/functions.xml 2006-04-12 20:30:42 UTC (rev 1810)
@@ -7702,12 +7702,36 @@
</programlisting>
<para>
- Within a stored routine or trigger, <literal>NOW()</literal>
- returns a constant time that indicates the time at which the
- routine or triggering statement began to execute. This differs
- from the behavior for <literal>SYSDATE()</literal>, which
- returns the exact time at which it executes.
+ <literal>NOW()</literal> returns a constant time that
+ indicates the time at which the statement began to execute.
+ (Within a stored routine or trigger, <literal>NOW()</literal>
+ returns the time at which the routine or triggering statement
+ began to execute.) This differs from the behavior for
+ <literal>SYSDATE()</literal>, which returns the exact time at
+ which it executes as of MySQL 5.0.13.
</para>
+
+<programlisting>
+mysql> <userinput>SELECT NOW(), SLEEP(2), NOW();</userinput>
++---------------------+----------+---------------------+
+| NOW() | SLEEP(2) | NOW() |
++---------------------+----------+---------------------+
+| 2006-04-12 13:47:36 | 0 | 2006-04-12 13:47:36 |
++---------------------+----------+---------------------+
+
+mysql> <userinput>SELECT SYSDATE(), SLEEP(2), SYSDATE();</userinput>
++---------------------+----------+---------------------+
+| SYSDATE() | SLEEP(2) | SYSDATE() |
++---------------------+----------+---------------------+
+| 2006-04-12 13:47:44 | 0 | 2006-04-12 13:47:46 |
++---------------------+----------+---------------------+
+</programlisting>
+
+ <para>
+ See the description for <literal>SYSDATE()</literal> for
+ additional information about the differences between the two
+ functions.
+ </para>
</listitem>
<listitem>
@@ -8112,13 +8136,50 @@
</para>
<para>
- Within a stored routine or trigger,
- <literal>SYSDATE()</literal> returns the time at which it
- executes. This differs from the behavior for
- <literal>NOW()</literal>, which returns the the time at which
- the routine or triggering statement began to execute.
+ As of MySQL 5.0.13, <literal>SYSDATE()</literal> returns the
+ time at which it executes. This differs from the behavior for
+ <literal>NOW()</literal>, which returns a constant time that
+ indicates the time at which the statement began to execute.
+ (Within a stored routine or trigger, <literal>NOW()</literal>
+ returns the time at which the routine or triggering statement
+ began to execute.)
</para>
+<programlisting>
+mysql> <userinput>SELECT NOW(), SLEEP(2), NOW();</userinput>
++---------------------+----------+---------------------+
+| NOW() | SLEEP(2) | NOW() |
++---------------------+----------+---------------------+
+| 2006-04-12 13:47:36 | 0 | 2006-04-12 13:47:36 |
++---------------------+----------+---------------------+
+
+mysql> <userinput>SELECT SYSDATE(), SLEEP(2), SYSDATE();</userinput>
++---------------------+----------+---------------------+
+| SYSDATE() | SLEEP(2) | SYSDATE() |
++---------------------+----------+---------------------+
+| 2006-04-12 13:47:44 | 0 | 2006-04-12 13:47:46 |
++---------------------+----------+---------------------+
+</programlisting>
+
+ <para>
+ In addition, the <literal>SET TIMESTAMP</literal> statement
+ affects the value returned by <literal>NOW()</literal> but not
+ by <literal>SYSDATE()</literal>. This means that timestamp
+ settings in the binary log have no effect on invocations of
+ <literal>SYSDATE()</literal>.
+ </para>
+
+ <para>
+ Because <literal>SYSDATE()</literal> can return different
+ values even within the same statement, and is not affected by
+ <literal>SET TIMESTAMP</literal>, it is non-deterministic and
+ therefore unsafe for replication. If that is a problem, you
+ can start the server with the
+ <option>--sysdate-is-now</option> option to cause
+ <literal>SYSDATE()</literal> to be an alias for
+ <literal>NOW()</literal>.
+ </para>
+
<remark role="help-description-end"/>
</listitem>
Modified: trunk/refman-5.0/installing.xml
===================================================================
--- trunk/refman-5.0/installing.xml 2006-04-12 17:03:14 UTC (rev 1809)
+++ trunk/refman-5.0/installing.xml 2006-04-12 20:30:42 UTC (rev 1810)
@@ -12354,6 +12354,28 @@
<listitem>
<para>
<emphasis role="bold">Incompatible change:</emphasis> Before
+ MySQL 5.0.13, <literal>NOW()</literal> and
+ <literal>SYSDATE()</literal> return the same value (the time
+ at which the statement in which the function occurs begins
+ executing). As of MySQL 5.0.13, <literal>SYSDATE()</literal>
+ returns the time at which it it executes, which can differ
+ from the value returned by <literal>NOW()</literal>. For
+ information about the implications for binary logging and
+ replication, see the description for
+ <literal>SYSDATE()</literal> in
+ <xref linkend="date-and-time-functions"/> and for
+ <literal>SET TIMESTAMP</literal> in
+ <xref linkend="set-option"/>. To restore the former behavior
+ for <literal>SYSDATE()</literal> and cause it to be an alias
+ for <literal>NOW()</literal>, start the server with the
+ <option>--sysdate-is-now</option> option (available as of
+ MySQL 5.0.20).
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <emphasis role="bold">Incompatible change:</emphasis> Before
MySQL 5.0.13,
<literal>GREATEST(<replaceable>x</replaceable>,NULL)</literal>
and
Modified: trunk/refman-5.0/replication.xml
===================================================================
--- trunk/refman-5.0/replication.xml 2006-04-12 17:03:14 UTC (rev 1809)
+++ trunk/refman-5.0/replication.xml 2006-04-12 20:30:42 UTC (rev 1810)
@@ -1634,6 +1634,20 @@
<listitem>
<para>
+ As of MySQL 5.0.13, the <literal>SYSDATE()</literal> function
+ is no longer equivalent to <literal>NOW()</literal>.
+ Implications are that <literal>SYSDATE()</literal> is not
+ replication-safe because it is not affected by <literal>SET
+ TIMESTAMP</literal> statements in the binary log and is
+ non-deterministic. To avoid this, you can start the server
+ with the <option>--sysdate-is-now</option> option to cause
+ <literal>SYSDATE()</literal> to be an alias for
+ <literal>NOW()</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
User privileges are replicated only if the
<literal>mysql</literal> database is replicated. That is, the
<literal>GRANT</literal>, <literal>REVOKE</literal>,
Modified: trunk/refman-5.0/sql-syntax.xml
===================================================================
--- trunk/refman-5.0/sql-syntax.xml 2006-04-12 17:03:14 UTC (rev 1809)
+++ trunk/refman-5.0/sql-syntax.xml 2006-04-12 20:30:42 UTC (rev 1810)
@@ -14865,6 +14865,18 @@
rows. <literal>timestamp_value</literal> should be a Unix
epoch timestamp, not a MySQL timestamp.
</para>
+
+ <para>
+ <literal>SET TIMESTAMP</literal> affects the value returned
+ by <literal>NOW()</literal> but not by
+ <literal>SYSDATE()</literal>. This means that timestamp
+ settings in the binary log have no effect on invocations of
+ <literal>SYSDATE()</literal>. The server can be started with
+ the <option>--sysdate-is-now</option> option to cause
+ <literal>SYSDATE()</literal> to be an alias for
+ <literal>NOW()</literal>, in which case <literal>SET
+ TIMESTAMP</literal> affects both functions.
+ </para>
</listitem>
<!--
Modified: trunk/refman-5.0/stored-procedures.xml
===================================================================
--- trunk/refman-5.0/stored-procedures.xml 2006-04-12 17:03:14 UTC (rev 1809)
+++ trunk/refman-5.0/stored-procedures.xml 2006-04-12 20:30:42 UTC (rev 1810)
@@ -2463,6 +2463,16 @@
number seed as implicit inputs that are identical on the
master and slave.)
</para>
+
+ <para>
+ <literal>SYSDATE()</literal> 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
+ <literal>SYSDATE()</literal> to be an alias for
+ <literal>NOW()</literal>.
+ </para>
</listitem>
<listitem>
Modified: trunk/refman-5.1/database-administration.xml
===================================================================
--- trunk/refman-5.1/database-administration.xml 2006-04-12 17:03:14 UTC (rev 1809)
+++ trunk/refman-5.1/database-administration.xml 2006-04-12 20:30:42 UTC (rev 1810)
@@ -2855,11 +2855,6 @@
</listitem>
<listitem>
- <remark role="todo">
- Some of these option values currently are unimplemented.
- Need to say which ones.
- </remark>
-
<para>
<indexterm>
<primary>mysqld</primary>
@@ -2883,6 +2878,40 @@
<para>
<indexterm>
<primary>mysqld</primary>
+ <secondary>sysdate-is-now option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>sysdate-is-now option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+ <option>--sysdate-is-now</option>
+ </para>
+
+ <para>
+ <literal>SYSDATE()</literal> by default returns the time
+ at which it executes, not the time at which the statement
+ in which it occurs begins executing. This differs from the
+ behavior of <literal>NOW()</literal>. This option causes
+ <literal>SYSDATE()</literal> to be an alias for
+ <literal>NOW()</literal>. For information about the
+ implications for binary logging and replication, see the
+ description for <literal>SYSDATE()</literal> in
+ <xref linkend="date-and-time-functions"/> and for
+ <literal>SET TIMESTAMP</literal> in
+ <xref linkend="set-option"/>.
+ </para>
+
+ <para>
+ This option was added in MySQL 5.1.8.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
<secondary>temp-pool option</secondary>
</indexterm>
Modified: trunk/refman-5.1/functions.xml
===================================================================
--- trunk/refman-5.1/functions.xml 2006-04-12 17:03:14 UTC (rev 1809)
+++ trunk/refman-5.1/functions.xml 2006-04-12 20:30:42 UTC (rev 1810)
@@ -7656,12 +7656,36 @@
</programlisting>
<para>
- Within a stored routine or trigger, <literal>NOW()</literal>
- returns a constant time that indicates the time at which the
- routine or triggering statement began to execute. This differs
- from the behavior for <literal>SYSDATE()</literal>, which
- returns the exact time at which it executes.
+ <literal>NOW()</literal> returns a constant time that
+ indicates the time at which the statement began to execute.
+ (Within a stored routine or trigger, <literal>NOW()</literal>
+ returns the time at which the routine or triggering statement
+ began to execute.) This differs from the behavior for
+ <literal>SYSDATE()</literal>, which returns the exact time at
+ which it executes.
</para>
+
+<programlisting>
+mysql> <userinput>SELECT NOW(), SLEEP(2), NOW();</userinput>
++---------------------+----------+---------------------+
+| NOW() | SLEEP(2) | NOW() |
++---------------------+----------+---------------------+
+| 2006-04-12 13:47:36 | 0 | 2006-04-12 13:47:36 |
++---------------------+----------+---------------------+
+
+mysql> <userinput>SELECT SYSDATE(), SLEEP(2), SYSDATE();</userinput>
++---------------------+----------+---------------------+
+| SYSDATE() | SLEEP(2) | SYSDATE() |
++---------------------+----------+---------------------+
+| 2006-04-12 13:47:44 | 0 | 2006-04-12 13:47:46 |
++---------------------+----------+---------------------+
+</programlisting>
+
+ <para>
+ See the description for <literal>SYSDATE()</literal> for
+ additional information about the differences between the two
+ functions.
+ </para>
</listitem>
<listitem>
@@ -8066,13 +8090,51 @@
</para>
<para>
- Within a stored routine or trigger,
<literal>SYSDATE()</literal> returns the time at which it
executes. This differs from the behavior for
- <literal>NOW()</literal>, which returns the the time at which
- the routine or triggering statement began to execute.
+ <literal>NOW()</literal>, which returns a constant time that
+ indicates the time at which the statement began to execute.
+ (Within a stored routine or trigger, <literal>NOW()</literal>
+ returns the time at which the routine or triggering statement
+ began to execute.)
</para>
+<programlisting>
+mysql> <userinput>SELECT NOW(), SLEEP(2), NOW();</userinput>
++---------------------+----------+---------------------+
+| NOW() | SLEEP(2) | NOW() |
++---------------------+----------+---------------------+
+| 2006-04-12 13:47:36 | 0 | 2006-04-12 13:47:36 |
++---------------------+----------+---------------------+
+
+mysql> <userinput>SELECT SYSDATE(), SLEEP(2), SYSDATE();</userinput>
++---------------------+----------+---------------------+
+| SYSDATE() | SLEEP(2) | SYSDATE() |
++---------------------+----------+---------------------+
+| 2006-04-12 13:47:44 | 0 | 2006-04-12 13:47:46 |
++---------------------+----------+---------------------+
+</programlisting>
+
+ <para>
+ In addition, the <literal>SET TIMESTAMP</literal> statement
+ affects the value returned by <literal>NOW()</literal> but not
+ by <literal>SYSDATE()</literal>. This means that timestamp
+ settings in the binary log have no effect on invocations of
+ <literal>SYSDATE()</literal>.
+ </para>
+
+ <para>
+ Because <literal>SYSDATE()</literal> can return different
+ values even within the same statement, and is not affected by
+ <literal>SET TIMESTAMP</literal>, it is non-deterministic and
+ therefore unsafe for replication if statement-based binary
+ logging is used. If that is a problem, you can use row-based
+ logging, or start the server with the
+ <option>--sysdate-is-now</option> option to cause
+ <literal>SYSDATE()</literal> to be an alias for
+ <literal>NOW()</literal>.
+ </para>
+
<remark role="help-description-end"/>
</listitem>
Modified: trunk/refman-5.1/replication.xml
===================================================================
--- trunk/refman-5.1/replication.xml 2006-04-12 17:03:14 UTC (rev 1809)
+++ trunk/refman-5.1/replication.xml 2006-04-12 20:30:42 UTC (rev 1810)
@@ -1843,6 +1843,20 @@
<listitem>
<para>
+ Unlike <literal>NOW()</literal>, the
+ <literal>SYSDATE()</literal> function is not replication-safe
+ because it is not affected by <literal>SET TIMESTAMP</literal>
+ statements in the binary log and is non-deterministic if
+ statement-based logging is used. This is not a problem if
+ row-based logging is used. Another option is to start the
+ server with the <option>--sysdate-is-now</option> option to
+ cause <literal>SYSDATE()</literal> to be an alias for
+ <literal>NOW()</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
User privileges are replicated only if the
<literal>mysql</literal> database is replicated. That is, the
<literal>GRANT</literal>, <literal>REVOKE</literal>,
@@ -5104,6 +5118,13 @@
</para>
</listitem>
+ <listitem>
+ <para>
+ <literal>SYSDATE()</literal> (unless the server is started
+ with the <option>--sysdate-is-now</option> option)
+ </para>
+ </listitem>
+
</itemizedlist>
<para>
Modified: trunk/refman-5.1/sql-syntax.xml
===================================================================
--- trunk/refman-5.1/sql-syntax.xml 2006-04-12 17:03:14 UTC (rev 1809)
+++ trunk/refman-5.1/sql-syntax.xml 2006-04-12 20:30:42 UTC (rev 1810)
@@ -15970,6 +15970,18 @@
rows. <literal>timestamp_value</literal> should be a Unix
epoch timestamp, not a MySQL timestamp.
</para>
+
+ <para>
+ <literal>SET TIMESTAMP</literal> affects the value returned
+ by <literal>NOW()</literal> but not by
+ <literal>SYSDATE()</literal>. This means that timestamp
+ settings in the binary log have no effect on invocations of
+ <literal>SYSDATE()</literal>. The server can be started with
+ the <option>--sysdate-is-now</option> option to cause
+ <literal>SYSDATE()</literal> to be an alias for
+ <literal>NOW()</literal>, in which case <literal>SET
+ TIMESTAMP</literal> affects both functions.
+ </para>
</listitem>
<!--
Modified: trunk/refman-5.1/stored-procedures.xml
===================================================================
--- trunk/refman-5.1/stored-procedures.xml 2006-04-12 17:03:14 UTC (rev 1809)
+++ trunk/refman-5.1/stored-procedures.xml 2006-04-12 20:30:42 UTC (rev 1810)
@@ -2587,6 +2587,17 @@
number seed as implicit inputs that are identical on the
master and slave.)
</para>
+
+ <para>
+ <literal>SYSDATE()</literal> 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
+ <literal>SYSDATE()</literal> to be an alias for
+ <literal>NOW()</literal>.
+ </para>
</listitem>
<listitem>
Modified: trunk/refman-common/news-5.0.xml
===================================================================
--- trunk/refman-common/news-5.0.xml 2006-04-12 17:03:14 UTC (rev 1809)
+++ trunk/refman-common/news-5.0.xml 2006-04-12 20:30:42 UTC (rev 1810)
@@ -441,6 +441,16 @@
<listitem>
<para>
+ Added the <option>--sysdate-is-now</option> option to
+ <command>mysqld</command> to enable
+ <literal>SYSDATE()</literal> to be treated as an alias for
+ <literal>NOW()</literal>. See
+ <xref linkend="date-and-time-functions"/>. (Bug #15101)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
<literal>InnoDB</literal>: The <literal>InnoDB</literal>
storage engine now provides a descriptive error message if
<filename>ibdata</filename> file information is omitted from
Modified: trunk/refman-common/news-5.1.xml
===================================================================
--- trunk/refman-common/news-5.1.xml 2006-04-12 17:03:14 UTC (rev 1809)
+++ trunk/refman-common/news-5.1.xml 2006-04-12 20:30:42 UTC (rev 1810)
@@ -700,6 +700,16 @@
<listitem>
<para>
+ Added the <option>--sysdate-is-now</option> option to
+ <command>mysqld</command> to enable
+ <literal>SYSDATE()</literal> to be treated as an alias for
+ <literal>NOW()</literal>. See
+ <xref linkend="date-and-time-functions"/>. (Bug #15101)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
The <literal>NDBCluster</literal> storage engine now supports
<literal>INSERT IGNORE</literal> and
<literal>REPLACE</literal> statements. Previously, these
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r1810 - in trunk: . refman-5.0 refman-5.1 refman-common | paul | 12 Apr |
