GTID: Extra transaction on slave before replication setup

[akuzminsky]

Problem

When setting up Percona replication with GTID, slaves end up with an extra transaction that the master doesn't have.

Slave shows:

mysql> show master status\G
Executed_Gtid_Set: 1c75ba85-f4df-11f0-ae97-02ffd8f80c5d:1,
bf30f9b7-f4de-11f0-8310-06ed89e4c4bd:1-13

Master shows:

mysql> show master status\G
Executed_Gtid_Set: bf30f9b7-f4de-11f0-8310-06ed89e4c4bd:1-13

The extra GTID (1c75ba85-f4df-11f0-ae97-02ffd8f80c5d:1) is the slave's own server UUID with transaction :1.

Analysis

The binlog on the slave shows this transaction in Previous-GTIDs, meaning it happened before replication was configured. Something wrote to MySQL on the slave before CHANGE MASTER TO was executed.

The user creation and other operations ARE replicating correctly from master - this is not the issue.

Impact

With GTID replication, any write on a slave creates a local transaction that the master doesn't have. This can cause issues during failover scenarios.

Proposed Fix

Run RESET MASTER on fresh replicas before configuring replication to clear local GTID history:

RESET MASTER;  -- Clear local GTIDs
CHANGE MASTER TO MASTER_HOST=..., MASTER_AUTO_POSITION=1;
START SLAVE;

In Puppet, ensure the order is:

1. Install MySQL
2. RESET MASTER (if this is a new replica)
3. Configure CHANGE MASTER TO
4. START SLAVE

Need to investigate what's causing the write before replication setup and either:

• Prevent it from happening, or
• Add RESET MASTER before replication configuration

[akuzminsky]

Root Cause Found

The problematic transaction is:

ALTER USER 'root'@'localhost' IDENTIFIED WITH 'auth_socket'

From binlog on slave:

#260119  2:32:44 server id 25860  end_log_pos 381
SET @@SESSION.GTID_NEXT= '1c75ba85-f4df-11f0-ae97-02ffd8f80c5d:1'/*!*/;
ALTER USER 'root'@'localhost' IDENTIFIED WITH 'auth_socket'

This runs on ALL nodes via Puppet before replication is configured, creating a local GTID on each slave.

Fix options:

1. Only run this on master (let it replicate to slaves)
2. Wrap with SET sql_log_bin = 0 when running on slaves
3. Run RESET MASTER on slaves before configuring replication