Tuesday, August 4, 2026

MySQL 8.0.17 GTID Crash Safety Improvement

I have known for some times that there is an interesting improvement in MySQL 8.0.17 regarding GTID Crash Safety, but I have not had the time nor the need to look into it before.  When writing my last post (Understanding MySQL Replication "fatal error 1236": [...]), I saw something interesting related to this, and it is now time to cover this on my blog. From my point of view, this change is very interesting, but the documentation about it should be improved (and more), so there will be bug reports.  Let's explore all this.

Context and History

(if you are familiar with the context and history, including my posts and talks on Replication Crash Safety and The consequences of sync_binlog != 1, you can directly skip to the section about InnoDB and Binary Log / GTID Consistency)

The improvement in 8.0.17 is part of what I generally call Replication Crash Safety, and more specifically Primary/Source Crash Safety and Replica Crash Safety (which I respectively called in the past Master and Slave Crash Safety).  This is a complex subject, which changed a lot from MySQL 5.5 to 8.0, and which I covered extensively in the past :

In the fall of 2020, when I updated my talk Demystifying MySQL Replication Crash Safety for MinervaDB Athena (slides, recording), I realized that something was new in 8.0.17 and mentioned it at slide #16.  But at the time, I did not know what to think because the related bugs were not closed (Bug #70659 and Bug #92109), because the release notes were very vague about this, and because little documentation was published on this subject (we will see below that I opened a bug for this, and that I had to dig in worklogs to understand the change).  I also mentioned 8.0.17 in my post Fixing low durability GTID replica with Voodoo (this post is a follow-up of my FOSDEM and MinervaDB Athena talks, and at the time, I had not yet explored the worklogs, they might even not have been public then).

This is all about the context and the history, before diving in 8.0.17, let's state the problem.

InnoDB, Binary Log and GTID Consistency / Inconsistency

(if you are familiar with the subject of InnoDB, Binary Log and GTID Consistency / Inconsistency, including a crash in reduced durability having caused mismatch between the GTID state, the content of the binlogs, and the content of InnoDB, you can directly skip to the section about the improvement in 8.0.17)

With sync_binlog = 1 and innodb_flush_log_at_trx_commit = 1 (trx_commit for short, these being the default since at least MySQL 5.7), there is no inconsistency.  With such configuration, both the InnoDB Redo Log and the Binary Log are fully persisted on transaction commit, and unless there is a serious fault in the storage subsystem (disks) or a bug in MySQL (this has been seen in the past), we have full consistency and durability (the C and D of ACID).  The flip side of this full / high consistency and durability is that two (2) syncs are needed to flush the Redo Log and the binary log (a MySQL fork optimized that, but this is out of the scope of this post).  Historically and on magnetic disks, a sync is a high latency operation (15 milliseconds), and such latency meant that it could be useful to run MySQL with reduced durability (sync_binlog = 0 and trx_commit = 2).  This avoids COMMIT latency and increases replication throughput.  With SSDs, the sync latency was reduced to 0.1 ms, but this did not remove the need for reduced durability because network storage — like AWS EBS or GCP PD — still have a sync latency dominated by communication round-trip (0.3 to 1 ms), and it can become a bottleneck.

Running MySQL with reduced durability has complex implications (some of which are in the realm of Data Loss), and I cover these in other blog posts and talks (post, talk #1 and talk #2 with slides and recording).  Note that even with reduced durability on one node, it is possible to have high durability on a cluster, but this is out of the scope of this post (this involves either Group Replication, Semi-Sync Replication, or other solutions competing with Oracle which I cannot mention here to be aggregated on Planet MySQL).  With reduced durability and after an operating system crash, committed transaction can vanish from InnoDB and the binary logs.  For added complexity, more transactions can disappear from the binary logs than from InnoDB (the Redo Logs are synced every second, and the binary logs can remain unflushed for longer than that).  Before MySQL 8.0.17, the GTID state of such crashed node was completely out of sync / inconsistent, for both the binary logs and for InnoDB.  This is where things improve in more recent versions (recent being relative / debatable here because 8.0.17 having been released in July 2019).

GTID Crash Safety Improvement of MySQL 8.0.17

Annex #1 shows the evolution of the GTID State Consistency with InnoDB and the Binary Logs after a Linux crash with reduced durability.  It involves a replication sandbox with two replicas where the primary and the first replica are configured with reduced durability (sync_binlog = 0 and trx_commit = 2) and the second replica is configured with full durability (sync_binlog = 1 and trx_commit = 1).  As shown in the annex and with MySQL 8.0.16 (not shown in the annex, but it is the same with 5.7 and 5.6), a crash rewinds both reduced durability nodes to their pre-insert GTID state (the primary and the first replica), with both the binary log and InnoDB being inconsistent with the GTID state (in a normal situation, the primary and replicas would run on different vms and fail independently, but the demonstration with one vm is still valid, and doing this with many vms is left as an exercise to the reader).  From MySQL 8.0.17 (and still in 8.0.46, 8.4.11, 9.7.2 and 26.7.0), the GTID state is consistent with InnoDB, and the lost transactions in the binary logs are reported in gtid_purged (as explained in the previous section, the InnoDB Redo Logs are synced every second, and the binary logs can remain unflushed for longer than that, which explains the crash losing more transactions from the binlogs than from InnoDB).  However, the documentation does not mention these updates on crash recovery.  And for this and more, I opened Bug #121055 : The documentation of gtid_purged (and more) is incomplete since 8.0.17.

The MySQL 8.0.17 Release Notes are not explicit on this GTID Crash Safety improvement, and there are reasons to be unhappy about this.  To me, it looks like the improvement is hidden behind the implementation of the Clone plugin, for which below can be found in the release notes.

MySQL now provides a clone plugin that permits cloning InnoDB data locally or from a remote MySQL server instance. [...]
The clone plugin supports replication. In addition to cloning data, a cloning operation extracts and transfers replication coordinates from the donor and applies them on the recipient, [...]
(WL #9209, WL #9210, WL #9211, WL #9212 (private), WL #11636, WL #9682)

Diving in WL #9211 (InnoDB: Clone Replication Coordinates), we can read below in the section on Requirements.

F-7. For all innodb transactions with GTID, GTID MUST be durable along with the transaction i.e. in case of a server crash, the GTID set in GTID_EXECUTED table must have all the committed and XA prepared transactions in Innodb after recovery.
[...]
NOTE: This is an independent requirement and can be tested without clone. It is implicit requirement of clone.

We can also read below in the section on High Level Architecture.

GTIDs are now persistent within Innodb and guaranteed to be consistent with committed transaction.
  • GTID is persisted in UNDO log header when a transaction with GTID is committed in Innodb. Same way GTID is persisted for external XA prepare, commit and rollback.
  • GTIDs are persisted to mysql.gtid_executed table at intervals by a new innodb background thread "gtid_persistor".
  • Purge background is not allowed to purge undo logs till GTIDs are written to mysql.gtid_executed by "gtid persistor".
  • During recovery, the GTIDs are extracted from unpurged undo logs and send to gtid_persistor which persists them to gtid_executed table before replication GTID processing at startup.

But all this is only from a worklog.  It matches what we see in Annex #1, but it is not a strong guarantee that MySQL implements full GTID Crash Safety in Reduced Durability.  Both above quotes are mentioned in Bug #121055, and hopefully, the documentation will be updated accordingly, and not only for gtid_purged, but also for gtid_executed, and mysql.gtid_executed, with mentions of GTID persisted in UNDO and the gtid persistor thread (which is also called the gtid persister thread in the doc).  I also commented in Bug #70659 and Bug #92109 mentioning that they looks fixed, but leaving their closing to Oracle (these bug titles are respectively "Make crash safe slave work with gtid + less durable settings" and "Please make replication crash safe with GITD and less durable setting").

Back at fatal error 1236

I discovered the updated gtid_purged (discovered, because not documented : see the bug from the previous section) while working on my previous post on fatal error 1236.  After recovery, my initial scripts were showing both gtid_executed and gtid_purged, and I postponed the observation on gtid_purged being updated after crash recovery to this follow-up post.  But the same way the documentation for gtid_purged was not updated for 8.0.17, so was the recovery recommendations for fatal error 1236.  And for this, I opened Bug #121056 : The recovery recommendations in Last_IO_Error "fatal error 1236" are wrong (since 8.0.17).

These recommendations were :

Replicate any transactions that source has rolled back from replica to source
and/or commit empty transactions on source to account for transactions that have been committed on source but are not included in GTID_EXECU[TED].

The second recommendation is outdated.  With the GTID Crash Safety Improvement of 8.0.17, there are no more "committed transactions on the source" which are not "included in GTID_EXECUDED".  This is what I point-out in the above-mentioned bug report.

Also in my previous post, I mention that the first recommendation (replicate any transactions...) is tricky to execute, and I suggest restoring a backup instead for simplicity and for avoiding a manual and error-prone operation.  This was the case before 8.0.17, but it is not the case anymore.  Replicating such transactions is now simple and it "only" consists in setting-up replication from the crashed source to the replica with the most transactions (but only if no writes were done on the primary).  Once this is done, both the crashed source and the replica are in sync, and writes could be allowed on that crashed primary, but I still advise against that.

The reason I advise against allowing writes on the crashed primary (even after replicating missing transactions) is that there is a gap in its binary logs (clearly identified in gtid_purged; the missing transactions being on replicas).  This is important to consider such gaps, especially if you are doing a backup of the binary logs (which you should).  The handling of such gap is probably very error-prone in binlog backup automation, and a failover to a replica is probably much easier to handle than these gaps.  Also, such gap can probably cause a lot of future headaches (including when restoring a backup and then re-sourcing it to a source having gap in its binlogs).  My advice is to get rid of such gap by purging binlogs from the crashed primary, but only once we are sure that these binlogs are present on a replica, and that they have been backed up.

Conclusion and Future Work

It took me time to wrap my head around all this, and I have not yet fully explored and tested all what I have in mind, especially that I am doing all this on personal time.  I might continue in a few months, but if you want to contribute, you could also look into these (please comment on this post if you are) :

  • Unclear if the improved crash recovery correctly traces, in the error log, updating gtid_purged and gtid_executed;
  • Unclear if crash recovery applies binary logs not present in InnoDB in the unlikely case that binlogs are synced before the InnoDB Redo Logs;
  • Unclear if XtraBackup leverages GTID in UNDO for lock-free backups;
  • Unclear if there is full tracing, in the error log, of the source and replica GTID state when the IO Thread connects (and reconnects) to a source : this, combined with GTID state logging on crash recovery and MySQL startup, would be useful to handle fatal error 1236 (or investigate related replica drift and replication breakage).

Annex #1 : Reduced Durability GTID Crash Safety Improvement in 8.0.17

In this annex, I show the improvement in MySQL 8.0.17 regarding GTID Crash Safety with reduced durability (sync_binlog = 0 and trx_commit = 2).

For running below, we need a recent version of dbdeployer.  The original creator and previous maintainer, Giuseppe Maxia alias datacharmer, stopped maintaining it in October 2023 at version 1.73.0 (which does not support MySQL 8.4+).  ProxySQL took over in April 2026, and below was run with their version (we see that accessible in the new GitHub repository.  I am thankful to ProxySQL for taking over the maintenance of this very useful tool, and to Giuseppe for creating it in the first place.

Below cannot be run on my usual AWS m6id.large instance because of insufficient RAM for running six (6) replication sandboxes (only 8 GiB).  The next larger instance is ok (m6id.xlarge with 16 GiB RAM, 4 vCPU and local SSDs).

# Create and initialize a sandbox for our tests.
# (in a function to be used with different MySQL versions)
# (below is portable from MySQL 8.0 to 26.7, which is why we need sql_replica)
# (portability is also why we need cli_arg, more about this in https://bugs.mysql.com/bug.php?id=118819)
# (dbda : [dbd]eployer [a]rguments)
# (we need replication crash safety as we will crash MySQL)
# (without the sleep 5 before calling s1, we might get errors because of a dbdeployer issue: https://github.com/ProxySQL/dbdeployer/issues/131)
# (WAIT_FOR_EXECUTED_GTID_SET is needed to set sync_binlog after CREATE TABLE on s1)
# (saving cli_arg to easily continue in the next steps)
function create_and_init_sandbox() {
  sql_replica=replica; grep -q -e mysql_8.0.1 -e mysql_8.0.2[012] <<< $v && sql_replica=slave
  cli_arg=""; grep -q -e mysql_9.[4567] -e mysql_2 <<< $v && cli_arg=--commands

  local dbda="--gtid --repl-crash-safe -c skip-${sql_replica}-start"
  dbdeployer deploy replication $v $dbda

  cd ~/sandboxes/rsandbox_${v//./_}
  ./m <<< "CREATE DATABASE test_jfg"
  ./m <<< "CREATE TABLE test_jfg.t(id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT)"

  local gtid_executed="$(./m -N <<< "SELECT @@global.gtid_executed")"
  sleep 5; ./s1 <<< "SELECT WAIT_FOR_EXECUTED_GTID_SET('$gtid_executed')" > /dev/null
  for c in m s1; do ./$c <<< "SET GLOBAL sync_binlog = 0, innodb_flush_log_at_trx_commit = 2"; done

  echo "cli_arg=$cli_arg" > jfg_env
}

# Create a sandbox for our tests with different versions.
# Then for each version, show gtid_executed and start a background job inserting in table t.
# Finally, after waiting for two seconds, crash Linux.
# (as each insert consumes a gtid, we can predict which id matches each gtid)
# (saving the vs variable to easily continue after reboot)
# (background create_and_init_sandbox in a sub-process to avoid job outputs)
# (the pv command is a trick to show execution time)
{
  vs="mysql_8.0.16 mysql_8.0.17 mysql_8.0.46 mysql_8.4.11 mysql_9.7.2 mysql_26.7.0"
  echo "vs='$vs'" > ~/sandboxes/jfg_env

  ( for v in $vs; do create_and_init_sandbox 2> /dev/null& done; wait; ) | pv -tN dbdepls. > /dev/null

  echo
  for v in $vs; do
    gtid_executed=$(~/sandboxes/rsandbox_${v//./_}/m -N <<< "SELECT @@global.gtid_executed")
    echo "gtid_executed=$gtid_executed" >> ~/sandboxes/rsandbox_${v//./_}/jfg_env
    printf "%-13s %s\n" $v $gtid_executed
  done

  sudo sync  # Without a sync, the crash might loose the sandboxes and the jfg_env files.

  for v in $vs; do
    ( yes "INSERT INTO t(id) VALUE (NULL); DO SLEEP(0.02);" | ~/sandboxes/rsandbox_${v//./_}/m test_jfg & )
  done

  echo; sleep 2; echo c | sudo tee /proc/sysrq-trigger > /dev/null
}
 dbdepls.: 0:00:42

mysql_8.0.16  00020617-1111-1111-1111-111111111111:1-35
mysql_8.0.17  00020718-1111-1111-1111-111111111111:1-35
mysql_8.0.46  00023647-1111-1111-1111-111111111111:1-35
mysql_8.4.11  00020512-1111-1111-1111-111111111111:1-35
mysql_9.7.2   00020903-1111-1111-1111-111111111111:1-35
mysql_26.7.0  00037701-1111-1111-1111-111111111111:1-35

Read from remote host 3.133.107.8: Connection reset by peer
Connection to 3.133.107.8 closed.
client_loop: send disconnect: Connection reset by peer

# Wait for reboot, reconnect, start sandboxes, and show gtid_executed and gtid_purged.
# From MySQL 8.0.17, gtid_executed is consistent with the content of InnoDB.
# Also from MySQL 8.0.17, gtid_purged is initialized after crash recovery.
{
  . ~/sandboxes/jfg_env
  ( for v in $vs; do ~/sandboxes/rsandbox_${v//./_}/start_all > /dev/null& done; wait; ) | pv -tN start_alls > /dev/null 

  for v in $vs; do
    echo; echo $v; cd ~/sandboxes/rsandbox_${v//./_}; . jfg_env

    ids="$(cut -d : -f 2 <<< "$gtid_executed")"
    last_id_init="$(cut -d - -f 2 <<< "$ids")"

    sql="SELECT MAX(id) as m, @@global.gtid_executed as e, @@global.gtid_purged as p FROM t\G"
    
    for c in m s1 s2; do
      res="$(./$c $cli_arg test_jfg <<< "$sql")"

      m=$(awk '$1 == "m:" {print $2}' <<< "$res")
      e=$(awk '$1 == "e:" {print $2}' <<< "$res")
      p=$(awk '$1 == "p:" {print $2}' <<< "$res")

      ids="$(cut -d : -f 2 <<< "$e")"
      last_id_now="$(cut -d - -f 2 <<< "$ids")"

      test $m -eq $(($last_id_now - $last_id_init)) && val=OK || val=KO

      printf "%-2s: %3s %s %42s %s\n" $c $m $val $e "$p"
    done
  done
}
start_alls: 0:00:15

mysql_8.0.16
m :  97 KO  00020617-1111-1111-1111-111111111111:1-35
s1:  97 KO  00020617-1111-1111-1111-111111111111:1-35
s2:  97 OK 00020617-1111-1111-1111-111111111111:1-132

mysql_8.0.17
m :  98 OK 00020718-1111-1111-1111-111111111111:1-133 00020718-1111-1111-1111-111111111111:36-133
s1: 103 OK 00020718-1111-1111-1111-111111111111:1-138 00020718-1111-1111-1111-111111111111:36-138
s2: 103 OK 00020718-1111-1111-1111-111111111111:1-138

mysql_8.0.46
m :  70 OK 00023647-1111-1111-1111-111111111111:1-105 00023647-1111-1111-1111-111111111111:36-105
s1:  54 OK  00023647-1111-1111-1111-111111111111:1-89 00023647-1111-1111-1111-111111111111:36-89
s2:  97 OK 00023647-1111-1111-1111-111111111111:1-132

mysql_8.4.11
m :  94 OK 00020512-1111-1111-1111-111111111111:1-129 00020512-1111-1111-1111-111111111111:36-129
s1: 102 OK 00020512-1111-1111-1111-111111111111:1-137 00020512-1111-1111-1111-111111111111:36-137
s2: 103 OK 00020512-1111-1111-1111-111111111111:1-138

mysql_9.7.2
m :  85 OK 00020903-1111-1111-1111-111111111111:1-120 00020903-1111-1111-1111-111111111111:36-120
s1:  90 OK 00020903-1111-1111-1111-111111111111:1-125 00020903-1111-1111-1111-111111111111:36-125
s2: 102 OK 00020903-1111-1111-1111-111111111111:1-137

mysql_26.7.0
m :  85 OK 00037701-1111-1111-1111-111111111111:1-120 00037701-1111-1111-1111-111111111111:36-120
s1:  98 OK 00037701-1111-1111-1111-111111111111:1-133 00037701-1111-1111-1111-111111111111:36-133
s2: 103 OK 00037701-1111-1111-1111-111111111111:1-138
at

No comments:

Post a Comment