Oracle 5.0, Reference Manual

Page: 1521 / 3232

Replication Features and Issues
1501
TIMESTAMP
statements in the binary log and is nondeterministic. To avoid this, you can start the
server with the
--sysdate-is-now
[423] option to cause
SYSDATE()
[928] to be an alias for
NOW()
[926] .
See also Section 16.4.1.24, “Replication and Time Zones” .
• The
GET_LOCK()
[965] ,
RELEASE_LOCK()
[967] ,
IS_FREE_LOCK()
[966] , and
IS_USED_LOCK()
[966] functions that handle user-level locks are replicated without the slave
knowing the concurrency context on the master. Therefore, these functions should not be used to
insert into a master table because the content on the slave would differ. For example, do not issue a
statement such as
INSERT INTO mytable VALUES(GET_LOCK(...))
.
As a workaround for the preceding limitations, 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()
[967] 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;
Non-delayed
INSERT
statements that refer to
RAND()
[912] or user-defined variables replicate
correctly. However, changing the statements to use
INSERT DELAYED
can result in different results on
master and slave.
Within a stored function,
RAND()
[912] 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()
[960] and
ROW_COUNT()
[963] functions are also not replicated reliably. 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.
16.4.1.9. Replication and
LIMIT