On MySQL version 5.0, SBR, when no database is selected, data is not replicated to slave. To reproduce: connect to MySQL database without specifying any database, don’t select any database (e.g.: select db1) and do update directly (e.g.: update db1.table1 set x=1). Work around: define/use database on any MySQL connection, eventhough it’s just information_schema
On MySQL version 5.0/1, SBR, MySQL didn’t yet support character collation replication on Stored Procedure. If master and slave has different collation and because the version replication doesn’t include collation, it would fail on slave. Work around: copy the argument into local variable. The slave will do collation conversion and avoid replication stop due to the replication error. E.g.: DELIMITER ;;
CREATE DEFINER=`defineruser`@`%` PROCEDURE `create_account`(IN Code VARCHAR(128))
BEGIN
set @Code=Code; insert into account set code=@Code;
END
On MySQL version 5.5/6, SBR, when there’s a chain replication, e.g. Master -> slave1 -> slave2, sometimes MySQL can’t properly replicate local variable. e.g.: When it’s not broken, the replication binlog may consist:
# at 1058556811
#130509 14:13:03 server id 111 end_log_pos 1058556904 User_var
SET @`Code`:=_utf8 0x433442422…4 COLLATE `utf8_unicode_ci`/*!*/;
It’s only happening on slave2, chained replication. When it’s broken the local variable doesn’t get replicated.
Work around: remove copy to local variable on stored procedure and use argument directly. The previous collation problem on replication has been fixed in this version.
Other than those replication issues that I found, it’s not a good idea to call another stored procedure inside another stored procedure. It’s because MySQL SBR replication can’t maintain variables of a stored procedure running inside another stored procedure. There’s a work around which is using RBR but some may still prefer SBR due to easiness to fix replication problem.]]>