FLUSH STATUS surprise?

by Nov 27, 2011

What does FLUSH STATUS do? We all know that it will simply reset all status variables (except for 'uptime') to same values as immediately after a server restart. Just google it and you will find that behaviour statet on hundreds of sites on the Internet.

But NO – it is not true! Not anymore. MySQL documentation:
http://dev.mysql.com/doc/refman/5.5/en/flush.html

"This option adds the current thread's session status variable values to the global values and resets the session values to zero. It also resets the counters for key caches (default and named) to zero and sets Max_used_connections to the current number of open connections."

This bug report http://bugs.mysql.com/bug.php?id=22875 has some background. Before introduction of seperate SESSION and GLOBAL status variables, FLUSH STATUS really did reset (almost) all status variables to same values as immediately after a server restart.  Now it affects SESSION status variables (but why is SUPER privilege required for this?) and a few GLOBAL status variables only.

From the bug report I think that the change of behavior was not really planned and specified properly in advance but came as some kind of surprise – also to some people in the MySQL developer team. I agree with Konstantin Osipov's (one of those MySQL developers that seemed surprised) comment in the bug report:

"As this behaviour got broken in 4.1, I personally consider it a regression and believe that it should be fixed as a regression. In order to fix it in line with the design of SHOW [SESSION/GLOBAL] STATUS we should implement FLUSH GLOBAL STATUS.FLUSH STATUS behaviour should be modified to not change any global data,  such as:
– key cache counters
– connection counters
– SHOW_LONG global variables.
In other words, FLUSH STATUS should behave as FLUSH SESSION STATUS and only reset session-scope status variables. [SESSION] keyword should be added to the grammar and as optional.Key cache counters, connection counters, SHOW_LONG variables and server-scope status variable (those that have session counterparts) should be reset by FLUSH GLOBAL STATUS. FLUSH SESSION STATUS should no longer require SUPER privilege (connection privilege is enough, and as long as it is checked at connection, no privilege check is necessary).FLUSH GLOBAL STATUS should require SUPER privelege."

But what happened was that the new behaviour was simply documented and since the problem/the report has been abandoned. It is now 5 years ago. Nothing happened since. The bug report is still open with the status of 'verified'.

The reason why I am writing this is that I just replied to a concern to a MONyog user on whether MONyog could be fooled by this when calculating counters in the "ALL TIME" timeframe. The answer is that it cannot provided the actual server has implemented the GLOBAL status variable 'uptime_since_flush_status'. All recent servers have (only very early 5.0 and 5.1 servers (5.0.35-,  5.1.24-) that nobody should use today have not). MONyog will simply discard all collected information older than 'uptime_since_flush_status' even if it has not yet been purged from the MONyog embedded database.

But still: Can we soon have that SESSION|GLOBAL switch for FLUSH STATUS, please? What we have now is a mismatch both as regards the semantics and the privileges requried. And 5 years of 'stand-still' for a 'verified' bug report related to server core features is too much (even though it is probably not the only one).