MySQL Bugs: #40328: slave-side triggers not activated under RBR

Bug #40328	slave-side triggers not activated under RBR
Submitted:	25 Oct 2008 13:40	Modified:	8 Oct 2014 22:31
Reporter:	wang xiaolin	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S4 (Feature request)
Version:	5.1+	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	RBR, triggers

Description:
hi all:

   In the Master and Slave System , My trigger only created at slave server ,
  but it will never been triggerd and execution in the replication of Row-Based .
 instead, the same trigger can been trigged and execution good in the 
Statement Based Replication ..

I think it is a bug or a big problem !!

if I want to only create a trigger at the slave server with row-base replicaton ,
How can I do to let it work as 5.0 replication ?

My MySQL:
mysql_5.1.23_ndb_6.2.15
uname -a
Linux xxxx 2.6.9-67.EL #1 Fri Nov 16 12:34:13 EST 2007 i686 i686 i386 GNU/Linux

How to repeat:
Master:
drop table test ;
drop table test_h ;
create table test (Code varchar(10),Name varchar(20) ,primary key(Code)) engine=NDB ;
create table test_h (Code varchar(10),Name varchar(20),oldName varchar(20) ,primary key(Code)) engine=NDB ;

only at slave :
CREATE TRIGGER `test_ins` AFTER insert ON test FOR EACH ROW 
INSERT INTO test_h (Code,Name,oldName ) VALUES (NEW.Code,NEW.Name,'haha....');

Thank you for the report.

Verified as described using MyISAM storage engine.

Although I think this can be "Not a Bug" due to nature of row-based replication this limitation should be at least documented.

hi Sveta Smirnova :

  Thanks you for reply .

  If I want to use the trigger on the slave in row-based replication, 
Could I change any mysql config parameter on master or slave ?
Is it possible ?

I really want to use this function . 
Could you give me any advise ?

Workaround: temporarily switch binary log format to STATEMENT .

It is a deliberate design to not execute the triggers on the slave side under row-based replication (they are still executed under statement-based replication though). Instead the *effects* of executing the trigger on the master side is replicated to the slave and applied there as rows are normally applied.

Consider what would happen if both applied the rows from the master *and* executed the trigger on the slave: you would get the trigger effects applied twice and it would be likely that the database on the master and the slave are out of sync (unless the trigger effects are idempotent, in which case it is just a waste of cycles to apply the trigger again).

As Sveta points out: if you want the trigger to execute on both the master and the slave (perhaps because you have different triggers on the master and slave), they you have to use statement-based replication.

Note that it is not necessary to use statement-based replication all-over; it suffices to switch to statement-based replication for the statements where you want this effect.

Category changed to "Documentation" as this is an explicit design to not execute triggers on the slave side for row-based replication: is not possible to guarantee consistent data on slave and master in this case. Same applies to events.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Reopening as a feature request.  In some cases, it is useful to have triggers on a slave that are not on the master, and to have them fired, even when replication uses RBR.  This could be done in a way to avoid multiple invocations.