1 # This test requires that --log-output includes 'table', and the general
4 # embedded server causes different stat
5 -- source include/not_embedded.inc
7 # Save the initial number of concurrent sessions
8 --source include/count_sessions.inc
10 # Disable concurrent inserts to avoid sporadic test failures as it might
11 # affect the the value of variables used throughout the test case.
12 set @old_concurrent_insert= @@global.concurrent_insert;
13 set @@global.concurrent_insert= 0;
15 # Disable logging to table, since this will also cause table locking and unlocking, which will
16 # show up in SHOW STATUS and may cause sporadic failures
18 SET @old_log_output = @@global.log_output;
19 SET GLOBAL LOG_OUTPUT = 'FILE';
21 # PS causes different statistics
24 connect (con1,localhost,root,,);
25 connect (con2,localhost,root,,);
29 show status like 'Table_lock%';
30 select * from information_schema.session_status where variable_name like 'Table_lock%';
33 --echo # Switched to connection: con1
35 set @old_general_log = @@global.general_log;
36 set global general_log = 'OFF';
38 drop table if exists t1;
41 create table t1(n int) engine=myisam;
42 insert into t1 values(1);
43 # Execute dummy select in order to ensure that tables used in the
44 # previous statement are unlocked and closed.
48 --echo # Switched to connection: con2
54 --echo # Switched to connection: con1
55 let $ID= `select connection_id()`;
56 --send update t1 set n = 3
59 --echo # Switched to connection: con2
60 # wait for the other query to start executing
61 let $wait_condition= select 1 from INFORMATION_SCHEMA.PROCESSLIST where ID = $ID and STATE = "Locked";
62 --source include/wait_condition.inc
66 --echo # Switched to connection: con1
68 show status like 'Table_locks_waited';
70 set global general_log = @old_general_log;
75 --echo # Switched to connection: default
84 show status like 'last_query_cost';
85 create table t1 (a int);
86 insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
87 insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
88 insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
89 insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
90 insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
91 select * from t1 where a=6;
92 show status like 'last_query_cost';
93 # Ensure value dosn't change by second status call
94 show status like 'last_query_cost';
96 show status like 'last_query_cost';
100 # Test for Bug#15933 max_used_connections is wrong after FLUSH STATUS
101 # if connections are cached
104 # The first suggested fix from the bug report was chosen
105 # (see http://bugs.mysql.com/bug.php?id=15933):
107 # a) On flushing the status, set max_used_connections to
108 # threads_connected, not to 0.
110 # b) Check if it is necessary to increment max_used_connections when
111 # taking a thread from the cache as well as when creating new threads
114 # Wait for at most $disconnect_timeout seconds for disconnects to finish.
115 let $disconnect_timeout = 10;
117 # Wait for any previous disconnects to finish.
121 eval SET @wait_left = $disconnect_timeout;
122 let $max_used_connections = `SHOW STATUS LIKE 'max_used_connections'`;
123 eval SET @max_used_connections = SUBSTRING('$max_used_connections', 21)+0;
124 let $wait_more = `SELECT @max_used_connections != 1 && @wait_left > 0`;
129 SET @wait_left = @wait_left - 1;
130 let $max_used_connections = `SHOW STATUS LIKE 'max_used_connections'`;
131 eval SET @max_used_connections = SUBSTRING('$max_used_connections', 21)+0;
132 let $wait_more = `SELECT @max_used_connections != 1 && @wait_left > 0`;
138 SHOW STATUS LIKE 'max_used_connections';
139 SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections';
141 # Save original setting.
142 SET @save_thread_cache_size=@@thread_cache_size;
143 SET GLOBAL thread_cache_size=3;
145 connect (con1,localhost,root,,);
146 connect (con2,localhost,root,,);
151 # Check that max_used_connections still reflects maximum value.
152 SHOW STATUS LIKE 'max_used_connections';
153 SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections';
155 # Check that after flush max_used_connections equals to current number
156 # of connections. First wait for previous disconnect to finish.
160 eval SET @wait_left = $disconnect_timeout;
161 let $max_used_connections = `SHOW STATUS LIKE 'max_used_connections'`;
162 eval SET @max_used_connections = SUBSTRING('$max_used_connections', 21)+0;
163 let $wait_more = `SELECT @max_used_connections != 2 && @wait_left > 0`;
168 SET @wait_left = @wait_left - 1;
169 let $max_used_connections = `SHOW STATUS LIKE 'max_used_connections'`;
170 eval SET @max_used_connections = SUBSTRING('$max_used_connections', 21)+0;
171 let $wait_more = `SELECT @max_used_connections != 2 && @wait_left > 0`;
175 # Check that we don't count disconnected thread any longer.
176 SHOW STATUS LIKE 'max_used_connections';
177 SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections';
179 # Check that max_used_connections is updated when cached thread is
181 connect (con2,localhost,root,,);
182 SHOW STATUS LIKE 'max_used_connections';
183 SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections';
185 # ...and when new thread is created.
186 connect (con3,localhost,root,,);
187 SHOW STATUS LIKE 'max_used_connections';
188 SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'max_used_connections';
190 # Restore original setting.
192 SET GLOBAL thread_cache_size=@save_thread_cache_size;
200 # Bug#30377 EXPLAIN loses last_query_cost when used with UNION
203 CREATE TABLE t1 ( a INT );
204 INSERT INTO t1 VALUES (1), (2);
206 SELECT a FROM t1 LIMIT 1;
207 SHOW SESSION STATUS LIKE 'Last_query_cost';
209 EXPLAIN SELECT a FROM t1;
210 SHOW SESSION STATUS LIKE 'Last_query_cost';
212 SELECT a FROM t1 UNION SELECT a FROM t1 ORDER BY a;
213 SHOW SESSION STATUS LIKE 'Last_query_cost';
215 EXPLAIN SELECT a FROM t1 UNION SELECT a FROM t1 ORDER BY a;
216 SHOW SESSION STATUS LIKE 'Last_query_cost';
218 SELECT a IN (SELECT a FROM t1) FROM t1 LIMIT 1;
219 SHOW SESSION STATUS LIKE 'Last_query_cost';
221 SELECT (SELECT a FROM t1 LIMIT 1) x FROM t1 LIMIT 1;
222 SHOW SESSION STATUS LIKE 'Last_query_cost';
224 SELECT * FROM t1 a, t1 b LIMIT 1;
225 SHOW SESSION STATUS LIKE 'Last_query_cost';
233 # Ensure that SHOW STATUS only changes global status variables
236 connect (con1,localhost,root,,);
237 let $rnd_next = `show global status like 'handler_read_rnd_next'`;
238 let $tmp_table = `show global status like 'Created_tmp_tables'`;
239 show status like 'com_show_status';
240 show status like 'hand%write%';
241 show status like '%tmp%';
242 show status like 'hand%write%';
243 show status like '%tmp%';
244 show status like 'com_show_status';
245 let $rnd_next2 = `show global status like 'handler_read_rnd_next'`;
246 let $tmp_table2 = `show global status like 'Created_tmp_tables'`;
248 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;
254 # Bug#30252 Com_create_function is not incremented.
257 show status like 'Com%function';
260 create function f1 (x INTEGER) returns integer
270 show status like 'Com%function';
273 # Bug#37908 Skipped access right check caused server crash.
275 connect (root, localhost, root,,test);
277 let $root_connection_id= `select connection_id()`;
279 create database db37908;
281 create table db37908.t1(f1 int);
282 insert into db37908.t1 values(1);
283 grant usage,execute on test.* to mysqltest_1@localhost;
285 create procedure proc37908() begin select 1; end |
286 create function func37908() returns int sql security invoker
287 return (select * from db37908.t1 limit 1)|
290 connect (user1,localhost,mysqltest_1,,test);
292 let $user1_connection_id= `select connection_id()`;
294 --error ER_TABLEACCESS_DENIED_ERROR
295 select * from db37908.t1;
296 --error ER_TABLEACCESS_DENIED_ERROR
297 show status where variable_name ='uptime' and 2 in (select * from db37908.t1);
298 --error ER_TABLEACCESS_DENIED_ERROR
299 show procedure status where name ='proc37908' and 1 in (select f1 from db37908.t1);
300 --error ER_TABLEACCESS_DENIED_ERROR
301 show function status where name ='func37908' and 1 in (select func37908());
306 drop database db37908;
307 drop procedure proc37908;
308 drop function func37908;
309 REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
310 DROP USER mysqltest_1@localhost;
311 # Wait till the sessions user1 and root are disconnected
312 let $wait_condition =
314 FROM information_schema.processlist
315 WHERE id in ('$root_connection_id','$user1_connection_id');
316 --source include/wait_condition.inc
319 # Bug#41131 "Questions" fails to increment - ignores statements instead stored procs
321 connect (con1,localhost,root,,);
324 DROP PROCEDURE IF EXISTS p1;
325 DROP FUNCTION IF EXISTS f1;
328 CREATE FUNCTION f1() RETURNS INTEGER
336 CREATE PROCEDURE p1()
341 let $org_queries= `SHOW STATUS LIKE 'Queries'`;
344 let $new_queries= `SHOW STATUS LIKE 'Queries'`;
346 let $diff= `SELECT SUBSTRING('$new_queries',9)-SUBSTRING('$org_queries',9)`;
356 # Restore global concurrent_insert value. Keep in the end of the test file.
358 set @@global.concurrent_insert= @old_concurrent_insert;
359 SET GLOBAL log_output = @old_log_output;
361 # Wait till we reached the initial number of concurrent sessions
362 --source include/wait_until_count_sessions.inc