MySQL Bugs: #106455: information_schema_stats_expiry is not a global variable

Bug #106455	information_schema_stats_expiry is not a global variable
Submitted:	14 Feb 2022 12:08	Modified:	15 Feb 2022 21:20
Reporter:	Rahul Sisondia	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.7, 8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:

The manual says that information_schema_stats_expiry is global variable 
manual :  https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html about the variable 

But looking at the code we can deduce that it is a session variable.  

static Sys_var_ulong Sys_information_schema_stats_expiry(
    "information_schema_stats_expiry",
    "The number of seconds after which mysqld server will fetch "
    "data from storage engine and replace the data in cache.",
    SESSION_VAR(information_schema_stats_expiry), CMD_LINE(REQUIRED_ARG),
    VALID_RANGE(0, LONG_TIMEOUT), DEFAULT(24 * 60 * 60), BLOCK_SIZE(1));

How to repeat:

mysql> select  @@information_schema_stats_expiry;
+-----------------------------------+
| @@information_schema_stats_expiry |
+-----------------------------------+
|                             86400 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> set @@global.information_schema_stats_expiry=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select  @@information_schema_stats_expiry;
+-----------------------------------+
| @@information_schema_stats_expiry |
+-----------------------------------+
|                             86400 |
+-----------------------------------+
1 row in set (0.00 sec)

Suggested fix:
Either update the documentation or make it global variable

Hello Rahul,

Thank you for the report and feedback.
IMHO this is not a bug but expected behavior, quoting from official manual "If you change a global system variable, the value is remembered and used to initialize the session value for new sessions until you change the variable to a different value or the server exits. The change is visible to any client that accesses the global value. However, the change affects the corresponding session value only for clients that connect after the change. The global variable change does not affect the session value for any current client sessions (not even the session within which the global value change occurs). " - https://dev.mysql.com/doc/refman/8.0/en/set-variable.html

--
 bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select  @@information_schema_stats_expiry;
+-----------------------------------+
| @@information_schema_stats_expiry |
+-----------------------------------+
|                             86400 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> set @@global.information_schema_stats_expiry=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select  @@information_schema_stats_expiry;
+-----------------------------------+
| @@information_schema_stats_expiry |
+-----------------------------------+
|                             86400 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> \r
Connection id:    8
Current database: *** NONE ***

mysql> select  @@information_schema_stats_expiry;
+-----------------------------------+
| @@information_schema_stats_expiry |
+-----------------------------------+
|                                 0 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql>

Or is there something I'm missing here, please let me know? Thank you!

regards,
Umesh

Hello Sastry, 
As per the manual the  Scope is Global, Session but if we look at the code and its implementation it is merely a session variable. This is a documentation bug at least.

Thank you for the feedback.
Let me pass on this to doc team.

regards,
Umesh

Hi Rahul,

This is not a bug. The scope macros used in sys_vars.cc and other places are as follows:

1. GLOBAL(varname) in sys_vars.cc means varname is global.

2. SESSION(varname) means that varname has both global and session scope--that is, varname has session scope *in addition to* global scope.

3. If varname is session-only, this indicated by SESSION_ONLY(varname), or, in a few cases, by the lack of GLOBAL(), SESSION(), or SESSION_ONLY().

By way of comparison, note that sys_var::flag_enum (https://dev.mysql.com/doc/dev/mysql-server/latest/classsys__var.html#a3ffb3adc1beedff5322c...) used in sys_var constructor has GLOBAL, SESSION, ONLY_SESSION values.

So information_schema_stats_expiry has both global and session scope.

cheers

jon.

The macro names are actually GLOBAL_VAR(), SESSION_VAR(), and SESSION_ONLY(). Sorry for any confusion.