Australian cloud for business software — built and run in Sydney since 2002

NetLeverage
NetLeverage

MySQL replica error 1236: 'Got fatal error from source when reading data from binary log'

Technical article
Troubleshooting

About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

Error 1236 means the source server refused or failed to send binary log data to the replica. One reason, and the one this article covers first, is that the replica asked for a log file, or a set of transactions, that the source has already purged. The replica cannot recover from that by itself, and forcing it forward would silently drop the missing transactions. Prove which log is missing, then rebuild the replica from a consistent copy of the source.

What the error means

Error 1236 is ER_SOURCE_FATAL_ERROR_READING_BINLOG, SQLSTATE HY000, with the template Got fatal error %d from source when reading data from binary log: '%s' (MySQL 8.4 Error Reference). Older servers, as in the bug reports and Percona article cited below, say "master" instead of "source". The number and description inside the message vary with the cause.

A replica's I/O (receiver) thread asks the source for events, either from a named file and position or, with GTID auto-positioning, by sending the set of transactions it already has. With auto-positioning the source works out the starting file by reading the Previous_gtids_log_event in the header of each binary log, newest first, and then sends every transaction the replica lacks (GTID Auto-Positioning, MySQL 8.4).

If any needed transaction has been purged from the source's binary log, or added to gtid_purged another way, "the source sends the error ER_SOURCE_HAS_PURGED_REQUIRED_GTIDS to the replica, and replication does not start". The same page says the replica "cannot recover automatically", that the missing GTIDs are listed in the source's error log in the ER_FOUND_MISSING_GTIDS warning, and that the correct approach is either to replicate the missing transactions from another source or to replace the replica with a new one built from a more recent backup. Error 1789 is ER_SOURCE_HAS_PURGED_REQUIRED_GTIDS: "Cannot replicate because the source purged required binary logs. Replicate the missing transactions from elsewhere, or provision a new replica from backup. Consider increasing the source's binary log expiration period."

How binary logs get purged

  • Automatic expiry. binlog_expire_logs_seconds defaults to 2592000 (30 days), is dynamic, and files can be removed "at startup and when the binary log is flushed". binlog_expire_logs_auto_purge defaults to ON; setting it to OFF takes precedence, and setting the seconds value to 0 also stops automatic purging (Binary Logging Options and Variables, 8.4).
  • Manual purge. PURGE BINARY LOGS TO 'name' or BEFORE datetime is safe while replicas are replicating, and does not remove a log a connected replica is reading. But "if a replica is not connected and you happen to purge one of the log files it has yet to read, the replica cannot replicate after it reconnects" (PURGE BINARY LOGS, 8.4).

The same page says that with replication you should use an expiration period "no lower than the maximum amount of time your replicas might lag behind the source".

Likely causes

Cause What you see How to confirm
Source purged a log the replica still needed Error 1236, or error 1789 with GTIDs The replica's Source_Log_File is not in the source's SHOW BINARY LOGS; or gtid_purged on the source overlaps what the replica lacks
Replica was stopped or disconnected longer than the expiry period Same, after a restart or outage Compare outage length with binlog_expire_logs_seconds
A manual PURGE BINARY LOGS ran Same, at a known time Operator history; the source's earliest log is newer than the replica's position
SOURCE_LOG_FILE set to the wrong name "Could not find first log file name in binary log index file" right after CHANGE REPLICATION SOURCE TO The name given is the index file or a path, not binlog.000145 style (bug #2486)
Event larger than the replica allows A message about max_allowed_packet Percona's article describes log event entry exceeded max_allowed_packet; compare packet settings on both servers
Position past the end of a log after a source crash "Client requested master to start replication from impossible position" Described by Percona as an unsynchronised log tail when sync_binlog was not 1

The last two rows rest on a Percona Blog article of 8 October 2014, not on the MySQL manual, so treat them as field reports and verify on your version.

Prove the cause before changing anything

All read-only. Run on the replica and then the source.

  1. Replica: SHOW REPLICA STATUS; (requires REPLICATION CLIENT). Read Source_Log_File, Read_Source_Log_Pos, Replica_IO_Running, Last_IO_Errno, Last_IO_Error, Retrieved_Gtid_Set, Executed_Gtid_Set and Auto_Position (SHOW REPLICA STATUS, 8.4). A non-empty Last_IO_Error also appears in the replica's error log.
  2. Source: SHOW BINARY LOGS; lists Log_name, File_size and Encrypted (SHOW BINARY LOGS, 8.4). Is the replica's Source_Log_File still in the list? Is the earliest file on the source newer than it?
  3. Source: SELECT @@GLOBAL.gtid_purged; and SELECT @@GLOBAL.binlog_expire_logs_seconds; show what has been purged and how long logs are kept.
  4. Source error log: look for the ER_FOUND_MISSING_GTIDS warning, which lists the missing GTIDs.
  5. Check the history: when did the replica last apply a transaction, and what is the source's retention? If the gap exceeds the retention, the cause is proven.

If you suspect the packet-size or position causes, read the event around the reported position on the source with mysqlbinlog; the manual names mysqlbinlog, SHOW RELAYLOG EVENTS and SHOW BINLOG EVENTS as the tools for inspecting events. The manual says replica_max_allowed_packet (default 1 GB) must be larger than the source's max_allowed_packet.

Fix

These statements are taken from the documentation and have not been run. Rebuilding replaces the replica's data: be sure you are working on the replica and not the source.

Why you should not skip forward

The skip tools exist for a failed transaction, not for a missing log. The manual's checklist asks whether the stopped transaction needs to be applied on the replica; if it does, "either make the appropriate corrections and reapply the transaction, or manually reconcile the data". The non-GTID method SET GLOBAL sql_replica_skip_counter = N skips events, not whole transactions in every case, cannot be used when gtid_mode=ON, and takes effect at the next START REPLICA (Skipping Transactions, 8.4). The GTID method commits an empty transaction with the failing GTID. Neither brings back transactions that exist on no server. For purged GTIDs, the auto-positioning page states that reconnecting without SOURCE_AUTO_POSITION "only results in the loss of the purged transactions on the replica". The replica then looks healthy while its data is missing rows.

Option A: replicate the gap from another source

If another server still holds the missing transactions, the manual's first approach is to replicate them from there.

Option B: rebuild from a consistent copy

  1. Take a consistent copy of the source. The GTID failover page describes a dump with --source-data set to 1 and --set-gtid-purged set to AUTO or ON, or copying the data directory with both servers stopped (the replica must have gtid_mode=ON) (Using GTIDs for Failover and Scaleout, 8.4). The mysqldump page gives mysqldump --all-databases --source-data --single-transaction > all_databases.sql. --source-data needs the RELOAD privilege and the binary log enabled.
  2. On the replica run STOP REPLICA;, load the copy, then point the replica at the right place. For file and position, CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000145', SOURCE_LOG_POS=4-style coordinates (file name with its numeric suffix; position 4 is the start of a file) (CHANGE REPLICATION SOURCE TO, 8.4). For GTIDs, SOURCE_AUTO_POSITION = 1, which cannot be combined with SOURCE_LOG_FILE or SOURCE_LOG_POS.
  3. START REPLICA;

If the cause was a wrong SOURCE_LOG_FILE, only the coordinates need correcting, not the data. For the packet-size cause, align the settings and restart replication. For the impossible-position cause, Percona's article repoints the replica to the next log at position 4 only after confirming with mysqlbinlog that the reported position is the end of the file; treat that as a field report, not a manual procedure.

Example (illustrative): SRC01 keeps binary logs for 259,200 seconds (3 days). Its replica REP01 is stopped for a maintenance task and left off for 5 days. On restart it reports error 1236, and SHOW BINARY LOGS on SRC01 begins at a file created 3 days ago. The team rebuilds REP01 from a fresh consistent dump of SRC01 and raises retention to 7 days (604,800 seconds). The values are invented.

Confirm the fix

  1. SHOW REPLICA STATUS; shows Replica_IO_Running as Yes, Replica_SQL_Running as Yes, and Last_IO_Errno of 0.
  2. With GTIDs, Executed_Gtid_Set on the replica approaches the source's and Auto_Position is 1.
  3. Compare row counts and key totals for a sample of tables on source and replica. A running replica is not proof it holds the same data.
  4. Make a harmless test change on the source and confirm it arrives. Watch lag for a full business cycle.

What to watch afterwards

  • Alert on the replica's oldest needed log against the source's oldest retained log, not only on lag.
  • Keep binlog_expire_logs_seconds comfortably above your longest planned replica outage. Set retention against the worst gap you tolerate, not the average.
  • Record the binary log coordinates with each backup so that a rebuilt replica or a point-in-time restore has a starting point.

Risks and limits

  • No command here has been run or lab-tested.
  • Statement names follow MySQL 8.4 (REPLICA, SOURCE). Older servers use SLAVE and MASTER, including sql_slave_skip_counter; MariaDB has its own GTID implementation and differs.
  • The packet-size and impossible-position causes come from a 2014 blog post, not from the manual; treat them as field reports and verify on your version.
  • The --set-gtid-purged and gtid_purged handling when loading a copy has rules the failover page covers; follow the full section before loading a dump into a replica that already has transactions.
  • Rebuilding discards the replica's local changes. If the replica took writes, reconcile first.

Sources