MySQL applys global variables to opened session

Question

I've found that session variables have priority over global variables.

Is it possible to apply global variables which is set by "set global" query to opened session so make local one to same with global one?

Or, is there any way to wait all opened session finish their tasks, and make sleeping session to reconnect?

score 0 · Answer 1 · answered Apr 03 '18 at 17:31

You can reset any session variable back to its global variable equivalent whenever you want, you don't have to reconnect.

e.g.

mysql> SET @@session.foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@global.foreign_key_checks,@@session.foreign_key_checks;
+-----------------------------+------------------------------+
| @@global.foreign_key_checks | @@session.foreign_key_checks |
+-----------------------------+------------------------------+
|                           1 |                            0 |
+-----------------------------+------------------------------+
1 row in set (0.00 sec)

mysql> SET @@session.foreign_key_checks=@@global.foreign_key_checks;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@global.foreign_key_checks,@@session.foreign_key_checks;
+-----------------------------+------------------------------+
| @@global.foreign_key_checks | @@session.foreign_key_checks |
+-----------------------------+------------------------------+
|                           1 |                            1 |
+-----------------------------+------------------------------+
1 row in set (0.00 sec)

is it valid on only current session?, other opened session will still have @@session.foreign_key_checks with value 0 — kconstant, Apr 04 '18 at 00:57
That's right, session variables only apply to the connection they are set within. — Paul Campbell, Apr 04 '18 at 05:56

score 0 · Answer 2 · answered Sep 14 '22 at 06:15

Yes, session variables are prioritized to use over global variables, and there are 2 kinds of global and session variables.

Like "transaction_isolation", one can automatically set global variables to session variables after changing global variables and reconnecting (logging out and logging in) MySQL.
Like "wait_timeout", one cannot automatically set global variables to session variables after changing global variables and reconnecting (logging out and logging in) MySQL.

For example about "transaction_isolation", REPEATABLE-READ is the default value for "transaction_isolation" global and session variables as shown below:

mysql> SELECT @@global.transaction_isolation, @@session.transaction_isolation;
+--------------------------------+---------------------------------+
| @@global.transaction_isolation | @@session.transaction_isolation |
+--------------------------------+---------------------------------+
| REPEATABLE-READ                | REPEATABLE-READ                 |
+--------------------------------+---------------------------------+

Then, we set READ-UNCOMMITTED to "transaction_isolation" global variable as shown below:

mysql> SET GLOBAL transaction_isolation = 'READ-UNCOMMITTED';

Then, READ-UNCOMMITTED is only set to "transaction_isolation" global variable as shown below:

mysql> SELECT @@global.transaction_isolation, @@session.transaction_isolation;
+--------------------------------+---------------------------------+
| @@global.transaction_isolation | @@session.transaction_isolation |
+--------------------------------+---------------------------------+
| READ-UNCOMMITTED               | REPEATABLE-READ                 |
+--------------------------------+---------------------------------+

Then, log out MySQL:

mysql> exit

Then, log in MySQL:

C:\Users\kai>mysql -u root -p

Now, READ-UNCOMMITTED is also set to "transaction_isolation" session variable as shown below:

mysql> SELECT @@global.transaction_isolation, @@session.transaction_isolation;
+--------------------------------+---------------------------------+
| @@global.transaction_isolation | @@session.transaction_isolation |
+--------------------------------+---------------------------------+
| READ-UNCOMMITTED               | READ-UNCOMMITTED                |
+--------------------------------+---------------------------------+

And for example about "wait_timeout", 28800 is the default value for "wait_timeout" global and session variables as shown below:

mysql> SELECT @@global.wait_timeout, @@session.wait_timeout;
+-----------------------+------------------------+
| @@global.wait_timeout | @@session.wait_timeout |
+-----------------------+------------------------+
|                 28800 |                  28800 |
+-----------------------+------------------------+

Then, we set 1000 to "wait_timeout" global variable as shown below:

mysql> SET GLOBAL wait_timeout = 1000;

Then, 1000 is only set to "wait_timeout" global variable as shown below:

mysql> SELECT @@global.wait_timeout , @@session.wait_timeout;
+-----------------------+------------------------+
| @@global.wait_timeout | @@session.wait_timeout |
+-----------------------+------------------------+
|                  1000 |                  28800 |
+-----------------------+------------------------+

Then, log out MySQL:

mysql> exit

Then, log in MySQL:

C:\Users\kai>mysql -u root -p

Now, 1000 is not set to "wait_timeout " session variable as shown below:

mysql> SELECT @@global.wait_timeout , @@session.wait_timeout;
+-----------------------+------------------------+
| @@global.wait_timeout | @@session.wait_timeout |
+-----------------------+------------------------+
|                  1000 |                  28800 |
+-----------------------+------------------------+

So in this case, just set 1000 to "wait_timeout " session variable as shown below;

mysql> SET SESSION wait_timeout = 1000;

Or, set DEFAULT which is 1000 of "wait_timeout" global variable to "wait_timeout" session variable as shown below:

mysql> SET SESSION wait_timeout = DEFAULT;

Now, 1000 is also set to "wait_timeout" session variable as shown below:

mysql> SELECT @@global.wait_timeout, @@session.wait_timeout;
+-----------------------+------------------------+
| @@global.wait_timeout | @@session.wait_timeout |
+-----------------------+------------------------+
|                  1000 |                   1000 |
+-----------------------+------------------------+

MySQL applys global variables to opened session

2 Answers2