This MySQL replication error — fatal error 1236 / Replica has more GTIDs than the source has, using the source's SERVER_UUID — shows the importance of thinking before acting. I am glad a non-DBA Colleague asked me about it, because if he had restarted replication, it would have caused a much bigger mess.
Often, we are tempted — or pushed — to just restart things as quickly as possible. In this specific case, restarting replication is unsafe as it leads to a replication breakage in the best case, and to silent data corruption in the worse case. It is a great example of the following two advice : 1) do not make a bigger mess, do not failover, do not reboot, and 2) we cannot just bring the database back up, we have things to do first to make this safe. Let's explore all these.
As shown in Annex #1 (discuss later) and with MySQL 8.4.11, the first sentence of our error is :
Replica has more GTIDs than the source has, using the source's SERVER_UUID.
Depending of where we look, it is prefixed with :
Got fatal error 1236 from source when reading data from binary log
(note that in this post, I use fatal error 1236 but a more descriptive error is Replica has more GTIDs than the source has, using the source's SERVER_UUID; this more descriptive error is too long to use, and for that and in a bug report mentioned below, I suggest to shorten it to Replica has more GTIDs than his source on her UUID or Replica has more GTIDs than its source on the source's UUID)
But you will only see these if you dive into why replication broke (with SHOW REPLICA STATUS, or in the P_S table replication_connection_status, or by looking in the error log). The following sentence — not necessarily easy to understand (the detail are further down below) — is what you will find after looking in the right place :
This may indicate that the the [sic] last binary log file was truncated or lost, e.g., after a power failure when sync_binlog != 1. The source may have rolled back transactions that were already replicated to the replica. 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 [message truncated]
Let's cover generalities first and then the error (direct link to the section about the error).
Data Incident Priority : Avoiding a Bigger Mess (and being Safe)
(in this section, I quote two other Data Engineer Colleagues : Jeremy Cole and Josh Varner; and it is not the first time, I also did it in my 2024 SRECon talk Autopsy of a Cascading Outage from a MySQL Crashing Bug at slide #35 and slide #36)
In his 2023 blog post (The customer is always wrong), Jeremy mentions the importance of avoiding a bigger mess. His exact words are :
Don’t make a bigger mess. Avoid taking any drastic actions to “fix” the underlying systems: don’t failover, don’t rebuild a host, don’t reboot.
In his 2022 Percona Live talk (Database Incident Management), Josh mentions priorities during a Data Incident. These are similar to Jeremy advice above (avoid a bigger mess), but include a more detail. To the question "Can’t we just bring the database back up", Josh answers "No, we have some things we need to do first to make this safe" (direct link on Youtube). Josh mentions three threats which could be considered more important than availability : 1) data corruption, 2) data leaks, and 3) data loss. And indeed, if not handled properly, the error of this post can cause data corruption and data loss.
Can’t we just bring the database back up ?No, we have things we need to do first to make this safe !
Also in my SRECon talk, I give an answer when pressed into doing something that we think is wrong (slide #35) :
I am not sure it is the best course of action and I am still thinking about it. Without anything better in 5 minutes, we will do what you suggest.
With that in mind, we can now come back to our replication error.
fatal error 1236 : Replica has more GTIDs than the source has, using the source's SERVER_UUID
It is easy to simulate / cause fatal error 1236 on purpose, and this is what we do here. The next section (direct link) discusses how it can happen in real life. This simulation allows us to easily show the silent data corruption / replica drift and is described in Annex #1.
In this annex, after creating a replication sandbox and initializing a simple table, we fake / generate on purpose an extra GTID on the first replica. Restarting replication on that replica causes fatal error 1236, but we do not yet have data corruption / replica drift.
To get replica drift, we restart replication after inserting two more rows in our table with two other transactions. The first row is lost on the replica because the matching GTID was already present (and the matching transaction ignored by the applier). This is an example of silent replica drift, and this is the worse case for not handling this error correctly.
Restarting Replication Leads to Replica Drift
For getting a visible error and replication breakage, we delete all rows on the primary. Because a row is missing on the replica, replication breaks with error Can't find record / HA_ERR_KEY_NOT_FOUND. A similar error would have happened with an UPDATE (but a TRUNCATE TABLE would have succeeded and magically fix the replica drift).
Not shown in Annex #1 but in the bug report mentioned later in this paragraph, the evolution of this error is messy. In 5.7, the IO Thread Error Number (Last_IO_Errno) is 1236 (hence the message Got fatal error 1236). But this changed to 13114 in 8.0 without changing the error message (still mentioning "fatal error 1236"). And for that and more, I opened Bug #121050 : Confusing Last_IO_Error "fatal error 1236" since 8.0 (also affecting 8.4, 9.7 and 26.7) and more.
fatal error 1236 : Real Life Occurrence
So far, we have explored the consequences of fatal error 1236, but we have not shown how it can happen in real life. Let's close this gap.
The full error mentions why fatal error 1236 can happen (in bold in the quote below) : a power failure when sync_binlog != 1. Power failure is probably a little restrictive, the general condition is any system failure, halting or crash, resulting in data written to the binary log not being fully persisted to disk (hence lost). So fatal error 1236 is a sub-topic of the general consequence of sync_binlog != 1, a subject I covered in the past in a blog post series and in a FOSDEM talk. There are a few things I do not agree with in the error message, including rolled back transactions and the recovery recommendations, but I come back to these below.
Got fatal error 1236 from source when reading data from binary log: 'Replica has more GTIDs than the source has, using the source's SERVER_UUID. This may indicate that the the [sic] last binary log file was truncated or lost, e.g., after a power failure when sync_binlog != 1. The source may have rolled back transactions that were already replicated to the replica. 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]'
In Annex #2, I used the extra replicas from the sandbox of Annex #1 for showing a real life occurrence of fatal error 1236. This involves a Linux crash of the primary with reduced durability (sync_binlog = 0 and innodb_flush_log_at_trx_commit = 2; trx_commit for short). All this is run on a single vm, so the replicas also crash, but because the replicas are using high durability (sync_binlog = 1 and trx_commit = 1), nothing is lost there (the normal situation would be replicas which do not crash, but this would need more than one vm, and the is left as an exercise to the reader).
After recovering from the crash and because the binary logs were not persisted after each transaction (sync_binlog = 0), transactions are lost from the replication logs (trx_commit = 2 persists the InnoDB Redo Logs every second, so there are usually more transactions lost from the binlogs than from InnoDB, but the inverse could also happen in certain edge cases, and this is out of the scope of this post). Because the replicas are run with high durability, nothing is lost there, and after their recovery, they have more transactions than their source. In the annex, when starting replication on the s2 replica (or when a non-crashed replica reconnects to the primary), it has more GTID than the source, causing fatal error 1236.
And note that fatal error 1236 is not systematic. If the lost GTIDs on the primary are consumed by new transactions before the replica reconnects, replication resumes ignoring the new transactions and causing drift / data corruption. This situation is also presented in the annex with the s3 replica. The right thing to do, when a primary with reduced durability suffers an OS crash, is to fail over to a replica, not resuming writes.
If writes were resumed, the replicas have diverged / drifted from the primary, and this should be fixed. But such fixing is not straightforward, and only you can decide what is the best in your situation :
- should you make the replicas match the data from the primary (and definitively loose transactions that reached the replicas but were lost on the primary),
- or should you fail over to a replica and restore the primary (and definitively loose writes on the primary that reused GTIDs present on replicas),
- or should you do some heavier lifting to avoid losing data (which risks being very complex, error-prone, and needs downtime to avoid further data inconsistencies).
Let's now cover the other parts of the error message. In it, we can read below, which I do not fully agree with.
The source may have rolled back transactions that were already replicated to the replica
It is not as much as having rolled back transactions, but more as not having completely committed them (at least not in a fully persistent / highly durable way). On recovery, the COMMIT of these transactions are not in the InnoDB Redo Logs (and they are probably also gone from the binlogs), so the reality is closer to these transactions having never been committed (because of reduced durability).
And for the recovery, the error message is telling us these two recommendations :
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].
In some respects, these are correct, but they are tricky to execute. It is not clear how to determine which transactions have rolled back on the source (more about this in a follow-up post) and committing empty transaction might be an outdated recommendation (more about this also in the follow-up post). Also, empty transactions in the source binary logs risks using these for other replicas and causing more data corruption / replica drift, so I would advise against this.
UPDATE : the follow-up post is published (MySQL 8.0.17 GTID Crash Safety Improvement) and contains a section Back at fatal error 1236.
IMHO, the gist of fatal error 1236 is that something wrong happened : a replica was allowed to reconnect to a reduced durability source/primary after an operating system crash. Hopefully, no writes happened on that primary, and the right thing to do is to promote a replica as the new primary (the one which has the most transactions). Regarding the primary, the safest thing is to restore it from backup (in some cases, it might be possible to avoid a restore, and this is the subject of the follow-up post). And this is why I suggest in Bug #121050 to strip most explanation and corrective actions from the error message, and to replace them with "This is an unusual situation where it might be unsafe to restart replication without a thorough investigation and corrective actions (a cause could be a source OS crash with sync_binlog != 1)".
Annex #1 : Causing fatal error 1236 Manually
In this annex, I describe how to get fatal error 1236 manually, and show the potential replica drift and replication breakage. It is not the usual way this error happens (described in the next annex), but it allows us to easily trigger the condition, see the symptoms / error messages, and show the risks of restarting replication (silent data corruption and replication breakage).
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 2.3.0 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 is run on an AWS m6id.large instance (local SSDs, 2 vCPU and 8 GiB RAM).
# Create and initialize a sandbox for our tests. # (dbda : [dbd]eployer [a]rguments) # (we create 3 replicas : s1 is used in this annex, the others in the next) # (we need replication crash safety as we will crash MySQL in the next annex) # (skipping starting replication after startup is needed for the next annex) # (the pv command is a trick to show execution time) { v=mysql_8.4.11 dbda="-n 4 --gtid --repl-crash-safe -c skip-replica-start" dbdeployer deploy replication $v $dbda | pv -tN dbdepl. > /dev/null cd ~/sandboxes/rsandbox_${v//./_} ./m <<< " CREATE DATABASE test_jfg; CREATE TABLE test_jfg.t(id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT); INSERT INTO test_jfg.t(id) VALUE (NULL); INSERT INTO test_jfg.t(id) VALUE (NULL)" } dbdepl.: 0:00:18 # Manually inject a GTID on s1 and show the error after restarting replication. # (DO SLEEP to let replication proceed) { gtid_executed="$(./m -N <<< "SELECT @@GLOBAL.gtid_executed")" uuid="$(cut -d : -f 1 <<< "$gtid_executed")" ids="$( cut -d : -f 2 <<< "$gtid_executed")" last_id="$(cut -d - -f 2 <<< "$ids")" next_id=$(($last_id + 1)) ./s1 <<< "SET GTID_NEXT = '$uuid:$next_id'; BEGIN; COMMIT" for c in m s1; do printf "%-2s: %s\n" $c $(./$c -N <<< "SELECT @@GLOBAL.gtid_executed"); done echo ./s1 <<< "STOP REPLICA; START REPLICA; DO SLEEP(1); SHOW REPLICA STATUS\G" | grep -e IO_Running: -e Last_IO_Err..: echo ./s1 -N <<< "SELECT LAST_ERROR_MESSAGE FROM performance_schema.replication_connection_status" echo tail -n 2 node1/data/msandbox.err } m : 00020512-1111-1111-1111-111111111111:1-37 s1: 00020512-1111-1111-1111-111111111111:1-38 Replica_IO_Running: No Last_IO_Errno: 13114 Last_IO_Error: Got fatal error 1236 from source when reading data from binary log: 'Replica has more GTIDs than the source has, using the source's SERVER_UUID. This may indicate that the the last binary log file was truncated or lost, e.g., after a power failure when sync_binlog != 1. The source may have rolled back transactions that were already replicated to the replica. 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' Got fatal error 1236 from source when reading data from binary log: 'Replica has more GTIDs than the source has, using the source's SERVER_UUID. This may indicate that the the last binary log file was truncated or lost, e.g., after a power failure when sync_binlog != 1. The source may have rolled back transactions that were already replicated to the replica. 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' 2026-08-03T21:24:37.241986Z 19 [ERROR] [MY-010557] [Repl] Error reading packet from server for channel '': Replica has more GTIDs than the source has, using the source's SERVER_UUID. This may indicate that the the last binary log file was truncated or lost, e.g., after a power failure when sync_binlog != 1. The source may have rolled back transactions that were already replicated to the replica. 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 (server_errno=1236) 2026-08-03T21:24:37.242011Z 19 [ERROR] [MY-013114] [Repl] Replica I/O for channel '': Got fatal error 1236 from source when reading data from binary log: 'Replica has more GTIDs than the source has, using the source's SERVER_UUID. This may indicate that the the last binary log file was truncated or lost, e.g., after a power failure when sync_binlog != 1. The source may have rolled back transactions that were already replicated to the replica. 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', Error_code: MY-013114 # Show the potential silent data corruption / replica drift. # SELECT COUNT is different on primary and replica, which shows replica drift. # (the 1st INSERT on the primary was skipped on the replica because its GTID is already there) { for i in 1 2; do ./m <<< "INSERT INTO test_jfg.t(id) VALUE (NULL)"; done ./s1 <<< "START REPLICA; DO SLEEP(1)" > /dev/null sql="SELECT c FROM (SELECT COUNT(*) as c FROM test_jfg.t) t" for c in m s1; do printf "%-2s: %s\n" $c $(./$c -N <<< "$sql"); done } m : 4 s1: 3 # Show the potential replication breakage caused by replica drift. # (replicating the DELETE fails because a row is absent on the replica) { ./m test_jfg <<< "DELETE FROM t" ./s1 <<< "DO SLEEP(1); SHOW REPLICA STATUS\G" | grep -e SQL_Running: -e Last_SQL_Err..: echo ./s1 -N <<< "SELECT LAST_ERROR_MESSAGE FROM performance_schema.replication_applier_status_by_worker WHERE LAST_ERROR_MESSAGE != ''" echo tail -n 2 node1/data/msandbox.err } Replica_SQL_Running: No Last_SQL_Errno: 1032 Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '00020512-1111-1111-1111-111111111111:40' at source log mysql-bin.000001, end_log_pos 10144. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. Worker 1 failed executing transaction '00020512-1111-1111-1111-111111111111:40' at source log mysql-bin.000001, end_log_pos 10144; Could not execute Delete_rows event on table test_jfg.t; Can't find record in 't', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's source log mysql-bin.000001, end_log_pos 10144 2026-08-03T21:25:06.239677Z 21 [ERROR] [MY-010584] [Repl] Replica SQL for channel '': Worker 1 failed executing transaction '00020512-1111-1111-1111-111111111111:40' at source log mysql-bin.000001, end_log_pos 10144; Could not execute Delete_rows event on table test_jfg.t; Can't find record in 't', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's source log mysql-bin.000001, end_log_pos 10144, Error_code: MY-001032 2026-08-03T21:25:06.254340Z 20 [Warning] [MY-010584] [Repl] Replica SQL for channel '': ... The replica coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details). Error_code: MY-001756
Annex #2 : fatal error 1236 in Real Life
In this annex, I show how fatal error 1236 can happen in real life. This involves a crash of the primary without having fully persisted the binary log. Normally, the replica would not crash, but I simulate all this on a single vm with reduced durability for the source and high durability for the replicas. Doing this with more than one vm is left as an exercise to the reader.
# (continuing with the sandbox from Annex #1) # Create two tables (t2 and t3), relax durability and show gtid_executed. # Then, start a background job inserting in t2. # Finally, after waiting for two seconds, crash Linux. # (reminder : s1 is corrupted and broken, the other replicas are fine) # (relaxing durability after table creation, so these cannot be lost from the binlogs) # (saving the v variable to easily continue after reboot) # (background inserts in a sub-process to avoid job outputs) { ./m -N test_jfg <<< " CREATE TABLE t2 LIKE t; CREATE TABLE t3 LIKE t; SET GLOBAL sync_binlog = 0, innodb_flush_log_at_trx_commit = 2; SELECT @@global.gtid_executed" echo "v=$v" > ~/sandboxes/jfg_env sudo sync # Without a sync, the crash might loose the sandbox and the jfg_env file. ( yes "INSERT INTO t2(id) VALUE (NULL); DO SLEEP(0.02);" | ./m test_jfg & ) sleep 2; echo c | sudo tee /proc/sysrq-trigger > /dev/null } 00020512-1111-1111-1111-111111111111:1-42 Read from remote host 3.144.88.48: Connection reset by peer Connection to 3.144.88.48 closed. client_loop: send disconnect: Connection reset by peer # Wait for reboot, reconnect, and start MySQL. # Then check GTIDs and trigger the error on s2 by restarting replication. # (s3 is left as is for the next test) { . ~/sandboxes/jfg_env cd ~/sandboxes/rsandbox_${v//./_} ./start_all | pv -tN start_all > /dev/null echo for c in m s2 s3; do printf "%-2s: %s\n" $c $(./$c -N <<< "SELECT @@global.gtid_executed"); done echo ./s2 <<< "START REPLICA; DO SLEEP(1); SHOW REPLICA STATUS\G" | grep -e IO_Running: -e Last_IO_Err..: } start_all: 0:00:12 m : 00020512-1111-1111-1111-111111111111:1-116 s2: 00020512-1111-1111-1111-111111111111:1-139 s3: 00020512-1111-1111-1111-111111111111:1-139 Replica_IO_Running: No Last_IO_Errno: 13114 Last_IO_Error: Got fatal error 1236 from source when reading data from binary log: 'Replica has more GTIDs than the source has, using the source's SERVER_UUID. This may indicate that the the last binary log file was truncated or lost, e.g., after a power failure when sync_binlog != 1. The source may have rolled back transactions that were already replicated to the replica. 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' # Insert enough rows in t3 to avoid the error, then restart replication and show data drift on s3. # We see that the replicas have more rows in table t2, and are missing rows in t3. # (note that s3 never reported fatal error 1236) # (causing replication breakage is left as an exercise to the reader) { yes "INSERT INTO t3(id) VALUE (NULL);" | head -n 1000 | ./m test_jfg for c in s2 s3; do ./$c <<< "START REPLICA" > /dev/null; done sql="SELECT MAX(id) FROM test_jfg.t2; SELECT MIN(id) FROM test_jfg.t3" sleep 1; for c in m s2 s3; do printf "%-2s: %3s %3s\n" $c $(./$c -N <<< "$sql"); done } m : 74 1 s2: 97 24 s3: 97 24
No comments:
Post a Comment