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
|
# embedded server causes different stat
-- source include/not_embedded.inc
# Save the initial number of concurrent sessions
--source include/count_sessions.inc
# PS causes different statistics
--disable_ps_protocol
connect (con1,localhost,root,,);
connect (con2,localhost,root,,);
flush status;
show status like 'Table_lock%';
connection con1;
SET SQL_LOG_BIN=0;
--disable_warnings
drop table if exists t1;
--enable_warnings
create table t1(n int) engine=myisam;
insert into t1 values(1);
connection con2;
lock tables t1 read;
unlock tables;
lock tables t1 read;
connection con1;
--send
update t1 set n = 3;
connection con2;
sleep 1;
unlock tables;
connection con1;
reap;
show status like 'Table_lock%';
drop table t1;
disconnect con2;
disconnect con1;
connection default;
# End of 4.1 tests
#
# last_query_cost
#
select 1;
show status like 'last_query_cost';
#
# 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';
# 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';
# 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';
# Check that max_used_connections is updated when cached thread is
# reused...
connect (con2,localhost,root,,);
SHOW STATUS LIKE 'max_used_connections';
# ...and when new thread is created.
connect (con3,localhost,root,,);
SHOW STATUS 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;
#
# 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;
# End of 5.0 tests
# Wait till all disconnects are completed
--source include/wait_until_count_sessions.inc
|
