SET STATEMENT ... FOR ... changes global variables

[Sergei Glushchenko]

MTR test case:

--source include/count_sessions.inc

SET STATEMENT sql_mode='' FOR SELECT 1;
SET STATEMENT innodb_tmpdir='/tmp' FOR SELECT 1;

--source include/wait_until_count_sessions.inc

fails with error

MTR's internal check of the test case 'main.sql-mode-ps' failed.
This means that the test case does not preserve the state that existed
before the test case was executed. Most likely the test case did not
do a proper clean-up. It could also be caused by the previous test run
by this thread, if the server wasn't restarted.
This is the diff of the states of the servers before and after the
test case was executed:
mysqltest: Logging to '/Users/sergei/percona/git/ps-bug1626519/5.7-ps-bug1626519/obj/mysql-test/var/tmp/check-mysqld_1.log'.
mysqltest: Results saved in '/Users/sergei/percona/git/ps-bug1626519/5.7-ps-bug1626519/obj/mysql-test/var/tmp/check-mysqld_1.result'.
mysqltest: Connecting to server localhost:13001 (socket /Users/sergei/percona/git/ps-bug1626519/5.7-ps-bug1626519/obj/mysql-test/var/tmp/mysqld.1.sock) as 'root', connection 'default', attempt 0 ...
mysqltest: ... Connected.
mysqltest: Start processing test commands from './include/check-testcase.test' ...
mysqltest: ... Done processing test commands.
--- /Users/sergei/percona/git/ps-bug1626519/5.7-ps-bug1626519/obj/mysql-test/var/tmp/check-mysqld_1.result 2016-10-09 20:59:13.000000000 +0300
+++ /Users/sergei/percona/git/ps-bug1626519/5.7-ps-bug1626519/obj/mysql-test/var/tmp/check-mysqld_1.reject 2016-10-09 20:59:13.000000000 +0300
@@ -353,7 +353,7 @@
 INNODB_TEMP_DATA_FILE_PATH ibtmp1:12M:autoextend
 INNODB_THREAD_CONCURRENCY 0
 INNODB_THREAD_SLEEP_DELAY 10000
-INNODB_TMPDIR
+INNODB_TMPDIR /private/tmp
 INNODB_TRACK_CHANGED_PAGES OFF
 INNODB_TRACK_REDO_LOG_NOW OFF
 INNODB_TRX_PURGE_VIEW_UPDATE_ONLY_DEBUG OFF
@@ -603,7 +603,7 @@
 SQL_BUFFER_RESULT OFF
 SQL_LOG_BIN ON
 SQL_LOG_OFF OFF
-SQL_MODE ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
+SQL_MODE
 SQL_NOTES ON
 SQL_QUOTE_SHOW_CREATE ON
 SQL_SAFE_UPDATES OFF

mysqltest: Result content mismatch

Using count_sessions/wait_until_count_sessions is important for some reason.

[Sergei Glushchenko (sergei.glushchenko)]

When I modify the test case to be as follows:

--source include/count_sessions.inc

SET STATEMENT sql_mode='' FOR SELECT 1;
SELECT @@global.sql_mode;
SET STATEMENT innodb_tmpdir='/tmp' FOR SELECT 1;
SELECT @@global.innodb_tmpdir;

--source include/wait_until_count_sessions.inc

The result file contains:

SET STATEMENT sql_mode='' FOR SELECT 1;
1
1
Warnings:
Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
SELECT @@global.sql_mode;
@@global.sql_mode

SET STATEMENT innodb_tmpdir='/tmp' FOR SELECT 1;
1
1
SELECT @@global.innodb_tmpdir;
@@global.innodb_tmpdir
/private/tmp

The bug is present only in 5.7

[Laurynas Biveinis (laurynas-biveinis)]

Reduced testcase

SHOW GLOBAL STATUS LIKE 'Threads_connected';

SELECT @@global.sql_mode;
SET STATEMENT sql_mode='NO_AUTO_CREATE_USER' FOR SELECT 1;
SELECT @@global.sql_mode;

[Laurynas Biveinis (laurynas-biveinis)]

If one replaces the SET STATEMENT statement with

SET STATEMENT sql_mode='NO_AUTO_CREATE_USER' FOR SELECT @@SESSION.sql_mode;

it will show that it is only the global variable which is changed. Also, by code review, audit plugins receive global var update notifications for the above statement.

[Laurynas Biveinis (laurynas-biveinis)]

Not a regression: this never worked and has been reported as bug 1385352, continuing there.