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
valuevalue, 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.