System variables are not replicated correctly when using
STATEMENT
mode, except for the following
variables when they are used with session scope:
When MIXED
mode is used, the variables in the
preceding list, when used with session scope, cause a switch
from statement-based to row-based logging. See
Section 5.2.4.3, “Mixed Binary Logging Format”.
sql_mode
is also replicated
except for the
NO_DIR_IN_CREATE
mode; the
slave always preserves its own value for
NO_DIR_IN_CREATE
, regardless
of any changes to this mode on the master. This is true for all
replication modes.
However, when mysqlbinlog parses a
SET @@sql_mode =
statement, the full
value
value
, including
NO_DIR_IN_CREATE
, is passed to
the receiving server. For this reason, replication of such a
statement may not be safe when STATEMENT
mode
is in use.
The storage_engine
system
variable is not replicated, regardless of the logging mode; this
is intended to facilitate replication between different storage
engines.
In statement-based replication, session variables are not
replicated properly when used in statements that update tables.
For example, SET MAX_JOIN_SIZE=1000
followed
by INSERT INTO mytable
VALUES(@@MAX_JOIN_SIZE)
does not insert the same data
on the master and the slave. However, this does not apply to the
common sequence of SET TIME_ZONE=...
followed
by INSERT INTO mytable
VALUES(CONVERT_TZ(...,...,@@time_zone))
.
Replication of session variables is not a problem when row-based replication is being used; with row-based replication, session variables are always replicated safely. See Section 16.1.2, “Replication Formats”.
User Comments
Add your own comment.