diff options
Diffstat (limited to 'mysql-test/suite/sys_vars/r')
31 files changed, 388 insertions, 225 deletions
diff --git a/mysql-test/suite/sys_vars/r/binlog_format_basic.result b/mysql-test/suite/sys_vars/r/binlog_format_basic.result index 72ba35cd753..0d76d9686e3 100644 --- a/mysql-test/suite/sys_vars/r/binlog_format_basic.result +++ b/mysql-test/suite/sys_vars/r/binlog_format_basic.result @@ -2,6 +2,7 @@ SELECT @@GLOBAL.binlog_format; @@GLOBAL.binlog_format STATEMENT '#---------------------BS_STVARS_002_01----------------------#' +SET @start_value= @@global.binlog_format; SELECT COUNT(@@GLOBAL.binlog_format); COUNT(@@GLOBAL.binlog_format) 1 @@ -12,19 +13,13 @@ COUNT(@@SESSION.binlog_format) 1 Expected '#---------------------BS_STVARS_002_02----------------------#' SET @@GLOBAL.binlog_format=1; -Expected error 'Read only variable' -Bug: Writeable static variable -SELECT COUNT(@@GLOBAL.binlog_format); -COUNT(@@GLOBAL.binlog_format) -1 -1 Expected +SELECT @@GLOBAL.binlog_format; +@@GLOBAL.binlog_format +STATEMENT SET @@SESSION.binlog_format=1; -Expected error 'Read only variable' -Bug: Writeable static variable -SELECT COUNT(@@SESSION.binlog_format); -COUNT(@@SESSION.binlog_format) -1 -1 Expected +SELECT @@SESSION.binlog_format; +@@SESSION.binlog_format +STATEMENT '#---------------------BS_STVARS_002_03----------------------#' SELECT @@GLOBAL.binlog_format = VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES @@ -37,14 +32,14 @@ COUNT(@@GLOBAL.binlog_format) 1 1 Expected SELECT COUNT(VARIABLE_VALUE) -FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES +FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='binlog_format'; COUNT(VARIABLE_VALUE) 1 1 Expected '#---------------------BS_STVARS_002_04----------------------#' -SELECT @@SESSION.binlog_format = VARIABLE_VALUE -FROM INFORMATION_SCHEMA.SESSION_VARIABLES +SELECT @@SESSION.binlog_format = VARIABLE_VALUE +FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME='binlog_format'; @@SESSION.binlog_format = VARIABLE_VALUE 1 @@ -54,7 +49,7 @@ COUNT(@@SESSION.binlog_format) 1 1 Expected SELECT COUNT(VARIABLE_VALUE) -FROM INFORMATION_SCHEMA.SESSION_VARIABLES +FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME='binlog_format'; COUNT(VARIABLE_VALUE) 1 @@ -76,3 +71,4 @@ SELECT COUNT(@@GLOBAL.binlog_format); COUNT(@@GLOBAL.binlog_format) 1 1 Expected +SET @@global.binlog_format= @start_value; diff --git a/mysql-test/suite/sys_vars/r/character_set_connection_func.result b/mysql-test/suite/sys_vars/r/character_set_connection_func.result index 2f5d28ec8af..6fc33a4f369 100644 --- a/mysql-test/suite/sys_vars/r/character_set_connection_func.result +++ b/mysql-test/suite/sys_vars/r/character_set_connection_func.result @@ -15,7 +15,7 @@ utf8 '#--------------------FN_DYNVARS_011_02-------------------------#' 'connection default' DROP TABLE IF EXISTS t1; -CREATE TABLE t1(b CHAR(40) character set utf8); +CREATE TABLE t1(b CHAR(40) CHARACTER SET utf8); '--verify that character_set_connection converts character_set_client--' SET @@session.character_set_client = utf8; SET @@session.character_set_results = utf8; @@ -53,3 +53,4 @@ SET @@global.character_set_connection = @global_character_set_connection; SET @@session.character_set_connection = @session_character_set_connection; SET @@session.character_set_client = @session_character_set_client; SET @@session.character_set_results = @session_character_set_results; +DROP TABLE t1; diff --git a/mysql-test/suite/sys_vars/r/character_set_database_func.result b/mysql-test/suite/sys_vars/r/character_set_database_func.result index 95b65eacfd2..bc512c73855 100644 --- a/mysql-test/suite/sys_vars/r/character_set_database_func.result +++ b/mysql-test/suite/sys_vars/r/character_set_database_func.result @@ -59,14 +59,14 @@ USE test; CREATE TABLE t1 (a CHAR(4) CHARACTER SET utf8) ENGINE=MyISAM CHARACTER SET utf8; 'Verify with latin'; SET @@session.character_set_database = latin1; -LOAD DATA INFILE 'MYSQLTEST_VARDIR/std_data_ln/charset_utf8.txt' INTO TABLE t1; +LOAD DATA INFILE 'MYSQLTEST_VARDIR/std_data/charset_utf8.txt' INTO TABLE t1; SELECT count(*) FROM t1 WHERE CHAR_LENGTH(a)>1; count(*) 2433 TRUNCATE TABLE t1; 'Verify with utf8'; SET @@session.character_set_database = utf8; -LOAD DATA INFILE 'MYSQLTEST_VARDIR/std_data_ln/charset_utf8.txt' INTO TABLE t1; +LOAD DATA INFILE 'MYSQLTEST_VARDIR/std_data/charset_utf8.txt' INTO TABLE t1; SELECT count(*) FROM t1 WHERE CHAR_LENGTH(a)>1; count(*) 1 diff --git a/mysql-test/suite/sys_vars/r/completion_type_func.result b/mysql-test/suite/sys_vars/r/completion_type_func.result index 282bd1006cd..daee738c10d 100644 --- a/mysql-test/suite/sys_vars/r/completion_type_func.result +++ b/mysql-test/suite/sys_vars/r/completion_type_func.result @@ -1,67 +1,67 @@ -drop table if exists t1; +DROP TABLE IF EXISTS t1; ## Creating new table ## CREATE TABLE t1 ( -id INT NOT NULL auto_increment, +id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id), -name varchar(30) +name VARCHAR(30) ) ENGINE = INNODB; '#--------------------FN_DYNVARS_017_01-------------------------#' ## Creating new connection ## -INSERT into t1(name) values('Record_1'); +INSERT INTO t1(name) VALUES('Record_1'); SET @@autocommit = 0; -SELECT * from t1; +SELECT * FROM t1; id name 1 Record_1 ## Setting value of variable to 0 ## SET @@session.completion_type = 0; ## Here commit & rollback should work normally ## START TRANSACTION; -SELECT * from t1; +SELECT * FROM t1; id name 1 Record_1 -INSERT into t1(name) values('Record_2'); -INSERT into t1(name) values('Record_3'); -SELECT * from t1; +INSERT INTO t1(name) VALUES('Record_2'); +INSERT INTO t1(name) VALUES('Record_3'); +SELECT * FROM t1; id name 1 Record_1 2 Record_2 3 Record_3 -DELETE FROM t1 where id = 2; -SELECT * from t1; +DELETE FROM t1 WHERE id = 2; +SELECT * FROM t1; id name 1 Record_1 3 Record_3 START TRANSACTION; -SELECT * from t1; +SELECT * FROM t1; id name 1 Record_1 3 Record_3 -INSERT into t1(name) values('Record_4'); -INSERT into t1(name) values('Record_5'); +INSERT INTO t1(name) VALUES('Record_4'); +INSERT INTO t1(name) VALUES('Record_5'); COMMIT; '#--------------------FN_DYNVARS_017_02-------------------------#' SET @@session.completion_type = 2; ## Here commit should work as COMMIT RELEASE ## START TRANSACTION; -SELECT * from t1; +SELECT * FROM t1; id name 1 Record_1 3 Record_3 4 Record_4 5 Record_5 -INSERT into t1(name) values('Record_6'); -INSERT into t1(name) values('Record_7'); +INSERT INTO t1(name) VALUES('Record_6'); +INSERT INTO t1(name) VALUES('Record_7'); COMMIT; ## Inserting rows should give error here because connection should ## ## disconnect after using COMMIT ## -INSERT into t1(name) values('Record_4'); +INSERT INTO t1(name) VALUES('Record_4'); Got one of the listed errors ## Creating new connection test_con2 ## SET @@session.completion_type = 2; ## Inserting rows and using Rollback which should Rollback & release ## START TRANSACTION; -SELECT * from t1; +SELECT * FROM t1; id name 1 Record_1 3 Record_3 @@ -69,8 +69,9 @@ id name 5 Record_5 6 Record_6 7 Record_7 -INSERT into t1(name) values('Record_8'); -INSERT into t1(name) values('Record_9'); +INSERT INTO t1(name) VALUES('Record_8'); +INSERT INTO t1(name) VALUES('Record_9'); ROLLBACK; -INSERT into t1(name) values('Record_4'); +INSERT INTO t1(name) VALUES('Record_4'); Got one of the listed errors +DROP TABLE t1; diff --git a/mysql-test/suite/sys_vars/r/concurrent_insert_func.result b/mysql-test/suite/sys_vars/r/concurrent_insert_func.result index 87573bb2873..774775a8287 100644 --- a/mysql-test/suite/sys_vars/r/concurrent_insert_func.result +++ b/mysql-test/suite/sys_vars/r/concurrent_insert_func.result @@ -1,73 +1,121 @@ -drop table if exists t1; +DROP TABLE IF EXISTS t1; ## Creating new table ## CREATE TABLE t1 ( -name varchar(30) +name VARCHAR(30) ); '#--------------------FN_DYNVARS_018_01-------------------------#' +SET @start_value= @@global.concurrent_insert; ## Setting initial value of variable to 1 ## SET @@global.concurrent_insert = 1; -INSERT into t1(name) values('Record_1'); -INSERT into t1(name) values('Record_2'); -INSERT into t1(name) values('Record_3'); +INSERT INTO t1(name) VALUES('Record_1'); +INSERT INTO t1(name) VALUES('Record_2'); +INSERT INTO t1(name) VALUES('Record_3'); ## locking table ## -lock table t1 read local; -## Creating new connection to insert some rows in table ## +LOCK TABLE t1 READ LOCAL; +## Creating new connection to insert some rows in table ## +connection test_con1; ## New records should come at the end of all rows ## -INSERT into t1(name) values('Record_4'); -SELECT * from t1; +INSERT INTO t1(name) VALUES('Record_4'); +SELECT * FROM t1; name Record_1 Record_2 Record_3 Record_4 ## unlocking tables ## -unlock tables; -## deleting record to create hole in table ## -DELETE from t1 where name ='Record_2'; +connection default; +UNLOCK TABLES; +## deleting record to create hole in table ## +DELETE FROM t1 WHERE name ='Record_2'; '#--------------------FN_DYNVARS_018_02-------------------------#' +LOCK TABLE t1 READ LOCAL; +connection test_con1; +SET @@global.concurrent_insert=1; +## send INSERT which should be blocked until unlock of the table ## +INSERT INTO t1(name) VALUES('Record_7'); +connection default; +## show processlist info and state ## +SELECT state,info FROM INFORMATION_SCHEMA.PROCESSLIST +WHERE state= "Locked" AND info LIKE "INSERT INTO t1%"; +state info +Locked INSERT INTO t1(name) VALUES('Record_7') +## table contents befor UNLOCK ## +SELECT * FROM t1; +name +Record_1 +Record_3 +Record_4 +UNLOCK TABLES; +## table contens after UNLOCK ## +SELECT * FROM t1; +name +Record_1 +Record_7 +Record_3 +Record_4 +INSERT INTO t1(name) VALUES('Record_6'); +connection test_con1; +SELECT * FROM t1; +name +Record_1 +Record_7 +Record_3 +Record_4 +Record_6 +connection default; '#--------------------FN_DYNVARS_018_03-------------------------#' ## lock table and connect with connection1 ## -lock table t1 read local; +LOCK TABLE t1 READ LOCAL; +connection test_con1; ## setting value of concurrent_insert to 2 ## SET @@global.concurrent_insert=2; ## Inserting record in table, record should go at the end of the table ## -INSERT into t1(name) values('Record_5'); -SELECT * from t1; +INSERT INTO t1(name) VALUES('Record_5'); +SELECT * FROM t1; name Record_1 +Record_7 Record_3 Record_4 +Record_6 Record_5 SELECT @@concurrent_insert; @@concurrent_insert 2 -## Switching to default connection ## +connection default; ## Unlocking table ## -unlock tables; -SELECT * from t1; +UNLOCK TABLES; +SELECT * FROM t1; name Record_1 +Record_7 Record_3 Record_4 +Record_6 Record_5 ## Inserting new row, this should go in the hole ## -INSERT into t1(name) values('Record_6'); -SELECT * from t1; +INSERT INTO t1(name) VALUES('Record_6'); +SELECT * FROM t1; name Record_1 -Record_6 +Record_7 Record_3 Record_4 +Record_6 Record_5 +Record_6 ## connection test_con1 ## -DELETE from t1 where name ='Record_3'; -SELECT * from t1; +DELETE FROM t1 WHERE name ='Record_3'; +SELECT * FROM t1; name Record_1 -Record_6 +Record_7 Record_4 +Record_6 Record_5 +Record_6 ## Dropping table ## -DROP table t1; +DROP TABLE t1; ## Disconnecting connection ## +SET @@global.concurrent_insert= @start_value; diff --git a/mysql-test/suite/sys_vars/r/delay_key_write_func.result b/mysql-test/suite/sys_vars/r/delay_key_write_func.result index ca4e25e08b1..d55cca7ab8e 100644 --- a/mysql-test/suite/sys_vars/r/delay_key_write_func.result +++ b/mysql-test/suite/sys_vars/r/delay_key_write_func.result @@ -1,4 +1,5 @@ '#--------------------FN_DYNVARS_023_01-------------------------#' +SET @start_value= @@global.delay_key_write; SET @@global.delay_key_write = ON; SELECT @@global.delay_key_write; @@global.delay_key_write @@ -28,8 +29,8 @@ Key_writes 9 SHOW STATUS LIKE 'Key_write_requests'; Variable_name Value Key_write_requests 9 -select count(*) from t1; -count(*) +SELECT COUNT(*) FROM t1; +COUNT(*) 9 '----check when delay_key_write is ON---' SET @@global.delay_key_write = ON; @@ -44,8 +45,8 @@ Key_writes 0 SHOW STATUS LIKE 'Key_write_requests'; Variable_name Value Key_write_requests 9 -select count(*) from t1; -count(*) +SELECT COUNT(*) FROM t1; +COUNT(*) 9 '----check when delay_key_write is ALL---' SET @@global.delay_key_write = ALL; @@ -60,8 +61,9 @@ Key_writes 0 SHOW STATUS LIKE 'Key_write_requests'; Variable_name Value Key_write_requests 9 -select count(*) from t1; -count(*) +SELECT COUNT(*) FROM t1; +COUNT(*) 9 DROP PROCEDURE sp_addRecords; DROP TABLE t1; +SET @@global.delay_key_write= @start_value; diff --git a/mysql-test/suite/sys_vars/r/general_log_file_basic.result b/mysql-test/suite/sys_vars/r/general_log_file_basic.result index 6847d92688e..48a8d90b55e 100644 --- a/mysql-test/suite/sys_vars/r/general_log_file_basic.result +++ b/mysql-test/suite/sys_vars/r/general_log_file_basic.result @@ -6,7 +6,7 @@ test.log SET @@global.general_log_file = DEFAULT; SELECT RIGHT(@@global.general_log_file,10) AS log_file; log_file -master.log +mysqld.log '#--------------------FN_DYNVARS_004_02------------------------#' SET @@global.general_log_file = mytest.log; SET @@global.general_log_file = 12; @@ -17,3 +17,4 @@ FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='general_log_file'; @@global.general_log_file = VARIABLE_VALUE 1 +SET @@global.general_log_file= @start_value; diff --git a/mysql-test/suite/sys_vars/r/general_log_file_func.result b/mysql-test/suite/sys_vars/r/general_log_file_func.result index 42ef723e644..482a03c1a97 100644 --- a/mysql-test/suite/sys_vars/r/general_log_file_func.result +++ b/mysql-test/suite/sys_vars/r/general_log_file_func.result @@ -1,8 +1,8 @@ -drop table if exists t1; +DROP TABLE IF EXISTS t1; ## Creating new table ## CREATE TABLE t1 ( -id INT NOT NULL auto_increment, +id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id), name VARCHAR(30) ); @@ -10,10 +10,10 @@ name VARCHAR(30) SELECT @@general_log_file; @@general_log_file mysql-test.log -INSERT into t1(name) values('Record_1'); -INSERT into t1(name) values('Record_2'); -INSERT into t1(name) values('Record_3'); -INSERT into t1(name) values('Record_4'); +INSERT INTO t1(name) VALUES('Record_1'); +INSERT INTO t1(name) VALUES('Record_2'); +INSERT INTO t1(name) VALUES('Record_3'); +INSERT INTO t1(name) VALUES('Record_4'); ## Verifying general log file ## ## Dropping table ## DROP TABLE t1; diff --git a/mysql-test/suite/sys_vars/r/general_log_func.result b/mysql-test/suite/sys_vars/r/general_log_func.result index 3bbd93f7207..39ba90265d5 100644 --- a/mysql-test/suite/sys_vars/r/general_log_func.result +++ b/mysql-test/suite/sys_vars/r/general_log_func.result @@ -25,8 +25,12 @@ SELECT @@general_log; ## Inserting some Records & Verifying output in log ## INSERT into t1(name) values('Record_3'); INSERT into t1(name) values('Record_4'); -## There should be no difference, case should pass ## -## This case is failing which shows that mysql is writing in general ## -## log when we set general_log to ON ## +## There should be a difference ## +SET @@global.max_allowed_packet= 1024*1024*1024; +SET @orig_file= load_file('MYSQLD_LOGFILE.orig'); +SET @copy_file= load_file('MYSQLD_LOGFILE.copy'); +SELECT STRCMP(@orig_file, @copy_file); +STRCMP(@orig_file, @copy_file) +1 ## Dropping tables ## DROP TABLE t1; diff --git a/mysql-test/suite/sys_vars/r/innodb_autoextend_increment_basic.result b/mysql-test/suite/sys_vars/r/innodb_autoextend_increment_basic.result index dafc71fd25d..69a8b6cc430 100644 --- a/mysql-test/suite/sys_vars/r/innodb_autoextend_increment_basic.result +++ b/mysql-test/suite/sys_vars/r/innodb_autoextend_increment_basic.result @@ -1,7 +1,4 @@ SET @global_start_value = @@global.innodb_autoextend_increment ; -SELECT @global_start_value; -@global_start_value -8 '#--------------------FN_DYNVARS_046_01------------------------#' SET @@global.innodb_autoextend_increment = 0; Warnings: @@ -57,16 +54,16 @@ SELECT @@global.innodb_autoextend_increment; @@global.innodb_autoextend_increment 1000 '#----------------------FN_DYNVARS_046_05------------------------#' -SELECT @@global.innodb_autoextend_increment = VARIABLE_VALUE -FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES +SELECT @@global.innodb_autoextend_increment = VARIABLE_VALUE +FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_autoextend_increment '; @@global.innodb_autoextend_increment = VARIABLE_VALUE 1 SELECT @@global.innodb_autoextend_increment ; @@global.innodb_autoextend_increment 1000 -SELECT VARIABLE_VALUE -FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES +SELECT VARIABLE_VALUE +FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_autoextend_increment '; VARIABLE_VALUE 1000 @@ -92,3 +89,4 @@ Warning 1292 Truncated incorrect autoextend_increment value: '0' SELECT @@global.innodb_autoextend_increment ; @@global.innodb_autoextend_increment 1 +SET @@global.innodb_autoextend_increment = @global_start_value; diff --git a/mysql-test/suite/sys_vars/r/innodb_autoinc_lock_mode_func.result b/mysql-test/suite/sys_vars/r/innodb_autoinc_lock_mode_func.result index 4c594558a6f..f8082cfaedb 100644 --- a/mysql-test/suite/sys_vars/r/innodb_autoinc_lock_mode_func.result +++ b/mysql-test/suite/sys_vars/r/innodb_autoinc_lock_mode_func.result @@ -9,14 +9,15 @@ DROP TABLE IF EXISTS t1; CREATE TABLE t1( a INT AUTO_INCREMENT PRIMARY KEY, b CHAR -)ENGINE=INNODB, AUTO_INCREMENT=100; +) ENGINE=INNODB, AUTO_INCREMENT=100; INSERT INTO t1 (a,b) VALUES (5,'a'), (NULL,'b'), (1,'c'), (NULL,'d'); INSERT INTO t1 (a,b) VALUES (NULL,'e'); 'the new auto incremented value should be 104' -SELECT * from t1; +SELECT * FROM t1; a b 1 c 5 a 100 b 101 d 104 e +DROP TABLE t1; diff --git a/mysql-test/suite/sys_vars/r/innodb_fast_shutdown_basic.result b/mysql-test/suite/sys_vars/r/innodb_fast_shutdown_basic.result index adbfb6ec867..4b923f4bdd2 100644 --- a/mysql-test/suite/sys_vars/r/innodb_fast_shutdown_basic.result +++ b/mysql-test/suite/sys_vars/r/innodb_fast_shutdown_basic.result @@ -93,14 +93,14 @@ ERROR HY000: Variable 'innodb_fast_shutdown' is a GLOBAL variable and should be SET @@local.innodb_fast_shutdown = 0; ERROR HY000: Variable 'innodb_fast_shutdown' is a GLOBAL variable and should be set with SET GLOBAL '#----------------------FN_DYNVARS_042_06------------------------#' -SELECT count(VARIABLE_VALUE) AS res_is_0 -FROM INFORMATION_SCHEMA.SESSION_VARIABLES +SELECT count(VARIABLE_VALUE) AS res_is_0 +FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME='innodb_fast_shutdown'; res_is_0 1 '#----------------------FN_DYNVARS_042_07------------------------#' SELECT @@global.innodb_fast_shutdown = -VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES +VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_fast_shutdown'; @@global.innodb_fast_shutdown = VARIABLE_VALUE @@ -125,3 +125,7 @@ SET @@global.innodb_fast_shutdown = FALSE; SELECT @@global.innodb_fast_shutdown; @@global.innodb_fast_shutdown 0 +SET @@global.innodb_fast_shutdown = @global_start_value; +SELECT @@global.innodb_fast_shutdown; +@@global.innodb_fast_shutdown +1 diff --git a/mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_func.result b/mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_func.result index 4f6d42fb716..d46c0d4a70a 100644 --- a/mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_func.result +++ b/mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_func.result @@ -1,13 +1,12 @@ +SET @start_value= @@global.innodb_max_dirty_pages_pct; '#--------------------FN_DYNVARS_044_02-------------------------#' SET @@global.innodb_max_dirty_pages_pct = 80; 'connect (con1,localhost,root,,,,)' -'connection con1' SELECT @@global.innodb_max_dirty_pages_pct; @@global.innodb_max_dirty_pages_pct 80 SET @@global.innodb_max_dirty_pages_pct = 70; 'connect (con2,localhost,root,,,,)' -'connection con2' SELECT @@global.innodb_max_dirty_pages_pct; @@global.innodb_max_dirty_pages_pct 70 @@ -19,7 +18,6 @@ FLUSH STATUS; CALL add_until(10); FLUSH TABLES; CALL add_records(500); -'--sleep 5' 'We expect dirty pages pct to be BELOW_MAX' CALL check_pct(10); PCT_VALUE @@ -28,3 +26,5 @@ DROP PROCEDURE add_records; DROP PROCEDURE add_until; DROP PROCEDURE check_pct; DROP FUNCTION dirty_pct; +DROP TABLE t1; +SET @@global.innodb_max_dirty_pages_pct= @start_value; diff --git a/mysql-test/suite/sys_vars/r/innodb_table_locks_func.result b/mysql-test/suite/sys_vars/r/innodb_table_locks_func.result index 22a52ce17fd..824d196d295 100644 --- a/mysql-test/suite/sys_vars/r/innodb_table_locks_func.result +++ b/mysql-test/suite/sys_vars/r/innodb_table_locks_func.result @@ -1,4 +1,8 @@ '#--------------------FN_DYNVARS_048_01-------------------------#' +SET @start_value= @@global.innodb_table_locks; +SELECT @start_value; +@start_value +1 SET @@global.innodb_table_locks = OFF; 'connect (con1,localhost,root,,,,)' 'connection con1' @@ -30,3 +34,7 @@ COMMIT; 'CONNECTION con2' UNLOCK tables; DROP TABLE t1; +SET @@global.innodb_table_locks= @start_value; +SELECT @@global.innodb_table_locks; +@@global.innodb_table_locks +1 diff --git a/mysql-test/suite/sys_vars/r/key_buffer_size_func.result b/mysql-test/suite/sys_vars/r/key_buffer_size_func.result index e2eff04f030..d5b79c260a0 100644 --- a/mysql-test/suite/sys_vars/r/key_buffer_size_func.result +++ b/mysql-test/suite/sys_vars/r/key_buffer_size_func.result @@ -1,12 +1,13 @@ -drop table if exists t1; +DROP TABLE IF EXISTS t1; ## Creating new table t1 ## CREATE TABLE t1 ( -id INT NOT NULL auto_increment, +id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id), -rollno int NOT NULL, +rollno INT NOT NULL, name VARCHAR(30) ); +SET @start_value= @@global.key_buffer_size; FLUSH STATUS; '#--------------------FN_DYNVARS_055_01-------------------------#' ## Setting initial value of variable to 131072 ## @@ -18,19 +19,43 @@ SELECT @@global.key_buffer_size; @@global.key_buffer_size 131072 ## Inserting some rows in table ## -INSERT into t1(rollno, name) values(1, 'Record_1'); -INSERT into t1(rollno, name) values(2, 'Record_2'); -INSERT into t1(rollno, name) values(1, 'Record_3'); -INSERT into t1(rollno, name) values(3, 'Record_4'); -INSERT into t1(rollno, name) values(1, 'Record_5'); -INSERT into t1(rollno, name) values(3, 'Record_6'); -INSERT into t1(rollno, name) values(4, 'Record_7'); -INSERT into t1(rollno, name) values(4, 'Record_8'); -INSERT into t1(rollno, name) values(4, 'Record_9'); -INSERT into t1(rollno, name) values(4, 'Record_10'); -## Verifying status of reading & writing variables ## +INSERT INTO t1(rollno, name) VALUES(1, 'Record_1'); +INSERT INTO t1(rollno, name) VALUES(2, 'Record_2'); +INSERT INTO t1(rollno, name) VALUES(1, 'Record_3'); +INSERT INTO t1(rollno, name) VALUES(3, 'Record_4'); +INSERT INTO t1(rollno, name) VALUES(1, 'Record_5'); +INSERT INTO t1(rollno, name) VALUES(3, 'Record_6'); +INSERT INTO t1(rollno, name) VALUES(4, 'Record_7'); +INSERT INTO t1(rollno, name) VALUES(4, 'Record_8'); +INSERT INTO t1(rollno, name) VALUES(4, 'Record_9'); +INSERT INTO t1(rollno, name) VALUES(4, 'Record_10'); +## Key_reads must be zero (no disk access) ## +show status like 'Key_reads'; +Variable_name Value +Key_reads 0 ## Switching to connection test_con2 ## -## Verifying status of reading & writing variables ## +## Key_reads must be zero (no disk access) ## +show status like 'Key_reads'; +Variable_name Value +Key_reads 0 +SET @@global.key_buffer_size = 36; +## Connecting with connection test_con1 ## +## Inserting some rows in table ## +INSERT INTO t1(rollno, name) VALUES(5, 'Record_11'); +INSERT INTO t1(rollno, name) VALUES(6, 'Record_12'); +INSERT INTO t1(rollno, name) VALUES(5, 'Record_13'); +INSERT INTO t1(rollno, name) VALUES(7, 'Record_14'); +INSERT INTO t1(rollno, name) VALUES(5, 'Record_15'); +INSERT INTO t1(rollno, name) VALUES(7, 'Record_16'); +INSERT INTO t1(rollno, name) VALUES(8, 'Record_17'); +INSERT INTO t1(rollno, name) VALUES(8, 'Record_18'); +INSERT INTO t1(rollno, name) VALUES(8, 'Record_19'); +INSERT INTO t1(rollno, name) VALUES(8, 'Record_20'); +## Key_reads must be zero (no disk access) ## +show status like 'Key_reads'; +Variable_name Value +Key_reads 10 ## Dropping table ## -DROP table if exists t1; +DROP TABLE IF EXISTS t1; ## Disconnecting both the connections ## +SET @@global.key_buffer_size= @start_value; diff --git a/mysql-test/suite/sys_vars/r/local_infile_func.result b/mysql-test/suite/sys_vars/r/local_infile_func.result index 52b922bc6d9..66a1e5544fb 100644 --- a/mysql-test/suite/sys_vars/r/local_infile_func.result +++ b/mysql-test/suite/sys_vars/r/local_infile_func.result @@ -18,7 +18,7 @@ SET @@global.local_infile = 1; DROP TABLE IF EXISTS t1; create table t1(a int); LOAD DATA LOCAL INFILE -'MYSQLTEST_VARDIR/std_data_ln/numbers.txt' INTO TABLE test.t1; +'MYSQLTEST_VARDIR/std_data/numbers.txt' INTO TABLE test.t1; SELECT count(*) from t1; count(*) 9 @@ -27,7 +27,7 @@ SET @@global.local_infile = 0; DROP TABLE IF EXISTS t1; create table t1(a int); LOAD DATA LOCAL INFILE -'MYSQLTEST_VARDIR/std_data_ln/numbers.txt' INTO TABLE test.t1; +'MYSQLTEST_VARDIR/std_data/numbers.txt' INTO TABLE test.t1; ERROR 42000: The used command is not allowed with this MySQL version SELECT count(*) from t1; count(*) diff --git a/mysql-test/suite/sys_vars/r/log_basic.result b/mysql-test/suite/sys_vars/r/log_basic.result index edda72fa91e..18a12b72437 100644 --- a/mysql-test/suite/sys_vars/r/log_basic.result +++ b/mysql-test/suite/sys_vars/r/log_basic.result @@ -1,4 +1,5 @@ '#--------------------FN_DYNVARS_062_01------------------#' +SET @start_log= @@global.log; SELECT @@global.log AS INIT_VALUE; INIT_VALUE 1 @@ -10,8 +11,11 @@ SET global general_log = 0; 'Bug# 34832: log is a system but it is not accessible using SET @@global.log;' 'SET GLOBAL log; and SELECT @@global.log. SHOW VARIABLES shows the value of log.' '#--------------------FN_DYNVARS_062_02-------------------------#' -SELECT VARIABLE_VALUE -FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES +SELECT VARIABLE_VALUE +FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='log'; VARIABLE_VALUE OFF +SET @@global.log= @start_log; +Warnings: +Warning 1287 The syntax '@@log' is deprecated and will be removed in MySQL 7.0. Please use '@@general_log' instead diff --git a/mysql-test/suite/sys_vars/r/log_bin_trust_function_creators_func.result b/mysql-test/suite/sys_vars/r/log_bin_trust_function_creators_func.result index 50de0466a0d..33443df5ae4 100644 --- a/mysql-test/suite/sys_vars/r/log_bin_trust_function_creators_func.result +++ b/mysql-test/suite/sys_vars/r/log_bin_trust_function_creators_func.result @@ -1,11 +1,12 @@ drop table if exists t1; '#--------------------FN_DYNVARS_063_01-------------------------#' +SET @start_value= @@global.log_bin_trust_function_creators; ## Creating new user tt ## -CREATE user tt@localhost; +CREATE USER tt@localhost; ## Setting value of variable to 0 ## SET @@global.log_bin_trust_function_creators = 0; ## Creating new table t2 ## -create table t2 (a INT); +CREATE TABLE t2 (a INT); ## Creating & connecting with new connection test_con1 ## SELECT @@log_bin_trust_function_creators; @@log_bin_trust_function_creators @@ -13,7 +14,7 @@ SELECT @@log_bin_trust_function_creators; ## Creating new function f1 fails because no DETERMINISTIC ### CREATE FUNCTION f1(a INT) RETURNS INT BEGIN -IF (a < 3) THEN +IF (a < 3) THEN INSERT INTO t2 VALUES (a); END IF; RETURN 1; @@ -46,8 +47,8 @@ f1(a) 1 1 ## Dropping function f1 & table t1 ## -drop function f1; -drop table t1; +DROP FUNCTION f1; +DROP TABLE t1; '#--------------------FN_DYNVARS_063_02-------------------------#' ## Switching to default connection ## ## Setting value of variable to 1 ## @@ -63,7 +64,7 @@ SELECT @@sql_log_bin; ## Creating new function f1 ## CREATE FUNCTION f1(a INT) RETURNS INT BEGIN -IF (a < 3) THEN +IF (a < 3) THEN INSERT INTO t2 VALUES (a); END IF; RETURN 1; @@ -78,7 +79,9 @@ f1(a) 1 1 ## Dropping function f1 ## -drop function f1; +DROP FUNCTION f1; ## Dropping table t1 & t2 ## -drop table t1,t2; -## Disconnecting both the connections ## +DROP TABLE t1,t2; +## Disconnecting test_con2## +DROP USER tt@localhost; +SET @@global.log_bin_trust_function_creators= @start_value; diff --git a/mysql-test/suite/sys_vars/r/log_queries_not_using_indexes_basic.result b/mysql-test/suite/sys_vars/r/log_queries_not_using_indexes_basic.result index d05437e3f66..b19b11a4209 100644 --- a/mysql-test/suite/sys_vars/r/log_queries_not_using_indexes_basic.result +++ b/mysql-test/suite/sys_vars/r/log_queries_not_using_indexes_basic.result @@ -1,53 +1,104 @@ -'#---------------------BS_STVARS_041_01----------------------#' -SELECT COUNT(@@GLOBAL.log_queries_not_using_indexes); -COUNT(@@GLOBAL.log_queries_not_using_indexes) +SET @start_value= @@global.log_queries_not_using_indexes; +SET @@global.log_queries_not_using_indexes= DEFAULT; +SELECT @@global.log_queries_not_using_indexes; +@@global.log_queries_not_using_indexes +0 +SET @@global.log_queries_not_using_indexes= TRUE; +SELECT @@global.log_queries_not_using_indexes; +@@global.log_queries_not_using_indexes 1 -1 Expected -'#---------------------BS_STVARS_041_02----------------------#' -SET @@GLOBAL.log_queries_not_using_indexes=1; -Expected error 'Read only variable' -"BUG:It should give error on setting this variable as it is readonly variable" -SELECT COUNT(@@GLOBAL.log_queries_not_using_indexes); -COUNT(@@GLOBAL.log_queries_not_using_indexes) +SET @@global.log_queries_not_using_indexes= true; +SELECT @@global.log_queries_not_using_indexes; +@@global.log_queries_not_using_indexes 1 -1 Expected -'#---------------------BS_STVARS_041_03----------------------#' -SELECT @@GLOBAL.log_queries_not_using_indexes = VARIABLE_VALUE -FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES -WHERE VARIABLE_NAME='log_queries_not_using_indexes'; -@@GLOBAL.log_queries_not_using_indexes = VARIABLE_VALUE -0 -1 Expected -SELECT COUNT(@@GLOBAL.log_queries_not_using_indexes); -COUNT(@@GLOBAL.log_queries_not_using_indexes) +SET @@global.log_queries_not_using_indexes= 0; +SELECT @@global.log_queries_not_using_indexes; +@@global.log_queries_not_using_indexes +0 +SET @@global.log_queries_not_using_indexes= 1; +SELECT @@global.log_queries_not_using_indexes; +@@global.log_queries_not_using_indexes 1 -1 Expected -SELECT COUNT(VARIABLE_VALUE) -FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES -WHERE VARIABLE_NAME='log_queries_not_using_indexes'; -COUNT(VARIABLE_VALUE) +SET @goodvar= TRUE; +SET @@global.log_queries_not_using_indexes= @goodvar; +SELECT @@global.log_queries_not_using_indexes; +@@global.log_queries_not_using_indexes 1 -1 Expected -'#---------------------BS_STVARS_041_04----------------------#' -SELECT @@log_queries_not_using_indexes = @@GLOBAL.log_queries_not_using_indexes; -@@log_queries_not_using_indexes = @@GLOBAL.log_queries_not_using_indexes +SET GLOBAL log_queries_not_using_indexes= DEFAULT; +SELECT @@global.log_queries_not_using_indexes; +@@global.log_queries_not_using_indexes +0 +SET GLOBAL log_queries_not_using_indexes= ON; +SELECT @@global.log_queries_not_using_indexes; +@@global.log_queries_not_using_indexes 1 -1 Expected -'#---------------------BS_STVARS_041_05----------------------#' -SELECT COUNT(@@log_queries_not_using_indexes); -COUNT(@@log_queries_not_using_indexes) +SET GLOBAL log_queries_not_using_indexes= OFF; +SELECT @@global.log_queries_not_using_indexes; +@@global.log_queries_not_using_indexes +0 +SET GLOBAL log_queries_not_using_indexes= -0; +SELECT @@global.log_queries_not_using_indexes; +@@global.log_queries_not_using_indexes +0 +SET GLOBAL log_queries_not_using_indexes= 0.00; +SELECT @@global.log_queries_not_using_indexes; +@@global.log_queries_not_using_indexes +0 +SET GLOBAL log_queries_not_using_indexes= -0.0; +SELECT @@global.log_queries_not_using_indexes; +@@global.log_queries_not_using_indexes +0 +SET GLOBAL log_queries_not_using_indexes= 001.00; +SELECT @@global.log_queries_not_using_indexes; +@@global.log_queries_not_using_indexes 1 -1 Expected -SELECT COUNT(@@local.log_queries_not_using_indexes); -ERROR HY000: Variable 'log_queries_not_using_indexes' is a GLOBAL variable -Expected error 'Variable is a GLOBAL variable' -SELECT COUNT(@@SESSION.log_queries_not_using_indexes); -ERROR HY000: Variable 'log_queries_not_using_indexes' is a GLOBAL variable -Expected error 'Variable is a GLOBAL variable' -SELECT COUNT(@@GLOBAL.log_queries_not_using_indexes); -COUNT(@@GLOBAL.log_queries_not_using_indexes) +SET GLOBAL log_queries_not_using_indexes= +1.0; +SELECT @@global.log_queries_not_using_indexes; +@@global.log_queries_not_using_indexes 1 -1 Expected -SELECT log_queries_not_using_indexes = @@SESSION.log_queries_not_using_indexes; -ERROR 42S22: Unknown column 'log_queries_not_using_indexes' in 'field list' -Expected error 'Readonly variable' +SET GLOBAL log_queries_not_using_indexes= +0; +SELECT @@global.log_queries_not_using_indexes; +@@global.log_queries_not_using_indexes +0 +SET GLOBAL log_queries_not_using_indexes= +0.000000; +SELECT @@global.log_queries_not_using_indexes; +@@global.log_queries_not_using_indexes +0 +SET GLOBAL log_queries_not_using_indexes= 0000.00000; +SELECT @@global.log_queries_not_using_indexes; +@@global.log_queries_not_using_indexes +0 +SET GLOBAL log_queries_not_using_indexes= .0; +SELECT @@global.log_queries_not_using_indexes; +@@global.log_queries_not_using_indexes +0 +SET @@global.log_queries_not_using_indexes= 'DEFAULT'; +ERROR 42000: Variable 'log_queries_not_using_indexes' can't be set to the value of 'DEFAULT' +SET @@global.log_queries_not_using_indexes= 'true'; +ERROR 42000: Variable 'log_queries_not_using_indexes' can't be set to the value of 'true' +SET @@global.log_queries_not_using_indexes= BLABLA; +ERROR 42000: Variable 'log_queries_not_using_indexes' can't be set to the value of 'BLABLA' +SET @@global.log_queries_not_using_indexes= 25; +ERROR 42000: Variable 'log_queries_not_using_indexes' can't be set to the value of '25' +SET @@global.log_queries_not_using_indexes= 12.34; +ERROR 42000: Variable 'log_queries_not_using_indexes' can't be set to the value of '12' +SET GLOBAL log_queries_not_using_indexes= -1; +ERROR 42000: Variable 'log_queries_not_using_indexes' can't be set to the value of '-1' +SET @badvar= 'true'; +SET @@global.log_queries_not_using_indexes= @badvar; +ERROR 42000: Variable 'log_queries_not_using_indexes' can't be set to the value of 'true' +SET GLOBAL log_queries_not_using_indexes= 'DEFAULT'; +ERROR 42000: Variable 'log_queries_not_using_indexes' can't be set to the value of 'DEFAULT' +SET log_queries_not_using_indexes= TRUE; +ERROR HY000: Variable 'log_queries_not_using_indexes' is a GLOBAL variable and should be set with SET GLOBAL +SET SESSION log_queries_not_using_indexes= TRUE; +ERROR HY000: Variable 'log_queries_not_using_indexes' is a GLOBAL variable and should be set with SET GLOBAL +SET @@session.log_queries_not_using_indexes= TRUE; +ERROR HY000: Variable 'log_queries_not_using_indexes' is a GLOBAL variable and should be set with SET GLOBAL +SET LOCAL log_queries_not_using_indexes= TRUE; +ERROR HY000: Variable 'log_queries_not_using_indexes' is a GLOBAL variable and should be set with SET GLOBAL +SET @@global log_queries_not_using_indexes= TRUE; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'log_queries_not_using_indexes= TRUE' at line 1 +SET @@SESSION log_queries_not_using_indexes= TRUE; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'log_queries_not_using_indexes= TRUE' at line 1 +SET @@global.log_queries_not_using_indexes= @start_value; diff --git a/mysql-test/suite/sys_vars/r/max_allowed_packet_func.result b/mysql-test/suite/sys_vars/r/max_allowed_packet_func.result index 43da24fa280..0d682b65aea 100644 --- a/mysql-test/suite/sys_vars/r/max_allowed_packet_func.result +++ b/mysql-test/suite/sys_vars/r/max_allowed_packet_func.result @@ -1,4 +1,5 @@ -1048576 +SET @start_max_allowed_packet= @@global.max_allowed_packet; +SET @start_value= @@global.net_buffer_length; drop table if exists t1; ## Creating new table t1 ## CREATE TABLE t1 @@ -20,7 +21,7 @@ SELECT @@session.net_buffer_length; @@session.net_buffer_length 16384 '#--------------------FN_DYNVARS_070_02-------------------------#' -## Setting value of max_allowed packet and net_buffer_length to 1024 ## +## Setting value of max_allowed packet and net_buffer_length to 1024 ## SET @@global.max_allowed_packet = 1024; SET @@global.net_buffer_length = 1024; SELECT @@global.max_allowed_packet; @@ -30,9 +31,8 @@ SELECT @@global.net_buffer_length; @@global.net_buffer_length 1024 ## Creating new connection test_con1 ## -## Inserting and fetching data of length greater than 1024 ## +## Inserting and fetching data of length greater than 1024 ## INSERT into t1(name) values("aaassssssssddddddddffffffgggggggg, askdlfjalsdkjfalksdjflaksdjfalkjdflaksjdflakjdflajsflajflajdfalsjfdlajfladjslfajdflajdsflajsflakjsdfla;kjflsdjkf;aljfa;lkdsfjla;sjlkajffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllakjsdffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffa;;;;;;;;;;;;;;;;;;;;;;;;;;;dsklfjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjkljffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffdkskkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); drop table t1; -SET @@global.max_allowed_packet = 1048576; -## Server disconnects after this case and error occurs that Server ## -## has gone away ## +SET @@global.max_allowed_packet= @start_max_allowed_packet; +SET @@global.net_buffer_length= @start_value; diff --git a/mysql-test/suite/sys_vars/r/max_seeks_for_key_func.result b/mysql-test/suite/sys_vars/r/max_seeks_for_key_func.result index b4c863af17b..5a2441276ec 100644 --- a/mysql-test/suite/sys_vars/r/max_seeks_for_key_func.result +++ b/mysql-test/suite/sys_vars/r/max_seeks_for_key_func.result @@ -1,8 +1,9 @@ -drop table if exists t1; +DROP TABLE IF EXISTS t1; CREATE TABLE t1 -(a int auto_increment primary key, -b char(20) +(a INT AUTO_INCREMENT PRIMARY KEY, +b CHAR(20) ); +SET @start_value= @@global.max_seeks_for_key; '#--------------------FN_DYNVARS_084_01-------------------------#' SELECT @@global.max_seeks_for_key = 10; @@global.max_seeks_for_key = 10 @@ -14,8 +15,8 @@ SET @@global.max_seeks_for_key = 20; SELECT @@global.max_seeks_for_key; @@global.max_seeks_for_key 20 -INSERT into t1(b) values("AREc"); -explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b; +INSERT INTO t1(b) VALUES("AREc"); +EXPLAIN SELECT STRAIGHT_JOIN * FROM t1,t1 AS t2 WHERE t1.b = t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 system NULL NULL NULL NULL 1 1 SIMPLE t2 system NULL NULL NULL NULL 1 @@ -23,9 +24,9 @@ SET @@session.max_seeks_for_key = 2; SELECT @@session.max_seeks_for_key; @@session.max_seeks_for_key 2 -INSERT into t1(b) values("BREc"); -INSERT into t1(b) values("CRec"); -explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b; +INSERT INTO t1(b) VALUES("BREc"); +INSERT INTO t1(b) VALUES("CRec"); +EXPLAIN SELECT STRAIGHT_JOIN * FROM t1,t1 AS t2 WHERE t1.b = t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where; Using join buffer @@ -40,8 +41,8 @@ SET @@global.max_seeks_for_key = 20; SELECT @@global.max_seeks_for_key; @@global.max_seeks_for_key 20 -INSERT into t1(b) values("AREc"); -explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b; +INSERT INTO t1(b) VALUES("AREc"); +EXPLAIN SELECT STRAIGHT_JOIN * FROM t1,t1 AS t2 WHERE t1.b = t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer @@ -49,27 +50,28 @@ SET @@session.max_seeks_for_key = 2; SELECT @@session.max_seeks_for_key; @@session.max_seeks_for_key 2 -INSERT into t1(b) values("BREc"); -INSERT into t1(b) values("CRec"); -explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b; +INSERT INTO t1(b) VALUES("BREc"); +INSERT INTO t1(b) VALUES("CRec"); +EXPLAIN SELECT STRAIGHT_JOIN * FROM t1,t1 AS t2 WHERE t1.b = t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 6 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer -INSERT INTO t1 values(null,"test"); +INSERT INTO t1 VALUES(null,"test"); INSERT INTO t1 VALUES (null,"a"),(null,"a"),(null,"a"), (null,"a"),(null,"a"),(null,"a"),(null,"a"), (null,"a"),(null,"a"),(null,"a"); -explain SELECT STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b; +EXPLAIN SELECT STRAIGHT_JOIN * FROM t1,t1 AS t2 WHERE t1.b = t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 17 1 SIMPLE t2 ALL NULL NULL NULL NULL 17 Using where; Using join buffer -analyze table t1; +ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status OK SET MAX_SEEKS_FOR_KEY=1; -explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b; +EXPLAIN SELECT STRAIGHT_JOIN * FROM t1,t1 AS t2 WHERE t1.b = t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 17 1 SIMPLE t2 ALL NULL NULL NULL NULL 17 Using where; Using join buffer SET MAX_SEEKS_FOR_KEY=DEFAULT; -drop table t1; +DROP TABLE t1; +SET @@global.max_seeks_for_key= @start_value; diff --git a/mysql-test/suite/sys_vars/r/max_sort_length_func.result b/mysql-test/suite/sys_vars/r/max_sort_length_func.result index 04f5aa7f4f1..9254f1fadd7 100644 --- a/mysql-test/suite/sys_vars/r/max_sort_length_func.result +++ b/mysql-test/suite/sys_vars/r/max_sort_length_func.result @@ -1,3 +1,4 @@ +SET @start_value= @@global.max_sort_length; SET @session_max_sort_length = @@Session.max_sort_length; DROP TABLE IF EXISTS t; ** creating tables ** @@ -296,3 +297,4 @@ SET @@SESSION.max_sort_length = @session_max_sort_length; DROP TABLE IF EXISTS t; DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; +SET @@global.max_sort_length= @start_value; diff --git a/mysql-test/suite/sys_vars/r/myisam_data_pointer_size_func.result b/mysql-test/suite/sys_vars/r/myisam_data_pointer_size_func.result index 90d224d6584..82d9a7dad66 100644 --- a/mysql-test/suite/sys_vars/r/myisam_data_pointer_size_func.result +++ b/mysql-test/suite/sys_vars/r/myisam_data_pointer_size_func.result @@ -1,4 +1,5 @@ '#--------------------FN_DYNVARS_093_01-------------------------#' +SET @start_value= @@global.myisam_data_pointer_size; SET @@global.myisam_data_pointer_size = 2; 'connect (con1,localhost,root,,,,)' 'connection con1' @@ -40,4 +41,4 @@ count(*) 65536 DROP PROCEDURE sp_addRec; DROP TABLE t1; -SET @@global.myisam_data_pointer_size = default; +SET @@global.myisam_data_pointer_size= @start_value; diff --git a/mysql-test/suite/sys_vars/r/myisam_stats_method_func.result b/mysql-test/suite/sys_vars/r/myisam_stats_method_func.result index ace6887cc90..55d91b7417d 100644 --- a/mysql-test/suite/sys_vars/r/myisam_stats_method_func.result +++ b/mysql-test/suite/sys_vars/r/myisam_stats_method_func.result @@ -1,4 +1,5 @@ '#--------------------FN_DYNVARS_097_01-------------------------#' +SET @start_value = @@global.myisam_stats_method; SET @@global.myisam_stats_method = nulls_equal; 'connect (con1,localhost,root,,,,)' 'connection con1' @@ -11,11 +12,11 @@ nulls_equal '#--------------------FN_DYNVARS_097_02-------------------------#' 'connection default' DROP TABLE IF EXISTS t1; -CREATE TABLE t1 (a int, key (a)); +CREATE TABLE t1 (a INT, KEY (a)); INSERT INTO t1 VALUES (0),(1),(2),(3),(4); INSERT INTO t1 SELECT NULL FROM t1; 'default: NULLs considered unequal' -SET myisam_stats_method=nulls_unequal; +SET myisam_stats_method = nulls_unequal; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status OK @@ -23,7 +24,7 @@ SHOW INDEX FROM t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 1 a 1 a A 10 NULL NULL YES BTREE INSERT INTO t1 VALUES (11); -DELETE FROM t1 WHERE a=11; +DELETE FROM t1 WHERE a = 11; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK @@ -31,9 +32,9 @@ SHOW INDEX FROM t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 1 a 1 a A 10 NULL NULL YES BTREE 'Set nulls to be equal' -SET myisam_stats_method=nulls_equal; +SET myisam_stats_method = nulls_equal; INSERT INTO t1 VALUES (11); -DELETE FROM t1 WHERE a=11; +DELETE FROM t1 WHERE a = 11; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status OK @@ -41,7 +42,7 @@ SHOW INDEX FROM t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 1 a 1 a A 5 NULL NULL YES BTREE INSERT INTO t1 VALUES (11); -DELETE FROM t1 WHERE a=11; +DELETE FROM t1 WHERE a = 11; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK @@ -49,14 +50,14 @@ SHOW INDEX FROM t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 1 a 1 a A 5 NULL NULL YES BTREE 'Set nulls to be ignored' -SET myisam_stats_method=nulls_ignored; -SHOW variables LIKE 'myisam_stats_method'; +SET myisam_stats_method = nulls_ignored; +SHOW VARIABLES LIKE 'myisam_stats_method'; Variable_name Value myisam_stats_method nulls_ignored -drop TABLE t1; +DROP TABLE t1; CREATE TABLE t1 ( -a char(3), b char(4), c char(5), d char(6), -key(a,b,c,d) +a CHAR(3), b CHAR(4), c CHAR(5), d CHAR(6), +KEY(a,b,c,d) ); INSERT INTO t1 VALUES ('bcd','def1', NULL, 'zz'); INSERT INTO t1 VALUES ('bcd','def2', NULL, 'zz'); @@ -81,5 +82,6 @@ t1 1 a 1 a A 0 NULL NULL YES BTREE t1 1 a 2 b A 0 NULL NULL YES BTREE t1 1 a 3 c A 0 NULL NULL YES BTREE t1 1 a 4 d A 0 NULL NULL YES BTREE -SET myisam_stats_method=DEFAULT; +SET myisam_stats_method = DEFAULT; DROP TABLE t1; +SET @@global.myisam_stats_method= @start_value; diff --git a/mysql-test/suite/sys_vars/r/query_cache_limit_func.result b/mysql-test/suite/sys_vars/r/query_cache_limit_func.result index e54884cd5c3..bbbb273428a 100644 --- a/mysql-test/suite/sys_vars/r/query_cache_limit_func.result +++ b/mysql-test/suite/sys_vars/r/query_cache_limit_func.result @@ -1,6 +1,8 @@ ** Setup ** SET @global_query_cache_limit = @@global.query_cache_limit; +SET @global_query_cache_size = @@global.query_cache_size; +SET @global_query_cache_type = @@global.query_cache_type; ** warnings ** DROP TABLE IF EXISTS t; ** creating table ** @@ -48,7 +50,7 @@ Qcache_queries_in_cache 1 FLUSH STATUS; RESET QUERY CACHE; ** set cache limit ** -SET @@GLOBAL.query_cache_limit=0; +SET @@GLOBAL.query_cache_limit = 0; ** fetching results ** SELECT * FROM t; id c @@ -67,7 +69,7 @@ Qcache_queries_in_cache 0 0 Expected '#---------------------FN_DYNVARS_132_03----------------------#' ** set cache limit ** -SET @@GLOBAL.query_cache_limit=DEFAULT; +SET @@GLOBAL.query_cache_limit = DEFAULT; ** Reset cache values ** FLUSH STATUS; RESET QUERY CACHE; @@ -86,7 +88,7 @@ SHOW STATUS LIKE 'Qcache_queries_in_cache'; Variable_name Value Qcache_queries_in_cache 1 1 Expected -SET @@GLOBAL.query_cache_limit=0; +SET @@GLOBAL.query_cache_limit = 0; SHOW STATUS LIKE 'Qcache_not_cached'; Variable_name Value Qcache_not_cached 0 @@ -112,4 +114,6 @@ Variable_name Value Qcache_queries_in_cache 1 1 Expected SET @@GLOBAL.query_cache_limit = @global_query_cache_limit; +SET GLOBAL query_cache_size = @global_query_cache_size; +SET GLOBAL query_cache_type = @global_query_cache_type; DROP TABLE IF EXISTS t; diff --git a/mysql-test/suite/sys_vars/r/query_cache_type_func.result b/mysql-test/suite/sys_vars/r/query_cache_type_func.result index 94c31804248..af121244572 100644 --- a/mysql-test/suite/sys_vars/r/query_cache_type_func.result +++ b/mysql-test/suite/sys_vars/r/query_cache_type_func.result @@ -4,6 +4,7 @@ ** Connection con0 ** SET @start_global_value = @@GLOBAL.query_cache_type; SET @start_session_value = @@SESSION.query_cache_type; +SET @start_query_cache_size= @@global.query_cache_size; CREATE TABLE t1(id int, value varchar(10)); INSERT INTO t1 VALUES(1, 'val1'); INSERT INTO t1 VALUES(2, 'val2'); @@ -216,6 +217,7 @@ Disconnecting con1,con2,con3 ** Connection con0 ** SET @@GLOBAL.query_cache_type = @start_global_value; SET @@SESSION.query_cache_type = @start_session_value; +SET GLOBAL query_cache_size = @start_query_cache_size; DROP TABLE t1; DROP PROCEDURE testProcHit; Disconnecting con0 diff --git a/mysql-test/suite/sys_vars/r/query_prealloc_size_func.result b/mysql-test/suite/sys_vars/r/query_prealloc_size_func.result index c77586f2fc4..4f7a8d18f32 100644 --- a/mysql-test/suite/sys_vars/r/query_prealloc_size_func.result +++ b/mysql-test/suite/sys_vars/r/query_prealloc_size_func.result @@ -1,17 +1,18 @@ ** Setup ** -CREATE TABLE t1 (id int auto_increment primary key, val text(200)); -INSERT INTO t1 values(NULL,'a'); -INSERT INTO t1 values(NULL,'b'); -INSERT INTO t1 values(NULL,'c'); -INSERT INTO t1 values(NULL,'d'); +SET @start_value = @@global.query_prealloc_size; +CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY, val TEXT(200)); +INSERT INTO t1 VALUES(NULL,'a'); +INSERT INTO t1 VALUES(NULL,'b'); +INSERT INTO t1 VALUES(NULL,'c'); +INSERT INTO t1 VALUES(NULL,'d'); SELECT * FROM t1 ORDER BY val; id val 1 a 2 b 3 c 4 d -SET SESSION query_prealloc_size = 8192; +SET SESSION query_prealloc_size = 8192; '#----------------------------FN_DYNVARS_137_05-----------------#' SET GLOBAL query_prealloc_size = 8192; SELECT @@SESSION.query_prealloc_size; @@ -36,3 +37,5 @@ SELECT @@GLOBAL.query_prealloc_size; @@GLOBAL.query_prealloc_size 8192 Expected Value : 8192; +DROP TABLE t1; +SET @@global.query_prealloc_size = @start_value; diff --git a/mysql-test/suite/sys_vars/r/rpl_max_binlog_size_func.result b/mysql-test/suite/sys_vars/r/rpl_max_binlog_size_func.result index a2ffd10d322..0d0a1ae03c4 100644 --- a/mysql-test/suite/sys_vars/r/rpl_max_binlog_size_func.result +++ b/mysql-test/suite/sys_vars/r/rpl_max_binlog_size_func.result @@ -1,6 +1,8 @@ DROP TABLE IF EXISTS t1; '--- check if log file is rotated after 4096 bytes ----' +SET @saved_max_binlog_size= @@global.max_binlog_size; SET @@global.max_binlog_size = 4096; CREATE TABLE t1(a CHAR(5)); 'mylog.000002 exists' +SET @@global.max_binlog_size= @saved_max_binlog_size; DROP TABLE t1; diff --git a/mysql-test/suite/sys_vars/r/slave_net_timeout_basic.result b/mysql-test/suite/sys_vars/r/slave_net_timeout_basic.result index d672d0152d7..7903d7f8215 100644 --- a/mysql-test/suite/sys_vars/r/slave_net_timeout_basic.result +++ b/mysql-test/suite/sys_vars/r/slave_net_timeout_basic.result @@ -1,7 +1,7 @@ SET @start_global_value = @@global.slave_net_timeout; SELECT @start_global_value; @start_global_value -3600 +120 '#--------------------FN_DYNVARS_146_01-------------------------#' SET @@global.slave_net_timeout = 500000; SET @@global.slave_net_timeout = DEFAULT; @@ -124,4 +124,4 @@ SET global slave_net_timeout = 99; SET @@global.slave_net_timeout = @start_global_value; SELECT @@global.slave_net_timeout; @@global.slave_net_timeout -3600 +120 diff --git a/mysql-test/suite/sys_vars/r/slow_query_log_file_basic.result b/mysql-test/suite/sys_vars/r/slow_query_log_file_basic.result index a62f3e21ef2..925f940a5c4 100644 --- a/mysql-test/suite/sys_vars/r/slow_query_log_file_basic.result +++ b/mysql-test/suite/sys_vars/r/slow_query_log_file_basic.result @@ -1,12 +1,9 @@ SET @start_value = @@global.slow_query_log_file; -SELECT @start_value; -@start_value -slowtest.log '#---------------------FN_DYNVARS_004_01-------------------------#' SET @@global.slow_query_log_file = DEFAULT; SELECT RIGHT(@@global.slow_query_log_file,15); RIGHT(@@global.slow_query_log_file,15) -master-slow.log +mysqld-slow.log '#--------------------FN_DYNVARS_004_02------------------------#' SET @@global.slow_query_log_file = mytest.log; SET @@global.slow_query_log_file = 12; @@ -17,3 +14,4 @@ FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='slow_query_log_file'; @@global.slow_query_log_file = VARIABLE_VALUE 1 +SET @@global.slow_query_log_file= @start_value; diff --git a/mysql-test/suite/sys_vars/r/slow_query_log_file_func.result b/mysql-test/suite/sys_vars/r/slow_query_log_file_func.result index 4b7a49c6f0b..201b33c8edb 100644 --- a/mysql-test/suite/sys_vars/r/slow_query_log_file_func.result +++ b/mysql-test/suite/sys_vars/r/slow_query_log_file_func.result @@ -1,3 +1,3 @@ '#--------------------FN_DYNVARS_018_01-------------------------#' -## --file_exists var/master-data/my_slow_test.log ## +## Checking if my_slow_test.log exists in servers datadir ## ## This case should pass because we have set this filename in opt file ## |