If I set gtid_mode = on and enforce_gtid_consistency=off, the operations that are not allowed to use gtid is still replicated in other ways?

From mysql documentation:

Only statements that can be logged using GTID safe statements can be logged when enforce_gtid_consistency is set to ON, so the operations listed here cannot be used with this option:

CREATE TEMPORARY TABLE or DROP TEMPORARY TABLE statements inside transactions.

Transactions or statements that update both transactional and nontransactional tables. There is an exception that nontransactional DML is allowed in the same transaction or in the same statement as transactional DML, if all nontransactional tables are temporary.

CREATE TABLE ... SELECT statements, prior to MySQL 8.0.21. From MySQL 8.0.21, CREATE TABLE ... SELECT statements are allowed for storage engines that support atomic DDL.

If setting enforce_gtid_consistency=off, those transactions that cannot use gtid are replicated in some other way, but all other transactions are replicated using gtid? How it works?

1 Answer 1


I just create a lab to test it, and find out we even can't enable gtid_mode to on when enforce_gtid_consistency=off is off.

ERROR 3111 (HY000): SET @@GLOBAL.GTID_MODE = ON is not allowed because ENFORCE_GTID_CONSISTENCY is not ON.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.