📋

Description

This error occurs when you attempt to set `ENFORCE_GTID_CONSISTENCY = ON` in MySQL, but the server detects active transactions that do not comply with GTID consistency rules. It indicates that some ongoing operations could lead to replication inconsistencies if GTID enforcement were enabled at that moment.

💬

Error Message

Cannot set ENFORCE_GTID_CONSISTENCY = ON because there are ongoing transactions that violate GTID consistency.

🔍

Known Causes

4 known causes

⚠️

Operations on Non-Transactional Tables

Ongoing transactions are performing operations on non-transactional storage engines (e.g., MyISAM, MEMORY) which inherently violate GTID consistency when mixed with transactional operations.

⚠️

GTID-Unsafe Statements Executed

Active transactions contain SQL statements (e.g., certain DDL or DML operations) that are considered unsafe for GTID replication.

⚠️

Mixed Transactional Operations

A single ongoing transaction is attempting to combine both GTID-consistent and GTID-inconsistent operations, making the entire transaction a violation.

⚠️

Existing Long-Running Transactions

Transactions that started prior to the attempt to enable GTID consistency may be ongoing and now violate the stricter enforcement rules.

🛠️

Solutions

3 solutions available

1. Identify and Resolve Violating Transactions advanced

Find and complete or rollback any transactions that are not GTID-consistent.

1

Connect to your MySQL server using a client like `mysql`.

mysql -u your_user -p

2

Check for transactions that are not GTID-consistent. This often involves looking for transactions that started before GTID was enabled or that have mixed GTID and non-GTID transactions.

SHOW GLOBAL STATUS LIKE 'Executed_Gtid_Set%';

3

Examine the output of `SHOW GLOBAL STATUS`. If you see any transactions that appear incomplete or are not aligned with GTID expectations, you'll need to address them. This might involve identifying the originating session and either committing or rolling back the transaction.

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_STATE <> 'RUNNING' AND TRX_AUTO_COMMIT = 0;

4

If you identify a specific transaction that needs to be rolled back, you can use its transaction ID. Obtain the transaction ID from `INFORMATION_SCHEMA.INNODB_TRX`.

KILL TRANSACTION <transaction_id>; -- Replace <transaction_id> with the actual ID

5

Once all non-GTID-consistent transactions are resolved, attempt to set `ENFORCE_GTID_CONSISTENCY = ON` again.

SET GLOBAL ENFORCE_GTID_CONSISTENCY = ON;

2. Restart MySQL Server with GTID Enabled medium

Restart the MySQL server with the correct configuration to enforce GTID consistency from the start.

1

Locate your MySQL configuration file (my.cnf or my.ini). Common locations include `/etc/my.cnf`, `/etc/mysql/my.cnf`, or `/etc/mysql/mysql.conf.d/mysqld.cnf`.

text

2

Edit the configuration file to ensure `gtid_mode = ON` (or `ON_PERMISSIVE` or `OFF`) and `enforce_gtid_consistency = ON` are set in the `[mysqld]` section. If `gtid_mode` is not already `ON`, you might need to change it first and restart before enabling `enforce_gtid_consistency`.

[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON

3

Save the configuration file.

text

4

Restart the MySQL service. The command varies depending on your operating system and init system.

sudo systemctl restart mysql

5

Verify that `ENFORCE_GTID_CONSISTENCY` is now `ON`.

SHOW GLOBAL VARIABLES LIKE 'enforce_gtid_consistency';

3. Temporarily Disable and Re-enable GTID Enforcement easy

A quick workaround to clear the state, but requires careful consideration of ongoing transactions.

1

Connect to your MySQL server.

mysql -u your_user -p

2

Temporarily disable GTID enforcement.

SET GLOBAL ENFORCE_GTID_CONSISTENCY = OFF;

3

Perform any necessary cleanup of transactions that might have been causing the violation. This step is crucial and might involve the methods described in Solution 1.

text

4

Once you are confident that no transactions are violating GTID consistency, re-enable enforcement.

SET GLOBAL ENFORCE_GTID_CONSISTENCY = ON;

5

Verify that GTID enforcement is now ON.

SHOW GLOBAL VARIABLES LIKE 'enforce_gtid_consistency';

🔗

Related Errors

5 related errors

MySQL Error 3116: Ongoing GTID Consistency Violations