3

When you have a MySQL statement-based replication and you create a stored procedure that modify some rows (INSERT, UPDATE, DELETE commands...), What is really replicated on the slave when you call this procedure on the master ? The CALL himself or the commands (INSERT, UPDATE, DELETE...) inside the procedure ?

For example, create such a procedure:

delimiter |

CREATE PROCEDURE myproc ()
      BEGIN

        DELETE FROM mytable LIMIT 1;

      END |

delimiter ;

Then call on the master:

master> CALL myproc();

=> does it replicates "CALL myproc()" or "DELETE FROM mytable LIMIT 1" ?

1 Answer 1

2

In short, no, your CALL is not replicated

You can take a look into the FAQ starting from B.4.22 and the Binary Logging of Stored Programs

B.4.23: 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.

B.4.24: 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.

4
  • +1, but as the docs say, for stored functions there COULD be a call. Apr 18, 2013 at 5:17
  • @ShlomiNoach, I can't find where the slave "for stored functions there COULD be a call" Apr 18, 2013 at 5:52
  • Sorry, that wasn't a quote. This is the quote: "Stored functions that change data are logged as function invocations, not as the DML events that occur inside each function.". Apr 18, 2013 at 11:22
  • So in the case of a function that modifies data, what replicates is the CALL to the function, not the function itself. But if same function does not modify data, then the CALL does not replicate. Apr 18, 2013 at 11:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.