# GTID Consistency failed

**URL:** <https://forums.percona.com/t/gtid-consistency-failed/36533>\
**Category:** Percona Server for MySQL\
**Tags:** mysql, percona\
**Created:** [February 4, 2025, 7:45pm UTC](https://forums.percona.com/t/gtid-consistency-failed/36533 "2025-02-04T19:45:22Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![srikar\_arutla](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/srikar_arutla/32/20037_2.png) [@srikar\_arutla](https://forums.percona.com/u/srikar_arutla)\
**Post date:** [February 4, 2025, 7:45pm UTC](https://forums.percona.com/t/gtid-consistency-failed/36533/1 "2025-02-04T19:45:23Z")

</div>

I want to set enforce\_gtid\_consistency= WARN and gtid\_mode=ON\_PERMISSIVE , and eliminate all queires that violate GTID consistency from applications from the warnings.  
after that i plan to set enforce\_gtid\_consistency= ON and gtid\_mode=ON .  
However, in one of our test environment when is set enforce\_gtid\_consistency= WARN and gtid\_mode=ON\_PERMISSIVE or even enforce\_gtid\_consistency= ON and gtid\_mode=ON . the statements that are restricted with GTID are still being executed with out any warnings on command line and also in error log

mysql\> show global variables like ‘%gtid%’;  
±---------------------------------±---------------------------------------+  
| Variable\_name | Value |  
±---------------------------------±---------------------------------------+  
| binlog\_gtid\_simple\_recovery | ON |  
| enforce\_gtid\_consistency | WARN |  
| gtid\_executed | 6e91cf0b-c20e-11ef-ab76-0afffd63e349:1 |  
| gtid\_executed\_compression\_period | 0 |  
| gtid\_mode | ON\_PERMISSIVE |  
| gtid\_owned | |  
| gtid\_purged | |  
| session\_track\_gtids | OFF |  
±---------------------------------±---------------------------------------+  
8 rows in set (0.01 sec)

mysql\>  
mysql\>  
mysql\> use sri;  
Reading table information for completion of table and column names  
You can turn off this feature to get a quicker startup with -A

Database changed  
mysql\>  
mysql\>  
mysql\> CREATE TABLE test7 AS SELECT \* FROM test;  
Query OK, 3 rows affected (0.03 sec)  
Records: 3 Duplicates: 0 Warnings: 0

mysql\>  
mysql\> create temporary table ttt (id int);  
Query OK, 0 rows affected (0.00 sec)

* * *

mysql\> show global variables like ‘%gtid%’;  
±---------------------------------±-----------------------------------------+  
| Variable\_name | Value |  
±---------------------------------±-----------------------------------------+  
| binlog\_gtid\_simple\_recovery | ON |  
| enforce\_gtid\_consistency | WARN |  
| gtid\_executed | 6e91cf0b-c20e-11ef-ab76-0afffd63e349:1-2 |  
| gtid\_executed\_compression\_period | 0 |  
| gtid\_mode | ON\_PERMISSIVE |  
| gtid\_owned | |  
| gtid\_purged | |  
| session\_track\_gtids | OFF |  
±---------------------------------±-----------------------------------------+  
8 rows in set (0.01 sec)

mysql\>  
mysql\> set global enforce\_gtid\_consistency = ON;  
Query OK, 0 rows affected (0.00 sec)

mysql\> set global gtid\_mode = ON;  
Query OK, 0 rows affected (0.01 sec)

mysql\>  
mysql\> \r  
Connection id: 17  
Current database: sri

mysql\>  
mysql\>  
mysql\> create temporary table ttt (id int);  
Query OK, 0 rows affected (0.01 sec)

mysql\>  
mysql\> CREATE TABLE test9 AS SELECT \* FROM test;  
Query OK, 3 rows affected (0.03 sec)  
Records: 3 Duplicates: 0 Warnings: 0

mysql\>  
mysql\>  
mysql\> show tables;  
±--------------+  
| Tables\_in\_sri |  
±--------------+  
| authors |  
| authors\_bkp |  
| deadlocks |  
| test |  
| test1 |  
| test2 |  
| test3 |  
| test4 |  
| test7 |  
| test9 |  
±--------------+  
10 rows in set (0.00 sec)

mysql\>  
mysql\> show global variables like ‘%gtid%’;  
±---------------------------------±-----------------------------------------+  
| Variable\_name | Value |  
±---------------------------------±-----------------------------------------+  
| binlog\_gtid\_simple\_recovery | ON |  
| enforce\_gtid\_consistency | ON |  
| gtid\_executed | 6e91cf0b-c20e-11ef-ab76-0afffd63e349:1-3 |  
| gtid\_executed\_compression\_period | 0 |  
| gtid\_mode | ON |  
| gtid\_owned | |  
| gtid\_purged | |  
| session\_track\_gtids | OFF |  
±---------------------------------±-----------------------------------------+  
8 rows in set (0.01 sec)

mysql\>  
mysql\> create table myisam (id int) engine=MYISAM;  
Query OK, 0 rows affected (0.01 sec)

mysql\>  
mysql\> show create table myisam \G;  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
Table: myisam  
Create Table: CREATE TABLE `myisam` (  
`id` int DEFAULT NULL  
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4\_0900\_ai\_ci  
1 row in set (0.00 sec)

ERROR:  
No query specified

mysql\>  
mysql\> show create table test;  
±------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
| Table | Create Table |  
±------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
| test | CREATE TABLE `test` (  
`id` int NOT NULL AUTO\_INCREMENT,  
`name` varchar(50) DEFAULT NULL,  
`last` varchar(50) DEFAULT NULL,  
PRIMARY KEY (`id`),  
KEY `idx1` (`name`)  
) ENGINE=InnoDB AUTO\_INCREMENT=1004 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4\_0900\_ai\_ci |  
±------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
1 row in set (0.00 sec)

mysql\>  
mysql\> begin;  
Query OK, 0 rows affected (0.00 sec)

mysql\>  
mysql\> insert into myisam values (1);  
Query OK, 1 row affected (0.01 sec)

mysql\>  
mysql\> insert into test values (10,‘aaa’,‘bbb’);  
Query OK, 1 row affected (0.00 sec)

mysql\>  
mysql\> commit;  
Query OK, 0 rows affected (0.01 sec)

mysql\>  
mysql\>  
mysql\>

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [February 5, 2025, 6:37pm UTC](https://forums.percona.com/t/gtid-consistency-failed/36533/2 "2025-02-05T18:37:29Z")

</div>

Hello @srikar_arutla,  
Do you have binary logs enabled? GTID consistency is **not** checked if binary logs are disabled. In my example below, binlogs are enabled:

```auto
> show global variables like '%gtid%';
+----------------------------------+------------------------------------------+
| Variable_name | Value |
+----------------------------------+------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | 00008035-0000-0000-0000-000000008035:1-2 |
| gtid_executed_compression_period | 0 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+------------------------------------------+

> select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
| 1 |
+-----------+

> show create table t0\G
***************************1. row***************************
       Table: t0
Create Table: CREATE TABLE `t0` (
  `c0` mediumtext,
  `c1` enum('val0','val1') DEFAULT NULL,
  `c2` tinyblob
) ENGINE=InnoDB

> insert into t0 VALUES('blah', 'val0', 'blah');

> show master status\G
***************************1. row***************************
             File: binlog.000040
         Position: 450
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 00008035-0000-0000-0000-000000008035:1

 > create table mymy (uid int unsigned) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost:8035] {msandbox} (world) > BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8035] {msandbox} (world) > insert into t0 VALUES('blah', 'val0', 'blah');
Query OK, 1 row affected (0.00 sec)

mysql [localhost:8035] {msandbox} (world) > insert into mymy VALUES (1);
ERROR 1785 (HY000): 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.

```

---

<div class="post-metadata">

**Author:** ![srikar\_arutla](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/srikar_arutla/32/20037_2.png) [@srikar\_arutla](https://forums.percona.com/u/srikar_arutla)\
**Post date:** [February 11, 2025, 6:03pm UTC](https://forums.percona.com/t/gtid-consistency-failed/36533/3 "2025-02-11T18:03:52Z")

</div>

Hi matthewb,  
Thanks for your response.  
Binary logging was enabled by default, i have not made any changes

---

<div class="post-metadata">

**Author:** ![srikar\_arutla](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/srikar_arutla/32/20037_2.png) [@srikar\_arutla](https://forums.percona.com/u/srikar_arutla)\
**Post date:** [February 11, 2025, 6:30pm UTC](https://forums.percona.com/t/gtid-consistency-failed/36533/4 "2025-02-11T18:30:44Z")

</div>

Thanks mattewb,  
This has been resolved after restarting mysql