List:Internals« Previous MessageNext Message »
From:Eric Date:February 17 2009 5:47pm
Subject:comments and query cache
View as plain text  
Hi All-

AFAICT, query comments are used as part of the hash key for query cache (at
least in 5.0).  Is this intentional?  Still the case in newer versions of
mysql?

We have a large mysql installation with many queries coming from a number of
different applications and sources (unfortunately sometimes as the same
mysql user) and we use comments to help us identify owners.  However, that
makes query caching not very useful for us (we often get the same query but
from different IPs which are in the comment).

I have a patch (attached) to strip comments before checking and storing the
query into the query cache but wanted to see if this was a reasonable way to
do it.  Also, I couldn't find a very reasonable function to strip comments.
(The one in innobase/hash/hash0hash.c seemed the best, but I imagine it is
not fit to include so I copied it just to get my proof-of-concept working
until/if something better is decided.)

If this seems useful and reasonable, is there a better way to get the
comments stripped?  Should I implement my own?  (Should be able to be done
in the existing char* without allocating any new memory, right?)  Does
InnoDB (what we use) or any other engine depend on the comments in any way
(I know oracle puts query hints in there)?  How should this behavior be
enabled since I imagine not everyone will want the runtime v. cache hit
tradeoff.  ./configure option?  my.cnf?  a dynamic variable?

Thanks,
Eric

Attachment: [text/html]
--- sql_cache.cc 2009-02-17 08:48:28.712523000 -0800 +++ sql_cache.cc.new 2009-02-17 08:47:42.765188000 -0800 @@ -823,6 +823,126 @@ } +// //esquid2, 12 feb 2009: +// +// copied from innobase/dict/dict0dict.c and modified slightly +// to fix dependencies and support removing leading space after +// an inline comment. +// +// could also rewrite to more-efficiently strip space inline in +// a single buffer. +/************************************************************************* +Removes MySQL comments from an SQL string. A comment is either +(a) '#' to the end of the line, +(b) '--<space>' to the end of the line, or +(c) '<slash><asterisk>' till the next '<asterisk><slash>' (like the familiar +C comment syntax). */ +static +char* +dict_strip_comments( +/*================*/ + /* out, own: SQL string stripped from + comments; the caller must free this + with mem_free()! */ + const char* sql_string) /* in: SQL string */ +{ + DBUG_ENTER("entering dict_strip_comments"); + char* str; + const char* sptr; + char* ptr; + /* unclosed quote character (0 if none) */ + char quote = 0; + + // esquid2 -- updated this from mem_alloc to make it compile clean + str = (char*) malloc(strlen(sql_string) + 1); + + sptr = sql_string; + ptr = str; + + for (;;) { +scan_more: + if (*sptr == '\0') { + *ptr = '\0'; + + // esquid2 -- updated to not use ut_a() + if (ptr > str + strlen(sql_string)) { + DBUG_PRINT("qcache", + ("ERROR IN dict_strip_comments")); + exit(27); + } + + return(str); + } + + + if (*sptr == quote) { + /* Closing quote character: do not look for + starting quote or comments. */ + quote = 0; + } else if (quote) { + /* Within quotes: do not look for + starting quotes or comments. */ + } else if (*sptr == '"' || *sptr == '`') { + /* Starting quote: remember the quote character. */ + quote = *sptr; + } else if (*sptr == '#' + || (sptr[0] == '-' && sptr[1] == '-' && + sptr[2] == ' ')) { + for (;;) { + /* In Unix a newline is 0x0A while in Windows + it is 0x0D followed by 0x0A */ + if (*sptr == (char)0x0A + || *sptr == (char)0x0D + || *sptr == '\0') { + + goto scan_more; + } + + sptr++; + } + } else if (!quote && *sptr == '/' && *(sptr + 1) == '*') { + for (;;) { + if (*sptr == '*' && *(sptr + 1) == '/') { + + sptr += 2; + // strip whitespace after inline comment + for (;;) { + if (*sptr == ' ' || + *sptr == '\n' || + *sptr == '\t') { + sptr++; + } else { + break; + } + } + + goto scan_more; + } + + if (*sptr == '\0') { + + goto scan_more; + } + sptr++; + } + } + + + + *ptr = *sptr; + + ptr++; + sptr++; + } +} + +//---------------------------------------------------------------- +// end copied function from dict0dict.c +//---------------------------------------------------------------- + + + + void Query_cache::store_query(THD *thd, TABLE_LIST *tables_used) { TABLE_COUNTER_TYPE local_tables; @@ -904,7 +1024,11 @@ DBUG_VOID_RETURN; } - /* Key is query + database + flag */ + /* Key is stripped query + database + flag */ + char* tmp = dict_strip_comments(thd->query); + strcpy(thd->query, tmp); + free(tmp); + thd->query_length = strlen(thd->query); if (thd->db_length) { memcpy(thd->query+thd->query_length+1, thd->db, thd->db_length); @@ -1042,6 +1166,11 @@ { uint i= 0; + char *tmp = dict_strip_comments(sql); + strcpy(sql, tmp); + free(tmp); + query_length = strlen(sql); + /* Skip '(' characters in queries like following: (select a from t1) union (select a from t1);
Thread
comments and query cacheEric17 Feb
  • Re: comments and query cacheAntony T Curtis18 Feb
  • re: comments and query cacheMichael Widenius18 Feb
    • RE: comments and query cacheRick James18 Feb
      • Re: comments and query cacheJocelyn Fournier19 Feb