diff options
| author | Oleksandr Byelkin <sanja@mariadb.com> | 2014-10-24 10:13:08 +0200 |
|---|---|---|
| committer | Oleksandr Byelkin <sanja@mariadb.com> | 2014-11-11 11:48:50 +0100 |
| commit | 1827d9e6d188e3ad53e057a583ebed02767b65ac (patch) | |
| tree | 83cbcb5d6e4284149e9a16803884ed4e4766b23d | |
| parent | a03dd94be804a4b8b1406696920834bb2c0bedbd (diff) | |
| download | mariadb-git-1827d9e6d188e3ad53e057a583ebed02767b65ac.tar.gz | |
MDEV-5231: Per query variables from Percona Server (rewritten)
29 files changed, 2681 insertions, 61 deletions
diff --git a/mysql-test/r/events_bugs.result b/mysql-test/r/events_bugs.result index e3599218aac..7ce3c740cd4 100644 --- a/mysql-test/r/events_bugs.result +++ b/mysql-test/r/events_bugs.result @@ -218,13 +218,13 @@ drop event events_test.mysqltest_user1; drop user mysqltest_user1@localhost; drop database mysqltest_db1; create event e_53 on schedule at (select s1 from ttx) do drop table t; -ERROR 42000: This version of MariaDB doesn't yet support 'Usage of subqueries or stored function calls as part of this statement' +ERROR 42000: CREATE/ALTER EVENT does not support subqueries or stored functions. create event e_53 on schedule every (select s1 from ttx) second do drop table t; -ERROR 42000: This version of MariaDB doesn't yet support 'Usage of subqueries or stored function calls as part of this statement' +ERROR 42000: CREATE/ALTER EVENT does not support subqueries or stored functions. create event e_53 on schedule every 5 second starts (select s1 from ttx) do drop table t; -ERROR 42000: This version of MariaDB doesn't yet support 'Usage of subqueries or stored function calls as part of this statement' +ERROR 42000: CREATE/ALTER EVENT does not support subqueries or stored functions. create event e_53 on schedule every 5 second ends (select s1 from ttx) do drop table t; -ERROR 42000: This version of MariaDB doesn't yet support 'Usage of subqueries or stored function calls as part of this statement' +ERROR 42000: CREATE/ALTER EVENT does not support subqueries or stored functions. drop event if exists e_16; drop procedure if exists p_16; create event e_16 on schedule every 1 second do set @a=5; @@ -265,7 +265,7 @@ begin call p22830_wait(); select 123; end| -ERROR 42000: This version of MariaDB doesn't yet support 'Usage of subqueries or stored function calls as part of this statement' +ERROR 42000: CREATE/ALTER EVENT does not support subqueries or stored functions. create event e22830_1 on schedule every 1 hour do begin call p22830_wait(); diff --git a/mysql-test/r/kill.result b/mysql-test/r/kill.result index 6afce67cc1a..6e108fda634 100644 --- a/mysql-test/r/kill.result +++ b/mysql-test/r/kill.result @@ -24,7 +24,7 @@ SELECT 4; 4 4 KILL (SELECT COUNT(*) FROM mysql.user); -ERROR 42000: This version of MariaDB doesn't yet support 'Usage of subqueries or stored function calls as part of this statement' +ERROR 42000: KILL does not support subqueries or stored functions. SET DEBUG_SYNC= 'thread_end SIGNAL con1_end'; SET DEBUG_SYNC= 'before_do_command_net_read SIGNAL con1_read WAIT_FOR kill'; SET DEBUG_SYNC= 'now WAIT_FOR con1_read'; diff --git a/mysql-test/r/set_statement.result b/mysql-test/r/set_statement.result new file mode 100644 index 00000000000..efe5286147c --- /dev/null +++ b/mysql-test/r/set_statement.result @@ -0,0 +1,1102 @@ +'# SET STATEMENT ..... FOR .... TEST' +DROP TABLE IF EXISTS t1; +DROP FUNCTION IF EXISTS myProc; +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +DROP PROCEDURE IF EXISTS p3; +DROP PROCEDURE IF EXISTS p4; +DROP PROCEDURE IF EXISTS p5; +DROP PROCEDURE IF EXISTS p6; +DROP VIEW IF EXISTS v1; +DROP TABLE IF EXISTS STATEMENT; +SET @save_binlog_format = @@binlog_format; +SET @save_keep_files_on_create = @@keep_files_on_create; +SET @save_max_join_size = @@max_join_size; +SET @save_myisam_repair_threads = @@myisam_repair_threads; +SET @save_myisam_sort_buffer_size = @@myisam_sort_buffer_size; +SET @save_sort_buffer_size = @@sort_buffer_size; +'# Setup database' +CREATE TABLE t1 (v1 INT, v2 INT); +INSERT INTO t1 VALUES (1,2); +INSERT INTO t1 VALUES (3,4); +'' +'#------------------ STATEMENT Test 1 -----------------------#' +'# Initialize variables to known setting' +SET SESSION sort_buffer_size=100000; +'' +'# Pre-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; +Variable_name Value +sort_buffer_size 100000 +SET STATEMENT sort_buffer_size=150000 FOR SELECT *,@@sort_buffer_size FROM t1; +v1 v2 @@sort_buffer_size +1 2 150000 +3 4 150000 +'' +'# Post-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; +Variable_name Value +sort_buffer_size 100000 +'' +'#------------------ STATEMENT Test 2 -----------------------#' +'# Initialize variables to known setting' +SET SESSION binlog_format=mixed; +SET SESSION sort_buffer_size=100000; +'# Pre-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; +Variable_name Value +sort_buffer_size 100000 +SHOW SESSION VARIABLES LIKE 'binlog_format'; +Variable_name Value +binlog_format MIXED +SET STATEMENT sort_buffer_size=150000, binlog_format=row +FOR SELECT *,@@sort_buffer_size,@@binlog_format FROM t1; +v1 v2 @@sort_buffer_size @@binlog_format +1 2 150000 ROW +3 4 150000 ROW +'# Post-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; +Variable_name Value +sort_buffer_size 100000 +SHOW SESSION VARIABLES LIKE 'binlog_format'; +Variable_name Value +binlog_format MIXED +'' +'#------------------ STATEMENT Test 3 -----------------------#' +'# set initial variable value, make prepared statement +SET SESSION binlog_format=row; +PREPARE stmt1 FROM 'SET STATEMENT binlog_format=row FOR SELECT *,@@binlog_format FROM t1'; +'' +'# Change variable setting' +SET SESSION binlog_format=mixed; +'' +'# Pre-STATEMENT variable value' +'' +SHOW SESSION VARIABLES LIKE 'binlog_format'; +Variable_name Value +binlog_format MIXED +'' +EXECUTE stmt1; +v1 v2 @@binlog_format +1 2 ROW +3 4 ROW +'' +'# Post-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'binlog_format'; +Variable_name Value +binlog_format MIXED +'' +DEALLOCATE PREPARE stmt1; +'#------------------ STATEMENT Test 4 -----------------------#' +'# set initial variable value, make prepared statement +SET SESSION myisam_sort_buffer_size=500000, myisam_repair_threads=1; +'' +'# Pre-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size'; +Variable_name Value +myisam_sort_buffer_size 500000 +SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; +Variable_name Value +myisam_repair_threads 1 +'' +SET STATEMENT myisam_sort_buffer_size=800000, +myisam_repair_threads=2 FOR OPTIMIZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +'' +'# Post-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size'; +Variable_name Value +myisam_sort_buffer_size 500000 +SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; +Variable_name Value +myisam_repair_threads 1 +'' +'#------------------ STATEMENT Test 5 -----------------------#' +'# Initialize variables to known setting' +SET SESSION sort_buffer_size=100000; +'' +'# Pre-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; +Variable_name Value +sort_buffer_size 100000 +'' +SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t2; +ERROR 42S02: Table 'test.t2' doesn't exist +'' +'# Post-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; +Variable_name Value +sort_buffer_size 100000 +'' +'#------------------ STATEMENT Test 6 -----------------------#' +'# Initialize variables to known setting' +SET SESSION keep_files_on_create=ON; +'' +'# Pre-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; +Variable_name Value +keep_files_on_create ON +'' +SET STATEMENT keep_files_on_create=OFF FOR SELECT *,@@keep_files_on_create FROM t1; +v1 v2 @@keep_files_on_create +1 2 0 +3 4 0 +'' +'# Post-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; +Variable_name Value +keep_files_on_create ON +'' +'#------------------ STATEMENT Test 7 -----------------------#' +'# Initialize variables to known setting' +SET SESSION max_join_size=2222220000000; +'' +'# Pre-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'max_join_size'; +Variable_name Value +max_join_size 2222220000000 +'' +SET STATEMENT max_join_size=1000000000000 FOR SELECT *,@@max_join_size FROM t1; +v1 v2 @@max_join_size +1 2 1000000000000 +3 4 1000000000000 +'' +'# Post-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'max_join_size'; +Variable_name Value +max_join_size 2222220000000 +'' +'#------------------Test 8-----------------------#' +'# Initialize test variables' +SET SESSION myisam_sort_buffer_size=500000, +myisam_repair_threads=1, +sort_buffer_size = 200000, +max_join_size=2222220000000, +keep_files_on_create=ON; +'' +'# LONG ' +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; +Variable_name Value +sort_buffer_size 200000 +SET STATEMENT sort_buffer_size = 100000 +FOR SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; +Variable_name Value +sort_buffer_size 100000 +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; +Variable_name Value +sort_buffer_size 200000 +'' +'# MY_BOOL ' +SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; +Variable_name Value +keep_files_on_create ON +SET STATEMENT keep_files_on_create=OFF +FOR SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; +Variable_name Value +keep_files_on_create OFF +SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; +Variable_name Value +keep_files_on_create ON +'' +'# INT/LONG ' +SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; +Variable_name Value +myisam_repair_threads 1 +SET STATEMENT myisam_repair_threads=2 +FOR SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; +Variable_name Value +myisam_repair_threads 2 +SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; +Variable_name Value +myisam_repair_threads 1 +'' +'# ULONGLONG ' +SHOW SESSION VARIABLES LIKE 'max_join_size'; +Variable_name Value +max_join_size 2222220000000 +SET STATEMENT max_join_size=2000000000000 +FOR SHOW SESSION VARIABLES LIKE 'max_join_size'; +Variable_name Value +max_join_size 2000000000000 +SHOW SESSION VARIABLES LIKE 'max_join_size'; +Variable_name Value +max_join_size 2222220000000 +'' +'#------------------Test 9-----------------------#' +'# set initial variable values +SET SESSION myisam_sort_buffer_size=500000, +myisam_repair_threads=1, +sort_buffer_size=100000, +binlog_format=mixed, +keep_files_on_create=ON, +max_join_size=2222220000000; +'' +'' +'# Pre-STATEMENT variable value +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +CREATE FUNCTION myProc (cost DECIMAL(10,2)) +RETURNS DECIMAL(10,2) +SQL SECURITY DEFINER +tax: BEGIN +DECLARE order_tax DECIMAL(10,2); +SET order_tax = cost * .05 + @@sort_buffer_size; +RETURN order_tax; +END| +'' +'# During Execution values +SET STATEMENT myisam_sort_buffer_size=400000, +myisam_repair_threads=2, +sort_buffer_size=200000, +binlog_format=row, +keep_files_on_create=OFF, +max_join_size=4444440000000 FOR +SELECT myProc(123.45); +myProc(123.45) +200006.17 +SET STATEMENT myisam_sort_buffer_size=400000, +myisam_repair_threads=2, +sort_buffer_size=300000, +binlog_format=row, +keep_files_on_create=OFF, +max_join_size=4444440000000 FOR +SELECT myProc(123.45); +myProc(123.45) +300006.17 +'' +'# Post-STATEMENT No 1 variable value Pre-STATEMENT for No 2' +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +SET STATEMENT myisam_sort_buffer_size=400000, +myisam_repair_threads=2, +sort_buffer_size=200000, +binlog_format=row, +keep_files_on_create=OFF, +max_join_size=4444440000000 FOR +DROP FUNCTION myProc; +'' +'# Post-STATEMENT No 2 variable value +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'#------------------Test 11-----------------------#' +'# set initial variable values +SET SESSION myisam_sort_buffer_size=500000, +myisam_repair_threads=1, +sort_buffer_size=100000, +binlog_format=mixed, +keep_files_on_create=ON, +max_join_size=2222220000000; +'' +'' +'# Pre-STATEMENT variable value +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +SET STATEMENT myisam_sort_buffer_size=400000, +myisam_repair_threads=2, +sort_buffer_size=200000, +keep_files_on_create=OFF, +max_join_size=4444440000000 FOR +PREPARE stmt1 FROM +'SET STATEMENT binlog_format=row FOR SELECT *,@@binlog_format,@@sort_buffer_size FROM t1'; +'' +'Test No 1 Post Value & Test 2 Pre values' +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +SET STATEMENT myisam_sort_buffer_size=400000, +myisam_repair_threads=2, +sort_buffer_size=200000, +keep_files_on_create=OFF, +max_join_size=4444440000000 FOR +EXECUTE stmt1; +v1 v2 @@binlog_format @@sort_buffer_size +1 2 ROW 200000 +3 4 ROW 200000 +'' +'# Post-STATEMENT No 2 +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +'#------------------Test 12-----------------------#' +'# set initial variable values +SET SESSION myisam_sort_buffer_size=500000, +myisam_repair_threads=1, +sort_buffer_size=100000, +binlog_format=mixed, +keep_files_on_create=ON, +max_join_size=2222220000000; +'' +'' +'# Pre-STATEMENT variable value +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +SET STATEMENT myisam_sort_buffer_size=400000, +myisam_repair_threads=2, +sort_buffer_size=200000, +binlog_format=row, +keep_files_on_create=OFF, +max_join_size=4444440000000 FOR +CREATE PROCEDURE p1() BEGIN +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +END| +'' +'Test No 1 Post Value & Test 2 Pre values' +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +SET STATEMENT myisam_sort_buffer_size=400001, +myisam_repair_threads=3, +sort_buffer_size=200001, +binlog_format=row, +keep_files_on_create=OFF, +max_join_size=4444440000001 FOR +CALL p1(); +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +400001 3 200001 ROW 0 4444440000001 +'' +'# Post-STATEMENT No 2 +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +'#------------------Test 13-----------------------#' +'# set initial variable values +SET SESSION myisam_sort_buffer_size=500000, +myisam_repair_threads=1, +sort_buffer_size=100000, +binlog_format=mixed, +keep_files_on_create=ON, +max_join_size=2222220000000; +'' +'' +CREATE PROCEDURE p2() BEGIN +SET STATEMENT myisam_sort_buffer_size=400000, +myisam_repair_threads=3, +sort_buffer_size=300000, +binlog_format=mixed, +keep_files_on_create=OFF, +max_join_size=3333330000000 FOR +CALL p1(); +END| +'' +'# Pre-STATEMENT variable value +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +SET STATEMENT myisam_sort_buffer_size=400000, +myisam_repair_threads=2, +sort_buffer_size=200000, +binlog_format=row, +keep_files_on_create=OFF, +max_join_size=4444440000000 FOR +CALL p2(); +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +400000 3 300000 MIXED 0 3333330000000 +'' +'# Post-STATEMENT +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +'#------------------Test 14-----------------------#' +'# set initial variable values +SET SESSION myisam_sort_buffer_size=500000, +myisam_repair_threads=1, +sort_buffer_size=100000, +binlog_format=mixed, +keep_files_on_create=ON, +max_join_size=2222220000000; +'' +'' +CREATE PROCEDURE p3() BEGIN +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +SET STATEMENT myisam_sort_buffer_size=320000, +myisam_repair_threads=2, +sort_buffer_size=220022, +binlog_format=row, +keep_files_on_create=ON, +max_join_size=2222220000000 FOR +CALL p2(); +END| +'' +'# Pre-STATEMENT variable value +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +SET STATEMENT myisam_sort_buffer_size=400000, +myisam_repair_threads=2, +sort_buffer_size=200000, +binlog_format=row, +keep_files_on_create=OFF, +max_join_size=4444440000000 FOR +CALL p3(); +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +400000 2 200000 ROW 0 4444440000000 +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +400000 3 300000 MIXED 0 3333330000000 +'' +'# Post-STATEMENT +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +'' +'' +'#------------------Test 15-----------------------#' +'# set initial variable values +SET SESSION myisam_sort_buffer_size=500000, +myisam_repair_threads=1, +sort_buffer_size=100000, +binlog_format=mixed, +keep_files_on_create=ON, +max_join_size=2222220000000; +'' +'' +CREATE PROCEDURE p4() BEGIN +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +SET STATEMENT myisam_sort_buffer_size=320000, +myisam_repair_threads=2, +sort_buffer_size=220022, +binlog_format=row, +keep_files_on_create=ON, +max_join_size=2222220000000 FOR +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +SET STATEMENT myisam_sort_buffer_size=320000, +myisam_repair_threads=2, +sort_buffer_size=220022, +binlog_format=row, +keep_files_on_create=ON, +max_join_size=2222220000000 FOR +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +SET STATEMENT myisam_sort_buffer_size=320000, +myisam_repair_threads=2, +sort_buffer_size=220022, +binlog_format=row, +keep_files_on_create=ON, +max_join_size=2222220000000 FOR +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +END| +'' +'# Pre-STATEMENT variable value +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +SET STATEMENT myisam_sort_buffer_size=400000, +myisam_repair_threads=2, +sort_buffer_size=200000, +binlog_format=row, +keep_files_on_create=OFF, +max_join_size=4444440000000 FOR +CALL p4(); +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +400000 2 200000 ROW 0 4444440000000 +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +320000 2 220022 ROW 1 2222220000000 +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +320000 2 220022 ROW 1 2222220000000 +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +320000 2 220022 ROW 1 2222220000000 +'' +'# Post-STATEMENT +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +'#------------------Test 16-----------------------#' +'' +'# Pre-STATEMENT variable value +SELECT @@sql_mode; +@@sql_mode + +'' +'' +SET STATEMENT sql_mode='ansi' FOR PREPARE stmt FROM 'SELECT "t1".* FROM t1'; +execute stmt; +v1 v2 +1 2 +3 4 +ALTER TABLE t1 ADD COLUMN v3 int; +execute stmt; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.* FROM t1' at line 1 +ALTER TABLE t1 drop COLUMN v3; +deallocate prepare stmt; +'' +'# Post-STATEMENT +SELECT @@sql_mode; +@@sql_mode + +check the same behaviour in normal set +SET sql_mode='ansi'; +PREPARE stmt FROM 'SELECT "t1".* FROM t1'; +SET sql_mode=default; +execute stmt; +v1 v2 +1 2 +3 4 +ALTER TABLE t1 ADD COLUMN v3 int; +execute stmt; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.* FROM t1' at line 1 +ALTER TABLE t1 drop COLUMN v3; +deallocate prepare stmt; +SELECT @@sql_mode; +@@sql_mode + +SET sql_mode='ansi'; +SELECT @@sql_mode; +@@sql_mode +REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI +CREATE PROCEDURE p6() BEGIN +SELECT @@sql_mode; +SELECT "t1".* FROM t1; +END| +SET sql_mode=default; +call p6; +@@sql_mode +REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI +v1 v2 +1 2 +3 4 +ALTER TABLE t1 ADD COLUMN v3 int; +create view v1 as select * from t1; +drop view v1; +call p6; +@@sql_mode +REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI +v1 v2 v3 +1 2 NULL +3 4 NULL +ALTER TABLE t1 drop COLUMN v3; +drop procedure p6; +SELECT @@sql_mode; +@@sql_mode + +# SET and the statement parsed as one unit before the SET takes effect +SET STATEMENT sql_mode='ansi' FOR +CREATE PROCEDURE p6() BEGIN +SELECT @@sql_mode; +SELECT "t1".* FROM t1; +END| +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.* FROM t1; +END' at line 4 +SELECT @@sql_mode; +@@sql_mode + +SET sql_mode='ansi'; +SELECT @@sql_mode; +@@sql_mode +REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI +BEGIN NOT ATOMIC +SELECT @@sql_mode; +SELECT "t1".* FROM t1; +END| +@@sql_mode +REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI +v1 v2 +1 2 +3 4 +SET sql_mode=default; +SELECT @@sql_mode; +@@sql_mode + +# SET and the statement parsed as one unit before the SET takes effect +SET STATEMENT sql_mode='ansi' FOR +BEGIN NOT ATOMIC +SELECT @@sql_mode; +SELECT "t1".* FROM t1; +END| +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.* FROM t1; +END' at line 4 +SET STATEMENT sql_mode='ansi' FOR +BEGIN NOT ATOMIC +SELECT @@sql_mode; +SELECT * FROM t1; +SELECT @@sql_mode; +END| +@@sql_mode +REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI +v1 v2 +1 2 +3 4 +@@sql_mode +REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI +'' +'' +'#------------------Test 17-----------------------#' +'# set initial variable values +SET SESSION myisam_sort_buffer_size=500000, +myisam_repair_threads=1, +sort_buffer_size=100000, +binlog_format=mixed, +keep_files_on_create=ON, +max_join_size=2222220000000; +'' +'# Pre-STATEMENT variable value +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +SET STATEMENT myisam_sort_buffer_size=320000, +myisam_repair_threads=2, +sort_buffer_size=220022, +binlog_format=row, +keep_files_on_create=ON, +max_join_size=2222220000000 +FOR SET SESSION +myisam_sort_buffer_size=260000, +myisam_repair_threads=3, +sort_buffer_size=230013, +binlog_format=row, +keep_files_on_create=ON, +max_join_size=2323230000000; +'' +'# Post-STATEMENT +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +'#------------------Test 18-----------------------#' +'# set initial variable values +SET SESSION myisam_sort_buffer_size=500000, +myisam_repair_threads=1, +sort_buffer_size=100000, +binlog_format=mixed, +keep_files_on_create=ON, +max_join_size=2222220000000; +'' +'# Pre-STATEMENT variable value +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +CREATE PROCEDURE p5() BEGIN +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +SET SESSION +myisam_sort_buffer_size=260000, +myisam_repair_threads=3, +sort_buffer_size=230013, +binlog_format=row, +keep_files_on_create=ON, +max_join_size=2323230000000; +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +END| +'' +'' +SET STATEMENT myisam_sort_buffer_size=400000, +myisam_repair_threads=2, +sort_buffer_size=200000, +binlog_format=row, +keep_files_on_create=OFF, +max_join_size=4444440000000 FOR +CALL p5(); +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +400000 2 200000 ROW 0 4444440000000 +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +260000 3 230013 ROW 1 2323230000000 +'' +'# Post-STATEMENT +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +'#------------------Test 19-----------------------#' +SET STATEMENT max_error_count=100 FOR; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 +SET STATEMENT max_error_count=100 INSERT t1 VALUES (1,2); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT t1 VALUES (1,2)' at line 1 +SET STATEMENT FOR INSERT INTO t1 VALUES (1,2); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FOR INSERT INTO t1 VALUES (1,2)' at line 1 +SET max_error_count=100 FOR INSERT INTO t1 VALUES (1,2); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FOR INSERT INTO t1 VALUES (1,2)' at line 1 +SET STATEMENT GLOBAL max_error_count=100 FOR INSERT INTO t1 VALUES (1,2); +ERROR HY000: Unknown system variable 'GLOBAL' +SET STATEMENT @@global.max_error_count=100 FOR INSERT INTO t1 VALUES (1,2); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@@global.max_error_count=100 FOR INSERT INTO t1 VALUES (1,2)' at line 1 +'' +'' +'#------------------Test 20-----------------------#' +SET STATEMENT connect_timeout=100 FOR INSERT INTO t1 VALUES (1,2); +ERROR HY000: Variable 'connect_timeout' is a GLOBAL variable and should be set with SET GLOBAL +'' +'' +'#------------------Test 21-----------------------#' +SELECT @@myisam_sort_buffer_size, @@sort_buffer_size; +@@myisam_sort_buffer_size @@sort_buffer_size +500000 100000 +SET STATEMENT myisam_sort_buffer_size = 700000, sort_buffer_size = 3000000 +FOR SET STATEMENT myisam_sort_buffer_size=200000 +FOR SELECT @@myisam_sort_buffer_size, @@sort_buffer_size; +@@myisam_sort_buffer_size @@sort_buffer_size +200000 100000 +SELECT @@myisam_sort_buffer_size, @@sort_buffer_size; +@@myisam_sort_buffer_size @@sort_buffer_size +500000 100000 +'' +'' +'#------------------Test 22-----------------------#' +CREATE TABLE STATEMENT(a INT); +DROP TABLE STATEMENT; +'' +'# Cleanup' +DROP TABLE t1; +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP PROCEDURE p3; +DROP PROCEDURE p4; +DROP PROCEDURE p5; +CREATE TABLE t1 (v1 INT, v2 INT); +insert into t1 values (1,1); +CREATE FUNCTION myProc () +RETURNS INT +SQL SECURITY DEFINER +BEGIN +DECLARE mx INT; +SET mx = (select max(v1) from t1); +RETURN mx; +END| +SET STATEMENT myisam_repair_threads=(select max(v1) from t1) FOR +select 1; +ERROR 42000: SET STATEMENT does not support subqueries or stored functions. +SET STATEMENT myisam_repair_threads=myProc() FOR +select 1; +ERROR 42000: SET STATEMENT does not support subqueries or stored functions. +drop function myProc; +drop table t1; +set session binlog_format=mixed; +PREPARE stmt1 FROM 'SELECT @@binlog_format'; +execute stmt1; +@@binlog_format +MIXED +set statement binlog_format=row for execute stmt1; +@@binlog_format +ROW +execute stmt1; +@@binlog_format +MIXED +deallocate prepare stmt1; +set statement binlog_format=row for PREPARE stmt1 FROM 'SELECT @@binlog_format'; +execute stmt1; +@@binlog_format +MIXED +execute stmt1; +@@binlog_format +MIXED +deallocate prepare stmt1; +PREPARE stmt1 FROM 'set statement binlog_format=row for SELECT @@binlog_format'; +execute stmt1; +@@binlog_format +ROW +execute stmt1; +@@binlog_format +ROW +deallocate prepare stmt1; +set session binlog_format=default; +set session binlog_format=mixed; +SET STATEMENT sort_buffer_size=150000 FOR set session binlog_format=row; +SELECT @@binlog_format; +@@binlog_format +ROW +SET @@binlog_format = @save_binlog_format; +SET @@keep_files_on_create = @save_keep_files_on_create; +SET @@max_join_size = @save_max_join_size; +SET @@myisam_repair_threads = @save_myisam_repair_threads; +SET @@myisam_sort_buffer_size = @save_myisam_sort_buffer_size; +SET @@sort_buffer_size = @save_sort_buffer_size; +#Correctly set timestamp +set session timestamp=4646464; +select @@timestamp != 4646464; +@@timestamp != 4646464 +0 +select @@timestamp != 4646464; +@@timestamp != 4646464 +0 +#Correctly returned normal behaviour +set session timestamp=default; +select @@timestamp != 4646464; +@@timestamp != 4646464 +1 +select @@timestamp != 4646464; +@@timestamp != 4646464 +1 +#here timestamp should be set only for the statement then restored default +set statement timestamp=4646464 for select @@timestamp; +@@timestamp +4646464.000000 +set @save_tm=@@timestamp; +select @@timestamp != 4646464; +@@timestamp != 4646464 +1 +select @@timestamp != 4646464; +@@timestamp != 4646464 +1 +select @@timestamp != @save_tm; +@@timestamp != @save_tm +1 +set @save_dfs=@@default_storage_engine; +SET @@default_storage_engine=MyISAM; +SET STATEMENT default_storage_engine=MEMORY for CREATE TABLE t1 (a int); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MEMORY DEFAULT CHARSET=latin1 +select @@default_storage_engine; +@@default_storage_engine +MyISAM +drop table t1; +SET STATEMENT default_storage_engine=MyISAM for CREATE TABLE t1 (a int); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +SET @@default_storage_engine=@save_dfs; +SELECT @@MAX_STATEMENT_TIME; +@@MAX_STATEMENT_TIME +0.000000 +SET STATEMENT MAX_STATEMENT_TIME=2 FOR SELECT SLEEP(1); +SLEEP(1) +0 +SHOW STATUS LIKE "max_statement_time_exceeded"; +Variable_name Value +Max_statement_time_exceeded 0 +SET STATEMENT MAX_STATEMENT_TIME=2 FOR SELECT SLEEP(3); +SLEEP(3) +1 +SHOW STATUS LIKE "max_statement_time_exceeded"; +Variable_name Value +Max_statement_time_exceeded 1 +SELECT @@MAX_STATEMENT_TIME; +@@MAX_STATEMENT_TIME +0.000000 +SET STATEMENT keycache1.key_buffer_size=1024 FOR SELECT 1; +ERROR HY000: Variable 'key_buffer_size' is a GLOBAL variable and should be set with SET GLOBAL +set @save_general_log=@@global.general_log; +# SET STATEMENT works (OK) +set statement lock_wait_timeout=1 for select @@lock_wait_timeout; +@@lock_wait_timeout +1 +# Setting a totally unrelated global variable +set global general_log=0; +# SET STATEMENT should work +set statement lock_wait_timeout=1 for select @@lock_wait_timeout; +@@lock_wait_timeout +1 +set global general_log=@save_general_log; +set statement autocommit=default for select 1; +ERROR 42000: The system variable autocommit cannot be set in SET STATEMENT. +set statement gtid_domain_id=default for select 1; +ERROR 42000: The system variable gtid_domain_id cannot be set in SET STATEMENT. +set statement tx_isolation=default for select 1; +ERROR 42000: The system variable tx_isolation cannot be set in SET STATEMENT. +set statement rand_seed1=default for select 1; +ERROR 42000: The system variable rand_seed1 cannot be set in SET STATEMENT. +set statement rand_seed2=default for select 1; +ERROR 42000: The system variable rand_seed2 cannot be set in SET STATEMENT. +set statement skip_replication=default for select 1; +ERROR 42000: The system variable skip_replication cannot be set in SET STATEMENT. +set statement default_master_connection=default for select 1; +ERROR 42000: The system variable default_master_connection cannot be set in SET STATEMENT. +set statement default_master_connection=default for select 1; +ERROR 42000: The system variable default_master_connection cannot be set in SET STATEMENT. +set statement last_insert_id=1 for select 1; +ERROR 42000: The system variable last_insert_id cannot be set in SET STATEMENT. +set statement sql_log_off=default for select 1; +ERROR 42000: The system variable sql_log_off cannot be set in SET STATEMENT. +set statement long_query_time=default for select 1; +ERROR 42000: The system variable long_query_time cannot be set in SET STATEMENT. +set statement log_slow_filter=default for select 1; +ERROR 42000: The system variable log_slow_filter cannot be set in SET STATEMENT. +set statement log_slow_verbosity=default for select 1; +ERROR 42000: The system variable log_slow_verbosity cannot be set in SET STATEMENT. +set statement log_slow_rate_limit=default for select 1; +ERROR 42000: The system variable log_slow_rate_limit cannot be set in SET STATEMENT. +set statement slow_query_log=default for select 1; +ERROR 42000: The system variable slow_query_log cannot be set in SET STATEMENT. +set statement min_examined_row_limit=default for select 1; +ERROR 42000: The system variable min_examined_row_limit cannot be set in SET STATEMENT. +set statement character_set_client=default for select 1; +ERROR 42000: The system variable character_set_client cannot be set in SET STATEMENT. +set statement character_set_connection=default for select 1; +ERROR 42000: The system variable character_set_connection cannot be set in SET STATEMENT. +set statement character_set_filesystem=default for select 1; +ERROR 42000: The system variable character_set_filesystem cannot be set in SET STATEMENT. +set statement collation_connection=default for select 1; +ERROR 42000: The system variable collation_connection cannot be set in SET STATEMENT. +set statement old_passwords=default for select 1; +ERROR 42000: The system variable old_passwords cannot be set in SET STATEMENT. +set statement query_cache_type=default for select 1; +ERROR 42000: The system variable query_cache_type cannot be set in SET STATEMENT. +set statement wait_timeout=default for select 1; +ERROR 42000: The system variable wait_timeout cannot be set in SET STATEMENT. +set statement interactive_timeout=default for select 1; +ERROR 42000: The system variable interactive_timeout cannot be set in SET STATEMENT. diff --git a/mysql-test/r/set_statement_debug.result b/mysql-test/r/set_statement_debug.result new file mode 100644 index 00000000000..bf873a6e824 --- /dev/null +++ b/mysql-test/r/set_statement_debug.result @@ -0,0 +1,22 @@ +set @save_debug_dbug= @@debug_dbug; +set statement debug_dbug="d,something" for select @@debug_dbug; +@@debug_dbug +d,something +set @@debug_dbug= @save_debug_dbug; +CREATE TABLE t1(f0 int auto_increment primary key, f1 int); +INSERT INTO t1(f1) VALUES (0),(1),(2),(3),(4),(5); +CALL mtr.add_suppression("Out of sort memory"); +SET statement debug_dbug= '+d,alloc_sort_buffer_fail' for SELECT * FROM t1 ORDER BY f1 ASC, f0; +ERROR HY001: Out of sort memory, consider increasing server sort buffer size +SELECT * FROM t1 ORDER BY f1 ASC, f0; +f0 f1 +1 0 +2 1 +3 2 +4 3 +5 4 +6 5 +DROP TABLE t1; +set @@debug_dbug= @save_debug_dbug; +set statement DEBUG_SYNC = 'now SIGNAL hi' for select 1; +ERROR 42000: The system variable debug_sync cannot be set in SET STATEMENT. diff --git a/mysql-test/r/set_statement_profiling.result b/mysql-test/r/set_statement_profiling.result new file mode 100644 index 00000000000..1f17c345680 --- /dev/null +++ b/mysql-test/r/set_statement_profiling.result @@ -0,0 +1,4 @@ +set statement profiling=default for select 1; +ERROR 42000: The system variable profiling cannot be set in SET STATEMENT. +set statement profiling_history_size=default for select 1; +ERROR 42000: The system variable profiling_history_size cannot be set in SET STATEMENT. diff --git a/mysql-test/suite/rpl/include/rpl_set_statement.inc b/mysql-test/suite/rpl/include/rpl_set_statement.inc new file mode 100644 index 00000000000..6ab2d4e66c6 --- /dev/null +++ b/mysql-test/suite/rpl/include/rpl_set_statement.inc @@ -0,0 +1,31 @@ +#Check if the variable is replicated correctly with "SET STATEMENT" +# Usage: +# $rpl_ssvt_var_name - the name of tested variable; +# $rpl_ssvt_var_value - the value to set; +# $rpl_ssvt_table - the table name to insert values. + +--connection master +--echo [connection master] +eval SELECT @@$rpl_ssvt_var_name; + +--connection slave +--echo [connection slave] +eval SELECT @@$rpl_ssvt_var_name; + +--connection master +--echo [connection master] +--disable_result_log +eval SET STATEMENT $rpl_ssvt_var_name=$rpl_ssvt_var_value FOR + INSERT INTO $rpl_ssvt_table VALUES(@@$rpl_ssvt_var_name); +--enable_result_log +eval SELECT @@$rpl_ssvt_var_name; + +--sync_slave_with_master +--echo [connection slave] +eval SELECT * FROM $rpl_ssvt_table; +eval SELECT @@$rpl_ssvt_var_name; + +--connection master +--echo [connection master] +eval DELETE FROM $rpl_ssvt_table; + diff --git a/mysql-test/suite/rpl/r/rpl_set_statement.result b/mysql-test/suite/rpl/r/rpl_set_statement.result new file mode 100644 index 00000000000..78d34bd0853 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_set_statement.result @@ -0,0 +1,140 @@ +include/master-slave.inc +[connection master] +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +call mtr.add_suppression("Unsafe statement written to the binary log*"); +CREATE TABLE t1 (a bigint unsigned not null); +CREATE TABLE t2 (a char(255) not null); + +There are the following types of variables: +1) variables that are NOT replicated correctly when using STATEMENT mode; + +[connection master] +SELECT @@max_join_size; +@@max_join_size +18446744073709551615 +[connection slave] +SELECT @@max_join_size; +@@max_join_size +18446744073709551615 +[connection master] +SET STATEMENT max_join_size=2 FOR +INSERT INTO t1 VALUES(@@max_join_size); +SELECT @@max_join_size; +@@max_join_size +18446744073709551615 +[connection slave] +SELECT * FROM t1; +a +18446744073709551615 +SELECT @@max_join_size; +@@max_join_size +18446744073709551615 +[connection master] +DELETE FROM t1; + +2) variables thar ARE replicated correctly +They must be replicated correctly with "SET STATEMENT" too. + +[connection master] +SELECT @@auto_increment_increment; +@@auto_increment_increment +1 +[connection slave] +SELECT @@auto_increment_increment; +@@auto_increment_increment +1 +[connection master] +SET STATEMENT auto_increment_increment=10 FOR +INSERT INTO t1 VALUES(@@auto_increment_increment); +SELECT @@auto_increment_increment; +@@auto_increment_increment +1 +[connection slave] +SELECT * FROM t1; +a +10 +SELECT @@auto_increment_increment; +@@auto_increment_increment +1 +[connection master] +DELETE FROM t1; + +3) sql_mode which is replicated correctly exept NO_DIR_IN_CREATE value; + +[connection master] +SELECT @@sql_mode; +@@sql_mode + +[connection slave] +SELECT @@sql_mode; +@@sql_mode + +[connection master] +SET STATEMENT sql_mode='ERROR_FOR_DIVISION_BY_ZERO' FOR +INSERT INTO t2 VALUES(@@sql_mode); +SELECT @@sql_mode; +@@sql_mode + +[connection slave] +SELECT * FROM t2; +a +ERROR_FOR_DIVISION_BY_ZERO +SELECT @@sql_mode; +@@sql_mode + +[connection master] +DELETE FROM t2; +[connection master] +SELECT @@sql_mode; +@@sql_mode + +[connection slave] +SELECT @@sql_mode; +@@sql_mode + +[connection master] +SET STATEMENT sql_mode='NO_DIR_IN_CREATE' FOR +INSERT INTO t2 VALUES(@@sql_mode); +SELECT @@sql_mode; +@@sql_mode + +[connection slave] +SELECT * FROM t2; +a + +SELECT @@sql_mode; +@@sql_mode + +[connection master] +DELETE FROM t2; + +4) variables that are not replicated at all: +default_storage_engine, storage_engine, max_heap_table_size + +[connection master] +SELECT @@max_heap_table_size; +@@max_heap_table_size +1048576 +[connection slave] +SELECT @@max_heap_table_size; +@@max_heap_table_size +1048576 +[connection master] +SET STATEMENT max_heap_table_size=16384 FOR +INSERT INTO t1 VALUES(@@max_heap_table_size); +SELECT @@max_heap_table_size; +@@max_heap_table_size +1048576 +[connection slave] +SELECT * FROM t1; +a +1048576 +SELECT @@max_heap_table_size; +@@max_heap_table_size +1048576 +[connection master] +DELETE FROM t1; +DROP TABLE t1; +DROP TABLE t2; +include/stop_slave.inc diff --git a/mysql-test/suite/rpl/t/rpl_set_statement.test b/mysql-test/suite/rpl/t/rpl_set_statement.test new file mode 100644 index 00000000000..8a3087bec6d --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_set_statement.test @@ -0,0 +1,57 @@ +--source include/master-slave.inc +--source include/have_binlog_format_statement.inc + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +--enable_warnings + +call mtr.add_suppression("Unsafe statement written to the binary log*"); +CREATE TABLE t1 (a bigint unsigned not null); +CREATE TABLE t2 (a char(255) not null); + +--echo +--echo There are the following types of variables: +--echo 1) variables that are NOT replicated correctly when using STATEMENT mode; +--echo + +--let $rpl_ssvt_var_name=max_join_size +--let $rpl_ssvt_var_value=2 +--let $rpl_ssvt_table=t1 +--source suite/rpl/include/rpl_set_statement.inc + +--echo +--echo 2) variables thar ARE replicated correctly +--echo They must be replicated correctly with "SET STATEMENT" too. +--echo +--let $rpl_ssvt_var_name=auto_increment_increment +--let $rpl_ssvt_var_value=10 +--let $rpl_ssvt_table=t1 +--source suite/rpl/include/rpl_set_statement.inc + +--echo +--echo 3) sql_mode which is replicated correctly exept NO_DIR_IN_CREATE value; +--echo +--let $rpl_ssvt_var_name=sql_mode +--let $rpl_ssvt_var_value='ERROR_FOR_DIVISION_BY_ZERO' +--let $rpl_ssvt_table=t2 +--source suite/rpl/include/rpl_set_statement.inc +--let $rpl_ssvt_var_name=sql_mode +--let $rpl_ssvt_var_value='NO_DIR_IN_CREATE' +--let $rpl_ssvt_table=t2 +--source suite/rpl/include/rpl_set_statement.inc + +--echo +--echo 4) variables that are not replicated at all: +--echo default_storage_engine, storage_engine, max_heap_table_size +--echo +--let $rpl_ssvt_var_name=max_heap_table_size +--let $rpl_ssvt_var_value=16384 +--let $rpl_ssvt_table=t1 +--source suite/rpl/include/rpl_set_statement.inc + +connection master; +DROP TABLE t1; +DROP TABLE t2; +sync_slave_with_master; +source include/stop_slave.inc; diff --git a/mysql-test/t/events_bugs.test b/mysql-test/t/events_bugs.test index dc31556998a..1e93917b08e 100644 --- a/mysql-test/t/events_bugs.test +++ b/mysql-test/t/events_bugs.test @@ -508,13 +508,13 @@ drop database mysqltest_db1; # # START - BUG#16394: Events: Crash if schedule contains SELECT # ---error ER_NOT_SUPPORTED_YET +--error ER_SUBQUERIES_NOT_SUPPORTED create event e_53 on schedule at (select s1 from ttx) do drop table t; ---error ER_NOT_SUPPORTED_YET +--error ER_SUBQUERIES_NOT_SUPPORTED create event e_53 on schedule every (select s1 from ttx) second do drop table t; ---error ER_NOT_SUPPORTED_YET +--error ER_SUBQUERIES_NOT_SUPPORTED create event e_53 on schedule every 5 second starts (select s1 from ttx) do drop table t; ---error ER_NOT_SUPPORTED_YET +--error ER_SUBQUERIES_NOT_SUPPORTED create event e_53 on schedule every 5 second ends (select s1 from ttx) do drop table t; # # END - BUG#16394: Events: Crash if schedule contains SELECT @@ -570,7 +570,7 @@ begin select release_lock('ee_22830'); end| ---error ER_NOT_SUPPORTED_YET +--error ER_SUBQUERIES_NOT_SUPPORTED create event e22830 on schedule every f22830() second do begin call p22830_wait(); diff --git a/mysql-test/t/kill.test b/mysql-test/t/kill.test index 06933bcb0b9..6e97f3bbc33 100644 --- a/mysql-test/t/kill.test +++ b/mysql-test/t/kill.test @@ -70,7 +70,7 @@ connection con2; SELECT 4; connection default; ---error ER_NOT_SUPPORTED_YET +--error ER_SUBQUERIES_NOT_SUPPORTED KILL (SELECT COUNT(*) FROM mysql.user); connection con1; diff --git a/mysql-test/t/set_statement.test b/mysql-test/t/set_statement.test new file mode 100644 index 00000000000..a65807192b5 --- /dev/null +++ b/mysql-test/t/set_statement.test @@ -0,0 +1,1049 @@ +--echo '# SET STATEMENT ..... FOR .... TEST' +############################ STATEMENT_SET ############################# +# # +# Testing working functionality of SET STATEMENT # +# # +# # +# There is important documentation within # +# # +# # +# Author: Joe Lukas # +# Creation: # +# 2009-08-02 Implement this test as part of # +# WL#681 Per query variable settings # +# # +######################################################################## + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP FUNCTION IF EXISTS myProc; +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +DROP PROCEDURE IF EXISTS p3; +DROP PROCEDURE IF EXISTS p4; +DROP PROCEDURE IF EXISTS p5; +DROP PROCEDURE IF EXISTS p6; +DROP VIEW IF EXISTS v1; +DROP TABLE IF EXISTS STATEMENT; +--enable_warnings +SET @save_binlog_format = @@binlog_format; +SET @save_keep_files_on_create = @@keep_files_on_create; +SET @save_max_join_size = @@max_join_size; +SET @save_myisam_repair_threads = @@myisam_repair_threads; +SET @save_myisam_sort_buffer_size = @@myisam_sort_buffer_size; +SET @save_sort_buffer_size = @@sort_buffer_size; +#################################################################### +#Set up current database +#################################################################### +--echo '# Setup database' +CREATE TABLE t1 (v1 INT, v2 INT); +INSERT INTO t1 VALUES (1,2); +INSERT INTO t1 VALUES (3,4); +--echo '' +--echo '#------------------ STATEMENT Test 1 -----------------------#' +#################################################################### +# Checks with variable value type ulong # +#################################################################### +--echo '# Initialize variables to known setting' +SET SESSION sort_buffer_size=100000; +--echo '' +--echo '# Pre-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; +SET STATEMENT sort_buffer_size=150000 FOR SELECT *,@@sort_buffer_size FROM t1; +--echo '' +--echo '# Post-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; +--echo '' +--echo '#------------------ STATEMENT Test 2 -----------------------#' +#################################################################### +# Checks for multiple set values inside STATEMENT ... FOR # +#################################################################### +--echo '# Initialize variables to known setting' +SET SESSION binlog_format=mixed; +SET SESSION sort_buffer_size=100000; +--echo '# Pre-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; +SHOW SESSION VARIABLES LIKE 'binlog_format'; +SET STATEMENT sort_buffer_size=150000, binlog_format=row + FOR SELECT *,@@sort_buffer_size,@@binlog_format FROM t1; +--echo '# Post-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; +SHOW SESSION VARIABLES LIKE 'binlog_format'; + +--echo '' +--echo '#------------------ STATEMENT Test 3 -----------------------#' +#################################################################### +# Check current variable value is stored in using stored # +# statements. # +#################################################################### +--echo '# set initial variable value, make prepared statement +SET SESSION binlog_format=row; +PREPARE stmt1 FROM 'SET STATEMENT binlog_format=row FOR SELECT *,@@binlog_format FROM t1'; +--echo '' +--echo '# Change variable setting' +SET SESSION binlog_format=mixed; +--echo '' +--echo '# Pre-STATEMENT variable value' +--echo '' +SHOW SESSION VARIABLES LIKE 'binlog_format'; +--echo '' +EXECUTE stmt1; +--echo '' +--echo '# Post-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'binlog_format'; + +--echo '' +DEALLOCATE PREPARE stmt1; +--echo '#------------------ STATEMENT Test 4 -----------------------#' +#################################################################### +# Check works with OPTIMIZE TABLE command # +# Checks works with a variable value of type INT # +# Checks works with variable type ULONGLONG # +#################################################################### +--echo '# set initial variable value, make prepared statement +SET SESSION myisam_sort_buffer_size=500000, myisam_repair_threads=1; +--echo '' +--echo '# Pre-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size'; +SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; +--echo '' +SET STATEMENT myisam_sort_buffer_size=800000, + myisam_repair_threads=2 FOR OPTIMIZE TABLE t1; +--echo '' +--echo '# Post-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size'; +SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; + +--echo '' +--echo '#------------------ STATEMENT Test 5 -----------------------#' +#################################################################### +# Checks if variable reset after error in statement after FOR # +#################################################################### +--echo '# Initialize variables to known setting' +SET SESSION sort_buffer_size=100000; +--echo '' +--echo '# Pre-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; +--echo '' +--error ER_NO_SUCH_TABLE +SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t2; +--echo '' +--echo '# Post-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; + +--echo '' +--echo '#------------------ STATEMENT Test 6 -----------------------#' +#################################################################### +# Checks works with variable type MY_BOOL # +#################################################################### +--echo '# Initialize variables to known setting' +SET SESSION keep_files_on_create=ON; +--echo '' +--echo '# Pre-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; +--echo '' +SET STATEMENT keep_files_on_create=OFF FOR SELECT *,@@keep_files_on_create FROM t1; +--echo '' +--echo '# Post-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; + +--echo '' +--echo '#------------------ STATEMENT Test 7 -----------------------#' +#################################################################### +# Checks works with variable type HA_ROWS # +#################################################################### +--echo '# Initialize variables to known setting' +SET SESSION max_join_size=2222220000000; +--echo '' +--echo '# Pre-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'max_join_size'; +--echo '' +SET STATEMENT max_join_size=1000000000000 FOR SELECT *,@@max_join_size FROM t1; +--echo '' +--echo '# Post-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'max_join_size'; + +--echo '' +--echo '#------------------Test 8-----------------------#' +#################################################################### +# Ensure variable of each type is set to proper value during # +# statement after FOR execution # +#################################################################### +--echo '# Initialize test variables' +SET SESSION myisam_sort_buffer_size=500000, + myisam_repair_threads=1, + sort_buffer_size = 200000, + max_join_size=2222220000000, + keep_files_on_create=ON; + +--echo '' +--echo '# LONG ' +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; +SET STATEMENT sort_buffer_size = 100000 + FOR SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; +--echo '' +--echo '# MY_BOOL ' +SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; +SET STATEMENT keep_files_on_create=OFF + FOR SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; +SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; + +--echo '' +--echo '# INT/LONG ' +SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; +SET STATEMENT myisam_repair_threads=2 + FOR SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; +SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; +--echo '' +--echo '# ULONGLONG ' +SHOW SESSION VARIABLES LIKE 'max_join_size'; +SET STATEMENT max_join_size=2000000000000 + FOR SHOW SESSION VARIABLES LIKE 'max_join_size'; +SHOW SESSION VARIABLES LIKE 'max_join_size'; + +--echo '' +--echo '#------------------Test 9-----------------------#' +#################################################################### +# No 1 - Check works with CREATE ... BEGIN ... END command # +# Display variables during execution # +# No 2 - Test with DROP command # +#################################################################### +--echo '# set initial variable values +SET SESSION myisam_sort_buffer_size=500000, + myisam_repair_threads=1, + sort_buffer_size=100000, + binlog_format=mixed, + keep_files_on_create=ON, + max_join_size=2222220000000; +--echo '' +--echo '' +--echo '# Pre-STATEMENT variable value +SELECT @@myisam_sort_buffer_size, + @@myisam_repair_threads, + @@sort_buffer_size, + @@binlog_format, + @@keep_files_on_create, + @@max_join_size; +--echo '' +--echo '' +DELIMITER |; +CREATE FUNCTION myProc (cost DECIMAL(10,2)) + RETURNS DECIMAL(10,2) + + SQL SECURITY DEFINER + + tax: BEGIN + DECLARE order_tax DECIMAL(10,2); + SET order_tax = cost * .05 + @@sort_buffer_size; + RETURN order_tax; + END| +DELIMITER ;| +--echo '' +--echo '# During Execution values +SET STATEMENT myisam_sort_buffer_size=400000, + myisam_repair_threads=2, + sort_buffer_size=200000, + binlog_format=row, + keep_files_on_create=OFF, + max_join_size=4444440000000 FOR + SELECT myProc(123.45); +SET STATEMENT myisam_sort_buffer_size=400000, + myisam_repair_threads=2, + sort_buffer_size=300000, + binlog_format=row, + keep_files_on_create=OFF, + max_join_size=4444440000000 FOR + SELECT myProc(123.45); +--echo '' +--echo '# Post-STATEMENT No 1 variable value Pre-STATEMENT for No 2' +SELECT @@myisam_sort_buffer_size, + @@myisam_repair_threads, + @@sort_buffer_size, + @@binlog_format, + @@keep_files_on_create, + @@max_join_size; +--echo '' +SET STATEMENT myisam_sort_buffer_size=400000, + myisam_repair_threads=2, + sort_buffer_size=200000, + binlog_format=row, + keep_files_on_create=OFF, + max_join_size=4444440000000 FOR + DROP FUNCTION myProc; +--echo '' +--echo '# Post-STATEMENT No 2 variable value +SELECT @@myisam_sort_buffer_size, + @@myisam_repair_threads, + @@sort_buffer_size, + @@binlog_format, + @@keep_files_on_create, + @@max_join_size; + +--echo '' +--echo '#------------------Test 11-----------------------#' +#################################################################### +# No 1 - Check with PREPARE statement # +# check with different variable on inside PREPARE # +# No 2 - Check with EXECUTE statement # +#################################################################### +--echo '# set initial variable values +SET SESSION myisam_sort_buffer_size=500000, + myisam_repair_threads=1, + sort_buffer_size=100000, + binlog_format=mixed, + keep_files_on_create=ON, + max_join_size=2222220000000; +--echo '' +--echo '' +--echo '# Pre-STATEMENT variable value +SELECT @@myisam_sort_buffer_size, + @@myisam_repair_threads, + @@sort_buffer_size, + @@binlog_format, + @@keep_files_on_create, + @@max_join_size; +--echo '' +--echo '' +SET STATEMENT myisam_sort_buffer_size=400000, + myisam_repair_threads=2, + sort_buffer_size=200000, + keep_files_on_create=OFF, + max_join_size=4444440000000 FOR + PREPARE stmt1 FROM + 'SET STATEMENT binlog_format=row FOR SELECT *,@@binlog_format,@@sort_buffer_size FROM t1'; +--echo '' +--echo 'Test No 1 Post Value & Test 2 Pre values' +SELECT @@myisam_sort_buffer_size, + @@myisam_repair_threads, + @@sort_buffer_size, + @@binlog_format, + @@keep_files_on_create, + @@max_join_size; +--echo '' +--echo '' +SET STATEMENT myisam_sort_buffer_size=400000, + myisam_repair_threads=2, + sort_buffer_size=200000, + keep_files_on_create=OFF, + max_join_size=4444440000000 FOR + EXECUTE stmt1; +--echo '' +--echo '# Post-STATEMENT No 2 +SELECT @@myisam_sort_buffer_size, + @@myisam_repair_threads, + @@sort_buffer_size, + @@binlog_format, + @@keep_files_on_create, + @@max_join_size; +--echo '' +--echo '' +--echo '#------------------Test 12-----------------------#' +#################################################################### +# No 1 - Check with PROCEDURE (show variables in procedure) # +# No 2 - Check with CALL statement show variables in PROCEDURE # +#################################################################### +--echo '# set initial variable values +SET SESSION myisam_sort_buffer_size=500000, + myisam_repair_threads=1, + sort_buffer_size=100000, + binlog_format=mixed, + keep_files_on_create=ON, + max_join_size=2222220000000; +--echo '' +--echo '' +--echo '# Pre-STATEMENT variable value +SELECT @@myisam_sort_buffer_size, + @@myisam_repair_threads, + @@sort_buffer_size, + @@binlog_format, + @@keep_files_on_create, + @@max_join_size; +--echo '' +--echo '' +DELIMITER |; +SET STATEMENT myisam_sort_buffer_size=400000, + myisam_repair_threads=2, + sort_buffer_size=200000, + binlog_format=row, + keep_files_on_create=OFF, + max_join_size=4444440000000 FOR + CREATE PROCEDURE p1() BEGIN + SELECT @@myisam_sort_buffer_size, + @@myisam_repair_threads, + @@sort_buffer_size, + @@binlog_format, + @@keep_files_on_create, + @@max_join_size; + END| +DELIMITER ;| +--echo '' +--echo 'Test No 1 Post Value & Test 2 Pre values' +SELECT @@myisam_sort_buffer_size, + @@myisam_repair_threads, + @@sort_buffer_size, + @@binlog_format, + @@keep_files_on_create, + @@max_join_size; +--echo '' +--echo '' +SET STATEMENT myisam_sort_buffer_size=400001, + myisam_repair_threads=3, + sort_buffer_size=200001, + binlog_format=row, + keep_files_on_create=OFF, + max_join_size=4444440000001 FOR + CALL p1(); +--echo '' +--echo '# Post-STATEMENT No 2 +SELECT @@myisam_sort_buffer_size, + @@myisam_repair_threads, + @@sort_buffer_size, + @@binlog_format, + @@keep_files_on_create, + @@max_join_size; +--echo '' +--echo '' + +--echo '#------------------Test 13-----------------------#' +#################################################################### +# Check PROCEDURE containing SET STATEMENT FOR # +# p1() from test 12 will be used to display variables # +#################################################################### +--echo '# set initial variable values +SET SESSION myisam_sort_buffer_size=500000, + myisam_repair_threads=1, + sort_buffer_size=100000, + binlog_format=mixed, + keep_files_on_create=ON, + max_join_size=2222220000000; +--echo '' +--echo '' +DELIMITER |; +CREATE PROCEDURE p2() BEGIN + SET STATEMENT myisam_sort_buffer_size=400000, + myisam_repair_threads=3, + sort_buffer_size=300000, + binlog_format=mixed, + keep_files_on_create=OFF, + max_join_size=3333330000000 FOR + CALL p1(); + END| +DELIMITER ;| +--echo '' +--echo '# Pre-STATEMENT variable value +SELECT @@myisam_sort_buffer_size, + @@myisam_repair_threads, + @@sort_buffer_size, + @@binlog_format, + @@keep_files_on_create, + @@max_join_size; +--echo '' +--echo '' +SET STATEMENT myisam_sort_buffer_size=400000, + myisam_repair_threads=2, + sort_buffer_size=200000, + binlog_format=row, + keep_files_on_create=OFF, + max_join_size=4444440000000 FOR + CALL p2(); +--echo '' +--echo '# Post-STATEMENT +SELECT @@myisam_sort_buffer_size, + @@myisam_repair_threads, + @@sort_buffer_size, + @@binlog_format, + @@keep_files_on_create, + @@max_join_size; +--echo '' +--echo '' +--echo '#------------------Test 14-----------------------#' +#################################################################### +# Check PROCEDURE containing compound SET STATEMENT FOR # +# p2() will be used as compounding statement from test 13 # +#################################################################### +--echo '# set initial variable values +SET SESSION myisam_sort_buffer_size=500000, + myisam_repair_threads=1, + sort_buffer_size=100000, + binlog_format=mixed, + keep_files_on_create=ON, + max_join_size=2222220000000; +--echo '' +--echo '' +DELIMITER |; +CREATE PROCEDURE p3() BEGIN + SELECT @@myisam_sort_buffer_size, + @@myisam_repair_threads, + @@sort_buffer_size, + @@binlog_format, + @@keep_files_on_create, + @@max_join_size; + SET STATEMENT myisam_sort_buffer_size=320000, + myisam_repair_threads=2, + sort_buffer_size=220022, + binlog_format=row, + keep_files_on_create=ON, + max_join_size=2222220000000 FOR + CALL p2(); + END| +DELIMITER ;| +--echo '' +--echo '# Pre-STATEMENT variable value +SELECT @@myisam_sort_buffer_size, + @@myisam_repair_threads, + @@sort_buffer_size, + @@binlog_format, + @@keep_files_on_create, + @@max_join_size; +--echo '' +--echo '' +SET STATEMENT myisam_sort_buffer_size=400000, + myisam_repair_threads=2, + sort_buffer_size=200000, + binlog_format=row, + keep_files_on_create=OFF, + max_join_size=4444440000000 FOR + CALL p3(); +--echo '' +--echo '# Post-STATEMENT +SELECT @@myisam_sort_buffer_size, + @@myisam_repair_threads, + @@sort_buffer_size, + @@binlog_format, + @@keep_files_on_create, + @@max_join_size; +--echo '' +--echo '' + + --echo '' +--echo '' +--echo '#------------------Test 15-----------------------#' +#################################################################### +# Check PROCEDURE containing compound SET STATEMENT FOR # +# call multiple SET STATEMENT .. FOR showing SELECT # +#################################################################### +--echo '# set initial variable values +SET SESSION myisam_sort_buffer_size=500000, + myisam_repair_threads=1, + sort_buffer_size=100000, + binlog_format=mixed, + keep_files_on_create=ON, + max_join_size=2222220000000; +--echo '' +--echo '' +DELIMITER |; +CREATE PROCEDURE p4() BEGIN + SELECT @@myisam_sort_buffer_size, + @@myisam_repair_threads, + @@sort_buffer_size, + @@binlog_format, + @@keep_files_on_create, + @@max_join_size; + SET STATEMENT myisam_sort_buffer_size=320000, + myisam_repair_threads=2, + sort_buffer_size=220022, + binlog_format=row, + keep_files_on_create=ON, + max_join_size=2222220000000 FOR + SELECT @@myisam_sort_buffer_size, + @@myisam_repair_threads, + @@sort_buffer_size, + @@binlog_format, + @@keep_files_on_create, + @@max_join_size; + SET STATEMENT myisam_sort_buffer_size=320000, + myisam_repair_threads=2, + sort_buffer_size=220022, + binlog_format=row, + keep_files_on_create=ON, + max_join_size=2222220000000 FOR + SELECT @@myisam_sort_buffer_size, + @@myisam_repair_threads, + @@sort_buffer_size, + @@binlog_format, + @@keep_files_on_create, + @@max_join_size; + SET STATEMENT myisam_sort_buffer_size=320000, + myisam_repair_threads=2, + sort_buffer_size=220022, + binlog_format=row, + keep_files_on_create=ON, + max_join_size=2222220000000 FOR + SELECT @@myisam_sort_buffer_size, + @@myisam_repair_threads, + @@sort_buffer_size, + @@binlog_format, + @@keep_files_on_create, + @@max_join_size; + END| +DELIMITER ;| +--echo '' +--echo '# Pre-STATEMENT variable value +SELECT @@myisam_sort_buffer_size, + @@myisam_repair_threads, + @@sort_buffer_size, + @@binlog_format, + @@keep_files_on_create, + @@max_join_size; +--echo '' +--echo '' +SET STATEMENT myisam_sort_buffer_size=400000, + myisam_repair_threads=2, + sort_buffer_size=200000, + binlog_format=row, + keep_files_on_create=OFF, + max_join_size=4444440000000 FOR + CALL p4(); +--echo '' +--echo '# Post-STATEMENT +SELECT @@myisam_sort_buffer_size, + @@myisam_repair_threads, + @@sort_buffer_size, + @@binlog_format, + @@keep_files_on_create, + @@max_join_size; + +--echo '' +--echo '' +--echo '#------------------Test 16-----------------------#' +#################################################################### +# Test Effect on parsing # +#################################################################### +--echo '' +--echo '# Pre-STATEMENT variable value +SELECT @@sql_mode; +--echo '' +--echo '' +SET STATEMENT sql_mode='ansi' FOR PREPARE stmt FROM 'SELECT "t1".* FROM t1'; +execute stmt; +ALTER TABLE t1 ADD COLUMN v3 int; +# repreparation with other mode cause an error +--error ER_PARSE_ERROR +execute stmt; +ALTER TABLE t1 drop COLUMN v3; +deallocate prepare stmt; +--echo '' +--echo '# Post-STATEMENT +SELECT @@sql_mode; +--echo check the same behaviour in normal set +SET sql_mode='ansi'; +PREPARE stmt FROM 'SELECT "t1".* FROM t1'; +SET sql_mode=default; +execute stmt; +ALTER TABLE t1 ADD COLUMN v3 int; +# repreparation with other mode cause an error +--error ER_PARSE_ERROR +execute stmt; +ALTER TABLE t1 drop COLUMN v3; +deallocate prepare stmt; +# the above test about SP +SELECT @@sql_mode; +SET sql_mode='ansi'; +SELECT @@sql_mode; +DELIMITER |; + CREATE PROCEDURE p6() BEGIN + SELECT @@sql_mode; + SELECT "t1".* FROM t1; + END| +DELIMITER ;| +SET sql_mode=default; +call p6; +ALTER TABLE t1 ADD COLUMN v3 int; +#force SP stack invalidation +create view v1 as select * from t1; +drop view v1; +call p6; +ALTER TABLE t1 drop COLUMN v3; +drop procedure p6; + + +SELECT @@sql_mode; +DELIMITER |; +--echo # SET and the statement parsed as one unit before the SET takes effect +--error ER_PARSE_ERROR +SET STATEMENT sql_mode='ansi' FOR + CREATE PROCEDURE p6() BEGIN + SELECT @@sql_mode; + SELECT "t1".* FROM t1; + END| +DELIMITER ;| +#call p1; +#ALTER TABLE t1 ADD COLUMN v3 int; +#--echo # no reparsing for now +#call p1; +#ALTER TABLE t1 drop COLUMN v3; +#drop procedure p1; + + +# the above test about compound statement +SELECT @@sql_mode; +SET sql_mode='ansi'; +SELECT @@sql_mode; +DELIMITER |; +BEGIN NOT ATOMIC + SELECT @@sql_mode; + SELECT "t1".* FROM t1; +END| +DELIMITER ;| +SET sql_mode=default; + + +SELECT @@sql_mode; +DELIMITER |; +--echo # SET and the statement parsed as one unit before the SET takes effect +--error ER_PARSE_ERROR +SET STATEMENT sql_mode='ansi' FOR +BEGIN NOT ATOMIC + SELECT @@sql_mode; + SELECT "t1".* FROM t1; +END| +SET STATEMENT sql_mode='ansi' FOR +BEGIN NOT ATOMIC + SELECT @@sql_mode; + SELECT * FROM t1; + SELECT @@sql_mode; +END| +DELIMITER ;| +--echo '' +--echo '' +--echo '#------------------Test 17-----------------------#' +#################################################################### +# Test effect of SET STATEMENT FOR with SET SESSION modifying # +# the same variables as the SET STATEMENT # +#################################################################### +--echo '# set initial variable values +SET SESSION myisam_sort_buffer_size=500000, + myisam_repair_threads=1, + sort_buffer_size=100000, + binlog_format=mixed, + keep_files_on_create=ON, + max_join_size=2222220000000; +--echo '' +--echo '# Pre-STATEMENT variable value +SELECT @@myisam_sort_buffer_size, + @@myisam_repair_threads, + @@sort_buffer_size, + @@binlog_format, + @@keep_files_on_create, + @@max_join_size; +--echo '' +--echo '' +SET STATEMENT myisam_sort_buffer_size=320000, + myisam_repair_threads=2, + sort_buffer_size=220022, + binlog_format=row, + keep_files_on_create=ON, + max_join_size=2222220000000 + FOR SET SESSION + myisam_sort_buffer_size=260000, + myisam_repair_threads=3, + sort_buffer_size=230013, + binlog_format=row, + keep_files_on_create=ON, + max_join_size=2323230000000; + +--echo '' +--echo '# Post-STATEMENT +SELECT @@myisam_sort_buffer_size, + @@myisam_repair_threads, + @@sort_buffer_size, + @@binlog_format, + @@keep_files_on_create, + @@max_join_size; + +--echo '' +--echo '' +--echo '#------------------Test 18-----------------------#' +#################################################################### +# Test effect of SET SESSION inside a stored procedure with # +# with a SET STATEMENT on outside variables # +#################################################################### +--echo '# set initial variable values +SET SESSION myisam_sort_buffer_size=500000, + myisam_repair_threads=1, + sort_buffer_size=100000, + binlog_format=mixed, + keep_files_on_create=ON, + max_join_size=2222220000000; +--echo '' +--echo '# Pre-STATEMENT variable value +SELECT @@myisam_sort_buffer_size, + @@myisam_repair_threads, + @@sort_buffer_size, + @@binlog_format, + @@keep_files_on_create, + @@max_join_size; +--echo '' +--echo '' +DELIMITER |; +CREATE PROCEDURE p5() BEGIN + SELECT @@myisam_sort_buffer_size, + @@myisam_repair_threads, + @@sort_buffer_size, + @@binlog_format, + @@keep_files_on_create, + @@max_join_size; + SET SESSION + myisam_sort_buffer_size=260000, + myisam_repair_threads=3, + sort_buffer_size=230013, + binlog_format=row, + keep_files_on_create=ON, + max_join_size=2323230000000; + SELECT @@myisam_sort_buffer_size, + @@myisam_repair_threads, + @@sort_buffer_size, + @@binlog_format, + @@keep_files_on_create, + @@max_join_size; + END| +DELIMITER ;| +--echo '' +--echo '' +SET STATEMENT myisam_sort_buffer_size=400000, + myisam_repair_threads=2, + sort_buffer_size=200000, + binlog_format=row, + keep_files_on_create=OFF, + max_join_size=4444440000000 FOR + CALL p5(); + +--echo '' +--echo '# Post-STATEMENT +SELECT @@myisam_sort_buffer_size, + @@myisam_repair_threads, + @@sort_buffer_size, + @@binlog_format, + @@keep_files_on_create, + @@max_join_size; + +--echo '' +--echo '' +--echo '#------------------Test 19-----------------------#' +#Test for bad syntax +--error ER_PARSE_ERROR +SET STATEMENT max_error_count=100 FOR; +--error ER_PARSE_ERROR +SET STATEMENT max_error_count=100 INSERT t1 VALUES (1,2); +--error ER_PARSE_ERROR +SET STATEMENT FOR INSERT INTO t1 VALUES (1,2); +--error ER_PARSE_ERROR +SET max_error_count=100 FOR INSERT INTO t1 VALUES (1,2); +--error ER_UNKNOWN_SYSTEM_VARIABLE +SET STATEMENT GLOBAL max_error_count=100 FOR INSERT INTO t1 VALUES (1,2); +--error ER_PARSE_ERROR +SET STATEMENT @@global.max_error_count=100 FOR INSERT INTO t1 VALUES (1,2); + +--echo '' +--echo '' +--echo '#------------------Test 20-----------------------#' +#Test for global-only variables +--error ER_GLOBAL_VARIABLE +SET STATEMENT connect_timeout=100 FOR INSERT INTO t1 VALUES (1,2); + +--echo '' +--echo '' +--echo '#------------------Test 21-----------------------#' +#Test for recursion +SELECT @@myisam_sort_buffer_size, @@sort_buffer_size; +SET STATEMENT myisam_sort_buffer_size = 700000, sort_buffer_size = 3000000 + FOR SET STATEMENT myisam_sort_buffer_size=200000 + FOR SELECT @@myisam_sort_buffer_size, @@sort_buffer_size; +SELECT @@myisam_sort_buffer_size, @@sort_buffer_size; + +--echo '' +--echo '' +--echo '#------------------Test 22-----------------------#' +#Test for STATEMENT keyword +CREATE TABLE STATEMENT(a INT); +DROP TABLE STATEMENT; + +--echo '' +--echo '# Cleanup' +DROP TABLE t1; +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP PROCEDURE p3; +DROP PROCEDURE p4; +DROP PROCEDURE p5; + +# +# Limitation of opening tables for set operation +# +CREATE TABLE t1 (v1 INT, v2 INT); +insert into t1 values (1,1); +DELIMITER |; +CREATE FUNCTION myProc () + RETURNS INT + + SQL SECURITY DEFINER + + BEGIN + DECLARE mx INT; + SET mx = (select max(v1) from t1); + RETURN mx; + END| +DELIMITER ;| +--error ER_SUBQUERIES_NOT_SUPPORTED +SET STATEMENT myisam_repair_threads=(select max(v1) from t1) FOR + select 1; + +--error ER_SUBQUERIES_NOT_SUPPORTED +SET STATEMENT myisam_repair_threads=myProc() FOR + select 1; +drop function myProc; +drop table t1; + +# +# Prepared Statement +# +set session binlog_format=mixed; +PREPARE stmt1 FROM 'SELECT @@binlog_format'; +execute stmt1; +set statement binlog_format=row for execute stmt1; +execute stmt1; +deallocate prepare stmt1; +set statement binlog_format=row for PREPARE stmt1 FROM 'SELECT @@binlog_format'; +execute stmt1; +execute stmt1; +deallocate prepare stmt1; +PREPARE stmt1 FROM 'set statement binlog_format=row for SELECT @@binlog_format'; +execute stmt1; +execute stmt1; +deallocate prepare stmt1; +set session binlog_format=default; + +# +# Percona server bug#1341438 +# SET SESSION statement combined with SET STATEMENT has no effect +# +set session binlog_format=mixed; +SET STATEMENT sort_buffer_size=150000 FOR set session binlog_format=row; +SELECT @@binlog_format; + +# +# restore variables of original tests +# +SET @@binlog_format = @save_binlog_format; +SET @@keep_files_on_create = @save_keep_files_on_create; +SET @@max_join_size = @save_max_join_size; +SET @@myisam_repair_threads = @save_myisam_repair_threads; +SET @@myisam_sort_buffer_size = @save_myisam_sort_buffer_size; +SET @@sort_buffer_size = @save_sort_buffer_size; + +# +# Percona sever bug#1341606 +# SET STATEMENT incorrectly restore vaues of some variables +# +--echo #Correctly set timestamp +set session timestamp=4646464; +select @@timestamp != 4646464; +select @@timestamp != 4646464; +--echo #Correctly returned normal behaviour +set session timestamp=default; +select @@timestamp != 4646464; +select @@timestamp != 4646464; +--echo #here timestamp should be set only for the statement then restored default +set statement timestamp=4646464 for select @@timestamp; +set @save_tm=@@timestamp; +select @@timestamp != 4646464; +select @@timestamp != 4646464; +select @@timestamp != @save_tm; + +# +# Test of temporary changing default storage engine +# +set @save_dfs=@@default_storage_engine; +SET @@default_storage_engine=MyISAM; +SET STATEMENT default_storage_engine=MEMORY for CREATE TABLE t1 (a int); +SHOW CREATE TABLE t1; +select @@default_storage_engine; +drop table t1; +SET STATEMENT default_storage_engine=MyISAM for CREATE TABLE t1 (a int); +SHOW CREATE TABLE t1; +drop table t1; +SET @@default_storage_engine=@save_dfs; + +# +# statement timeout +# +SELECT @@MAX_STATEMENT_TIME; +SET STATEMENT MAX_STATEMENT_TIME=2 FOR SELECT SLEEP(1); +SHOW STATUS LIKE "max_statement_time_exceeded"; +SET STATEMENT MAX_STATEMENT_TIME=2 FOR SELECT SLEEP(3); +SHOW STATUS LIKE "max_statement_time_exceeded"; +SELECT @@MAX_STATEMENT_TIME; + +# +# MDEV-6946:Assertion `0' failed in mysql_execute_command on SET STATEMENT +# keycache1.key_buffer_size=.. FOR +# +--error ER_GLOBAL_VARIABLE +SET STATEMENT keycache1.key_buffer_size=1024 FOR SELECT 1; + + +# +# MDEV-6940: SET STATEMENT executed after SET GLOBAL does not work +# +set @save_general_log=@@global.general_log; +--echo # SET STATEMENT works (OK) +set statement lock_wait_timeout=1 for select @@lock_wait_timeout; +--echo # Setting a totally unrelated global variable +set global general_log=0; +--echo # SET STATEMENT should work +set statement lock_wait_timeout=1 for select @@lock_wait_timeout; +set global general_log=@save_general_log; + +# +# Prohibited Variables +# +--error ER_SET_STATEMENT_NOT_SUPPORTED +set statement autocommit=default for select 1; +--error ER_SET_STATEMENT_NOT_SUPPORTED +set statement gtid_domain_id=default for select 1; +--error ER_SET_STATEMENT_NOT_SUPPORTED +set statement tx_isolation=default for select 1; +--error ER_SET_STATEMENT_NOT_SUPPORTED +set statement rand_seed1=default for select 1; +--error ER_SET_STATEMENT_NOT_SUPPORTED +set statement rand_seed2=default for select 1; +--error ER_SET_STATEMENT_NOT_SUPPORTED +set statement skip_replication=default for select 1; +--error ER_SET_STATEMENT_NOT_SUPPORTED +set statement default_master_connection=default for select 1; +--error ER_SET_STATEMENT_NOT_SUPPORTED +set statement default_master_connection=default for select 1; +--error ER_SET_STATEMENT_NOT_SUPPORTED +set statement last_insert_id=1 for select 1; +--error ER_SET_STATEMENT_NOT_SUPPORTED +set statement sql_log_off=default for select 1; +--error ER_SET_STATEMENT_NOT_SUPPORTED +set statement long_query_time=default for select 1; +--error ER_SET_STATEMENT_NOT_SUPPORTED +set statement log_slow_filter=default for select 1; +--error ER_SET_STATEMENT_NOT_SUPPORTED +set statement log_slow_verbosity=default for select 1; +--error ER_SET_STATEMENT_NOT_SUPPORTED +set statement log_slow_rate_limit=default for select 1; +--error ER_SET_STATEMENT_NOT_SUPPORTED +set statement slow_query_log=default for select 1; +--error ER_SET_STATEMENT_NOT_SUPPORTED +set statement min_examined_row_limit=default for select 1; +--error ER_SET_STATEMENT_NOT_SUPPORTED +set statement character_set_client=default for select 1; +--error ER_SET_STATEMENT_NOT_SUPPORTED +set statement character_set_connection=default for select 1; +--error ER_SET_STATEMENT_NOT_SUPPORTED +set statement character_set_filesystem=default for select 1; +--error ER_SET_STATEMENT_NOT_SUPPORTED +set statement collation_connection=default for select 1; +--error ER_SET_STATEMENT_NOT_SUPPORTED +set statement old_passwords=default for select 1; +--error ER_SET_STATEMENT_NOT_SUPPORTED +set statement query_cache_type=default for select 1; +--error ER_SET_STATEMENT_NOT_SUPPORTED +set statement wait_timeout=default for select 1; +--error ER_SET_STATEMENT_NOT_SUPPORTED +set statement interactive_timeout=default for select 1; + diff --git a/mysql-test/t/set_statement_debug.test b/mysql-test/t/set_statement_debug.test new file mode 100644 index 00000000000..a409370a61a --- /dev/null +++ b/mysql-test/t/set_statement_debug.test @@ -0,0 +1,24 @@ +--source include/have_debug.inc +--source include/have_debug_sync.inc + +set @save_debug_dbug= @@debug_dbug; + +# check that change debug_dbug visible in SELECT +set statement debug_dbug="d,something" for select @@debug_dbug; +set @@debug_dbug= @save_debug_dbug; + +#check that change debug_dbug has influence of hooks... +CREATE TABLE t1(f0 int auto_increment primary key, f1 int); +INSERT INTO t1(f1) VALUES (0),(1),(2),(3),(4),(5); +CALL mtr.add_suppression("Out of sort memory"); +--error ER_OUT_OF_SORTMEMORY +SET statement debug_dbug= '+d,alloc_sort_buffer_fail' for SELECT * FROM t1 ORDER BY f1 ASC, f0; + +# ... and works only for one statement +SELECT * FROM t1 ORDER BY f1 ASC, f0; + +DROP TABLE t1; +set @@debug_dbug= @save_debug_dbug; + +--error ER_SET_STATEMENT_NOT_SUPPORTED +set statement DEBUG_SYNC = 'now SIGNAL hi' for select 1; diff --git a/mysql-test/t/set_statement_profiling.test b/mysql-test/t/set_statement_profiling.test new file mode 100644 index 00000000000..6ef64d2f18c --- /dev/null +++ b/mysql-test/t/set_statement_profiling.test @@ -0,0 +1,9 @@ +source include/have_profiling.inc; + +# +# Prohibited Variables +# +--error ER_SET_STATEMENT_NOT_SUPPORTED +set statement profiling=default for select 1; +--error ER_SET_STATEMENT_NOT_SUPPORTED +set statement profiling_history_size=default for select 1; diff --git a/sql/item.h b/sql/item.h index ff0c786ab94..e006622dca2 100644 --- a/sql/item.h +++ b/sql/item.h @@ -2819,6 +2819,16 @@ protected: fixed= 1; } public: + Item_string(CHARSET_INFO *csi, const char *str_arg, uint length_arg) + : m_cs_specified(FALSE) + { + collation.set(csi, DERIVATION_COERCIBLE); + set_name(NULL, 0, system_charset_info); + decimals= NOT_FIXED_DEC; + fixed= 1; + str_value.copy(str_arg, length_arg, csi); + max_length= str_value.numchars() * csi->mbmaxlen; + } // Constructors with the item name set from its value Item_string(const char *str, uint length, CHARSET_INFO *cs, Derivation dv, uint repertoire) @@ -2954,6 +2964,7 @@ public: } return MYSQL_TYPE_STRING; // Not a temporal literal } + }; diff --git a/sql/item_func.cc b/sql/item_func.cc index 4ec0466bda8..25c0197cb9b 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -5547,7 +5547,7 @@ get_var_with_binlog(THD *thd, enum_sql_command sql_command, tmp_var_list.push_back(new set_var_user(new Item_func_set_user_var(name, new Item_null()))); /* Create the variable */ - if (sql_set_variables(thd, &tmp_var_list)) + if (sql_set_variables(thd, &tmp_var_list, false)) { thd->lex= sav_lex; goto err; diff --git a/sql/lex.h b/sql/lex.h index 5ca188f99a0..51b69f00990 100644 --- a/sql/lex.h +++ b/sql/lex.h @@ -567,6 +567,7 @@ static SYMBOL symbols[] = { { "START", SYM(START_SYM)}, { "STARTING", SYM(STARTING)}, { "STARTS", SYM(STARTS_SYM)}, + { "STATEMENT", SYM(STATEMENT_SYM)}, { "STATS_AUTO_RECALC",SYM(STATS_AUTO_RECALC_SYM)}, { "STATS_PERSISTENT", SYM(STATS_PERSISTENT_SYM)}, { "STATS_SAMPLE_PAGES",SYM(STATS_SAMPLE_PAGES_SYM)}, diff --git a/sql/set_var.cc b/sql/set_var.cc index bae65118112..1dad3603799 100644 --- a/sql/set_var.cc +++ b/sql/set_var.cc @@ -147,7 +147,7 @@ sys_var::sys_var(sys_var_chain *chain, const char *name_arg, flags(flags_arg), show_val_type(show_val_type_arg), guard(lock), offset(off), on_check(on_check_func), on_update(on_update_func), deprecation_substitute(substitute), - is_os_charset(FALSE) + is_os_charset(FALSE), default_val(FALSE) { /* There is a limitation in handle_options() related to short options: @@ -675,9 +675,10 @@ sys_var *intern_find_sys_var(const char *str, uint length) -1 ERROR, message not sent */ -int sql_set_variables(THD *thd, List<set_var_base> *var_list) +int sql_set_variables(THD *thd, List<set_var_base> *var_list, bool free) { - int error; + int error= 0; + bool was_error= thd->is_error(); List_iterator_fast<set_var_base> it(*var_list); DBUG_ENTER("sql_set_variables"); @@ -687,7 +688,7 @@ int sql_set_variables(THD *thd, List<set_var_base> *var_list) if ((error= var->check(thd))) goto err; } - if (!(error= MY_TEST(thd->is_error()))) + if (was_error || !(error= MY_TEST(thd->is_error()))) { it.rewind(); while ((var= it++)) @@ -695,7 +696,8 @@ int sql_set_variables(THD *thd, List<set_var_base> *var_list) } err: - free_underlaid_joins(thd, &thd->lex->select_lex); + if (free) + free_underlaid_joins(thd, &thd->lex->select_lex); DBUG_RETURN(error); } @@ -788,6 +790,7 @@ int set_var::light_check(THD *thd) */ int set_var::update(THD *thd) { + var->set_is_default(value == 0); return value ? var->update(thd, this) : var->set_default(thd, this); } diff --git a/sql/set_var.h b/sql/set_var.h index fe2a0d8e953..47f19f0ff96 100644 --- a/sql/set_var.h +++ b/sql/set_var.h @@ -61,7 +61,8 @@ public: sys_var *next; LEX_CSTRING name; enum flag_enum { GLOBAL, SESSION, ONLY_SESSION, SCOPE_MASK=1023, - READONLY=1024, ALLOCATED=2048, PARSE_EARLY=4096 }; + READONLY=1024, ALLOCATED=2048, PARSE_EARLY=4096, + NO_SET_STATEMENT=8192}; enum { NO_GETOPT=-1, GETOPT_ONLY_HELP=-2 }; enum where { CONFIG, AUTO, SQL, COMPILE_TIME, ENV }; @@ -87,6 +88,7 @@ protected: on_update_function on_update; const char *const deprecation_substitute; bool is_os_charset; ///< true if the value is in character_set_filesystem + bool default_val; public: sys_var(sys_var_chain *chain, const char *name_arg, const char *comment, @@ -133,6 +135,7 @@ public: that support the syntax @@keycache_name.variable_name */ bool is_struct() { return option.var_type & GET_ASK_ADDR; } + bool is_set_stmt_ok() const { return !(flags & NO_SET_STATEMENT); } bool is_written_to_binlog(enum_var_type type) { return type != OPT_GLOBAL && binlog_status == SESSION_VARIABLE_IN_BINLOG; } bool check_update_type(Item_result type) @@ -191,6 +194,8 @@ public: return insert_dynamic(array, (uchar*)&option); } void do_deprecated_warning(THD *thd); + bool is_default() { return default_val; } + void set_is_default(bool def) { default_val= def; } virtual uchar *default_value_ptr(THD *thd) { return (uchar*)&option.def_value; } @@ -249,6 +254,7 @@ public: virtual int check(THD *thd)=0; /* To check privileges etc. */ virtual int update(THD *thd)=0; /* To set the value */ virtual int light_check(THD *thd) { return check(thd); } /* for PS */ + virtual bool is_system() { return FALSE; } }; @@ -290,6 +296,7 @@ public: else value=value_arg; } + virtual bool is_system() { return 1; } int check(THD *thd); int update(THD *thd); int light_check(THD *thd); @@ -388,7 +395,7 @@ SHOW_VAR* enumerate_sys_vars(THD *thd, bool sorted, enum enum_var_type type); int fill_sysvars(THD *thd, TABLE_LIST *tables, COND *cond); sys_var *find_sys_var(THD *thd, const char *str, uint length=0); -int sql_set_variables(THD *thd, List<set_var_base> *var_list); +int sql_set_variables(THD *thd, List<set_var_base> *var_list, bool free); #define SYSVAR_AUTOSIZE(VAR,VAL) \ do { \ diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt index 199a822d022..98781a2d6b3 100644 --- a/sql/share/errmsg-utf8.txt +++ b/sql/share/errmsg-utf8.txt @@ -7111,3 +7111,7 @@ ER_TABLE_DEFINITION_TOO_BIG eng "The definition for table %`s is too big" ER_STATEMENT_TIMEOUT 70100 eng "Query execution was interrupted (max_statement_time exceeded)" +ER_SUBQUERIES_NOT_SUPPORTED 42000 + eng "%s does not support subqueries or stored functions." +ER_SET_STATEMENT_NOT_SUPPORTED 42000 + eng "The system variable %.200s cannot be set in SET STATEMENT." diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 34fa0308f7d..90c550d6a30 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -3441,6 +3441,7 @@ void Query_arena::free_items() { next= free_list->next; DBUG_ASSERT(free_list != next); + DBUG_PRINT("info", ("free item: 0x%lx", (ulong) free_list)); free_list->delete_self(); } /* Postcondition: free_list is 0 */ diff --git a/sql/sql_class.h b/sql/sql_class.h index 4198402ae9b..7a34960e390 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -3826,6 +3826,10 @@ public: thr_timer_end(&query_timer); #endif } + void restore_set_statement_var() + { + main_lex.restore_set_statement_var(); + } }; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 7eb38b1dd7a..6432e1937be 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -542,6 +542,8 @@ void lex_start(THD *thd) lex->reset_slave_info.all= false; lex->limit_rows_examined= 0; lex->limit_rows_examined_cnt= ULONGLONG_MAX; + lex->var_list.empty(); + lex->stmt_var_list.empty(); DBUG_VOID_RETURN; } @@ -4233,6 +4235,17 @@ int LEX::print_explain(select_result_sink *output, uint8 explain_flags, return res; } +void LEX::restore_set_statement_var() +{ + DBUG_ENTER("LEX::restore_set_statement_var"); + if (!old_var_list.is_empty()) + { + DBUG_PRINT("info", ("vars: %d", old_var_list.elements)); + sql_set_variables(thd, &old_var_list, false); + old_var_list.empty(); + } + DBUG_VOID_RETURN; +} /* Save explain structures of a UNION. The only variable member is whether the diff --git a/sql/sql_lex.h b/sql/sql_lex.h index b18cff131ca..6c631e20c87 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -2401,6 +2401,8 @@ struct LEX: public Query_tables_list List<Item> *insert_list,field_list,value_list,update_list; List<List_item> many_values; List<set_var_base> var_list; + List<set_var_base> stmt_var_list; //SET_STATEMENT values + List<set_var_base> old_var_list; // SET STATEMENT old values List<Item_func_set_user_var> set_var_list; // in-query assignment list List<Item_param> param_list; List<LEX_STRING> view_list; // view list (list of field names in view) @@ -2775,6 +2777,7 @@ struct LEX: public Query_tables_list int print_explain(select_result_sink *output, uint8 explain_flags, bool is_analyze, bool *printed_anything); + void restore_set_statement_var(); }; diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index b905d8b0bd6..5af700b6917 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -2635,6 +2635,101 @@ mysql_execute_command(THD *thd) thd->get_binlog_format(&orig_binlog_format, &orig_current_stmt_binlog_format); + if (!lex->stmt_var_list.is_empty() && !thd->slave_thread) + { + DBUG_PRINT("info", ("SET STATEMENT %d vars", lex->stmt_var_list.elements)); + + lex->old_var_list.empty(); + List_iterator_fast<set_var_base> it(lex->stmt_var_list); + set_var_base *var; + while ((var=it++)) + { + DBUG_ASSERT(var->is_system()); + set_var *o= NULL, *v= (set_var*)var; + if (!v->var->is_set_stmt_ok()) + { + my_error(ER_SET_STATEMENT_NOT_SUPPORTED, MYF(0), v->var->name.str); + goto error; + } + if (v->var->is_default()) + o= new set_var(v->type, v->var, &v->base, NULL); + else + { + switch (v->var->option.var_type & GET_TYPE_MASK) + { + case GET_BOOL: + case GET_INT: + case GET_LONG: + case GET_LL: + { + bool null_value; + longlong val= v->var->val_int(&null_value, thd, v->type, &v->base); + o= new set_var(v->type, v->var, &v->base, + (null_value ? + (Item *)new Item_null() : + (Item *)new Item_int(val))); + } + break; + case GET_UINT: + case GET_ULONG: + case GET_ULL: + { + bool null_value; + ulonglong val= v->var->val_int(&null_value, thd, v->type, &v->base); + o= new set_var(v->type, v->var, &v->base, + (null_value ? + (Item *)new Item_null() : + (Item *)new Item_uint(val))); + } + break; + case GET_DOUBLE: + { + bool null_value; + double val= v->var->val_real(&null_value, thd, v->type, &v->base); + o= new set_var(v->type, v->var, &v->base, + (null_value ? + (Item *)new Item_null() : + (Item *)new Item_float(val, 1))); + } + break; + default: + case GET_NO_ARG: + case GET_DISABLED: + DBUG_ASSERT(0); + case 0: + case GET_FLAGSET: + case GET_ENUM: + case GET_SET: + case GET_STR: + case GET_STR_ALLOC: + { + char buff[STRING_BUFFER_USUAL_SIZE]; + String tmp(buff, sizeof(buff), v->var->charset(thd)),*val; + val= v->var->val_str(&tmp, thd, v->type, &v->base); + if (val) + { + Item_string *str= new Item_string(v->var->charset(thd), + val->ptr(), val->length()); + o= new set_var(v->type, v->var, &v->base, str); + } + else + o= new set_var(v->type, v->var, &v->base, new Item_null()); + } + break; + } + } + DBUG_ASSERT(o); + lex->old_var_list.push_back(o); + } + if (thd->is_error() || + (res= sql_set_variables(thd, &lex->stmt_var_list, false))) + { + if (!thd->is_error()) + my_error(ER_WRONG_ARGUMENTS, MYF(0), "SET"); + goto error; + } + } + /* Force statement logging for DDL commands to allow us to update privilege, system or statistic tables directly without the updates @@ -4100,7 +4195,7 @@ end_with_restore_list: if ((check_table_access(thd, SELECT_ACL, all_tables, FALSE, UINT_MAX, FALSE) || open_and_lock_tables(thd, all_tables, TRUE, 0))) goto error; - if (!(res= sql_set_variables(thd, lex_var_list))) + if (!(res= sql_set_variables(thd, lex_var_list, true))) { my_ok(thd); } @@ -4344,8 +4439,7 @@ end_with_restore_list: DBUG_ASSERT(lex->event_parse_data); if (lex->table_or_sp_used()) { - my_error(ER_NOT_SUPPORTED_YET, MYF(0), "Usage of subqueries or stored " - "function calls as part of this statement"); + my_error(ER_SUBQUERIES_NOT_SUPPORTED, MYF(0), "CREATE/ALTER EVENT"); break; } @@ -4665,8 +4759,7 @@ end_with_restore_list: { if (lex->table_or_sp_used()) { - my_error(ER_NOT_SUPPORTED_YET, MYF(0), "Usage of subqueries or stored " - "function calls as part of this statement"); + my_error(ER_SUBQUERIES_NOT_SUPPORTED, MYF(0), "KILL"); break; } @@ -5059,6 +5152,7 @@ create_sp_error: case SQLCOM_COMPOUND: DBUG_ASSERT(all_tables == 0); DBUG_ASSERT(thd->in_sub_stmt == 0); + lex->sphead->m_sql_mode= thd->variables.sql_mode; if (do_execute_sp(thd, lex->sphead)) goto error; break; @@ -5482,6 +5576,8 @@ finish: DBUG_ASSERT(!thd->in_active_multi_stmt_transaction() || thd->in_multi_stmt_transaction_mode()); + + lex->restore_set_statement_var(); lex->unit.cleanup(); if (! thd->in_sub_stmt) diff --git a/sql/sql_plugin.h b/sql/sql_plugin.h index 6b310865bba..9cfdba1edcd 100644 --- a/sql/sql_plugin.h +++ b/sql/sql_plugin.h @@ -189,4 +189,5 @@ extern bool plugin_foreach_with_mask(THD *thd, plugin_foreach_func *func, int type, uint state_mask, void *arg); extern bool plugin_dl_foreach(THD *thd, const LEX_STRING *dl, plugin_foreach_func *func, void *arg); + #endif diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index b1765cdda04..5c67a4c45a7 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -3299,7 +3299,7 @@ void Prepared_statement::cleanup_stmt() { DBUG_ENTER("Prepared_statement::cleanup_stmt"); DBUG_PRINT("enter",("stmt: 0x%lx", (long) this)); - + thd->restore_set_statement_var(); cleanup_items(free_list); thd->cleanup_after_query(); thd->rollback_item_tree_changes(); @@ -3619,7 +3619,9 @@ Prepared_statement::execute_loop(String *expanded_query, Reprepare_observer reprepare_observer; bool error; int reprepare_attempt= 0; - +#ifndef DBUG_OFF + Item *free_list_state= thd->free_list; +#endif thd->select_number= select_number_after_prepare; /* Check if we got an error when sending long data */ if (state == Query_arena::STMT_ERROR) @@ -3646,7 +3648,7 @@ reexecute: allocated items when cleaning up after validation of the prepared statement. */ - DBUG_ASSERT(thd->free_list == NULL); + DBUG_ASSERT(thd->free_list == free_list_state); /* Install the metadata observer. If some metadata version is diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 632bdd25915..d11ac485c06 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -256,7 +256,6 @@ static bool maybe_start_compound_statement(THD *thd) Lex->sp_chistics.suid= SP_IS_NOT_SUID; Lex->sphead->set_body_start(thd, YYLIP->get_cpp_ptr()); - Lex->sphead->m_sql_mode= thd->variables.sql_mode; } return 0; } @@ -788,8 +787,10 @@ static void sp_create_assignment_lex(THD *thd, bool no_lookahead) lex->var_list.empty(); lex->autocommit= 0; /* get_ptr() is only correct with no lookahead. */ - DBUG_ASSERT(no_lookahead); - lex->sphead->m_tmp_query= lip->get_ptr(); + if (no_lookahead) + lex->sphead->m_tmp_query= lip->get_ptr(); + else + lex->sphead->m_tmp_query= lip->get_tok_end(); /* Inherit from outer lex. */ lex->option_type= old_lex->option_type; } @@ -932,10 +933,10 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize); %parse-param { THD *thd } %lex-param { THD *thd } /* - Currently there are 163 shift/reduce conflicts. + Currently there are 164 shift/reduce conflicts. We should not introduce new conflicts any more. */ -%expect 163 +%expect 164 /* Comments for TOKENS. @@ -1481,6 +1482,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize); %token STARTING %token STARTS_SYM %token START_SYM /* SQL-2003-R */ +%token STATEMENT_SYM %token STATS_AUTO_RECALC_SYM %token STATS_PERSISTENT_SYM %token STATS_SAMPLE_PAGES_SYM @@ -14321,6 +14323,7 @@ keyword_sp: | SQL_NO_CACHE_SYM {} | SQL_THREAD {} | STARTS_SYM {} + | STATEMENT_SYM {} | STATUS_SYM {} | STORAGE_SYM {} | STRING_SYM {} @@ -14395,8 +14398,37 @@ set: } start_option_value_list {} + | SET STATEMENT_SYM + { + LEX *lex= Lex; + mysql_init_select(lex); + lex->option_type= OPT_SESSION; + lex->sql_command= SQLCOM_SET_OPTION; + lex->autocommit= 0; + } + set_stmt_option_value_following_option_type_list + { + LEX *lex= Lex; + if (lex->table_or_sp_used()) + { + my_error(ER_SUBQUERIES_NOT_SUPPORTED, MYF(0), "SET STATEMENT"); + MYSQL_YYABORT; + } + lex->stmt_var_list= lex->var_list; + lex->var_list.empty(); + } + FOR_SYM verb_clause + {} ; +set_stmt_option_value_following_option_type_list: + /* + Only system variables can be used here. If this condition is changed + please check careful code under lex->option_type == OPT_STATEMENT + condition on wrong type casts. + */ + option_value_following_option_type + | set_stmt_option_value_following_option_type_list ',' option_value_following_option_type // Start of option value list start_option_value_list: diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index b5a43103c53..a7d58431704 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -636,7 +636,7 @@ static bool fix_thd_charset(sys_var *self, THD *thd, enum_var_type type) static Sys_var_struct Sys_character_set_client( "character_set_client", "The character set for statements " "that arrive from the client", - SESSION_VAR(character_set_client), NO_CMD_LINE, + NO_SET_STMT SESSION_VAR(character_set_client), NO_CMD_LINE, offsetof(CHARSET_INFO, csname), DEFAULT(&default_charset_info), NO_MUTEX_GUARD, IN_BINLOG, ON_CHECK(check_cs_client), ON_UPDATE(fix_thd_charset)); @@ -645,7 +645,7 @@ static Sys_var_struct Sys_character_set_connection( "character_set_connection", "The character set used for " "literals that do not have a character set introducer and for " "number-to-string conversion", - SESSION_VAR(collation_connection), NO_CMD_LINE, + NO_SET_STMT SESSION_VAR(collation_connection), NO_CMD_LINE, offsetof(CHARSET_INFO, csname), DEFAULT(&default_charset_info), NO_MUTEX_GUARD, IN_BINLOG, ON_CHECK(check_charset_not_null), ON_UPDATE(fix_thd_charset)); @@ -659,7 +659,7 @@ static Sys_var_struct Sys_character_set_results( static Sys_var_struct Sys_character_set_filesystem( "character_set_filesystem", "The filesystem character set", - SESSION_VAR(character_set_filesystem), NO_CMD_LINE, + NO_SET_STMT SESSION_VAR(character_set_filesystem), NO_CMD_LINE, offsetof(CHARSET_INFO, csname), DEFAULT(&character_set_filesystem), NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(check_charset_not_null), ON_UPDATE(fix_thd_charset)); @@ -705,7 +705,7 @@ static bool check_collation_not_null(sys_var *self, THD *thd, set_var *var) static Sys_var_struct Sys_collation_connection( "collation_connection", "The collation of the connection " "character set", - SESSION_VAR(collation_connection), NO_CMD_LINE, + NO_SET_STMT SESSION_VAR(collation_connection), NO_CMD_LINE, offsetof(CHARSET_INFO, name), DEFAULT(&default_charset_info), NO_MUTEX_GUARD, IN_BINLOG, ON_CHECK(check_collation_not_null), ON_UPDATE(fix_thd_charset)); @@ -997,7 +997,7 @@ static bool check_master_connection(sys_var *self, THD *thd, set_var *var) static Sys_var_session_lexstring Sys_default_master_connection( "default_master_connection", "Master connection to use for all slave variables and slave commands", - SESSION_ONLY(default_master_connection), + NO_SET_STMT SESSION_ONLY(default_master_connection), NO_CMD_LINE, IN_SYSTEM_CHARSET, DEFAULT(""), MAX_CONNECTION_NAME, ON_CHECK(check_master_connection)); #endif @@ -1024,7 +1024,7 @@ static Sys_var_ulong Sys_interactive_timeout( "interactive_timeout", "The number of seconds the server waits for activity on an interactive " "connection before closing it", - SESSION_VAR(net_interactive_timeout), + NO_SET_STMT SESSION_VAR(net_interactive_timeout), CMD_LINE(REQUIRED_ARG), VALID_RANGE(1, LONG_TIMEOUT), DEFAULT(NET_WAIT_TIMEOUT), BLOCK_SIZE(1)); @@ -1182,7 +1182,7 @@ static Sys_var_double Sys_long_query_time( "Log all queries that have taken more than long_query_time seconds " "to execute to file. The argument will be treated as a decimal value " "with microsecond precision", - SESSION_VAR(long_query_time_double), + NO_SET_STMT SESSION_VAR(long_query_time_double), CMD_LINE(REQUIRED_ARG), VALID_RANGE(0, LONG_TIMEOUT), DEFAULT(10), NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(0), ON_UPDATE(update_cached_long_query_time)); @@ -1437,7 +1437,7 @@ static Sys_var_uint Sys_gtid_domain_id( "parallel paths (for example multiple masters), each independent " "source server must use a distinct domain_id. For simple tree-shaped " "replication topologies, it can be left at its default, 0.", - SESSION_VAR(gtid_domain_id), + NO_SET_STMT SESSION_VAR(gtid_domain_id), CMD_LINE(REQUIRED_ARG), VALID_RANGE(0, UINT_MAX32), DEFAULT(0), BLOCK_SIZE(1), NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(check_gtid_domain_id)); @@ -2037,7 +2037,7 @@ static Sys_var_ulong Sys_min_examined_row_limit( "min_examined_row_limit", "Don't write queries to slow log that examine fewer rows " "than that", - SESSION_VAR(min_examined_row_limit), CMD_LINE(REQUIRED_ARG), + NO_SET_STMT SESSION_VAR(min_examined_row_limit), CMD_LINE(REQUIRED_ARG), VALID_RANGE(0, UINT_MAX), DEFAULT(0), BLOCK_SIZE(1)); #ifdef _WIN32 @@ -2130,8 +2130,9 @@ static bool check_old_passwords(sys_var *self, THD *thd, set_var *var) static Sys_var_mybool Sys_old_passwords( "old_passwords", "Use old password encryption method (needed for 4.0 and older clients)", - SESSION_VAR(old_passwords), CMD_LINE(OPT_ARG), DEFAULT(FALSE), - NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(check_old_passwords)); + NO_SET_STMT SESSION_VAR(old_passwords), CMD_LINE(OPT_ARG), + DEFAULT(FALSE), NO_MUTEX_GUARD, NOT_IN_BINLOG, + ON_CHECK(check_old_passwords)); export sys_var *Sys_old_passwords_ptr= &Sys_old_passwords; // for sql_acl.cc static Sys_var_ulong Sys_open_files_limit( @@ -2675,7 +2676,7 @@ static Sys_var_enum Sys_query_cache_type( "OFF = Don't cache or retrieve results. ON = Cache all results " "except SELECT SQL_NO_CACHE ... queries. DEMAND = Cache only " "SELECT SQL_CACHE ... queries", - SESSION_VAR(query_cache_type), CMD_LINE(REQUIRED_ARG), + NO_SET_STMT SESSION_VAR(query_cache_type), CMD_LINE(REQUIRED_ARG), query_cache_type_names, DEFAULT(1), NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(check_query_cache_type), ON_UPDATE(fix_query_cache_type)); @@ -3192,7 +3193,7 @@ static bool check_tx_isolation(sys_var *self, THD *thd, set_var *var) // NO_CMD_LINE - different name of the option static Sys_var_tx_isolation Sys_tx_isolation( "tx_isolation", "Default transaction isolation level", - SESSION_VAR(tx_isolation), NO_CMD_LINE, + NO_SET_STMT SESSION_VAR(tx_isolation), NO_CMD_LINE, tx_isolation_names, DEFAULT(ISO_REPEATABLE_READ), NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(check_tx_isolation)); @@ -3284,7 +3285,7 @@ static Sys_var_ulong Sys_net_wait_timeout( "wait_timeout", "The number of seconds the server waits for activity on a " "connection before closing it", - SESSION_VAR(net_wait_timeout), CMD_LINE(REQUIRED_ARG), + NO_SET_STMT SESSION_VAR(net_wait_timeout), CMD_LINE(REQUIRED_ARG), VALID_RANGE(1, IF_WIN(INT_MAX32/1000, LONG_TIMEOUT)), DEFAULT(NET_WAIT_TIMEOUT), BLOCK_SIZE(1)); @@ -3321,7 +3322,7 @@ static Sys_var_plugin Sys_default_tmp_storage_engine( */ static Sys_var_debug_sync Sys_debug_sync( "debug_sync", "Debug Sync Facility", - sys_var::ONLY_SESSION, NO_CMD_LINE, + NO_SET_STMT sys_var::ONLY_SESSION, NO_CMD_LINE, DEFAULT(0), NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(check_has_super)); #endif /* defined(ENABLED_DEBUG_SYNC) */ @@ -3407,7 +3408,8 @@ static bool fix_autocommit(sys_var *self, THD *thd, enum_var_type type) static Sys_var_bit Sys_autocommit( "autocommit", "autocommit", - SESSION_VAR(option_bits), NO_CMD_LINE, OPTION_AUTOCOMMIT, DEFAULT(TRUE), + NO_SET_STMT SESSION_VAR(option_bits), NO_CMD_LINE, + OPTION_AUTOCOMMIT, DEFAULT(TRUE), NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(0), ON_UPDATE(fix_autocommit)); export sys_var *Sys_autocommit_ptr= &Sys_autocommit; // for sql_yacc.yy @@ -3423,7 +3425,7 @@ static Sys_var_bit Sys_big_selects( static Sys_var_bit Sys_log_off( "sql_log_off", "sql_log_off", - SESSION_VAR(option_bits), NO_CMD_LINE, OPTION_LOG_OFF, + NO_SET_STMT SESSION_VAR(option_bits), NO_CMD_LINE, OPTION_LOG_OFF, DEFAULT(FALSE), NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(check_has_super)); /** @@ -3527,12 +3529,12 @@ static Sys_var_bit Sys_unique_checks( #ifdef ENABLED_PROFILING static Sys_var_bit Sys_profiling( "profiling", "profiling", - SESSION_VAR(option_bits), NO_CMD_LINE, OPTION_PROFILING, + NO_SET_STMT SESSION_VAR(option_bits), NO_CMD_LINE, OPTION_PROFILING, DEFAULT(FALSE)); static Sys_var_ulong Sys_profiling_history_size( "profiling_history_size", "Limit of query profiling memory", - SESSION_VAR(profiling_history_size), CMD_LINE(REQUIRED_ARG), + NO_SET_STMT SESSION_VAR(profiling_history_size), CMD_LINE(REQUIRED_ARG), VALID_RANGE(0, 100), DEFAULT(15), BLOCK_SIZE(1)); #endif @@ -3566,7 +3568,8 @@ static bool check_skip_replication(sys_var *self, THD *thd, set_var *var) static Sys_var_bit Sys_skip_replication( "skip_replication", "skip_replication", - SESSION_ONLY(option_bits), NO_CMD_LINE, OPTION_SKIP_REPLICATION, + NO_SET_STMT SESSION_ONLY(option_bits), + NO_CMD_LINE, OPTION_SKIP_REPLICATION, DEFAULT(FALSE), NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(check_skip_replication)); @@ -3616,7 +3619,7 @@ static ulonglong read_last_insert_id(THD *thd) } static Sys_var_session_special Sys_last_insert_id( "last_insert_id", "The value to be returned from LAST_INSERT_ID()", - sys_var::ONLY_SESSION, NO_CMD_LINE, + NO_SET_STMT sys_var::ONLY_SESSION, NO_CMD_LINE, VALID_RANGE(0, ULONGLONG_MAX), BLOCK_SIZE(1), NO_MUTEX_GUARD, IN_BINLOG, ON_CHECK(0), ON_UPDATE(update_last_insert_id), ON_READ(read_last_insert_id)); @@ -3688,7 +3691,7 @@ static ulonglong read_rand_seed(THD *thd) static Sys_var_session_special Sys_rand_seed1( "rand_seed1", "Sets the internal state of the RAND() " "generator for replication purposes", - sys_var::ONLY_SESSION, NO_CMD_LINE, + NO_SET_STMT sys_var::ONLY_SESSION, NO_CMD_LINE, VALID_RANGE(0, ULONG_MAX), BLOCK_SIZE(1), NO_MUTEX_GUARD, IN_BINLOG, ON_CHECK(0), ON_UPDATE(update_rand_seed1), ON_READ(read_rand_seed)); @@ -3706,7 +3709,7 @@ static bool update_rand_seed2(THD *thd, set_var *var) static Sys_var_session_special Sys_rand_seed2( "rand_seed2", "Sets the internal state of the RAND() " "generator for replication purposes", - sys_var::ONLY_SESSION, NO_CMD_LINE, + NO_SET_STMT sys_var::ONLY_SESSION, NO_CMD_LINE, VALID_RANGE(0, ULONG_MAX), BLOCK_SIZE(1), NO_MUTEX_GUARD, IN_BINLOG, ON_CHECK(0), ON_UPDATE(update_rand_seed2), ON_READ(read_rand_seed)); @@ -3738,7 +3741,7 @@ static Sys_var_session_special Sys_warning_count( static Sys_var_ulong Sys_default_week_format( "default_week_format", "The default week format used by WEEK() functions", - SESSION_VAR(default_week_format), CMD_LINE(REQUIRED_ARG), + NO_SET_STMT SESSION_VAR(default_week_format), CMD_LINE(REQUIRED_ARG), VALID_RANGE(0, 7), DEFAULT(0), BLOCK_SIZE(1)); static Sys_var_ulonglong Sys_group_concat_max_len( @@ -3961,7 +3964,7 @@ static Sys_var_mybool Sys_slow_query_log( "Log slow queries to a table or log file. Defaults logging to a file " "'hostname'-slow.log or a table mysql.slow_log if --log-output=TABLE is " "used. Must be enabled to activate other slow log options", - SESSION_VAR(sql_log_slow), CMD_LINE(OPT_ARG), + NO_SET_STMT SESSION_VAR(sql_log_slow), CMD_LINE(OPT_ARG), DEFAULT(FALSE), NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(0), ON_UPDATE(fix_log_state)); @@ -4832,7 +4835,7 @@ static const char *log_slow_filter_names[]= static Sys_var_set Sys_log_slow_filter( "log_slow_filter", "Log only certain types of queries", - SESSION_VAR(log_slow_filter), CMD_LINE(REQUIRED_ARG), + NO_SET_STMT SESSION_VAR(log_slow_filter), CMD_LINE(REQUIRED_ARG), log_slow_filter_names, DEFAULT(MAX_SET(array_elements(log_slow_filter_names)-1))); @@ -4879,7 +4882,7 @@ static Sys_var_ulong Sys_log_slow_rate_limit( "Write to slow log every #th slow query. Set to 1 to log everything. " "Increase it to reduce the size of the slow or the performance impact " "of slow logging", - SESSION_VAR(log_slow_rate_limit), CMD_LINE(REQUIRED_ARG), + NO_SET_STMT SESSION_VAR(log_slow_rate_limit), CMD_LINE(REQUIRED_ARG), VALID_RANGE(1, UINT_MAX), DEFAULT(1), BLOCK_SIZE(1)); static const char *log_slow_verbosity_names[]= { "innodb", "query_plan", @@ -4887,7 +4890,7 @@ static const char *log_slow_verbosity_names[]= { "innodb", "query_plan", static Sys_var_set Sys_log_slow_verbosity( "log_slow_verbosity", "Verbosity level for the slow log", - SESSION_VAR(log_slow_verbosity), CMD_LINE(REQUIRED_ARG), + NO_SET_STMT SESSION_VAR(log_slow_verbosity), CMD_LINE(REQUIRED_ARG), log_slow_verbosity_names, DEFAULT(LOG_SLOW_VERBOSITY_INIT)); static Sys_var_ulong Sys_join_cache_level( diff --git a/sql/sys_vars.h b/sql/sys_vars.h index da93b765d0f..645d57e3e17 100644 --- a/sql/sys_vars.h +++ b/sql/sys_vars.h @@ -57,6 +57,7 @@ // this means that Sys_var_charptr initial value was malloc()ed #define PREALLOCATED sys_var::ALLOCATED+ #define PARSED_EARLY sys_var::PARSE_EARLY+ +#define NO_SET_STMT sys_var::NO_SET_STATEMENT+ /* Sys_var_bit meaning is reversed, like in |