# MySQL GTID Error: GTID Replication Failures and Recovery

Your MySQL GTID replication fails with errors:

bash
[ERROR] The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, 
        but the master has purged binary logs containing GTIDs that the slave requires.
[ERROR] Slave has additional GTIDs that are not present on master
[ERROR] GTID violation: Cannot execute statement with GTID_NEXT='uuid:123'
[ERROR] ER_GTID_MODE_OFF: Cannot set GTID_NEXT when GTID_MODE = OFF

GTID (Global Transaction Identifier) simplifies replication but introduces specific error patterns.

Understanding GTID

GTID format: source_id:transaction_number

Example: 3E11FA47-71CA-11E1-9E33-C80AA9429562:23

  • Each transaction gets a unique GTID
  • Slaves track executed GTIDs
  • Master auto-positions slaves based on missing GTIDs
  • Enables easy failover and replication topology changes

Step 1: Check GTID Status

```sql -- Check if GTID mode is enabled SHOW VARIABLES LIKE 'gtid_mode'; SHOW VARIABLES LIKE 'enforce_gtid_consistency';

-- Show executed GTIDs SHOW GLOBAL VARIABLES LIKE 'gtid_executed';

-- Show purged GTIDs SHOW GLOBAL VARIABLES LIKE 'gtid_purged';

-- On slave, show GTID status SHOW SLAVE STATUS\G -- Look for: -- Retrieved_Gtid_Set -- Executed_Gtid_Set -- Auto_Position ```

Step 2: Fix GTID Gaps

Slave missing transactions that master has purged:

bash
[ERROR] Master has purged binary logs containing GTIDs that the slave requires

Check what's purged:

```sql -- On master SHOW GLOBAL VARIABLES LIKE 'gtid_purged';

-- On slave SHOW GLOBAL VARIABLES LIKE 'gtid_executed'; SHOW SLAVE STATUS\G -- Compare Retrieved_Gtid_Set vs master's gtid_executed ```

Option A: Rebuild slave from fresh backup

```bash # On master, take backup mysqldump --single-transaction --set-gtid-purged=ON \ --all-databases --routines --triggers --events > master_backup.sql

# On slave mysql -e "STOP SLAVE; RESET SLAVE ALL;" mysql < master_backup.sql

# Set GTID_PURGED (mysqldump usually includes this) # Or manually: SET GLOBAL GTID_PURGED = '<gtid_set_from_master>';

# Configure replication CHANGE MASTER TO MASTER_HOST = 'master', MASTER_USER = 'repl', MASTER_PASSWORD = 'password', MASTER_AUTO_POSITION = 1; START SLAVE; ```

Option B: Skip missing GTIDs (causes data inconsistency)

sql
-- On slave, set missing GTIDs as purged
STOP SLAVE;
SET GLOBAL GTID_PURGED = '<missing_gtid_range>';
START SLAVE;

Calculate missing GTIDs:

```sql -- On master SELECT @@GLOBAL.gtid_executed;

-- On slave SELECT @@GLOBAL.gtid_executed;

-- Missing = master_executed - slave_executed -- Use GTID subtraction (manual calculation needed) ```

Step 3: Fix Slave Has Extra GTIDs

bash
[ERROR] Slave has additional GTIDs that are not present on master

This happens when: - Slave received transactions not from current master - Slave was previously a master or received from another source

```sql -- Check slave's executed GTIDs SHOW SLAVE STATUS\G -- Look at Executed_Gtid_Set

-- Check master's executed GTIDs -- On master: SHOW GLOBAL VARIABLES LIKE 'gtid_executed'; ```

Option A: Clear extra GTIDs by resetting slave

```sql STOP SLAVE; RESET SLAVE ALL; -- Removes all replication configuration

-- Reset GTID executed history RESET MASTER; -- On slave, clears gtid_executed and gtid_purged

-- Now rebuild from master backup ```

Option B: Ignore extra GTIDs (MySQL 5.7.6+)

sql
STOP SLAVE;
SET GLOBAL GTID_EXECUTED = '<master_gtid_set>';  -- Overwrite
START SLAVE;

Step 4: Fix GTID_NEXT Errors

bash
[ERROR] Cannot set GTID_NEXT when GTID_MODE = OFF
[ERROR] Statement violates GTID consistency

Enable GTID mode:

ini
[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON

Requires restart. Enabling sequence:

sql
-- Step by step (MySQL 5.7+)
SET GLOBAL gtid_mode = OFF_PERMISSIVE;
SET GLOBAL gtid_mode = ON_PERMISSIVE;
-- Wait for all anonymous transactions to clear
SHOW STATUS LIKE 'Ongoing_anonymous_transaction_count';
-- When 0:
SET GLOBAL enforce_gtid_consistency = ON;
SET GLOBAL gtid_mode = ON;

Fix GTID consistency violation:

```sql -- Error: Cannot execute statement because it violates GTID consistency

-- Check what statement failed -- Common causes: -- 1. CREATE TABLE ... SELECT (not allowed with GTID) -- 2. CREATE TEMPORARY TABLE within transaction -- 3. Updates to non-transactional tables within transaction

-- Fix CREATE TABLE SELECT: CREATE TABLE new_table LIKE source_table; INSERT INTO new_table SELECT * FROM source_table;

-- Fix temp tables: -- Use session GTID_NEXT: SET GTID_NEXT = 'ANONYMOUS'; CREATE TEMPORARY TABLE temp_data (...); -- Work with temp table DROP TEMPORARY TABLE temp_data; SET GTID_NEXT = 'AUTOMATIC'; ```

Step 5: Fix Duplicate GTID

bash
[ERROR] Duplicate GTID found: 'uuid:123' already exists

This means slave already executed a transaction it's receiving again:

```sql -- Check slave status SHOW SLAVE STATUS\G

-- Identify the duplicate GTID -- Look at Last_SQL_Error for the GTID number

-- Inject empty transaction to skip SET GTID_NEXT = 'uuid:123'; -- The duplicate GTID BEGIN; COMMIT; SET GTID_NEXT = 'AUTOMATIC';

START SLAVE SQL_THREAD; ```

Step 6: Handle Multi-Source GTID Conflicts

If slave replicates from multiple masters with overlapping GTIDs:

```sql -- Check replication channels SHOW SLAVE STATUS FOR CHANNEL 'channel_1'\G SHOW SLAVE STATUS FOR CHANNEL 'channel_2'\G

-- Each channel should have different source UUIDs -- Check UUIDs SELECT @@SERVER_UUID;

-- If same UUID, one source needs new UUID -- On problematic source: RESET MASTER; -- Generates new UUID ```

Step 7: Skip a Failed GTID Transaction

```sql -- Get failed GTID from SHOW SLAVE STATUS SHOW SLAVE STATUS\G -- Look at Retrieved_Gtid_Set and Executed_Gtid_Set -- Find the gap

-- Skip by injecting empty transaction SET GTID_NEXT = 'source_uuid:transaction_number'; BEGIN; COMMIT; SET GTID_NEXT = 'AUTOMATIC';

-- Restart SQL thread START SLAVE SQL_THREAD; ```

Step 8: Fix GTID After Slave Promotion

After promoting slave to master:

```sql -- On new master (former slave) -- Check GTID state SHOW GLOBAL VARIABLES LIKE 'gtid_executed';

-- Ensure no other server has conflicting GTIDs -- If there are, they need to reset or rebuild

-- On other slaves: STOP SLAVE; CHANGE MASTER TO MASTER_HOST = 'new_master', MASTER_AUTO_POSITION = 1; START SLAVE; ```

Step 9: Fix GTID Binary Log Purge

When purging old logs, GTIDs must be tracked:

```sql -- Check what will be lost SHOW BINARY LOGS; SELECT @@GLOBAL.gtid_executed;

-- Purge logs safely PURGE BINARY LOGS BEFORE '2024-01-01'; -- MySQL automatically updates gtid_purged

-- Verify SHOW GLOBAL VARIABLES LIKE 'gtid_purged'; ```

Step 10: Handle GTID with Non-Transactional Tables

MyISAM or other non-transactional tables with GTID:

```sql -- Error: Cannot execute statement in transaction involving non-transactional table

-- Option 1: Convert to InnoDB ALTER TABLE myisam_table ENGINE = InnoDB;

-- Option 2: Execute outside transaction SET GTID_NEXT = 'ANONYMOUS'; -- Execute non-transactional statements SET GTID_NEXT = 'AUTOMATIC';

-- Option 3: Disable enforce_gtid_consistency temporarily -- Not recommended for production SET GLOBAL enforce_gtid_consistency = OFF; ```

Step 11: Debug GTID Sequences

```sql -- View GTID intervals SELECT VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'gtid_executed';

-- Parse GTID set: UUID:1-5:10:20-25 means intervals 1-5, 10, 20-25

-- Check replication status for GTID details SELECT Retrieved_Gtid_Set, Executed_Gtid_Set, Auto_Position FROM performance_schema.replication_connection_status; ```

Step 12: Monitor GTID Replication

```sql -- Check for GTID gaps SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME IN ('gtid_executed', 'gtid_purged');

-- Check ongoing transactions SHOW STATUS LIKE 'Ongoing_automatic_gtid_violating_transaction_count'; SHOW STATUS LIKE 'Ongoing_anonymous_transaction_count'; ```

```bash #!/bin/bash # check_gtid.sh

MASTER_GTID=$(mysql -h master -N -e "SELECT @@GLOBAL.gtid_executed") SLAVE_GTID=$(mysql -N -e "SELECT @@GLOBAL.gtid_executed")

if [ "$MASTER_GTID" != "$SLAVE_GTID" ]; then echo "GTID mismatch: Master=$MASTER_GTID, Slave=$SLAVE_GTID" | \ mail -s "GTID Alert" admin@example.com fi ```

Step 13: Complete GTID Reset (Last Resort)

If GTID state is completely broken:

```bash # Stop all servers in replication topology # On each server:

mysql -e "STOP SLAVE; RESET SLAVE ALL;" mysql -e "RESET MASTER;" -- Clears GTID history

# Rebuild from designated master backup mysqldump --single-transaction --set-gtid-purged=ON \ --all-databases > master_backup.sql

# Restore on all slaves mysql < master_backup.sql

# Configure replication mysql -e "CHANGE MASTER TO MASTER_HOST='master', MASTER_AUTO_POSITION=1; START SLAVE;" ```

Best Practice GTID Configuration

```ini [mysqld] # Enable GTID gtid_mode = ON enforce_gtid_consistency = ON

# Binary logging required for GTID log_bin = mysql-bin binlog_format = ROW sync_binlog = 1

# For safe failover log_slave_updates = ON -- Slaves log received transactions

# Crash-safe relay_log_recovery = ON relay_log_info_repository = TABLE master_info_repository = TABLE ```

Quick Reference Commands

```sql -- Check GTID status SHOW VARIABLES LIKE 'gtid%'; SHOW GLOBAL VARIABLES LIKE 'gtid_executed'; SHOW GLOBAL VARIABLES LIKE 'gtid_purged';

-- Show slave GTID status SHOW SLAVE STATUS\G

-- Skip GTID transaction SET GTID_NEXT = 'uuid:tx_num'; BEGIN; COMMIT; SET GTID_NEXT = 'AUTOMATIC'; START SLAVE SQL_THREAD;

-- Set GTID purged SET GLOBAL GTID_PURGED = 'uuid:1-1000';

-- Reset GTID history RESET MASTER;

-- Enable auto-position CHANGE MASTER TO MASTER_AUTO_POSITION = 1;

-- Configure GTID step by step SET GLOBAL gtid_mode = OFF_PERMISSIVE; SET GLOBAL gtid_mode = ON_PERMISSIVE; SET GLOBAL enforce_gtid_consistency = ON; SET GLOBAL gtid_mode = ON; ```

Troubleshooting Checklist

  • [ ] Check GTID mode is ON on all servers
  • [ ] Verify enforce_gtid_consistency is ON
  • [ ] Compare master and slave gtid_executed
  • [ ] Check for gtid_purged gaps
  • [ ] Identify specific failing GTID
  • [ ] Skip transaction or rebuild as needed
  • [ ] Verify no conflicting GTIDs from multiple sources
  • [ ] Use MASTER_AUTO_POSITION = 1 for simpler management
  • [ ] Monitor for ongoing GTID transactions
  • [ ] Ensure log_slave_updates ON on slaves for failover