MySQL

How to Fix MySQL "Gtid_set Has Holes" Error on Replication Slave Setup

4 min read by DebuggedIt

Quick answer

Setting up or resuming GTID-based replication fails with MySQL complaining about "holes" in the GTID set, refusing to proceed. This is MySQL's GTID consistency...

Setting up or resuming GTID-based replication fails with MySQL complaining about "holes" in the GTID set, refusing to proceed. This is MySQL's GTID consistency checking doing exactly what it's designed to do — detecting a gap in the sequence of transaction identifiers that would make replication position ambiguous or unsafe to resume from.

The Problem

Starting or configuring replication fails with a specific GTID-related error:

mysql> START SLAVE;
ERROR 1777 (HY000): Cannot replicate because the replicate has an incomplete GTID set,
being GTID_SUBTRACT('3E11FA47-71CA-11E1-9E33-C80AA9429562:1-20', gtid_purged)

Or, when trying to set the GTID purged state manually:

mysql> SET GLOBAL gtid_purged = '3E11FA47-71CA-11E1-9E33-C80AA9429562:1-10:15-20';
ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED cannot be set because gtid_set has holes

Why It Happens

GTID-based replication tracks every transaction by a globally unique identifier, and MySQL expects the set of GTIDs a server has processed (or purged) to be a contiguous range with no gaps — a "hole" means transactions 11-14 are missing from an otherwise sequential set, for example, which is inherently ambiguous: did those transactions never happen, were they purged separately, or is something actually broken? MySQL refuses to proceed rather than guess. Common causes:

  • A partial or manually reconstructed GTID set — someone manually adjusted gtid_purged or restored from a backup that doesn't represent a clean, contiguous transaction history.
  • Binary logs were purged non-contiguously — logs were deleted or expired out of order relative to how GTIDs were actually generated, leaving gaps in what's recoverable.
  • A restore from a backup taken with mysqldump without --set-gtid-purged configured correctly, leaving the restored server's GTID state inconsistent with what actually happened on the source.
  • Mixing GTID-based and non-GTID-based (position-based) replication changes on the same server at different points in its history, creating an inconsistent hybrid state.

The Fix

First, understand your current GTID state clearly before attempting any fix:

SHOW MASTER STATUS;
SELECT @@GLOBAL.gtid_executed;
SELECT @@GLOBAL.gtid_purged;

If you're setting up a new replica from a fresh backup, the cleanest approach is ensuring the backup and restore process handles GTID state automatically and correctly, rather than trying to set it manually. Using mysqldump with GTID support explicitly enabled handles this correctly:

mysqldump --all-databases --source-data=2 --single-transaction --set-gtid-purged=ON > backup.sql

Restore on the replica, letting the dump file's own SET @@GLOBAL.gtid_purged statement establish the correct, contiguous state automatically:

mysql < backup.sql

If you're stuck with an already-inconsistent GTID set and need to reset it, the safe path is resetting GTID state entirely and starting fresh from a known consistent point, since attempting to manually patch a "hole" is fragile and risks masking a genuine data consistency issue rather than actually resolving one:

STOP SLAVE;
RESET SLAVE ALL;
RESET MASTER;

This clears all GTID history — only do this on a replica you're comfortable fully re-provisioning from a fresh, known-good backup of the source, not on a server holding data you can't afford to lose or re-derive.

After resetting, re-establish replication from a fresh, consistent snapshot of the source:

CHANGE MASTER TO
  MASTER_HOST='source-db-host',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='repl_password',
  MASTER_AUTO_POSITION=1;
START SLAVE;

Verify replication is now healthy and the GTID sets align correctly between source and replica:

SHOW SLAVE STATUS\G

Check specifically that Slave_IO_Running and Slave_SQL_Running both show Yes, and that Seconds_Behind_Master is decreasing toward zero rather than stuck or growing.

Still Not Working?

If you can't afford a full reset and need to understand exactly what the gap actually represents before deciding how to proceed, compare the specific GTID ranges between source and replica directly to identify precisely which transaction IDs are missing:

-- On the source
SELECT @@GLOBAL.gtid_executed;

-- On the replica
SELECT @@GLOBAL.gtid_executed;
SELECT @@GLOBAL.gtid_purged;

Compare the two sets manually to identify the exact missing range, then investigate your binary log retention and backup history to understand whether those specific transactions are genuinely recoverable from an existing binary log archive or a different backup, rather than guessing at a fix without first understanding precisely what data the gap actually represents.

You might also like