Set statement timeout for query execution

How to set statement timeout for query execution?

Prerequisites:

sqlalchemy 1.3.19

MySQL 5.7.13

You can set the connection timeout easily like this:

But sometimes when you execute a long time processing SQL, and you need to set a timeout for the query. Otherwise, the script will keep working for hours and gives no feedback.

For MySQL it self, you can use timeouts to prevent long-running queries from taking down your MySQL.

With MySQL 5.7 you can now use a new optimizer query hint to configure the max execution time of SELECT queries in Milliseconds.

Or you can set a session-wide or global timeout:

The timeouts only apply to read-only SELECT queries.

If your query takes to long, it fails with the following error:

In Python, we can do this:

Then in the place where you want to execute the query:

the timeout was set to 10 seconds.

Referfence:

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_interactive_timeout

https://stackoverflow.com/questions/35640726/how-to-set-connection-timeout-in-sqlalchemy/35640876

Use timeouts to prevent long-running SELECT queries from taking down your MySQL

https://stackoverflow.com/questions/6492366/how-to-set-statement-timeout-for-query-execution