How to Track Queries that violates GTID consistency:

MySQL & MariaDB
,
1

Hi Everyone,

I am trying to migrate from traditional binary log replication to GTID-based replication.
We have a database of size 6TB, and it is a write-heavy system containing many queries that violate GTID consistency.

By setting enforce_gtid_consistency to WARN, I see warnings in the error log whenever the database receives queries that violate GTID consistency, as shown below:

2025-02-11T17:34:54.756063Z 10 [Warning] [MY-013229] [Server] Statement violates GTID consistency: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE are not allowed inside a transaction or inside a procedure in a transactional context when @@session.binlog_format=STATEMENT.

However, I am unable to identify the exact statements or determine which users/hosts are executing these queries.

To successfully eliminate all GTID violations, I need to identify the hosts/users responsible for these queries.
I have attempted the following methods:

  1. General Log: While the general log records all statements, it does not indicate whether a query violated GTID consistency or resulted in an error or warning.
  2. Performance Schema.events_statements_history table: This table records queries but does not include user or host information.

Is there a way to accurately track the exact queries, along with the users/hosts from which they originate, when GTID consistency violations occur?

2

Hey @srikar_arutla,
Since you have the timestamp in the log, you can check the slow query log at that same timestamp. The slow log has the query, database, and user that executed the query.

1 Like
3

Hi @matthewb,
Thanks for your response.
To track all the queries, I have to set the long_query_time value to 0.
However, considering the server’s heavy workload, if every query is being recorded, it may impact performance.

is there any alternative solution to record just GTID Violated queries?

4

long_query_time=0 won’t be that much of an impact unless you’re doing 20,000 qps or more. You can always enable slow query sampling to log every other or every 3rd query if you’re concerned.

Not that I can think of. The only places were queries are logged at all are general_log, slow log, binary log, and audit log. None of those indicate if the logged query violates GTID consistency.

1 Like
5

Hi @matthewb ,

I will track those queries using slow log if that is the only way
Thank you for the response again.