MySQL Error 1842: ER_GTID_PURGED_WAS_CHANGED - How to Fix and Prevent

What does MySQL Error 1842 mean?

The exact message is: "@@GLOBAL.GTID_PURGED was changed from '%s' to '%s'." MySQL raises it when the global GTID_PURGED value differs between the start and end of a statement that needs the variable to remain unchanged.

The guard prevents mismatched GTID bookkeeping, which could corrupt replication topology or make point-in-time recovery impossible.

When does the error occur?

It shows up during SET GLOBAL GTID_PURGED, mysqlbinlog --preserve statements, logical dumps containing SET GTID_PURGED, or server startup scripts that manipulate GTID sets.

Any concurrent action such as RESET MASTER, PURGE BINARY LOGS, or automatic binlog expiration that rewrites GTID_PURGED while your session is running can trigger the mismatch.

Why is it critical to fix?

If left unresolved, GTID inconsistencies break replication, hinder failover, and block incremental backups that rely on monotonic GTID sequences.

Production replicas may refuse to connect or apply new transactions, leading to data drift and prolonged downtime.

What Causes This Error?

The primary cause is concurrent modification of @@GLOBAL.GTID_PURGED by another thread or internal purge event.

Running SET GTID_PURGED with a stale value taken earlier in the session is another frequent trigger, especially during restores.

How to Fix MySQL Error 1842

First, capture the current GTID sets: SHOW VARIABLES LIKE 'gtid_purged' and 'gtid_executed'. Verify your intended GTID_PURGED is a subset of GTID_EXECUTED.

Lock the server to prevent further GTID changes: FLUSH TABLES WITH READ LOCK; SET SESSION sql_log_bin = 0; Then run SET GLOBAL GTID_PURGED = 'new_set'; followed by UNLOCK TABLES.

Common Scenarios and Solutions

If you are importing a dump, remove the embedded SET @@GLOBAL.GTID_PURGED line and apply the correct value after the load completes under a global read lock.

For replication re-initialisation, stop all replication threads, RESET MASTER on the replica, copy GTID_EXECUTED from the source, then run SET GLOBAL GTID_PURGED using a single administrative session.

Best Practices to Avoid This Error

Schedule binlog purges during maintenance windows so they do not clash with manual GTID operations.

Always execute SET GTID_PURGED in single-user mode or while holding a global read lock to eliminate concurrent changes.

Automate GTID bookkeeping with version-controlled scripts in Galaxy so the exact statements are peer-reviewed and replayable.

Related Errors and Solutions

1841 ER_GTID_INDEX_NOT_IMPLEMENTED indicates an invalid GTID index was referenced, usually fixed by correcting the GTID string format.

1236 ER_MASTER_FATAL_ERROR_READING_BINLOG shows up when replicas cannot parse a purged GTID event; resync the replica from a fresh backup.