Bug #84630 override master variables with init_slave
Submitted: 23 Jan 2017 23:27 Modified: 13 Mar 2017 13:01
Reporter: Trey Raymond Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.6.x, 5.7.x OS:Any
Assigned to: CPU Architecture:Any
Tags: foreign key, init_slave, replication, unique
Triage: Needs Triage: D5 (Feature request)

[23 Jan 2017 23:27] Trey Raymond
Description:
As it stands, some variables are passed down in binlog headers.  See:
https://dev.mysql.com/doc/refman/5.7/en/replication-features-variables.html
That's a good thing by itself.

The issue is, anything set in init_slave should be able to override those.  I believe it's just an order of operations issue, where init_slave is evaluated by the sql thread right after it starts up, but the other sets are excuted when it starts processing a relay log.

A common use case for this would be to turn off some extraneous checks to speed up sql thread execution.  As it's still limited to one thread per schema, replication throughput is generally a bottleneck long before master writes.

This case is assuming a traditional single-writer with 1 (or more in 5.7) warm backup masters and any number of slaves and rbr.  In such a case, there's no need for foreign key or unique checks when your schema and data are in sync...so if that were the case, init_slave could be used to disable them and save some cycles/reads.  Similar case with innodb_support_xa, though that's deprecated now (boo).

How to repeat:
this is known behavior, but you can test it anyway:

stop slave; set global init_slave='set session foreign_key_checks=0'; start slave;
*generate an out of sync data set and a master write that would cause an fk failure*

Worker 3 failed executing transaction '064042aa-....' at master log ....., end_log_pos 38391; Could not execute Write_rows event on table .....; Cannot add or update a child row: a foreign key constraint fails ....., Error_code: 1452; handler error HA_ERR_NO_REFERENCED_ROW; the event's master log FIRST, end_log_pos 38391

Suggested fix:
The simplest on paper would be to execute init_slave after setting the master values for the variables, but if I recall that gets set at file rollover and master value change, not just on sql thread start.  Correct me if I'm wrong there, that's just top of my head.  If so, that'd mean init_slave gets executed a lot, which is bad - that code is specifically to initialize the slave sql thread, and can be used for a variety of things in that vein.

So, a way where it still only executes at thread start, but isn't overwritten.  Something to cache any vars set there and ignore what the master says for them?  Or something more elegant.
[13 Mar 2017 13:01] Umesh Shastry
Hello Trey Raymond,

Thank you for the feedback and feature request!

Thanks,
Umesh