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
|
#
# Testing of user status (the userstat variable).
# Note that this test requires a fresh restart to not have problems with the
# old status values
-- source include/have_innodb.inc
-- source include/have_log_bin.inc
-- source include/have_perfschema.inc
--enable_prepare_warnings
--disable_ps2_protocol
--disable_cursor_protocol
select variable_value from information_schema.global_status where variable_name="handler_read_key" into @global_read_key;
--disable_prepare_warnings
--enable_cursor_protocol
show columns from information_schema.client_statistics;
show columns from information_schema.user_statistics;
show columns from information_schema.index_statistics;
show columns from information_schema.table_statistics;
# Disable logging to get right number of writes into the tables.
set @save_general_log=@@global.general_log;
set @@global.general_log=0;
set @@global.userstat=1;
flush status;
create table t1 (a int, primary key (a), b int default 0) engine=innodb;
insert into t1 (a) values (1),(2),(3),(4);
update t1 set b=1;
update t1 set b=5 where a=2;
delete from t1 where a=3;
--disable_cursor_protocol
/* Empty query */
select * from t1 where a=999;
drop table t1;
# test SSL connections
--connect (ssl_con,localhost,root,,,,,SSL)
SELECT (VARIABLE_VALUE <> '') AS have_ssl FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='Ssl_cipher';
--connection default
--enable_cursor_protocol
#
# Test the commit and rollback are counted
#
create table t1 (a int, primary key (a), b int default 0) engine=innodb;
begin;
insert into t1 values(1,1);
commit;
begin;
insert into t1 values(2,2);
commit;
begin;
insert into t1 values(3,3);
rollback;
drop table t1;
--disable_cursor_protocol
select sleep(1);
show status like "rows%";
show status like "ha%";
select variable_value - @global_read_key as "handler_read_key" from information_schema.global_status where variable_name="handler_read_key";
--enable_cursor_protocol
--disconnect ssl_con
# Ensure that the following commands doesn't change statistics
set @@global.userstat=0;
#
# Check that we got right statistics
#
select * from information_schema.index_statistics;
select * from information_schema.table_statistics;
show table_statistics;
show index_statistics;
--query_vertical select TOTAL_CONNECTIONS, TOTAL_SSL_CONNECTIONS, CONCURRENT_CONNECTIONS, ROWS_READ, ROWS_SENT, ROWS_DELETED, ROWS_INSERTED, ROWS_UPDATED, SELECT_COMMANDS, UPDATE_COMMANDS, COMMIT_TRANSACTIONS, ROLLBACK_TRANSACTIONS, DENIED_CONNECTIONS, LOST_CONNECTIONS, ACCESS_DENIED, EMPTY_QUERIES from information_schema.client_statistics;
--query_vertical select TOTAL_CONNECTIONS, TOTAL_SSL_CONNECTIONS, CONCURRENT_CONNECTIONS, ROWS_READ, ROWS_SENT, ROWS_DELETED, ROWS_INSERTED, ROWS_UPDATED, SELECT_COMMANDS, UPDATE_COMMANDS, COMMIT_TRANSACTIONS, ROLLBACK_TRANSACTIONS, DENIED_CONNECTIONS, LOST_CONNECTIONS, ACCESS_DENIED, EMPTY_QUERIES from information_schema.user_statistics;
# different values in --ps-protocol
select OTHER_COMMANDS IN (7,8) from information_schema.client_statistics;
select OTHER_COMMANDS IN (7,8) from information_schema.user_statistics;
flush table_statistics;
flush index_statistics;
select * from information_schema.index_statistics;
select * from information_schema.table_statistics;
show status like "%generic%";
#
# Test that some variables are not 0
#
select connected_time <> 0, busy_time <> 0, bytes_received <> 0,
bytes_sent <> 0, binlog_bytes_written <> 0
from information_schema.user_statistics;
select connected_time <> 0, busy_time <> 0, bytes_received <> 0,
bytes_sent <> 0, binlog_bytes_written <> 0
from information_schema.client_statistics;
#
# Test of in transaction
#
create table t1 (a int) engine=innodb;
select @@in_transaction;
begin;
select @@in_transaction;
insert into t1 values (1);
select @@in_transaction;
commit;
select @@in_transaction;
set @@autocommit=0;
select @@in_transaction;
insert into t1 values (2);
select @@in_transaction;
set @@autocommit=1;
select @@in_transaction;
drop table t1;
set @@global.general_log=@save_general_log;
--echo #
--echo # MDEV-25242 Server crashes in check_grant upon invoking function with userstat enabled
--echo #
create function f() returns int return (select 1 from performance_schema.threads);
set global userstat= 1;
create table t1 (a int primary key);
insert into t1 values (1),(2);
select * from t1 where a=1;
--error ER_SUBQUERY_NO_1_ROW
select f() from information_schema.table_statistics;
--error ER_SUBQUERY_NO_1_ROW
select f() from information_schema.index_statistics;
set global userstat= 0;
drop function f;
drop table t1;
--enable_ps2_protocol
--echo # End of 10.2 tests
--echo #
--echo # MDEV-36586 USER_STATISTICS.BUSY_TIME is in microseconds
--echo #
select distinct busy_time>1e5, cpu_time>1e5 from information_schema.user_statistics;
--echo # End of 10.11 tests
--echo #
--echo # MDEV-33901 INDEX_STATISTICS.QUERIES is incremented additionally for
--echo # subqueries
--echo #
SET @save_userstat= @@userstat;
set global userstat= 1;
create or replace table t1 (a int, key(a)) engine=MyISAM;
insert into t1 values (1),(2),(3),(4);
flush index_statistics;
--disable_ps2_protocol
select a from t1 where a in ( select a from t1 );
--enable_ps2_protocol
show index_statistics;
drop table t1;
set global userstat=@save_userstat;
--echo # End of 11.5 tests
--echo #
--echo # MDEV-34782 SIGSEGV in handler::update_global_table_stats in
--echo # close_thread_table()
--echo #
CREATE TABLE t1 (a CHAR(1));
HANDLER t1 OPEN;
INSERT INTO t1 VALUES (1);
HANDLER t1 READ NEXT;
SET GLOBAL userstat=1;
HANDLER t1 close;
drop table t1;
SET GLOBAL userstat=@save_userstat;
|
