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
| Field | Original Value | New Value |
|---|---|---|
| Description |
must check if this is a bug of query cache, or mdev-4682 qc_info plugin {code} 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; {code} with {code} select QUERY_CACHE_ID,STATEMENT_SCHEMA,STATEMENT_TEXT from information_schema.QUERY_CACHE_QUERIES; {code} || *QUERY_CACHE_ID* || *STATEMENT_SCHEMA* || *STATEMENT_TEXT* || || 1 || teste || select * from t || || 2 || teste || select * from v2 || || 3 || teste || select * from v || {code} select * from information_schema.QUERY_CACHE_QUERIES_TABLES; {code} || *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 || {code} select * from information_schema.QUERY_CACHE_TABLES; {code} || *TABLE_SCHEMA* || *TABLE_NAME* || *TABLE_HASHED* || *TABLE_TYPE* || *QUERIES_IN_CACHE* || || teste || || 1 || NON_TRANSACT || 1 || <- THIS SHOULD BE v || teste || t || 1 || NON_TRANSACT || 3 || || teste || 2 || 1 || NON_TRANSACT || 1 || <- THIS SHOULD BE v2 |
must check if this is a bug of query cache, or mdev-4682 qc_info plugin {code} 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; {code} with {code} select QUERY_CACHE_ID,STATEMENT_SCHEMA,STATEMENT_TEXT from information_schema.QUERY_CACHE_QUERIES; {code} || *QUERY_CACHE_ID* || *STATEMENT_SCHEMA* || *STATEMENT_TEXT* || || 1 || teste || select * from t || || 2 || teste || select * from v2 || || 3 || teste || select * from v || {code} select * from information_schema.QUERY_CACHE_QUERIES_TABLES; {code} || *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 || {code} select * from information_schema.QUERY_CACHE_TABLES; {code} || *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 || |
| Description |
must check if this is a bug of query cache, or mdev-4682 qc_info plugin {code} 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; {code} with {code} select QUERY_CACHE_ID,STATEMENT_SCHEMA,STATEMENT_TEXT from information_schema.QUERY_CACHE_QUERIES; {code} || *QUERY_CACHE_ID* || *STATEMENT_SCHEMA* || *STATEMENT_TEXT* || || 1 || teste || select * from t || || 2 || teste || select * from v2 || || 3 || teste || select * from v || {code} select * from information_schema.QUERY_CACHE_QUERIES_TABLES; {code} || *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 || {code} select * from information_schema.QUERY_CACHE_TABLES; {code} || *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 || |
must check if this is a bug of query cache, or mdev-4682 qc_info plugin {code} 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; {code} with {code} select QUERY_CACHE_ID,STATEMENT_SCHEMA,STATEMENT_TEXT from information_schema.QUERY_CACHE_QUERIES; {code} || *QUERY_CACHE_ID* || *STATEMENT_SCHEMA* || *STATEMENT_TEXT* || || 1 || teste || select * from t || || 2 || teste || select * from v2 || || 3 || teste || select * from v || {code} select * from information_schema.QUERY_CACHE_QUERIES_TABLES; {code} || *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 || {code} select * from information_schema.QUERY_CACHE_TABLES; {code} || *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 {code} ... 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); ... {code} |
| Description |
must check if this is a bug of query cache, or mdev-4682 qc_info plugin {code} 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; {code} with {code} select QUERY_CACHE_ID,STATEMENT_SCHEMA,STATEMENT_TEXT from information_schema.QUERY_CACHE_QUERIES; {code} || *QUERY_CACHE_ID* || *STATEMENT_SCHEMA* || *STATEMENT_TEXT* || || 1 || teste || select * from t || || 2 || teste || select * from v2 || || 3 || teste || select * from v || {code} select * from information_schema.QUERY_CACHE_QUERIES_TABLES; {code} || *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 || {code} select * from information_schema.QUERY_CACHE_TABLES; {code} || *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 {code} ... 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); ... {code} |
must check if this is a bug of query cache, or mdev-4682 qc_info plugin {code} 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; {code} with {code} select QUERY_CACHE_ID,STATEMENT_SCHEMA,STATEMENT_TEXT from information_schema.QUERY_CACHE_QUERIES; {code} || *QUERY_CACHE_ID* || *STATEMENT_SCHEMA* || *STATEMENT_TEXT* || || 1 || teste || select * from t || || 2 || teste || select * from v2 || || 3 || teste || select * from v || {code} select * from information_schema.QUERY_CACHE_QUERIES_TABLES; {code} || *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 || {code} select * from information_schema.QUERY_CACHE_TABLES; {code} || *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 {code} ... 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); ... {code} --- 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) {code} 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 {code} |
| Description |
must check if this is a bug of query cache, or mdev-4682 qc_info plugin {code} 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; {code} with {code} select QUERY_CACHE_ID,STATEMENT_SCHEMA,STATEMENT_TEXT from information_schema.QUERY_CACHE_QUERIES; {code} || *QUERY_CACHE_ID* || *STATEMENT_SCHEMA* || *STATEMENT_TEXT* || || 1 || teste || select * from t || || 2 || teste || select * from v2 || || 3 || teste || select * from v || {code} select * from information_schema.QUERY_CACHE_QUERIES_TABLES; {code} || *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 || {code} select * from information_schema.QUERY_CACHE_TABLES; {code} || *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 {code} ... 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); ... {code} --- 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) {code} 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 {code} |
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 {code} 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; {code} with {code} select QUERY_CACHE_ID,STATEMENT_SCHEMA,STATEMENT_TEXT from information_schema.QUERY_CACHE_QUERIES; {code} || *QUERY_CACHE_ID* || *STATEMENT_SCHEMA* || *STATEMENT_TEXT* || || 1 || teste || select * from t || || 2 || teste || select * from v2 || || 3 || teste || select * from v || {code} select * from information_schema.QUERY_CACHE_QUERIES_TABLES; {code} || *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 || {code} select * from information_schema.QUERY_CACHE_TABLES; {code} || *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 {code} ... 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); ... {code} --- 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) {code} 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 {code} |
| Affects Version/s | 10.0.13 [ 16300 ] | |
| Affects Version/s | 5.5.39 [ 16301 ] | |
| Affects Version/s | 5.3.12 [ 12000 ] | |
| Affects Version/s | 5.2.14 [ 12101 ] | |
| Affects Version/s | 5.1.67 [ 12100 ] |
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 |
| Remote Link | This issue links to "patch (Web Link)" [ 19808 ] |
| Summary | possible bug with query cache when using views | bug with query cache when using views |
| Priority | Trivial [ 5 ] | Critical [ 2 ] |
| Affects Version/s | 10.1 [ 16100 ] |
| Fix Version/s | 10.0 [ 16000 ] | |
| Fix Version/s | 5.5 [ 15800 ] |
| 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 {code} 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; {code} with {code} select QUERY_CACHE_ID,STATEMENT_SCHEMA,STATEMENT_TEXT from information_schema.QUERY_CACHE_QUERIES; {code} || *QUERY_CACHE_ID* || *STATEMENT_SCHEMA* || *STATEMENT_TEXT* || || 1 || teste || select * from t || || 2 || teste || select * from v2 || || 3 || teste || select * from v || {code} select * from information_schema.QUERY_CACHE_QUERIES_TABLES; {code} || *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 || {code} select * from information_schema.QUERY_CACHE_TABLES; {code} || *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 {code} ... 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); ... {code} --- 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) {code} 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 {code} |
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 {code} 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; {code} with {code} select QUERY_CACHE_ID,STATEMENT_SCHEMA,STATEMENT_TEXT from information_schema.QUERY_CACHE_QUERIES; {code} || *QUERY_CACHE_ID* || *STATEMENT_SCHEMA* || *STATEMENT_TEXT* || || 1 || teste || select * from t || || 2 || teste || select * from v2 || || 3 || teste || select * from v || {code} select * from information_schema.QUERY_CACHE_QUERIES_TABLES; {code} || *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 || {code} select * from information_schema.QUERY_CACHE_TABLES; {code} || *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 {code} ... 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); ... {code} --- 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) {code} 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 {code} --- 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 || |
| Status | Open [ 1 ] | In Progress [ 3 ] |
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
| Fix Version/s | 10.0.15 [ 17300 ] | |
| Fix Version/s | 5.5.40 [ 17100 ] | |
| Fix Version/s | 5.5 [ 15800 ] | |
| Fix Version/s | 10.0 [ 16000 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Workflow | MariaDB v2 [ 55018 ] | MariaDB v3 [ 64838 ] |
| Workflow | MariaDB v3 [ 64838 ] | MariaDB v4 [ 148252 ] |
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