Error
Error Code:
1785
MySQL Error 1785: GTID Consistency Violation
Description
This error indicates an attempt to perform an operation that compromises Global Transaction Identifier (GTID) consistency, specifically when modifying non-transactional tables. It typically occurs in GTID-enabled environments where updates to non-transactional tables are not performed in autocommitted statements or single-statement transactions, or when mixed with updates to transactional tables.
Error Message
Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.
Known Causes
3 known causesMixing Transactional and Non-Transactional Tables
A single SQL statement attempts to update both transactional (e.g., InnoDB) and non-transactional (e.g., MyISAM) tables simultaneously. This is a direct violation of GTID consistency rules.
Non-Transactional Update in Multi-Statement Transaction
An update to a non-transactional table is performed within a multi-statement transaction context, rather than as an autocommitted statement or a single-statement transaction.
Incorrect Autocommit State for Non-Transactional Tables
Updates to non-transactional tables are attempted when the `autocommit` session variable is disabled, and the operation is not part of a single-statement transaction.
Solutions
3 solutions available1. Separate Transactional and Non-Transactional Table Updates easy
Execute DML statements on transactional and non-transactional tables in separate transactions.
1
Identify if your SQL statement is attempting to modify both transactional (e.g., InnoDB) and non-transactional (e.g., MyISAM) tables within a single statement. If so, break it down.
Example of a problematic statement:
UPDATE my_innodb_table SET column = 'value' WHERE id = 1;
INSERT INTO my_myisam_table (column) VALUES ('new_value');2
Execute statements modifying transactional tables within explicit transactions or as autocommitted statements. Then, execute statements modifying non-transactional tables as separate autocommitted statements.
Solution:
-- For transactional tables (e.g., InnoDB)
START TRANSACTION;
UPDATE my_innodb_table SET column = 'value' WHERE id = 1;
COMMIT;
-- For non-transactional tables (e.g., MyISAM)
INSERT INTO my_myisam_table (column) VALUES ('new_value');2. Ensure All Tables are Transactional medium
Convert all tables to a transactional storage engine like InnoDB.
1
Identify tables using non-transactional storage engines (e.g., MyISAM). You can do this by querying the `information_schema.TABLES` table.
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE ENGINE != 'InnoDB';2
For each identified non-transactional table, convert it to InnoDB. This typically involves an `ALTER TABLE` statement. **Note:** This operation can take a significant amount of time and disk space for large tables. Consider performing this during a maintenance window.
ALTER TABLE your_table_name ENGINE=InnoDB;3
After conversion, re-run your original statement. It should now be consistent with GTID.
Your original statement that was failing, e.g.:
UPDATE my_innodb_table SET column = 'value' WHERE id = 1;
INSERT INTO my_myisam_table (column) VALUES ('new_value');3. Disable GTID Temporarily (for Development/Testing) easy
Temporarily disable GTID to allow mixed transactional/non-transactional updates during development or testing phases.
1
Log in to your MySQL server as a user with sufficient privileges (e.g., root).
2
Set the `gtid_mode` and `enforce_gtid_consistency` system variables to OFF. **WARNING:** This should **never** be done on production systems as it breaks the guarantees of GTID.
SET GLOBAL gtid_mode = OFF;
SET GLOBAL enforce_gtid_consistency = OFF;3
Execute your mixed transactional and non-transactional statements.
Your original statement that was failing, e.g.:
UPDATE my_innodb_table SET column = 'value' WHERE id = 1;
INSERT INTO my_myisam_table (column) VALUES ('new_value');4
Crucially, re-enable GTID and consistency enforcement before proceeding with any further operations or restarting the server.
SET GLOBAL gtid_mode = ON;
SET GLOBAL enforce_gtid_consistency = ON;