Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Using sql_log_bin=0 breaks GTID consistency

Description

In some special cases, where a PXC cluster node has an async replica, we may need to skip some events from being binlogged. Hence we can use  SET sql_log_bin=0 before a write, so that it will replicate to other PXC nodes, but it will not be binlogged.

Check PXC-3731 for more details.

Unfortunately, by doing that, a GTID gap will be introduced in gtid_executed set on the PXC source. Also, after the source node is restarted, the gap event will appear in gtid_purged. This is a problem for async replicas, as they will try to fetch the missing transaction, but the source will refuse informing that it was already purged (error 1236).

As setting sql_log_bin=0 in PXC/Galera disables MySQL (async) replication, it should also effectively disable MySQL GTID, which is only needed for that type of replication. But that should be not by creating empty gaps, but rather by stopping to increase the GTID sequence number.

 

A simple test case using dbdeployer PXC setup:

 

 

 

Environment

None

AFFECTED CS IDs

CS0029780, CS0042274

Smart Checklist

Activity

Show:

Przemyslaw Malkowski September 21, 2022 at 9:48 AM

Also, after service restart, the other nodes show the same missing sequences as gtid_purged, for example:

Przemyslaw Malkowski September 21, 2022 at 9:41 AM

I should add one explanation here. Describing the gap as present in the gtid_executed set was a bit inaccurate.

What I meant was that gtid_executed gets incremented even though no transaction is registered in the binary log. So, it's more like a phantom event - registered in gtid_executed, but not present anywhere. Hence upon restart, MySQL scans for GTIDs and notes there is nothing like that and adds such ones to the gtid_purged set. While at the same time, the gtid_executed set stays contiguous.

Done

Details

Assignee

Reporter

Needs Review

Yes

Needs QA

Yes

Fix versions

Affects versions

Priority

Smart Checklist

Created September 9, 2022 at 11:23 AM
Updated June 6, 2024 at 7:59 AM
Resolved December 20, 2023 at 12:20 PM