Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
5.1.67, 5.2.14, 5.3.12, 5.5.39, 10.0.13
-
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 |
Attachments
Issue Links
Activity
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
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