1. 

   cayasanchez, Member

   Good morning MyDBR team:

   We have few reports that sometimes takes too long to show the data, when this happens myDBR can't show any other report.
   We want to configure MyDBR to wait only 60 seconds, if the query takes more than that automatically kill the process.
   I didn't find anything in MyDBR documentation, do you have this option to set the maximum running time for a report?

   I tried adding this statemente before the select, but it didn't work:

   SET STATEMENT max_statement_time = 60 FOR SELECT ....

   Thanks for your help
2. 

   myDBR Team, Key Master

   Long running reports should not block other reports.

   The max_statement_time setting should work for SELECT queries in both MySQL and MariaDB. In MySQL, the parameter is in milliseconds, while in MariaDB, it is in seconds.

   Alternatively, you can use an event to track and handle long-running queries (note that this is applied per query, not the full procedure):

   Set the event_scheduler on:
   

   SET GLOBAL event_scheduler = ON;

   Create a procedure that checks long running queries:

   create procedure sp_killmax60sec()
   
   begin
   
   declare v_id int;
   
   declare done int default 0;
   
   declare c_cursor cursor for
   
     select id
   
     from INFORMATION_SCHEMA.PROCESSLIST pl
   
     where COMMAND='Query' and DB='mydbr' and pl.TIME>60;
   
   declare continue handler for not found set done = 1;
   
   open c_cursor;
   
   repeat
   
     fetch c_cursor into v_id;
   
     if not done then
   
       kill v_id;
   
     end if;
   
   until done end repeat;
   
   close c_cursor;
   
   end
   
   

   And then create an event for it:

   CREATE EVENT killer ON SCHEDULE EVERY 5 SECOND DO CALL sp_killmax60sec();

   --
   myDBR Team

Reply

You must log in to post.