Bug #99773 QueryCache: session track gitds info error when hit query result cache

• Submitted: 4 Jun 2020 3:10
• Modified: 4 Jun 2020 7:39
• Reporter: Ze Yang (OCA)
• Status: Verified
• Category: MySQL Server: Query Cache
• Severity: S3 (Non-critical)
• Version: 5.6, 5.7, 5.7.30
• OS: Any
• CPU Architecture: Any

[4 Jun 2020 3:10] Ze Yang

Description:
As in current query cache store all packet sent to client, when session_track_gtids is set, the query should not hit query result cache.

When session_track_gtids is set, if the query hit the query result cache, the gtids track info  returned will be the old info.

How to repeat:
query_cache_gtid_track-master.opt

```
--query_cache_type=1
--log-slave-updates --enforce-gtid-consistency --gtid-mode=ON
```

query_cache_gtid_track.test

```
--source include/have_log_bin.inc
--source include/have_gtid.inc
--source include/have_query_cache.inc

RESET MASTER;

--enable_session_track_info
CREATE TABLE t1 (c1 INT) Engine=InnoDB;
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (c1 INT) Engine=InnoDB;
INSERT INTO t2 VALUES (1);
set session session_track_gtids='all_gtids';
SELECT * FROM t1;
INSERT INTO t2 VALUES (2);
SELECT * FROM t1;
SELECT * FROM t1 WHERE 1;

DROP TABLE t1;
DROP TABLE t2;
```

query_cache_gtid_track.result
The server_uuid will be different when repeat run, but this have no influence on bug analysis.
When need the server_uuid be fixed, would add --debug=+d,server_uuid_deterministic in opt file and run with debug mode.

```
RESET MASTER;
CREATE TABLE t1 (c1 INT) Engine=InnoDB;
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (c1 INT) Engine=InnoDB;
INSERT INTO t2 VALUES (1);
set session session_track_gtids='all_gtids';
-- Tracker : SESSION_TRACK_GTIDS
-- 723cf33f-a60c-11ea-92f7-7cd30ac41dc0:1-4

SELECT * FROM t1;
c1
1
-- Tracker : SESSION_TRACK_GTIDS
-- 723cf33f-a60c-11ea-92f7-7cd30ac41dc0:1-4

INSERT INTO t2 VALUES (2);
-- Tracker : SESSION_TRACK_GTIDS
-- 723cf33f-a60c-11ea-92f7-7cd30ac41dc0:1-5

SELECT * FROM t1;
c1
1
-- Tracker : SESSION_TRACK_GTIDS
-- 723cf33f-a60c-11ea-92f7-7cd30ac41dc0:1-4  --track info error here

SELECT * FROM t1 WHERE 1;
c1
1
-- Tracker : SESSION_TRACK_GTIDS
-- 723cf33f-a60c-11ea-92f7-7cd30ac41dc0:1-5

DROP TABLE t1;
-- Tracker : SESSION_TRACK_GTIDS
-- 723cf33f-a60c-11ea-92f7-7cd30ac41dc0:1-6

DROP TABLE t2;
-- Tracker : SESSION_TRACK_GTIDS
-- 723cf33f-a60c-11ea-92f7-7cd30ac41dc0:1-7
```

Suggested fix:
reject hit query cache when track gtids set

diff --git a/sql/sql_cache.cc b/sql/sql_cache.cc
index 634e7bf9fc1..20e3cae2a62 100644
--- a/sql/sql_cache.cc
+++ b/sql/sql_cache.cc
@@ -1280,6 +1280,9 @@ void Query_cache::store_query(THD *thd, TABLE_LIST *tables_used)
   if (thd->variables.session_track_transaction_info != TX_TRACK_NONE)
     DBUG_VOID_RETURN;

+  if (thd->variables.session_track_gtids != OFF)
+    DBUG_VOID_RETURN;
+
   /*
     The query cache is only supported for the classic protocols.
     Although protocol_callback.cc is not compiled in embedded, there

[4 Jun 2020 7:39] MySQL Verification Team

Hello Ze Yang,

Thank you for the report and feedback.

regards,
Umesh