Last month I blogged about the silly changes to the default for the SHOW STATUS command in MySQL 5.0 in Stop the madness: SHOW STATUS, and I filed MySQL Bug #19093: SHOW STATUS defaults to SESSION.
Well, it looks like Monty has spoken and this will not be fixed. That’s unfortunate, but oh well, I tried.
You can read the full text of Monty’s reply in the bug report itself, but his reasons for supporting the current behaviour boil down to:
- It’s similar to what SHOW VARIABLES does. — Yes, this is true, but neither commands’ output makes sense, really. If you do SHOW [SESSION] STATUS, you get a mix of session-scoped and global-scoped results back, and there’s no way to tell which is which. Same goes for SHOW [SESSION] VARIABLES. Ugh.
- MySQL 5.0 is considered “stable” now, so it’s bad to change it now. — I agree on this part, it’s bad form to change it now, but at the same time, very few people are really using 5.0, so this is in fact a good time to change it, before people find out that it’s broken.
- MySQL’s documentation has long suggested that people use FLUSH STATUS and SHOW STATUS to find out how query was executed, which kind of works now. — Yes, the manual has suggested this, and a few people have made limited use of it, but come on, it’s impossible to use this in production, and it’s only even of limited value on a test server.
I suggested a compromise: To add a Scope column to the output of the SHOW STATUS and SHOW VARIABLES commands, which would indicate the scope of the value seen in the output. What do you think?
Jeremy Cole 
How exactly would “scope” help? Does that solve the problems: for example, I don’t think it solves the problems with regard to existing tools, right?
Hi Martijn,
As I mentioned in my suggestion in the bug report, it doesn’t solve anything, of the current problem, but it *does* bring a little piece of sanity back. And, at least if someone is bitten by the problem, and they go to run SHOW STATUS to see what it returns, they’ll see a column full of “SESSION”, and that might tip them off to what’s wrong.
Regards,
Jeremy
That would be nice to have, but only be really efficient and useful if used for both SHOW STATUS and SHOW VARIABLES.
So, in other words … SHOW STATUS includes a “random” subset, or what?
Shouldn’t FLUSH STATUS support a SESSION option as well…
Hi Rainer,
SHOW STATUS and SHOW VARIABLES (which both default to the SESSION variant) return a mix of session-scoped and global-scoped variables.
Basically, they return all variables, regardless of whether they have a session-scoped variant. If they lack a session-scoped variant, they return the global value in its place. In order to understand the output, you have to know and keep track of which ones are really session and which are really global. (Which can change at any time.)
Did I mention this was ill-conceived? :)
Hi Nick,
Yes, it probably should exist (but doesn’t currently).
*conceives SHOW IDEAL STATUS*