Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6781

bug with query cache when using views

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 5.1.67, 5.2.14, 5.3.12, 5.5.39, 10.0.13
    • 5.5.40, 10.0.15
    • Query Cache
    • 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

            People

              sanja Oleksandr Byelkin
              rspadim roberto spadim
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.