# name : query_cache_with_comments.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 @@ -915,6 +915,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; --- /dev/null +++ b/sql/query_strip_comments.h @@ -0,0 +1,37 @@ +#ifndef _SQL_QUERY_STRIPC_COMMENTS_H_ +#define _SQL_QUERY_STRIPC_COMMENTS_H_ +#ifdef HAVE_QUERY_CACHE + +// implemented in sql_cache.cc +class QueryStripComments +{ +private: + QueryStripComments(const QueryStripComments&); + QueryStripComments& operator=(const QueryStripComments&); +public: + QueryStripComments(); + ~QueryStripComments(); + void set(const char* a_query, uint a_query_length, uint a_additional_length); + + char* query() { return buffer; } + uint query_length() { return length; } +private: + void cleanup(); +private: + char* buffer; + uint length /*query length, not buffer length*/; + uint buffer_length; +}; +class QueryStripComments_Backup +{ +public: + QueryStripComments_Backup(THD* a_thd,QueryStripComments* qsc); + ~QueryStripComments_Backup(); +private: + THD* thd; + char* query; + uint length; +}; + +#endif // HAVE_QUERY_CACHE +#endif // _SQL_QUERY_STRIPC_COMMENTS_H_ --- a/sql/sql_cache.cc +++ b/sql/sql_cache.cc @@ -344,6 +344,198 @@ #include "probes_mysql.h" #include "transaction.h" +#include "query_strip_comments.h" + +/* + Number of bytes to be allocated in a query cache buffer in addition to the + query string length. + + The query buffer layout is: + + buffer :== + The input statement(s) + '\0' Terminating null char + Length of following current database name (size_t) + Name of current database + Flags struct +*/ +#define QUERY_BUFFER_ADDITIONAL_LENGTH(db_length) \ + (1 + sizeof(size_t) + db_length + QUERY_CACHE_FLAGS_SIZE) + +QueryStripComments::QueryStripComments() +{ + buffer = 0; + length = 0; + buffer_length = 0; +} +QueryStripComments::~QueryStripComments() +{ + cleanup(); +} + +inline bool query_strip_comments_is_white_space(char c) +{ + return ((' ' == c) || ('\t' == c) || ('\r' == c) || ('\n' ==c )); +} +void QueryStripComments::set(const char* query, uint query_length, uint additional_length) +{ + uint new_buffer_length = query_length + additional_length; + if(new_buffer_length > buffer_length) + { + cleanup(); + buffer = (char*)my_malloc(new_buffer_length,MYF(0)); + } + uint query_position = 0; + uint position = 0; + // Skip whitespaces from begin + while((query_position < query_length) && query_strip_comments_is_white_space(query[query_position])) + { + ++query_position; + } + long int last_space = -1; + while(query_position < query_length) + { + char current = query[query_position]; + bool insert_space = false; // insert space to buffer, (IMPORTANT) don't update query_position + switch(current) + { + case '\'': + case '"': + { + buffer[position++] = query[query_position++]; // copy current symbol + while(query_position < query_length) + { + if(current == query[query_position]) // found pair quote + { + break; + } + buffer[position++] = query[query_position++]; // copy current symbol + } + break; + } + case '/': + { + if(((query_position + 2) < query_length) && ('*' == query[query_position+1]) && ('!' != query[query_position+2])) + { + query_position += 2; // skip "/*" + do + { + if('*' == query[query_position] && '/' == query[query_position+1]) // check for "*/" + { + query_position += 2; // skip "*/" + insert_space = true; + break; + } + else + { + ++query_position; + } + } + while(query_position < query_length); + if(!insert_space) + { + continue; + } + } + break; + } + case '-': + { + if(query[query_position+1] == '-') + { + ++query_position; // skip "-", and go to search of "\n" + } + else + { + break; + } + } + case '#': + { + do + { + ++query_position; // skip current symbol (# or -) + if('\n' == query[query_position]) // check for '\n' + { + ++query_position; // skip '\n' + insert_space = true; + break; + } + } + while(query_position < query_length); + if(insert_space) + { + break; + } + else + { + continue; + } + } + default: + if(query_strip_comments_is_white_space(current)) + { + insert_space = true; + ++query_position; + } + break; // make gcc happy + } + if(insert_space) + { + if((uint) (last_space + 1) != position) + { + last_space = position; + buffer[position++] = ' '; + } + } + else if (query_position < query_length) + { + buffer[position++] = query[query_position++]; + } + } + while((0 < position) && query_strip_comments_is_white_space(buffer[position - 1])) + { + --position; + } + buffer[position] = 0; + length = position; +} +void QueryStripComments::cleanup() +{ + if(buffer) + { + my_free(buffer); + } + buffer = 0; + length = 0; + buffer_length = 0; +} +QueryStripComments_Backup::QueryStripComments_Backup(THD* a_thd,QueryStripComments* qsc) +{ + if(opt_query_cache_strip_comments) + { + thd = a_thd; + query = thd->query(); + length = thd->query_length(); + qsc->set(query, length, QUERY_BUFFER_ADDITIONAL_LENGTH(thd->db_length)); + *(size_t *) (qsc->query() + qsc->query_length() + 1)= thd->db_length; + thd->set_query(qsc->query(),qsc->query_length()); + } + else + { + thd = 0; + query = 0; + length = 0; + } +} +QueryStripComments_Backup::~QueryStripComments_Backup() +{ + if(thd) + { + thd->set_query(query,length); + } +} + #ifdef EMBEDDED_LIBRARY #include "emb_qcache.h" #endif @@ -454,7 +646,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 +1471,8 @@ unlock(); DBUG_VOID_RETURN; } + QueryStripComments *query_strip_comments = &(thd->query_strip_comments); + QueryStripComments_Backup backup(thd,query_strip_comments); /* Key is query + database + flag */ if (thd->db_length) @@ -1451,6 +1650,9 @@ Query_cache_block_table *block_table, *block_table_end; ulong tot_length; Query_cache_query_flags flags; + QueryStripComments *query_strip_comments = &(thd->query_strip_comments); + char *sql_backup = sql; + uint query_length_backup = query_length; DBUG_ENTER("Query_cache::send_result_to_client"); /* @@ -1472,21 +1674,103 @@ { uint i= 0; - /* - Skip '(' characters in queries like following: - (select a from t1) union (select a from t1); - */ - while (sql[i]=='(') - i++; + if(opt_query_cache_strip_comments) + { + /* Skip all comments and non-letter symbols */ + uint& query_position = i; + char* query = sql; + while(query_position < query_length) + { + bool check = false; + char current = query[query_position]; + switch(current) + { + case '/': + if(((query_position + 2) < query_length) && ('*' == query[query_position+1]) && ('!' != query[query_position+2])) + { + query_position += 2; // skip "/*" + do + { + if('*' == query[query_position] && '/' == query[query_position+1]) // check for "*/" (without space) + { + query_position += 2; // skip "*/" (without space) + break; + } + else + { + ++query_position; + } + } + while(query_position < query_length); + continue; // analyze current symbol + } + break; + case '-': + if(query[query_position+1] == '-') + { + ++query_position; // skip "-" + } + else + { + break; + } + case '#': + do + { + ++query_position; // skip current symbol + if('\n' == query[query_position]) // check for '\n' + { + ++query_position; // skip '\n' + break; + } + } + while(query_position < query_length); + continue; // analyze current symbol + case '\r': + case '\n': + case '\t': + case ' ': + case '(': + case ')': + break; + default: + check = true; + break; // make gcc happy + } // switch(current) + if(check) + { + if(query_position + 2 < query_length) + { + // cacheable + break; + } + else + { + DBUG_PRINT("qcache", ("The statement is not a SELECT; Not cached")); + goto err; + } + } // if(check) + ++query_position; + } // while(query_position < query_length) + } + else // if(opt_query_cache_strip_comments) + { + /* + Skip '(' characters in queries like following: + (select a from t1) union (select a from t1); + */ + while (sql[i]=='(') + i++; - /* - Test if the query is a SELECT - (pre-space is removed in dispatch_command). + } // if(opt_query_cache_strip_comments) + /* + Test if the query is a SELECT + (pre-space is removed in dispatch_command). - First '/' looks like comment before command it is not - frequently appeared in real life, consequently we can - check all such queries, too. - */ + First '/' looks like comment before command it is not + 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') && @@ -1543,6 +1827,14 @@ goto err_unlock; Query_cache_block *query_block; + if(opt_query_cache_strip_comments) + { + query_strip_comments->set(sql, query_length, + QUERY_BUFFER_ADDITIONAL_LENGTH(thd->db_length)); + sql = query_strip_comments->query(); + query_length = query_strip_comments->query_length(); + *(size_t *) (sql + query_length + 1)= thd->db_length; + } tot_length= query_length + 1 + sizeof(size_t) + thd->db_length + QUERY_CACHE_FLAGS_SIZE; @@ -1611,6 +1903,8 @@ (uchar*) &flags, QUERY_CACHE_FLAGS_SIZE); query_block = (Query_cache_block *) my_hash_search(&queries, (uchar*) sql, tot_length); + sql = sql_backup; + query_length = query_length_backup; /* Quick abort on unlocked data */ if (query_block == 0 || query_block->query()->result() == 0 || --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -40,6 +40,9 @@ #include "thr_lock.h" /* thr_lock_type, THR_LOCK_DATA, THR_LOCK_INFO */ +#ifdef HAVE_QUERY_CACHE +#include "query_strip_comments.h" +#endif // HAVE_QUERY_CACHE class Reprepare_observer; class Relay_log_info; @@ -766,6 +769,9 @@ statement lifetime. FIXME: must be const */ ulong id; +#ifdef HAVE_QUERY_CACHE + QueryStripComments query_strip_comments; // see sql_cache.cc +#endif //HAVE_QUERY_CACHE /* MARK_COLUMNS_NONE: Means mark_used_colums is not set and no indicator to --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -1895,6 +1895,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,95 @@ +--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 ' \' ' 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,866 @@ +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 ' \' ' 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 ' \' ' from t1; +' + ' + ' + ' +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 51 +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,865 @@ +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 ' \' ' 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 ' \' ' from t1; +' + ' + ' + ' +select ' \' ' 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 +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 @@ -500,6 +500,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 = @@ -942,6 +946,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_bug856404.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_bug856404-master.opt @@ -0,0 +1 @@ +--query-cache-size=10M --query-cache-strip-comments --- /dev/null +++ b/mysql-test/t/percona_bug856404.test @@ -0,0 +1,15 @@ +######################################################################## +# Bug #856404: Crash when query_cache_strip_comments enabled +######################################################################## + +--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;