MySQL Bugs: #19093: SHOW STATUS defaults to SESSION

Bug #19093	SHOW STATUS defaults to SESSION
Submitted:	14 Apr 2006 0:33	Modified:	31 May 2006 19:36
Reporter:	Jeremy Cole (Basic Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0	OS:	Linux (Linux, any)
Assigned to:	Iggy Galarza	CPU Architecture:	Any

Description:
Please, please, stop the madness.  This is a totally unnecessary and daft change to make, and only makes everyone's lives harder for absolutely NO gain.

With the new SHOW SESSION STATUS and SHOW GLOBAL STATUS commands in MySQL 5.0, the behaviour of SHOW STATUS has changed, and now defaults to showing the session status instead of (as in 3.x and 4.x) the global status.

This breaks every monitoring script currently in existance in a quite subtle way: they just start reporting stagnant/wrong information.

How to repeat:
SHOW STATUS

Suggested fix:
Change the default behaviour of SHOW STATUS to default to SHOW GLOBAL STATUS, for parity with 4.x.

This should not break anything, really: most people and most scripts currently using the command currently have to do SHOW GLOBAL STATUS to get the global statuses, and that syntax would continue to work.

This is a serious problem.  There are closed source applications that us SHOW STATUS to monitor MySQL databases that may not be maintained anymore, or may not be able to be upgraded due to licensing or version restrictions.

In these cases, the new behavior completely breaks these monitoring systems leaving customers completely in the lurch.

I strongly urge you to reconsider this "feature" and bring back the default behavior of SHOW STATUS.  

SHOW SESSION STATUS is very useful, but should most certainly not be the default in my opinion.

I agree. There is no benefit to changing the default behavior of show status and it causes many issues. I'm happy that show session status was created but changing the default behavior is a mistake that will cause many problems and much frustration. 

I hope this oversight is corrected.

This bug is related to bug #18669 "Session COM_STATISTICS breaks mysqladmin status"

Whatever happened to the "Law/Principal of Least Astonishment" ?  MySQL's failure to adhere to this has bitten me on occasion. While we're at it can we please do things like alias @@global.table_cache => @@global.table_open_cache and present a warning stating that the variable name has been changed in 5.1.

Also related to:

http://bugs.mysql.com/bug.php?id=13328

http://bugs.mysql.com/bug.php?id=16124

Also note that \s (status) in the mysql command-line client in 5.0 uses "show status" as well, and thus shows slow query and open table counts for the current session.

I have several applications that rely on SHOW STATUS, and I had to adjust all of them because of this changed behavior in 5.0. That's bad, and it was a hassle for me, but I believe that there are countless unsuspecting applications being used with MySQL < 5.0, whose authors (and their users) don't even know about it. 
This is a source of cascading bugs that can be avoided by defaulting SHOW STATUS to GOBAL.

This seems to be a no-brainer.  The default can't simply be changed.  There needs to be a pain threshold for changes like this.

It sounds logical NOT to change the default and break existing applications. 

There really should be someone who takes care of backwards compatibility problems over there at MySQL. I've said it before -- if you want to change something like that, at least let it take 1 major version with a mark of "deprecated".

C'mon, revert this change!

Bug http://bugs.mysql.com/bug.php?id=19422 was marked as
duplicate of this one.

Sure, 

This is pretty ugly way and I vouted for it to be  GLOBAL before 5.0 was released but Monty decided other way.  Now we have the challange as changing it back in 5.0   6 months after it was changed may break  new applications which may use it to pool session data (ie for query profiling) 

Not a nice choise. 

I'm however quite sure  there are much more old applications which got broken rather than new ones.

On the side notes with recent MySQL versions you need to be prepared for changes when you upgrade. For example in MySQL 4.1 you've got to redo parsing of timestamp values, with 5.0 you need to rewrite number of joins   and there are few lesser items.

Yes, as I've stated: My feeling is that there are far more applications expecting the old behaviour than the new.

Regarding timestamp formatting in 4.1: I've got a patch that "fixes" that, by adding an sql_mode called timestamp_mysql40_format.

Backwards compatibility isn't that hard.  Why has MySQL forgotten how to do it?

It's also not listed in the 5.0 upgrade guide as something that breaks backwards compatability. I'm emailing docs to update.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6649

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Sorry, but this is not a bug (except maybe in documentation if this
change is not clearly noted in the upgrade section).

It was before regard as a bug that 'show status' did show you the
global information and in my opinion we fixed this 'right' in 5.0. It
now works like the other 'show' commands, like 'show variables' that
shows the local variables. To have 'show status' work differently than
most other commands would not be good thing.

Trying to fix this 'back' in a stable releases as 5.0 is also not an
option at it would break people that depends on the current behavior.

In the MySQL manual, we have from the start said that the to monitor
what a query does, you should do:

flush status
select...
show status

Before you got a lot of 'noise' into the status, which the current behavior
fixed.

Most applications are going to be updated to 5.0 soon, so this
shouldn't be a problem in the long run. If one has an application that one wants to have compatible with both 5.0 and 5.1 one should use:

show /*!50002 GLOBAL */ status"

(like we have done in mysqladmin since 5.0.2)

To summarize why we are not going to fix this:

- We made the change early in a new major release, which is when it's
ok to fix wrong behavior. (We do belive in keeping things backward compatible
but sometimes you have things you got wrong early on or things that
many regards as a bug).
- We can't make an incompatible change in a stable release.
(This change was made in 5.0.2 and it has thus been the expected behavior
for anyone using it 5.0).
- The current behavior is consistent with other similar commands.
- It's how a new MySQL user would expect 'show status' to work
- In theory it's possible to add a sql mode to handle this for one release
but we try to not do that, expect in rare cases, as having millions of
sql modes makes things hard to manage in the long run.

Hi Monty,

Thanks for the detailed reply. I suspect that your decision is made, but nonetheless would like to reply to a few of your points.

It's hardly convincing to compare SHOW STATUS to SHOW VARIABLES as a mark of something done right, as SHOW VARIABLES (since 4.1 anyway) has been showing a mix of session and global variables for a while now, with no indication as to the scope of anything it shows. It isn't exactly the hallmark of usability.

SHOW STATUS at least didn't have that problem. If anything I would have changed the behaviour of SHOW VARIABLES to only show GLOBAL by default, if I had changed anything. That way, at least SHOW SESSION VARIABLES and SHOW SESSION STATUS could default to show ONLY the variables that are specific to a given session.

As it is, it's a usability nightmare.

To argue that you've been claiming in the manual to use FLUSH STATUS and SHOW STATUS to test a command is a weak argument at best: that's always been a shoddy and problem-filled way of finding any information about how a command was executed. SHOW SESSION STATUS theoretically fixes that, but not really, unless you're a human dictionary. See my suggestion for Scope below.

Next, it would be frighteningly stupid for anyone to have written a new application that is dependent on the new behaviour of SHOW STATUS: on the contrary, one hopes they would have used the same silly trick: SHOW /*!50002 SESSION */ STATUS to ensure that what they get back doesn't change.

But yes, I'll concede that it will break new applications that depend on this new behaviour, and challenge you to name one. :)

In order to not be completely belligerent in this: I'll suggest a compromise. If your mind is set that the change will not be reversed, and we are stuck with the new, in my opinion, broken behaviour, can we add a "Scope" column to the SHOW STATUS and SHOW VARIABLES (default, SESSION, and GLOBAL) commands to show whether each variable is being taken from the SESSION or GLOBAL scope?

That won't fix the broken applications, but will at least let a DBA know, when they run the command to see why their monitoring stopped working, that the problem is related to the scope of the variables they are seeing.

I have explicitly pointed out the incompatibility with
pre-5.0.2 versions (and noted the /*!50002 GLOBAL */
workaround, for those who can use it) in these sections:

- Upgrading from 4.1
  http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html
- SHOW STATUS
  http://dev.mysql.com/doc/refman/5.0/en/show-status.html
- 5.0.2 changelog
  http://dev.mysql.com/doc/refman/5.0/en/news-5-0-2.html

I don't mean to stir up an old bug report, but instead of having session and global values with the same name, and the session values "hiding" the global values, perhaps one could see ALL values, and the session values can have a different name.

With this suggestion, if I run SHOW STATUS, I'd see something like

Com_admin_commands 100
...
Session_com_admin_commands 0

Perhaps this change can be made in 5.1, because as Monty pointed out, early in a major release is the time to fix things.

I have been thinking about it for a while, and I think this addresses everyone's needs, follows the Principle Of Least Astonishment, and conforms to Monty's statement that "It was before regard as a bug that 'show status' did show you the global information and in my opinion we fixed this 'right' in 5.0."

I'd like MySQL to give this serious consideration, whether one considers the current functionality to have fixed some previous deficiency or not. Even ignoring past history and pretending that SHOW STATUS has always shown SESSION by default and allowed the option of GLOBAL, SHOW STATUS has the problem of having GLOBAL and SESSION values with the same name, and you can only see one at a time -- the one hides the other.

This comment is not about reverting to the previous behavior, because I agree it had its problems too. I just want us to fix those problems a better way. The incompatibility between the old and new behavior isn't the only problem with the new behavior -- it is still not good enough.

I didn't want to open a new bug report for this, but if desired I will.