<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="stored-programs-logging">
      </a>
      27.7 Stored Program Binary Logging
     </h2>
    </div>
   </div>
  </div>
  <p>
   The binary log contains information about SQL statements that
      modify database contents. This information is stored in the form
      of
   <span class="quote">
    “
    <span class="quote">
     events
    </span>
    ”
   </span>
   that describe the modifications. (Binary
      log events differ from scheduled event stored objects.) The binary
      log has two important purposes:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      For replication, the binary log is used on source replication
          servers as a record of the statements to be sent to replica
          servers. The source sends the events contained in its binary
          log to its replicas, which execute those events to make the
          same data changes that were made on the source. See
      <a class="xref" href="replication-implementation.html" title="19.2 Replication Implementation">
       Section 19.2, “Replication Implementation”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Certain data recovery operations require use of the binary
          log. After a backup file has been restored, the events in the
          binary log that were recorded after the backup was made are
          re-executed. These events bring databases up to date from the
          point of the backup. See
      <a class="xref" href="recovery-from-backups.html" title="9.3.2 Using Backups for Recovery">
       Section 9.3.2, “Using Backups for Recovery”
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   However, if logging occurs at the statement level, there are
      certain binary logging issues with respect to stored programs
      (stored procedures and functions, triggers, and events):
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      In some cases, a statement might affect different sets of rows
          on source and replica.
     </p>
    </li>
    <li class="listitem">
     <p>
      Replicated statements executed on a replica are processed by
          the replica's applier thread. Unless you implement
          replication privilege checks (see
      <a class="xref" href="replication-privilege-checks.html" title="19.3.3 Replication Privilege Checks">
       Section 19.3.3, “Replication Privilege Checks”
      </a>
      ), the applier
          thread has full privileges. In this situation, it is possible
          for a procedure to follow different execution paths on source
          and replica servers, so a user could write a routine
          containing a dangerous statement that executes only on the
          replica.
     </p>
    </li>
    <li class="listitem">
     <p>
      If a stored program that modifies data is nondeterministic, it
          is not repeatable. This can result in different data on source
          and replica, or cause restored data to differ from the
          original data.
     </p>
    </li>
   </ul>
  </div>
  <p>
   This section describes how MySQL handles binary logging for stored
      programs. It states the current conditions that the implementation
      places on the use of stored programs, and what you can do to avoid
      logging problems. It also provides additional information about
      the reasons for these conditions.
  </p>
  <p>
   Unless noted otherwise, the remarks here assume that binary
      logging is enabled on the server (see
   <a class="xref" href="binary-log.html" title="7.4.4 The Binary Log">
    Section 7.4.4, “The Binary Log”
   </a>
   .) If the binary log is not enabled,
      replication is not possible, nor is the binary log available for
      data recovery. Binary logging is enabled by default, and is
      disabled only if you start the server with
   <a class="link" href="replication-options-binary-log.html#option_mysqld_log-bin">
    <code class="option">
     --skip-log-bin
    </code>
   </a>
   or
   <a class="link" href="replication-options-binary-log.html#option_mysqld_log-bin">
    <code class="option">
     --disable-log-bin
    </code>
   </a>
   at startup.
  </p>
  <p>
   In general, the issues described here result when binary logging
      occurs at the SQL statement level (statement-based binary
      logging). If you use row-based binary logging, the log contains
      changes made to individual rows as a result of executing SQL
      statements. When routines or triggers execute, row changes are
      logged, not the statements that make the changes. For stored
      procedures, this means that the
   <a class="link" href="call.html" title="15.2.1 CALL Statement">
    <code class="literal">
     CALL
    </code>
   </a>
   statement is not logged. For
      stored functions, row changes made within the function are logged,
      not the function invocation. For triggers, row changes made by the
      trigger are logged. On the replica side, only the row changes are
      seen, not the stored program invocation.
  </p>
  <p>
   Mixed format binary logging
      (
   <a class="link" href="replication-options-binary-log.html#sysvar_binlog_format">
    <code class="literal">
     binlog_format=MIXED
    </code>
   </a>
   ) uses
      statement-based binary logging, except for cases where only
      row-based binary logging is guaranteed to lead to proper results.
      With mixed format, when a stored function, stored procedure,
      trigger, event, or prepared statement contains anything that is
      not safe for statement-based binary logging, the entire statement
      is marked as unsafe and logged in row format. The statements used
      to create and drop procedures, functions, triggers, and events are
      always safe, and are logged in statement format. For more
      information about row-based, mixed, and statement-based logging,
      and how safe and unsafe statements are determined, see
   <a class="xref" href="replication-formats.html" title="19.2.1 Replication Formats">
    Section 19.2.1, “Replication Formats”
   </a>
   .
  </p>
  <p>
   The conditions on the use of stored functions in MySQL can be
      summarized as follows. These conditions do not apply to stored
      procedures or Event Scheduler events and they do not apply unless
      binary logging is enabled.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      To create or alter a stored function, you must have the
      <a class="link" href="privileges-provided.html#priv_set-any-definer">
       <code class="literal">
        SET_ANY_DEFINER
       </code>
      </a>
      privilege, in
          addition to the
      <a class="link" href="privileges-provided.html#priv_create-routine">
       <code class="literal">
        CREATE ROUTINE
       </code>
      </a>
      or
      <a class="link" href="privileges-provided.html#priv_alter-routine">
       <code class="literal">
        ALTER ROUTINE
       </code>
      </a>
      privilege that
          is normally required. (Depending on the
      <code class="literal">
       DEFINER
      </code>
      value in the function definition,
      <a class="link" href="privileges-provided.html#priv_set-any-definer">
       <code class="literal">
        SET_ANY_DEFINER
       </code>
      </a>
      might be
          required regardless of whether binary logging is enabled. See
      <a class="xref" href="create-procedure.html" title="15.1.17 CREATE PROCEDURE and CREATE FUNCTION Statements">
       Section 15.1.17, “CREATE PROCEDURE and CREATE FUNCTION Statements”
      </a>
      .)
     </p>
    </li>
    <li class="listitem">
     <p>
      When you create a stored function, you must declare either
          that it is deterministic or that it does not modify data.
          Otherwise, it may be unsafe for data recovery or replication.
     </p>
     <p>
      By default, for a
      <a class="link" href="create-function.html" title="15.1.14 CREATE FUNCTION Statement">
       <code class="literal">
        CREATE
          FUNCTION
       </code>
      </a>
      statement to be accepted, at least one of
      <code class="literal">
       DETERMINISTIC
      </code>
      ,
      <code class="literal">
       NO SQL
      </code>
      ,
          or
      <code class="literal">
       READS SQL DATA
      </code>
      must be specified
          explicitly. Otherwise an error occurs:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-none"><div class="docs-select-all right" id="sa11762485"><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-none">ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
or READS SQL DATA in its declaration and binary logging is enabled
(you *might* want to use the less safe log_bin_trust_function_creators
variable)</code></pre>
     </div>
     <p>
      This function is deterministic (and does not modify data), so
          it is safe:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa30232769"><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">CREATE</span> <span class="token keyword">FUNCTION</span> f1<span class="token punctuation">(</span>i <span class="token datatype">INT</span><span class="token punctuation">)</span>
<span class="token keyword">RETURNS</span> <span class="token datatype">INT</span>
<span class="token keyword">DETERMINISTIC</span>
<span class="token keyword">READS</span> <span class="token keyword">SQL</span> <span class="token keyword">DATA</span>
<span class="token keyword">BEGIN</span>
  <span class="token keyword">RETURN</span> i<span class="token punctuation">;</span>
<span class="token keyword">END</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      This function uses
      <a class="link" href="miscellaneous-functions.html#function_uuid">
       <code class="literal">
        UUID()
       </code>
      </a>
      ,
          which is not deterministic, so the function also is not
          deterministic and is not safe:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa9731082"><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">CREATE</span> <span class="token keyword">FUNCTION</span> f2<span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token keyword">RETURNS</span> <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">36</span><span class="token punctuation">)</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> utf8mb4
<span class="token keyword">BEGIN</span>
  <span class="token keyword">RETURN</span> <span class="token function">UUID</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">END</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      This function modifies data, so it may not be safe:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa90358683"><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">CREATE</span> <span class="token keyword">FUNCTION</span> f3<span class="token punctuation">(</span>p_id <span class="token datatype">INT</span><span class="token punctuation">)</span>
<span class="token keyword">RETURNS</span> <span class="token datatype">INT</span>
<span class="token keyword">BEGIN</span>
  <span class="token keyword">UPDATE</span> t <span class="token keyword">SET</span> modtime <span class="token operator">=</span> <span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">WHERE</span> id <span class="token operator">=</span> p_id<span class="token punctuation">;</span>
  <span class="token keyword">RETURN</span> <span class="token function">ROW_COUNT</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">END</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      Assessment of the nature of a function is based on the
      <span class="quote">
       “
       <span class="quote">
        honesty
       </span>
       ”
      </span>
      of the creator. MySQL does not check
          that a function declared
      <code class="literal">
       DETERMINISTIC
      </code>
      is
          free of statements that produce nondeterministic results.
     </p>
    </li>
    <li class="listitem">
     <p>
      When you attempt to execute a stored function, if
      <a class="link" href="replication-options-binary-log.html#sysvar_binlog_format">
       <code class="literal">
        binlog_format=STATEMENT
       </code>
      </a>
      is
          set, the
      <code class="literal">
       DETERMINISTIC
      </code>
      keyword must be
          specified in the function definition. If this is not the case,
          an error is generated and the function does not run, unless
      <a class="link" href="replication-options-binary-log.html#sysvar_log_bin_trust_function_creators">
       <code class="literal">
        log_bin_trust_function_creators=1
       </code>
      </a>
      is specified to override this check (see below). For recursive
          function calls, the
      <code class="literal">
       DETERMINISTIC
      </code>
      keyword
          is required on the outermost call only. If row-based or mixed
          binary logging is in use, the statement is accepted and
          replicated even if the function was defined without the
      <code class="literal">
       DETERMINISTIC
      </code>
      keyword.
     </p>
    </li>
    <li class="listitem">
     <p>
      Because MySQL does not check if a function really is
          deterministic at creation time, the invocation of a stored
          function with the
      <code class="literal">
       DETERMINISTIC
      </code>
      keyword
          might carry out an action that is unsafe for statement-based
          logging, or invoke a function or procedure containing unsafe
          statements. If this occurs when
      <a class="link" href="replication-options-binary-log.html#sysvar_binlog_format">
       <code class="literal">
        binlog_format=STATEMENT
       </code>
      </a>
      is
          set, a warning message is issued. If row-based or mixed binary
          logging is in use, no warning is issued, and the statement is
          replicated in row-based format.
     </p>
    </li>
    <li class="listitem">
     <p>
      To relax the preceding conditions on function creation (that
          you must have the
      <a class="link" href="privileges-provided.html#priv_super">
       <code class="literal">
        SUPER
       </code>
      </a>
      privilege and that a function must be declared deterministic
          or to not modify data), set the global
      <a class="link" href="replication-options-binary-log.html#sysvar_log_bin_trust_function_creators">
       <code class="literal">
        log_bin_trust_function_creators
       </code>
      </a>
      system variable to 1. By default, this variable has a value of
          0, but you can change it like this:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa66781442"><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 prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> log_bin_trust_function_creators <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      You can also set this variable at server startup.
     </p>
     <p>
      If binary logging is not enabled,
      <a class="link" href="replication-options-binary-log.html#sysvar_log_bin_trust_function_creators">
       <code class="literal">
        log_bin_trust_function_creators
       </code>
      </a>
      does not apply.
      <a class="link" href="privileges-provided.html#priv_super">
       <code class="literal">
        SUPER
       </code>
      </a>
      is not
          required for function creation unless, as described
          previously, the
      <code class="literal">
       DEFINER
      </code>
      value in the
          function definition requires it.
     </p>
    </li>
    <li class="listitem">
     <p>
      For information about built-in functions that may be unsafe
          for replication (and thus cause stored functions that use them
          to be unsafe as well), see
      <a class="xref" href="replication-features.html" title="19.5.1 Replication Features and Issues">
       Section 19.5.1, “Replication Features and Issues”
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   Triggers are similar to stored functions, so the preceding remarks
      regarding functions also apply to triggers with the following
      exception:
   <a class="link" href="create-trigger.html" title="15.1.22 CREATE TRIGGER Statement">
    <code class="literal">
     CREATE TRIGGER
    </code>
   </a>
   does not
      have an optional
   <code class="literal">
    DETERMINISTIC
   </code>
   characteristic,
      so triggers are assumed to be always deterministic. However, this
      assumption might be invalid in some cases. For example, the
   <a class="link" href="miscellaneous-functions.html#function_uuid">
    <code class="literal">
     UUID()
    </code>
   </a>
   function is nondeterministic
      (and does not replicate). Be careful about using such functions in
      triggers.
  </p>
  <p>
   Triggers can update tables, so error messages similar to those for
      stored functions occur with
   <a class="link" href="create-trigger.html" title="15.1.22 CREATE TRIGGER Statement">
    <code class="literal">
     CREATE
      TRIGGER
    </code>
   </a>
   if you do not have the required privileges. On
      the replica side, the replica uses the trigger
   <code class="literal">
    DEFINER
   </code>
   attribute to determine which user is
      considered to be the creator of the trigger.
  </p>
  <p>
   The rest of this section provides additional detail about the
      logging implementation and its implications. You need not read it
      unless you are interested in the background on the rationale for
      the current logging-related conditions on stored routine use. This
      discussion applies only for statement-based logging, and not for
      row-based logging, with the exception of the first item:
   <code class="literal">
    CREATE
   </code>
   and
   <code class="literal">
    DROP
   </code>
   statements
      are logged as statements regardless of the logging mode.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The server writes
      <a class="link" href="create-event.html" title="15.1.13 CREATE EVENT Statement">
       <code class="literal">
        CREATE EVENT
       </code>
      </a>
      ,
      <a class="link" href="create-procedure.html" title="15.1.17 CREATE PROCEDURE and CREATE FUNCTION Statements">
       <code class="literal">
        CREATE PROCEDURE
       </code>
      </a>
      ,
      <a class="link" href="create-function.html" title="15.1.14 CREATE FUNCTION Statement">
       <code class="literal">
        CREATE FUNCTION
       </code>
      </a>
      ,
      <a class="link" href="alter-event.html" title="15.1.3 ALTER EVENT Statement">
       <code class="literal">
        ALTER EVENT
       </code>
      </a>
      ,
      <a class="link" href="alter-procedure.html" title="15.1.7 ALTER PROCEDURE Statement">
       <code class="literal">
        ALTER PROCEDURE
       </code>
      </a>
      ,
      <a class="link" href="alter-function.html" title="15.1.4 ALTER FUNCTION Statement">
       <code class="literal">
        ALTER FUNCTION
       </code>
      </a>
      ,
      <a class="link" href="drop-event.html" title="15.1.25 DROP EVENT Statement">
       <code class="literal">
        DROP EVENT
       </code>
      </a>
      ,
      <a class="link" href="drop-procedure.html" title="15.1.29 DROP PROCEDURE and DROP FUNCTION Statements">
       <code class="literal">
        DROP PROCEDURE
       </code>
      </a>
      , and
      <a class="link" href="drop-function.html" title="15.1.26 DROP FUNCTION Statement">
       <code class="literal">
        DROP FUNCTION
       </code>
      </a>
      statements to the
          binary log.
     </p>
    </li>
    <li class="listitem">
     <p>
      A stored function invocation is logged as a
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      statement if the
          function changes data and occurs within a statement that would
          not otherwise be logged. This prevents nonreplication of data
          changes that result from use of stored functions in nonlogged
          statements. For example,
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      statements are not written to the binary log, but a
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      might invoke a stored
          function that makes changes. To handle this, a
      <code class="literal">
       SELECT
       <em class="replaceable">
        <code>
         func_name
        </code>
       </em>
       ()
      </code>
      statement is
          written to the binary log when the given function makes a
          change. Suppose that the following statements are executed on
          the source server:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa64774596"><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">CREATE</span> <span class="token keyword">FUNCTION</span> f1<span class="token punctuation">(</span>a <span class="token datatype">INT</span><span class="token punctuation">)</span> <span class="token keyword">RETURNS</span> <span class="token datatype">INT</span>
<span class="token keyword">BEGIN</span>
  <span class="token function">IF</span> <span class="token punctuation">(</span>a <span class="token operator">&lt;</span> <span class="token number">3</span><span class="token punctuation">)</span> <span class="token keyword">THEN</span>
    <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t2 <span class="token keyword">VALUES</span> <span class="token punctuation">(</span>a<span class="token punctuation">)</span><span class="token punctuation">;</span>
  <span class="token keyword">END</span> <span class="token keyword">IF</span><span class="token punctuation">;</span>
  <span class="token keyword">RETURN</span> <span class="token number">0</span><span class="token punctuation">;</span>
<span class="token keyword">END</span><span class="token punctuation">;</span>

<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>a <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> f1<span class="token punctuation">(</span>a<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      When the
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      statement
          executes, the function
      <code class="literal">
       f1()
      </code>
      is invoked
          three times. Two of those invocations insert a row, and MySQL
          logs a
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      statement for
          each of them. That is, MySQL writes the following statements
          to the binary log:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa54642899"><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">SELECT</span> f1<span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> f1<span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      The server also logs a
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      statement for a stored function invocation when the function
          invokes a stored procedure that causes an error. In this case,
          the server writes the
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      statement to the log along with the expected error code. On
          the replica, if the same error occurs, that is the expected
          result and replication continues. Otherwise, replication
          stops.
     </p>
    </li>
    <li class="listitem">
     <p>
      Logging stored function invocations rather than the statements
          executed by a function has a security implication for
          replication, which arises from two factors:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         It is possible for a function to follow different
              execution paths on source and replica servers.
        </p>
       </li>
       <li class="listitem">
        <p>
         Statements executed on a replica are processed by the
              replica's applier thread. Unless you implement replication
              privilege checks (see
         <a class="xref" href="replication-privilege-checks.html" title="19.3.3 Replication Privilege Checks">
          Section 19.3.3, “Replication Privilege Checks”
         </a>
         ), the
              applier thread has full privileges.
        </p>
       </li>
      </ul>
     </div>
     <p>
      The implication is that although a user must have the
      <a class="link" href="privileges-provided.html#priv_create-routine">
       <code class="literal">
        CREATE ROUTINE
       </code>
      </a>
      privilege to
          create a function, the user can write a function containing a
          dangerous statement that executes only on the replica where it
          is processed by a thread that has full privileges. For
          example, if the source and replica servers have server ID
          values of 1 and 2, respectively, a user on the source server
          could create and invoke an unsafe function
      <code class="literal">
       unsafe_func()
      </code>
      as follows:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa42368773"><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 prompt">mysql&gt;</span> <span class="token keyword">delimiter</span> <span class="token comment" spellcheck="true">//</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">FUNCTION</span> unsafe_func <span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">RETURNS</span> <span class="token datatype">INT</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">BEGIN</span>
<span class="token prompt">    -&gt;</span>   <span class="token keyword">IF</span> <span class="token variable">@@server_id</span><span class="token operator">=</span><span class="token number">2</span> <span class="token keyword">THEN</span> <em class="replaceable">dangerous_statement</em><span class="token punctuation">;</span> <span class="token keyword">END</span> <span class="token keyword">IF</span><span class="token punctuation">;</span>
<span class="token prompt">    -&gt;</span>   <span class="token keyword">RETURN</span> <span class="token number">1</span><span class="token punctuation">;</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">END</span><span class="token punctuation">;</span>
<span class="token prompt">    -&gt;</span> <span class="token comment" spellcheck="true">//</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">delimiter</span> <span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t <span class="token keyword">VALUES</span><span class="token punctuation">(</span>unsafe_func<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      The
      <a class="link" href="create-function.html" title="15.1.14 CREATE FUNCTION Statement">
       <code class="literal">
        CREATE FUNCTION
       </code>
      </a>
      and
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      statements are written
          to the binary log, so the replica executes them. Because the
          replica's applier thread has full privileges, it executes the
          dangerous statement. Thus, the function invocation has
          different effects on the source and replica and is not
          replication-safe.
     </p>
     <p>
      To guard against this danger for servers that have binary
          logging enabled, stored function creators must have the
      <a class="link" href="privileges-provided.html#priv_super">
       <code class="literal">
        SUPER
       </code>
      </a>
      privilege, in addition to
          the usual
      <a class="link" href="privileges-provided.html#priv_create-routine">
       <code class="literal">
        CREATE ROUTINE
       </code>
      </a>
      privilege that is required. Similarly, to use
      <a class="link" href="alter-function.html" title="15.1.4 ALTER FUNCTION Statement">
       <code class="literal">
        ALTER FUNCTION
       </code>
      </a>
      , you must have
          the
      <a class="link" href="privileges-provided.html#priv_super">
       <code class="literal">
        SUPER
       </code>
      </a>
      privilege in addition
          to the
      <a class="link" href="privileges-provided.html#priv_alter-routine">
       <code class="literal">
        ALTER ROUTINE
       </code>
      </a>
      privilege.
          Without the
      <a class="link" href="privileges-provided.html#priv_super">
       <code class="literal">
        SUPER
       </code>
      </a>
      privilege, an
          error occurs:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-none"><div class="docs-select-all right" id="sa82060304"><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-none">ERROR 1419 (HY000): You do not have the SUPER privilege and
binary logging is enabled (you *might* want to use the less safe
log_bin_trust_function_creators variable)</code></pre>
     </div>
     <p>
      If you do not want to require function creators to have the
      <a class="link" href="privileges-provided.html#priv_super">
       <code class="literal">
        SUPER
       </code>
      </a>
      privilege (for example,
          if all users with the
      <a class="link" href="privileges-provided.html#priv_create-routine">
       <code class="literal">
        CREATE
          ROUTINE
       </code>
      </a>
      privilege on your system are experienced
          application developers), set the global
      <a class="link" href="replication-options-binary-log.html#sysvar_log_bin_trust_function_creators">
       <code class="literal">
        log_bin_trust_function_creators
       </code>
      </a>
      system variable to 1. You can also set this variable at server
          startup. If binary logging is not enabled,
      <a class="link" href="replication-options-binary-log.html#sysvar_log_bin_trust_function_creators">
       <code class="literal">
        log_bin_trust_function_creators
       </code>
      </a>
      does not apply.
      <a class="link" href="privileges-provided.html#priv_super">
       <code class="literal">
        SUPER
       </code>
      </a>
      is not
          required for function creation unless, as described
          previously, the
      <code class="literal">
       DEFINER
      </code>
      value in the
          function definition requires it.
     </p>
    </li>
    <li class="listitem">
     <p>
      The use of replication privilege checks recommended regardless
          of any choices you make about privileges for function
          creators. Replication privilege checks can be set up to ensure
          that only expected and relevant operations are authorized for
          the replication channel. For instructions to do this, see
      <a class="xref" href="replication-privilege-checks.html" title="19.3.3 Replication Privilege Checks">
       Section 19.3.3, “Replication Privilege Checks”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      If a function that performs updates is nondeterministic, it is
          not repeatable. This can have two undesirable effects:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         It causes a replica to differ from the source.
        </p>
       </li>
       <li class="listitem">
        <p>
         Restored data does not match the original data.
        </p>
       </li>
      </ul>
     </div>
     <p>
      To deal with these problems, MySQL enforces the following
          requirement: On a source server, creation and alteration of a
          function is refused unless you declare the function to be
          deterministic or to not modify data. Two sets of function
          characteristics apply here:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         The
         <code class="literal">
          DETERMINISTIC
         </code>
         and
         <code class="literal">
          NOT
              DETERMINISTIC
         </code>
         characteristics indicate whether a
              function always produces the same result for given inputs.
              The default is
         <code class="literal">
          NOT DETERMINISTIC
         </code>
         if
              neither characteristic is given. To declare that a
              function is deterministic, you must specify
         <code class="literal">
          DETERMINISTIC
         </code>
         explicitly.
        </p>
       </li>
       <li class="listitem">
        <p>
         The
         <code class="literal">
          CONTAINS SQL
         </code>
         ,
         <code class="literal">
          NO
              SQL
         </code>
         ,
         <code class="literal">
          READS SQL DATA
         </code>
         , and
         <code class="literal">
          MODIFIES SQL DATA
         </code>
         characteristics
              provide information about whether the function reads or
              writes data. Either
         <code class="literal">
          NO SQL
         </code>
         or
         <code class="literal">
          READS SQL DATA
         </code>
         indicates that a
              function does not change data, but you must specify one of
              these explicitly because the default is
         <code class="literal">
          CONTAINS
              SQL
         </code>
         if no characteristic is given.
        </p>
       </li>
      </ul>
     </div>
     <p>
      By default, for a
      <a class="link" href="create-function.html" title="15.1.14 CREATE FUNCTION Statement">
       <code class="literal">
        CREATE
          FUNCTION
       </code>
      </a>
      statement to be accepted, at least one of
      <code class="literal">
       DETERMINISTIC
      </code>
      ,
      <code class="literal">
       NO SQL
      </code>
      ,
          or
      <code class="literal">
       READS SQL DATA
      </code>
      must be specified
          explicitly. Otherwise an error occurs:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-none"><div class="docs-select-all right" id="sa48931894"><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-none">ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
or READS SQL DATA in its declaration and binary logging is enabled
(you *might* want to use the less safe log_bin_trust_function_creators
variable)</code></pre>
     </div>
     <p>
      If you set
      <a class="link" href="replication-options-binary-log.html#sysvar_log_bin_trust_function_creators">
       <code class="literal">
        log_bin_trust_function_creators
       </code>
      </a>
      to 1, the requirement that functions be deterministic or not
          modify data is dropped.
     </p>
    </li>
    <li class="listitem">
     <p>
      Stored procedure calls are logged at the statement level
          rather than at the
      <a class="link" href="call.html" title="15.2.1 CALL Statement">
       <code class="literal">
        CALL
       </code>
      </a>
      level.
          That is, the server does not log the
      <a class="link" href="call.html" title="15.2.1 CALL Statement">
       <code class="literal">
        CALL
       </code>
      </a>
      statement, it logs those
          statements within the procedure that actually execute. As a
          result, the same changes that occur on the source server also
          occur on replicas. This prevents problems that could result
          from a procedure having different execution paths on different
          machines.
     </p>
     <p>
      In general, statements executed within a stored procedure are
          written to the binary log using the same rules that would
          apply were the statements to be executed in standalone
          fashion. Some special care is taken when logging procedure
          statements because statement execution within procedures is
          not quite the same as in nonprocedure context:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         <a class="indexterm" name="idm46045081319760">
         </a>
         A statement to be logged might contain references to local
              procedure variables. These variables do not exist outside
              of stored procedure context, so a statement that refers to
              such a variable cannot be logged literally. Instead, each
              reference to a local variable is replaced by this
              construct for logging purposes:
        </p>
        <div class="copytoclipboard-wrapper">
         <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa50741401"><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 function">NAME_CONST</span><span class="token punctuation">(</span><em class="replaceable">var_name</em><span class="token punctuation">,</span> <em class="replaceable">var_value</em><span class="token punctuation">)</span></code></pre>
        </div>
        <p>
         <em class="replaceable">
          <code>
           var_name
          </code>
         </em>
         is the local variable
              name, and
         <em class="replaceable">
          <code>
           var_value
          </code>
         </em>
         is a
              constant indicating the value that the variable has at the
              time the statement is logged.
         <a class="link" href="miscellaneous-functions.html#function_name-const">
          <code class="literal">
           NAME_CONST()
          </code>
         </a>
         has a value of
         <em class="replaceable">
          <code>
           var_value
          </code>
         </em>
         , and a
         <span class="quote">
          “
          <span class="quote">
           name
          </span>
          ”
         </span>
         of
         <em class="replaceable">
          <code>
           var_name
          </code>
         </em>
         . Thus, if you invoke
              this function directly, you get a result like this:
        </p>
        <div class="copytoclipboard-wrapper">
         <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa29365048"><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 prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">NAME_CONST</span><span class="token punctuation">(</span><span class="token string">'myname'</span><span class="token punctuation">,</span> <span class="token number">14</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output"><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 punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> myname <span class="token punctuation">|</span></span>
<span class="token output"><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 punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>     14 <span class="token punctuation">|</span></span>
<span class="token output"><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 punctuation">+</span></span></code></pre>
        </div>
        <p>
         <a class="link" href="miscellaneous-functions.html#function_name-const">
          <code class="literal">
           NAME_CONST()
          </code>
         </a>
         enables a
              logged standalone statement to be executed on a replica
              with the same effect as the original statement that was
              executed on the source within a stored procedure.
        </p>
        <p>
         The use of
         <a class="link" href="miscellaneous-functions.html#function_name-const">
          <code class="literal">
           NAME_CONST()
          </code>
         </a>
         can
              result in a problem for
         <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
          <code class="literal">
           CREATE TABLE
              ... SELECT
          </code>
         </a>
         statements when the source column
              expressions refer to local variables. Converting these
              references to
         <a class="link" href="miscellaneous-functions.html#function_name-const">
          <code class="literal">
           NAME_CONST()
          </code>
         </a>
         expressions can result in column names that are different
              on the source and replica servers, or names that are too
              long to be legal column identifiers. A workaround is to
              supply aliases for columns that refer to local variables.
              Consider this statement when
         <code class="literal">
          myvar
         </code>
         has
              a value of 1:
        </p>
        <div class="copytoclipboard-wrapper">
         <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa85212137"><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">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">SELECT</span> myvar<span class="token punctuation">;</span></code></pre>
        </div>
        <p>
         This is rewritten as follows:
        </p>
        <div class="copytoclipboard-wrapper">
         <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa69061401"><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">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">SELECT</span> <span class="token function">NAME_CONST</span><span class="token punctuation">(</span>myvar<span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
        </div>
        <p>
         To ensure that the source and replica tables have the same
              column names, write the statement like this:
        </p>
        <div class="copytoclipboard-wrapper">
         <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa10464233"><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">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">SELECT</span> myvar <span class="token keyword">AS</span> myvar<span class="token punctuation">;</span></code></pre>
        </div>
        <p>
         The rewritten statement becomes:
        </p>
        <div class="copytoclipboard-wrapper">
         <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa82588595"><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">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">SELECT</span> <span class="token function">NAME_CONST</span><span class="token punctuation">(</span>myvar<span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> myvar<span class="token punctuation">;</span></code></pre>
        </div>
       </li>
       <li class="listitem">
        <p>
         A statement to be logged might contain references to
              user-defined variables. To handle this, MySQL writes a
         <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
          <code class="literal">
           SET
          </code>
         </a>
         statement to the binary log to make sure that the variable
              exists on the replica with the same value as on the
              source. For example, if a statement refers to a variable
         <code class="literal">
          @my_var
         </code>
         , that statement is preceded in
              the binary log by the following statement, where
         <em class="replaceable">
          <code>
           value
          </code>
         </em>
         is the value of
         <code class="literal">
          @my_var
         </code>
         on the source:
        </p>
        <div class="copytoclipboard-wrapper">
         <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa36359258"><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> <span class="token variable">@my_var</span> <span class="token operator">=</span> <span class="token keyword"><em class="replaceable">value</em></span><span class="token punctuation">;</span></code></pre>
        </div>
       </li>
       <li class="listitem">
        <p>
         Procedure calls can occur within a committed or
              rolled-back transaction. Transactional context is
              accounted for so that the transactional aspects of
              procedure execution are replicated correctly. That is, the
              server logs those statements within the procedure that
              actually execute and modify data, and also logs
         <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
          <code class="literal">
           BEGIN
          </code>
         </a>
         ,
         <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
          <code class="literal">
           COMMIT
          </code>
         </a>
         , and
         <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
          <code class="literal">
           ROLLBACK
          </code>
         </a>
         statements as necessary. For example, if a procedure
              updates only transactional tables and is executed within a
              transaction that is rolled back, those updates are not
              logged. If the procedure occurs within a committed
              transaction,
         <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
          <code class="literal">
           BEGIN
          </code>
         </a>
         and
         <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
          <code class="literal">
           COMMIT
          </code>
         </a>
         statements are
              logged with the updates. For a procedure that executes
              within a rolled-back transaction, its statements are
              logged using the same rules that would apply if the
              statements were executed in standalone fashion:
        </p>
        <div class="itemizedlist">
         <ul class="itemizedlist" style="list-style-type: square; ">
          <li class="listitem">
           <p>
            Updates to transactional tables are not logged.
           </p>
          </li>
          <li class="listitem">
           <p>
            Updates to nontransactional tables are logged because
                  rollback does not cancel them.
           </p>
          </li>
          <li class="listitem">
           <p>
            Updates to a mix of transactional and nontransactional
                  tables are logged surrounded by
            <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
             <code class="literal">
              BEGIN
             </code>
            </a>
            and
            <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
             <code class="literal">
              ROLLBACK
             </code>
            </a>
            so that replicas make the same changes and rollbacks
                  as on the source.
           </p>
          </li>
         </ul>
        </div>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      A stored procedure call is
      <span class="emphasis">
       <em>
        not
       </em>
      </span>
      written to
          the binary log at the statement level if the procedure is
          invoked from within a stored function. In that case, the only
          thing logged is the statement that invokes the function (if it
          occurs within a statement that is logged) or a
      <a class="link" href="do.html" title="15.2.3 DO Statement">
       <code class="literal">
        DO
       </code>
      </a>
      statement (if it occurs
          within a statement that is not logged). For this reason, care
          should be exercised in the use of stored functions that invoke
          a procedure, even if the procedure is otherwise safe in
          itself.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
