MySQL Bugs: #94517: Create variable to enable triggers in RBR

Bug #94517	Create variable to enable triggers in RBR
Submitted:	1 Mar 2019 0:37	Modified:	1 Mar 2019 16:03
Reporter:	Christopher Shumake	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	RBR, trigger

Description:
There are valid use cases for updating tables on replicas through triggers while using row-based-replication. Data migrations, for instance, are aided by this capability.

How to repeat:
-

Suggested fix:
Please add in a variable 'slave_run_triggers_for_rbr' and allow triggers to execute on RBR binlog events replayed by replicas.

Alternatively:
Enable replicas to convert RBR to simplest SBR CRUD statements so that an intermediary replica can generate the required binlog format that allows triggers to execute on replicas. 

Thanks

Hi,

Thank you for your suggestion on the feature request. 

There is, however, one major problem.

If triggers would run on the RBR events from the relay log, then this would obligatory lead to the failure of the replication system. Since, in that case, master and slave(s) would be out-of-sync.

We are happy to accept new features, provided those are properly and fully designed, in the sense that at least there is no place for the regressions from the new feature.

We shall wait for the more detailed description of the new feature, which would work without any negative consequence.

Hello,

Yes, it could. Whomever is choosing to use triggers on a replica would have to do it carefully, which is why it'd be a flag and default OFF. Not having the option is preventing me from using carefully implemented, novel solutions. Let me share two examples. 

Example:

I have databases in production without any triggers. We need to migrate data between instances. The source data is large and constantly getting updates.

My intent is to setup a new table on a replica, populate it via triggers on every write to the source table via REPLACE statements, and truncate the table the moment I've paused replication to start a mysqldump of the source data. 

I've worked out a resolution with the consumers of the second table's 'write history' regarding deletes.

Using a replica for this activity avoids the write overhead on the master and is preferred since I want to often truncate the 'write history' table without the intrusive MDL or database-wide flush and lock. 

SBR is not an option due to the age of the system. 

Example 2:

I've heard the case where replicas maintain summary tables for counters to offload work from the master.

Sorry,

But I do not think that we can accommodate you.

If there is one case in thousand where some feature would work, that is not good enough. It is irrelevant is default is OFF. People turn on the features for thousands of reasons and then report bugs here.

Also, there are so many other obstacles for a feature like that. The binary image could be idempotent. Next, trigger would require a full and unchanged query in a simple text format. There are dozen of other consideration.

Simply too risky to be considered.

You will have to find some other solution, on the application level, for your problem.