1 /* 2 Copyright (c) 2008, Roland Bouman 3 http://rpbouman.blogspot.com/ 4 roland.bouman@gmail.com 5 All rights reserved. 6 7 Redistribution and use in source and binary forms, with or without 8 modification, are permitted provided that the following conditions are met: 9 * Redistributions of source code must retain the above copyright 10 notice, this list of conditions and the following disclaimer. 11 * Redistributions in binary form must reproduce the above copyright 12 notice, this list of conditions and the following disclaimer in the 13 documentation and/or other materials provided with the distribution. 14 * Neither the name of the Roland Bouman nor the 15 names of the contributors may be used to endorse or promote products 16 derived from this software without specific prior written permission. 17 18 THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND 19 ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED 20 WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE 21 DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> BE LIABLE FOR ANY 22 DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES 23 (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; 24 LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND 25 ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT 26 (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS 27 SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. 28 */ 29 30 31 #ifndef MYSQL_SERVER 32 #define MYSQL_SERVER 33 #endif 34 35 #include <my_global.h> 36 #include <sql_parse.h> // check_global_access 37 #include <sql_acl.h> // PROCESS_ACL 38 #include <sql_class.h> // THD 39 #include <sql_cache.h> 40 #include <table.h> // ST_SCHEMA_TABLE 41 #include <set_var.h> // sql_mode_string_representation 42 #include <tztime.h> 43 #include <mysql/plugin.h> 44 45 class Accessible_Query_Cache : public Query_cache { 46 public: 47 HASH *get_queries() 48 { 49 return &this->queries; 50 } 51 } *qc; 52 53 bool schema_table_store_record(THD *thd, TABLE *table); 54 55 #define MAX_STATEMENT_TEXT_LENGTH 32767 56 #define COLUMN_STATEMENT_SCHEMA 0 57 #define COLUMN_STATEMENT_TEXT 1 58 #define COLUMN_RESULT_BLOCKS_COUNT 2 59 #define COLUMN_RESULT_BLOCKS_SIZE 3 60 #define COLUMN_RESULT_BLOCKS_SIZE_USED 4 61 #define COLUMN_LIMIT 5 62 #define COLUMN_MAX_SORT_LENGTH 6 63 #define COLUMN_GROUP_CONCAT_MAX_LENGTH 7 64 #define COLUMN_CHARACTER_SET_CLIENT 8 65 #define COLUMN_CHARACTER_SET_RESULT 9 66 #define COLUMN_COLLATION 10 67 #define COLUMN_TIMEZONE 11 68 #define COLUMN_DEFAULT_WEEK_FORMAT 12 69 #define COLUMN_DIV_PRECISION_INCREMENT 13 70 #define COLUMN_SQL_MODE 14 71 #define COLUMN_LC_TIME_NAMES 15 72 73 #define COLUMN_CLIENT_LONG_FLAG 16 74 #define COLUMN_CLIENT_PROTOCOL_41 17 75 #define COLUMN_PROTOCOL_TYPE 18 76 #define COLUMN_MORE_RESULTS_EXISTS 19 77 #define COLUMN_IN_TRANS 20 78 #define COLUMN_AUTOCOMMIT 21 79 #define COLUMN_PKT_NR 22 80 #define COLUMN_HITS 23 81 82 /* ST_FIELD_INFO is defined in table.h */ 83 static ST_FIELD_INFO qc_info_fields[]= 84 { 85 {"STATEMENT_SCHEMA", NAME_LEN, MYSQL_TYPE_STRING, 0, 0, 0, 0}, 86 {"STATEMENT_TEXT", MAX_STATEMENT_TEXT_LENGTH, MYSQL_TYPE_STRING, 0, 0, 0, 0}, 87 {"RESULT_BLOCKS_COUNT", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, 0, 0}, 88 {"RESULT_BLOCKS_SIZE", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, 0, 0, 0}, 89 {"RESULT_BLOCKS_SIZE_USED", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, 0, 0, 0}, 90 {"LIMIT", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, 0, 0, 0}, 91 {"MAX_SORT_LENGTH", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, 0, 0, 0}, 92 {"GROUP_CONCAT_MAX_LENGTH", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, 0, 0, 0}, 93 {"CHARACTER_SET_CLIENT", MY_CS_NAME_SIZE, MYSQL_TYPE_STRING, 0, 0, 0, 0}, 94 {"CHARACTER_SET_RESULT", MY_CS_NAME_SIZE, MYSQL_TYPE_STRING, 0, 0, 0, 0}, 95 {"COLLATION", MY_CS_NAME_SIZE, MYSQL_TYPE_STRING, 0, 0, 0, 0}, 96 {"TIMEZONE", 50, MYSQL_TYPE_STRING, 0, 0, 0, 0}, 97 {"DEFAULT_WEEK_FORMAT", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, 0, 0}, 98 {"DIV_PRECISION_INCREMENT", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, 0, 0}, 99 {"SQL_MODE", 250, MYSQL_TYPE_STRING, 0, 0, 0, 0}, 100 {"LC_TIME_NAMES", 100, MYSQL_TYPE_STRING, 0, 0, 0, 0}, 101 {"CLIENT_LONG_FLAG", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_TINY, 0, 0, 0, 0}, 102 {"CLIENT_PROTOCOL_41", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_TINY, 0, 0, 0, 0}, 103 {"PROTOCOL_TYPE", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_TINY, 0, 0, 0, 0}, 104 {"MORE_RESULTS_EXISTS", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_TINY, 0, 0, 0, 0}, 105 {"IN_TRANS", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_TINY, 0, 0, 0, 0}, 106 {"AUTOCOMMIT", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_TINY, 0, 0, 0, 0}, 107 {"PACKET_NUMBER", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_TINY, 0, 0, 0, 0}, 108 {"HITS", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, MY_I_S_UNSIGNED, 0, 0}, 109 {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, 0} 110 }; 111 112 113 static const char unknown[]= "#UNKNOWN#"; 114 115 static int qc_info_fill_table(THD *thd, TABLE_LIST *tables, 116 COND *cond) 117 { 118 int status= 1; 119 CHARSET_INFO *scs= system_charset_info; 120 TABLE *table= tables->table; 121 HASH *queries = qc->get_queries(); 122 123 /* one must have PROCESS privilege to see others' queries */ 124 if (check_global_access(thd, PROCESS_ACL, true)) 125 return 0; 126 127 if (qc->try_lock(thd)) 128 return 0; // QC is or is being disabled 129 130 /* loop through all queries in the query cache */ 131 for (uint i= 0; i < queries->records; i++) 132 { 133 const uchar *query_cache_block_raw; 134 Query_cache_block* query_cache_block; 135 Query_cache_query* query_cache_query; 136 Query_cache_query_flags flags; 137 uint result_blocks_count; 138 ulonglong result_blocks_size; 139 ulonglong result_blocks_size_used; 140 Query_cache_block *first_result_block; 141 Query_cache_block *result_block; 142 const char *statement_text; 143 size_t statement_text_length; 144 size_t flags_length; 145 const char *key, *db; 146 size_t key_length, db_length; 147 LEX_CSTRING sql_mode_str; 148 const String *tz; 149 CHARSET_INFO *cs_client; 150 CHARSET_INFO *cs_result; 151 CHARSET_INFO *collation; 152 153 query_cache_block_raw = my_hash_element(queries, i); 154 query_cache_block = (Query_cache_block*)query_cache_block_raw; 155 if (unlikely(!query_cache_block || 156 query_cache_block->type != Query_cache_block::QUERY)) 157 continue; 158 159 query_cache_query = query_cache_block->query(); 160 161 /* Get the actual SQL statement for this query cache query */ 162 statement_text = (const char*)query_cache_query->query(); 163 statement_text_length = strlen(statement_text); 164 /* We truncate SQL statements up to MAX_STATEMENT_TEXT_LENGTH in our I_S table */ 165 table->field[COLUMN_STATEMENT_TEXT]->store((char*)statement_text, 166 MY_MIN(statement_text_length, MAX_STATEMENT_TEXT_LENGTH), scs); 167 168 /* get the entire key that identifies this query cache query */ 169 key = (const char*)query_cache_query_get_key(query_cache_block_raw, 170 &key_length, 0); 171 /* get and store the flags */ 172 flags_length= key_length - QUERY_CACHE_FLAGS_SIZE; 173 memcpy(&flags, key+flags_length, QUERY_CACHE_FLAGS_SIZE); 174 table->field[COLUMN_LIMIT]->store(flags.limit, 0); 175 table->field[COLUMN_MAX_SORT_LENGTH]->store(flags.max_sort_length, 0); 176 table->field[COLUMN_GROUP_CONCAT_MAX_LENGTH]->store(flags.group_concat_max_len, 0); 177 178 cs_client= get_charset(flags.character_set_client_num, MYF(MY_WME)); 179 if (likely(cs_client)) 180 table->field[COLUMN_CHARACTER_SET_CLIENT]-> 181 store(cs_client->csname, strlen(cs_client->csname), scs); 182 else 183 table->field[COLUMN_CHARACTER_SET_CLIENT]-> 184 store(STRING_WITH_LEN(unknown), scs); 185 186 cs_result= get_charset(flags.character_set_results_num, MYF(MY_WME)); 187 if (likely(cs_result)) 188 table->field[COLUMN_CHARACTER_SET_RESULT]-> 189 store(cs_result->csname, strlen(cs_result->csname), scs); 190 else 191 table->field[COLUMN_CHARACTER_SET_RESULT]-> 192 store(STRING_WITH_LEN(unknown), scs); 193 194 collation= get_charset(flags.collation_connection_num, MYF(MY_WME)); 195 if (likely(collation)) 196 table->field[COLUMN_COLLATION]-> 197 store(collation->name, strlen(collation->name), scs); 198 else 199 table->field[COLUMN_COLLATION]-> store(STRING_WITH_LEN(unknown), scs); 200 201 tz= flags.time_zone->get_name(); 202 if (likely(tz)) 203 table->field[COLUMN_TIMEZONE]->store(tz->ptr(), tz->length(), scs); 204 else 205 table->field[COLUMN_TIMEZONE]-> store(STRING_WITH_LEN(unknown), scs); 206 table->field[COLUMN_DEFAULT_WEEK_FORMAT]->store(flags.default_week_format, 0); 207 table->field[COLUMN_DIV_PRECISION_INCREMENT]->store(flags.div_precision_increment, 0); 208 209 sql_mode_string_representation(thd, flags.sql_mode, &sql_mode_str); 210 table->field[COLUMN_SQL_MODE]->store(sql_mode_str.str, sql_mode_str.length, scs); 211 212 table->field[COLUMN_LC_TIME_NAMES]->store(flags.lc_time_names->name,strlen(flags.lc_time_names->name), scs); 213 214 table->field[COLUMN_CLIENT_LONG_FLAG]->store(flags.client_long_flag, 0); 215 table->field[COLUMN_CLIENT_PROTOCOL_41]->store(flags.client_protocol_41, 0); 216 table->field[COLUMN_PROTOCOL_TYPE]->store(flags.protocol_type, 0); 217 table->field[COLUMN_MORE_RESULTS_EXISTS]->store(flags.more_results_exists, 0); 218 table->field[COLUMN_IN_TRANS]->store(flags.in_trans, 0); 219 table->field[COLUMN_AUTOCOMMIT]->store(flags.autocommit, 0); 220 table->field[COLUMN_PKT_NR]->store(flags.pkt_nr, 0); 221 table->field[COLUMN_HITS]->store(query_cache_query->hits(), 0); 222 223 /* The database against which the statement is executed is part of the 224 query cache query key 225 */ 226 compile_time_assert(QUERY_CACHE_DB_LENGTH_SIZE == 2); 227 db= key + statement_text_length + 1 + QUERY_CACHE_DB_LENGTH_SIZE; 228 db_length= uint2korr(db - QUERY_CACHE_DB_LENGTH_SIZE); 229 230 table->field[COLUMN_STATEMENT_SCHEMA]->store(db, db_length, scs); 231 232 /* If we have result blocks, process them */ 233 first_result_block= query_cache_query->result(); 234 if(query_cache_query->is_results_ready() && 235 first_result_block) 236 { 237 /* initialize so we can loop over the result blocks*/ 238 result_block= first_result_block; 239 result_blocks_count = 1; 240 result_blocks_size = result_block->length; 241 result_blocks_size_used = result_block->used; 242 243 /* loop over the result blocks*/ 244 while((result_block= result_block->next)!=first_result_block) 245 { 246 /* calculate total number of result blocks */ 247 result_blocks_count++; 248 /* calculate total size of result blocks */ 249 result_blocks_size += result_block->length; 250 /* calculate total of used size of result blocks */ 251 result_blocks_size_used += result_block->used; 252 } 253 } 254 else 255 { 256 result_blocks_count = 0; 257 result_blocks_size = 0; 258 result_blocks_size_used = 0; 259 } 260 table->field[COLUMN_RESULT_BLOCKS_COUNT]->store(result_blocks_count, 0); 261 table->field[COLUMN_RESULT_BLOCKS_SIZE]->store(result_blocks_size, 0); 262 table->field[COLUMN_RESULT_BLOCKS_SIZE_USED]-> 263 store(result_blocks_size_used, 0); 264 265 if (schema_table_store_record(thd, table)) 266 goto cleanup; 267 } 268 status = 0; 269 270 cleanup: 271 qc->unlock(); 272 return status; 273 } 274 275 static int qc_info_plugin_init(void *p) 276 { 277 ST_SCHEMA_TABLE *schema= (ST_SCHEMA_TABLE *)p; 278 279 schema->fields_info= qc_info_fields; 280 schema->fill_table= qc_info_fill_table; 281 282 #ifdef _WIN32 283 qc = (Accessible_Query_Cache *) 284 GetProcAddress(GetModuleHandle(NULL), "?query_cache@@3VQuery_cache@@A"); 285 #else 286 qc = (Accessible_Query_Cache *)&query_cache; 287 #endif 288 289 return qc == 0; 290 } 291 292 293 static struct st_mysql_information_schema qc_info_plugin= 294 { MYSQL_INFORMATION_SCHEMA_INTERFACE_VERSION }; 295 296 /* 297 Plugin library descriptor 298 */ 299 300 maria_declare_plugin(query_cache_info) 301 { 302 MYSQL_INFORMATION_SCHEMA_PLUGIN, 303 &qc_info_plugin, 304 "QUERY_CACHE_INFO", 305 "Roland Bouman, Daniel Black", 306 "Lists all queries in the query cache.", 307 PLUGIN_LICENSE_BSD, 308 qc_info_plugin_init, /* Plugin Init */ 309 0, /* Plugin Deinit */ 310 0x0101, /* version, hex */ 311 NULL, /* status variables */ 312 NULL, /* system variables */ 313 "1.1", /* version as a string */ 314 MariaDB_PLUGIN_MATURITY_STABLE 315 } 316 maria_declare_plugin_end; 317 318