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
|
# This test requires that --log-output includes 'table', and the general
# log is on
flush status;
--disable_warnings
drop table if exists t1;
--enable_warnings
# End of 4.1 tests
#
# last_query_cost
#
select 1;
--replace_column 2 #
show status like 'last_query_cost';
create temporary table t1 (a int) engine=myisam;
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;
--replace_column 2 #
show status like 'last_query_cost';
# Ensure value dosn't change by second status call
--replace_column 2 #
show status like 'last_query_cost';
select 1;
--replace_column 2 #
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 $max_used = `SELECT @max_used_connections`;
let $wait_left= `SELECT @wait_left`;
while ($max_used != 1 && $wait_left > 0)
{
FLUSH STATUS;
SET @wait_left = @wait_left - 1;
let $max_used_connections = `SHOW STATUS LIKE 'max_used_connections'`;
SET @max_used_connections = SUBSTRING('$max_used_connections', 21)+0;
let $max_used = `SELECT @max_used_connections`;
let $wait_left= `SELECT @wait_left`;
}
--enable_query_log
--enable_result_log
# Prerequisite.
SHOW STATUS LIKE 'max_used_connections';
SELECT * FROM data_dictionary.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections';
# Save original setting.
connect (con1,localhost,root,,);
connect (con2,localhost,root,,);
connection con1;
disconnect con2;
connection default;
disconnect con1;
# Check that max_used_connections still reflects maximum value.
SHOW STATUS LIKE 'max_used_connections';
SELECT * FROM data_dictionary.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections';
#
# 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';
--replace_column 9 #
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';
--replace_column 9 #
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
# https://bugs.launchpad.net/drizzle/+bug/310508
#
#
# 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
#
# Bug #17954: Threads_connected > Threads_created
#
#SELECT VARIABLE_VALUE INTO @tc FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Threads_connected';
#SELECT VARIABLE_NAME FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Threads_created' AND VARIABLE_VALUE < @tc;
#SELECT VARIABLE_VALUE INTO @tr FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Threads_running';
#FLUSH STATUS;
#SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Threads_connected' AND VARIABLE_VALUE < @tc;
#SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Threads_running' AND VARIABLE_VALUE < @tr;
|