WrMtnCp8

1drop procedure if exists your_schema.kill_long_processes;
2delimiter ;;
3create procedure your_schema.kill_long_processes (p_long_query_time int(10), p_list_of_users varchar(255), p_kill_write_queries_too tinyint(1))
4begin
5if find_in_set ('admin', p_list_of_users) 
6   or find_in_set ('root', p_list_of_users) 
7   or find_in_set ('event_scheduler', p_list_of_users) 
8   or find_in_set('system user', p_list_of_users) then
9	select 'You are not authorized to perform this action!';
10else
11	drop table if exists your_schema.temp_kill_long_processes;	
12	create table your_schema.temp_kill_long_processes as
13	select id, kill_query from (select @id random, @id:= @id + 1 id, concat('kill ',p.id,';') kill_query 
14	from information_schema.processlist p, (select @id:= 0) r
15  where p.command <> 'Sleep'
16  	and p.user not in ('admin','system user','root','event_scheduler')
17  	and find_in_set (p.user, p_list_of_users)
18  	and p.time > p_long_query_time) t;  
19	alter table your_schema.temp_kill_long_processes add primary key (id);
20	set @total_kill_queries:= (select id from your_schema.temp_kill_long_processes 
21	                           order by 1 desc limit 1), @current_kill_query_count:= 1;
22	while (@current_kill_query_count <= @total_kill_queries) do
23		set @query_text:= (select kill_query from your_schema.temp_kill_long_processes 
24											  where id = @current_kill_query_count limit 1);
25		if p_kill_write_queries_too = 1 then	
26			prepare stmt from @query_text;
27			execute stmt;
28			deallocate prepare stmt;	
29		else 
30			if @query_text not like '%update%' 
31		     and @query_text not like '%insert%' 
32		     and @query_text not like '%delete%' 
33		     and @query_text not like '%create%' 
34		     and @query_text not like '%alter%'
35		  then
36				prepare stmt from @query_text;
37				execute stmt;
38				deallocate prepare stmt;	
39			end if;			
40		end if;
41		set @current_kill_query_count:= @current_kill_query_count + 1;
42	end while;
43end if;
44end ;;
45delimiter ;