# name : query_cache_enhance.patch # introduced : 11 or before # maintainer : Oleg # #!!! notice !!! # Any small change to this file in the main branch # should be done or reviewed by the maintainer! --- /dev/null +++ b/patch_info/query_cache_enhance.patch @@ -0,0 +1,15 @@ +File=query_cache_enhance.patch +Name= query cache Percona's cumulative patch +Version=1.0 +Author=Percona +License=GPL +Comment= 1) Add new status - Waiting on query cache mutex (status_wait_query_cache_mutex.patch) + 2) Remove comments from query (need for cache hit) (query_cache_with_comments.patch) + 3) Totally disable query cache (query_cache_totally_disable.info) +2010-05 - First version avaliable (query_cache_with_comments.patch) +2010-07 - First version avaliable (status_wait_query_cache_mutex.patch +2010-07 - First version avaliable (query_cache_totally_disable.info) +2010-07 - Fix crash (query_cache_with_comments.patch) +2010-07 - Fix incorrect behavior diff (query_cache_with_comments.patch) +2010-09 - Merge patches to one +2010-11 - Ported to 5.5 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -904,6 +904,7 @@ #endif #ifdef HAVE_QUERY_CACHE ulong query_cache_min_res_unit= QUERY_CACHE_MIN_RESULT_DATA_SIZE; +my_bool opt_query_cache_strip_comments= FALSE; Query_cache query_cache; #endif #ifdef HAVE_SMEM --- a/sql/mysqld.h +++ b/sql/mysqld.h @@ -91,6 +91,7 @@ extern my_bool opt_log, opt_slow_log; extern my_bool opt_backup_history_log; extern my_bool opt_backup_progress_log; +extern my_bool opt_query_cache_strip_comments; extern ulonglong log_output_options; extern ulong log_backup_output_options; extern my_bool opt_log_queries_not_using_indexes; --- a/sql/sql_cache.cc +++ b/sql/sql_cache.cc @@ -344,6 +344,496 @@ #include "probes_mysql.h" #include "transaction.h" + +namespace query_comments_parser +{ + + +enum Kind +{ + /* 'Empty' symbol - epsilon in classic parsers */ + Empty, + /* + Special symbols: + * exclamation comment: slash-star-exclamation comment-body star-slash + * single-line and multi-line comments + */ + Special, + /* Whitespaces: ' ' \t \r \n */ + WhiteSpace, + /* + 1) C-style comment (slash-star comment-body star-slash) + 2) signle-line comment: + * sharp comment (sharp comment-body new-line) + * minus-minus comment (minus-minus comment-body new-line) + */ + Comment, + /* Not a special symbols (this symbols can't be before SELECT ). */ + Another, + /* Error: not-closed quotes, not-closed C-style comment, end-of-query */ + Error +}; + + +/** + Analyze kind of prefix of input string. + + @param where pointer to pointer to begin of string. After analyzing input + string function skip analyzed prefix and return pointer to the next part + of string in the @param where. + + @return kind of analyzed prefix. +*/ +static Kind analyze(const char **where, const char *const end) +{ + DBUG_ASSERT(where != NULL); + DBUG_ASSERT(*where != NULL); + const char*&to= *where; + /* if empty */ + if (*to == '\0') + { + return Empty; + } + + /* current symbol */ + char current= *to; + + switch (current) + { + case '\'': + case '"': + /* skip quote */ + to++; + /* search pair */ + while (true) + { + /* check for pair of quote */ + if (*to == current) + { + /* skip second quote */ + to++; + /* check for same symbol after second quote */ + if (to < end && *to == current) + { + /* same symbol, skip it */ + to++; + /* check for end-of-line */ + if (to == end) + { + /* not found - not closed quote */ + return Error; + } + else + { + /* continue search of pair */ + continue; + } + } + else + { + return Another; + } + } + /* check for escaped symbols */ + if (*to == '\\') + { + /* backslash, skip it */ + to++; + } + /* check for end-of-line */ + if (to == end) + { + /* not found - not closed quote */ + return Error; + } + /* skip current symbol */ + to++; + } + case '-': + /* Skip minus */ + to++; + /* Check for second minus */ + if (*to != '-') + { + /* Just minus */ + return Another; + } + else + { + /* + Prefix is minus-minus, next case-branch is processing + single line comments. + */ + } + case '#': + /* + This is single-line comment, it started by "#" or "--". + Skip first symbol. + */ + to++; + /* search new-line */ + to= strchr(to, '\n'); + if (NULL == to) + { + /* not found, end of the comment is the end of the query */ + to= end; + } + else + { + /* skip end-of-line */ + to++; + } + return Comment; + case '/': + /* skip slash */ + to++; + /* check for star */ + if (*to == '*') + { + /* skip star */ + to++; + /* check for exclamation */ + bool exclamation= (*to == '!'); + /* search star-slash */ + to= strstr(to, "*/"); + if (NULL == to) + { + /* not found - not closed comment */ + return Error; + } + else + { + /* found */ + DBUG_ASSERT(to + 1 < end); + DBUG_ASSERT(0 == strncmp(to, "*/", 2)); + /* skip star-slash */ + to++; + to++; + return (exclamation ? Special : Comment); + } + } + else + { + /* just slash */ + return Another; + } + case ' ': + case '\t': + case '\r': + case '\n': + { + /* skip space */ + to++; + return WhiteSpace; + } + case '\\': + { + /* skip backslash */ + to++; + if (to == end) + { + /* + query complete by backslash + probable error? + */ + return Another; + } + else + { + /* skip after backslash symbol */ + to++; + return Another; + } + } + case '(': + case ')': + { + /* skip parenthese */ + to++; + return Special; + } + default: + { + /* skip symbol */ + to++; + return Another; + } + }; +} + + +static bool remove_comments_from_query(const char *const query, + const size_t query_length, + char *const result, + size_t *result_length) +{ + /* pointer to begin of parsed block */ + const char *from= query; + const char *to= query; + /* pointer to end of the query */ + const char *const end= query + query_length; + /* pointer to last space */ + const char *space= NULL; + /* current position in result buffer */ + char *current= result; + while (true) + { + from= to; + switch (analyze(&to, end)) + { + case Empty: + { + /* + parse completed + check for whitespace in the end + */ + if (current == space) + { + /* drop whitespace in the end of query */ + --current; + } + /* result is null-terminated string */ + *current= 0; + /* set result length */ + *result_length= current - result; + /* all right */ + return true; + } + case Comment: + /* should just insert space instead of comment */ + case WhiteSpace: + if (space == current || from == query) + { + /* previous symbol was space */ + } + else + { + /* insert space to result buffer */ + *current= ' '; + /* switch after inserted space */ + current++; + } + /* remember last-after-space position */ + space= current; + /* parse again */ + continue; + case Special: + case Another: + { + /* calculate parsed block size */ + size_t block_size= to - from; + /* copy parsed block to result */ + memcpy(current, from, block_size); + /* switch result after copied block */ + current+= block_size; + /* switch after parsed block */ + from= to; + /* parse again */ + continue; + } + case Error: + default: + { + /* bad source query */ + return false; + } + } + } +} + + +static size_t skip_not_another(const char *const query, size_t query_length) +{ + const char *from= query; + const char *to= query; + const char *const end= query + query_length; + while (true) + { + switch (analyze(&to, end)) + { + case Error: + return 0; + case Empty: + case Another: + return (from - query); + default: + from= to; + continue; + }; + } +} + + +static size_t skip_default(const char *const query, size_t /* query_length */) +{ + size_t query_position= 0; + /* + Skip '(' characters in queries like following: + (select a from t1) union (select a from t1); + */ + while (query[query_position]=='(') + query_position++; + return query_position; +} + + +} /* namespace query_comments_parser */ + +class Query_Switcher +{ +private: + Query_Switcher(const Query_Switcher&); + Query_Switcher& operator=(const Query_Switcher&); + + +public: + Query_Switcher(THD *thd) : + target_query(&(thd_query_string(thd)->str)), + target_length(&(thd_query_string(thd)->length)), + backup_query(thd->query()), + backup_length(thd->query_length()) + { + } + + Query_Switcher(char **query, + size_t *length) : + target_query(query), + target_length(length), + backup_query(*query), + backup_length(*length) + { + } +public: + void replace(Query_Without_Comments *query_without_comments) + { + *target_query= query_without_comments->query(); + *target_length= query_without_comments->length(); + } + void restore() + { + *target_query= backup_query; + *target_length= backup_length; + } +private: + char* *target_query; + size_t *target_length; +public: + char *const backup_query; + size_t const backup_length; +}; + +class Comments_Processor +{ +private: + Comments_Processor(const Comments_Processor&); + Comments_Processor& operator=(const Comments_Processor&); + + +public: + Comments_Processor(THD *thd) : + query_switcher (thd), + db_length (thd->db_length), + query_without_comments(&(thd->query_without_comments)), + enabled (opt_query_cache_strip_comments), + restore (false) + { + } + + + Comments_Processor(Query_Without_Comments *current_query_without_comments, + char **query, + size_t *length, + const size_t current_db_length) : + query_switcher (query, length), + db_length (current_db_length), + query_without_comments(current_query_without_comments), + enabled (opt_query_cache_strip_comments), + restore (false) + { + } + + + ~Comments_Processor() + { + restore_comments(); + } + + + size_t prefix_length() + { + using query_comments_parser::skip_not_another; + using query_comments_parser::skip_default; + if (enabled) + { + return skip_not_another(query_switcher.backup_query, + query_switcher.backup_length); + } + else + { + return skip_default(query_switcher.backup_query, + query_switcher.backup_length); + } + } + + + bool remove_comments() + { + if (!enabled || restore) + { + return true; + } + /* Allocate memory for query rewrite */ + if (!query_without_comments->allocate(query_switcher.backup_length, + db_length)) + { + return false; + } + /* Remove comment from query */ + size_t result_length; + using query_comments_parser::remove_comments_from_query; + if (!(restore= remove_comments_from_query(query_switcher.backup_query, + query_switcher.backup_length, + query_without_comments->query(), + &result_length))) + { + return false; + } + query_without_comments->set_length(result_length); + size_t db_length_from_query= + *((size_t*)(query_switcher.backup_query + + query_switcher.backup_length + 1)); + *((size_t*)(query_without_comments->query() + + result_length + 1))= db_length_from_query; + /* Replace original query by striped */ + query_switcher.replace(query_without_comments); + return restore; + } + + + void restore_comments() + { + if (enabled && restore) + { + /* Replace striped query by original */ + query_switcher.restore(); + + /* Clean query_without_comments */ + query_without_comments->set_length(0); + + /* Mark as restored */ + restore= false; + } + } +private: + Query_Switcher query_switcher; +private: + const size_t db_length; +private: + Query_Without_Comments *query_without_comments; + bool enabled; + bool restore; +}; + #ifdef EMBEDDED_LIBRARY #include "emb_qcache.h" #endif @@ -454,7 +939,12 @@ Query_cache_wait_state wait_state(thd, __func__, __FILE__, __LINE__); DBUG_ENTER("Query_cache::try_lock"); + const char *old_proc_info= thd->proc_info; + thd_proc_info(thd,"Waiting on query cache mutex"); + DEBUG_SYNC(thd, "before_query_cache_mutex"); mysql_mutex_lock(&structure_guard_mutex); + DEBUG_SYNC(thd, "after_query_cache_mutex"); + thd->proc_info = old_proc_info; while (1) { if (m_cache_lock_status == Query_cache::UNLOCKED) @@ -1274,6 +1764,8 @@ unlock(); DBUG_VOID_RETURN; } + Comments_Processor comments_processor(thd); + comments_processor.remove_comments(); /* Key is query + database + flag */ if (thd->db_length) @@ -1440,7 +1932,7 @@ */ int -Query_cache::send_result_to_client(THD *thd, char *sql, uint query_length) +Query_cache::send_result_to_client(THD *thd, char *sql, uint query_length_uint) { ulonglong engine_data; Query_cache_query *query; @@ -1452,6 +1944,11 @@ ulong tot_length; Query_cache_query_flags flags; DBUG_ENTER("Query_cache::send_result_to_client"); + size_t query_length= query_length_uint; + Comments_Processor comments_processor(&(thd->query_without_comments), + &sql, + &query_length, + thd->db_length); /* Testing 'query_cache_size' without a lock here is safe: the thing @@ -1471,13 +1968,7 @@ } { - uint i= 0; - /* - Skip '(' characters in queries like following: - (select a from t1) union (select a from t1); - */ - while (sql[i]=='(') - i++; + size_t i= comments_processor.prefix_length(); /* Test if the query is a SELECT @@ -1487,10 +1978,11 @@ frequently appeared in real life, consequently we can check all such queries, too. */ - if ((my_toupper(system_charset_info, sql[i]) != 'S' || - my_toupper(system_charset_info, sql[i + 1]) != 'E' || - my_toupper(system_charset_info, sql[i + 2]) != 'L') && - sql[i] != '/') + if (!((i + 2 < query_length) && + ((my_toupper(system_charset_info, sql[i]) == 'S' && + my_toupper(system_charset_info, sql[i + 1]) == 'E' && + my_toupper(system_charset_info, sql[i + 2]) == 'L') || + sql[i] == '/'))) { DBUG_PRINT("qcache", ("The statement is not a SELECT; Not cached")); goto err; @@ -1543,6 +2035,7 @@ goto err_unlock; Query_cache_block *query_block; + comments_processor.remove_comments(); tot_length= query_length + 1 + sizeof(size_t) + thd->db_length + QUERY_CACHE_FLAGS_SIZE; @@ -1611,6 +2104,7 @@ (uchar*) &flags, QUERY_CACHE_FLAGS_SIZE); query_block = (Query_cache_block *) my_hash_search(&queries, (uchar*) sql, tot_length); + comments_processor.restore_comments(); /* Quick abort on unlocked data */ if (query_block == 0 || query_block->query()->result() == 0 || --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -1487,6 +1487,74 @@ extern "C" void my_message_sql(uint error, const char *str, myf MyFlags); + +#ifdef HAVE_QUERY_CACHE + + +/* + @class Query_Without_Comments + This class provides way for safety (re)allocation + a memory for a query without comments. +*/ +class Query_Without_Comments +{ +private: + /* + Denied copy and assigment for object of this class. + */ + Query_Without_Comments(const Query_Without_Comments&); + Query_Without_Comments& operator=(const Query_Without_Comments&); + + +public: + /* + Constructor is filling fields by zero (no allocation). + */ + Query_Without_Comments(); + + + /* + Destructor clean allocated memory + */ + ~Query_Without_Comments(); +public: + + +/* + (Re)allocate memory for query. Query length after that is 0. + */ + bool allocate(size_t query_length, size_t db_length); + + + /* + Set result query length, when query + without comments is copied to buffer. + */ + void set_length(size_t query_length); + + +public: + /* + Result query. + */ + char* query(); + + + /* + Result query length + */ + size_t length(); + + +private: + char* buffer; + size_t q_length; + size_t b_length; +}; + + +#endif /* HAVE_QUERY_CACHE */ + /** @class THD For each client connection we create a separate thread with THD serving as @@ -1544,6 +1612,7 @@ struct st_mysql_stmt *current_stmt; #endif #ifdef HAVE_QUERY_CACHE + Query_Without_Comments query_without_comments; Query_cache_tls query_cache_tls; #endif NET net; // client connection descriptor --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -1809,6 +1809,11 @@ NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(0), ON_UPDATE(fix_query_cache_size)); +static Sys_var_mybool Sys_query_cache_strip_comments( + "query_cache_strip_comments", "Enable and disable optimisation \"strip comment for query cache\" - optimisation strip all comments from query while search query result in query cache", + GLOBAL_VAR(opt_query_cache_strip_comments), CMD_LINE(OPT_ARG), + DEFAULT(FALSE)); + static Sys_var_ulong Sys_query_cache_limit( "query_cache_limit", "Don't cache results that are bigger than this", --- /dev/null +++ b/mysql-test/include/percona_query_cache_with_comments.inc @@ -0,0 +1,117 @@ +--source include/percona_query_cache_with_comments_clear.inc +let $query=/* with comment first */select * from t1; +eval $query; +--source include/percona_query_cache_with_comments_eval.inc + +let $query=# with comment first +select * from t1; +--source include/percona_query_cache_with_comments_eval.inc + +let $query=-- with comment first +select * from t1; +--source include/percona_query_cache_with_comments_eval.inc + +let $query=/* with comment first and "quote" */select * from t1; +--source include/percona_query_cache_with_comments_eval.inc + +let $query=# with comment first and "quote" +select * from t1; +--source include/percona_query_cache_with_comments_eval.inc + +let $query=-- with comment first and "quote" +select * from t1; +--source include/percona_query_cache_with_comments_eval.inc + +let $query= + /* with comment and whitespaces first */select * from t1; +--source include/percona_query_cache_with_comments_eval.inc + +let $query= + # with comment and whitespaces first +select * from t1; +--source include/percona_query_cache_with_comments_eval.inc + +let $query= + -- with comment and whitespaces first +select * from t1; +--source include/percona_query_cache_with_comments_eval.inc + +let $internal=* internal comment *; + +let $query=select * /$internal/ from t1; +--source include/percona_query_cache_with_comments_eval.inc +let $query=select */$internal/ from t1; +--source include/percona_query_cache_with_comments_eval.inc +let $query=select */$internal/from t1; +--source include/percona_query_cache_with_comments_eval.inc + +let $internal=* internal comment with "quote" *; + +let $query=select * /$internal/ from t1; +--source include/percona_query_cache_with_comments_eval.inc +let $query=select */$internal/ from t1; +--source include/percona_query_cache_with_comments_eval.inc +let $query=select */$internal/from t1; +--source include/percona_query_cache_with_comments_eval.inc + +let $query=select * from t1 +; +--source include/percona_query_cache_with_comments_eval.inc + +let $query=select * from t1 ; +--source include/percona_query_cache_with_comments_eval.inc + +let $query=select * from t1 ; +--source include/percona_query_cache_with_comments_eval.inc + +let $query=select * from t1 +/* comment in the end */; +--source include/percona_query_cache_with_comments_eval.inc + +let $query=select * from t1 +/* *\/ */; +--source include/percona_query_cache_with_comments_eval.inc + +let $query=select * from t1 +/* comment in the end */ +; +--source include/percona_query_cache_with_comments_eval.inc + +let $query=select * from t1 #comment in the end; +--source include/percona_query_cache_with_comments_eval.inc + +let $query=select * from t1 #comment in the end +; +--source include/percona_query_cache_with_comments_eval.inc + +let $query=select * from t1 -- comment in the end; +--source include/percona_query_cache_with_comments_eval.inc + +let $query=select * from t1 -- comment in the end +; +--source include/percona_query_cache_with_comments_eval.inc + +let $query=select */* a comment \*/from t1; +--source include/percona_query_cache_with_comments_eval.inc + +let $query=select *# a comment \\ +from t1; +--source include/percona_query_cache_with_comments_eval.inc + +let $query=select *-- a comment \\ +from t1; +--source include/percona_query_cache_with_comments_eval.inc + +let $query=select "\\\\"" /* not a comment */" from t1; +--source include/percona_query_cache_with_comments_eval.inc + +let $query=select "\\\\"" /*! not a comment */" from t1; +--source include/percona_query_cache_with_comments_eval.inc + +# following two queries related to bug #856404. +# There are different queries, but opt_query_cache_strip_comments thinks that they are equal. +let $query=select ' \' ' from t1; +--source include/percona_query_cache_with_comments_eval.inc + +let $query=select ' \' /* comment inside quotes with internal backslash quote */' from t1; +--source include/percona_query_cache_with_comments_eval.inc --- /dev/null +++ b/mysql-test/include/percona_query_cache_with_comments_begin.inc @@ -0,0 +1,12 @@ +-- source include/have_query_cache.inc + +set GLOBAL query_cache_size=1355776; + +--disable_warnings +drop table if exists t1; +--enable_warnings + +create table t1 (a int not null); +insert into t1 values (1),(2),(3); + +--source include/percona_query_cache_with_comments_clear.inc --- /dev/null +++ b/mysql-test/include/percona_query_cache_with_comments_clear.inc @@ -0,0 +1,5 @@ +# Reset query cache variables. +flush query cache; # This crashed in some versions +flush query cache; # This crashed in some versions +reset query cache; +flush status; --- /dev/null +++ b/mysql-test/include/percona_query_cache_with_comments_end.inc @@ -0,0 +1,3 @@ +DROP TABLE t1; +SET GLOBAL query_cache_size=default; +set global query_cache_strip_comments=OFF; --- /dev/null +++ b/mysql-test/include/percona_query_cache_with_comments_eval.inc @@ -0,0 +1,7 @@ +echo -----------------------------------------------------; +echo $query; +echo -----------------------------------------------------; +--source include/percona_query_cache_with_comments_show.inc +eval $query; +eval $query; +--source include/percona_query_cache_with_comments_show.inc --- /dev/null +++ b/mysql-test/include/percona_query_cache_with_comments_show.inc @@ -0,0 +1,8 @@ +let $show=show status like "Qcache_queries_in_cache"; +eval $show; +let $show=show status like "Qcache_inserts"; +eval $show; +let $show=show status like "Qcache_hits"; +eval $show; + + --- /dev/null +++ b/mysql-test/r/percona_query_cache_with_comments.result @@ -0,0 +1,1058 @@ +set global query_cache_strip_comments=ON; +set GLOBAL query_cache_size=1355776; +drop table if exists t1; +create table t1 (a int not null); +insert into t1 values (1),(2),(3); +flush query cache; +flush query cache; +reset query cache; +flush status; +flush query cache; +flush query cache; +reset query cache; +flush status; +/* with comment first */select * from t1; +a +1 +2 +3 +----------------------------------------------------- +/* with comment first */select * from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 0 +/* with comment first */select * from t1; +a +1 +2 +3 +/* with comment first */select * from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 2 +----------------------------------------------------- +# with comment first +select * from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 2 +# with comment first +select * from t1; +a +1 +2 +3 +# with comment first +select * from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 4 +----------------------------------------------------- +-- with comment first +select * from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 4 +-- with comment first +select * from t1; +a +1 +2 +3 +-- with comment first +select * from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 6 +----------------------------------------------------- +/* with comment first and "quote" */select * from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 6 +/* with comment first and "quote" */select * from t1; +a +1 +2 +3 +/* with comment first and "quote" */select * from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 8 +----------------------------------------------------- +# with comment first and "quote" +select * from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 8 +# with comment first and "quote" +select * from t1; +a +1 +2 +3 +# with comment first and "quote" +select * from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 10 +----------------------------------------------------- +-- with comment first and "quote" +select * from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 10 +-- with comment first and "quote" +select * from t1; +a +1 +2 +3 +-- with comment first and "quote" +select * from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 12 +----------------------------------------------------- +/* with comment and whitespaces first */select * from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 12 +/* with comment and whitespaces first */select * from t1; +a +1 +2 +3 +/* with comment and whitespaces first */select * from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 14 +----------------------------------------------------- +# with comment and whitespaces first +select * from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 14 +# with comment and whitespaces first +select * from t1; +a +1 +2 +3 +# with comment and whitespaces first +select * from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 16 +----------------------------------------------------- +-- with comment and whitespaces first +select * from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 16 +-- with comment and whitespaces first +select * from t1; +a +1 +2 +3 +-- with comment and whitespaces first +select * from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 18 +----------------------------------------------------- +select * /* internal comment */ from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 18 +select * /* internal comment */ from t1; +a +1 +2 +3 +select * /* internal comment */ from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 20 +----------------------------------------------------- +select */* internal comment */ from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 20 +select */* internal comment */ from t1; +a +1 +2 +3 +select */* internal comment */ from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 22 +----------------------------------------------------- +select */* internal comment */from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 22 +select */* internal comment */from t1; +a +1 +2 +3 +select */* internal comment */from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 24 +----------------------------------------------------- +select * /* internal comment with "quote" */ from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 24 +select * /* internal comment with "quote" */ from t1; +a +1 +2 +3 +select * /* internal comment with "quote" */ from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 26 +----------------------------------------------------- +select */* internal comment with "quote" */ from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 26 +select */* internal comment with "quote" */ from t1; +a +1 +2 +3 +select */* internal comment with "quote" */ from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 28 +----------------------------------------------------- +select */* internal comment with "quote" */from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 28 +select */* internal comment with "quote" */from t1; +a +1 +2 +3 +select */* internal comment with "quote" */from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 30 +----------------------------------------------------- +select * from t1 + +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 30 +select * from t1 +; +a +1 +2 +3 +select * from t1 +; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 32 +----------------------------------------------------- +select * from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 32 +select * from t1 ; +a +1 +2 +3 +select * from t1 ; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 34 +----------------------------------------------------- +select * from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 34 +select * from t1 ; +a +1 +2 +3 +select * from t1 ; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 36 +----------------------------------------------------- +select * from t1 +/* comment in the end */ +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 36 +select * from t1 +/* comment in the end */; +a +1 +2 +3 +select * from t1 +/* comment in the end */; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 38 +----------------------------------------------------- +select * from t1 +/* *\/ */ +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 38 +select * from t1 +/* *\/ */; +a +1 +2 +3 +select * from t1 +/* *\/ */; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 40 +----------------------------------------------------- +select * from t1 +/* comment in the end */ + +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 40 +select * from t1 +/* comment in the end */ +; +a +1 +2 +3 +select * from t1 +/* comment in the end */ +; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 42 +----------------------------------------------------- +select * from t1 #comment in the end +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 42 +select * from t1 #comment in the end; +a +1 +2 +3 +select * from t1 #comment in the end; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 44 +----------------------------------------------------- +select * from t1 #comment in the end + +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 44 +select * from t1 #comment in the end +; +a +1 +2 +3 +select * from t1 #comment in the end +; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 46 +----------------------------------------------------- +select * from t1 -- comment in the end +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 46 +select * from t1 -- comment in the end; +a +1 +2 +3 +select * from t1 -- comment in the end; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 48 +----------------------------------------------------- +select * from t1 -- comment in the end + +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 48 +select * from t1 -- comment in the end +; +a +1 +2 +3 +select * from t1 -- comment in the end +; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 50 +----------------------------------------------------- +select */* a comment \*/from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 50 +select */* a comment \*/from t1; +a +1 +2 +3 +select */* a comment \*/from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 52 +----------------------------------------------------- +select *# a comment \ +from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 52 +select *# a comment \ +from t1; +a +1 +2 +3 +select *# a comment \ +from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 54 +----------------------------------------------------- +select *-- a comment \ +from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 54 +select *-- a comment \ +from t1; +a +1 +2 +3 +select *-- a comment \ +from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 56 +----------------------------------------------------- +select "\\"" /* not a comment */" from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 56 +select "\\"" /* not a comment */" from t1; +\" /* not a comment */ +\" /* not a comment */ +\" /* not a comment */ +\" /* not a comment */ +select "\\"" /* not a comment */" from t1; +\" /* not a comment */ +\" /* not a comment */ +\" /* not a comment */ +\" /* not a comment */ +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 2 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 2 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 57 +----------------------------------------------------- +select "\\"" /*! not a comment */" from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 2 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 2 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 57 +select "\\"" /*! not a comment */" from t1; +\" /*! not a comment */ +\" /*! not a comment */ +\" /*! not a comment */ +\" /*! not a comment */ +select "\\"" /*! not a comment */" from t1; +\" /*! not a comment */ +\" /*! not a comment */ +\" /*! not a comment */ +\" /*! not a comment */ +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 3 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 3 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 58 +----------------------------------------------------- +select ' \' ' from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 3 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 3 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 58 +select ' \' ' from t1; +' + ' + ' + ' +select ' \' ' from t1; +' + ' + ' + ' +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 4 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 4 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 59 +----------------------------------------------------- +select ' \' /* comment inside quotes with internal backslash quote */' from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 4 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 4 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 59 +select ' \' /* comment inside quotes with internal backslash quote */' from t1; +' /* comment inside quotes with internal backslash quote */ + ' /* comment inside quotes with internal backslash quote */ + ' /* comment inside quotes with internal backslash quote */ + ' /* comment inside quotes with internal backslash quote */ +select ' \' /* comment inside quotes with internal backslash quote */' from t1; +' /* comment inside quotes with internal backslash quote */ + ' /* comment inside quotes with internal backslash quote */ + ' /* comment inside quotes with internal backslash quote */ + ' /* comment inside quotes with internal backslash quote */ +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 5 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 5 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 60 +DROP TABLE t1; +SET GLOBAL query_cache_size=default; +set global query_cache_strip_comments=OFF; --- /dev/null +++ b/mysql-test/r/percona_query_cache_with_comments_crash.result @@ -0,0 +1,21 @@ +set GLOBAL query_cache_size=1355776; +drop table if exists t1; +create table t1 (a int not null); +insert into t1 values (1),(2),(3); +flush query cache; +flush query cache; +reset query cache; +flush status; +( select * from t1 ); +a +1 +2 +3 +/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */; +/* only comment */; +# only comment +; +-- only comment +; +DROP TABLE t1; +SET GLOBAL query_cache_size= default; --- /dev/null +++ b/mysql-test/r/percona_query_cache_with_comments_disable.result @@ -0,0 +1,1057 @@ +set GLOBAL query_cache_size=1355776; +drop table if exists t1; +create table t1 (a int not null); +insert into t1 values (1),(2),(3); +flush query cache; +flush query cache; +reset query cache; +flush status; +flush query cache; +flush query cache; +reset query cache; +flush status; +/* with comment first */select * from t1; +a +1 +2 +3 +----------------------------------------------------- +/* with comment first */select * from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 0 +/* with comment first */select * from t1; +a +1 +2 +3 +/* with comment first */select * from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 2 +----------------------------------------------------- +# with comment first +select * from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 2 +# with comment first +select * from t1; +a +1 +2 +3 +# with comment first +select * from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 2 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 2 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 2 +----------------------------------------------------- +-- with comment first +select * from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 2 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 2 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 2 +-- with comment first +select * from t1; +a +1 +2 +3 +-- with comment first +select * from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 3 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 3 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 2 +----------------------------------------------------- +/* with comment first and "quote" */select * from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 3 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 3 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 2 +/* with comment first and "quote" */select * from t1; +a +1 +2 +3 +/* with comment first and "quote" */select * from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 4 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 4 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 3 +----------------------------------------------------- +# with comment first and "quote" +select * from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 4 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 4 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 3 +# with comment first and "quote" +select * from t1; +a +1 +2 +3 +# with comment first and "quote" +select * from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 5 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 5 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 3 +----------------------------------------------------- +-- with comment first and "quote" +select * from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 5 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 5 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 3 +-- with comment first and "quote" +select * from t1; +a +1 +2 +3 +-- with comment first and "quote" +select * from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 6 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 6 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 3 +----------------------------------------------------- +/* with comment and whitespaces first */select * from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 6 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 6 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 3 +/* with comment and whitespaces first */select * from t1; +a +1 +2 +3 +/* with comment and whitespaces first */select * from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 7 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 7 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 4 +----------------------------------------------------- +# with comment and whitespaces first +select * from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 7 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 7 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 4 +# with comment and whitespaces first +select * from t1; +a +1 +2 +3 +# with comment and whitespaces first +select * from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 8 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 8 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 4 +----------------------------------------------------- +-- with comment and whitespaces first +select * from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 8 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 8 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 4 +-- with comment and whitespaces first +select * from t1; +a +1 +2 +3 +-- with comment and whitespaces first +select * from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 9 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 9 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 4 +----------------------------------------------------- +select * /* internal comment */ from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 9 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 9 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 4 +select * /* internal comment */ from t1; +a +1 +2 +3 +select * /* internal comment */ from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 10 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 10 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 5 +----------------------------------------------------- +select */* internal comment */ from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 10 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 10 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 5 +select */* internal comment */ from t1; +a +1 +2 +3 +select */* internal comment */ from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 11 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 11 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 6 +----------------------------------------------------- +select */* internal comment */from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 11 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 11 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 6 +select */* internal comment */from t1; +a +1 +2 +3 +select */* internal comment */from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 12 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 12 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 7 +----------------------------------------------------- +select * /* internal comment with "quote" */ from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 12 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 12 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 7 +select * /* internal comment with "quote" */ from t1; +a +1 +2 +3 +select * /* internal comment with "quote" */ from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 13 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 13 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 8 +----------------------------------------------------- +select */* internal comment with "quote" */ from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 13 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 13 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 8 +select */* internal comment with "quote" */ from t1; +a +1 +2 +3 +select */* internal comment with "quote" */ from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 14 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 14 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 9 +----------------------------------------------------- +select */* internal comment with "quote" */from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 14 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 14 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 9 +select */* internal comment with "quote" */from t1; +a +1 +2 +3 +select */* internal comment with "quote" */from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 15 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 15 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 10 +----------------------------------------------------- +select * from t1 + +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 15 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 15 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 10 +select * from t1 +; +a +1 +2 +3 +select * from t1 +; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 16 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 16 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 11 +----------------------------------------------------- +select * from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 16 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 16 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 11 +select * from t1 ; +a +1 +2 +3 +select * from t1 ; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 16 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 16 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 13 +----------------------------------------------------- +select * from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 16 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 16 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 13 +select * from t1 ; +a +1 +2 +3 +select * from t1 ; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 16 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 16 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 15 +----------------------------------------------------- +select * from t1 +/* comment in the end */ +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 16 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 16 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 15 +select * from t1 +/* comment in the end */; +a +1 +2 +3 +select * from t1 +/* comment in the end */; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 17 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 17 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 16 +----------------------------------------------------- +select * from t1 +/* *\/ */ +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 17 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 17 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 16 +select * from t1 +/* *\/ */; +a +1 +2 +3 +select * from t1 +/* *\/ */; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 18 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 18 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 17 +----------------------------------------------------- +select * from t1 +/* comment in the end */ + +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 18 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 18 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 17 +select * from t1 +/* comment in the end */ +; +a +1 +2 +3 +select * from t1 +/* comment in the end */ +; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 18 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 18 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 19 +----------------------------------------------------- +select * from t1 #comment in the end +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 18 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 18 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 19 +select * from t1 #comment in the end; +a +1 +2 +3 +select * from t1 #comment in the end; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 19 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 19 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 20 +----------------------------------------------------- +select * from t1 #comment in the end + +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 19 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 19 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 20 +select * from t1 #comment in the end +; +a +1 +2 +3 +select * from t1 #comment in the end +; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 19 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 19 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 22 +----------------------------------------------------- +select * from t1 -- comment in the end +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 19 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 19 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 22 +select * from t1 -- comment in the end; +a +1 +2 +3 +select * from t1 -- comment in the end; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 20 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 20 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 23 +----------------------------------------------------- +select * from t1 -- comment in the end + +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 20 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 20 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 23 +select * from t1 -- comment in the end +; +a +1 +2 +3 +select * from t1 -- comment in the end +; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 20 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 20 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 25 +----------------------------------------------------- +select */* a comment \*/from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 20 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 20 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 25 +select */* a comment \*/from t1; +a +1 +2 +3 +select */* a comment \*/from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 21 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 21 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 26 +----------------------------------------------------- +select *# a comment \ +from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 21 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 21 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 26 +select *# a comment \ +from t1; +a +1 +2 +3 +select *# a comment \ +from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 22 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 22 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 27 +----------------------------------------------------- +select *-- a comment \ +from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 22 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 22 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 27 +select *-- a comment \ +from t1; +a +1 +2 +3 +select *-- a comment \ +from t1; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 23 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 23 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 28 +----------------------------------------------------- +select "\\"" /* not a comment */" from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 23 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 23 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 28 +select "\\"" /* not a comment */" from t1; +\" /* not a comment */ +\" /* not a comment */ +\" /* not a comment */ +\" /* not a comment */ +select "\\"" /* not a comment */" from t1; +\" /* not a comment */ +\" /* not a comment */ +\" /* not a comment */ +\" /* not a comment */ +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 24 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 24 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 29 +----------------------------------------------------- +select "\\"" /*! not a comment */" from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 24 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 24 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 29 +select "\\"" /*! not a comment */" from t1; +\" /*! not a comment */ +\" /*! not a comment */ +\" /*! not a comment */ +\" /*! not a comment */ +select "\\"" /*! not a comment */" from t1; +\" /*! not a comment */ +\" /*! not a comment */ +\" /*! not a comment */ +\" /*! not a comment */ +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 25 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 25 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 30 +----------------------------------------------------- +select ' \' ' from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 25 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 25 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 30 +select ' \' ' from t1; +' + ' + ' + ' +select ' \' ' from t1; +' + ' + ' + ' +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 26 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 26 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 31 +----------------------------------------------------- +select ' \' /* comment inside quotes with internal backslash quote */' from t1 +----------------------------------------------------- +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 26 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 26 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 31 +select ' \' /* comment inside quotes with internal backslash quote */' from t1; +' /* comment inside quotes with internal backslash quote */ + ' /* comment inside quotes with internal backslash quote */ + ' /* comment inside quotes with internal backslash quote */ + ' /* comment inside quotes with internal backslash quote */ +select ' \' /* comment inside quotes with internal backslash quote */' from t1; +' /* comment inside quotes with internal backslash quote */ + ' /* comment inside quotes with internal backslash quote */ + ' /* comment inside quotes with internal backslash quote */ + ' /* comment inside quotes with internal backslash quote */ +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 27 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 27 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 32 +DROP TABLE t1; +SET GLOBAL query_cache_size=default; +set global query_cache_strip_comments=OFF; --- /dev/null +++ b/mysql-test/r/percona_query_cache_with_comments_prepared_statements.result @@ -0,0 +1,396 @@ +set GLOBAL query_cache_size=1355776; +flush query cache; +flush query cache; +reset query cache; +flush status; +drop table if exists t1; +create table t1 (a int not null); +insert into t1 values (1),(2),(3); +set global query_cache_strip_comments=ON; +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 0 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 0 +prepare stmt from '/* with comment */ select * from t1'; +execute stmt; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 0 +execute stmt; +a +1 +2 +3 +execute stmt; +a +1 +2 +3 +execute stmt; +a +1 +2 +3 +execute stmt; +a +1 +2 +3 +execute stmt; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 5 +prepare stmt from 'select * from t1'; +execute stmt; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 6 +prepare stmt from 'select * /*internal comment*/from t1'; +execute stmt; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 7 +prepare stmt from 'select * /*internal comment*/ from t1'; +execute stmt; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 8 +prepare stmt from 'select * from t1 /* at the end */'; +execute stmt; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 9 +prepare stmt from 'select * from t1 /* with "quote" */'; +execute stmt; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 10 +prepare stmt from 'select * from t1 /* with \'quote\' */'; +execute stmt; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 11 +prepare stmt from 'select * from t1 # 123 +'; +execute stmt; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 12 +prepare stmt from 'select * from t1 # 123 with "quote" +'; +execute stmt; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 13 +prepare stmt from 'select * from t1 # 123 with \'quote\' +'; +execute stmt; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 14 +prepare stmt from 'select * from t1 +# 123 +'; +execute stmt; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 15 +prepare stmt from '#456 +select * from t1 +# 123 +'; +execute stmt; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 16 +prepare stmt from 'select * from t1 -- 123 +'; +execute stmt; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 17 +prepare stmt from 'select * from t1 +-- 123 +'; +execute stmt; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 18 +prepare stmt from '-- comment in first +select * from t1 +# 123 +'; +execute stmt; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 19 +prepare stmt from '(#456( +select * from t1 +# 123( +)'; +execute stmt; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 2 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 2 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 19 +prepare stmt from '/*test*/(-- comment in first( +select * from t1 +-- 123 asdasd +/* test */)'; +execute stmt; +a +1 +2 +3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 2 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 2 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 20 +prepare stmt from 'select "test",a from t1'; +execute stmt; +test a +test 1 +test 2 +test 3 +execute stmt; +test a +test 1 +test 2 +test 3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 3 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 3 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 21 +prepare stmt from 'select "test /* internal \'comment\' */",a from t1'; +execute stmt; +test /* internal 'comment' */ a +test /* internal 'comment' */ 1 +test /* internal 'comment' */ 2 +test /* internal 'comment' */ 3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 4 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 4 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 21 +prepare stmt from 'select "test #internal comment" ,a from t1'; +execute stmt; +test #internal comment a +test #internal comment 1 +test #internal comment 2 +test #internal comment 3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 5 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 5 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 21 +prepare stmt from 'select "test #internal comment" #external comment +,a from t1'; +execute stmt; +test #internal comment a +test #internal comment 1 +test #internal comment 2 +test #internal comment 3 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 5 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 5 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 22 +DROP TABLE t1; +SET GLOBAL query_cache_size= default; +set global query_cache_strip_comments=OFF; --- /dev/null +++ b/mysql-test/t/percona_query_cache_with_comments.test @@ -0,0 +1,5 @@ +--disable_ps_protocol +set global query_cache_strip_comments=ON; +-- source include/percona_query_cache_with_comments_begin.inc +-- source include/percona_query_cache_with_comments.inc +-- source include/percona_query_cache_with_comments_end.inc --- /dev/null +++ b/mysql-test/t/percona_query_cache_with_comments_crash.test @@ -0,0 +1,22 @@ +-- source include/have_query_cache.inc +set GLOBAL query_cache_size=1355776; +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (a int not null); +insert into t1 values (1),(2),(3); +flush query cache; # This crashed in some versions +flush query cache; # This crashed in some versions +reset query cache; +flush status; +( select * from t1 ); +/*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */; +/* only comment */; +let query=# only comment +; +eval $query; +let query=-- only comment +; +eval $query; +DROP TABLE t1; +SET GLOBAL query_cache_size= default; --- /dev/null +++ b/mysql-test/t/percona_query_cache_with_comments_disable.test @@ -0,0 +1,3 @@ +-- source include/percona_query_cache_with_comments_begin.inc +-- source include/percona_query_cache_with_comments.inc +-- source include/percona_query_cache_with_comments_end.inc --- /dev/null +++ b/mysql-test/t/percona_query_cache_with_comments_prepared_statements.test @@ -0,0 +1,208 @@ +-- source include/have_query_cache.inc + +set GLOBAL query_cache_size=1355776; + +# Reset query cache variables. +flush query cache; # This crashed in some versions +flush query cache; # This crashed in some versions +reset query cache; +flush status; +--disable_warnings +drop table if exists t1; +--enable_warnings + +# +# First simple test +# + +create table t1 (a int not null); +insert into t1 values (1),(2),(3); + +set global query_cache_strip_comments=ON; + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; + +prepare stmt from '/* with comment */ select * from t1'; +execute stmt; + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; + +execute stmt; +execute stmt; +execute stmt; +execute stmt; +execute stmt; + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; + +prepare stmt from 'select * from t1'; +execute stmt; + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; + +prepare stmt from 'select * /*internal comment*/from t1'; +execute stmt; + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; + +prepare stmt from 'select * /*internal comment*/ from t1'; +execute stmt; + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; + +prepare stmt from 'select * from t1 /* at the end */'; +execute stmt; + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; + +prepare stmt from 'select * from t1 /* with "quote" */'; +execute stmt; + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; + +prepare stmt from 'select * from t1 /* with \'quote\' */'; +execute stmt; + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; + +prepare stmt from 'select * from t1 # 123 +'; +execute stmt; + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; + +prepare stmt from 'select * from t1 # 123 with "quote" +'; +execute stmt; + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; + +prepare stmt from 'select * from t1 # 123 with \'quote\' +'; +execute stmt; + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; + +prepare stmt from 'select * from t1 +# 123 +'; +execute stmt; + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; + +prepare stmt from '#456 +select * from t1 +# 123 +'; +execute stmt; + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; + +prepare stmt from 'select * from t1 -- 123 +'; +execute stmt; + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; + +prepare stmt from 'select * from t1 +-- 123 +'; +execute stmt; + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; + +prepare stmt from '-- comment in first +select * from t1 +# 123 +'; +execute stmt; + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; + +prepare stmt from '(#456( +select * from t1 +# 123( +)'; +execute stmt; + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; + +prepare stmt from '/*test*/(-- comment in first( +select * from t1 +-- 123 asdasd +/* test */)'; +execute stmt; + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; + +prepare stmt from 'select "test",a from t1'; +execute stmt; +execute stmt; + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; + +prepare stmt from 'select "test /* internal \'comment\' */",a from t1'; +execute stmt; + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; + +prepare stmt from 'select "test #internal comment" ,a from t1'; +execute stmt; + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; + +prepare stmt from 'select "test #internal comment" #external comment +,a from t1'; +execute stmt; + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; + +DROP TABLE t1; +SET GLOBAL query_cache_size= default; +set global query_cache_strip_comments=OFF; --- /dev/null +++ b/mysql-test/t/percona_status_wait_query_cache_mutex.test @@ -0,0 +1,35 @@ +--source include/have_query_cache.inc +--source include/have_debug.inc +--source include/have_debug_sync.inc +SET GLOBAL query_cache_size=1355776; +--source include/percona_query_cache_with_comments_clear.inc +--let try_lock_mutex_query=SELECT "try_lock_mutex_query" as action + +--connect (mutex_locked_conn, localhost, root,,) +--connect (try_mutex_lock_conn, localhost, root,,) + +--connection mutex_locked_conn +SET DEBUG_SYNC='after_query_cache_mutex SIGNAL mutex_locked WAIT_FOR unlock_mutex'; +send SELECT "mutex_locked_query" as action; + +--connection default +SET DEBUG_SYNC='now WAIT_FOR mutex_locked'; + +--connection try_mutex_lock_conn +SET DEBUG_SYNC='before_query_cache_mutex SIGNAL try_lock_mutex'; +send_eval $try_lock_mutex_query; + +--connection default +SET DEBUG_SYNC='now WAIT_FOR try_lock_mutex'; +eval SELECT SQL_NO_CACHE state FROM INFORMATION_SCHEMA.PROCESSLIST WHERE info='$try_lock_mutex_query'; +SET DEBUG_SYNC='now SIGNAL unlock_mutex'; + +--connection mutex_locked_conn +reap; +--connection try_mutex_lock_conn +reap; + +--connection default +--disconnect mutex_locked_conn +--disconnect try_mutex_lock_conn +SET GLOBAL query_cache_size=0; --- a/mysql-test/r/mysqld--help-notwin.result +++ b/mysql-test/r/mysqld--help-notwin.result @@ -491,6 +491,10 @@ The minimum size for blocks allocated by the query cache --query-cache-size=# The memory allocated to store results from old queries + --query-cache-strip-comments + Enable and disable optimisation "strip comment for query + cache" - optimisation strip all comments from query while + search query result in query cache --query-cache-type=name OFF = Don't cache or retrieve results. ON = Cache all results except SELECT SQL_NO_CACHE ... queries. DEMAND = @@ -928,6 +932,7 @@ query-cache-limit 1048576 query-cache-min-res-unit 4096 query-cache-size 0 +query-cache-strip-comments FALSE query-cache-type ON query-cache-wlock-invalidate FALSE query-prealloc-size 8192 --- /dev/null +++ b/mysql-test/r/percona_status_wait_query_cache_mutex.result @@ -0,0 +1,20 @@ +SET GLOBAL query_cache_size=1355776; +flush query cache; +flush query cache; +reset query cache; +flush status; +SET DEBUG_SYNC='after_query_cache_mutex SIGNAL mutex_locked WAIT_FOR unlock_mutex'; +SELECT "mutex_locked_query" as action; +SET DEBUG_SYNC='now WAIT_FOR mutex_locked'; +SET DEBUG_SYNC='before_query_cache_mutex SIGNAL try_lock_mutex'; +SELECT "try_lock_mutex_query" as action; +SET DEBUG_SYNC='now WAIT_FOR try_lock_mutex'; +SELECT SQL_NO_CACHE state FROM INFORMATION_SCHEMA.PROCESSLIST WHERE info='SELECT "try_lock_mutex_query" as action'; +state +Waiting on query cache mutex +SET DEBUG_SYNC='now SIGNAL unlock_mutex'; +action +mutex_locked_query +action +try_lock_mutex_query +SET GLOBAL query_cache_size=0; --- /dev/null +++ b/mysql-test/r/percona_query_cache_with_comments_crash_2.result @@ -0,0 +1,8 @@ +DROP TABLE IF EXISTS table17_int; +DROP TABLE IF EXISTS table30_int; +CREATE TABLE `table17_int` (pk integer auto_increment primary key, `col_char_10_not_null_key` char(10), `col_enum_not_null_key` int); +CREATE TABLE `table30_int` (pk integer auto_increment primary key, `col_enum_not_null_key` int); +SELECT X . `pk` FROM `table17_int` AS X LEFT JOIN `table30_int` AS Y USING ( `col_enum_not_null_key` ) WHERE X . `col_char_10_not_null_key` != ' you need to translate Views labels into other languages, consider installing the Internationalization package\'s Views translation module.' LIMIT 7 /*Generated by THREAD_ID 1*/; +pk +DROP TABLE table17_int; +DROP TABLE table30_int; --- /dev/null +++ b/mysql-test/t/percona_query_cache_with_comments_crash_2-master.opt @@ -0,0 +1 @@ +--query-cache-size=10M --query-cache-strip-comments --- /dev/null +++ b/mysql-test/t/percona_query_cache_with_comments_crash_2.test @@ -0,0 +1,9 @@ +--disable_warnings +DROP TABLE IF EXISTS table17_int; +DROP TABLE IF EXISTS table30_int; +--enable_warnings +CREATE TABLE `table17_int` (pk integer auto_increment primary key, `col_char_10_not_null_key` char(10), `col_enum_not_null_key` int); +CREATE TABLE `table30_int` (pk integer auto_increment primary key, `col_enum_not_null_key` int); +SELECT X . `pk` FROM `table17_int` AS X LEFT JOIN `table30_int` AS Y USING ( `col_enum_not_null_key` ) WHERE X . `col_char_10_not_null_key` != ' you need to translate Views labels into other languages, consider installing the Internationalization package\'s Views translation module.' LIMIT 7 /*Generated by THREAD_ID 1*/; +DROP TABLE table17_int; +DROP TABLE table30_int; --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -807,6 +807,99 @@ sql_errno == ER_TRG_NO_DEFINER); } +#ifdef HAVE_QUERY_CACHE + + +Query_Without_Comments::Query_Without_Comments() : + buffer(0), + q_length(0), + b_length(0) +{ +} + + +Query_Without_Comments::~Query_Without_Comments() +{ + if(buffer) + { + my_free(buffer); + } +} + + +bool Query_Without_Comments::allocate(size_t query_length, size_t db_length) +{ + DBUG_ENTER("Query_Without_Comments::allocate"); + DBUG_PRINT("info", ("old buffer: %p " + "old query: '%-.4096s' " + "old buffer length: %u " + "old query length: %u", + buffer, + buffer, + (uint) b_length, + (uint) q_length)); + /* save maximum query length for check in the set_length */ + q_length= query_length; + /* according to sql_parse.cc memory allocation */ + size_t new_b_length= (query_length + 1) + sizeof(size_t) + db_length + + QUERY_CACHE_FLAGS_SIZE; + if (b_length < new_b_length) + { + b_length= new_b_length; + if (buffer) + { + buffer= (char*) my_realloc(buffer, b_length, MYF(0)); + } + else + { + buffer= (char *) my_malloc(b_length, MYF(0)); + } + } + buffer[0]= 0; + DBUG_PRINT("info", ("buffer: %p " + "buffer length: %u " + "query maximum length: %u", + buffer, + (uint) b_length, + (uint) q_length)); + DBUG_RETURN(buffer); +} + + +void Query_Without_Comments::set_length(size_t query_length) +{ + DBUG_ENTER("Query_Without_Comments::set_length"); + DBUG_ASSERT(query_length <= q_length); + buffer[query_length]= 0; + DBUG_PRINT("info", ("buffer: %p " + "query: '%-.4096s' " + "buffer length: %u " + "query maximum length: %u " + "query length: %u", + buffer, + buffer, + (uint) b_length, + (uint) q_length, + (uint) query_length)); + q_length= query_length; + DBUG_VOID_RETURN; +} + + +char* Query_Without_Comments::query() +{ + return buffer; +} + + +size_t Query_Without_Comments::length() +{ + return q_length; +} + + +#endif // HAVE_QUERY_CACHE + THD::THD() :Statement(&main_lex, &main_mem_root, STMT_CONVENTIONAL_EXECUTION,