visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: mysql "show status" NOT returning correct values !
By: Nono Great Britain  Date: 20/01/2007 11:24:45  English  Points: 20 Status: Answered
Quality : Excellent
Hello,

I use a very old PHP script that checks values from the "SHOW STATUS" command passed to MySql.

Suddenly, I was shocked to see that the number of selects, connections, bytes sent etc didn't change over time !

The returned values are COMPLETELY DIFFERENT from the ones I see in the console when I type directly "show status"

I tried to deactivate the query cache using the SESSION attribute, but to no avail on my script's results

HELP !
By: VGR Date: 20/01/2007 11:31:33 English  Type : Answer
Ooookaaaayyyy so you tried "set SESSION query_cache_type=0;" (or = OFF) as per the documentation on the QCache. No wonder it didn't change anything, for three reasons :
1) theoretically, those queries "SHOW STATUS" are not cacheable
2) the QCache never shows stale data (in other words, it seems to perform perfectly well)
3) your MySql session in the console was NOT the same as the MySql session in your browser ;-))

The answer to your question lies somewhere else. If you read the updated documentation at this MySql doc page about SHOW STATUS you'll notice that it says :


The GLOBAL and SESSION options are new in MySQL 5.0.2. With the GLOBAL modifier, SHOW STATUS displays the status values for all connections to MySQL. With SESSION, it displays the status values for the current connection. If no modifier is present, the default is SESSION. LOCAL is a synonym for SESSION.



So in fact, starting with MySql 5.0.2 (not that long ago ;-), the ***default*** behaviour of "SHOW STATUS" CHANGED : it no longer displays GLOBAL values.

All you've to do is add "GLOBAL" in your "SHOW STATUS" command in your script, and things should be back working as usual.

The recommended way is to write

SHOW /*!50002 GLOBAL */ STATUS;


but some data layers don't like comments (some even don't accept the SQL-required trailing semi-colon).

It would probably have been simplier for us than MySql people didn't make "SESSION" the default value of "SHOW STATUS" but rather "SHOW GLOBAL STATUS".

best regards ;-)
By: Nono Date: 24/01/2007 07:27:20 English  Type : Comment
YEAH ! Cool to have European Experts Exchange around !

many many thanks !

you're the King

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 292.975900 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page