MySQL blocks turning ENFORCE_GTID_CONSISTENCY to ON because at least one open transaction contains statements that violate GTID rules.
MySQL error 3116 ER_CANT_SET_ENFORCE_GTID_CONSISTENCY_ON_WITH_ONGOING_GTID_VIOLATING_TRANSACTIONS occurs when you enable ENFORCE_GTID_CONSISTENCY while unsafe transactions are still open. Finish, rollback, or kill those sessions, then re-run SET GLOBAL ENFORCE_GTID_CONSISTENCY = ON to resolve the issue.
ER_CANT_SET_ENFORCE_GTID_CONSISTENCY_ON_WITH_ONGOING_GTID_VIOLATING_TRANSACTIONS
MySQL raises error 3116 when you try to change ENFORCE_GTID_CONSISTENCY from OFF to ON while at least one open transaction already executed statements that break GTID safety rules such as CREATE TEMPORARY TABLE or implicit XA operations.
The server refuses the request to protect replication integrity because GTID based replication needs every transaction to be deterministic and replayable on replicas.
Unfinished sessions that include unsafe statements violate GTID rules, so MySQL blocks the variable change until those transactions are closed.
Long running autocommit=0 workloads, interactive shells left inside transactions, or bulk loads that create temporary objects commonly trigger the problem.
Identify offending sessions with SHOW PROCESSLIST, then COMMIT, ROLLBACK, or KILL each connection. Afterwards execute SET GLOBAL ENFORCE_GTID_CONSISTENCY = ON; the command should now succeed.
If you require a rolling switch, place the server in read only mode, wait for existing writes to drain, verify no violating transactions remain, and finally enable enforcement.
Idle client consoles often keep transactions open. Simply issue COMMIT or exit the client to clear the lock and retry.
ETL jobs that use CREATE TEMPORARY TABLE cause violations. Rewrite the job to use normal tables or run it after GTID enforcement has been enabled.
Schedule GTID transitions during planned maintenance, set READ_ONLY=ON, and ensure applications disconnect before toggling ENFORCE_GTID_CONSISTENCY.
Adopt code reviews or Galaxy query endorsements to block unsafe statements early and keep production always GTID safe.
Error 1780 ER_GTID_UNSAFE_DEFINITION signals an unsafe DDL inside a GTID transaction. Remove or rewrite the statement to comply with GTID rules.
Error 1782 ER_GTID_UNSAFE_CREATE_SELECT appears when CREATE TABLE ... SELECT runs in GTID mode. Use INSERT ... SELECT into an existing table or refactor the workflow.
START TRANSACTION sessions not yet committed or rolled back still hold unsafe statements.
ETL tools that create temporary tables or non deterministic DDL block GTID enforcement until completion.
Developers sometimes forget to commit after making schema changes, leaving the session open indefinitely.
Frameworks that set autocommit=0 start transactions automatically and may include unsafe DDL.
Unsafe definition found during GTID mode. Resolve by rewriting or isolating the DDL.
CREATE TABLE ... SELECT detected as GTID unsafe. Replace with INSERT ... SELECT.
Replica servers must already run GTID mode to switch the source to ON.
Run SHOW PROCESSLIST or PERFORMANCE_SCHEMA tables to see sessions executing CREATE TEMPORARY TABLE or other unsafe operations, then act on those session IDs.
Use COMMIT or ROLLBACK first. If you must KILL the session, uncommitted changes are rolled back, preserving consistency but discarding pending work.
No. MySQL intentionally blocks the change to protect replication. All offending transactions must end before the variable can be set to ON.
Galaxy highlights unsafe statements during query review and lets teams endorse GTID safe queries, reducing the chance of violating transactions reaching production.
.avif)
.avif){kind=logo}
