diff options
Diffstat (limited to 'mysql-test/t/status.test')
| -rw-r--r-- | mysql-test/t/status.test | 168 |
1 files changed, 157 insertions, 11 deletions
diff --git a/mysql-test/t/status.test b/mysql-test/t/status.test index 3e4d4eb7ffe..5da210f5a69 100644 --- a/mysql-test/t/status.test +++ b/mysql-test/t/status.test @@ -1,9 +1,23 @@ +# This test requires that --log-output includes 'table', and the general +# log is on + # embedded server causes different stat -- source include/not_embedded.inc # Save the initial number of concurrent sessions --source include/count_sessions.inc +# Disable concurrent inserts to avoid sporadic test failures as it might +# affect the the value of variables used throughout the test case. +set @old_concurrent_insert= @@global.concurrent_insert; +set @@global.concurrent_insert= 0; + +# Disable logging to table, since this will also cause table locking and unlocking, which will +# show up in SHOW STATUS and may cause sporadic failures + +SET @old_log_output = @@global.log_output; +SET GLOBAL LOG_OUTPUT = 'FILE'; + # PS causes different statistics --disable_ps_protocol @@ -11,33 +25,54 @@ connect (con1,localhost,root,,); connect (con2,localhost,root,,); flush status; + show status like 'Table_lock%'; +select * from information_schema.session_status where variable_name like 'Table_lock%'; + connection con1; -SET SQL_LOG_BIN=0; +--echo # Switched to connection: con1 +set sql_log_bin=0; +set @old_general_log = @@global.general_log; +set global general_log = 'OFF'; --disable_warnings drop table if exists t1; --enable_warnings create table t1(n int) engine=myisam; insert into t1 values(1); +# Execute dummy select in order to ensure that tables used in the +# previous statement are unlocked and closed. +select 1; + connection con2; +--echo # Switched to connection: con2 lock tables t1 read; unlock tables; lock tables t1 read; + connection con1; ---send -update t1 set n = 3; +--echo # Switched to connection: con1 +let $ID= `select connection_id()`; +--send update t1 set n = 3 + connection con2; -sleep 1; +--echo # Switched to connection: con2 +# wait for the other query to start executing +let $wait_condition= select 1 from INFORMATION_SCHEMA.PROCESSLIST where ID = $ID and STATE = "Locked"; +--source include/wait_condition.inc unlock tables; + connection con1; +--echo # Switched to connection: con1 reap; -show status like 'Table_lock%'; +show status like 'Table_locks_waited'; drop table t1; +set global general_log = @old_general_log; disconnect con2; disconnect con1; connection default; +--echo # Switched to connection: default # End of 4.1 tests @@ -47,9 +82,22 @@ connection default; select 1; show status like 'last_query_cost'; +create table t1 (a int); +insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); +insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); +insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); +insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); +insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); +select * from t1 where a=6; +show status like 'last_query_cost'; +# Ensure value dosn't change by second status call +show status like 'last_query_cost'; +select 1; +show status like 'last_query_cost'; +drop table t1; # -# Test for Bug #15933 max_used_connections is wrong after FLUSH STATUS +# Test for Bug#15933 max_used_connections is wrong after FLUSH STATUS # if connections are cached # # @@ -88,6 +136,7 @@ while ($wait_more) # Prerequisite. SHOW STATUS LIKE 'max_used_connections'; +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections'; # Save original setting. SET @save_thread_cache_size=@@thread_cache_size; @@ -101,6 +150,7 @@ disconnect con2; # Check that max_used_connections still reflects maximum value. SHOW STATUS LIKE 'max_used_connections'; +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections'; # Check that after flush max_used_connections equals to current number # of connections. First wait for previous disconnect to finish. @@ -124,15 +174,18 @@ while ($wait_more) --enable_result_log # Check that we don't count disconnected thread any longer. SHOW STATUS LIKE 'max_used_connections'; +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections'; # Check that max_used_connections is updated when cached thread is # reused... connect (con2,localhost,root,,); SHOW STATUS LIKE 'max_used_connections'; +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections'; # ...and when new thread is created. connect (con3,localhost,root,,); SHOW STATUS LIKE 'max_used_connections'; +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections'; # Restore original setting. connection default; @@ -144,7 +197,7 @@ disconnect con1; # -# Bug #30377: EXPLAIN loses last_query_cost when used with UNION +# Bug#30377 EXPLAIN loses last_query_cost when used with UNION # CREATE TABLE t1 ( a INT ); @@ -174,6 +227,94 @@ SHOW SESSION STATUS LIKE 'Last_query_cost'; DROP TABLE t1; +# End of 5.0 tests + +# +# Ensure that SHOW STATUS only changes global status variables +# + +connect (con1,localhost,root,,); +let $rnd_next = `show global status like 'handler_read_rnd_next'`; +let $tmp_table = `show global status like 'Created_tmp_tables'`; +show status like 'com_show_status'; +show status like 'hand%write%'; +show status like '%tmp%'; +show status like 'hand%write%'; +show status like '%tmp%'; +show status like 'com_show_status'; +let $rnd_next2 = `show global status like 'handler_read_rnd_next'`; +let $tmp_table2 = `show global status like 'Created_tmp_tables'`; +--disable_query_log +eval select substring_index('$rnd_next2',0x9,-1)-substring_index('$rnd_next',0x9,-1) as rnd_diff, substring_index('$tmp_table2',0x9,-1)-substring_index('$tmp_table',0x9,-1) as tmp_table_diff; +--enable_query_log +disconnect con1; +connection default; + +# +# Bug#30252 Com_create_function is not incremented. +# +flush status; +show status like 'Com%function'; + +DELIMITER //; +create function f1 (x INTEGER) returns integer + begin + declare ret integer; + set ret = x * 10; + return ret; + end // +DELIMITER ;// + +drop function f1; + +show status like 'Com%function'; + +# +# Bug#37908 Skipped access right check caused server crash. +# +connect (root, localhost, root,,test); +connection root; +let $root_connection_id= `select connection_id()`; +--disable_warnings +create database db37908; +--enable_warnings +create table db37908.t1(f1 int); +insert into db37908.t1 values(1); +grant usage,execute on test.* to mysqltest_1@localhost; +delimiter |; +create procedure proc37908() begin select 1; end | +create function func37908() returns int sql security invoker + return (select * from db37908.t1 limit 1)| +delimiter ;| + +connect (user1,localhost,mysqltest_1,,test); +connection user1; +let $user1_connection_id= `select connection_id()`; + +--error ER_TABLEACCESS_DENIED_ERROR +select * from db37908.t1; +--error ER_TABLEACCESS_DENIED_ERROR +show status where variable_name ='uptime' and 2 in (select * from db37908.t1); +--error ER_TABLEACCESS_DENIED_ERROR +show procedure status where name ='proc37908' and 1 in (select f1 from db37908.t1); +--error ER_TABLEACCESS_DENIED_ERROR +show function status where name ='func37908' and 1 in (select func37908()); + +connection default; +disconnect user1; +disconnect root; +drop database db37908; +drop procedure proc37908; +drop function func37908; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +DROP USER mysqltest_1@localhost; +# Wait till the sessions user1 and root are disconnected +let $wait_condition = + SELECT COUNT(*) = 0 + FROM information_schema.processlist + WHERE id in ('$root_connection_id','$user1_connection_id'); +--source include/wait_condition.inc + # # Bug#41131 "Questions" fails to increment - ignores statements instead stored procs # @@ -201,17 +342,22 @@ let $org_queries= `SHOW STATUS LIKE 'Queries'`; SELECT f1(); CALL p1(); let $new_queries= `SHOW STATUS LIKE 'Queries'`; ---disable_log +--disable_query_log let $diff= `SELECT SUBSTRING('$new_queries',9)-SUBSTRING('$org_queries',9)`; ---enable_log +--enable_query_log eval SELECT $diff; disconnect con1; connection default; DROP PROCEDURE p1; DROP FUNCTION f1; -# End of 5.0 tests +# End of 5.1 tests + +# Restore global concurrent_insert value. Keep in the end of the test file. +--connection default +set @@global.concurrent_insert= @old_concurrent_insert; +SET GLOBAL log_output = @old_log_output; -# Wait till all disconnects are completed +# Wait till we reached the initial number of concurrent sessions --source include/wait_until_count_sessions.inc |