diff options
| author | Horst Hunger <horst@mysql.com> | 2008-09-10 12:50:39 +0200 |
|---|---|---|
| committer | Horst Hunger <horst@mysql.com> | 2008-09-10 12:50:39 +0200 |
| commit | 1fc57773200240c9836e4bdd634d6fcc1ae32719 (patch) | |
| tree | d1f4d655f8887b7cd2ec96c8afbbb8b13c97669f /mysql-test | |
| parent | 39315c3c1d753544b757e19ae565f782a24b9261 (diff) | |
| download | mariadb-git-1fc57773200240c9836e4bdd634d6fcc1ae32719.tar.gz | |
Final fix for bug#38349: Did the changes due to the 2 reviews.
- Updated slow_query_log_file_basic and general_log_file basis instead of the func version as
the func version run good but the basic versions fail.
- Sent innodb.test to dev@innodb.com.
- variables.test has differences probably due to a bug in mtr or in the SET statement (see bug#39369).
- general_log_file_basic.test and slow_query_log_file_bsaic.test have differences, which might be
produced by the new mtr (see bug#38124).
Diffstat (limited to 'mysql-test')
65 files changed, 1032 insertions, 747 deletions
diff --git a/mysql-test/r/binlog_format_basic.result b/mysql-test/r/binlog_format_basic.result index 3fee9bade7e..f9b1c2c6e09 100644 --- a/mysql-test/r/binlog_format_basic.result +++ b/mysql-test/r/binlog_format_basic.result @@ -1,4 +1,5 @@ '#---------------------BS_STVARS_002_01----------------------#' +SET @start_value= @@global.binlog_format; SELECT COUNT(@@GLOBAL.binlog_format); COUNT(@@GLOBAL.binlog_format) 1 @@ -9,19 +10,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 @@ -34,14 +29,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 @@ -51,7 +46,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 @@ -73,3 +68,4 @@ SELECT COUNT(@@GLOBAL.binlog_format); COUNT(@@GLOBAL.binlog_format) 1 1 Expected +SET @@global.binlog_format= @start_value; diff --git a/mysql-test/r/character_set_connection_func.result b/mysql-test/r/character_set_connection_func.result index 2f5d28ec8af..6fc33a4f369 100644 --- a/mysql-test/r/character_set_connection_func.result +++ b/mysql-test/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/r/completion_type_func.result b/mysql-test/r/completion_type_func.result index 282bd1006cd..daee738c10d 100644 --- a/mysql-test/r/completion_type_func.result +++ b/mysql-test/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/r/concurrent_insert_func.result b/mysql-test/r/concurrent_insert_func.result index 87573bb2873..774775a8287 100644 --- a/mysql-test/r/concurrent_insert_func.result +++ b/mysql-test/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/r/delay_key_write_func.result b/mysql-test/r/delay_key_write_func.result index ca4e25e08b1..d55cca7ab8e 100644 --- a/mysql-test/r/delay_key_write_func.result +++ b/mysql-test/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/r/general_log_file_basic.result b/mysql-test/r/general_log_file_basic.result index 2d98da6f1a0..48a8d90b55e 100644 --- a/mysql-test/r/general_log_file_basic.result +++ b/mysql-test/r/general_log_file_basic.result @@ -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/r/general_log_file_func.result b/mysql-test/r/general_log_file_func.result index 42ef723e644..482a03c1a97 100644 --- a/mysql-test/r/general_log_file_func.result +++ b/mysql-test/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/r/innodb_autoextend_increment_basic.result b/mysql-test/r/innodb_autoextend_increment_basic.result index dafc71fd25d..69a8b6cc430 100644 --- a/mysql-test/r/innodb_autoextend_increment_basic.result +++ b/mysql-test/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/r/innodb_autoinc_lock_mode_func.result b/mysql-test/r/innodb_autoinc_lock_mode_func.result index 4c594558a6f..f8082cfaedb 100644 --- a/mysql-test/r/innodb_autoinc_lock_mode_func.result +++ b/mysql-test/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/r/innodb_fast_shutdown_basic.result b/mysql-test/r/innodb_fast_shutdown_basic.result index adbfb6ec867..4b923f4bdd2 100644 --- a/mysql-test/r/innodb_fast_shutdown_basic.result +++ b/mysql-test/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/r/innodb_max_dirty_pages_pct_func.result b/mysql-test/r/innodb_max_dirty_pages_pct_func.result index 2f1467af94e..d46c0d4a70a 100644 --- a/mysql-test/r/innodb_max_dirty_pages_pct_func.result +++ b/mysql-test/r/innodb_max_dirty_pages_pct_func.result @@ -1,14 +1,12 @@ +SET @start_value= @@global.innodb_max_dirty_pages_pct; '#--------------------FN_DYNVARS_044_02-------------------------#' -SET @old_innodb_max_dirty_pages_pct= @@global.innodb_max_dirty_pages_pct; 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 @@ -20,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 @@ -30,4 +27,4 @@ DROP PROCEDURE add_until; DROP PROCEDURE check_pct; DROP FUNCTION dirty_pct; DROP TABLE t1; -SET @@global.innodb_max_dirty_pages_pct = @old_innodb_max_dirty_pages_pct; +SET @@global.innodb_max_dirty_pages_pct= @start_value; diff --git a/mysql-test/r/innodb_table_locks_func.result b/mysql-test/r/innodb_table_locks_func.result index 22a52ce17fd..824d196d295 100644 --- a/mysql-test/r/innodb_table_locks_func.result +++ b/mysql-test/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/r/insert_update.result b/mysql-test/r/insert_update.result index 704cf444681..a285810b78b 100644 --- a/mysql-test/r/insert_update.result +++ b/mysql-test/r/insert_update.result @@ -393,6 +393,7 @@ id c1 cnt 1 0 3 2 2 1 DROP TABLE t1; +DROP TABLE t2; create table t1(f1 int primary key, f2 timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP); insert into t1(f1) values(1); diff --git a/mysql-test/r/key_buffer_size_func.result b/mysql-test/r/key_buffer_size_func.result index e2eff04f030..d5b79c260a0 100644 --- a/mysql-test/r/key_buffer_size_func.result +++ b/mysql-test/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/r/log_basic.result b/mysql-test/r/log_basic.result index 44d0ff48f1d..20ec6bcc88f 100644 --- a/mysql-test/r/log_basic.result +++ b/mysql-test/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,9 @@ SET global 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; diff --git a/mysql-test/r/log_bin_trust_function_creators_func.result b/mysql-test/r/log_bin_trust_function_creators_func.result index 3611f0f08fd..30ba316eb31 100644 --- a/mysql-test/r/log_bin_trust_function_creators_func.result +++ b/mysql-test/r/log_bin_trust_function_creators_func.result @@ -1,12 +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 @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators; 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 @@ -17,7 +17,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; @@ -34,8 +34,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 ## @@ -51,7 +51,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; @@ -66,9 +66,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= @old_log_bin_trust_function_creators; +SET @@global.log_bin_trust_function_creators= @start_value; diff --git a/mysql-test/r/log_queries_not_using_indexes_basic.result b/mysql-test/r/log_queries_not_using_indexes_basic.result index d05437e3f66..b19b11a4209 100644 --- a/mysql-test/r/log_queries_not_using_indexes_basic.result +++ b/mysql-test/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/r/log_state.result b/mysql-test/r/log_state.result index c293956148f..9e1a541fc9e 100644 --- a/mysql-test/r/log_state.result +++ b/mysql-test/r/log_state.result @@ -1,3 +1,6 @@ +set @start_general_log= @@global.general_log; +set @start_slow_query_log= @@global.slow_query_log; +set @start_general_log_file= @@global.general_log_file; set global general_log= OFF; truncate table mysql.general_log; truncate table mysql.slow_log; @@ -271,3 +274,6 @@ SET GLOBAL slow_query_log_file = @slow_query_log_file_saved; # -- End of Bug#32748. End of 5.1 tests +set @@global.general_log= @start_general_log; +set @@global.slow_query_log= @start_slow_query_log; +set @@global.general_log_file= @start_general_log_file; diff --git a/mysql-test/r/log_tables.result b/mysql-test/r/log_tables.result index 2a4cee9fbbc..2af4d5e94dc 100644 --- a/mysql-test/r/log_tables.result +++ b/mysql-test/r/log_tables.result @@ -1,3 +1,5 @@ +SET @old_general_log_state = @@global.general_log; +SET @old_slow_log_state = @@global.slow_query_log; use mysql; truncate table general_log; select * from general_log; @@ -354,6 +356,7 @@ slow_log slow_log_new drop table slow_log_new, general_log_new; use test; +SET @my_log_output= @@global.log_output; SET GLOBAL LOG_OUTPUT = 'TABLE'; SET GLOBAL general_log = 0; FLUSH LOGS; @@ -422,6 +425,8 @@ SET SESSION long_query_time =@old_long_query_time; FLUSH LOGS; ALTER TABLE mysql.slow_log DROP COLUMN seq; ALTER TABLE mysql.slow_log ENGINE = CSV; +SET GLOBAL general_log = @old_general_log_state; +SET GLOBAL slow_query_log = @old_slow_log_state; drop procedure if exists proc25422_truncate_slow; drop procedure if exists proc25422_truncate_general; drop procedure if exists proc25422_alter_slow; @@ -725,6 +730,8 @@ execute long_query using @lparam; set global general_log = off; select command_type, argument from mysql.general_log where thread_id = @thread_id; command_type argument +Query set @old_general_log_state = @@global.general_log +Query set global general_log = on Query set @lparam = "000 001 002 003 004 005 006 007 008 009" "010 011 012 013 014 015 016 017 018 019" "020 021 022 023 024 025 026 027 028 029" @@ -858,5 +865,6 @@ TIMESTAMP 1 1 SELECT SQL_NO_CACHE 'Bug#31700 - KEY', f1,f2,f3,SLEEP(1.1) FROM t1 TIMESTAMP 1 1 SELECT SQL_NO_CACHE 'Bug#31700 - PK', f1,f2,f3,SLEEP(1.1) FROM t1 WHERE f1=2 DROP TABLE t1; TRUNCATE TABLE mysql.slow_log; +SET GLOBAL log_output= @my_log_output; SET GLOBAL slow_query_log = @old_slow_log_state; SET SESSION long_query_time =@old_long_query_time; diff --git a/mysql-test/r/max_allowed_packet_func.result b/mysql-test/r/max_allowed_packet_func.result index 9e44bcfb7b1..87d1a908932 100644 --- a/mysql-test/r/max_allowed_packet_func.result +++ b/mysql-test/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 @@ -8,7 +9,7 @@ PRIMARY KEY (id), name BLOB ); '#--------------------FN_DYNVARS_070_01-------------------------#' -## 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 @@session.max_allowed_packet = 1024; SET @@session.net_buffer_length = 1024; SELECT @@session.max_allowed_packet; @@ -29,7 +30,7 @@ id name 'Bug#35381: Error is not coming on inserting and fetching data of length' 'greater than max_allowed_packet size at session level'; '#--------------------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; @@ -39,9 +40,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/r/max_seeks_for_key_func.result b/mysql-test/r/max_seeks_for_key_func.result index b4c863af17b..5a2441276ec 100644 --- a/mysql-test/r/max_seeks_for_key_func.result +++ b/mysql-test/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/r/max_sort_length_func.result b/mysql-test/r/max_sort_length_func.result index 04f5aa7f4f1..9254f1fadd7 100644 --- a/mysql-test/r/max_sort_length_func.result +++ b/mysql-test/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/r/myisam_data_pointer_size_func.result b/mysql-test/r/myisam_data_pointer_size_func.result index f32e25c7427..82d9a7dad66 100644 --- a/mysql-test/r/myisam_data_pointer_size_func.result +++ b/mysql-test/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,3 +41,4 @@ count(*) 65536 DROP PROCEDURE sp_addRec; DROP TABLE t1; +SET @@global.myisam_data_pointer_size= @start_value; diff --git a/mysql-test/r/myisam_stats_method_func.result b/mysql-test/r/myisam_stats_method_func.result index ace6887cc90..55d91b7417d 100644 --- a/mysql-test/r/myisam_stats_method_func.result +++ b/mysql-test/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/r/profiling.result b/mysql-test/r/profiling.result index a9ada576b4b..c96074eb830 100644 --- a/mysql-test/r/profiling.result +++ b/mysql-test/r/profiling.result @@ -11,6 +11,7 @@ select @@profiling; 0 set global profiling = ON; ERROR HY000: Variable 'profiling' is a SESSION variable and can't be used with SET GLOBAL +set @start_value= @@global.profiling_history_size; set global profiling_history_size=100; show global variables like 'profil%'; Variable_name Value @@ -412,4 +413,5 @@ Warnings: Note 1051 Unknown table 'test.v1' drop function if exists f1; set session profiling = OFF; +set global profiling_history_size= @start_value; End of 5.0 tests diff --git a/mysql-test/r/query_cache_limit_func.result b/mysql-test/r/query_cache_limit_func.result index e54884cd5c3..bbbb273428a 100644 --- a/mysql-test/r/query_cache_limit_func.result +++ b/mysql-test/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/r/query_cache_type_func.result b/mysql-test/r/query_cache_type_func.result index 94c31804248..af121244572 100644 --- a/mysql-test/r/query_cache_type_func.result +++ b/mysql-test/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/r/query_prealloc_size_func.result b/mysql-test/r/query_prealloc_size_func.result index c77586f2fc4..4f7a8d18f32 100644 --- a/mysql-test/r/query_prealloc_size_func.result +++ b/mysql-test/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/r/slow_query_log_file_basic.result b/mysql-test/r/slow_query_log_file_basic.result index 29ee7430d51..925f940a5c4 100644 --- a/mysql-test/r/slow_query_log_file_basic.result +++ b/mysql-test/r/slow_query_log_file_basic.result @@ -1,7 +1,4 @@ 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); @@ -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/r/slow_query_log_file_func.result b/mysql-test/r/slow_query_log_file_func.result index 61d71f38891..201b33c8edb 100644 --- a/mysql-test/r/slow_query_log_file_func.result +++ b/mysql-test/r/slow_query_log_file_func.result @@ -1,3 +1,3 @@ '#--------------------FN_DYNVARS_018_01-------------------------#' -## Checking if my_slow_test.log exist in servers datadir ## +## Checking if my_slow_test.log exists in servers datadir ## ## This case should pass because we have set this filename in opt file ## diff --git a/mysql-test/r/variables-notembedded.result b/mysql-test/r/variables-notembedded.result index 48a1719e2c7..e241edcc5ec 100644 --- a/mysql-test/r/variables-notembedded.result +++ b/mysql-test/r/variables-notembedded.result @@ -15,3 +15,4 @@ slave_skip_errors 3,100,137,643,1752 ---- Clean Up ---- set global slave_net_timeout=default; set global sql_slave_skip_counter= 0; +set @@global.slave_net_timeout= @my_slave_net_timeout; diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index 3f66599751d..06bbdcfd175 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -15,6 +15,7 @@ set @my_max_heap_table_size =@@global.max_heap_table_size; set @my_max_insert_delayed_threads=@@global.max_insert_delayed_threads; set @my_max_join_size =@@global.max_join_size; set @my_myisam_data_pointer_size =@@global.myisam_data_pointer_size; +set @my_myisam_max_sort_file_size =@@global.myisam_max_sort_file_size; set @my_net_buffer_length =@@global.net_buffer_length; set @my_net_write_timeout =@@global.net_write_timeout; set @my_net_read_timeout =@@global.net_read_timeout; @@ -988,7 +989,7 @@ set global delayed_queue_size =@my_delayed_queue_size; set global flush =@my_flush; set global flush_time =@my_flush_time; set global key_buffer_size =@my_key_buffer_size; -set global max_binlog_cache_size =default; +set global max_binlog_cache_size =@my_max_binlog_cache_size; set global max_binlog_size =@my_max_binlog_size; set global max_connect_errors =@my_max_connect_errors; set global max_connections =@my_max_connections; @@ -999,6 +1000,7 @@ set global max_join_size =@my_max_join_size; set global max_user_connections =default; set global max_write_lock_count =default; set global myisam_data_pointer_size =@my_myisam_data_pointer_size; +set global myisam_max_sort_file_size =@my_myisam_max_sort_file_size; set global net_buffer_length =@my_net_buffer_length; set global net_write_timeout =@my_net_write_timeout; set global net_read_timeout =@my_net_read_timeout; diff --git a/mysql-test/t/binlog_format_basic.test b/mysql-test/t/binlog_format_basic.test index e9dfade8f56..d7a4fb190ab 100644 --- a/mysql-test/t/binlog_format_basic.test +++ b/mysql-test/t/binlog_format_basic.test @@ -1,5 +1,3 @@ - - ################## mysql-test\t\binlog_format_basic.test ###################### # # # Variable Name: binlog_format # @@ -17,8 +15,8 @@ # * Value Check # # * Scope Check # # # -# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # -# server-system-variables.html # +# Reference: # +# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html # # # ############################################################################### @@ -26,6 +24,8 @@ #################################################################### # Displaying default value # #################################################################### +SET @start_value= @@global.binlog_format; + SELECT COUNT(@@GLOBAL.binlog_format); --echo 1 Expected @@ -36,24 +36,11 @@ SELECT COUNT(@@SESSION.binlog_format); #################################################################### # Check if Value can set # #################################################################### - -#--error ER_INCORRECT_GLOBAL_LOCAL_VAR SET @@GLOBAL.binlog_format=1; ---echo Expected error 'Read only variable' ---echo Bug: Writeable static variable -SELECT COUNT(@@GLOBAL.binlog_format); ---echo 1 Expected - - +SELECT @@GLOBAL.binlog_format; -#--error ER_INCORRECT_GLOBAL_LOCAL_VAR SET @@SESSION.binlog_format=1; ---echo Expected error 'Read only variable' ---echo Bug: Writeable static variable -SELECT COUNT(@@SESSION.binlog_format); ---echo 1 Expected - - +SELECT @@SESSION.binlog_format; --echo '#---------------------BS_STVARS_002_03----------------------#' ################################################################# @@ -69,7 +56,7 @@ SELECT COUNT(@@GLOBAL.binlog_format); --echo 1 Expected SELECT COUNT(VARIABLE_VALUE) -FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES +FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='binlog_format'; --echo 1 Expected @@ -79,8 +66,8 @@ WHERE VARIABLE_NAME='binlog_format'; # Check if the value in SESSION Table matches value in variable # ################################################################# -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'; --echo 1 Expected @@ -88,13 +75,11 @@ SELECT COUNT(@@SESSION.binlog_format); --echo 1 Expected SELECT COUNT(VARIABLE_VALUE) -FROM INFORMATION_SCHEMA.SESSION_VARIABLES +FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME='binlog_format'; --echo 1 Expected - - --echo '#---------------------BS_STVARS_002_05----------------------#' ################################################################################ # Check if binlog_format can be accessed with and without @@ sign # @@ -109,5 +94,5 @@ SELECT COUNT(@@SESSION.binlog_format); SELECT COUNT(@@GLOBAL.binlog_format); --echo 1 Expected - +SET @@global.binlog_format= @start_value; diff --git a/mysql-test/t/character_set_connection_func.test b/mysql-test/t/character_set_connection_func.test index 04883b66544..11a8e311fd1 100644 --- a/mysql-test/t/character_set_connection_func.test +++ b/mysql-test/t/character_set_connection_func.test @@ -14,14 +14,14 @@ # Description: Test Cases of Dynamic System Variable character_set_connection # # that checks the behavior of this variable # # # -# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # -# server-system-variables.html # +# Reference: # +# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html # # # ############################################################################### --echo '#--------------------FN_DYNVARS_011_01-------------------------#' ########################################################################## -# Check if setting character_set_connection is changed in new connection # +# Check if setting character_set_connection is changed in new connection # ########################################################################## #save @@ -35,7 +35,7 @@ SET @@global.character_set_connection = utf8; --echo 'connect (con1,localhost,root,,,,)' connect (con1,localhost,root,,,,); --echo 'connection con1' -connection con1; +connection con1; SELECT @@global.character_set_connection; SELECT @@session.character_set_connection; disconnect con1; @@ -51,7 +51,7 @@ connection default; --disable_warnings DROP TABLE IF EXISTS t1; --enable_warnings -CREATE TABLE t1(b CHAR(40) character set utf8); +CREATE TABLE t1(b CHAR(40) CHARACTER SET utf8); #============================================================================== --echo '--verify that character_set_connection converts character_set_client--' #============================================================================== @@ -90,13 +90,16 @@ INSERT INTO t1 VALUES('ЁЂЃЄ'); SELECT * FROM t1; TRUNCATE TABLE t1; -#restore -#save + +# Cleanup 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; + ############################################################# # End of functionality Testing for character_set_connection # ############################################################# + diff --git a/mysql-test/t/completion_type_func.test b/mysql-test/t/completion_type_func.test index 4f1ebf6700b..ed0f04c37b4 100644 --- a/mysql-test/t/completion_type_func.test +++ b/mysql-test/t/completion_type_func.test @@ -1,4 +1,4 @@ -############## mysql-test\t\completion_type_func.test ######################### +############## mysql-test\t\completion_type_func.test ########################## # # # Variable Name: completion_type # # Scope: GLOBAL & SESSION # @@ -12,7 +12,7 @@ # Author: Salman Rawala # # # # Description: Test Cases of Dynamic System Variable "completion_type" # -# that checks functinality of this variable # +# that checks functionality of this variable # # # # Reference: http://dev.mysql.com/doc/refman/5.1/en/ # # server-system-variables.html#option_mysqld_completion_type # @@ -22,7 +22,7 @@ --source include/have_innodb.inc --disable_warnings -drop table if exists t1; +DROP TABLE IF EXISTS t1; --enable_warnings ######################### @@ -32,62 +32,62 @@ drop table if exists t1; --echo ## 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; --echo '#--------------------FN_DYNVARS_017_01-------------------------#' ######################################################### -# Setting initial value of completion_type to zero # +# Setting initial value of completion_type to zero # ######################################################### --echo ## Creating new connection ## connect (test_con1,localhost,root,,); connection test_con1; -INSERT into t1(name) values('Record_1'); +INSERT INTO t1(name) VALUES('Record_1'); SET @@autocommit = 0; -SELECT * from t1; +SELECT * FROM t1; --echo ## Setting value of variable to 0 ## SET @@session.completion_type = 0; --echo ## Here commit & rollback should work normally ## START TRANSACTION; -SELECT * from t1; -INSERT into t1(name) values('Record_2'); -INSERT into t1(name) values('Record_3'); -SELECT * from t1; -DELETE FROM t1 where id = 2; -SELECT * from t1; +SELECT * FROM t1; +INSERT INTO t1(name) VALUES('Record_2'); +INSERT INTO t1(name) VALUES('Record_3'); +SELECT * FROM t1; +DELETE FROM t1 WHERE id = 2; +SELECT * FROM t1; START TRANSACTION; -SELECT * from t1; -INSERT into t1(name) values('Record_4'); -INSERT into t1(name) values('Record_5'); +SELECT * FROM t1; +INSERT INTO t1(name) VALUES('Record_4'); +INSERT INTO t1(name) VALUES('Record_5'); COMMIT; --echo '#--------------------FN_DYNVARS_017_02-------------------------#' ######################################################### -# Setting initial value of completion_type to 2 # +# Setting initial value of completion_type to 2 # ######################################################### SET @@session.completion_type = 2; --echo ## Here commit should work as COMMIT RELEASE ## START TRANSACTION; -SELECT * from t1; -INSERT into t1(name) values('Record_6'); -INSERT into t1(name) values('Record_7'); +SELECT * FROM t1; +INSERT INTO t1(name) VALUES('Record_6'); +INSERT INTO t1(name) VALUES('Record_7'); COMMIT; --echo ## Inserting rows should give error here because connection should ## --echo ## disconnect after using COMMIT ## --Error 2006,2013,1053 -INSERT into t1(name) values('Record_4'); +INSERT INTO t1(name) VALUES('Record_4'); --echo ## Creating new connection test_con2 ## connect (test_con2,localhost,root,,); @@ -96,11 +96,17 @@ SET @@session.completion_type = 2; --echo ## Inserting rows and using Rollback which should Rollback & release ## START TRANSACTION; -SELECT * from t1; -INSERT into t1(name) values('Record_8'); -INSERT into t1(name) values('Record_9'); +SELECT * FROM t1; +INSERT INTO t1(name) VALUES('Record_8'); +INSERT INTO t1(name) VALUES('Record_9'); ROLLBACK; --Error 2006,2013,1053 -INSERT into t1(name) values('Record_4'); +INSERT INTO t1(name) VALUES('Record_4'); + +connection default; +disconnect test_con1; +disconnect test_con2; + +DROP TABLE t1; diff --git a/mysql-test/t/concurrent_insert_func.test b/mysql-test/t/concurrent_insert_func.test index e6ea7c4304b..3810a559244 100644 --- a/mysql-test/t/concurrent_insert_func.test +++ b/mysql-test/t/concurrent_insert_func.test @@ -14,14 +14,14 @@ # Description: Test Cases of Dynamic System Variable "concurrent_insert" # # that checks functionality of this variable # # # -# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # -# server-system-variables.html#option_mysqld_concurrent_insert # +# Reference: # +# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html # # # ################################################################################ --disable_warnings -drop table if exists t1; +DROP TABLE IF EXISTS t1; --enable_warnings ######################### @@ -31,110 +31,131 @@ drop table if exists t1; --echo ## Creating new table ## CREATE TABLE t1 ( -name varchar(30) +name VARCHAR(30) ); --echo '#--------------------FN_DYNVARS_018_01-------------------------#' #################################################################### -# Setting initial value of concurrent_insert to 1 -# concurrent_insert = 1 means Enables concurrent insert +# Setting initial value of concurrent_insert to 1 +# concurrent_insert = 1 means Enables concurrent insert # for MyISAM tables that don't have holes #################################################################### +SET @start_value= @@global.concurrent_insert; + --echo ## 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'); --echo ## locking table ## -lock table t1 read local; +LOCK TABLE t1 READ LOCAL; ---echo ## Creating new connection to insert some rows in table ## +--echo ## Creating new connection to insert some rows in table ## connect (test_con1,localhost,root,,); +--echo connection test_con1; connection test_con1; --echo ## 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; --echo ## unlocking tables ## +--echo connection default; connection default; -unlock tables; +UNLOCK TABLES; ---echo ## deleting record to create hole in table ## -DELETE from t1 where name ='Record_2'; +--echo ## deleting record to create hole in table ## +DELETE FROM t1 WHERE name ='Record_2'; --echo '#--------------------FN_DYNVARS_018_02-------------------------#' #################################################################### -# Setting initial value of concurrent_insert to 1 -# concurrent_insert = 1 and trying to insert some values +# Setting initial value of concurrent_insert to 1 +# concurrent_insert = 1 and trying to insert some values # in MyISAM tables that have holes #################################################################### - # lock table and connect with connection1 -#lock table t1 read local; -#connection test_con1; +LOCK TABLE t1 READ LOCAL; +--echo connection test_con1; +connection test_con1; # setting value of concurrent_insert to 1 -#SET @@global.concurrent_insert=1; - -#INSERT into t1(name) values('Record_7'); -#SELECT * from t1; - -#connection default; -#unlock tables; - -#SELECT * from t1; -#INSERT into t1(name) values('Record_6'); - -# On inserting rows in hole while the value of concurrent_insert is 1 -# MySQL server hangs. +SET @@global.concurrent_insert=1; +--echo ## send INSERT which should be blocked until unlock of the table ## +send +INSERT INTO t1(name) VALUES('Record_7'); +--echo connection default; +connection default; +# wait until INSERT will be locked (low performance) +let $wait_condition= SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST + WHERE state= "Locked" AND info LIKE "INSERT INTO t1%"; +--source include/wait_condition.inc + +--echo ## show processlist info and state ## +SELECT state,info FROM INFORMATION_SCHEMA.PROCESSLIST +WHERE state= "Locked" AND info LIKE "INSERT INTO t1%"; +--echo ## table contents befor UNLOCK ## +SELECT * FROM t1; +UNLOCK TABLES; +--echo ## table contens after UNLOCK ## +SELECT * FROM t1; +INSERT INTO t1(name) VALUES('Record_6'); + +--echo connection test_con1; +connection test_con1; +# to complete the send above^ +reap; +SELECT * FROM t1; +--echo connection default; +connection default; --echo '#--------------------FN_DYNVARS_018_03-------------------------#' -############################################################################### +################################################################################ # Setting value of concurrent_insert to 2 to verify values after inserting # it into table with holes -# concurrent_insert = 2 means Enables concurrent insert +# concurrent_insert = 2 means Enables concurrent insert # for MyISAM tables that have holes but inserts values at the end of all rows -############################################################################### +################################################################################ --echo ## lock table and connect with connection1 ## -lock table t1 read local; +LOCK TABLE t1 READ LOCAL; +--echo connection test_con1; connection test_con1; - --echo ## setting value of concurrent_insert to 2 ## SET @@global.concurrent_insert=2; --echo ## 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; SELECT @@concurrent_insert; ---echo ## Switching to default connection ## +--echo connection default; connection default; --echo ## Unlocking table ## -unlock tables; +UNLOCK TABLES; -SELECT * from t1; +SELECT * FROM t1; --echo ## 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; --echo ## connection test_con1 ## -DELETE from t1 where name ='Record_3'; -SELECT * from t1; +DELETE FROM t1 WHERE name ='Record_3'; +SELECT * FROM t1; --echo ## Dropping table ## -DROP table t1; +DROP TABLE t1; --echo ## Disconnecting connection ## disconnect test_con1; +SET @@global.concurrent_insert= @start_value; + diff --git a/mysql-test/t/delay_key_write_func.test b/mysql-test/t/delay_key_write_func.test index 3c0319be427..a44b37653d2 100644 --- a/mysql-test/t/delay_key_write_func.test +++ b/mysql-test/t/delay_key_write_func.test @@ -14,16 +14,17 @@ # Description: Test Cases of Dynamic System Variable delay_key_write # # that checks the behavior of this variable # # # -# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # -# server-system-variables.html # +# Reference: # +# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html # # # ############################################################################### --echo '#--------------------FN_DYNVARS_023_01-------------------------#' ####################################################################### -# Check if setting delay_key_write is changed in every new connection # +# Check if setting delay_key_write is changed in every new connection # ####################################################################### +SET @start_value= @@global.delay_key_write; SET @@global.delay_key_write = ON; SELECT @@global.delay_key_write; @@ -51,11 +52,11 @@ SELECT @@global.delay_key_write AS res_is_ALL; --disable_query_log DELIMITER //; CREATE PROCEDURE sp_addRecords (IN var1 INT,IN var2 INT) -BEGIN - WHILE (var1 < var2) DO - INSERT INTO t1 VALUES(var1,REPEAT('MYSQL',10),100000.0/var1); - SET var1=var1+1; - END WHILE; +BEGIN + WHILE (var1 < var2) DO + INSERT INTO t1 VALUES(var1,REPEAT('MYSQL',10),100000.0/var1); + SET var1=var1+1; + END WHILE; END// DELIMITER ;// --enable_query_log @@ -77,7 +78,7 @@ CREATE TABLE t1( a INT PRIMARY KEY, b VARCHAR(512), c DOUBLE -)delay_key_write = 1; +) DELAY_KEY_WRITE = 1; --enable_query_log @@ -88,7 +89,7 @@ CALL sp_addRecords(1,10); SHOW STATUS LIKE 'Key_reads'; SHOW STATUS LIKE 'Key_writes'; SHOW STATUS LIKE 'Key_write_requests'; -select count(*) from t1; +SELECT COUNT(*) FROM t1; #============================================================================== --echo '----check when delay_key_write is ON---' @@ -105,7 +106,7 @@ CREATE TABLE t1( a INT PRIMARY KEY, b VARCHAR(512), c DOUBLE -)delay_key_write = 1; +) DELAY_KEY_WRITE = 1; --enable_query_log FLUSH STATUS; @@ -114,7 +115,7 @@ CALL sp_addRecords(1,10); SHOW STATUS LIKE 'Key_reads'; SHOW STATUS LIKE 'Key_writes'; SHOW STATUS LIKE 'Key_write_requests'; -select count(*) from t1; +SELECT COUNT(*) FROM t1; #============================================================================== --echo '----check when delay_key_write is ALL---' @@ -130,7 +131,7 @@ CREATE TABLE t1( a INT PRIMARY KEY, b VARCHAR(512), c DOUBLE -)delay_key_write = 0; +) DELAY_KEY_WRITE = 0; --enable_query_log FLUSH STATUS; @@ -139,11 +140,16 @@ CALL sp_addRecords(1,10); SHOW STATUS LIKE 'Key_reads'; SHOW STATUS LIKE 'Key_writes'; SHOW STATUS LIKE 'Key_write_requests'; -select count(*) from t1; +SELECT COUNT(*) FROM t1; DROP PROCEDURE sp_addRecords; DROP TABLE t1; +disconnect user1; + +connection default; +SET @@global.delay_key_write= @start_value; #################################################### # End of functionality testing for delay_key_write # #################################################### + diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index 24935ce397d..0e72351fcd6 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -16,7 +16,6 @@ log_tables.test : Bug #37798: main.log_tables fails random wait_timeout_func : BUG#36873 2008-07-06 sven wait_timeout_func.test fails randomly delayed_insert_limit_func : BUG#37962 2008-07-08 sven *_func tests containing sleeps/race conditions event_scheduler_func : BUG#37962 2008-07-08 sven *_func tests containing sleeps/race conditions -innodb_max_dirty_pages_pct_func : BUG#37962 2008-07-08 sven *_func tests containing sleeps/race conditions interactive_timeout_func : BUG#37962 2008-07-08 sven *_func tests containing sleeps/race conditions query_cache_wlock_invalidate_func : BUG#37962 2008-07-08 sven *_func tests containing sleeps/race conditions rpl_init_slave_func : BUG#37962 2008-07-08 sven *_func tests containing sleeps/race conditions @@ -25,3 +24,4 @@ slow_query_log_func : BUG#37962 2008-07-08 sven *_func tests c sql_low_priority_updates_func : BUG#37962 2008-07-08 sven *_func tests containing sleeps/race conditions timestamp_func : BUG#37962 2008-07-08 sven *_func tests containing sleeps/race conditions log_output_func : BUG#37766 2008-07-10 sven main.log_output_func randomly fails in pushbuild + diff --git a/mysql-test/t/general_log_file_basic.test b/mysql-test/t/general_log_file_basic.test index 639e6619ef9..014108f88d2 100644 --- a/mysql-test/t/general_log_file_basic.test +++ b/mysql-test/t/general_log_file_basic.test @@ -10,6 +10,7 @@ # # # Creation Date: 2008-03-16 # # Author: Salman Rawala # +# Modified: HHunger 2008-09-11 Set system variable back to the start value # # # # Description: Test Cases of Dynamic System Variable "general_log_file" # # that checks behavior of this variable in the following ways # @@ -18,8 +19,8 @@ # * Scope & Access method # # * Data Integrity # # # -# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # -# server-system-variables.html#option_mysqld_general_log_file # +# Reference: # +# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html # # # ################################################################################ @@ -67,7 +68,9 @@ SELECT @@global.general_log_file = VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='general_log_file'; +SET @@global.general_log_file= @start_value; ##################################################### # END OF general_log_file TESTS # ##################################################### + diff --git a/mysql-test/t/general_log_file_func.test b/mysql-test/t/general_log_file_func.test index 96951785ca2..1a73a4a5eaa 100644 --- a/mysql-test/t/general_log_file_func.test +++ b/mysql-test/t/general_log_file_func.test @@ -21,7 +21,7 @@ --disable_warnings -drop table if exists t1; +DROP TABLE IF EXISTS t1; --enable_warnings ######################### @@ -31,7 +31,7 @@ drop table if exists t1; --echo ## Creating new table ## CREATE TABLE t1 ( -id INT NOT NULL auto_increment, +id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id), name VARCHAR(30) ); @@ -40,14 +40,12 @@ name VARCHAR(30) #################################################################### # Verifying general log as we have initialized in opt file # #################################################################### - - SELECT @@general_log_file; -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'); --echo ## Verifying general log file ## let $MYSQLD_DATADIR= `select @@datadir`; diff --git a/mysql-test/t/innodb_autoextend_increment_basic.test b/mysql-test/t/innodb_autoextend_increment_basic.test index 86f8c7aa7a4..eb208e7197f 100644 --- a/mysql-test/t/innodb_autoextend_increment_basic.test +++ b/mysql-test/t/innodb_autoextend_increment_basic.test @@ -1,99 +1,87 @@ -################# mysql-test\t\innodb_autoextend_increment _basic.test ################### -# # -# Variable Name: innodb_autoextend_increment # -# Scope: GLOBAL # -# Access Type: Dynamic # -# Data Type: Numeric # -# Default Value: 8 # -# Range: 0,1 # -# # -# # -# Creation Date: 2008-02-07 # -# Author: Rizwan # -# # -#Description:Test Cases of Dynamic System Variable innodb_autoextend_increment# -# that checks the behavior of this variable in the following ways # -# * Default Value # -# * Valid & Invalid values # -# * Scope & Access method # -# * Data Integrity # -# # -# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # -# server-system-variables.html # -# # -############################################################################### +################# mysql-test\t\innodb_autoextend_increment _basic.test ########### +# # +# Variable Name: innodb_autoextend_increment # +# Scope: GLOBAL # +# Access Type: Dynamic # +# Data Type: Numeric # +# Default Value: 8 # +# Range: 0,1 # +# # +# # +# Creation Date: 2008-02-07 # +# Author: Rizwan # +# # +# Description: # +# Test Cases of Dynamic System Variable innodb_autoextend_increment that # +# checks the behavior of this variable in the following ways # +# * Default Value # +# * Valid & Invalid values # +# * Scope & Access method # +# * Data Integrity # +# # +# Reference: http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html # +# # +################################################################################## --source include/have_innodb.inc --source include/load_sysvars.inc -######################################################################## +######################################################################## # START OF innodb_autoextend_increment TESTS # -######################################################################## +######################################################################## -################################################################################ -# Saving initial value of innodb_autoextend_increment in a temporary variable# -################################################################################ +############################################################################### +# Saving initial value of innodb_autoextend_increment in a temporary variable # +############################################################################### SET @global_start_value = @@global.innodb_autoextend_increment ; -SELECT @global_start_value; - - --echo '#--------------------FN_DYNVARS_046_01------------------------#' -######################################################################## +######################################################################## # Display the DEFAULT value of innodb_autoextend_increment # -######################################################################## - - +######################################################################## SET @@global.innodb_autoextend_increment = 0; SET @@global.innodb_autoextend_increment = DEFAULT; SELECT @@global.innodb_autoextend_increment ; --echo '#---------------------FN_DYNVARS_046_02-------------------------#' -#################################################################################### +#################################################################################### # Check if innodb_autoextend_increment can be accessed with and without @@ sign # -#################################################################################### +#################################################################################### --Error ER_GLOBAL_VARIABLE SET innodb_autoextend_increment = 1; SELECT @@innodb_autoextend_increment ; - --Error ER_UNKNOWN_TABLE SELECT local.innodb_autoextend_increment ; - SET global innodb_autoextend_increment = 0; SELECT @@global.innodb_autoextend_increment ; --echo '#--------------------FN_DYNVARS_046_03------------------------#' -########################################################################## -# change the value of innodb_autoextend_increment to a valid value # -########################################################################## - - +########################################################################## +# change the value of innodb_autoextend_increment to a valid value # +########################################################################## SET @@global.innodb_autoextend_increment = 1; SELECT @@global.innodb_autoextend_increment ; SET @@global.innodb_autoextend_increment = 1000; SELECT @@global.innodb_autoextend_increment ; ---echo '#--------------------FN_DYNVARS_046_04-------------------------#' -########################################################################### -# Change the value of innodb_autoextend_increment to invalid value # -########################################################################### - - +--echo '#--------------------FN_DYNVARS_046_04-------------------------#' +########################################################################### +# Change the value of innodb_autoextend_increment to invalid value # +########################################################################### SET @@global.innodb_autoextend_increment = -1; SELECT @@global.innodb_autoextend_increment; - --Error ER_WRONG_TYPE_FOR_VAR SET @@global.innodb_autoextend_increment = "T"; SELECT @@global.innodb_autoextend_increment; @@ -102,31 +90,28 @@ SELECT @@global.innodb_autoextend_increment; SET @@global.innodb_autoextend_increment = "Y"; SELECT @@global.innodb_autoextend_increment; - SET @@global.innodb_autoextend_increment = 1001; SELECT @@global.innodb_autoextend_increment; - - --echo '#----------------------FN_DYNVARS_046_05------------------------#' -######################################################################### +######################################################################### # Check if the value in GLOBAL Table matches value in variable # ######################################################################### -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 '; SELECT @@global.innodb_autoextend_increment ; -SELECT VARIABLE_VALUE -FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES +SELECT VARIABLE_VALUE +FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_autoextend_increment '; --echo '#---------------------FN_DYNVARS_046_06-------------------------#' -################################################################### +################################################################### # Check if ON and OFF values can be used on variable # -################################################################### +################################################################### --ERROR ER_WRONG_TYPE_FOR_VAR SET @@global.innodb_autoextend_increment = OFF; @@ -136,25 +121,25 @@ SELECT @@global.innodb_autoextend_increment ; SET @@global.innodb_autoextend_increment = ON; SELECT @@global.innodb_autoextend_increment ; + --echo '#---------------------FN_DYNVARS_046_07----------------------#' -################################################################### +################################################################### # Check if TRUE and FALSE values can be used on variable # -################################################################### - +################################################################### SET @@global.innodb_autoextend_increment = TRUE; SELECT @@global.innodb_autoextend_increment ; SET @@global.innodb_autoextend_increment = FALSE; SELECT @@global.innodb_autoextend_increment ; -############################## + +############################## # Restore initial value # ############################## +SET @@global.innodb_autoextend_increment = @global_start_value; -#SET @@global.innodb_autoextend_increment = @global_start_value; -#SELECT @@global.innodb_autoextend_increment ; - ############################################################### -# END OF innodb_autoextend_increment TESTS # -############################################################### +# END OF innodb_autoextend_increment TESTS # +############################################################### + diff --git a/mysql-test/t/innodb_autoinc_lock_mode_func.test b/mysql-test/t/innodb_autoinc_lock_mode_func.test index 1ff7868ce87..89c1c80a6dc 100644 --- a/mysql-test/t/innodb_autoinc_lock_mode_func.test +++ b/mysql-test/t/innodb_autoinc_lock_mode_func.test @@ -11,11 +11,12 @@ # Creation Date: 2008-03-08 # # Author: Rizwan Maredia # # # -#Description:Test Cases of Dynamic System Variable innodb_autoextend_increment# -# that checks the behavior of this variable # +# Description: # +# Test Cases of Dynamic System Variable innodb_autoextend_increment that # +# checks the behavior of this variable # # # -# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # -# server-system-variables.html # +# Reference: # +# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html # # # ############################################################################### @@ -43,12 +44,14 @@ 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'); --echo 'the new auto incremented value should be 104' -SELECT * from t1; +SELECT * FROM t1; + +DROP TABLE t1; ############################################################### # End of functionality Testing for innodb_autoinc_lock_mode # diff --git a/mysql-test/t/innodb_fast_shutdown_basic.test b/mysql-test/t/innodb_fast_shutdown_basic.test index 5e108708716..7de63332844 100644 --- a/mysql-test/t/innodb_fast_shutdown_basic.test +++ b/mysql-test/t/innodb_fast_shutdown_basic.test @@ -9,54 +9,51 @@ # # # # # Creation Date: 2008-02-20 # -# Author: Sharique Abdullah # +# Author: Sharique Abdullah # # # -# Description: Test Cases of Dynamic System Variable innodb_fast_shutdown # -# that checks the behavior of this variable in the following ways# +# Description: # +# Test Cases of Dynamic System Variable innodb_fast_shutdown that checks # +# the behavior of this variable in the following ways # # * Default Value # # * Valid & Invalid values # # * Scope & Access method # # * Data Integrity # # # -# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # -# server-system-variables.html # +# Reference: # +# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html # # # ############################################################################### --source include/have_innodb.inc --source include/load_sysvars.inc -######################################################################## +######################################################################## # START OF innodb_fast_shutdown TESTS # -######################################################################## +######################################################################## -############################################################################## +############################################################################## # Saving initial value of innodb_fast_shutdown in a temporary variable # -############################################################################## - - +############################################################################## SET @global_start_value = @@global.innodb_fast_shutdown; SELECT @@global.innodb_fast_shutdown; - + --echo '#--------------------FN_DYNVARS_042_01------------------------#' -######################################################################## +######################################################################## # Display the DEFAULT value of innodb_fast_shutdown # -######################################################################## +######################################################################## SET @@global.innodb_fast_shutdown = 0; SET @@global.innodb_fast_shutdown = DEFAULT; SELECT @@global.innodb_fast_shutdown; - --echo '#---------------------FN_DYNVARS_042_02-------------------------#' -############################################################################# +############################################################################# # Check if innodb_fast_shutdown can be accessed with and without @@ sign # -############################################################################# - +############################################################################# --ERROR ER_GLOBAL_VARIABLE SET innodb_fast_shutdown = 1; @@ -70,25 +67,26 @@ SELECT @@global.innodb_fast_shutdown; --echo '#--------------------FN_DYNVARS_042_03------------------------#' -######################################################################## -# Change the value of innodb_fast_shutdown to a valid value # -######################################################################## +########################################################################## +# Change the value of innodb_fast_shutdown to a valid value # +########################################################################## SET @@global.innodb_fast_shutdown = 0; SELECT @@global.innodb_fast_shutdown; SET @@global.innodb_fast_shutdown = 1; SELECT @@global.innodb_fast_shutdown; -## a value of 2 is used to just flush logs and then shutdown cold. Not supported on Netware +## A value of 2 is used to just flush logs and then shutdown cold. +## Not supported on Netware SET @@global.innodb_fast_shutdown = 2; SELECT @@global.innodb_fast_shutdown; + --echo '#--------------------FN_DYNVARS_042_04-------------------------#' -########################################################################### +########################################################################### # Change the value of innodb_fast_shutdown to invalid value # -########################################################################### - +########################################################################### SET @@global.innodb_fast_shutdown = -1; SELECT @@global.innodb_fast_shutdown; @@ -121,11 +119,10 @@ SET @@global.innodb_fast_shutdown = "0"; SELECT @@global.innodb_fast_shutdown; - --echo '#-------------------FN_DYNVARS_042_05----------------------------#' -########################################################################### +########################################################################### # Test if accessing session innodb_fast_shutdown gives error # -########################################################################### +########################################################################### --Error ER_GLOBAL_VARIABLE SET @@session.innodb_fast_shutdown = 0; @@ -136,32 +133,30 @@ SET @@innodb_fast_shutdown = 0; SET @@local.innodb_fast_shutdown = 0; - --echo '#----------------------FN_DYNVARS_042_06------------------------#' -######################################################################### +######################################################################### # Check if the value in SESSION Table contains variable value # ######################################################################### -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'; --echo '#----------------------FN_DYNVARS_042_07------------------------#' -######################################################################### -# Check if the value in GLOBAL Table matches value in variable # +######################################################################### +# Check if the value in GLOBAL Table matches value in variable # ######################################################################### 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'; --echo '#---------------------FN_DYNVARS_042_08-------------------------#' -################################################################### +################################################################### # Check if ON and OFF values can be used on variable # -################################################################### - +################################################################### --Error ER_WRONG_TYPE_FOR_VAR SET @@global.innodb_fast_shutdown = OFF; @@ -171,23 +166,25 @@ SELECT @@global.innodb_fast_shutdown; SET @@global.innodb_fast_shutdown = ON; SELECT @@global.innodb_fast_shutdown; + --echo '#---------------------FN_DYNVARS_042_09----------------------#' -################################################################### +################################################################### # Check if TRUE and FALSE values can be used on variable # -################################################################### +################################################################### SET @@global.innodb_fast_shutdown = TRUE; SELECT @@global.innodb_fast_shutdown; SET @@global.innodb_fast_shutdown = FALSE; SELECT @@global.innodb_fast_shutdown; -############################## + +############################## # Restore initial value # ############################## -#SET @@innodb_fast_shutdown = @global_start_value; -#SELECT @@innodb_fast_shutdown; +SET @@global.innodb_fast_shutdown = @global_start_value; +SELECT @@global.innodb_fast_shutdown; -######################################################################## +######################################################################## # END OF innodb_fast_shutdown TESTS # -######################################################################## +######################################################################## diff --git a/mysql-test/t/innodb_max_dirty_pages_pct_func.test b/mysql-test/t/innodb_max_dirty_pages_pct_func.test index 4b2af554810..52ebc2f4c49 100644 --- a/mysql-test/t/innodb_max_dirty_pages_pct_func.test +++ b/mysql-test/t/innodb_max_dirty_pages_pct_func.test @@ -11,33 +11,31 @@ # Creation Date: 2008-03-08 # # Author: Rizwan # # # -#Description: Test Cases of Dynamic System Variable innodb_max_dirty_pages_pct# -# that checks the behavior of this variable # +# Description: # +# Test cases of Dynamic System Variable innodb_max_dirty_pages_pct that # +# checks the behavior of this variable # # # -# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # -# server-system-variables.html # +# Reference: # +# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html # # # ############################################################################### --source include/have_innodb.inc ---echo '#--------------------FN_DYNVARS_044_02-------------------------#' -########################################################################### -# Check if setting innodb_max_dirty_pages_pct is changed in new connection# -########################################################################### -SET @old_innodb_max_dirty_pages_pct= @@global.innodb_max_dirty_pages_pct; +SET @start_value= @@global.innodb_max_dirty_pages_pct; + +--echo '#--------------------FN_DYNVARS_044_02-------------------------#' +############################################################################ +# Check if setting innodb_max_dirty_pages_pct is changed in new connection # +############################################################################ SET @@global.innodb_max_dirty_pages_pct = 80; --echo 'connect (con1,localhost,root,,,,)' connect (con1,localhost,root,,,,); ---echo 'connection con1' -connection con1; SELECT @@global.innodb_max_dirty_pages_pct; SET @@global.innodb_max_dirty_pages_pct = 70; --echo 'connect (con2,localhost,root,,,,)' connect (con2,localhost,root,,,,); ---echo 'connection con2' -connection con2; SELECT @@global.innodb_max_dirty_pages_pct; disconnect con2; disconnect con1; @@ -60,6 +58,11 @@ DROP FUNCTION IF EXISTS dirty_pct; DROP TABLE IF EXISTS t1; --enable_warnings +CREATE TABLE t1( +a INT AUTO_INCREMENT PRIMARY KEY, +b CHAR(200) +)ENGINE=INNODB; + DELIMITER //; CREATE PROCEDURE add_records(IN NUM INT) BEGIN @@ -76,21 +79,21 @@ BEGIN DECLARE res DECIMAL(20,17); DECLARE a1,b1 VARCHAR(256); DECLARE a2,b2 VARCHAR(256); - DECLARE dirty CURSOR FOR SELECT * FROM information_schema.global_status + DECLARE dirty CURSOR FOR SELECT * FROM information_schema.global_status WHERE variable_name LIKE 'Innodb_buffer_pool_pages_dirty' - UNION SELECT * FROM information_schema.session_status + UNION SELECT * FROM information_schema.session_status WHERE variable_name LIKE 'Innodb_buffer_pool_pages_dirty'; DECLARE total CURSOR FOR SELECT * FROM information_schema.global_status WHERE variable_name LIKE 'Innodb_buffer_pool_pages_total' UNION SELECT * FROM information_schema.session_status WHERE variable_name LIKE 'Innodb_buffer_pool_pages_total'; - + OPEN dirty; OPEN total; FETCH dirty INTO a1, b1; FETCH total INTO a2, b2; - + SET res = ( CONVERT(b1,DECIMAL)*100)/CONVERT(b2,DECIMAL); CLOSE dirty; @@ -104,7 +107,7 @@ BEGIN SET pct = dirty_pct(); SET last = 0; - WHILE (pct<NUM and pct<100) DO + WHILE (pct>NUM and pct<100) DO CALL add_records(500); SET pct = dirty_pct(); IF (pct<last) THEN @@ -126,11 +129,6 @@ END// DELIMITER ;// -CREATE TABLE t1( -a INT AUTO_INCREMENT PRIMARY KEY, -b CHAR(200) -)ENGINE=INNODB; - --enable_query_log #========================================================== @@ -141,26 +139,28 @@ SET @@global.innodb_max_dirty_pages_pct = 10; FLUSH STATUS; -# add rows until dirty pages pct is less than this value +# Add rows until dirty pages pct is less than this value CALL add_until(10); -# give server some time to flush dirty pages +# Give the server some time to flush dirty pages FLUSH TABLES; CALL add_records(500); ---echo '--sleep 5' ---sleep 5 + +# Execute dirty_pct (wait) until dirty pages < 10% +# Use polling to execute dirty_pct ina loop +let $wait_condition= SELECT dirty_pct() < 10; +--source include/wait_condition.inc --echo 'We expect dirty pages pct to be BELOW_MAX' CALL check_pct(10); -#SHOW STATUS LIKE 'innodb%'; - 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 = @old_innodb_max_dirty_pages_pct; + +SET @@global.innodb_max_dirty_pages_pct= @start_value; ################################################################## # End of functionality Testing for innodb_max_dirty_pages_pct # diff --git a/mysql-test/t/innodb_table_locks_func.test b/mysql-test/t/innodb_table_locks_func.test index d233a2a5240..6638a20c926 100644 --- a/mysql-test/t/innodb_table_locks_func.test +++ b/mysql-test/t/innodb_table_locks_func.test @@ -14,8 +14,8 @@ # Description: Test Cases of Dynamic System Variable innodb_table_locks # # that checks the behavior of this variable # # # -# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # -# server-system-variables.html # +# Reference: # +# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html # # # ############################################################################### @@ -24,9 +24,12 @@ --echo '#--------------------FN_DYNVARS_048_01-------------------------#' #################################################################### -# Check if setting innodb_table_locks is changed in new connection # +# Check if setting innodb_table_locks is changed in new connection # #################################################################### +SET @start_value= @@global.innodb_table_locks; +SELECT @start_value; + SET @@global.innodb_table_locks = OFF; --echo 'connect (con1,localhost,root,,,,)' connect (con1,localhost,root,,,,); @@ -80,6 +83,9 @@ UNLOCK tables; DROP TABLE t1; disconnect con2; +connection default; +SET @@global.innodb_table_locks= @start_value; +SELECT @@global.innodb_table_locks; ########################################################## # End of functionality Testing for innodb_table_locks # diff --git a/mysql-test/t/insert_update.test b/mysql-test/t/insert_update.test index 67f21731afe..de38ae0b0d3 100644 --- a/mysql-test/t/insert_update.test +++ b/mysql-test/t/insert_update.test @@ -76,7 +76,7 @@ INSERT t1 SELECT a,b,c FROM t2 WHERE d=1 ON DUPLICATE KEY UPDATE c=t1.c+100; SELECT * FROM t1; INSERT t1 SET a=5 ON DUPLICATE KEY UPDATE b=0; SELECT * FROM t1; ---error 1052 +--error ER_NON_UNIQ_ERROR INSERT t1 SELECT a,b,c FROM t2 WHERE d=2 ON DUPLICATE KEY UPDATE c=c+VALUES(a); INSERT t1 SELECT a,b,c FROM t2 WHERE d=2 ON DUPLICATE KEY UPDATE c=t1.c+VALUES(t1.a); SELECT *, VALUES(a) FROM t1; @@ -95,9 +95,9 @@ insert ignore into t1 select a from t1 as t2 on duplicate key update a=t1.a+1 ; select * from t1; insert into t1 select 1 on duplicate key update a=2; select * from t1; ---error 1052 +--error ER_NON_UNIQ_ERROR insert into t1 select a from t1 on duplicate key update a=a+1 ; ---error 1052 +--error ER_NON_UNIQ_ERROR insert ignore into t1 select a from t1 on duplicate key update a=t1.a+1 ; drop table t1; @@ -171,13 +171,13 @@ SET SQL_MODE = 'TRADITIONAL'; CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL); ---error 1364 +--error ER_NO_DEFAULT_FOR_FIELD INSERT INTO t1 (a) VALUES (1); ---error 1364 +--error ER_NO_DEFAULT_FOR_FIELD INSERT INTO t1 (a) VALUES (1) ON DUPLICATE KEY UPDATE a = b; ---error 1364 +--error ER_NO_DEFAULT_FOR_FIELD INSERT INTO t1 (a) VALUES (1) ON DUPLICATE KEY UPDATE b = b; SELECT * FROM t1; @@ -278,7 +278,7 @@ INSERT INTO t1 (id,c1) VALUES (1,10); SELECT * FROM t1; CREATE TABLE t2 (id INT, c1 INT); INSERT INTO t2 VALUES (1,NULL), (2,2); ---error 1048 +--error ER_BAD_NULL_ERROR INSERT INTO t1 (id,c1) SELECT 1,NULL ON DUPLICATE KEY UPDATE c1=NULL; SELECT * FROM t1; @@ -290,6 +290,7 @@ INSERT IGNORE INTO t1 (id,c1) SELECT * FROM t2 SELECT * FROM t1; DROP TABLE t1; +DROP TABLE t2; # # Bug#28904: INSERT .. ON DUPLICATE was silently updating rows when it diff --git a/mysql-test/t/key_buffer_size_func.test b/mysql-test/t/key_buffer_size_func.test index 05c591678dc..8ee305a3ac1 100644 --- a/mysql-test/t/key_buffer_size_func.test +++ b/mysql-test/t/key_buffer_size_func.test @@ -14,13 +14,13 @@ # Description: Test Cases of Dynamic System Variable key_buffer_size # # that checks the functionality of this variable # # # -# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # -# server-system-variables.html # +# Reference: # +# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html # # # ############################################################################### --disable_warnings -drop table if exists t1; +DROP TABLE IF EXISTS t1; --enable_warnings ######################### @@ -30,12 +30,14 @@ drop table if exists t1; --echo ## 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; --echo '#--------------------FN_DYNVARS_055_01-------------------------#' @@ -64,37 +66,60 @@ SELECT @@global.key_buffer_size; --echo ## 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'); - ---echo ## Verifying status of reading & writing variables ## -# Disabled due to differences in results -#show status like 'Key%'; +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'); + +--echo ## Key_reads must be zero (no disk access) ## +show status like 'Key_reads'; --echo ## Switching to connection test_con2 ## connection test_con2; ---echo ## Verifying status of reading & writing variables ## -# Disabled due to differences in results -#show status like 'Key%'; +--echo ## Key_reads must be zero (no disk access) ## +show status like 'Key_reads'; + +CONNECTION default; +--disable_warnings +SET @@global.key_buffer_size = 36; +--enable_warnings + +--echo ## Connecting with connection test_con1 ## +CONNECTION test_con1; + +--echo ## 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'); + +--echo ## Key_reads must be zero (no disk access) ## +show status like 'Key_reads'; ############################################################ # Disconnecting all connection & dropping table # ############################################################ --echo ## Dropping table ## -DROP table if exists t1; +DROP TABLE IF EXISTS t1; --echo ## Disconnecting both the connections ## DISCONNECT test_con2; DISCONNECT test_con1; +connection default; +SET @@global.key_buffer_size= @start_value; diff --git a/mysql-test/t/log_basic.test b/mysql-test/t/log_basic.test index 16d7a4bfe7f..f195eae4153 100644 --- a/mysql-test/t/log_basic.test +++ b/mysql-test/t/log_basic.test @@ -1,6 +1,6 @@ ###################### mysql-test\t\log_basic.test ############################ # # -# Variable Name: log_basic # +# Variable Name: log_basic # # Scope: GLOBAL # # Access Type: Dynamic # # Data Type: string # @@ -11,15 +11,16 @@ # Creation Date: 2008-02-07 # # Author: Rizwan # # # -# Description: Test Cases of Dynamic System Variable log # -# that checks the behavior of this variable in the following ways# +# Description: # +# Test Cases of Dynamic System Variable log that checks the behavior of # +# this variable in the following ways # # * Default Value # # * Valid & Invalid values # # * Scope & Access method # # * Data Integrity # # # -# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # -# server-system-variables.html # +# Reference: # +# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html # # # ############################################################################### @@ -34,6 +35,8 @@ # Accessing variable # ############################################################# +SET @start_log= @@global.log; + SELECT @@global.log AS INIT_VALUE; SELECT @@log AS INIT_VALUE; @@ -47,14 +50,17 @@ SET global log = 0; --echo '#--------------------FN_DYNVARS_062_02-------------------------#' -################################################################################### -# Check if the value in GLOBAL Table matches value in variable # -################################################################################### +################################################################################ +# Check if the value in GLOBAL Table matches value in variable # +################################################################################ # We can only access log value from information schema global_variables table -SELECT VARIABLE_VALUE -FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES +SELECT VARIABLE_VALUE +FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='log'; +SET @@global.log= @start_log; + ############################################ # END OF log TESTS # ############################################ + diff --git a/mysql-test/t/log_bin_trust_function_creators_func.test b/mysql-test/t/log_bin_trust_function_creators_func.test index 52483ec281a..265911a0207 100644 --- a/mysql-test/t/log_bin_trust_function_creators_func.test +++ b/mysql-test/t/log_bin_trust_function_creators_func.test @@ -30,16 +30,16 @@ drop table if exists t1; # Setting initial value of variable to 0 and verifying whether user # is allowed to create function or not. ######################################################################## +SET @start_value= @@global.log_bin_trust_function_creators; --echo ## Creating new user tt ## -CREATE user tt@localhost; +CREATE USER tt@localhost; --echo ## Setting value of variable to 0 ## -SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators; SET @@global.log_bin_trust_function_creators = 0; --echo ## Creating new table t2 ## -create table t2 (a INT); +CREATE TABLE t2 (a INT); --echo ## Creating & connecting with new connection test_con1 ## connect (test_con1,localhost,tt,,); @@ -52,7 +52,7 @@ SELECT @@sql_log_bin; delimiter |; 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; @@ -70,8 +70,8 @@ INSERT INTO t1 VALUES (1),(2),(3); SELECT f1(a) FROM t1; --echo ## Dropping function f1 & table t1 ## -drop function f1; -drop table t1; +DROP FUNCTION f1; +DROP TABLE t1; --echo '#--------------------FN_DYNVARS_063_02-------------------------#' ######################################################################## @@ -97,7 +97,7 @@ SELECT @@sql_log_bin; delimiter |; 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; @@ -112,15 +112,16 @@ INSERT INTO t1 VALUES (1),(2),(3); SELECT f1(a) FROM t1; --echo ## Dropping function f1 ## -drop function f1; +DROP FUNCTION f1; --echo ## Dropping table t1 & t2 ## -drop table t1,t2; +DROP TABLE t1,t2; ---echo ## Disconnecting both the connections ## +--echo ## Disconnecting test_con2## disconnect test_con2; connection default; DROP USER tt@localhost; -SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators; +SET @@global.log_bin_trust_function_creators= @start_value; + diff --git a/mysql-test/t/log_queries_not_using_indexes_basic.test b/mysql-test/t/log_queries_not_using_indexes_basic.test index d6de4d4ca02..f1152c07176 100644 --- a/mysql-test/t/log_queries_not_using_indexes_basic.test +++ b/mysql-test/t/log_queries_not_using_indexes_basic.test @@ -1,5 +1,3 @@ - - ################## mysql-test\t\log_queries_not_using_indexes_basic.test ###### # # # Variable Name: log_queries_not_using_indexes # @@ -9,95 +7,126 @@ # # # # # Creation Date: 2008-02-07 # -# Author : Sharique Abdullah # -# # +# Author : Sharique Abdullah # # # -# Description:Test Cases of Dynamic System Variable log_queries_not_using_indexes# +# Description:Test Cases of Dynamic System Variable # +# log_queries_not_using_indexes # # that checks the behavior of this variable in the following ways # # * Value Check # # * Scope Check # # # -# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # -# server-system-variables.html # +# Reference: # +# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html # # # ############################################################################### ---echo '#---------------------BS_STVARS_041_01----------------------#' +SET @start_value= @@global.log_queries_not_using_indexes; + #################################################################### -# Displaying default value # +# Valid values for boolean # #################################################################### -SELECT COUNT(@@GLOBAL.log_queries_not_using_indexes); ---echo 1 Expected +SET @@global.log_queries_not_using_indexes= DEFAULT; +SELECT @@global.log_queries_not_using_indexes; +SET @@global.log_queries_not_using_indexes= TRUE; +SELECT @@global.log_queries_not_using_indexes; ---echo '#---------------------BS_STVARS_041_02----------------------#' -# -# Test case for Bug #35433 -# -#################################################################### -# Check if Value can set # -#################################################################### +SET @@global.log_queries_not_using_indexes= true; +SELECT @@global.log_queries_not_using_indexes; + +SET @@global.log_queries_not_using_indexes= 0; +SELECT @@global.log_queries_not_using_indexes; + +SET @@global.log_queries_not_using_indexes= 1; +SELECT @@global.log_queries_not_using_indexes; + +SET @goodvar= TRUE; +SET @@global.log_queries_not_using_indexes= @goodvar; +SELECT @@global.log_queries_not_using_indexes; -#--error ER_INCORRECT_GLOBAL_LOCAL_VAR -SET @@GLOBAL.log_queries_not_using_indexes=1; ---echo Expected error 'Read only variable' ---ECHO "BUG:It should give error on setting this variable as it is readonly variable" -SELECT COUNT(@@GLOBAL.log_queries_not_using_indexes); ---echo 1 Expected +SET GLOBAL log_queries_not_using_indexes= DEFAULT; +SELECT @@global.log_queries_not_using_indexes; +SET GLOBAL log_queries_not_using_indexes= ON; +SELECT @@global.log_queries_not_using_indexes; +SET GLOBAL log_queries_not_using_indexes= OFF; +SELECT @@global.log_queries_not_using_indexes; +SET GLOBAL log_queries_not_using_indexes= -0; +SELECT @@global.log_queries_not_using_indexes; + +SET GLOBAL log_queries_not_using_indexes= 0.00; +SELECT @@global.log_queries_not_using_indexes; + +SET GLOBAL log_queries_not_using_indexes= -0.0; +SELECT @@global.log_queries_not_using_indexes; + +SET GLOBAL log_queries_not_using_indexes= 001.00; +SELECT @@global.log_queries_not_using_indexes; + +SET GLOBAL log_queries_not_using_indexes= +1.0; +SELECT @@global.log_queries_not_using_indexes; + +SET GLOBAL log_queries_not_using_indexes= +0; +SELECT @@global.log_queries_not_using_indexes; + +SET GLOBAL log_queries_not_using_indexes= +0.000000; +SELECT @@global.log_queries_not_using_indexes; + +SET GLOBAL log_queries_not_using_indexes= 0000.00000; +SELECT @@global.log_queries_not_using_indexes; + +SET GLOBAL log_queries_not_using_indexes= .0; +SELECT @@global.log_queries_not_using_indexes; ---echo '#---------------------BS_STVARS_041_03----------------------#' ################################################################# # Check if the value in GLOBAL Table matches value in variable # ################################################################# -SELECT @@GLOBAL.log_queries_not_using_indexes = VARIABLE_VALUE -FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES -WHERE VARIABLE_NAME='log_queries_not_using_indexes'; ---echo 1 Expected +--error ER_WRONG_VALUE_FOR_VAR +SET @@global.log_queries_not_using_indexes= 'DEFAULT'; + +--error ER_WRONG_VALUE_FOR_VAR +SET @@global.log_queries_not_using_indexes= 'true'; -SELECT COUNT(@@GLOBAL.log_queries_not_using_indexes); ---echo 1 Expected +--error ER_WRONG_VALUE_FOR_VAR +SET @@global.log_queries_not_using_indexes= BLABLA; -SELECT COUNT(VARIABLE_VALUE) -FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES -WHERE VARIABLE_NAME='log_queries_not_using_indexes'; ---echo 1 Expected +--error ER_WRONG_VALUE_FOR_VAR +SET @@global.log_queries_not_using_indexes= 25; +--error ER_WRONG_VALUE_FOR_VAR +SET @@global.log_queries_not_using_indexes= 12.34; +--error ER_WRONG_VALUE_FOR_VAR +SET GLOBAL log_queries_not_using_indexes= -1; ---echo '#---------------------BS_STVARS_041_04----------------------#' -################################################################################ -# Check if accessing variable with and without GLOBAL point to same variable # -################################################################################ -SELECT @@log_queries_not_using_indexes = @@GLOBAL.log_queries_not_using_indexes; ---echo 1 Expected +SET @badvar= 'true'; +--error ER_WRONG_VALUE_FOR_VAR +SET @@global.log_queries_not_using_indexes= @badvar; +--error ER_WRONG_VALUE_FOR_VAR +SET GLOBAL log_queries_not_using_indexes= 'DEFAULT'; +--error ER_GLOBAL_VARIABLE +SET log_queries_not_using_indexes= TRUE; ---echo '#---------------------BS_STVARS_041_05----------------------#' -################################################################################ -# Check if log_queries_not_using_indexes can be accessed with and without @@ sign # -################################################################################ +--error ER_GLOBAL_VARIABLE +SET SESSION log_queries_not_using_indexes= TRUE; -SELECT COUNT(@@log_queries_not_using_indexes); ---echo 1 Expected +--error ER_GLOBAL_VARIABLE +SET @@session.log_queries_not_using_indexes= TRUE; ---Error ER_INCORRECT_GLOBAL_LOCAL_VAR -SELECT COUNT(@@local.log_queries_not_using_indexes); ---echo Expected error 'Variable is a GLOBAL variable' +--error ER_GLOBAL_VARIABLE +SET LOCAL log_queries_not_using_indexes= TRUE; ---Error ER_INCORRECT_GLOBAL_LOCAL_VAR -SELECT COUNT(@@SESSION.log_queries_not_using_indexes); ---echo Expected error 'Variable is a GLOBAL variable' +--error ER_PARSE_ERROR +SET @@global log_queries_not_using_indexes= TRUE; -SELECT COUNT(@@GLOBAL.log_queries_not_using_indexes); ---echo 1 Expected +--error ER_PARSE_ERROR +SET @@SESSION log_queries_not_using_indexes= TRUE; ---Error ER_BAD_FIELD_ERROR -SELECT log_queries_not_using_indexes = @@SESSION.log_queries_not_using_indexes; ---echo Expected error 'Readonly variable' +SET @@global.log_queries_not_using_indexes= @start_value; diff --git a/mysql-test/t/log_state.test b/mysql-test/t/log_state.test index f72c0b751ee..8b638e21000 100644 --- a/mysql-test/t/log_state.test +++ b/mysql-test/t/log_state.test @@ -1,7 +1,15 @@ +# hhunger 08.22.2008: with check-testcases differences due to bug#38889 using +# slow_query_log_file or general_log_file + -- source include/not_embedded.inc --source include/have_csv.inc --disable_ps_protocol +# save default value to set them back at the end of the test +set @start_general_log= @@global.general_log; +set @start_slow_query_log= @@global.slow_query_log; +set @start_general_log_file= @@global.general_log_file; + set global general_log= OFF; truncate table mysql.general_log; truncate table mysql.slow_log; @@ -71,16 +79,16 @@ show variables like 'slow_query_log_file'; show variables like 'log_output'; # Can't set general_log_file to a non existing file ---error 1231 +--error ER_WRONG_VALUE_FOR_VAR set global general_log_file='/not exiting path/log.master'; # Can't set general_log_file to a directory --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR ---error 1231 +--error ER_WRONG_VALUE_FOR_VAR eval set global general_log_file='$MYSQLTEST_VARDIR'; # Can't set general_log_file to empty string ---error 1231 +--error ER_WRONG_VALUE_FOR_VAR set global general_log_file=''; --replace_column 2 # @@ -219,13 +227,13 @@ SET GLOBAL slow_query_log = @old_slow_log_state; set @old_general_log_file= @@global.general_log_file; set @old_slow_query_log_file= @@global.slow_query_log_file; ---error 1231 +--error ER_WRONG_VALUE_FOR_VAR set global general_log_file= concat('/not exiting path/log.maste', 'r'); ---error 1231 +--error ER_WRONG_VALUE_FOR_VAR set global general_log_file= NULL; ---error 1231 +--error ER_WRONG_VALUE_FOR_VAR set global slow_query_log_file= concat('/not exiting path/log.maste', 'r'); ---error 1231 +--error ER_WRONG_VALUE_FOR_VAR set global slow_query_log_file= NULL; set global general_log_file= @old_general_log_file; @@ -267,8 +275,14 @@ SET GLOBAL slow_query_log_file = @slow_query_log_file_saved; # Cleanup (must be done last to avoid delayed 'Quit' message in general log) # disconnect con1; +# set back the saved default values +connection default; +set @@global.general_log= @start_general_log; +set @@global.slow_query_log= @start_slow_query_log; +set @@global.general_log_file= @start_general_log_file; # Remove the log files that was created in the "default location" # i.e var/run --remove_file $MYSQLTEST_VARDIR/run/mysqld.log --remove_file $MYSQLTEST_VARDIR/run/mysqld-slow.log + diff --git a/mysql-test/t/log_tables.test b/mysql-test/t/log_tables.test index 3047d16d3b6..22cd219534f 100644 --- a/mysql-test/t/log_tables.test +++ b/mysql-test/t/log_tables.test @@ -7,6 +7,9 @@ # check that CSV engine was compiled in --source include/have_csv.inc +SET @old_general_log_state = @@global.general_log; +SET @old_slow_log_state = @@global.slow_query_log; + --disable_ps_protocol use mysql; @@ -444,6 +447,7 @@ use test; # AUTO_INCREMENT numbers) # +SET @my_log_output= @@global.log_output; SET GLOBAL LOG_OUTPUT = 'TABLE'; ## test the general log @@ -509,6 +513,9 @@ FLUSH LOGS; ALTER TABLE mysql.slow_log DROP COLUMN seq; ALTER TABLE mysql.slow_log ENGINE = CSV; +SET GLOBAL general_log = @old_general_log_state; +SET GLOBAL slow_query_log = @old_slow_log_state; + # # Bug#25422 (Hang with log tables) # @@ -964,5 +971,7 @@ DROP TABLE t1; TRUNCATE TABLE mysql.slow_log; +SET GLOBAL log_output= @my_log_output; SET GLOBAL slow_query_log = @old_slow_log_state; SET SESSION long_query_time =@old_long_query_time; + diff --git a/mysql-test/t/max_allowed_packet_func.test b/mysql-test/t/max_allowed_packet_func.test index 37ca15f85f0..c6a2a561234 100644 --- a/mysql-test/t/max_allowed_packet_func.test +++ b/mysql-test/t/max_allowed_packet_func.test @@ -14,16 +14,16 @@ # Description: Test Cases of Dynamic System Variable max_allowed_packet # # that checks the functionality of this variable # # # -# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # -# server-system-variables.html # +# Reference: # +# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html # # # ############################################################################### #due to lost connection on win64 --source include/not_windows.inc -let $start_global_value = `SELECT @@global.max_allowed_packet`; ---echo $start_global_value +SET @start_max_allowed_packet= @@global.max_allowed_packet; +SET @start_value= @@global.net_buffer_length; --disable_warnings drop table if exists t1; @@ -43,11 +43,11 @@ name BLOB --echo '#--------------------FN_DYNVARS_070_01-------------------------#' ############################################################################### -# Setting initial value of max_allowed_packet to 1024 at session level and -# verifying its behavior after inserting data greater than 1024 bytes +# Setting initial value of max_allowed_packet to 1024 at session level and # +# verifying its behavior after inserting data greater than 1024 bytes # ############################################################################### ---echo ## Setting value of max_allowed packet and net_buffer_length to 1024 ## +--echo ## Setting value of max_allowed packet and net_buffer_length to 1024 ## SET @@session.max_allowed_packet = 1024; SET @@session.net_buffer_length = 1024; SELECT @@session.max_allowed_packet; @@ -67,10 +67,10 @@ SELECT * from t1; --echo '#--------------------FN_DYNVARS_070_02-------------------------#' ############################################################################### # Setting value of max_allowed_packet to 1024 at global level and -# verifying its behavior after inserting data greater than 1024 bytes +# verifying its behavior after inserting data greater than 1024 bytes ############################################################################### ---echo ## Setting value of max_allowed packet and net_buffer_length to 1024 ## +--echo ## 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; @@ -80,16 +80,13 @@ SELECT @@global.net_buffer_length; connect (test_con1,localhost,root,,); connection test_con1; ---echo ## Inserting and fetching data of length greater than 1024 ## +--echo ## Inserting and fetching data of length greater than 1024 ## --Error 0,ER_NET_PACKET_TOO_LARGE INSERT into t1(name) values("aaassssssssddddddddffffffgggggggg, askdlfjalsdkjfalksdjflaksdjfalkjdflaksjdflakjdflajsflajflajdfalsjfdlajfladjslfajdflajdsflajsflakjsdfla;kjflsdjkf;aljfa;lkdsfjla;sjlkajffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllakjsdffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffa;;;;;;;;;;;;;;;;;;;;;;;;;;;dsklfjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjkljffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffdkskkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); disconnect test_con1; connection default; drop table t1; -eval SET @@global.max_allowed_packet = $start_global_value; - ---echo ## Server disconnects after this case and error occurs that Server ## ---echo ## has gone away ## - +SET @@global.max_allowed_packet= @start_max_allowed_packet; +SET @@global.net_buffer_length= @start_value; diff --git a/mysql-test/t/max_seeks_for_key_func.test b/mysql-test/t/max_seeks_for_key_func.test index cdabd43328f..d49382b9f4f 100644 --- a/mysql-test/t/max_seeks_for_key_func.test +++ b/mysql-test/t/max_seeks_for_key_func.test @@ -1,11 +1,7 @@ - - - -#Test for max_seeks_for_key# - +# Test for max_seeks_for_key # --disable_warnings -drop table if exists t1; +DROP TABLE IF EXISTS t1; --enable_warnings @@ -14,11 +10,11 @@ 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; --echo '#--------------------FN_DYNVARS_084_01-------------------------#' ########################################################## @@ -35,15 +31,15 @@ SELECT @@session.max_seeks_for_key = 10; # Setting global value of variable and inserting data in table SET @@global.max_seeks_for_key = 20; SELECT @@global.max_seeks_for_key; -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; # Setting session value of variable and inserting data in table SET @@session.max_seeks_for_key = 2; SELECT @@session.max_seeks_for_key; -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; --echo '#--------------------FN_DYNVARS_084_02-------------------------#' @@ -63,31 +59,26 @@ SELECT @@session.max_seeks_for_key = 10; # Setting global value of variable and inserting data in table SET @@global.max_seeks_for_key = 20; SELECT @@global.max_seeks_for_key; -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; # Setting session value of variable and inserting data in table SET @@session.max_seeks_for_key = 2; SELECT @@session.max_seeks_for_key; -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; - - - - -##################################################### +###################################################### # Inserting values in table t and analyzing table # -##################################################### +###################################################### -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; -analyze table t1; +EXPLAIN SELECT STRAIGHT_JOIN * FROM t1,t1 AS t2 WHERE t1.b = t2.b; +ANALYZE TABLE t1; ################################################################### # Setting new value for max_seeks_for_key and anaylyzing table # @@ -96,9 +87,14 @@ analyze table t1; 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; SET MAX_SEEKS_FOR_KEY=DEFAULT; -drop table t1; +connection default; +disconnect test_con1; +disconnect test_con2; + +DROP TABLE t1; +SET @@global.max_seeks_for_key= @start_value; diff --git a/mysql-test/t/max_sort_length_func.test b/mysql-test/t/max_sort_length_func.test index e3197653f0d..74642a10861 100644 --- a/mysql-test/t/max_sort_length_func.test +++ b/mysql-test/t/max_sort_length_func.test @@ -9,14 +9,14 @@ # # # # # Creation Date: 2008-03-02 # -# Author: Sharique Abdullah # +# Author: Sharique Abdullah # # # -# Description: Test Cases of Dynamic System Variable max_prepared_stmt_count_fn# +# Description: Test Cases of Dynamic System Variable max_sort_length # # that checks behavior of this variable in the following ways # # * Functionality based on different values # # # -#Reference: http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html# -# option_mysqld_max_sort_length # +# Reference: # +# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html # # # ################################################################################ @@ -24,6 +24,8 @@ # Setup # +SET @start_value= @@global.max_sort_length; + SET @session_max_sort_length = @@Session.max_sort_length; @@ -186,7 +188,7 @@ SELECT c from t ORDER BY c, id; #Check if sorting is applied on BLOB type # ########################################### --echo Testing type BLOB -## Setting global value of variable and inserting data in table +# Setting global value of variable and inserting data in table SET @@global.max_sort_length = 30; SELECT @@global.max_sort_length; INSERT INTO t1 set c = repeat('x',29); @@ -211,7 +213,7 @@ SELECT c from t1 ORDER BY c, id; #Check if sorting is applied on CHAR type # ########################################### --echo Testing type CHAR -## Setting global value of variable and inserting data in table +# Setting global value of variable and inserting data in table SET @@global.max_sort_length = 30; SELECT @@global.max_sort_length; INSERT INTO t2 set c = repeat('x',29); @@ -251,3 +253,5 @@ DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; --enable_warnings +SET @@global.max_sort_length= @start_value; + diff --git a/mysql-test/t/myisam_data_pointer_size_func.test b/mysql-test/t/myisam_data_pointer_size_func.test index 36ad31acf6c..854209834d9 100644 --- a/mysql-test/t/myisam_data_pointer_size_func.test +++ b/mysql-test/t/myisam_data_pointer_size_func.test @@ -14,16 +14,17 @@ # Description: Test Cases of Dynamic System Variable myisam_data_pointer_size # # that checks the behavior of this variable # # # -# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # -# server-system-variables.html # +# Reference: # +# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html # # # ################################################################################ --echo '#--------------------FN_DYNVARS_093_01-------------------------#' ############################################################################### -# Check if setting myisam_data_pointer_size is changed in every new connection# +# Check if setting myisam_data_pointer_size is changed in every new connection# ############################################################################### +SET @start_value= @@global.myisam_data_pointer_size; SET @@global.myisam_data_pointer_size = 2; # con1 will be default connection from now on --echo 'connect (con1,localhost,root,,,,)' @@ -48,7 +49,6 @@ disconnect con2; --echo 'connection con1' connection con1; - #=========================================================== # Checking myisam_data_pointer_size is 2 #=========================================================== @@ -59,10 +59,7 @@ DROP PROCEDURE IF EXISTS sp_addRec; DROP TABLE IF EXISTS t1; --enable_warnings - - DELIMITER //; - CREATE PROCEDURE sp_addRec(IN count INT) BEGIN WHILE (count>0) DO @@ -70,7 +67,6 @@ BEGIN SET count = count -1; END WHILE; END // - DELIMITER ;// # setting 2 will allow data pointer to access files with size < 65536 @@ -105,6 +101,9 @@ SELECT count(*) from t1; DROP PROCEDURE sp_addRec; DROP TABLE t1; +connection default; +SET @@global.myisam_data_pointer_size= @start_value; + ################################################################ # End of functionality Testing for myisam_data_pointer_size # ################################################################ diff --git a/mysql-test/t/myisam_stats_method_func.test b/mysql-test/t/myisam_stats_method_func.test index 8b47015ba87..a317f8d93da 100644 --- a/mysql-test/t/myisam_stats_method_func.test +++ b/mysql-test/t/myisam_stats_method_func.test @@ -14,16 +14,18 @@ # Description: Test Cases of Dynamic System Variable myisam_stats_method # # that checks the behavior of this variable # # # -# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # -# server-system-variables.html # +# Reference: # +# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html # # # ############################################################################### --echo '#--------------------FN_DYNVARS_097_01-------------------------#' ##################################################################### -# Check if Setting myisam_stats_method is changed in new connection # +# Check if Setting myisam_stats_method is changed in new connection # ##################################################################### +SET @start_value = @@global.myisam_stats_method; + SET @@global.myisam_stats_method = nulls_equal; --echo 'connect (con1,localhost,root,,,,)' connect (con1,localhost,root,,,,); @@ -45,19 +47,19 @@ connection default; DROP TABLE IF EXISTS t1; --enable_warnings -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; #======================================= --echo 'default: NULLs considered unequal' #======================================= -SET myisam_stats_method=nulls_unequal; +SET myisam_stats_method = nulls_unequal; ANALYZE TABLE t1; SHOW INDEX FROM t1; INSERT INTO t1 VALUES (11); -DELETE FROM t1 WHERE a=11; +DELETE FROM t1 WHERE a = 11; CHECK TABLE t1; SHOW INDEX FROM t1; @@ -65,14 +67,14 @@ SHOW INDEX FROM t1; #===================================== --echo '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; -ANALYZE TABLE t1; +DELETE FROM t1 WHERE a = 11; +ANALYZE TABLE t1; SHOW INDEX FROM t1; INSERT INTO t1 VALUES (11); -DELETE FROM t1 WHERE a=11; +DELETE FROM t1 WHERE a = 11; CHECK TABLE t1; SHOW INDEX FROM t1; @@ -80,13 +82,13 @@ SHOW INDEX FROM t1; --echo 'Set nulls to be ignored' #===================================== -SET myisam_stats_method=nulls_ignored; -SHOW variables LIKE 'myisam_stats_method'; -drop TABLE t1; +SET myisam_stats_method = nulls_ignored; +SHOW VARIABLES LIKE 'myisam_stats_method'; +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'); @@ -98,9 +100,12 @@ DELETE FROM t1; ANALYZE TABLE t1; SHOW INDEX FROM t1; -SET myisam_stats_method=DEFAULT; +SET myisam_stats_method = DEFAULT; DROP TABLE t1; +SET @@global.myisam_stats_method= @start_value; + ######################################################## # End of functionality Testing for myisam_stats_method # ######################################################## + diff --git a/mysql-test/t/profiling.test b/mysql-test/t/profiling.test index 5cb3f66aa2b..275c2d77c4a 100644 --- a/mysql-test/t/profiling.test +++ b/mysql-test/t/profiling.test @@ -14,6 +14,7 @@ select @@profiling; set global profiling = ON; # But size is okay +set @start_value= @@global.profiling_history_size; set global profiling_history_size=100; show global variables like 'profil%'; @@ -261,6 +262,6 @@ drop function if exists f1; ## last thing in the file set session profiling = OFF; - +set global profiling_history_size= @start_value; ## --echo End of 5.0 tests diff --git a/mysql-test/t/query_cache_limit_func.test b/mysql-test/t/query_cache_limit_func.test index 8dd5cf42d3d..e90a0e6adda 100644 --- a/mysql-test/t/query_cache_limit_func.test +++ b/mysql-test/t/query_cache_limit_func.test @@ -9,14 +9,14 @@ # # # # # Creation Date: 2008-03-02 # -# Author: Sharique Abdullah # +# Author: Sharique Abdullah # # # # Description: Test Cases of Dynamic System Variable "Query_cache_limit" # # that checks behavior of this variable in the following ways # # * Functionality based on different values # # # -# Reference: http://dev.mysql.com/doc/refman/5.1/en # -# /server-system-variables.html#option_mysqld_Query_cache_limit # +# Reference: # +# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html # # # ############################################################################### @@ -28,6 +28,8 @@ # 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; --echo ** warnings ** --disable_warnings @@ -105,7 +107,7 @@ RESET QUERY CACHE; #set cache limit --echo ** set cache limit ** -SET @@GLOBAL.query_cache_limit=0; +SET @@GLOBAL.query_cache_limit = 0; #fetching results# --echo ** fetching results ** @@ -113,7 +115,6 @@ SELECT * FROM t; # Check status after setting value# --echo ** Check status after setting value ** -#let $newcachevalue1= query_get_value(SHOW STATUS LIKE 'Qcache_queries_in_cache', Value, 1); SHOW STATUS LIKE 'Qcache_not_cached'; --echo 1 Expected SHOW STATUS LIKE 'Qcache_queries_in_cache'; @@ -127,7 +128,7 @@ SHOW STATUS LIKE 'Qcache_queries_in_cache'; #set cache limit to default --echo ** set cache limit ** -SET @@GLOBAL.query_cache_limit=DEFAULT; +SET @@GLOBAL.query_cache_limit = DEFAULT; # Reset cache & flush status --echo ** Reset cache values ** @@ -143,7 +144,7 @@ SHOW STATUS LIKE 'Qcache_not_cached'; SHOW STATUS LIKE 'Qcache_queries_in_cache'; --echo 1 Expected -SET @@GLOBAL.query_cache_limit=0; +SET @@GLOBAL.query_cache_limit = 0; SHOW STATUS LIKE 'Qcache_not_cached'; --echo 0 Expected @@ -167,7 +168,10 @@ SHOW STATUS LIKE 'Qcache_queries_in_cache'; # 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; --disable_warnings DROP TABLE IF EXISTS t; --enable_warnings + diff --git a/mysql-test/t/query_cache_type_func.test b/mysql-test/t/query_cache_type_func.test index 64f332eb7c0..4679d1ec339 100644 --- a/mysql-test/t/query_cache_type_func.test +++ b/mysql-test/t/query_cache_type_func.test @@ -14,8 +14,8 @@ # Description: Test Cases of Dynamic System Variable "query_cache_type" # # that checks behavior of this variable in the following ways # # # -# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # -# server-system-variables.html#option_mysqld_query_cache_type # +# Reference: # +# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html # # # ############################################################################### @@ -36,6 +36,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; # # Creating test table @@ -305,6 +306,7 @@ 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; @@ -312,3 +314,4 @@ DROP PROCEDURE testProcHit; --echo Disconnecting con0 disconnect con0; + diff --git a/mysql-test/t/query_prealloc_size_func.test b/mysql-test/t/query_prealloc_size_func.test index 4f1e3e63e16..7267b9e317e 100644 --- a/mysql-test/t/query_prealloc_size_func.test +++ b/mysql-test/t/query_prealloc_size_func.test @@ -1,32 +1,32 @@ ############# mysql-test\t\query_prealloc_size_func.test ###################### -# # -# Variable Name: query_prealloc_size # -# Scope: GLOBAL & SESSION # -# Access Type: Dynamic # -# Data Type: integer # -# Default Value: 8192 # -# Values: 8192-4294967295 # -# # -# # -# Creation Date: 2008-02-22 # -# Author: Sharique Abdullah # -# # -# Description: Test Cases of Dynamic System Variable "query_prealloc_size" # -# that checks behavior of this variable in the following ways # -# * Default Value # -# * Valid & Invalid values # -# * Scope & Access method # -# * Cache behaviors # -# # -# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # -# server-system-variables.html#option_mysqld_query_prealloc_size # -# # +# # +# Variable Name: query_prealloc_size # +# Scope: GLOBAL & SESSION # +# Access Type: Dynamic # +# Data Type: integer # +# Default Value: 8192 # +# Values: 8192-4294967295 # +# # +# # +# Creation Date: 2008-02-22 # +# Author: Sharique Abdullah # +# # +# Description: Test Cases of Dynamic System Variable "query_prealloc_size" # +# that checks behavior of this variable in the following ways # +# * Default Value # +# * Valid & Invalid values # +# * Scope & Access method # +# * Cache behaviors # +# # +# Reference: # +# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html # +# # ############################################################################### --echo ** Setup ** ---echo +--echo # # Setup # @@ -35,16 +35,18 @@ # Save initial value # -CREATE TABLE t1 (id int auto_increment primary key, val text(200)); +SET @start_value = @@global.query_prealloc_size; -INSERT INTO t1 values(NULL,'a'); -INSERT INTO t1 values(NULL,'b'); -INSERT INTO t1 values(NULL,'c'); -INSERT INTO t1 values(NULL,'d'); +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; -SET SESSION query_prealloc_size = 8192; +SET SESSION query_prealloc_size = 8192; --echo '#----------------------------FN_DYNVARS_137_05-----------------#' @@ -84,3 +86,7 @@ connection default; disconnect con_int1; disconnect con_int2; +DROP TABLE t1; + +SET @@global.query_prealloc_size = @start_value; + diff --git a/mysql-test/t/slow_query_log_file_basic.test b/mysql-test/t/slow_query_log_file_basic.test index 2620cd5eabc..9125b686cad 100644 --- a/mysql-test/t/slow_query_log_file_basic.test +++ b/mysql-test/t/slow_query_log_file_basic.test @@ -10,6 +10,7 @@ # # # Creation Date: 2008-03-16 # # Author: Salman Rawala # +# Modified: HHunger 2008-09-11 set system var back to start value # # # # Description: Test Cases of Dynamic System Variable "slow_query_log_file" # # that checks behavior of this variable in the following ways # @@ -18,8 +19,8 @@ # * Scope & Access method # # * Data Integrity # # # -# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # -# server-system-variables.html#option_mysqld_slow_query_log_file # +# Reference: # +# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html # # ################################################################################ @@ -35,8 +36,6 @@ ########################################################################### SET @start_value = @@global.slow_query_log_file; -SELECT @start_value; - --echo '#---------------------FN_DYNVARS_004_01-------------------------#' ############################################### @@ -66,7 +65,8 @@ SELECT @@global.slow_query_log_file = VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='slow_query_log_file'; - +SET @@global.slow_query_log_file= @start_value; ##################################################### # END OF slow_query_log_file TESTS # ##################################################### + diff --git a/mysql-test/t/slow_query_log_file_func.test b/mysql-test/t/slow_query_log_file_func.test index 9a2ddae8c4a..32420fc294a 100644 --- a/mysql-test/t/slow_query_log_file_func.test +++ b/mysql-test/t/slow_query_log_file_func.test @@ -26,7 +26,7 @@ #################################################################### let $MYSQLD_DATADIR= `select @@datadir`; ---echo ## Checking if my_slow_test.log exist in servers datadir ## +--echo ## Checking if my_slow_test.log exists in servers datadir ## --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR --file_exists $MYSQLD_DATADIR/my_slow_test.log diff --git a/mysql-test/t/variables-notembedded.test b/mysql-test/t/variables-notembedded.test index 79011a89b36..20ce3a74532 100644 --- a/mysql-test/t/variables-notembedded.test +++ b/mysql-test/t/variables-notembedded.test @@ -28,3 +28,5 @@ set global slave_net_timeout=default; # sql_slave_skip_counter is write-only, so we can't save previous # value and restore it here. That's ok, because it's normally 0. set global sql_slave_skip_counter= 0; +set @@global.slave_net_timeout= @my_slave_net_timeout; + diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index 221f46605cd..7dbe72865d3 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -24,6 +24,7 @@ set @my_max_heap_table_size =@@global.max_heap_table_size; set @my_max_insert_delayed_threads=@@global.max_insert_delayed_threads; set @my_max_join_size =@@global.max_join_size; set @my_myisam_data_pointer_size =@@global.myisam_data_pointer_size; +set @my_myisam_max_sort_file_size =@@global.myisam_max_sort_file_size; set @my_net_buffer_length =@@global.net_buffer_length; set @my_net_write_timeout =@@global.net_write_timeout; set @my_net_read_timeout =@@global.net_read_timeout; @@ -34,7 +35,6 @@ set @my_server_id =@@global.server_id; set @my_slow_launch_time =@@global.slow_launch_time; set @my_storage_engine =@@global.storage_engine; set @my_thread_cache_size =@@global.thread_cache_size; - # case insensitivity tests (new in 5.0) set @`test`=1; select @test, @`test`, @TEST, @`TEST`, @"teSt"; @@ -750,7 +750,7 @@ set global delayed_queue_size =@my_delayed_queue_size; set global flush =@my_flush; set global flush_time =@my_flush_time; set global key_buffer_size =@my_key_buffer_size; -set global max_binlog_cache_size =default; #@my_max_binlog_cache_size; +set global max_binlog_cache_size =@my_max_binlog_cache_size; set global max_binlog_size =@my_max_binlog_size; set global max_connect_errors =@my_max_connect_errors; set global max_connections =@my_max_connections; @@ -761,6 +761,7 @@ set global max_join_size =@my_max_join_size; set global max_user_connections =default; set global max_write_lock_count =default; set global myisam_data_pointer_size =@my_myisam_data_pointer_size; +set global myisam_max_sort_file_size =@my_myisam_max_sort_file_size; set global net_buffer_length =@my_net_buffer_length; set global net_write_timeout =@my_net_write_timeout; set global net_read_timeout =@my_net_read_timeout; @@ -771,7 +772,6 @@ set global server_id =@my_server_id; set global slow_launch_time =@my_slow_launch_time; set global storage_engine =@my_storage_engine; set global thread_cache_size =@my_thread_cache_size; - # # Bug#28580 Repeatation of status variables # |