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:
1 2 |
sqlalchemy.create_engine("mysql+pymysql://{}:{}@{}:{}/{}?charset={}", connect_args={'connect_timeout': 10}) //10 seconds |
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.
1 |
SELECT /*+ MAX_EXECUTION_TIME(1000) */ status, count(*) FROM articles GROUP BY status ORDER BY status; |
Or you can set a session-wide or global timeout:
1 2 |
SET SESSION MAX_EXECUTION_TIME=2000; SET GLOBAL MAX_EXECUTION_TIME=2000; |
The timeouts only apply to read-only SELECT queries.
If your query takes to long, it fails with the following error:
1 |
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded |
In Python, we can do this:
1 2 3 4 5 6 7 8 |
class TimeOutProxy(ConnectionProxy): def cursor_execute(self, execute, cursor, statement, parameters, context, executemany): timeout = context.execution_options.get('timeout', None) if timeout: c = cursor.connection.cursor() c.execute('SET SESSION MAX_EXECUTION_TIME=%d;' % int(timeout * 1000)) c.close() return execute(cursor, statement, parameters, context) |
1 2 3 |
engine_ppc = sqlalchemy.create_engine( "mysql+pymysql://{}:{}@{}:{}/{}?charset={}", connect_args={'connect_timeout': 10}, proxy=TimeOutProxy()) |
Then in the place where you want to execute the query:
1 2 3 4 5 |
try: engine_ppc.execute( sa_text('''Select from table where id>0 ''').execution_options(autocommit=True, timeout=10)) except sqlalchemy.exc.OperationalError as e: print("Timeout when querying", flush=True) |
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
![[汇总]数据分析案例](http://www.mobabel.net/wp-content/themes/hueman/assets/front/img/thumb-medium-empty.png)
![[汇总]AI经验](http://www.mobabel.net/wp-content/themes/hueman/assets/front/img/thumb-small-empty.png)
