Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: 5.1.67, 5.2.14, 5.3.12, 5.5.39, 10.0.13
-
Component/s: Query Cache
-
Labels:None
Description
must check if this is a bug of query cache, or mdev-4682 qc_info plugin, but looking at sql_cache.cc i think that's something wrong with sql_cache.cc
reset query cache;
create table t (a int);
insert into t values (1);
insert into t values (2);
create view v as select * from t;
create view v2 as select * from t;
select * from t;
select * from v;
select * from v2;
with MDEV-4682 qc_info plugin:
select QUERY_CACHE_ID,STATEMENT_SCHEMA,STATEMENT_TEXT from information_schema.QUERY_CACHE_QUERIES;
| QUERY_CACHE_ID | STATEMENT_SCHEMA | STATEMENT_TEXT |
|---|---|---|
| 1 | teste | select * from t |
| 2 | teste | select * from v2 |
| 3 | teste | select * from v |
select * from information_schema.QUERY_CACHE_QUERIES_TABLES;
| QUERY_CACHE_ID | SCHEMA | TABLE |
|---|---|---|
| 1 | teste | t |
| 2 | teste | 2 <- THIS SHOULD BE v2 |
| 2 | teste | t |
| 3 | teste | <- THIS SHOULD BE v |
| 3 | teste | t |
select * from information_schema.QUERY_CACHE_TABLES;
| TABLE_SCHEMA | TABLE_NAME | TABLE_HASHED | TABLE_TYPE | QUERIES_IN_CACHE |
|---|---|---|---|---|
| teste | <- THIS SHOULD BE v | 1 | NON_TRANSACT <- this should be view? | 1 |
| teste | t | 1 | NON_TRANSACT | 3 |
| teste | 2 <- THIS SHOULD BE v2 | 1 | NON_TRANSACT <- this should be view? | 1 |
—
from sql_cache.cc
...
Query_cache::register_tables_from_list(THD *thd, TABLE_LIST *tables_used,
TABLE_COUNTER_TYPE counter,
Query_cache_block_table **block_table)
...
DBUG_PRINT("qcache", ("view: %s db: %s",
tables_used->view_name.str,
tables_used->view_db.str));
key_length= get_table_def_key(tables_used, &key);
/*
There are not callback function for for VIEWs
*/
if (!insert_table(key_length, key, (*block_table),
tables_used->view_db.length + 1, 0, <----- maybe we shouldn't use +1????
HA_CACHE_TBL_NONTRANSACT, 0, 0, TRUE))
DBUG_RETURN(0);
/*
We do not need to register view tables here because they are already
present in the global list.
*/
...
DBUG_PRINT("qcache",
("table: %s db: %s openinfo: 0x%lx keylen: %lu key: 0x%lx",
tables_used->table->s->table_name.str,
tables_used->table->s->table_cache_key.str,
(ulong) tables_used->table,
(ulong) tables_used->table->s->table_cache_key.length,
(ulong) tables_used->table->s->table_cache_key.str));
if (!insert_table(tables_used->table->s->table_cache_key.length,
tables_used->table->s->table_cache_key.str,
(*block_table),
tables_used->db_length, 0, <- check that for tables we don't use +1 ?!
tables_used->table->file->table_cache_type(),
tables_used->callback_func,
tables_used->engine_data,
TRUE))
DBUG_RETURN(0);
if (tables_used->table->file->
register_query_cache_dependant_tables(thd, this, block_table, &n))
DBUG_RETURN(0);
...
—
about non transact with views, i think it's an old point about how store cache information since myisam merge use it as nontransact and connect too, and both don't store anything (like a view)
maybe we could include a HA_CACHE_TBL_VIEW or HA_CACHE_TBL_NO_STORAGE just to look better than NON_TRANSACT (ok it's =0 and don't change anything)
C:\GIT\mariadb-tmp\server>grep HA_CACHE_TBL_NONTRANSACT * -r plugin/qc_info/qc_info.cc: if((query_cache_query->tables_type() & HA_CACHE_TBL_NONTRANSACT) == HA_CACHE_TBL_NONTRANSACT){ plugin/qc_info/qc_info.cc: /* every body is nontransact since HA_CACHE_TBL_NONTRANSACT == 0 */ plugin/qc_info/qc_info.cc: if(query_cache_table_entry->table_type==HA_CACHE_TBL_NONTRANSACT) sql/handler.h:#define HA_CACHE_TBL_NONTRANSACT 0 sql/handler.h: virtual uint8 table_cache_type() { return HA_CACHE_TBL_NONTRANSACT; } sql/ha_partition.cc: HA_CACHE_TBL_NONTRANSACT - because all changes goes through partition table sql/sql_cache.cc: HA_CACHE_TBL_NONTRANSACT, 0, 0, TRUE)) sql/sql_cache.cc: *tables_type|= HA_CACHE_TBL_NONTRANSACT; storage/connect/ha_connect.h: return HA_CACHE_TBL_NONTRANSACT; storage/myisammrg/ha_myisammrg.cc: (*tables_type)|= HA_CACHE_TBL_NONTRANSACT; storage/myisammrg/ha_myisammrg.cc: but it has no effect because HA_CACHE_TBL_NONTRANSACT is 0
—
solved:
yeap that's a bug 
different (and correct) result with patch:
| TABLE_SCHEMA | TABLE_NAME | TABLE_HASHED | TABLE_TYPE | QUERIES_IN_CACHE |
|---|---|---|---|---|
| teste | vtv | 1 | NON_TRANSACT | 1 |
| teste | tt | 1 | NON_TRANSACT | 3 |
| teste | v | 1 | NON_TRANSACT | 1 |
| QUERY_CACHE_ID | SCHEMA | TABLE |
|---|---|---|
| 1 | teste | v |
| 1 | teste | tt |
| 2 | teste | tt |
| 3 | teste | vtv |
| 3 | teste | tt |
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
yeap that's a bug
different (and correct) result with patch:
| TABLE_SCHEMA | TABLE_NAME | TABLE_HASHED | TABLE_TYPE | QUERIES_IN_CACHE |
|---|---|---|---|---|
| teste | vtv | 1 | NON_TRANSACT | 1 |
| teste | tt | 1 | NON_TRANSACT | 3 |
| teste | v | 1 | NON_TRANSACT | 1 |
| QUERY_CACHE_ID | SCHEMA | TABLE |
|---|---|---|
| 1 | teste | v |
| 1 | teste | tt |
| 2 | teste | tt |
| 3 | teste | vtv |
| 3 | teste | tt |
Thank you for finding it! The patch is OK!
(I'll just think in what version it should be fixed, probably 5.5 as statet here)
well i think it's an old bug, maybe any version with query cache, that's not a biiig problem, but it's a problem since invalidation take some time
there's other problem with query cache and aria storage, that's a "big" problem, something like concurrent insert and query cache not working ok resulting in old values being query cached instead of last insert value, there's a open MDEV-6817, if you could take a look, i think it's an important bug since it make aria+query cache unusable
Pushed to 5.5.
Thank you!
nice =]
can't use query_cache_tables / query_cache_queries_tables with wrong table name reported by qc_info plugin, this bug block many uses of qc_info plugin