Movatterモバイル変換

System variables are not replicated correctly when usingSTATEMENT mode, except for the following variables when they are used with session scope:

WhenMIXED mode is used, the variables in the preceding list, when used with session scope, cause a switch from statement-based to row-based logging. SeeSection 5.4.4.3, “Mixed Binary Logging Format”.

sql_mode is also replicated except for theNO_DIR_IN_CREATE mode; the replica always preserves its own value forNO_DIR_IN_CREATE, regardless of changes to it on the source. This is true for all replication formats.

However, whenmysqlbinlog parses aSET @@sql_mode =mode statement, the fullmode value, includingNO_DIR_IN_CREATE, is passed to the receiving server. For this reason, replication of such a statement may not be safe whenSTATEMENT mode is in use.

Thedefault_storage_engine system variable is not replicated, regardless of the logging mode; this is intended to facilitate replication between different storage engines.

Theread_only system variable is not replicated. In addition, the enabling this variable has different effects with regard to temporary tables, table locking, and theSET PASSWORD statement in different MySQL versions.

Themax_heap_table_size system variable is not replicated. Increasing the value of this variable on the source without doing so on the replica can lead eventually toTable is full errors on the replica when trying to executeINSERT statements on aMEMORY table on the source that is thus permitted to grow larger than its counterpart on the replica. For more information, seeSection 16.4.1.20, “Replication and MEMORY Tables”.

In statement-based replication, session variables are not replicated properly when used in statements that update tables. For example, the following sequence of statements do not insert the same data on the source and the replica:

SET max_join_size=1000;INSERT INTO mytable VALUES(@@max_join_size);

This does not apply to the common sequence:

SET time_zone=...;INSERT INTO mytable VALUES(CONVERT_TZ(..., ..., @@time_zone));

Replication of session variables is not a problem when row-based replication is being used, in which case, session variables are always replicated safely. SeeSection 16.2.1, “Replication Formats”.

The following session variables are written to the binary log and honored by the replica when parsing the binary log, regardless of the logging format:

To help reduce possible confusion, we recommend that you always use the same setting for thelower_case_table_names system variable on both source and replica, especially when you are running MySQL on platforms with case-sensitive file systems.