Bug #75395 Explain relation between slave_preserve_commit_order and binlog_order_commit.
Submitted: 2 Jan 2015 14:46 Modified: 25 Jul 2018 17:16
Reporter: Jean-François Gagné Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S4 (Feature request)
Version:5.7.5 OS:Any
Assigned to: CPU Architecture:Any

[2 Jan 2015 14:46] Jean-François Gagné
Description:
Hi,

At the URL below, the slave_preserve_commit_order option is described:

http://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html#sysvar_slave_preserv...

From the documentation:

[...] enabling this variable ensures that the order which transactions were committed to the binary log on the master is preserved on the slave.  [...]

and

[...] The master must have binlog_order_commits enabled. [...]

From the documentation of binlog_order_commits (URL below):

[...] transactions are committed in the same order they are written to the binary log.  [...]

http://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_binlog_o...

I do not understand why slave_preserve_commit_order (on the slave) needs binlog_order_commits (on the master).  The option binlog_order_commits does not look to impact the binary logs (see BUG#75394), could you give more information why those two options are related ?

Moreover, I was able to configure a slave with slave_preserve_commit_order when the master is not with binlog_order_commits, which seams to contradict the documentation of slave_preserve_commit_order ("The master must have binlog_order_commits enabled").  Could the impact of this configuration be described in more details in the documentation.

Finally, I would like to understand what is the behavior of a slave with slave_preserve_commit_order ON and binlog_order_commits OFF when transactions are executed from the SQL Thread and normal threads at the same time (I guess transactions from the SQL Thread and not from it can group commit together, how are they ordered ?).  Can this be added to the documentation ?

Thanks,

JFG

How to repeat:
I set slave_preserve_commit_order ON on a slave with its master having binlog_order_commits to OFF with the following commands.

# To shorten next commands.
$ sgvl="show global variables like"

# Check master settings.
$ ssh $master sudo mysql <<< "$sgvl 'binlog_order_commits';"
Variable_name   Value
binlog_order_commits    ON

# Check slave settings.
$ ssh $slave sudo mysql <<< "$sgvl 'slave_parallel_workers'; $sgvl 'slave_parallel_type'; $sgvl 'slave_preserve_commit_order';"
Variable_name   Value
slave_parallel_workers  10
Variable_name   Value
slave_parallel_type     LOGICAL_CLOCK
Variable_name   Value
slave_preserve_commit_order     OFF

# Set the slave to slave_preserve_commit_order ON.
$ ssh $slave sudo mysql <<< "stop slave; set global slave_preserve_commit_order = 1; start slave; $sgvl 'slave_preserve_commit_order';"
Variable_name   Value
slave_preserve_commit_order     ON

# Set back the slave to slave_preserve_commit_order OFF.
$ ssh $slave sudo mysql <<< "stop slave; set global slave_preserve_commit_order = 0; start slave; $sgvl 'slave_preserve_commit_order';"
Variable_name   Value
slave_preserve_commit_order     OFF

# Set the master to binlog_order_commits OFF.
$ ssh $master sudo mysql <<< "set global binlog_order_commits = 0; $sgvl 'binlog_order_commits';"
Variable_name   Value
binlog_order_commits    OFF

# Set the slave to slave_preserve_commit_order ON, this "should" not work according to the documentation.
$ ssh $slave sudo mysql <<< "stop slave; set global slave_preserve_commit_order = 1; start slave; $sgvl 'slave_preserve_commit_order';"
Variable_name   Value
slave_preserve_commit_order     ON
[25 Jul 2018 17:16] Margaret Fisher
Posted by developer:
 
Sorry it took so long for this bug to surface! Thanks for the report. I have checked and the documentation for slave_preserve_commit_order no longer mentions binlog_order_commits, although I can't identify the bug or work item for which that change was made. It's gone, anyway. I also have your related bug IMPROVE DOCUMENTATION OF BINLOG_ORDER_COMMITS which I will look into, as I see that documentation is unchanged.