(mysql.info) stored-procedure-replication-faq

Info Catalog

(mysql.info) stored-procedure-syntax

(mysql.info) stored-procedures

(mysql.info) stored-procedure-logging

 
 17.3 Stored Procedures, Functions, Triggers, and Replication: Frequently Asked Questions
 ========================================================================================
 
    * Do MySQL 5.0 stored procedures and functions work with replication?
 
      Yes, standard actions carried out in stored procedures and
      functions are replicated from a master MySQL server to a slave
      server. There are a few limitations that are described in detail
      in  stored-procedure-logging.
 
    * Are stored procedures and functions created on a master server
      replicated to a slave?
 
      Yes, creation of stored procedures and functions carried out
      through normal DDL statements on a master server are replicated to
      a slave, so the objects will exist on both servers. `ALTER' and
      `DROP' statements for stored procedures and functions are also
      replicated.
 
    * How are actions that take place inside stored procedures and
      functions replicated?
 
      MySQL records each DML event that occurs in a stored procedure and
      replicates those individual actions to a slave server. The actual
      calls made to execute stored procedures are not replicated.
 
      Stored functions that change data are logged as function
      invocations, not as the DML events that occur inside each function.
 
    * Are there special security requirements for using stored
      procedures and functions together with replication?
 
      Yes. Because a slave server has authority to execute any statement
      read from a master'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:
 
         * Any user wishing to create stored functions must be granted
           the `SUPER' privilege.
 
         * Alternatively, a DBA can set the
           `log_bin_trust_function_creators' system variable to 1, which
           enables anyone with the standard `CREATE ROUTINE' privilege
           to create stored functions.
 
      Note: Before MySQL 5.0.16, these restrictions also apply to stored
      procedures and the system variable is named
      `log_bin_trust_routine_creators'.
 
    * What limitations exist for replicating stored procedure and
      function actions?
 
      Non-deterministic (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, and therefore, random actions replicated to
      a slave will not mirror those performed on a master. Note that
      declaring stored functions to be `DETERMINISTIC' or setting the
      `log_bin_trust_function_creators' system variable to 0 will not
      allow random-valued operations to be invoked.
 
      In addition, time-based actions cannot be reproduced on a slave
      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.
 
      Finally, non-transactional tables for which errors occur during
      large DML actions (such as bulk inserts) may experience
      replication issues in that a master may be partially updated from
      DML activity, but no updates are done to the slave because of the
      errors that occurred. A workaround is for a function's DML actions
      to be carried out with the `IGNORE' keyword so that updates on the
      master that cause errors are ignored and updates that do not cause
      errors are replicated to the slave.
 
    * Do the preceding limitations affect MySQL's ability to do
      point-in-time recovery?
 
      The same limitations that affect replication do affect
      point-in-time recovery.
 
    * What will MySQL do to correct the aforementioned limitations?
 
      A future release of MySQL is expected to feature a choice in how
      replication should be handled:
 
         * Statement-based replication (current implementation).
 
         * Row-level replication (that will solve all the limitations
           described earlier).
 
    * Do triggers work with replication?
 
      Triggers and replication in MySQL 5.0 work the same as in most
      other database engines: Actions carried out through triggers on a
      master are not replicated to a slave server. Instead, triggers
      that exist on tables that reside on a MySQL master server need to
      be created on the corresponding tables on any MySQL slave servers
      so that the triggers activate on the slaves as well as the master.
 
    * How are actions carried out through triggers on a master
      replicated to a slave?
 
      First, the triggers that exist on a master must be re-created on
      the slave server. Once this is done, the replication flow works as
      any other standard DML statement that participates in replication.
      For example, consider a table `EMP' that has an `AFTER' insert
      trigger, which exists on a master MySQL server. The same `EMP'
      table and `AFTER' insert trigger exist on the slave server as
      well. The replication flow would be:
 
   1. An `INSERT' statement is made to `EMP'.
 
   2. The `AFTER' trigger on `EMP' activates.
 
   3. The `INSERT' statement is written to the binary log.
 
   4. The replication slave picks up the `INSERT' statement to `EMP' and
      executes it.
 
   5. The `AFTER' trigger on `EMP' that exists on the slave activates.
 

Info Catalog

(mysql.info) stored-procedure-syntax

(mysql.info) stored-procedures

(mysql.info) stored-procedure-logging