Certain functions do not replicate well under some conditions:
The USER()
,
CURRENT_USER()
(or
CURRENT_USER
),
UUID()
,
VERSION()
,
LOAD_FILE()
, and
RAND()
functions are
replicated without change and thus do not work reliably on
the slave unless row-based replication is enabled. (See
Section 16.1.2, “Replication Formats”.)
For early implementations of mixed-format logging, stored
functions, triggers, and views that use these functions in
their body do not replicate reliably in mixed-format logging
mode because the logging did not switch from statement-based
to row-based format. For example, INSERT INTO t
SELECT FROM v
, where v
is a
view that selects UUID()
could cause problems. This limitation is lifted in MySQL
5.1.12.
Beginning with MySQL 5.1.23,
USER()
and
CURRENT_USER()
are
automatically replicated using row-based replication when
using MIXED
mode, and generate a warning
in STATEMENT
mode. (Bug#28086) Beginning
with MySQL 5.1.42, the same is true for
VERSION()
. (Bug#47995)
Beginning with MySQL 5.1.43, this is also true with regard
to the RAND()
function.
(Bug#49222)
For NOW()
, the binary log
includes the timestamp. This means that the value
as returned by the call to this function on the
master is replicated to the slave. This can lead
to a possibly unexpected result when replicating between
MySQL servers in different time zones. Suppose that the
master is located in New York, the slave is located in
Stockholm, and both servers are using local time. Suppose
further that, on the master, you create a table
mytable
, perform an
INSERT
statement on this
table, and then select from the table, as shown here:
mysql>CREATE TABLE mytable (mycol TEXT);
Query OK, 0 rows affected (0.06 sec) mysql>INSERT INTO mytable VALUES ( NOW() );
Query OK, 1 row affected (0.00 sec) mysql>SELECT * FROM mytable;
+---------------------+ | mycol | +---------------------+ | 2009-09-01 12:00:00 | +---------------------+ 1 row in set (0.00 sec)
Local time in Stockholm is 6 hours later than in New York;
so, if you issue SELECT NOW()
on the
slave at that exact same instant, the value
2009-09-01 18:00:00
is returned. For this
reason, if you select from the slave's copy of
mytable
after the
CREATE TABLE
and
INSERT
statements just shown
have been replicated, you might expect
mycol
to contain the value
2009-09-01 18:00:00
. However, this is not
the case; when you select from the slave's copy of
mytable
, you obtain exactly the same
result as on the master:
mysql> SELECT * FROM mytable;
+---------------------+
| mycol |
+---------------------+
| 2009-09-01 12:00:00 |
+---------------------+
1 row in set (0.00 sec)
Unlike NOW()
, the
SYSDATE()
function is not
replication-safe because it is not affected by SET
TIMESTAMP
statements in the binary log and is
nondeterministic if statement-based logging is used. This is
not a problem if row-based logging is used.
An alternative is to use the
--sysdate-is-now
option to
cause SYSDATE()
to be an
alias for NOW()
. This must be
done on the master and the slave to work correctly. In such
cases, a warning is still issued by this function, but can
safely be ignored as long as
--sysdate-is-now
is used on
both the master and the slave.
Beginning with MySQL 5.1.42,
SYSDATE()
is automatically
replicated using row-based replication when using
MIXED
mode, and generates a warning in
STATEMENT
mode. (Bug#47995)
The following restriction applies to
statement-based replication only, not to row-based
replication. The
GET_LOCK()
,
RELEASE_LOCK()
,
IS_FREE_LOCK()
, and
IS_USED_LOCK()
functions that
handle user-level locks are replicated without the slave
knowing the concurrency context on master. Therefore, these
functions should not be used to insert into a master's table
because the content on the slave would differ. For example,
do not issue a statement such as INSERT INTO
mytable VALUES(GET_LOCK(...))
.
Beginning with MySQL 5.1.42, these functions are
automatically replicated using row-based replication when
using MIXED
mode, and generate a warning
in STATEMENT
mode. (Bug#47995)
As a workaround for the preceding limitations when
statement-based replication is in effect, you can use the
strategy of saving the problematic function result in a user
variable and referring to the variable in a later statement. For
example, the following single-row
INSERT
is problematic due to the
reference to the UUID()
function:
INSERT INTO t VALUES(UUID());
To work around the problem, do this instead:
SET @my_uuid = UUID(); INSERT INTO t VALUES(@my_uuid);
That sequence of statements replicates because the value of
@my_uuid
is stored in the binary log as a
user-variable event prior to the
INSERT
statement and is available
for use in the INSERT
.
The same idea applies to multiple-row inserts, but is more cumbersome to use. For a two-row insert, you can do this:
SET @my_uuid1 = UUID(); @my_uuid2 = UUID(); INSERT INTO t VALUES(@my_uuid1),(@my_uuid2);
However, if the number of rows is large or unknown, the workaround is difficult or impracticable. For example, you cannot convert the following statement to one in which a given individual user variable is associated with each row:
INSERT INTO t2 SELECT UUID(), * FROM t1;
Within a stored function, RAND()
replicates correctly as long as it is invoked only once during
the execution of the function. (You can consider the function
execution timestamp and random number seed as implicit inputs
that are identical on the master and slave.)
The FOUND_ROWS()
and
ROW_COUNT()
functions are not
replicated reliably using statement-based replication. A
workaround is to store the result of the function call in a user
variable, and then use that in the
INSERT
statement. For example, if
you wish to store the result in a table named
mytable
, you might normally do so like this:
SELECT SQL_CALC_FOUND_ROWS FROM mytable LIMIT 1; INSERT INTO mytable VALUES( FOUND_ROWS() );
However, if you are replicating mytable
, you
should use SELECT INTO
, and then store the
variable in the table, like this:
SELECT SQL_CALC_FOUND_ROWS INTO @found_rows FROM mytable LIMIT 1; INSERT INTO mytable VALUES(@found_rows);
In this way, the user variable is replicated as part of the context, and applied on the slave correctly.
Beginning with MySQL 5.1.23, these functions are automatically
replicated using row-based replication when using
MIXED
mode, and generate a warning in
STATEMENT
mode. (Bug#12092, Bug#30244)
User Comments
Add your own comment.