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.

TRIGGERS

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.

Example:

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);

Binary Log:

#111213 23:16:21 server id 1 end_log_pos 269 Query thread_id=3 exec_time=0 error_code=0
use test/*!*/;
SET TIMESTAMP=1323814581/*!*/;
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.

FUNCTIONS

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.

Example:

mysql> CREATE FUNCTION this_year() RETURNS INT DETERMINISTIC RETURN YEAR(CURDATE());
mysql> insert into t VALUES(this_year());

Binary Log:

#111213 23:25:46 server id 1 end_log_pos 676 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1323815146/*!*/;
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())'

STORED PROCEDURES

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.

Example:

mysql> create procedure this_pyear() BEGIN INSERT INTO t VALUES(YEAR(CURDATE())); END;//
mysql> CALL this_pyear();

Binary Log:

#111213 23:33:41 server id 1 end_log_pos 2055 Query thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1323815621/*!*/;
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.

EVENTS

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.

Example:

mysql> create event year ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO INSERT INTO t VALUES(YEAR(CURDATE()));

Binary Logs:

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
SET TIMESTAMP=1323820314/*!*/;
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

Local Variables are logged as an extra events before the statement itself. So all used variables are replicated on the slave.

Example:

mysql> SELECT YEAR(CURDATE()) INTO @this_year;
mysql> insert into t VALUES(@this_year)

Binary Log:

#111213 23:58:11 server id 1 end_log_pos 457 User_var
SET @this_year:=2011/*!*/;
# at 457
#111213 23:58:11 server id 1 end_log_pos 552 Query thread_id=13 exec_time=0 error_code=0
SET TIMESTAMP=1323817091/*!*/;
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.

Binary Log:

#111214 0:03:04 server id 1 end_log_pos 811 Intvar
SET INSERT_ID=1/*!*/;
# at 811
#111214 0:03:04 server id 1 end_log_pos 905 Query thread_id=14 exec_time=0 error_code=0
SET TIMESTAMP=1323817384/*!*/;
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.

Binary Log:

#111214 0:04:50 server id 1 end_log_pos 1245 Intvar
SET LAST_INSERT_ID=2/*!*/;
#111214 0:04:50 server id 1 end_log_pos 1273 Intvar
SET INSERT_ID=3/*!*/;
#111214 0:04:50 server id 1 end_log_pos 1382 Query thread_id=15 exec_time=0 error_code=0
SET TIMESTAMP=1323817490/*!*/;
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?

Example:
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);

Binary Log:

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
SET INSERT_ID=6/*!*/;
#111214 21:54:06 server id 1 end_log_pos 319 Query thread_id=12 exec_time=0
SET TIMESTAMP=1323896046/*!*/;
insert into y (i,value) VALUES(4,1),(NULL,1),(NULL,1)

Conclusion

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.

5 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
huarong

#111214 0:03:04 server id 1 end_log_pos 811 Intvar
SET INSERT_ID=1/*!*/;
# at 811
#111214 0:03:04 server id 1 end_log_pos 905 Query thread_id=14 exec_time=0 error_code=0
SET TIMESTAMP=1323817384/*!*/;
INSERT INTO y (value) VALUES(1)

====================================
INSERT INTO y (value) VALUES(1) did NOT generate “SET INSERT_ID=1/*!*/;” in my server 5.5.15-log 。

INSERT INTO y (value) VALUES(NULL) will do that.

0
chris stephens

Excellent post. I was only aware of data divergence issues with non-deterministic SQL. Are there situations with row-based replication that can lead to data divergence as well?

0
Miguel Angel Nieto

@chris With row based replication you shouldn’t have problems with all the statements explained in this post. On the other hand you can have other problems with the row based replication that you can’t have with statement. For example, you can have lots of problems if the charset of a CHAR column is different between the Master and Slave 🙂

0
Ashqar Mahi

I have configured Mysql master-slave replication and it is working fine. But I am facing a little issue regarding replication.

I have used Stored Procedure in one of my database to pull data and push it into the master database table. at this point replication is not working. no data is replicating when data is inserted through SP. otherwise replication is working fine(if i manually insert data into master database table than it replicated).

Need help/suggestion

0
Deepak Mendiratta

I am using row based replication, but when i am creating a stored procedure on the master DB, it is getting created on the slave DB as well. Can anyone please help me how can i restrict stored procedure/functions to be created on the slave DB. this is what i have in my.cnf file.

[mysqld]
binlog_format = row

0