List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:November 18 2010 7:31am
Subject:Does mysql cache strip out /* comments */ first?
View as plain text  
Like most developers, I have a wrapper that all of my SQL queries go
through in PHP.

We have a dedicated "NOC" screen that shows the "mytop" status of each
DEV/TEST/PROD master/slave pair.
http://daevid.com/content/examples/snippets.php (Automatic Monitoring of
remote servers)

We sometimes see stuck queries and are always hesitant to "kill" them off
because we never know WHO is executing that SQL. Is it a customer? Is it a
developer? Is it the boss? Is it rogue from some script gone awry? Mytop
doesn't give the full query due to screen real-estate amongst other
reasons. The downside is they bog down the server until they eventually
time-out or complete.

Anyways, today I implemented a simple, transparent and effective step
towards this puzzle.

I prefix ALL SQL (since it goes through my "sql_query()" function) with 

	/* ${SCRIPTNAME} */ 

Now all sql in the mytop shows up as:

	/* foo.php */  SELECT * FROM foo WHERE id = 1;
	/* bar.php */  UPDATE bar SET a = b WHERE id = 2;
	Etc...

What I'd REALLY like to do is add more information in there. Perhaps add
the FUNCTION/METHOD and the logged-in web USER that is actually executing
that SQL, etc.

My concern is, my gut tells me that the built in mysql cache system is
"dumb". And by that I mean, I suspect that mySQL isn't "smart" enough to
strip out comments from the SQL statement string BEFORE storing it as the
cache hash key (yet I have no facts either way to back it up and hence the
reason for this email).

http://dev.mysql.com/doc/refman/5.0/en/query-cache.html
http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html 

Can anyone please tell me I'm wrong and that it is smarter than I give it
credit for, as I think this would be a very useful "feature" (or bug-fix as
the case may be).

 

Thread
Does mysql cache strip out /* comments */ first?Daevid Vincent18 Nov
  • Re: Does mysql cache strip out /* comments */ first?Johan De Meersman18 Nov
    • RE: Does mysql cache strip out /* comments */ first?andrew.2.moore18 Nov
      • Re: Does mysql cache strip out /* comments */ first?Johan De Meersman18 Nov
  • Re: Does mysql cache strip out /* comments */ first?Ewen Fortune18 Nov
    • RE: Does mysql cache strip out /* comments */ first?Daevid Vincent18 Nov
      • Re: Does mysql cache strip out /* comments */ first?Ewen Fortune18 Nov
        • Re: Does mysql cache strip out /* comments */ first?Waynn Lue19 Nov