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" ?

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
4
  • +1, but as the docs say, for stored functions there COULD be a call. Commented Apr 18, 2013 at 5:17
  • @ShlomiNoach, I can't find where the slave "for stored functions there COULD be a call" Commented 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.". Commented 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. Commented Apr 18, 2013 at 11:23

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.