MySQL Bugs: #78069: Global-only variables in session_variables and session

Bug #78069	Global-only variables in session_variables and session_status are useless
Submitted:	14 Aug 2015 12:26	Modified:	18 Aug 2015 20:20
Reporter:	Sveta Smirnova (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Performance Schema	Severity:	S4 (Feature request)
Version:	5.7.8	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
Release Notes in 5.7.8 contain following:

When the Performance Schema session variable tables produced output, they included no rows for global-only variables and thus did not fully reflect all variable values in effect for the current session. This has been corrected so that each table has a row for each session variable, and a row for each global variable that has no session counterpart. This change applies to the session_variables and session_status tables. 

But this change makes usage of session tables harder: we usually need to know which session variables changed, not see 500+ rows with same information

How to repeat:
SELECT * FROM session_status WHERE variable_value>0; 

is much easier way to find out what happened inside session than

select ss.variable_name, ss.variable_value 
from session_status ss left join global_status gs using(variable_name) 
where ss.variable_value != gs.variable_value or gs.variable_value is null 
and ss.variable_value>0;

Suggested fix:
I don't see any public request for this change. Is it really needed to make tables consistent, but harder to use?

Hi this is indeed very much welcome change.

Verifed.

SHOW VARIABLES has always displayed both session level variables and global
level variables in the same output, this is nothing new.

Likewise for table INFORMATION_SCHEMA.SESSION_VARIABLES.

The performance schema table session_variable does the same,
in particular to preserve this established behavior.

To take an example, some MySQL connectors, the Java connector in particular,
do rely on SHOW VARIABLE to return both session level and global level
variables, and this since 5.0.3 per the following code:

See src/com/mysql/jdbc/ConnectionImpl.java,
method ConnectionImpl.loadServerVariables()

            if (versionMeetsMinimum(5, 0, 3)) {
                query = versionComment + "SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout'"
                        + " OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client'"
                        + " OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server'"
                        + " OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results'"
                        + " OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone'"
                        + " OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length'"
                        + " OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size'"
                        + " OR Variable_name = 'license' OR Variable_name = 'init_connect'";
            }

Other connectors or applications are likely to be affected also,
for example when connecting to the server, a typical query such as
  SHOW VARIABLES WHERE Variable_name='version';
must succeed even without the GLOBAL keyword, 
or existing application issuing this query (for good or bad reasons)
will be utterly broken.

Allowing already deployed applications using already deployed connectors
to connect to a MySQL 5.7 server is not a item we would qualify as 'useless'.

This bug report is not verified, it is rejected.

Maybe can be addition to sys schema to have a view of session/global variables only?

Marc,

btw this report is not about SHOW VARIABLES. I don't offer to change behavior of this command. This report, instead, of Performance Schema tables only.

Does your answer mean what SHOW VARIABLES are mapped to Performance Schema now? What will happen if one turns Performance Schema OFF?

Sveta, this is a deliberate change to improve consistency between SHOW VARIABLES and PS to make it easier for applications to use the easier to parse PS and to make it easier for users to migrate to using PS.

I agree with the observation that this has made it a bit harder to find variables that have changed than it was in 5.6. Another quite common desire is to show all variables that are not set to server defaults.

You seem to have a good suggestion for the documentation team, on what the workaround to get the 5.6 behaviour should be. Perhaps you could file a documentation bug suggesting that they give the workaround, pointing to this bug as the reason?

When it comes to turning PS off, the hope is that PS overhead is lower than the overhead of doing things without having PS off. This is because we're replacing some alternative internal ways of doing things with PS being the underlying data provider. So things like SHOW VARIABLES and SHOW STATUS and the relates IS tables can draw their output from PS instead of duplicating the work. But PS isn't all on or all off, and we can work to try to keep the set that is on by default quite low overhead.

The PS migration intent is documented at https://dev.mysql.com/doc/refman/5.7/en/performance-schema-variable-table-migration.html .

As usual we'll continue to monitor the potential disruption of removing older ways of doing things and can adjust the plans to keep the pain level suitably low, while still making progress. You remember how long it took us to make InnoDB the default storage engine then to make innodb_file_per_table the default. We're trying to move faster but always have to look at the older applications we have to support as well.

As you can see from the migration page, when show_compatibility_56 is off, SHOW VARIABLES and SHOW STATUS now draw their data from PS.

We can hope that people may just switch to using the PS forms because it's easy to join those with other tables to produce enhanced reporting like the changed variables but also whatever else comes up as convenient.

The default memory cost of PS has also been improved, described at https://dev.mysql.com/doc/refman/5.7/en/performance-schema-variable-table-migration.html and https://dev.mysql.com/doc/refman/5.7/en/performance-schema-memory-model.html so we can hope that there's less reason than before to have it off.

PS tuning is still going to be an ongoing project, though. Always room to improve how it works in some way.

James Day, MySQL Senior Principal Support Engineer, Oracle