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>