Global query timeout in MySQL 5.6

Question

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.

Answer 1

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.