Error
Error Code:
3117
MySQL Error 3117: GTID Consistency Violation
Description
This error indicates that MySQL has detected one or more active transactions that violate the server's Global Transaction Identifier (GTID) consistency requirements. It typically occurs when `ENFORCE_GTID_CONSISTENCY` is enabled, preventing transactions that are not GTID-safe from committing. This enforcement maintains data integrity and replication reliability within the database system.
Error Message
There are ongoing transactions that violate GTID consistency.
Known Causes
3 known causesNon-GTID-Safe Statements
Transactions contain SQL statements (e.g., `CREATE TEMPORARY TABLE` followed by DDL, or `CREATE TABLE ... SELECT` in specific contexts) that are incompatible with GTID consistency rules.
Strict GTID Enforcement
The `ENFORCE_GTID_CONSISTENCY` server variable is enabled, which strictly prevents transactions that do not adhere to GTID safety rules from executing.
Mixed DDL and DML Operations
A single transaction attempts to combine Data Definition Language (DDL) and Data Manipulation Language (DML) statements in a manner that violates GTID assignment logic.
Solutions
3 solutions available1. Identify and Rollback Inconsistent Transactions medium
Locate and terminate transactions that are causing the GTID inconsistency.
1
Connect to the MySQL server where the error is occurring.
2
Check for active transactions and their associated GTIDs. Look for transactions that might be in an inconsistent state or are long-running.
SHOW GLOBAL STATUS LIKE 'wsrep_provider_name';
SHOW PROCESSLIST;3
If you identify a suspicious or problematic transaction (e.g., very long running, or known to be problematic), you can attempt to roll it back. This might involve identifying the session ID and killing it. **Caution: This can lead to data loss for that specific transaction.**
KILL <process_id>;4
After terminating the transaction, attempt the operation that previously failed. It may now succeed.
2. Force GTID Synchronization (with Caution) advanced
Attempt to resynchronize GTIDs across the cluster, but be aware of potential implications.
1
This solution is typically applied in a Galera Cluster environment. Identify the node that is reporting the GTID consistency violation.
2
On the problematic node, temporarily stop the MySQL service.
sudo systemctl stop mysql # or appropriate command for your OS3
Locate the Galera State Snapshot (GSS) file. This file contains information about the cluster's state and GTIDs. The location can vary, but it's often in the data directory.
4
Edit the GSS file to force a specific GTID state. **This is a highly sensitive operation and requires deep understanding of GTIDs and your cluster's history. Incorrect modifications can lead to data divergence.** Consult Galera documentation for the exact procedure and syntax for your version.
5
Restart the MySQL service on the problematic node.
sudo systemctl start mysql # or appropriate command for your OS6
Monitor the cluster's replication status to ensure consistency is re-established.
SHOW GLOBAL STATUS LIKE 'wsrep_local_recv_queue_avg';
SHOW GLOBAL STATUS LIKE 'wsrep_cluster_size';3. Restarting the Cluster (as a Last Resort) advanced
A full cluster restart can sometimes resolve persistent GTID issues.
1
Ensure all critical operations are completed and that a maintenance window is available, as this will cause downtime for the entire cluster.
2
Gracefully shut down all nodes in the Galera Cluster. The order of shutdown can be important, often starting with the node that is not reporting issues.
sudo systemctl stop mysql # or appropriate command for your OS3
Once all nodes are shut down, start them one by one. It's generally recommended to start the node that is considered the 'primary' or has the most up-to-date state first.
sudo systemctl start mysql # or appropriate command for your OS4
Monitor the cluster's status and replication to confirm that GTID consistency is restored.
SHOW GLOBAL STATUS LIKE 'wsrep_local_recv_queue_avg';
SHOW GLOBAL STATUS LIKE 'wsrep_cluster_size';