<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="faqs-stored-procs">
      </a>
      A.4 MySQL 8.4 FAQ: Stored Procedures and Functions
     </h2>
    </div>
   </div>
  </div>
  <div class="qandaset">
   <a name="idm46045056374464">
   </a>
   <dl>
    <dt>
     A.4.1.
     <a href="faqs-stored-procs.html#faq-mysql-have-procedures-functions">
      Does MySQL support stored procedures and functions?
     </a>
    </dt>
    <dt>
     A.4.2.
     <a href="faqs-stored-procs.html#faq-mysql-where-procedures-functions-docs">
      Where can I find documentation for MySQL stored procedures and
        stored functions?
     </a>
    </dt>
    <dt>
     A.4.3.
     <a href="faqs-stored-procs.html#faq-mysql-where-procedures-forum">
      Is there a discussion forum for MySQL stored procedures?
     </a>
    </dt>
    <dt>
     A.4.4.
     <a href="faqs-stored-procs.html#faq-mysql-where-ansi-2003-spec">
      Where can I find the ANSI SQL 2003 specification for stored
        procedures?
     </a>
    </dt>
    <dt>
     A.4.5.
     <a href="faqs-stored-procs.html#faq-mysql-how-manage-routines">
      How do you manage stored routines?
     </a>
    </dt>
    <dt>
     A.4.6.
     <a href="faqs-stored-procs.html#faq-mysql-how-view-procedures-functions">
      Is there a way to view all stored procedures and stored
        functions in a given database?
     </a>
    </dt>
    <dt>
     A.4.7.
     <a href="faqs-stored-procs.html#faq-mysql-where-procedures-stored">
      Where are stored procedures stored?
     </a>
    </dt>
    <dt>
     A.4.8.
     <a href="faqs-stored-procs.html#faq-mysql-how-group-procedures-functions">
      Is it possible to group stored procedures or stored functions
        into packages?
     </a>
    </dt>
    <dt>
     A.4.9.
     <a href="faqs-stored-procs.html#faq-mysql-can-procedure-call-procedure">
      Can a stored procedure call another stored procedure?
     </a>
    </dt>
    <dt>
     A.4.10.
     <a href="faqs-stored-procs.html#faq-mysql-can-procedure-call-trigger">
      Can a stored procedure call a trigger?
     </a>
    </dt>
    <dt>
     A.4.11.
     <a href="faqs-stored-procs.html#faq-mysql-can-procedure-access-table">
      Can a stored procedure access tables?
     </a>
    </dt>
    <dt>
     A.4.12.
     <a href="faqs-stored-procs.html#faq-mysql-can-procedure-raise-error">
      Do stored procedures have a statement for raising application
        errors?
     </a>
    </dt>
    <dt>
     A.4.13.
     <a href="faqs-stored-procs.html#faq-mysql-have-exceptions">
      Do stored procedures provide exception handling?
     </a>
    </dt>
    <dt>
     A.4.14.
     <a href="faqs-stored-procs.html#faq-mysql-can-routine-results">
      Can MySQL stored routines return result sets?
     </a>
    </dt>
    <dt>
     A.4.15.
     <a href="faqs-stored-procs.html#faq-mysql-have-with-recompile">
      Is WITH RECOMPILE supported for stored
        procedures?
     </a>
    </dt>
    <dt>
     A.4.16.
     <a href="faqs-stored-procs.html#faq-mysql-have-mod-plsql">
      Is there a MySQL equivalent to using
        mod_plsql as a gateway on Apache to talk
        directly to a stored procedure in the database?
     </a>
    </dt>
    <dt>
     A.4.17.
     <a href="faqs-stored-procs.html#faq-mysql-can-procedure-array">
      Can I pass an array as input to a stored procedure?
     </a>
    </dt>
    <dt>
     A.4.18.
     <a href="faqs-stored-procs.html#faq-mysql-can-pass-cursor-in">
      Can I pass a cursor as an IN parameter to a
        stored procedure?
     </a>
    </dt>
    <dt>
     A.4.19.
     <a href="faqs-stored-procs.html#faq-mysql-can-return-cursor-out">
      Can I return a cursor as an OUT parameter
        from a stored procedure?
     </a>
    </dt>
    <dt>
     A.4.20.
     <a href="faqs-stored-procs.html#faq-mysql-can-print-var-in-procedure">
      Can I print out a variable's value within a stored routine for
        debugging purposes?
     </a>
    </dt>
    <dt>
     A.4.21.
     <a href="faqs-stored-procs.html#faq-mysql-can-rollback-transaction-procedure">
      Can I commit or roll back transactions inside a stored
        procedure?
     </a>
    </dt>
    <dt>
     A.4.22.
     <a href="faqs-stored-procs.html#faq-mysql-can-procedures-replicatation">
      Do MySQL stored procedures and functions work with replication?
     </a>
    </dt>
    <dt>
     A.4.23.
     <a href="faqs-stored-procs.html#faq-mysql-are-procedures-replicated">
      Are stored procedures and functions created on a replication
        source server replicated to a replica?
     </a>
    </dt>
    <dt>
     A.4.24.
     <a href="faqs-stored-procs.html#faq-mysql-how-procedures-replicated">
      How are actions that take place inside stored procedures and
        functions replicated?
     </a>
    </dt>
    <dt>
     A.4.25.
     <a href="faqs-stored-procs.html#faq-mysql-security-procedures-replication">
      Are there special security requirements for using stored
        procedures and functions together with replication?
     </a>
    </dt>
    <dt>
     A.4.26.
     <a href="faqs-stored-procs.html#faq-mysql-limitations-procedures-replication">
      What limitations exist for replicating stored procedure and
        function actions?
     </a>
    </dt>
    <dt>
     A.4.27.
     <a href="faqs-stored-procs.html#faq-mysql-limitations-pit-recovery">
      Do the preceding limitations affect the ability of MySQL to do
        point-in-time recovery?
     </a>
    </dt>
    <dt>
     A.4.28.
     <a href="faqs-stored-procs.html#faq-mysql-when-limitations-resolved">
      What is being done to correct the aforementioned limitations?
     </a>
    </dt>
   </dl>
   <table border="0" style="width: 100%;">
    <colgroup>
     <col align="left" width="1%"/>
     <col/>
    </colgroup>
    <tbody>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-mysql-have-procedures-functions">
       </a>
       <a name="idm46045056373712">
       </a>
       <p>
        <b>
         A.4.1.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        Does MySQL support stored procedures and functions?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        Yes. MySQL supports two types of stored routines, stored
        procedures, and stored functions.
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-mysql-where-procedures-functions-docs">
       </a>
       <a name="idm46045056371536">
       </a>
       <p>
        <b>
         A.4.2.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        Where can I find documentation for MySQL stored procedures and
        stored functions?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        See
        <a class="xref" href="stored-routines.html" title="27.2 Using Stored Routines">
         Section 27.2, “Using Stored Routines”
        </a>
        .
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-mysql-where-procedures-forum">
       </a>
       <a name="idm46045056368832">
       </a>
       <p>
        <b>
         A.4.3.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        Is there a discussion forum for MySQL stored procedures?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        Yes. See
        <a class="ulink" href="https://forums.mysql.com/list.php?98" target="_blank">
         https://forums.mysql.com/list.php?98
        </a>
        .
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-mysql-where-ansi-2003-spec">
       </a>
       <a name="idm46045056366080">
       </a>
       <p>
        <b>
         A.4.4.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        Where can I find the ANSI SQL 2003 specification for stored
        procedures?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        Unfortunately, the official specifications are not freely
        available (ANSI makes them available for purchase). However,
        there are books, such as
        <em class="citetitle">
         SQL-99 Complete,
        Really
        </em>
        by Peter Gulutzan and Trudy Pelzer, that
        provide a comprehensive overview of the standard, including
        coverage of stored procedures.
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-mysql-how-manage-routines">
       </a>
       <a name="idm46045056363248">
       </a>
       <p>
        <b>
         A.4.5.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        How do you manage stored routines?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        It is always good practice to use a clear naming scheme for your
        stored routines. You can manage stored procedures with
        <code class="literal">
         CREATE [FUNCTION|PROCEDURE]
        </code>
        ,
        <code class="literal">
         ALTER
        [FUNCTION|PROCEDURE]
        </code>
        ,
        <code class="literal">
         DROP
        [FUNCTION|PROCEDURE]
        </code>
        , and
        <code class="literal">
         SHOW CREATE
        [FUNCTION|PROCEDURE]
        </code>
        . You can obtain information about
        existing stored procedures using the
        <a class="link" href="information-schema-routines-table.html" title="28.3.30 The INFORMATION_SCHEMA ROUTINES Table">
         <code class="literal">
          ROUTINES
         </code>
        </a>
        table in the
        <code class="literal">
         INFORMATION_SCHEMA
        </code>
        database (see
        <a class="xref" href="information-schema-routines-table.html" title="28.3.30 The INFORMATION_SCHEMA ROUTINES Table">
         Section 28.3.30, “The INFORMATION_SCHEMA ROUTINES Table”
        </a>
        ).
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-mysql-how-view-procedures-functions">
       </a>
       <a name="idm46045056355504">
       </a>
       <p>
        <b>
         A.4.6.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        Is there a way to view all stored procedures and stored
        functions in a given database?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        Yes. For a database named
        <em class="replaceable">
         <code>
          dbname
         </code>
        </em>
        , use
        this query on the
        <a class="link" href="information-schema-routines-table.html" title="28.3.30 The INFORMATION_SCHEMA ROUTINES Table">
         <code class="literal">
          INFORMATION_SCHEMA.ROUTINES
         </code>
        </a>
        table:
       </p>
       <div class="copytoclipboard-wrapper">
        <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa3645708"><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> ROUTINE_TYPE<span class="token punctuation">,</span> ROUTINE_NAME
    <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>ROUTINES
    <span class="token keyword">WHERE</span> ROUTINE_SCHEMA<span class="token operator">=</span><span class="token string">'<em class="replaceable">dbname</em>'</span><span class="token punctuation">;</span></code></pre>
       </div>
       <p>
        For more information, see
        <a class="xref" href="information-schema-routines-table.html" title="28.3.30 The INFORMATION_SCHEMA ROUTINES Table">
         Section 28.3.30, “The INFORMATION_SCHEMA ROUTINES Table”
        </a>
        .
       </p>
       <p>
        The body of a stored routine can be viewed using
        <a class="link" href="show-create-function.html" title="15.7.7.9 SHOW CREATE FUNCTION Statement">
         <code class="literal">
          SHOW CREATE FUNCTION
         </code>
        </a>
        (for a
        stored function) or
        <a class="link" href="show-create-procedure.html" title="15.7.7.10 SHOW CREATE PROCEDURE Statement">
         <code class="literal">
          SHOW CREATE
        PROCEDURE
         </code>
        </a>
        (for a stored procedure). See
        <a class="xref" href="show-create-procedure.html" title="15.7.7.10 SHOW CREATE PROCEDURE Statement">
         Section 15.7.7.10, “SHOW CREATE PROCEDURE Statement”
        </a>
        , for more information.
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-mysql-where-procedures-stored">
       </a>
       <a name="idm46045056345376">
       </a>
       <p>
        <b>
         A.4.7.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        Where are stored procedures stored?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        Stored procedures are stored in the
        <code class="literal">
         mysql.routines
        </code>
        and
        <code class="literal">
         mysql.parameters
        </code>
        tables, which are part of
        the data dictionary. You cannot access these tables directly.
        Instead, query the
        <code class="literal">
         INFORMATION_SCHEMA
        </code>
        <a class="link" href="information-schema-routines-table.html" title="28.3.30 The INFORMATION_SCHEMA ROUTINES Table">
         <code class="literal">
          ROUTINES
         </code>
        </a>
        and
        <a class="link" href="information-schema-parameters-table.html" title="28.3.20 The INFORMATION_SCHEMA PARAMETERS Table">
         <code class="literal">
          PARAMETERS
         </code>
        </a>
        tables. See
        <a class="xref" href="information-schema-routines-table.html" title="28.3.30 The INFORMATION_SCHEMA ROUTINES Table">
         Section 28.3.30, “The INFORMATION_SCHEMA ROUTINES Table”
        </a>
        , and
        <a class="xref" href="information-schema-parameters-table.html" title="28.3.20 The INFORMATION_SCHEMA PARAMETERS Table">
         Section 28.3.20, “The INFORMATION_SCHEMA PARAMETERS Table”
        </a>
        .
       </p>
       <p>
        You can also use
        <a class="link" href="show-create-function.html" title="15.7.7.9 SHOW CREATE FUNCTION Statement">
         <code class="literal">
          SHOW CREATE
        FUNCTION
         </code>
        </a>
        to obtain information about stored functions,
        and
        <a class="link" href="show-create-procedure.html" title="15.7.7.10 SHOW CREATE PROCEDURE Statement">
         <code class="literal">
          SHOW CREATE PROCEDURE
         </code>
        </a>
        to
        obtain information about stored procedures. See
        <a class="xref" href="show-create-procedure.html" title="15.7.7.10 SHOW CREATE PROCEDURE Statement">
         Section 15.7.7.10, “SHOW CREATE PROCEDURE Statement”
        </a>
        .
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-mysql-how-group-procedures-functions">
       </a>
       <a name="idm46045056333456">
       </a>
       <p>
        <b>
         A.4.8.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        Is it possible to group stored procedures or stored functions
        into packages?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        No. This is not supported in MySQL.
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-mysql-can-procedure-call-procedure">
       </a>
       <a name="idm46045056331376">
       </a>
       <p>
        <b>
         A.4.9.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        Can a stored procedure call another stored procedure?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        Yes.
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-mysql-can-procedure-call-trigger">
       </a>
       <a name="idm46045056329408">
       </a>
       <p>
        <b>
         A.4.10.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        Can a stored procedure call a trigger?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        A stored procedure can execute an SQL statement, such as an
        <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
         <code class="literal">
          UPDATE
         </code>
        </a>
        , that causes a trigger to
        activate.
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-mysql-can-procedure-access-table">
       </a>
       <a name="idm46045056326128">
       </a>
       <p>
        <b>
         A.4.11.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        Can a stored procedure access tables?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        Yes. A stored procedure can access one or more tables as
        required.
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-mysql-can-procedure-raise-error">
       </a>
       <a name="idm46045056324032">
       </a>
       <p>
        <b>
         A.4.12.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        Do stored procedures have a statement for raising application
        errors?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        Yes. MySQL implements the SQL standard
        <code class="literal">
         SIGNAL
        </code>
        and
        <code class="literal">
         RESIGNAL
        </code>
        statements. See
        <a class="xref" href="condition-handling.html" title="15.6.7 Condition Handling">
         Section 15.6.7, “Condition Handling”
        </a>
        .
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-mysql-have-exceptions">
       </a>
       <a name="idm46045056319872">
       </a>
       <p>
        <b>
         A.4.13.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        Do stored procedures provide exception handling?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        MySQL implements
        <a class="link" href="handler.html" title="15.2.5 HANDLER Statement">
         <code class="literal">
          HANDLER
         </code>
        </a>
        definitions according to the SQL standard. See
        <a class="xref" href="declare-handler.html" title="15.6.7.2 DECLARE ... HANDLER Statement">
         Section 15.6.7.2, “DECLARE ... HANDLER Statement”
        </a>
        , for details.
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-mysql-can-routine-results">
       </a>
       <a name="idm46045056315936">
       </a>
       <p>
        <b>
         A.4.14.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        Can MySQL stored routines return result sets?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        <span class="emphasis">
         <em>
          Stored procedures
         </em>
        </span>
        can, but stored functions
        cannot. If you perform an ordinary
        <a class="link" href="select.html" title="15.2.13 SELECT Statement">
         <code class="literal">
          SELECT
         </code>
        </a>
        inside a stored procedure,
        the result set is returned directly to the client. You need to
        use the MySQL 4.1 (or higher) client/server protocol for this to
        work. This means that, for example, in PHP, you need to use the
        <code class="literal">
         mysqli
        </code>
        extension rather than the old
        <code class="literal">
         mysql
        </code>
        extension.
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-mysql-have-with-recompile">
       </a>
       <a name="idm46045056310560">
       </a>
       <p>
        <b>
         A.4.15.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        Is
        <code class="literal">
         WITH RECOMPILE
        </code>
        supported for stored
        procedures?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        No.
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-mysql-have-mod-plsql">
       </a>
       <a name="idm46045056307888">
       </a>
       <p>
        <b>
         A.4.16.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        Is there a MySQL equivalent to using
        <code class="literal">
         mod_plsql
        </code>
        as a gateway on Apache to talk
        directly to a stored procedure in the database?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        There is no equivalent in MySQL.
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-mysql-can-procedure-array">
       </a>
       <a name="idm46045056305104">
       </a>
       <p>
        <b>
         A.4.17.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        Can I pass an array as input to a stored procedure?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        No.
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-mysql-can-pass-cursor-in">
       </a>
       <a name="idm46045056303136">
       </a>
       <p>
        <b>
         A.4.18.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        Can I pass a cursor as an
        <code class="literal">
         IN
        </code>
        parameter to a
        stored procedure?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        Cursors are only available inside stored procedures.
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-mysql-can-return-cursor-out">
       </a>
       <a name="idm46045056300432">
       </a>
       <p>
        <b>
         A.4.19.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        Can I return a cursor as an
        <code class="literal">
         OUT
        </code>
        parameter
        from a stored procedure?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        Cursors are only available inside stored procedures. However, if
        you do not open a cursor on a
        <a class="link" href="select.html" title="15.2.13 SELECT Statement">
         <code class="literal">
          SELECT
         </code>
        </a>
        , the result is sent
        directly to the client. You can also
        <code class="literal">
         SELECT
        INTO
        </code>
        variables. See
        <a class="xref" href="select.html" title="15.2.13 SELECT Statement">
         Section 15.2.13, “SELECT Statement”
        </a>
        .
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-mysql-can-print-var-in-procedure">
       </a>
       <a name="idm46045056294992">
       </a>
       <p>
        <b>
         A.4.20.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        Can I print out a variable's value within a stored routine for
        debugging purposes?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        Yes, you can do this in a
        <span class="emphasis">
         <em>
          stored procedure
         </em>
        </span>
        ,
        but not in a stored function. If you perform an ordinary
        <a class="link" href="select.html" title="15.2.13 SELECT Statement">
         <code class="literal">
          SELECT
         </code>
        </a>
        inside a stored procedure,
        the result set is returned directly to the client. You must use
        the MySQL 4.1 (or above) client/server protocol for this to
        work. This means that, for example, in PHP, you need to use the
        <code class="literal">
         mysqli
        </code>
        extension rather than the old
        <code class="literal">
         mysql
        </code>
        extension.
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-mysql-can-rollback-transaction-procedure">
       </a>
       <a name="idm46045056289472">
       </a>
       <p>
        <b>
         A.4.21.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        Can I commit or roll back transactions inside a stored
        procedure?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        Yes. However, you cannot perform transactional operations within
        a stored function.
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-mysql-can-procedures-replicatation">
       </a>
       <a name="idm46045056287392">
       </a>
       <p>
        <b>
         A.4.22.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        Do MySQL stored procedures and functions work with replication?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        Yes, standard actions carried out in stored procedures and
        functions are replicated from a replication source server to a
        replica. There are a few limitations that are described in
        detail in
        <a class="xref" href="stored-programs-logging.html" title="27.7 Stored Program Binary Logging">
         Section 27.7, “Stored Program Binary Logging”
        </a>
        .
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-mysql-are-procedures-replicated">
       </a>
       <a name="idm46045056284464">
       </a>
       <p>
        <b>
         A.4.23.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        Are stored procedures and functions created on a replication
        source server replicated to a replica?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        Yes, creation of stored procedures and functions carried out
        through normal DDL statements on a replication source server are
        replicated to a replica, so that the objects exist on both
        servers.
        <code class="literal">
         ALTER
        </code>
        and
        <code class="literal">
         DROP
        </code>
        statements for stored procedures and functions are also
        replicated.
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-mysql-how-procedures-replicated">
       </a>
       <a name="idm46045056280640">
       </a>
       <p>
        <b>
         A.4.24.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        How are actions that take place inside stored procedures and
        functions replicated?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        MySQL records each DML event that occurs in a stored procedure
        and replicates those individual actions to a replica. The actual
        calls made to execute stored procedures are not replicated.
       </p>
       <p>
        Stored functions that change data are logged as function
        invocations, not as the DML events that occur inside each
        function.
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-mysql-security-procedures-replication">
       </a>
       <a name="idm46045056277744">
       </a>
       <p>
        <b>
         A.4.25.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        Are there special security requirements for using stored
        procedures and functions together with replication?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        Yes. Because a replica has authority to execute any statement
        read from a source's binary log, special security constraints
        exist for using stored functions with replication. If
        replication or binary logging in general (for the purpose of
        point-in-time recovery) is active, then MySQL DBAs have two
        security options open to them:
       </p>
       <div class="orderedlist">
        <ol class="orderedlist" type="1">
         <li class="listitem">
          <p>
           Any user wishing to create stored functions must be
              granted the
           <a class="link" href="privileges-provided.html#priv_super">
            <code class="literal">
             SUPER
            </code>
           </a>
           privilege.
          </p>
         </li>
         <li class="listitem">
          <p>
           Alternatively, a DBA can set the
           <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, which enables anyone with the
              standard
           <a class="link" href="privileges-provided.html#priv_create-routine">
            <code class="literal">
             CREATE ROUTINE
            </code>
           </a>
           privilege to create stored functions.
          </p>
         </li>
        </ol>
       </div>
       <p>
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-mysql-limitations-procedures-replication">
       </a>
       <a name="idm46045056268768">
       </a>
       <p>
        <b>
         A.4.26.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        What limitations exist for replicating stored procedure and
        function actions?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        Nondeterministic (random) or time-based actions embedded in
        stored procedures may not replicate properly. By their very
        nature, randomly produced results are not predictable and cannot
        be exactly reproduced; therefore, random actions replicated to a
        replica do not mirror those performed on a source. Declaring
        stored functions to be
        <code class="literal">
         DETERMINISTIC
        </code>
        or
        setting the
        <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 0 keeps random operations producing random
        values from being invoked.
       </p>
       <p>
        In addition, time-based actions cannot be reproduced on a
        replica because the timing of such actions in a stored procedure
        is not reproducible through the binary log used for replication.
        It records only DML events and does not factor in timing
        constraints.
       </p>
       <p>
        Finally, nontransactional tables for which errors occur during
        large DML actions (such as bulk inserts) may experience
        replication issues in that a source may be partially updated
        from DML activity, but no updates are done to the replica
        because of the errors that occurred. A workaround is for a
        function's DML actions to be carried out with the
        <code class="literal">
         IGNORE
        </code>
        keyword so that updates on the source
        that cause errors are ignored and updates that do not cause
        errors are replicated to the replica.
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-mysql-limitations-pit-recovery">
       </a>
       <a name="idm46045056261872">
       </a>
       <p>
        <b>
         A.4.27.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        Do the preceding limitations affect the ability of MySQL to do
        point-in-time recovery?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        The same limitations that affect replication do affect
        point-in-time recovery.
       </p>
      </td>
     </tr>
     <tr class="question">
      <td align="left" valign="top">
       <a name="faq-mysql-when-limitations-resolved">
       </a>
       <a name="idm46045056259712">
       </a>
       <p>
        <b>
         A.4.28.
        </b>
       </p>
      </td>
      <td align="left" valign="top">
       <p>
        What is being done to correct the aforementioned limitations?
       </p>
      </td>
     </tr>
     <tr class="answer">
      <td align="left" valign="top">
      </td>
      <td align="left" valign="top">
       <p>
        You can choose either statement-based replication or row-based
        replication. The original replication implementation is based on
        statement-based binary logging. Row-based binary logging
        resolves the limitations mentioned earlier.
       </p>
       <p>
        <span class="firstterm">
         Mixed
        </span>
        replication is also
        available (by starting the server with
        <a class="link" href="replication-options-binary-log.html#sysvar_binlog_format">
         <code class="option">
          --binlog-format=mixed
         </code>
        </a>
        ). This
        hybrid form of replication
        <span class="quote">
         “
         <span class="quote">
          knows
         </span>
         ”
        </span>
        whether
        statement-level replication can safely be used, or row-level
        replication is required.
       </p>
       <p>
        For additional information, see
        <a class="xref" href="replication-formats.html" title="19.2.1 Replication Formats">
         Section 19.2.1, “Replication Formats”
        </a>
        .
       </p>
      </td>
     </tr>
    </tbody>
   </table>
  </div>
 </div>
 <br/>
</div>
