diff options
| -rw-r--r-- | mysql-test/r/information_schema.result | 16 | ||||
| -rw-r--r-- | mysql-test/r/information_schema_db.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/status.result | 23 | ||||
| -rw-r--r-- | mysql-test/r/variables.result | 151 | ||||
| -rw-r--r-- | mysql-test/t/status.test | 7 | ||||
| -rw-r--r-- | mysql-test/t/variables.test | 50 | ||||
| -rw-r--r-- | sql/sql_show.cc | 233 | ||||
| -rw-r--r-- | sql/table.h | 4 |
8 files changed, 479 insertions, 9 deletions
diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 077fa0f2376..9ba55e42326 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -46,6 +46,8 @@ COLUMN_PRIVILEGES ENGINES EVENTS FILES +GLOBAL_STATUS +GLOBAL_VARIABLES KEY_COLUMN_USAGE PARTITIONS PLUGINS @@ -54,6 +56,8 @@ REFERENTIAL_CONSTRAINTS ROUTINES SCHEMATA SCHEMA_PRIVILEGES +SESSION_STATUS +SESSION_VARIABLES STATISTICS TABLES TABLE_CONSTRAINTS @@ -758,6 +762,7 @@ table_schema table_name column_name information_schema COLUMNS COLUMN_TYPE information_schema EVENTS EVENT_DEFINITION information_schema EVENTS SQL_MODE +information_schema GLOBAL_VARIABLES VARIABLE_VALUE information_schema PARTITIONS PARTITION_EXPRESSION information_schema PARTITIONS SUBPARTITION_EXPRESSION information_schema PARTITIONS PARTITION_DESCRIPTION @@ -765,6 +770,7 @@ information_schema PLUGINS PLUGIN_DESCRIPTION information_schema PROCESSLIST INFO information_schema ROUTINES ROUTINE_DEFINITION information_schema ROUTINES SQL_MODE +information_schema SESSION_VARIABLES VARIABLE_VALUE information_schema TRIGGERS ACTION_CONDITION information_schema TRIGGERS ACTION_STATEMENT information_schema TRIGGERS SQL_MODE @@ -847,7 +853,7 @@ delete from mysql.db where user='mysqltest_4'; flush privileges; SELECT table_schema, count(*) FROM information_schema.TABLES where TABLE_SCHEMA!='cluster' GROUP BY TABLE_SCHEMA; table_schema count(*) -information_schema 23 +information_schema 27 mysql 21 create table t1 (i int, j int); create trigger trg1 before insert on t1 for each row @@ -1240,6 +1246,8 @@ COLUMN_PRIVILEGES TABLE_SCHEMA ENGINES ENGINE EVENTS EVENT_SCHEMA FILES TABLE_SCHEMA +GLOBAL_STATUS VARIABLE_NAME +GLOBAL_VARIABLES VARIABLE_NAME KEY_COLUMN_USAGE CONSTRAINT_SCHEMA PARTITIONS TABLE_SCHEMA PLUGINS PLUGIN_NAME @@ -1248,6 +1256,8 @@ REFERENTIAL_CONSTRAINTS CONSTRAINT_SCHEMA ROUTINES ROUTINE_SCHEMA SCHEMATA SCHEMA_NAME SCHEMA_PRIVILEGES TABLE_SCHEMA +SESSION_STATUS VARIABLE_NAME +SESSION_VARIABLES VARIABLE_NAME STATISTICS TABLE_SCHEMA TABLES TABLE_SCHEMA TABLE_CONSTRAINTS CONSTRAINT_SCHEMA @@ -1278,6 +1288,8 @@ COLUMN_PRIVILEGES TABLE_SCHEMA ENGINES ENGINE EVENTS EVENT_SCHEMA FILES TABLE_SCHEMA +GLOBAL_STATUS VARIABLE_NAME +GLOBAL_VARIABLES VARIABLE_NAME KEY_COLUMN_USAGE CONSTRAINT_SCHEMA PARTITIONS TABLE_SCHEMA PLUGINS PLUGIN_NAME @@ -1286,6 +1298,8 @@ REFERENTIAL_CONSTRAINTS CONSTRAINT_SCHEMA ROUTINES ROUTINE_SCHEMA SCHEMATA SCHEMA_NAME SCHEMA_PRIVILEGES TABLE_SCHEMA +SESSION_STATUS VARIABLE_NAME +SESSION_VARIABLES VARIABLE_NAME STATISTICS TABLE_SCHEMA TABLES TABLE_SCHEMA TABLE_CONSTRAINTS CONSTRAINT_SCHEMA diff --git a/mysql-test/r/information_schema_db.result b/mysql-test/r/information_schema_db.result index faba5e8d83c..db14b7b6600 100644 --- a/mysql-test/r/information_schema_db.result +++ b/mysql-test/r/information_schema_db.result @@ -13,6 +13,8 @@ COLUMN_PRIVILEGES ENGINES EVENTS FILES +GLOBAL_STATUS +GLOBAL_VARIABLES KEY_COLUMN_USAGE PARTITIONS PLUGINS @@ -21,6 +23,8 @@ REFERENTIAL_CONSTRAINTS ROUTINES SCHEMATA SCHEMA_PRIVILEGES +SESSION_STATUS +SESSION_VARIABLES STATISTICS TABLES TABLE_CONSTRAINTS diff --git a/mysql-test/r/status.result b/mysql-test/r/status.result index 83c6a6f5288..48a1d80dc7e 100644 --- a/mysql-test/r/status.result +++ b/mysql-test/r/status.result @@ -3,6 +3,10 @@ show status like 'Table_lock%'; Variable_name Value Table_locks_immediate 0 Table_locks_waited 0 +select * from information_schema.session_status where variable_name like 'Table_lock%'; +VARIABLE_NAME VARIABLE_VALUE +TABLE_LOCKS_IMMEDIATE 0.0000000 +TABLE_LOCKS_WAITED 0.0000000 SET SQL_LOG_BIN=0; drop table if exists t1; create table t1(n int) engine=myisam; @@ -16,6 +20,10 @@ show status like 'Table_lock%'; Variable_name Value Table_locks_immediate 3 Table_locks_waited 1 +select * from information_schema.session_status where variable_name like 'Table_lock%'; +VARIABLE_NAME VARIABLE_VALUE +TABLE_LOCKS_IMMEDIATE 3.0000000 +TABLE_LOCKS_WAITED 1.0000000 drop table t1; select 1; 1 @@ -53,21 +61,36 @@ FLUSH STATUS; SHOW STATUS LIKE 'max_used_connections'; Variable_name Value Max_used_connections 2 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections'; +VARIABLE_NAME VARIABLE_VALUE +MAX_USED_CONNECTIONS 2.0000000 SET @save_thread_cache_size=@@thread_cache_size; SET GLOBAL thread_cache_size=3; SHOW STATUS LIKE 'max_used_connections'; Variable_name Value Max_used_connections 4 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections'; +VARIABLE_NAME VARIABLE_VALUE +MAX_USED_CONNECTIONS 4.0000000 FLUSH STATUS; SHOW STATUS LIKE 'max_used_connections'; Variable_name Value Max_used_connections 3 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections'; +VARIABLE_NAME VARIABLE_VALUE +MAX_USED_CONNECTIONS 3.0000000 SHOW STATUS LIKE 'max_used_connections'; Variable_name Value Max_used_connections 4 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections'; +VARIABLE_NAME VARIABLE_VALUE +MAX_USED_CONNECTIONS 4.0000000 SHOW STATUS LIKE 'max_used_connections'; Variable_name Value Max_used_connections 5 +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections'; +VARIABLE_NAME VARIABLE_VALUE +MAX_USED_CONNECTIONS 5.0000000 SET GLOBAL thread_cache_size=@save_thread_cache_size; show status like 'com_show_status'; Variable_name Value diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index 3b6bfc60a80..5526ca8634f 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -103,21 +103,36 @@ set max_join_size=100; show variables like 'max_join_size'; Variable_name Value max_join_size 100 +select * from information_schema.session_variables where variable_name like 'max_join_size'; +VARIABLE_NAME VARIABLE_VALUE +MAX_JOIN_SIZE 100 show global variables like 'max_join_size'; Variable_name Value max_join_size 10 +select * from information_schema.global_variables where variable_name like 'max_join_size'; +VARIABLE_NAME VARIABLE_VALUE +MAX_JOIN_SIZE 10 set GLOBAL max_join_size=2000; show global variables like 'max_join_size'; Variable_name Value max_join_size 2000 +select * from information_schema.global_variables where variable_name like 'max_join_size'; +VARIABLE_NAME VARIABLE_VALUE +MAX_JOIN_SIZE 2000 set max_join_size=DEFAULT; show variables like 'max_join_size'; Variable_name Value max_join_size 2000 +select * from information_schema.session_variables where variable_name like 'max_join_size'; +VARIABLE_NAME VARIABLE_VALUE +MAX_JOIN_SIZE 2000 set GLOBAL max_join_size=DEFAULT; show global variables like 'max_join_size'; Variable_name Value max_join_size HA_POS_ERROR +select * from information_schema.global_variables where variable_name like 'max_join_size'; +VARIABLE_NAME VARIABLE_VALUE +MAX_JOIN_SIZE HA_POS_ERROR set @@max_join_size=1000, @@global.max_join_size=2000; select @@local.max_join_size, @@global.max_join_size; @@local.max_join_size @@global.max_join_size @@ -149,14 +164,23 @@ set global concurrent_insert=2; show variables like 'concurrent_insert'; Variable_name Value concurrent_insert 2 +select * from information_schema.session_variables where variable_name like 'concurrent_insert'; +VARIABLE_NAME VARIABLE_VALUE +CONCURRENT_INSERT 2 set global concurrent_insert=1; show variables like 'concurrent_insert'; Variable_name Value concurrent_insert 1 +select * from information_schema.session_variables where variable_name like 'concurrent_insert'; +VARIABLE_NAME VARIABLE_VALUE +CONCURRENT_INSERT 1 set global concurrent_insert=0; show variables like 'concurrent_insert'; Variable_name Value concurrent_insert 0 +select * from information_schema.session_variables where variable_name like 'concurrent_insert'; +VARIABLE_NAME VARIABLE_VALUE +CONCURRENT_INSERT 0 set global concurrent_insert=DEFAULT; select @@concurrent_insert; @@concurrent_insert @@ -165,26 +189,44 @@ set global timed_mutexes=ON; show variables like 'timed_mutexes'; Variable_name Value timed_mutexes ON +select * from information_schema.session_variables where variable_name like 'timed_mutexes'; +VARIABLE_NAME VARIABLE_VALUE +TIMED_MUTEXES ON set global timed_mutexes=0; show variables like 'timed_mutexes'; Variable_name Value timed_mutexes OFF +select * from information_schema.session_variables where variable_name like 'timed_mutexes'; +VARIABLE_NAME VARIABLE_VALUE +TIMED_MUTEXES OFF set storage_engine=MYISAM, storage_engine="HEAP", global storage_engine="MERGE"; show local variables like 'storage_engine'; Variable_name Value storage_engine MEMORY +select * from information_schema.session_variables where variable_name like 'storage_engine'; +VARIABLE_NAME VARIABLE_VALUE +STORAGE_ENGINE MEMORY show global variables like 'storage_engine'; Variable_name Value storage_engine MRG_MYISAM +select * from information_schema.global_variables where variable_name like 'storage_engine'; +VARIABLE_NAME VARIABLE_VALUE +STORAGE_ENGINE MRG_MYISAM set GLOBAL query_cache_size=100000; set GLOBAL myisam_max_sort_file_size=2000000; show global variables like 'myisam_max_sort_file_size'; Variable_name Value myisam_max_sort_file_size 1048576 +select * from information_schema.global_variables where variable_name like 'myisam_max_sort_file_size'; +VARIABLE_NAME VARIABLE_VALUE +MYISAM_MAX_SORT_FILE_SIZE 1048576 set GLOBAL myisam_max_sort_file_size=default; show variables like 'myisam_max_sort_file_size'; Variable_name Value myisam_max_sort_file_size FILE_SIZE +select * from information_schema.session_variables where variable_name like 'myisam_max_sort_file_size'; +VARIABLE_NAME VARIABLE_VALUE +MYISAM_MAX_SORT_FILE_SIZE FILE_SIZE set global net_retry_count=10, session net_retry_count=10; set global net_buffer_length=1024, net_write_timeout=200, net_read_timeout=300; set session net_buffer_length=2048, net_write_timeout=500, net_read_timeout=600; @@ -194,12 +236,24 @@ net_buffer_length 1024 net_read_timeout 300 net_retry_count 10 net_write_timeout 200 +select * from information_schema.global_variables where variable_name like 'net_%'; +VARIABLE_NAME VARIABLE_VALUE +NET_BUFFER_LENGTH 1024 +NET_READ_TIMEOUT 300 +NET_RETRY_COUNT 10 +NET_WRITE_TIMEOUT 200 show session variables like 'net_%'; Variable_name Value net_buffer_length 2048 net_read_timeout 600 net_retry_count 10 net_write_timeout 500 +select * from information_schema.session_variables where variable_name like 'net_%'; +VARIABLE_NAME VARIABLE_VALUE +NET_BUFFER_LENGTH 2048 +NET_READ_TIMEOUT 600 +NET_RETRY_COUNT 10 +NET_WRITE_TIMEOUT 500 set session net_buffer_length=8000, global net_read_timeout=900, net_write_timeout=1000; show global variables like 'net_%'; Variable_name Value @@ -207,24 +261,45 @@ net_buffer_length 1024 net_read_timeout 900 net_retry_count 10 net_write_timeout 1000 +select * from information_schema.global_variables where variable_name like 'net_%'; +VARIABLE_NAME VARIABLE_VALUE +NET_BUFFER_LENGTH 1024 +NET_READ_TIMEOUT 900 +NET_RETRY_COUNT 10 +NET_WRITE_TIMEOUT 1000 show session variables like 'net_%'; Variable_name Value net_buffer_length 7168 net_read_timeout 600 net_retry_count 10 net_write_timeout 500 +select * from information_schema.session_variables where variable_name like 'net_%'; +VARIABLE_NAME VARIABLE_VALUE +NET_BUFFER_LENGTH 7168 +NET_READ_TIMEOUT 600 +NET_RETRY_COUNT 10 +NET_WRITE_TIMEOUT 500 set net_buffer_length=1; show variables like 'net_buffer_length'; Variable_name Value net_buffer_length 1024 +select * from information_schema.session_variables where variable_name like 'net_buffer_length'; +VARIABLE_NAME VARIABLE_VALUE +NET_BUFFER_LENGTH 1024 set net_buffer_length=2000000000; show variables like 'net_buffer_length'; Variable_name Value net_buffer_length 1048576 +select * from information_schema.session_variables where variable_name like 'net_buffer_length'; +VARIABLE_NAME VARIABLE_VALUE +NET_BUFFER_LENGTH 1048576 set character set cp1251_koi8; show variables like "character_set_client"; Variable_name Value character_set_client cp1251 +select * from information_schema.session_variables where variable_name like 'character_set_client'; +VARIABLE_NAME VARIABLE_VALUE +CHARACTER_SET_CLIENT cp1251 select @@timestamp>0; @@timestamp>0 1 @@ -239,6 +314,13 @@ query_prealloc_size 8192 range_alloc_block_size 2048 transaction_alloc_block_size 8192 transaction_prealloc_size 4096 +select * from information_schema.session_variables where variable_name like '%alloc%'; +VARIABLE_NAME VARIABLE_VALUE +QUERY_ALLOC_BLOCK_SIZE 8192 +QUERY_PREALLOC_SIZE 8192 +RANGE_ALLOC_BLOCK_SIZE 2048 +TRANSACTION_ALLOC_BLOCK_SIZE 8192 +TRANSACTION_PREALLOC_SIZE 4096 set @@range_alloc_block_size=1024*16; set @@query_alloc_block_size=1024*17+2; set @@query_prealloc_size=1024*18; @@ -254,6 +336,13 @@ query_prealloc_size 18432 range_alloc_block_size 16384 transaction_alloc_block_size 19456 transaction_prealloc_size 20480 +select * from information_schema.session_variables where variable_name like '%alloc%'; +VARIABLE_NAME VARIABLE_VALUE +QUERY_ALLOC_BLOCK_SIZE 17408 +QUERY_PREALLOC_SIZE 18432 +RANGE_ALLOC_BLOCK_SIZE 16384 +TRANSACTION_ALLOC_BLOCK_SIZE 19456 +TRANSACTION_PREALLOC_SIZE 20480 set @@range_alloc_block_size=default; set @@query_alloc_block_size=default, @@query_prealloc_size=default; set transaction_alloc_block_size=default, @@transaction_prealloc_size=default; @@ -264,6 +353,13 @@ query_prealloc_size 8192 range_alloc_block_size 2048 transaction_alloc_block_size 8192 transaction_prealloc_size 4096 +select * from information_schema.session_variables where variable_name like '%alloc%'; +VARIABLE_NAME VARIABLE_VALUE +QUERY_ALLOC_BLOCK_SIZE 8192 +QUERY_PREALLOC_SIZE 8192 +RANGE_ALLOC_BLOCK_SIZE 2048 +TRANSACTION_ALLOC_BLOCK_SIZE 8192 +TRANSACTION_PREALLOC_SIZE 4096 SELECT @@version LIKE 'non-existent'; @@version LIKE 'non-existent' 0 @@ -485,6 +581,9 @@ set global myisam_max_sort_file_size=4294967296; show global variables like 'myisam_max_sort_file_size'; Variable_name Value myisam_max_sort_file_size MAX_FILE_SIZE +select * from information_schema.global_variables where variable_name like 'myisam_max_sort_file_size'; +VARIABLE_NAME VARIABLE_VALUE +MYISAM_MAX_SORT_FILE_SIZE MAX_FILE_SIZE set global myisam_max_sort_file_size=default; select @@global.max_user_connections,@@local.max_join_size; @@global.max_user_connections @@local.max_join_size @@ -524,18 +623,30 @@ set @tstlw = @@log_warnings; show global variables like 'log_warnings'; Variable_name Value log_warnings 1 +select * from information_schema.global_variables where variable_name like 'log_warnings'; +VARIABLE_NAME VARIABLE_VALUE +LOG_WARNINGS 1 set global log_warnings = 0; show global variables like 'log_warnings'; Variable_name Value log_warnings 0 +select * from information_schema.global_variables where variable_name like 'log_warnings'; +VARIABLE_NAME VARIABLE_VALUE +LOG_WARNINGS 0 set global log_warnings = 42; show global variables like 'log_warnings'; Variable_name Value log_warnings 42 +select * from information_schema.global_variables where variable_name like 'log_warnings'; +VARIABLE_NAME VARIABLE_VALUE +LOG_WARNINGS 42 set global log_warnings = @tstlw; show global variables like 'log_warnings'; Variable_name Value log_warnings 1 +select * from information_schema.global_variables where variable_name like 'log_warnings'; +VARIABLE_NAME VARIABLE_VALUE +LOG_WARNINGS 1 create table t1 ( c1 tinyint, c2 smallint, @@ -567,10 +678,16 @@ SET GLOBAL MYISAM_DATA_POINTER_SIZE= 7; SHOW VARIABLES LIKE 'MYISAM_DATA_POINTER_SIZE'; Variable_name Value myisam_data_pointer_size 7 +SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'MYISAM_DATA_POINTER_SIZE'; +VARIABLE_NAME VARIABLE_VALUE +MYISAM_DATA_POINTER_SIZE 7 SET GLOBAL table_open_cache=-1; SHOW VARIABLES LIKE 'table_open_cache'; Variable_name Value table_open_cache 1 +SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'table_open_cache'; +VARIABLE_NAME VARIABLE_VALUE +TABLE_OPEN_CACHE 1 SET GLOBAL table_open_cache=DEFAULT; set character_set_results=NULL; select ifnull(@@character_set_results,"really null"); @@ -639,21 +756,36 @@ set @@sql_big_selects = 1; show variables like 'sql_big_selects'; Variable_name Value sql_big_selects ON +select * from information_schema.session_variables where variable_name like 'sql_big_selects'; +VARIABLE_NAME VARIABLE_VALUE +SQL_BIG_SELECTS ON set @@sql_big_selects = @old_sql_big_selects; set @@sql_notes = 0, @@sql_warnings = 0; show variables like 'sql_notes'; Variable_name Value sql_notes OFF +select * from information_schema.session_variables where variable_name like 'sql_notes'; +VARIABLE_NAME VARIABLE_VALUE +SQL_NOTES OFF show variables like 'sql_warnings'; Variable_name Value sql_warnings OFF +select * from information_schema.session_variables where variable_name like 'sql_warnings'; +VARIABLE_NAME VARIABLE_VALUE +SQL_WARNINGS OFF set @@sql_notes = 1, @@sql_warnings = 1; show variables like 'sql_notes'; Variable_name Value sql_notes ON +select * from information_schema.session_variables where variable_name like 'sql_notes'; +VARIABLE_NAME VARIABLE_VALUE +SQL_NOTES ON show variables like 'sql_warnings'; Variable_name Value sql_warnings ON +select * from information_schema.session_variables where variable_name like 'sql_warnings'; +VARIABLE_NAME VARIABLE_VALUE +SQL_WARNINGS ON select @@system_time_zone; @@system_time_zone # @@ -667,12 +799,21 @@ select @@basedir, @@datadir, @@tmpdir; show variables like 'basedir'; Variable_name Value basedir # +select * from information_schema.session_variables where variable_name like 'basedir'; +VARIABLE_NAME VARIABLE_VALUE +BASEDIR # show variables like 'datadir'; Variable_name Value datadir # +select * from information_schema.session_variables where variable_name like 'datadir'; +VARIABLE_NAME VARIABLE_VALUE +DATADIR # show variables like 'tmpdir'; Variable_name Value tmpdir # +select * from information_schema.session_variables where variable_name like 'tmpdir'; +VARIABLE_NAME VARIABLE_VALUE +TMPDIR # select @@ssl_ca, @@ssl_capath, @@ssl_cert, @@ssl_cipher, @@ssl_key; @@ssl_ca @@ssl_capath @@ssl_cert @@ssl_cipher @@ssl_key # # # # # @@ -683,12 +824,22 @@ ssl_capath # ssl_cert # ssl_cipher # ssl_key # +select * from information_schema.session_variables where variable_name like 'ssl%'; +VARIABLE_NAME VARIABLE_VALUE +SSL_CA # +SSL_CAPATH # +SSL_CERT # +SSL_CIPHER # +SSL_KEY # select @@log_queries_not_using_indexes; @@log_queries_not_using_indexes 0 show variables like 'log_queries_not_using_indexes'; Variable_name Value log_queries_not_using_indexes OFF +select * from information_schema.session_variables where variable_name like 'log_queries_not_using_indexes'; +VARIABLE_NAME VARIABLE_VALUE +LOG_QUERIES_NOT_USING_INDEXES OFF End of 5.0 tests set global binlog_cache_size =@my_binlog_cache_size; set global connect_timeout =@my_connect_timeout; diff --git a/mysql-test/t/status.test b/mysql-test/t/status.test index 55f9d95adc5..2afcd49962c 100644 --- a/mysql-test/t/status.test +++ b/mysql-test/t/status.test @@ -13,6 +13,7 @@ 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; --disable_warnings @@ -34,6 +35,7 @@ unlock tables; connection con1; reap; show status like 'Table_lock%'; +select * from information_schema.session_status where variable_name like 'Table_lock%'; drop table t1; disconnect con2; @@ -102,6 +104,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; @@ -115,6 +118,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. @@ -138,15 +142,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; diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index 771a4ad3ed3..a33f1f72b8a 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -83,16 +83,24 @@ drop table t1; set GLOBAL max_join_size=10; set max_join_size=100; show variables like 'max_join_size'; +select * from information_schema.session_variables where variable_name like 'max_join_size'; --replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR show global variables like 'max_join_size'; +--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR +select * from information_schema.global_variables where variable_name like 'max_join_size'; set GLOBAL max_join_size=2000; show global variables like 'max_join_size'; +select * from information_schema.global_variables where variable_name like 'max_join_size'; set max_join_size=DEFAULT; --replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR show variables like 'max_join_size'; +--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR +select * from information_schema.session_variables where variable_name like 'max_join_size'; set GLOBAL max_join_size=DEFAULT; --replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR show global variables like 'max_join_size'; +--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR +select * from information_schema.global_variables where variable_name like 'max_join_size'; set @@max_join_size=1000, @@global.max_join_size=2000; select @@local.max_join_size, @@global.max_join_size; select @@identity, length(@@version)>0; @@ -106,50 +114,68 @@ set big_tables=OFF, big_tables=ON, big_tables=0, big_tables=1, big_tables="OFF", set global concurrent_insert=2; show variables like 'concurrent_insert'; +select * from information_schema.session_variables where variable_name like 'concurrent_insert'; set global concurrent_insert=1; show variables like 'concurrent_insert'; +select * from information_schema.session_variables where variable_name like 'concurrent_insert'; set global concurrent_insert=0; show variables like 'concurrent_insert'; +select * from information_schema.session_variables where variable_name like 'concurrent_insert'; set global concurrent_insert=DEFAULT; select @@concurrent_insert; set global timed_mutexes=ON; show variables like 'timed_mutexes'; +select * from information_schema.session_variables where variable_name like 'timed_mutexes'; set global timed_mutexes=0; show variables like 'timed_mutexes'; +select * from information_schema.session_variables where variable_name like 'timed_mutexes'; set storage_engine=MYISAM, storage_engine="HEAP", global storage_engine="MERGE"; show local variables like 'storage_engine'; +select * from information_schema.session_variables where variable_name like 'storage_engine'; show global variables like 'storage_engine'; +select * from information_schema.global_variables where variable_name like 'storage_engine'; set GLOBAL query_cache_size=100000; set GLOBAL myisam_max_sort_file_size=2000000; show global variables like 'myisam_max_sort_file_size'; +select * from information_schema.global_variables where variable_name like 'myisam_max_sort_file_size'; set GLOBAL myisam_max_sort_file_size=default; --replace_result 2147483647 FILE_SIZE 9223372036854775807 FILE_SIZE show variables like 'myisam_max_sort_file_size'; +--replace_result 2147483647 FILE_SIZE 9223372036854775807 FILE_SIZE +select * from information_schema.session_variables where variable_name like 'myisam_max_sort_file_size'; set global net_retry_count=10, session net_retry_count=10; set global net_buffer_length=1024, net_write_timeout=200, net_read_timeout=300; set session net_buffer_length=2048, net_write_timeout=500, net_read_timeout=600; show global variables like 'net_%'; +select * from information_schema.global_variables where variable_name like 'net_%'; show session variables like 'net_%'; +select * from information_schema.session_variables where variable_name like 'net_%'; set session net_buffer_length=8000, global net_read_timeout=900, net_write_timeout=1000; show global variables like 'net_%'; +select * from information_schema.global_variables where variable_name like 'net_%'; show session variables like 'net_%'; +select * from information_schema.session_variables where variable_name like 'net_%'; set net_buffer_length=1; show variables like 'net_buffer_length'; +select * from information_schema.session_variables where variable_name like 'net_buffer_length'; set net_buffer_length=2000000000; show variables like 'net_buffer_length'; +select * from information_schema.session_variables where variable_name like 'net_buffer_length'; set character set cp1251_koi8; show variables like "character_set_client"; +select * from information_schema.session_variables where variable_name like 'character_set_client'; select @@timestamp>0; set @@rand_seed1=10000000,@@rand_seed2=1000000; select ROUND(RAND(),5); show variables like '%alloc%'; +select * from information_schema.session_variables where variable_name like '%alloc%'; set @@range_alloc_block_size=1024*16; set @@query_alloc_block_size=1024*17+2; set @@query_prealloc_size=1024*18; @@ -157,10 +183,12 @@ set @@transaction_alloc_block_size=1024*20-1; set @@transaction_prealloc_size=1024*21-1; select @@query_alloc_block_size; show variables like '%alloc%'; +select * from information_schema.session_variables where variable_name like '%alloc%'; set @@range_alloc_block_size=default; set @@query_alloc_block_size=default, @@query_prealloc_size=default; set transaction_alloc_block_size=default, @@transaction_prealloc_size=default; show variables like '%alloc%'; +select * from information_schema.session_variables where variable_name like '%alloc%'; # # Bug #10904 Illegal mix of collations between @@ -363,6 +391,8 @@ set global ft_boolean_syntax = @@init_connect; set global myisam_max_sort_file_size=4294967296; --replace_result 4294967296 MAX_FILE_SIZE 2146435072 MAX_FILE_SIZE show global variables like 'myisam_max_sort_file_size'; +--replace_result 4294967296 MAX_FILE_SIZE 2146435072 MAX_FILE_SIZE +select * from information_schema.global_variables where variable_name like 'myisam_max_sort_file_size'; set global myisam_max_sort_file_size=default; # @@ -398,12 +428,16 @@ SELECT @@global.local.key_buffer_size; # BUG#5135: cannot turn on log_warnings with SET in 4.1 (and 4.0) set @tstlw = @@log_warnings; show global variables like 'log_warnings'; +select * from information_schema.global_variables where variable_name like 'log_warnings'; set global log_warnings = 0; show global variables like 'log_warnings'; +select * from information_schema.global_variables where variable_name like 'log_warnings'; set global log_warnings = 42; show global variables like 'log_warnings'; +select * from information_schema.global_variables where variable_name like 'log_warnings'; set global log_warnings = @tstlw; show global variables like 'log_warnings'; +select * from information_schema.global_variables where variable_name like 'log_warnings'; # # BUG#4788 show create table provides incorrect statement @@ -435,6 +469,7 @@ drop table t1; SET GLOBAL MYISAM_DATA_POINTER_SIZE= 7; SHOW VARIABLES LIKE 'MYISAM_DATA_POINTER_SIZE'; +SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'MYISAM_DATA_POINTER_SIZE'; # # Bug #6958: negative arguments to integer options wrap around @@ -442,6 +477,7 @@ SHOW VARIABLES LIKE 'MYISAM_DATA_POINTER_SIZE'; SET GLOBAL table_open_cache=-1; SHOW VARIABLES LIKE 'table_open_cache'; +SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'table_open_cache'; SET GLOBAL table_open_cache=DEFAULT; # @@ -527,6 +563,7 @@ select @@global.character_set_filesystem; set @old_sql_big_selects = @@sql_big_selects; set @@sql_big_selects = 1; show variables like 'sql_big_selects'; +select * from information_schema.session_variables where variable_name like 'sql_big_selects'; set @@sql_big_selects = @old_sql_big_selects; # @@ -535,10 +572,14 @@ set @@sql_big_selects = @old_sql_big_selects; # set @@sql_notes = 0, @@sql_warnings = 0; show variables like 'sql_notes'; +select * from information_schema.session_variables where variable_name like 'sql_notes'; show variables like 'sql_warnings'; +select * from information_schema.session_variables where variable_name like 'sql_warnings'; set @@sql_notes = 1, @@sql_warnings = 1; show variables like 'sql_notes'; +select * from information_schema.session_variables where variable_name like 'sql_notes'; show variables like 'sql_warnings'; +select * from information_schema.session_variables where variable_name like 'sql_warnings'; # # Bug #12792: @@system_time_zone is not SELECTable. @@ -565,9 +606,15 @@ select @@basedir, @@datadir, @@tmpdir; --replace_column 2 # show variables like 'basedir'; --replace_column 2 # +select * from information_schema.session_variables where variable_name like 'basedir'; +--replace_column 2 # show variables like 'datadir'; --replace_column 2 # +select * from information_schema.session_variables where variable_name like 'datadir'; +--replace_column 2 # show variables like 'tmpdir'; +--replace_column 2 # +select * from information_schema.session_variables where variable_name like 'tmpdir'; # # Bug #19606: make ssl settings available via SHOW VARIABLES and @@variables @@ -577,6 +624,8 @@ show variables like 'tmpdir'; select @@ssl_ca, @@ssl_capath, @@ssl_cert, @@ssl_cipher, @@ssl_key; --replace_column 2 # show variables like 'ssl%'; +--replace_column 2 # +select * from information_schema.session_variables where variable_name like 'ssl%'; # # Bug #19616: make log_queries_not_using_indexes available in SHOW VARIABLES @@ -584,6 +633,7 @@ show variables like 'ssl%'; # select @@log_queries_not_using_indexes; show variables like 'log_queries_not_using_indexes'; +select * from information_schema.session_variables where variable_name like 'log_queries_not_using_indexes'; --echo End of 5.0 tests diff --git a/sql/sql_show.cc b/sql/sql_show.cc index 3acc025b84f..14ca547a527 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -1994,15 +1994,22 @@ void remove_status_vars(SHOW_VAR *list) } } +inline void make_upper(char *buf) +{ + for (; *buf; buf++) + *buf= my_toupper(system_charset_info, *buf); +} + static bool show_status_array(THD *thd, const char *wild, SHOW_VAR *variables, enum enum_var_type value_type, struct system_status_var *status_var, - const char *prefix, TABLE *table) + const char *prefix, TABLE *table, + bool ucase_names) { char buff[SHOW_VAR_FUNC_BUFF_SIZE], *prefix_end; - /* the variable name should not be longer then 80 characters */ - char name_buffer[80]; + /* the variable name should not be longer than 64 characters */ + char name_buffer[64]; int len; LEX_STRING null_lex_str; SHOW_VAR tmp, *var; @@ -2020,6 +2027,8 @@ static bool show_status_array(THD *thd, const char *wild, { strnmov(prefix_end, variables->name, len); name_buffer[sizeof(name_buffer)-1]=0; /* Safety */ + if (ucase_names) + make_upper(name_buffer); /* if var->type is SHOW_FUNC, call the function. @@ -2031,8 +2040,8 @@ static bool show_status_array(THD *thd, const char *wild, SHOW_TYPE show_type=var->type; if (show_type == SHOW_ARRAY) { - show_status_array(thd, wild, (SHOW_VAR *) var->value, - value_type, status_var, name_buffer, table); + show_status_array(thd, wild, (SHOW_VAR *) var->value, value_type, + status_var, name_buffer, table, ucase_names); } else { @@ -2041,7 +2050,7 @@ static bool show_status_array(THD *thd, const char *wild, { char *value=var->value; const char *pos, *end; // We assign a lot of const's - long nr; + if (show_type == SHOW_SYS) { show_type= ((sys_var*) value)->type(); @@ -2123,6 +2132,7 @@ static bool show_status_array(THD *thd, const char *wild, table->field[0]->store(name_buffer, strlen(name_buffer), system_charset_info); table->field[1]->store(pos, (uint32) (end - pos), system_charset_info); + table->field[1]->set_notnull(); if (schema_table_store_record(thd, table)) DBUG_RETURN(TRUE); } @@ -4563,7 +4573,7 @@ int fill_variables(THD *thd, TABLE_LIST *tables, COND *cond) const char *wild= lex->wild ? lex->wild->ptr() : NullS; pthread_mutex_lock(&LOCK_global_system_variables); res= show_status_array(thd, wild, init_vars, - lex->option_type, 0, "", tables->table); + lex->option_type, 0, "", tables->table, 0); pthread_mutex_unlock(&LOCK_global_system_variables); DBUG_RETURN(res); } @@ -4583,7 +4593,8 @@ int fill_status(THD *thd, TABLE_LIST *tables, COND *cond) (SHOW_VAR *)all_status_vars.buffer, OPT_GLOBAL, (lex->option_type == OPT_GLOBAL ? - &tmp: thd->initial_status_var), "",tables->table); + &tmp: thd->initial_status_var), + "", tables->table, 0); pthread_mutex_unlock(&LOCK_status); DBUG_RETURN(res); } @@ -4732,6 +4743,21 @@ TABLE *create_schema_table(THD *thd, TABLE_LIST *table_list) DBUG_RETURN(0); } break; + case MYSQL_TYPE_DECIMAL: + if (!(item= new Item_decimal((longlong) fields_info->value, false))) + { + DBUG_RETURN(0); + } + item->unsigned_flag= (fields_info->field_length/10000)%10; + item->decimals= fields_info->field_length%10; + item->max_length= (fields_info->field_length/100)%100; + if (item->unsigned_flag == 0) + item->max_length+= 1; + if (item->decimals > 0) + item->max_length+= 1; + item->set_name(fields_info->field_name, + strlen(fields_info->field_name), cs); + break; default: /* this should be changed when Item_empty_string is fixed(in 4.1) */ if (!(item= new Item_empty_string("", 0, cs))) @@ -5171,6 +5197,173 @@ int fill_schema_files(THD *thd, TABLE_LIST *tables, COND *cond) DBUG_RETURN(0); } +int fill_schema_status(THD *thd, SHOW_VAR *variables, + struct system_status_var *status_var, + const char *prefix, TABLE *table) +{ + SHOW_VAR tmp, *var; + SHOW_TYPE show_type; + LEX_STRING null_lex_str; + char buff[SHOW_VAR_FUNC_BUFF_SIZE]; + char name_buf[64], *name_pos; + int name_len; + DBUG_ENTER("fill_schema_status"); + + null_lex_str.str= 0; + null_lex_str.length= 0; + + name_pos= strnmov(name_buf, prefix, sizeof(name_buf) - 1); + if (*prefix) + *name_pos++= '_'; + name_len= name_buf + sizeof(name_buf) - name_pos; + + for (; variables->name; variables++) + { + strnmov(name_pos, variables->name, name_len); + name_buf[sizeof(name_buf) - 1]= 0; + make_upper(name_buf); + + for (var= variables; var->type == SHOW_FUNC; var= &tmp) + ((mysql_show_var_func)(var->value))(thd, &tmp, buff); + + show_type= var->type; + + if (show_type == SHOW_ARRAY) + { + fill_schema_status(thd, (SHOW_VAR*) var->value, + status_var, name_buf, table); + } + else + { + char *value= var->value; + + restore_record(table, s->default_values); + table->field[0]->store(name_buf, strlen(name_buf), system_charset_info); + + if (show_type == SHOW_SYS) + { + show_type= ((sys_var*) value)->type(); + value= (char*) ((sys_var*) value)->value_ptr(thd, OPT_GLOBAL, + &null_lex_str); + } + + switch (show_type) + { + case SHOW_DOUBLE_STATUS: + value= (char*) status_var + (ulong) value; + table->field[1]->store(*(double*) value); + break; + case SHOW_LONG_STATUS: + value= (char*) status_var + (ulong) value; + /* fall through */ + case SHOW_LONG: + case SHOW_LONG_NOFLUSH: /* the difference lies in refresh_status() */ + table->field[1]->store((longlong) *(long*) value, false); + break; + case SHOW_LONGLONG: + table->field[1]->store(*(longlong*) value, false); + break; + case SHOW_HA_ROWS: + table->field[1]->store((longlong) *(ha_rows*) value, false); + break; + case SHOW_BOOL: + table->field[1]->store((longlong) *(bool*) value, false); + break; + case SHOW_MY_BOOL: + table->field[1]->store((longlong) *(my_bool*) value, false); + break; + case SHOW_INT: + table->field[1]->store((longlong) *(uint32*) value, false); + break; + case SHOW_HAVE: /* always displayed as 0 */ + table->field[1]->store((longlong) 0, false); + break; + case SHOW_CHAR_PTR: + value= *(char**) value; + /* fall through */ + case SHOW_CHAR: /* always displayed as 0 */ + table->field[1]->store((longlong) 0, false); + break; + case SHOW_KEY_CACHE_LONG: + value= (char*) dflt_key_cache + (ulong) value; + table->field[1]->store((longlong) *(long*) value, false); + break; + case SHOW_KEY_CACHE_LONGLONG: + value= (char*) dflt_key_cache + (ulong) value; + table->field[1]->store(*(longlong*) value, false); + break; + case SHOW_UNDEF: /* always displayed as 0 */ + table->field[1]->store((longlong) 0, false); + break; + case SHOW_SYS: /* cannot happen */ + default: + DBUG_ASSERT(0); + break; + } + + table->field[1]->set_notnull(); + if (schema_table_store_record(thd, table)) + DBUG_RETURN(1); + } + } + + DBUG_RETURN(0); +} + +int fill_schema_global_status(THD *thd, TABLE_LIST *tables, COND *cond) +{ + STATUS_VAR tmp; + int res= 0; + DBUG_ENTER("fill_schema_global_status"); + + pthread_mutex_lock(&LOCK_status); + calc_sum_of_all_status(&tmp); + res= fill_schema_status(thd, (SHOW_VAR*) all_status_vars.buffer, + &tmp, "", tables->table); + pthread_mutex_unlock(&LOCK_status); + + DBUG_RETURN(res); +} + +int fill_schema_session_status(THD *thd, TABLE_LIST *tables, COND *cond) +{ + int res= 0; + DBUG_ENTER("fill_schema_session_status"); + + pthread_mutex_lock(&LOCK_status); + res= fill_schema_status(thd, (SHOW_VAR*) all_status_vars.buffer, + &thd->status_var, "", tables->table); + pthread_mutex_unlock(&LOCK_status); + + DBUG_RETURN(res); +} + +int fill_schema_global_variables(THD *thd, TABLE_LIST *tables, COND *cond) +{ + int res= 0; + DBUG_ENTER("fill_schema_global_variables"); + + pthread_mutex_lock(&LOCK_global_system_variables); + res= show_status_array(thd, "", init_vars, OPT_GLOBAL, + NULL, "", tables->table, 1); + pthread_mutex_unlock(&LOCK_global_system_variables); + + DBUG_RETURN(res); +} + +int fill_schema_session_variables(THD *thd, TABLE_LIST *tables, COND *cond) +{ + int res= 0; + DBUG_ENTER("fill_schema_session_variables"); + + pthread_mutex_lock(&LOCK_global_system_variables); + res= show_status_array(thd, "", init_vars, OPT_SESSION, + NULL, "", tables->table, 1); + pthread_mutex_unlock(&LOCK_global_system_variables); + + DBUG_RETURN(res); +} + ST_FIELD_INFO schema_fields_info[]= { {"CATALOG_NAME", FN_REFLEN, MYSQL_TYPE_STRING, 0, 1, 0}, @@ -5523,6 +5716,22 @@ ST_FIELD_INFO variables_fields_info[]= }; +ST_FIELD_INFO status_fields_info[]= +{ + {"VARIABLE_NAME", 64, MYSQL_TYPE_STRING, 0, 0, "Variable_name"}, + {"VARIABLE_VALUE", 2207, MYSQL_TYPE_DECIMAL, 0, 0, "Value"}, + {0, 0, MYSQL_TYPE_STRING, 0, 0, 0} +}; + + +ST_FIELD_INFO system_variables_fields_info[]= +{ + {"VARIABLE_NAME", 64, MYSQL_TYPE_STRING, 0, 0, "Variable_name"}, + {"VARIABLE_VALUE", 65535, MYSQL_TYPE_STRING, 0, 1, "Value"}, + {0, 0, MYSQL_TYPE_STRING, 0, 0, 0} +}; + + ST_FIELD_INFO processlist_fields_info[]= { {"ID", 4, MYSQL_TYPE_LONG, 0, 0, "Id"}, @@ -5635,6 +5844,10 @@ ST_SCHEMA_TABLE schema_tables[]= fill_schema_events, make_old_format, 0, -1, -1, 0}, {"FILES", files_fields_info, create_schema_table, fill_schema_files, 0, 0, -1, -1, 0}, + {"GLOBAL_STATUS", status_fields_info, create_schema_table, + fill_schema_global_status, make_old_format, 0, -1, -1, 0}, + {"GLOBAL_VARIABLES", system_variables_fields_info, create_schema_table, + fill_schema_global_variables, make_old_format, 0, -1, -1, 0}, {"KEY_COLUMN_USAGE", key_column_usage_fields_info, create_schema_table, get_all_tables, 0, get_schema_key_column_usage_record, 4, 5, 0}, {"OPEN_TABLES", open_tables_fields_info, create_schema_table, @@ -5654,6 +5867,10 @@ ST_SCHEMA_TABLE schema_tables[]= fill_schema_shemata, make_schemata_old_format, 0, 1, -1, 0}, {"SCHEMA_PRIVILEGES", schema_privileges_fields_info, create_schema_table, fill_schema_schema_privileges, 0, 0, -1, -1, 0}, + {"SESSION_STATUS", status_fields_info, create_schema_table, + fill_schema_session_status, make_old_format, 0, -1, -1, 0}, + {"SESSION_VARIABLES", system_variables_fields_info, create_schema_table, + fill_schema_session_variables, make_old_format, 0, -1, -1, 0}, {"STATISTICS", stat_fields_info, create_schema_table, get_all_tables, make_old_format, get_schema_stat_record, 1, 2, 0}, {"STATUS", variables_fields_info, create_schema_table, fill_status, diff --git a/sql/table.h b/sql/table.h index 3fb7222cb0d..c490c283b72 100644 --- a/sql/table.h +++ b/sql/table.h @@ -474,6 +474,8 @@ enum enum_schema_tables SCH_ENGINES, SCH_EVENTS, SCH_FILES, + SCH_GLOBAL_STATUS, + SCH_GLOBAL_VARIABLES, SCH_KEY_COLUMN_USAGE, SCH_OPEN_TABLES, SCH_PARTITIONS, @@ -483,6 +485,8 @@ enum enum_schema_tables SCH_PROCEDURES, SCH_SCHEMATA, SCH_SCHEMA_PRIVILEGES, + SCH_SESSION_STATUS, + SCH_SESSION_VARIABLES, SCH_STATISTICS, SCH_STATUS, SCH_TABLES, |