Statement based replication writes the queries that modify data in the Binary Log to replicate them on the slave or to use it as a PITR recovery. Here we will see what is the behavior of the MySQL when it needs to log “not usual” queries like Events, Functions, Stored Procedures, Local Variables, etc. We’ll learn what problems can we have and how to avoid them.
When a statement activates a Trigger only the original query is logged not the subsequent triggered statements. If you want to maintain the consistency of your data is necessary to define the same Triggers in Master and Slave servers.
mysql> create trigger Copy_data AFTER INSERT on t FOR EACH ROW INSERT INTO t_copy VALUE(NEW.i);
mysql> insert into t VALUES(1),(2),(3);
#111213 23:16:21 server id 1 end_log_pos 269 Query thread_id=3 exec_time=0 error_code=0
insert into t VALUES(1),(2),(3)
This behavior can be a problem to take in account or in some other cases help us in our infrastructure. Is possible to define different Triggers in your replication servers if you need different actions for the same statements.
Calls to functions are logged directly on the Binary Log. Therefore if you don’t have all functions created on all your servers you will break your replication and the SQL process will stop with an error.
mysql> CREATE FUNCTION this_year() RETURNS INT DETERMINISTIC RETURN YEAR(CURDATE());
mysql> insert into t VALUES(this_year());
#111213 23:25:46 server id 1 end_log_pos 676 Query thread_id=5 exec_time=0 error_code=0
insert into t VALUES(this_year())
If you forget to create the function on your slave the replication will be broken. After executing a SHOW SLAVE STATUS you will see a message like the following one:
Last_Error: Error 'FUNCTION test.this_year does not exist' on query. Default database: 'test'. Query: 'insert into t VALUES(this_year())'
The behaviour of stored procedures and functions are completely different. If our stored procedure write data to our tables the queries inside the procedure get logged and not the call to the procedure itself. So in this case you don’t need to replicate all your stored procedures in your slaves servers.
mysql> create procedure this_pyear() BEGIN INSERT INTO t VALUES(YEAR(CURDATE())); END;//
mysql> CALL this_pyear();
#111213 23:33:41 server id 1 end_log_pos 2055 Query thread_id=6 exec_time=0 error_code=0
INSERT INTO t VALUES(YEAR(CURDATE()))
We’re going to do it a little bit more difficult:
Our procedure modifies a table that has a associated ON INSERT trigger and we use the previous function to insert the present year. Following the previous rules that we explained before is easy to imagine. The query inside the procedure gets logged with the explicit call to the Function but not the statement that the trigger executed.
We have a trigger that calls a the procedure. In this case neither the trigger, the CALL or the queries inside the procedure that modifies data are logged. So in this case you need to manually create the trigger and procedure on the slave servers.
When we create an Event on the Master server it gets replicated to the slave with the DISABLE ON SLAVE option. Thanks to that the Event is not executed N times for every slave we have in our infrastructure and we won’t duplicate data. The statements inside the Event are logged and not the Event itself.
mysql> create event year ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO INSERT INTO t VALUES(YEAR(CURDATE()));
First the event gets logged to the Binary Log:
CREATE DEFINER=`msandbox`@`localhost` event year ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO INSERT INTO t VALUES(YEAR(CURDATE()))
After one hour the event gets executed. The insert is logged and then re-executed on the slave server so is not necessary to have the events enabled on all servers:
#111214 0:51:54 server id 1 end_log_pos 1593 Query thread_id=18 exec_time=0 error_code=0
INSERT INTO t VALUES(YEAR(CURDATE()))
If later we need to promote a Slave to Master we will need to do some steps to enable all the events replicated from the master because as we saw they’re disabled by default. Here we can see the previous event after it gets replicated on the slave:
CREATE DEFINER=`msandbox`@`localhost` EVENT `year` ON SCHEDULE AT '2011-12-14 22:03:06' ON COMPLETION NOT PRESERVE DISABLE ON SLAVE DO INSERT INTO t VALUES(YEAR(CURDATE()));
These are the steps we need to do to enable on the promoted slave.
- We should disable the event manager on Slave with SET GLOBAL event_scheduler = OFF;
- ENABLE every event with “ALTER EVENT event_name ENABLE”
- Enable again the event_scheduler.
To demote a server we need to follow the same previous steps but in this case we alter every event adding DISABLE ON SLAVE. ALTER EVENT even_name DISABLE ON SLAVE.
Local Variables are logged as an extra events before the statement itself. So all used variables are replicated on the slave.
mysql> SELECT YEAR(CURDATE()) INTO @this_year;
mysql> insert into t VALUES(@this_year)
#111213 23:58:11 server id 1 end_log_pos 457 User_var
# at 457
#111213 23:58:11 server id 1 end_log_pos 552 Query thread_id=13 exec_time=0 error_code=0
insert into t VALUES(@this_year)
Is important to add a note here. There is a bug related with this described on https://bugs.launchpad.net/percona-server/+bug/860910. In a master-master setup a master can show a wrong ‘SHOW SLAVE STATUS’ output when using SET user-variables and then using it to perform writes. The issue is fixed only in Percona server 5.5.17-22.1.
AUTO INCREMENTAL VALUES
In order to have the same auto incremental values on master and slaves the actual used auto incremental value is logged as an extra event just before the statement. MySQL uses the INSERT_ID variable to store that value.
#111214 0:03:04 server id 1 end_log_pos 811 Intvar
# at 811
#111214 0:03:04 server id 1 end_log_pos 905 Query thread_id=14 exec_time=0 error_code=0
INSERT INTO y (value) VALUES(1)
If we use “LAST_INSERT_ID” on our master the value of that function is logged as a variable on the binary log so the slave can use the same value.
#111214 0:04:50 server id 1 end_log_pos 1245 Intvar
#111214 0:04:50 server id 1 end_log_pos 1273 Intvar
#111214 0:04:50 server id 1 end_log_pos 1382 Query thread_id=15 exec_time=0 error_code=0
INSERT INTO y (value) VALUES(LAST_INSERT_ID())
That was for a insert with only one value. What is the behavior if we insert multiple values and we have gaps?
Imagine the table has this values on the auto incremental column: 1,2,3,5
Then we execute the following:
insert into y (i,value) VALUES(4,1),(NULL,1),(NULL,1);
The value 4 is inserted with the INSERT statement and the INSERT_ID is the next auto incremental value:
#111214 21:54:06 server id 1 end_log_pos 203 Intvar
#111214 21:54:06 server id 1 end_log_pos 319 Query thread_id=12 exec_time=0
insert into y (i,value) VALUES(4,1),(NULL,1),(NULL,1)
As we can see there are a lot of things to take in account when we are working with statement based replication. Knowing in advances how MySQL works internally can help us to improve our replication availability and data.