summaryrefslogtreecommitdiff
path: root/mysql-test/main/status.test
blob: 221a24aedf49a67f0d7038fe0903a723efe63ff7 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
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
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
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
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
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
# 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

connect (con1,localhost,root,,);
connect (con2,localhost,root,,);
connection default;

flush status;

show status like 'Table_lock%';
select * from information_schema.session_status where variable_name like 'Table_lock%';

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);
select get_lock('mysqltest_lock', 100);

connection con2;
--echo # Sending:
--send update t1 set n = get_lock('mysqltest_lock', 100)

connection con1;
--echo # Wait for the first UPDATE to get blocked.
let $wait_condition= select count(*) from INFORMATION_SCHEMA.PROCESSLIST
                     where STATE = "User lock" and
                           INFO = "update t1 set n = get_lock('mysqltest_lock', 100)";
--source include/wait_condition.inc

let $ID= `select connection_id()`;
--echo # Sending:
--send update t1 set n = 3

connection default;
--echo # wait for the second UPDATE to get blocked
let $wait_condition= select 1 from INFORMATION_SCHEMA.PROCESSLIST
                     where ID = $ID and STATE = "Waiting for table level lock";
--source include/wait_condition.inc
select release_lock('mysqltest_lock');

connection con2;
--echo # Reaping first UPDATE
--reap
select release_lock('mysqltest_lock');

connection con1;
--echo # Reaping second UPDATE
reap;
show status like 'Table_locks_waited';

connection default;
drop table t1;
set global general_log = @old_general_log;

disconnect con2;
disconnect con1;

# End of 4.1 tests

#
# last_query_cost
#

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

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

# Wait for any previous disconnects to finish.
FLUSH STATUS;
--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.
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;
SET GLOBAL thread_cache_size=3;

connect (con1,localhost,root,,);
connect (con2,localhost,root,,);

connection con1;
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.
FLUSH STATUS;
--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.
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;
SET GLOBAL thread_cache_size=@save_thread_cache_size;

disconnect con3;
disconnect con2;
disconnect con1;


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


# 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);
create user mysqltest_1@localhost;
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
#
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_query_log
let $diff= `SELECT SUBSTRING('$new_queries',9)-SUBSTRING('$org_queries',9)`;
--enable_query_log
eval SELECT $diff;
disconnect con1;
connection default;
DROP PROCEDURE p1;
DROP FUNCTION f1;

# End of 5.1 tests

#
# Test of internal temporary table status variables
#

flush status;
create table t1 (a int not null auto_increment primary key, g int, b blob);
insert into t1 (g,b) values (1,'a'), (2, 'b'), (3, 'b'), (1, 'c');
select * from t1;
select b, count(*) from t1 group by b;
select g, count(*) from t1 group by g;
show status like 'Row%';
show status like 'Handler%';
show status like '%tmp%';
drop table t1;

#
# Test of handler status counts
#
CREATE TABLE t1 (i int(11) DEFAULT NULL, KEY i (i) ) ENGINE=MyISAM;
insert into t1 values (1),(2),(3),(4),(5);
flush status;
select * from t1 where i=5 union select * from t1 where i=5;
show status like "handler%";
drop table t1;

# End of 5.3 tests

--echo #
--echo # MDEV-11153 - Introduce status variables for table cache monitoring and
--echo #              tuning
--echo #
SET @old_table_open_cache= @@table_open_cache;
SET @@global.table_open_cache=10;
FLUSH TABLES;
FLUSH STATUS;
SHOW STATUS LIKE 'Table_open_cache%';
disable_query_log;
let $i= `SELECT @@table_open_cache + 5`;
while ($i)
{
  eval CREATE TABLE t$i(a INT);
  eval DELETE FROM t$i;
  eval DELETE FROM t$i;
  eval DELETE FROM t$i;
  dec $i;
}
enable_query_log;
SHOW STATUS LIKE 'Table_open_cache%';
FLUSH TABLES;
FLUSH STATUS;
disable_query_log;
let $i= `SELECT @@table_open_cache + 5`;
while ($i)
{
  eval DROP TABLE t$i;
  dec $i;
}
enable_query_log;
SET @@global.table_open_cache= @old_table_open_cache;

--echo #
--echo # MDEV-14505 - Threads_running becomes scalability bottleneck
--echo #
--echo # Session status for Threads_running is currently always 1.
SHOW STATUS LIKE 'Threads_running';
SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='THREADS_RUNNING';
FLUSH STATUS;
SHOW STATUS LIKE 'Threads_running';
SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='THREADS_RUNNING';

--echo #
--echo # MDEV-15915 Add Feature_json status variable.
--echo #
SHOW STATUS LIKE 'Feature_json';
select json_valid('123');
SHOW STATUS LIKE 'Feature_json';

# 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 we reached the initial number of concurrent sessions
--source include/wait_until_count_sessions.inc