mysql 读取 超时,MySQL 5.6中的全局查询超时

I need to apply a query timeout at a global level in my application. The query: SET SESSION max_execution_time=1 does this with MySQL 5.7. I am using MySQL 5.6 and cannot upgrade at the moment. Any solution with SQL Alchemy would also help.

解决方案

It seems there is no equivalent to max_execution_time in MySQL prior to versions 5.7.4 and 5.7.8 (the setting changed its name). What you can do is create your own periodic job that checks if queries have exceeded timeout and manually kill them. Unfortunately that is not quite the same as what the newer MySQL versions do: without inspecting the command info you'll end up killing all queries, not just read only SELECT, and it is nigh impossible to control at session level.

One way to do that would be to create a stored procedure that queries the process list and kills as required. Such stored procedure could look like:

DELIMITER //

CREATE PROCEDURE stmt_timeout_killer (timeout INT)

BEGIN

DECLARE query_id INT;

DECLARE done INT DEFAULT FALSE;

DECLARE curs CURSOR FOR

SELECT id

FROM information_schema.processlist

WHERE command = 'Query' AND time >= timeout;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

-- Ignore ER_NO_SUCH_THREAD, in case the query finished between

-- checking the process list and actually killing threads

DECLARE CONTINUE HANDLER FOR 1094 BEGIN END;

OPEN curs;

read_loop: LOOP

FETCH curs INTO query_id;

IF done THEN

LEAVE read_loop;

END IF;

-- Prevent suicide

IF query_id != CONNECTION_ID() THEN

KILL QUERY query_id;

END IF;

END LOOP;

CLOSE curs;

END//

DELIMITER ;

Alternatively you could implement all that in your application logic, but it would require separate round trips to the database for each query to be killed. What's left then is to call this periodically:

# Somewhere suitable

engine.execute(text("CALL stmt_timeout_killer(:timeout)"), timeout=30)

How and where exactly depends heavily on your actual application.