SET STATEMENT does not work after SET GLOBAL / SHOW GLOBAL STATUS and affects the global value

[Elena Stepanova]

# Initial values:
MySQL [test]> select @@global.lock_wait_timeout, @@session.lock_wait_timeout;
+----------------------------+-----------------------------+
| @@global.lock_wait_timeout | @@session.lock_wait_timeout |
+----------------------------+-----------------------------+
| 31536000 | 31536000 |
+----------------------------+-----------------------------+
1 row in set (0.01 sec)

# SET STATEMENT works (OK):
MySQL [test]> set statement lock_wait_timeout=1 for select @@lock_wait_timeout;
+---------------------+
| @@lock_wait_timeout |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.00 sec)

# Initial values are intact (OK):
MySQL [test]> select @@global.lock_wait_timeout, @@session.lock_wait_timeout;
+----------------------------+-----------------------------+
| @@global.lock_wait_timeout | @@session.lock_wait_timeout |
+----------------------------+-----------------------------+
| 31536000 | 31536000 |
+----------------------------+-----------------------------+
1 row in set (0.00 sec)

# Setting a totally unrelated global variable:
MySQL [test]> set global general_log=0;
Query OK, 0 rows affected (0.01 sec)

# Initial values are still intact (OK):
MySQL [test]> select @@global.lock_wait_timeout, @@session.lock_wait_timeout;
+----------------------------+-----------------------------+
| @@global.lock_wait_timeout | @@session.lock_wait_timeout |
+----------------------------+-----------------------------+
| 31536000 | 31536000 |
+----------------------------+-----------------------------+
1 row in set (0.00 sec)

# ... but SET STATEMENT does not work anymore (FAIL):
MySQL [test]> set statement lock_wait_timeout=1 for select @@lock_wait_timeout;
+---------------------+
| @@lock_wait_timeout |
+---------------------+
| 31536000 |
+---------------------+
1 row in set (0.00 sec)

# ... and it corrupts the global value (BIG FAIL):
MySQL [test]> select @@global.lock_wait_timeout, @@session.lock_wait_timeout;
+----------------------------+-----------------------------+
| @@global.lock_wait_timeout | @@session.lock_wait_timeout |
+----------------------------+-----------------------------+
| 1 | 31536000 |
+----------------------------+-----------------------------+
1 row in set (0.00 sec)

[Valerii Kravchuk (valerii-kravchuk)]

This is easy to confirm:

[openxs@centos ~]$ mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.21-69.0 Percona Server (GPL), Release 69.0, Revision 675

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

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 @@global.lock_wait_timeout, @@session.lock_wait_timeout;
+----------------------------+-----------------------------+
| @@global.lock_wait_timeout | @@session.lock_wait_timeout |
+----------------------------+-----------------------------+
| 31536000 | 31536000 |
+----------------------------+-----------------------------+
1 row in set (0.00 sec)

mysql> set statement lock_wait_timeout=1 for select @@lock_wait_timeout;
+---------------------+
| @@lock_wait_timeout |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.00 sec)

mysql> select @@global.lock_wait_timeout, @@session.lock_wait_timeout;
+----------------------------+-----------------------------+
| @@global.lock_wait_timeout | @@session.lock_wait_timeout |
+----------------------------+-----------------------------+
| 31536000 | 31536000 |
+----------------------------+-----------------------------+
1 row in set (0.00 sec)

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

mysql> select @@global.lock_wait_timeout, @@session.lock_wait_timeout;
+----------------------------+-----------------------------+
| @@global.lock_wait_timeout | @@session.lock_wait_timeout |
+----------------------------+-----------------------------+
| 31536000 | 31536000 |
+----------------------------+-----------------------------+
1 row in set (0.00 sec)

mysql> set statement lock_wait_timeout=1 for select @@lock_wait_timeout;
+---------------------+
| @@lock_wait_timeout |
+---------------------+
| 31536000 |
+---------------------+
1 row in set (0.00 sec)

mysql> select @@global.lock_wait_timeout, @@session.lock_wait_timeout;
+----------------------------+-----------------------------+
| @@global.lock_wait_timeout | @@session.lock_wait_timeout |
+----------------------------+-----------------------------+
| 1 | 31536000 |
+----------------------------+-----------------------------+
1 row in set (0.00 sec)

[George Ormond Lorch III (gl-az)]

I just hit this trying to fix some myrocks tests, consider the following mtr case which fails on termination as the ending global sql_mode is not the same as the starting global sql_mode

1 --source include/have_innodb.inc
  2
  3 --let $engine=innodb
  4
  5 SET @ORIG_VALUE = @@INNODB_ADAPTIVE_FLUSHING;
  6 SET GLOBAL INNODB_ADAPTIVE_FLUSHING = 0;
  7
  8 #
  9 # Fixed point columns with indexes
 10 #
 11
 12 eval CREATE TABLE t1 (
 13 d1 DECIMAL(10,2) PRIMARY KEY,
 14 d2 DECIMAL(60,10),
 15 n1 NUMERIC,
 16 n2 NUMERIC(65,4)
 17 ) ENGINE=$engine;
 18
 19 SHOW INDEX IN t1;
 20
 21 SET STATEMENT sql_mode=NO_ENGINE_SUBSTITUTION FOR
 22 INSERT IGNORE INTO t1 (d1,d2,n1,n2) VALUES
 23 (10.22,60.12345,123456,14.3456),
 24 (10.0,60.12345,123456,14),
 25 (11.14,15,123456,13),
 26 (100,100,1,2),
 27 (0,0,0,0),
 28 (4540424564.23,3343303441.0,12,13),
 29 (15,17,23,100000);
 30
 31 DROP TABLE t1;
 32
 33 SET GLOBAL INNODB_ADAPTIVE_FLUSHING = @ORIG_VALUE;

[Laurynas Biveinis (laurynas-biveinis)]

Coming from bug 1631783, the same happens if SET GLOBAL is replaced by SHOW GLOBAL STATUS. What these two have in common is that they set lex->option_type to OPT_GLOBAL, which is then used by SET STATEMENT parsing as-is, without resetting to OPT_SESSION.

[Laurynas Biveinis (laurynas-biveinis)]

    https://github.com/percona/percona-server/pull/1795
    https://github.com/percona/percona-server/pull/1796

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1568