<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="replication-features-variables">
      </a>
      19.5.1.39 Replication and Variables
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045134848128">
  </a>
  <a class="indexterm" name="idm46045134846640">
  </a>
  <a class="indexterm" name="idm46045134845152">
  </a>
  <a class="indexterm" name="idm46045134843664">
  </a>
  <a class="indexterm" name="idm46045134842176">
  </a>
  <p>
   System variables are not replicated correctly when using
   <code class="literal">
    STATEMENT
   </code>
   mode, except for the following
        variables when they are used with session scope:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="link" href="replication-options-source.html#sysvar_auto_increment_increment">
       <code class="literal">
        auto_increment_increment
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="replication-options-source.html#sysvar_auto_increment_offset">
       <code class="literal">
        auto_increment_offset
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_character_set_client">
       <code class="literal">
        character_set_client
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_character_set_connection">
       <code class="literal">
        character_set_connection
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_character_set_database">
       <code class="literal">
        character_set_database
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_character_set_server">
       <code class="literal">
        character_set_server
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_collation_connection">
       <code class="literal">
        collation_connection
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_collation_database">
       <code class="literal">
        collation_database
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_collation_server">
       <code class="literal">
        collation_server
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_foreign_key_checks">
       <code class="literal">
        foreign_key_checks
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_identity">
       <code class="literal">
        identity
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_last_insert_id">
       <code class="literal">
        last_insert_id
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_lc_time_names">
       <code class="literal">
        lc_time_names
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_pseudo_thread_id">
       <code class="literal">
        pseudo_thread_id
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_sql_auto_is_null">
       <code class="literal">
        sql_auto_is_null
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_time_zone">
       <code class="literal">
        time_zone
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_timestamp">
       <code class="literal">
        timestamp
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_unique_checks">
       <code class="literal">
        unique_checks
       </code>
      </a>
     </p>
    </li>
   </ul>
  </div>
  <p>
   When
   <code class="literal">
    MIXED
   </code>
   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
   <a class="xref" href="binary-log-mixed.html" title="7.4.4.3 Mixed Binary Logging Format">
    Section 7.4.4.3, “Mixed Binary Logging Format”
   </a>
   .
  </p>
  <p>
   <a class="link" href="server-system-variables.html#sysvar_sql_mode">
    <code class="literal">
     sql_mode
    </code>
   </a>
   is also replicated
        except for the
   <a class="link" href="sql-mode.html#sqlmode_no_dir_in_create">
    <code class="literal">
     NO_DIR_IN_CREATE
    </code>
   </a>
   mode; the
        replica always preserves its own value for
   <a class="link" href="sql-mode.html#sqlmode_no_dir_in_create">
    <code class="literal">
     NO_DIR_IN_CREATE
    </code>
   </a>
   , regardless
        of changes to it on the source. This is true for all replication
        formats.
  </p>
  <p>
   However, when
   <a class="link" href="mysqlbinlog.html" title="6.6.9 mysqlbinlog — Utility for Processing Binary Log Files">
    <span class="command">
     <strong>
      mysqlbinlog
     </strong>
    </span>
   </a>
   parses a
   <code class="literal">
    SET @@sql_mode =
    <em class="replaceable">
     <code>
      mode
     </code>
    </em>
   </code>
   statement, the full
   <em class="replaceable">
    <code>
     mode
    </code>
   </em>
   value, including
   <a class="link" href="sql-mode.html#sqlmode_no_dir_in_create">
    <code class="literal">
     NO_DIR_IN_CREATE
    </code>
   </a>
   , is passed to
        the receiving server. For this reason, replication of such a
        statement may not be safe when
   <code class="literal">
    STATEMENT
   </code>
   mode
        is in use.
  </p>
  <p>
   The
   <a class="link" href="server-system-variables.html#sysvar_default_storage_engine">
    <code class="literal">
     default_storage_engine
    </code>
   </a>
   system variable is not replicated, regardless of the logging
        mode; this is intended to facilitate replication between
        different storage engines.
  </p>
  <p>
   The
   <a class="link" href="server-system-variables.html#sysvar_read_only">
    <code class="literal">
     read_only
    </code>
   </a>
   system variable
        is not replicated. In addition, the enabling this variable has
        different effects with regard to temporary tables, table
        locking, and the
   <a class="link" href="set-password.html" title="15.7.1.10 SET PASSWORD Statement">
    <code class="literal">
     SET PASSWORD
    </code>
   </a>
   statement in different MySQL versions.
  </p>
  <p>
   The
   <a class="link" href="server-system-variables.html#sysvar_max_heap_table_size">
    <code class="literal">
     max_heap_table_size
    </code>
   </a>
   system
        variable is not replicated. Increasing the value of this
        variable on the source without doing so on the replica can lead
        eventually to
   <span class="errortext">
    Table is full
   </span>
   errors on the
        replica when trying to execute
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   statements on a
   <a class="link" href="memory-storage-engine.html" title="18.3 The MEMORY Storage Engine">
    <code class="literal">
     MEMORY
    </code>
   </a>
   table on the source that is
        thus permitted to grow larger than its counterpart on the
        replica. For more information, see
   <a class="xref" href="replication-features-memory.html" title="19.5.1.21 Replication and MEMORY Tables">
    Section 19.5.1.21, “Replication and MEMORY Tables”
   </a>
   .
  </p>
  <p>
   In statement-based replication, session variables are not
        replicated properly when used in statements that update tables.
        For example, the following sequence of statements does not
        insert the same data on the source and the replica:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa45702363"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SET</span> max_join_size<span class="token operator">=</span><span class="token number">1000</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> mytable <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token variable">@@max_join_size</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   This does not apply to the common sequence:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa87823290"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SET</span> time_zone<span class="token operator">=</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> mytable <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token function">CONVERT_TZ</span><span class="token punctuation">(</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">,</span> <span class="token variable">@@time_zone</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Replication of session variables is not a problem when row-based
        replication is being used, in which case, session variables are
        always replicated safely. See
   <a class="xref" href="replication-formats.html" title="19.2.1 Replication Formats">
    Section 19.2.1, “Replication Formats”
   </a>
   .
  </p>
  <p>
   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:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_sql_mode">
       <code class="literal">
        sql_mode
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_foreign_key_checks">
       <code class="literal">
        foreign_key_checks
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_unique_checks">
       <code class="literal">
        unique_checks
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_character_set_client">
       <code class="literal">
        character_set_client
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_collation_connection">
       <code class="literal">
        collation_connection
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_collation_database">
       <code class="literal">
        collation_database
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_collation_server">
       <code class="literal">
        collation_server
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_sql_auto_is_null">
       <code class="literal">
        sql_auto_is_null
       </code>
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Important
   </div>
   <p>
    Even though session variables relating to character sets and
          collations are written to the binary log, replication between
          different character sets is not supported.
   </p>
  </div>
  <p>
   To help reduce possible confusion, we recommend that you always
        use the same setting for the
   <a class="link" href="server-system-variables.html#sysvar_lower_case_table_names">
    <code class="literal">
     lower_case_table_names
    </code>
   </a>
   system
        variable on both source and replica, especially when you are
        running MySQL on platforms with case-sensitive file systems. The
   <a class="link" href="server-system-variables.html#sysvar_lower_case_table_names">
    <code class="literal">
     lower_case_table_names
    </code>
   </a>
   setting
        can only be configured when initializing the server.
  </p>
 </div>
 <br/>
</div>
