status.test 9.49 KB
Newer Older
1 2 3
# This test requires that --log-output includes 'table', and the general
# log is on

4 5 6
# embedded server causes different stat
-- source include/not_embedded.inc

7 8 9 10 11
# 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;

12 13
# PS causes different statistics
--disable_ps_protocol
14

unknown's avatar
unknown committed
15 16
connect (con1,localhost,root,,);
connect (con2,localhost,root,,);
unknown's avatar
unknown committed
17

18
flush status;
19

unknown's avatar
unknown committed
20
show status like 'Table_lock%';
21
select * from information_schema.session_status where variable_name like 'Table_lock%';
22

unknown's avatar
unknown committed
23
connection con1;
24 25 26
--echo # Switched to connection: con1
set sql_log_bin=0;
set @old_general_log = @@global.general_log;
27
set global general_log = 'OFF';
28
--disable_warnings
unknown's avatar
unknown committed
29
drop table if exists t1;
30 31
--enable_warnings

unknown's avatar
unknown committed
32
create table t1(n int) engine=myisam;
unknown's avatar
unknown committed
33
insert into t1 values(1);
34 35 36
# Execute dummy select in order to ensure that tables used in the
# previous statement are unlocked and closed.
select 1;
37

unknown's avatar
unknown committed
38
connection con2;
39
--echo # Switched to connection: con2
unknown's avatar
unknown committed
40 41 42
lock tables t1 read;
unlock tables;
lock tables t1 read;
43

unknown's avatar
unknown committed
44
connection con1;
45
--echo # Switched to connection: con1
46
let $ID= `select connection_id()`;
47
--send update t1 set n = 3
48

unknown's avatar
unknown committed
49
connection con2;
50
--echo # Switched to connection: con2
51
# wait for the other query to start executing
52
let $wait_condition= select 1 from INFORMATION_SCHEMA.PROCESSLIST where ID = $ID and STATE = "Locked";
53
--source include/wait_condition.inc
unknown's avatar
unknown committed
54
unlock tables;
55

unknown's avatar
unknown committed
56
connection con1;
57
--echo # Switched to connection: con1
unknown's avatar
unknown committed
58
reap;
59
show status like 'Table_locks_waited';
60
drop table t1;
61
set global general_log = @old_general_log;
62

63 64 65
disconnect con2;
disconnect con1;
connection default;
66
--echo # Switched to connection: default
67

68
# End of 4.1 tests
69 70

#
71
# last_query_cost
72 73 74 75
#

select 1;
show status like 'last_query_cost';
76 77 78 79 80 81 82 83 84 85 86 87 88
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;
89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104

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

105 106
# Wait for at most $disconnect_timeout seconds for disconnects to finish.
let $disconnect_timeout = 10;
107

108
# Wait for any previous disconnects to finish.
109
FLUSH STATUS;
110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128
--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.
129
SHOW STATUS LIKE 'max_used_connections';
130
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections';
131 132 133 134 135

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

136 137
connect (con1,localhost,root,,);
connect (con2,localhost,root,,);
138

139 140
connection con1;
disconnect con2;
141 142 143

# Check that max_used_connections still reflects maximum value.
SHOW STATUS LIKE 'max_used_connections';
144
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections';
145 146

# Check that after flush max_used_connections equals to current number
147
# of connections.  First wait for previous disconnect to finish.
148
FLUSH STATUS;
149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166
--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.
167
SHOW STATUS LIKE 'max_used_connections';
168
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections';
169 170 171

# Check that max_used_connections is updated when cached thread is
# reused...
172
connect (con2,localhost,root,,);
173
SHOW STATUS LIKE 'max_used_connections';
174
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections';
175 176

# ...and when new thread is created.
177
connect (con3,localhost,root,,);
178
SHOW STATUS LIKE 'max_used_connections';
179
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections';
180 181 182 183 184 185 186 187 188

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

disconnect con3;
disconnect con2;
disconnect con1;

189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220

#
# Bug #30377: EXPLAIN loses last_query_cost when used with UNION
#

CREATE TABLE t1 ( a INT );
INSERT INTO t1 VALUES (1), (2);

SELECT a FROM t1 LIMIT 1;
SHOW SESSION STATUS LIKE 'Last_query_cost';

EXPLAIN SELECT a FROM t1;
SHOW SESSION STATUS LIKE 'Last_query_cost';

SELECT a FROM t1 UNION SELECT a FROM t1 ORDER BY a;
SHOW SESSION STATUS LIKE 'Last_query_cost';

EXPLAIN SELECT a FROM t1 UNION SELECT a FROM t1 ORDER BY a;
SHOW SESSION STATUS LIKE 'Last_query_cost';

SELECT a IN (SELECT a FROM t1) FROM t1 LIMIT 1;
SHOW SESSION STATUS LIKE 'Last_query_cost';

SELECT (SELECT a FROM t1 LIMIT 1) x FROM t1 LIMIT 1;
SHOW SESSION STATUS LIKE 'Last_query_cost';

SELECT * FROM t1 a, t1 b LIMIT 1;
SHOW SESSION STATUS LIKE 'Last_query_cost';

DROP TABLE t1;


221
# End of 5.0 tests
222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240

#
# 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
241 242
disconnect con1;
connection default;
243

244 245 246
# 
# Bug#30252 Com_create_function is not incremented.
#
unknown's avatar
unknown committed
247
flush status;
248
show status like 'Com%function';
249 250 251 252 253 254 255 256 257 258 259 260

DELIMITER //;
create function f1 (x INTEGER) returns integer
  begin
    declare ret integer;
    set ret = x * 10;
    return ret;
  end //
DELIMITER ;//

drop function f1;

261
show status like 'Com%function';
262

263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295
#
# Bug#37908: Skipped access right check caused server crash.
#
connect (root, localhost, root,,test);
connection root;
--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;

--error 1142
select * from db37908.t1;
--error 1142
show status where variable_name ='uptime' and 2 in (select * from db37908.t1);
--error 1142
show procedure status where name ='proc37908' and 1 in (select f1 from db37908.t1);
--error 1142
show function status where name ='func37908' and 1 in (select func37908());

connection root;
drop database db37908;
drop procedure proc37908;
drop function func37908;
296

297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332
#
# Bug#41131 "Questions" fails to increment - ignores statements instead stored procs
#
connect (con1,localhost,root,,);
connection con1;
--disable_warnings
DROP PROCEDURE IF EXISTS p1;
DROP FUNCTION IF EXISTS f1;
--enable_warnings
DELIMITER $$;
CREATE FUNCTION f1() RETURNS INTEGER
BEGIN
  DECLARE foo INTEGER;
  DECLARE bar INTEGER;
  SET foo=1;
  SET bar=2;
  RETURN foo;
END $$
CREATE PROCEDURE p1()
  BEGIN
  SELECT 1;
END $$
DELIMITER ;$$
let $org_queries= `SHOW STATUS LIKE 'Queries'`;
SELECT f1();
CALL p1();
let $new_queries= `SHOW STATUS LIKE 'Queries'`;
--disable_log
let $diff= `SELECT SUBSTRING('$new_queries',9)-SUBSTRING('$org_queries',9)`;
--enable_log
eval SELECT $diff;
disconnect con1;
connection default;
DROP PROCEDURE p1;
DROP FUNCTION f1;

333
# End of 5.1 tests
334 335 336 337

# Restore global concurrent_insert value. Keep in the end of the test file.
--connection default
set @@global.concurrent_insert= @old_concurrent_insert;