[MDEV-27047] replication does not remove affected queries from query cache Created: 2021-11-15  Updated: 2022-05-03  Resolved: 2021-11-16

Status: Closed
Project: MariaDB Server
Component/s: Query Cache, Replication
Affects Version/s: 10.5.13, 10.6.5
Fix Version/s: 10.5.14, 10.6.6

Type: Bug Priority: Critical
Reporter: Joerg Michels Assignee: Marko Mäkelä
Resolution: Fixed Votes: 0
Labels: regression
Environment:

CentOS 8

Attachments: File db01.cnf     File db03.cnf    
Issue Links:
Blocks
Duplicate
is duplicated by MDEV-28212 Query Cache on Primary-Secondary-Seco... Closed
Relates

 Description   

Since the upgrade to MariaDB 10.5.13 we are facing the problem where replication no longer removes affected queries from the query cache.

We have this problem on at least two independent systems.

How to reproduce

On replication master:
> create table test (number_col int);
> insert into test set number_col=1;

On slave:
> select * from test;

+------------+
 
| number_col |
 
+------------+
 
|          1 |
 
+------------+
 
1 row in set (0.001 sec)

On master:
> insert into test set number_col=2;

On slave:
> select * from test;

+------------+
 
| number_col |
 
+------------+
 
|          1 |
 
+------------+
 
1 row in set (0.001 sec)

> select SQL_NO_CACHE * from test;

+------------+
 
| number_col |
 
+------------+
 
|          1 |
 
|          2 |
 
+------------+
 
2 rows in set (0.001 sec)

with kind regards,

Joerg

 Comments   
Comment by Alice Sherepa [ 2021-11-15 ]

could you please add your .cnf file(s). I tried to repeat it, but failed

Comment by Joerg Michels [ 2021-11-15 ]

db01.cnf is from master
db03.cnf is from slave

Comment by Alice Sherepa [ 2021-11-15 ]

Thank you!
I reproduced as described on 10.5-10.7, with row binlog format.

-- source include/have_binlog_format_row.inc
 
-- source include/have_innodb.inc
 
-- source include/master-slave.inc
 
 
 
--connection slave
 
SET @qtype= @@global.query_cache_type;
 
SET GLOBAL query_cache_type= ON;
 
SET query_cache_type= ON;
 
 
 
--connection master
 
create table t1 (i int) engine=innodb;
 
insert into t1 set i=1;
 
 
 
--sync_slave_with_master
 
 
 
select * from t1;
 
--connection master
 
insert into t1 set i=2;
 
 
 
--sync_slave_with_master
 
select * from t1;
 
 
 
select sql_no_cache * from t1;
 
 
 
--connection master
 
DROP TABLE t1;
 
--sync_slave_with_master
 
SET GLOBAL query_cache_type= @qtype;
 
--source include/rpl_end.inc

Comment by Sergei Golubchik [ 2021-11-15 ]

caused by merge 5f8561a6bc, in particular:

--- a/sql/log_event_server.cc
 
+++ b/sql/log_event_server.cc
 
@@ -5573,20 +5573,14 @@ int Rows_log_event::do_apply_event(rpl_group_info *rgi)
 
       }
 
     }
 
 
-#ifdef HAVE_QUERY_CACHE
 
-#ifdef WITH_WSREP
 
+#if defined(WITH_WSREP) && defined(HAVE_QUERY_CACHE)
 
     /*
 
       Moved invalidation right before the call to rows_event_stmt_cleanup(),
 
       to avoid query cache being polluted with stale entries,
 
     */
 
-    if (! (WSREP(thd) && wsrep_thd_is_applying(thd)))
 
-    {
 
-#endif /* WITH_WSREP */
 
+    if (WSREP(thd) && wsrep_thd_is_applying(thd))
 
       query_cache.invalidate_locked_for_write(thd, rgi->tables_to_lock);
 
-#ifdef WITH_WSREP
 
-    }
 
-#endif /* WITH_WSREP */
 
-#endif
 
+#endif /* WITH_WSREP && HAVE_QUERY_CACHE */
 
   }
 
 
   table= m_table= rgi->m_table_map.get_table(m_table_id);

which reverted the condition in if(). And also disabled query cache invalidation when WSREP is not compiled in.

Generated at Thu Feb 08 09:49:56 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.