Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
+1, but as the docs say, for stored functions there COULD be a call. –  Shlomi Noach Apr 18 '13 at 5:17
    
@ShlomiNoach, I can't find where the slave "for stored functions there COULD be a call" –  Cristian Porta Apr 18 '13 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.". –  Shlomi Noach Apr 18 '13 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. –  Shlomi Noach Apr 18 '13 at 11:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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