--- qc_info.old.cc 2013-05-25 04:17:46.360703435 -0300 +++ qc_info.cc 2013-05-25 17:33:05.911189120 -0300 @@ -29,7 +29,7 @@ /* - * TODO: report query cache flags + * TODO: report query cache flags with "human readable" values */ #ifndef MYSQL_SERVER #define MYSQL_SERVER @@ -41,6 +41,7 @@ #include // THD #include // ST_SCHEMA_TABLE #include +#include class Accessible_Query_Cache : public Query_cache { public: @@ -48,43 +49,173 @@ { return &this->queries; } + HASH *get_tables() + { + return &this->tables; + } } *qc; bool schema_table_store_record(THD *thd, TABLE *table); #define MAX_STATEMENT_TEXT_LENGTH 32767 -#define COLUMN_STATEMENT_SCHEMA 0 -#define COLUMN_STATEMENT_TEXT 1 -#define COLUMN_RESULT_BLOCKS_COUNT 2 -#define COLUMN_RESULT_BLOCKS_SIZE 3 -#define COLUMN_RESULT_BLOCKS_SIZE_USED 4 +#define COLUMN_ENTRY_POSITION_IN_CACHE 0 +#define COLUMN_STATEMENT_SCHEMA 1 +#define COLUMN_STATEMENT_TEXT 2 +#define COLUMN_RESULT_FOUND_ROWS 3 +#define COLUMN_QUERY_HITS 4 +#define COLUMN_SELECT_EXPEND_TIME 5 +#define COLUMN_SELECT_ROWS_READ 6 +#define COLUMN_TABLES 7 +#define COLUMN_RESULT_LENGTH 8 +#define COLUMN_RESULT_BLOCKS_COUNT 9 +#define COLUMN_RESULT_BLOCKS_SIZE 10 +#define COLUMN_RESULT_BLOCKS_SIZE_USED 11 +#define COLUMN_RESULT_TABLES_TYPE 12 +#define COLUMN_FLAGS_CLIENT_LONG_FLAG 13 +#define COLUMN_FLAGS_CLIENT_PROTOCOL_41 14 +#define COLUMN_FLAGS_PROTOCOL_TYPE 15 +#define COLUMN_FLAGS_MORE_RESULTS_EXISTS 16 +#define COLUMN_FLAGS_IN_TRANS 17 +#define COLUMN_FLAGS_AUTOCOMMIT 18 +#define COLUMN_FLAGS_PKT_NR 19 +#define COLUMN_FLAGS_CHARACTER_SET_CLIENT_NUM 19 +#define COLUMN_FLAGS_CHARACTER_SET_RESULTS_NUM 20 +#define COLUMN_FLAGS_COLLATION_CONNECTION_NUM 21 +#define COLUMN_FLAGS_LIMIT 22 +/*#define COLUMN_FLAGS_TIME_ZONE 23*/ +#define COLUMN_FLAGS_SQL_MODE 23 +#define COLUMN_FLAGS_MAX_SORT_LENGTH 24 +#define COLUMN_FLAGS_GROUP_CONCAT_MAX_LEN 25 +#define COLUMN_FLAGS_DIV_PRECISION_INCREMENT 26 +#define COLUMN_FLAGS_DEFAULT_WEEK_FORMAT 27 +/*#define COLUMN_FLAGS_LC_TIME_NAMES 28*/ /* ST_FIELD_INFO is defined in table.h */ -static ST_FIELD_INFO qc_info_fields[]= +/* table queries */ +static ST_FIELD_INFO qc_info_fields_queries[]= { + {"ENTRY_POSITION_IN_CACHE", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, 0, 0}, {"STATEMENT_SCHEMA", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0, 0}, {"STATEMENT_TEXT", MAX_STATEMENT_TEXT_LENGTH, MYSQL_TYPE_STRING, 0, 0, 0, 0}, + {"RESULT_FOUND_ROWS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, 0, 0, 0}, + {"QUERY_HITS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, 0, 0, 0}, + {"SELECT_EXPEND_TIME", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, 0, 0, 0}, + {"SELECT_ROWS_READ", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, 0, 0, 0}, + {"TABLES", MAX_STATEMENT_TEXT_LENGTH, MYSQL_TYPE_STRING, 0, 0, 0, 0}, + + {"RESULT_LENGTH", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, 0, 0}, {"RESULT_BLOCKS_COUNT", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, 0, 0}, - {"RESULT_BLOCKS_SIZE", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, 0, 0, 0}, - {"RESULT_BLOCKS_SIZE_USED", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, 0, 0, 0}, + {"RESULT_BLOCKS_SIZE", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, 0, 0, 0}, + {"RESULT_BLOCKS_SIZE_USED", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, 0, 0, 0}, + {"RESULT_TABLES_TYPE", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, 0, 0}, + {"FLAGS_CLIENT_LONG_FLAG", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, 0, 0}, + {"FLAGS_CLIENT_PROTOCOL_41", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, 0, 0}, + {"FLAGS_PROTOCOL_TYPE", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, 0, 0}, + {"FLAGS_MORE_RESULTS_EXISTS", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, 0, 0}, + {"FLAGS_IN_TRANS", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, 0, 0}, + {"FLAGS_AUTOCOMMIT", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, 0, 0}, + {"FLAGS_PKT_NR", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, 0, 0}, + {"FLAGS_CHARACTER_SET_CLIENT_NUM", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, 0, 0}, + {"FLAGS_CHARACTER_SET_RESULTS_NUM", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, 0, 0}, + {"FLAGS_COLLATION_CONNECTION_NUM", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, 0, 0}, + {"FLAGS_LIMIT", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, 0, 0}, +/* {"FLAGS_TIME_ZONE", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, 0, 0}, */ + {"FLAGS_SQL_MODE", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, 0, 0, 0}, + {"FLAGS_MAX_SORT_LENGTH", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, 0, 0}, + {"FLAGS_GROUP_CONCAT_MAX_LEN", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, 0, 0}, + {"FLAGS_DIV_PRECISION_INCREMENT", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, 0, 0}, + {"FLAGS_DEFAULT_WEEK_FORMAT", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, 0, 0}, +/* {"FLAGS_LC_TIME_NAMES", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, 0, 0}, */ + {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, 0} }; -static int qc_info_fill_table(THD *thd, TABLE_LIST *tables, +/* table tables */ +static ST_FIELD_INFO qc_info_fields_tables[]= +{ + {"TABLE_SCHEMA", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0, 0}, + {"TABLE_NAME", MAX_STATEMENT_TEXT_LENGTH, MYSQL_TYPE_STRING, 0, 0, 0, 0}, + {"TABLE_HASHED", MAX_TINYINT_WIDTH, MYSQL_TYPE_TINY, 0, 0, 0, 0}, + {"QUERIES_IN_CACHE", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, 0, 0}, + {"ENTRY_POSITION_IN_CACHE", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, 0, 0}, + + {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, 0} +}; + +static int qc_info_fill_table_tables(THD *thd, TABLE_LIST *tables, COND *cond) { int status= 1; CHARSET_INFO *scs= system_charset_info; TABLE *table= tables->table; - HASH *queries = qc->get_queries(); + HASH *qc_tables = qc->get_tables(); /* one must have PROCESS privilege to see others' queries */ if (check_global_access(thd, PROCESS_ACL, true)) return 0; +/* if (qc->query_cache_size==0 || thd->variables.query_cache_type==0) */ + if (qc->is_disabled()) + return 0; + if (qc->try_lock(thd)) return status; + + /* loop through all queries in the query cache */ + for (uint i= 0; i < qc_tables->records; i++) + { + const uchar *query_cache_block_raw; + Query_cache_block* query_cache_block; + Query_cache_table* query_cache_table_entry; + char *table_name, *db_name; + size_t table_name_length, db_name_length; + + query_cache_block_raw = my_hash_element(qc_tables, i); + query_cache_block = (Query_cache_block*)query_cache_block_raw; + if (query_cache_block->type != Query_cache_block::TABLE) + continue; + query_cache_table_entry = query_cache_block->table(); + + table_name=query_cache_table_entry->table(); + table_name_length=strlen(table_name); + db_name=query_cache_table_entry->db(); + db_name_length=strlen(db_name); + + + table->field[0]->store(db_name, db_name_length, scs); + table->field[1]->store(table_name, table_name_length, scs); + table->field[2]->store(query_cache_table_entry->is_hashed(), 0 ); + table->field[3]->store(query_cache_table_entry->m_cached_query_count, 0 ); + table->field[4]->store((long)i, 0 ); + + if (schema_table_store_record(thd, table)) + goto cleanup; + } + status = 0; +cleanup: + qc->unlock(); + return status; +} +static int qc_info_fill_table_queries(THD *thd, TABLE_LIST *tables, + COND *cond) +{ + int status= 1; + CHARSET_INFO *scs= system_charset_info; + TABLE *table= tables->table; + HASH *queries = qc->get_queries(); + + /* one must have PROCESS privilege to see others' queries */ + if (check_global_access(thd, PROCESS_ACL, true)) + return 0; + +/* if (qc->query_cache_size==0 || thd->variables.query_cache_type==0) */ + if (qc->is_disabled()) + return 0; + + if (qc->try_lock(thd)) + return status; + /* loop through all queries in the query cache */ for (uint i= 0; i < queries->records; i++) { @@ -98,9 +229,11 @@ Query_cache_block *result_block; const char *statement_text; size_t statement_text_length; - const char *key, *db; + char *key, *db; size_t key_length, db_length; - + Query_cache_query_flags flags; + char str[MAX_STATEMENT_TEXT_LENGTH]; + query_cache_block_raw = my_hash_element(queries, i); query_cache_block = (Query_cache_block*)query_cache_block_raw; if (query_cache_block->type != Query_cache_block::QUERY) @@ -108,6 +241,34 @@ query_cache_query = query_cache_block->query(); + + /* rows */ + table->field[COLUMN_RESULT_FOUND_ROWS]->store(query_cache_query->found_rows(), 0); + table->field[COLUMN_RESULT_LENGTH]->store(query_cache_query->length(), 0); + table->field[COLUMN_RESULT_TABLES_TYPE]->store(query_cache_query->tables_type(), 0); /* what is this ? */ + table->field[COLUMN_ENTRY_POSITION_IN_CACHE]->store((long)i, 0); + table->field[COLUMN_QUERY_HITS]->store(query_cache_query->query_hits(), 0); + table->field[COLUMN_SELECT_EXPEND_TIME]->store(query_cache_query->query_expend_time(), 0); + table->field[COLUMN_SELECT_ROWS_READ]->store(query_cache_query->query_rows_read(), 0); + + memset(&str[0], 0, sizeof(str)); + for (TABLE_COUNTER_TYPE t= 0; t < query_cache_block->n_tables; t++) + { + Query_cache_table *tmp_table= query_cache_block->table(t)->parent; + strncat(str, "`", MAX_STATEMENT_TEXT_LENGTH); + strncat(str, tmp_table->db(), MAX_STATEMENT_TEXT_LENGTH); + strncat(str, "`.`", MAX_STATEMENT_TEXT_LENGTH); + strncat(str, tmp_table->table(), MAX_STATEMENT_TEXT_LENGTH); + strncat(str, "`", MAX_STATEMENT_TEXT_LENGTH); + if(t+1 < query_cache_block->n_tables){ + strncat(str, ", ", MAX_STATEMENT_TEXT_LENGTH); + } + } + table->field[COLUMN_TABLES]->store(str, + min(strlen(str), MAX_STATEMENT_TEXT_LENGTH), scs); + + + /* Key is query + database + flag */ /* Get the actual SQL statement for this query cache query */ statement_text = (const char*)query_cache_query->query(); statement_text_length = strlen(statement_text); @@ -116,8 +277,32 @@ min(statement_text_length, MAX_STATEMENT_TEXT_LENGTH), scs); /* get the entire key that identifies this query cache query */ - key = (const char*)query_cache_query_get_key(query_cache_block_raw, + key = (char*)query_cache_query_get_key(query_cache_block_raw, &key_length, 0); + + /* Flags */ + key_length-= QUERY_CACHE_FLAGS_SIZE; // Point at flags + memcpy( &flags, key+key_length, QUERY_CACHE_FLAGS_SIZE); + table->field[COLUMN_FLAGS_CLIENT_LONG_FLAG]->store(flags.client_long_flag, 0); + table->field[COLUMN_FLAGS_CLIENT_PROTOCOL_41]->store(flags.client_protocol_41, 0); + table->field[COLUMN_FLAGS_PROTOCOL_TYPE]->store(flags.protocol_type, 0); + table->field[COLUMN_FLAGS_MORE_RESULTS_EXISTS]->store(flags.more_results_exists, 0); + table->field[COLUMN_FLAGS_IN_TRANS]->store(flags.in_trans, 0); + table->field[COLUMN_FLAGS_AUTOCOMMIT]->store(flags.autocommit, 0); + table->field[COLUMN_FLAGS_PKT_NR]->store(flags.pkt_nr, 0); + table->field[COLUMN_FLAGS_CHARACTER_SET_CLIENT_NUM]->store(flags.character_set_client_num, 0); + table->field[COLUMN_FLAGS_CHARACTER_SET_RESULTS_NUM]->store(flags.character_set_results_num, 0); + table->field[COLUMN_FLAGS_COLLATION_CONNECTION_NUM]->store(flags.collation_connection_num, 0); + table->field[COLUMN_FLAGS_LIMIT]->store(flags.limit, 0); +/* table->field[COLUMN_FLAGS_TIME_ZONE]->store(flags.time_zone, 0);*/ + table->field[COLUMN_FLAGS_SQL_MODE]->store(flags.sql_mode, 0); + table->field[COLUMN_FLAGS_MAX_SORT_LENGTH]->store(flags.max_sort_length, 0); + table->field[COLUMN_FLAGS_GROUP_CONCAT_MAX_LEN]->store(flags.group_concat_max_len, 0); + table->field[COLUMN_FLAGS_DIV_PRECISION_INCREMENT]->store(flags.div_precision_increment, 0); + table->field[COLUMN_FLAGS_DEFAULT_WEEK_FORMAT]->store(flags.default_week_format, 0); +/* table->field[COLUMN_FLAGS_LC_TIME_NAMES]->store(flags.lc_time_names, 0);*/ + memcpy(key + key_length, &flags, QUERY_CACHE_FLAGS_SIZE); // restore flags + /* The database against which the statement is executed is part of the query cache query key */ @@ -154,6 +339,8 @@ result_blocks_size = 0; result_blocks_size_used = 0; } + + table->field[COLUMN_RESULT_BLOCKS_COUNT]->store(result_blocks_count, 0); table->field[COLUMN_RESULT_BLOCKS_SIZE]->store(result_blocks_size, 0); table->field[COLUMN_RESULT_BLOCKS_SIZE_USED]->store(result_blocks_size_used, 0); @@ -168,12 +355,26 @@ return status; } -static int qc_info_plugin_init(void *p) +static int qc_info_plugin_init_queries(void *p) { ST_SCHEMA_TABLE *schema= (ST_SCHEMA_TABLE *)p; + schema->fields_info= qc_info_fields_queries; + schema->fill_table= qc_info_fill_table_queries; - schema->fields_info= qc_info_fields; - schema->fill_table= qc_info_fill_table; +#ifdef _WIN32 + qc = (Accessible_Query_Cache *) + GetProcAddress(GetModuleHandle(NULL), "?query_cache@@3VQuery_cache@@A"); +#else + qc = (Accessible_Query_Cache *)&query_cache; +#endif + + return qc == 0; +} +static int qc_info_plugin_init_tables(void *p) +{ + ST_SCHEMA_TABLE *schema= (ST_SCHEMA_TABLE *)p; + schema->fields_info= qc_info_fields_tables; + schema->fill_table= qc_info_fill_table_tables; #ifdef _WIN32 qc = (Accessible_Query_Cache *) @@ -195,19 +396,34 @@ maria_declare_plugin(query_cache_info) { - MYSQL_INFORMATION_SCHEMA_PLUGIN, - &qc_info_plugin, - "QUERY_CACHE_INFO", - "Roland Bouman", - "Lists all queries in the query cache.", - PLUGIN_LICENSE_BSD, - qc_info_plugin_init, /* Plugin Init */ - 0, /* Plugin Deinit */ - 0x0100, /* version, hex */ - NULL, /* status variables */ - NULL, /* system variables */ - "1.0", /* version as a string */ - MariaDB_PLUGIN_MATURITY_ALPHA + MYSQL_INFORMATION_SCHEMA_PLUGIN, /* the plugin type (see include/mysql/plugin.h) */ + &qc_info_plugin, /* pointer to type-specific plugin descriptor */ + "QUERY_CACHE_QUERIES", /* plugin name */ + "Roland Bouman", /* plugin author */ + "Lists all queries in the query cache.", /* the plugin description */ + PLUGIN_LICENSE_BSD, /* the plugin license (see include/mysql/plugin.h) */ + qc_info_plugin_init_queries, /* Pointer to plugin initialization function */ + 0, /* Pointer to plugin deinitialization function */ + 0x0101, /* Numeric version 0xAABB means AA.BB veriosn */ + NULL, /* Status variables */ + NULL, /* System variables */ + "1.01", /* String version representation */ + MariaDB_PLUGIN_MATURITY_ALPHA /* Maturity (see include/mysql/plugin.h)*/ +}, +{ + MYSQL_INFORMATION_SCHEMA_PLUGIN, /* the plugin type (see include/mysql/plugin.h) */ + &qc_info_plugin, /* pointer to type-specific plugin descriptor */ + "QUERY_CACHE_TABLES", /* plugin name */ + "Roberto Spadim", /* plugin author */ + "Lists all table in the query cache.",/* the plugin description */ + PLUGIN_LICENSE_BSD, /* the plugin license (see include/mysql/plugin.h) */ + qc_info_plugin_init_tables, /* Pointer to plugin initialization function */ + 0, /* Pointer to plugin deinitialization function */ + 0x0100, /* Numeric version 0xAABB means AA.BB veriosn */ + NULL, /* Status variables */ + NULL, /* System variables */ + "1.0", /* String version representation */ + MariaDB_PLUGIN_MATURITY_ALPHA /* Maturity (see include/mysql/plugin.h)*/ } maria_declare_plugin_end;