status.test 5.66 KB
Newer Older
1 2 3
# embedded server causes different stat
-- source include/not_embedded.inc

4 5
# PS causes different statistics
--disable_ps_protocol
6

7 8
connect (con1,localhost,root,,);
connect (con2,localhost,root,,);
9

10
flush status;
11
show status like 'Table_lock%';
12
select * from information_schema.session_status where variable_name like 'Table_lock%';
13 14
connection con1;
SET SQL_LOG_BIN=0;
15
--disable_warnings
16
drop table if exists t1;
17 18
--enable_warnings

19
create table t1(n int) engine=myisam;
20 21 22 23 24 25
insert into t1 values(1);
connection con2;
lock tables t1 read;
unlock tables;
lock tables t1 read;
connection con1;
26 27
--send
update t1 set n = 3;
28 29 30 31 32 33
connection con2;
sleep 0.5;
unlock tables;
connection con1;
reap;
show status like 'Table_lock%';
34
select * from information_schema.session_status where variable_name like 'Table_lock%';
35
drop table t1;
36

37 38 39 40
disconnect con2;
disconnect con1;
connection default;

41
# End of 4.1 tests
42 43

#
44
# last_query_cost
45 46 47 48
#

select 1;
show status like 'last_query_cost';
49 50 51 52 53 54 55 56 57 58 59 60 61
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;
62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77

#
# Test for Bug #15933 max_used_connections is wrong after FLUSH STATUS
# if connections are cached
#
#
# The first suggested fix from the bug report was chosen
# (see http://bugs.mysql.com/bug.php?id=15933):
#
#   a) On flushing the status, set max_used_connections to
#   threads_connected, not to 0.
#
#   b) Check if it is necessary to increment max_used_connections when
#   taking a thread from the cache as well as when creating new threads
#

78 79
# Wait for at most $disconnect_timeout seconds for disconnects to finish.
let $disconnect_timeout = 10;
80

81
# Wait for any previous disconnects to finish.
82
FLUSH STATUS;
83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101
--disable_query_log
--disable_result_log
eval SET @wait_left = $disconnect_timeout;
let $max_used_connections = `SHOW STATUS LIKE 'max_used_connections'`;
eval SET @max_used_connections = SUBSTRING('$max_used_connections', 21)+0;
let $wait_more = `SELECT @max_used_connections != 1 && @wait_left > 0`;
while ($wait_more)
{
  sleep 1;
  FLUSH STATUS;
  SET @wait_left = @wait_left - 1;
  let $max_used_connections = `SHOW STATUS LIKE 'max_used_connections'`;
  eval SET @max_used_connections = SUBSTRING('$max_used_connections', 21)+0;
  let $wait_more = `SELECT @max_used_connections != 1 && @wait_left > 0`;
}
--enable_query_log
--enable_result_log

# Prerequisite.
102
SHOW STATUS LIKE 'max_used_connections';
103
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections';
104 105 106 107 108

# Save original setting.
SET @save_thread_cache_size=@@thread_cache_size;
SET GLOBAL thread_cache_size=3;

109 110
connect (con1,localhost,root,,);
connect (con2,localhost,root,,);
111

112 113
connection con1;
disconnect con2;
114 115 116

# Check that max_used_connections still reflects maximum value.
SHOW STATUS LIKE 'max_used_connections';
117
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections';
118 119

# Check that after flush max_used_connections equals to current number
120
# of connections.  First wait for previous disconnect to finish.
121
FLUSH STATUS;
122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139
--disable_query_log
--disable_result_log
eval SET @wait_left = $disconnect_timeout;
let $max_used_connections = `SHOW STATUS LIKE 'max_used_connections'`;
eval SET @max_used_connections = SUBSTRING('$max_used_connections', 21)+0;
let $wait_more = `SELECT @max_used_connections != 2 && @wait_left > 0`;
while ($wait_more)
{
  sleep 1;
  FLUSH STATUS;
  SET @wait_left = @wait_left - 1;
  let $max_used_connections = `SHOW STATUS LIKE 'max_used_connections'`;
  eval SET @max_used_connections = SUBSTRING('$max_used_connections', 21)+0;
  let $wait_more = `SELECT @max_used_connections != 2 && @wait_left > 0`;
}
--enable_query_log
--enable_result_log
# Check that we don't count disconnected thread any longer.
140
SHOW STATUS LIKE 'max_used_connections';
141
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections';
142 143 144

# Check that max_used_connections is updated when cached thread is
# reused...
145
connect (con2,localhost,root,,);
146
SHOW STATUS LIKE 'max_used_connections';
147
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections';
148 149

# ...and when new thread is created.
150
connect (con3,localhost,root,,);
151
SHOW STATUS LIKE 'max_used_connections';
152
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections';
153 154 155 156 157 158 159 160 161 162

# Restore original setting.
connection default;
SET GLOBAL thread_cache_size=@save_thread_cache_size;

disconnect con3;
disconnect con2;
disconnect con1;

# End of 5.0 tests
163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183

#
# 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

# End of 5.1 tests