vertx-sql-client
vertx-sql-client copied to clipboard
eclipse-vertx
query timeout
JDBC and JPA let me set a timeout on a query. Unless I'm missing something, it's a feature that's still needed in the reactive world, and AFAICT there's currently no way to do it in the Vert.x client.
what happens when the timeout elapses for the queries in progress ?
They get aborted, and the client receives an exception.
The current API I can see in JDBC is Connection#setNetworkTimeout(Executor,int) , is that the API you are referring to or am I missing something ?
Ok that makes more sense to me than what I found before.
we can support this for PG (the API for cancelling a query already exist) but I don't know how other implementations can handle it.
@aguibert @BillyYccc do you know if MySQL, MSSQL and DB2 support this ?
Google found this for me:
It seems that the actual way to proceed is similar to PostgreSQL which is to open a new connection to the server and execute a KILL QUERY statement using an ID provided by MySQL that we get in MySQLClient at https://github.com/eclipse-vertx/vertx-sql-client/blob/master/vertx-mysql-client/src/main/java/io/vertx/mysqlclient/impl/codec/InitialHandshakeCommandCodec.java#L78
In JDBC the implementation of Statement#setQueryTimeout is opening another connection and send a query cancel signal to ask the server to terminate the executing query.
that timeout means query_timeout = network_timeout + statement_timeout
But such mechanisms are not reliable because the cancel request mostly only tells which connection to cancel without any query identifier, so the cancel request might arrive at the server but the query has already been completed which will cause the new query being terminated unexpectedly. And the cancel request might get stuck by the network as well :-)
There is statement-level timeout in most mainstream database servers, which is far more reliable. The timer starts from when the command is handled by the server.
- MySQL max_execution_time
- Postgres statement_timeout
- MSSQL remote query timeout
In JDBC the implementation of
Statement#setQueryTimeoutis opening another connection and send a query cancel signal to ask the server to terminate the executing query.
Ugh, that sounds like a terrible way to do this. But surely that's just the implementation detail of some particular JDBC driver, that can't possibly be the way it's specified to work by JDBC, can it?
JDBC does not specify anything.
It is inherent to the client/database protocol design. These protocol (designed in the 80's) are usually not multiplexed.
In practice it means that when the database is executing a query then it will not be able to read anything from the client until the query has finished and the database has sent the response and therefore the only way is to use another connection and send a command to interrupt the query in progress using an identifier (provided by the connection interrupted). This is how it works for PostgreSQL and MySQL at least.
With a multiplexed protocol, you have the opportunity to reuse the same connection to perform this. e.g HTTP/2 do this and provides the ability to reset a stream (request/response).
It is inherent to the client/database protocol design. These protocol (designed in the 80's) are usually not multiplexed.
Right, but I had understood that the databases themselves had ways to specify the query timeout within their proprietary flavors of SQL, that the JDBC drivers were abstracting.
It is inherent to the client/database protocol design. These protocol (designed in the 80's) are usually not multiplexed.
Right, but I had understood that the databases themselves had ways to specify the query timeout within their proprietary flavors of SQL, that the JDBC drivers were abstracting.
(Just like in that link I sent you.)
I mean, how does it make sense for the client to be the thing responsible for timing out a stuck query?? That's gotta be something the server does.
I think there are two ways which are both doable in the client
- provide a
statementTimeoutoption which abstracts setting those statement execution timeout for different databases likeSET max_execution_time = 5000for MySQLSET statement_timeout = 5000;for PostgreSQL etc...
the SQL Client executes such queries for every new created connections once the option is configured, if there's a long stuck query the client will get a error signal from the server.
- provide a
queryTimeoutoption like JDBC and works in the way like most of the JDBC implementations
this will start a timer for each query and send specific vendor cancel requests if the request exceeds the configured timeout.
In fact the name queryTimeout is ambiguous, it's more like a requestTimeout. I suspect if anyone would use this unreliable mechanism in the high load production environment because nowadays the database server provide a statement execution timeout option, and there's cost setting a timer for every query request.
there's cost setting a timer for every query request.
Well the use case is that you have a default timeout set on the server, but then for a certain long-running query you want to increase the timeout just for that single query.
For example, you never want to wait a minute for a login query. But you might for a query which is rendering a complex report.
So having a single global timeout isn't sufficiently flexible.
APIs could be provided like
- setting execution timeout in server
private void configureSessionStatementExecutionTimeoutExample(int timeout, SqlConnection connection) {
connection.setStatementTimeout(timeout); // execution timeout
}
- cancel by the client
private void queryWithTimeoutExample(int timeout, SqlClient sqlClient) {
RequestOptions requestOptions = new RequestOptions();
requestOptions.setRequestTimeout(timeout);
sqlClient.query(requestOptions, "SELECT sleep(50)") // long query
.execute()
.onFailure(cancelSig -> {
// cancelled by the client
});
}
In this way the timeout could be configured at the query start and flexible for each timer
@gavinking there might be a configurable settings on the server to timeout a query, that would usually be configured by sysadmin
What we are explaining here is that usually protocols gives the opportunity to cancel a query from the client and the JDBC implementations for the query timeout usually set a timer locally and when the timer fires then JDBC implementations uses this API to cancel the query. This is what the JDBC PostgreSQL and MySQL implementation do.
I think what we can do is:
1/ provide a ability to cancel the current query in progress using a programmatic API like we do have now in PgConnection#cancelRequest
2/ have on the Query instance a timeout that will set a timer and use the connection query cancellation when the timer fires before the query resolves
In MSSQL ATTENTION message could be sent directly from the current session to the server to cancel the ongoing request, it's something like a out-of-band signal. https://docs.microsoft.com/en-us/openspecs/windows_protocols/ms-tds/dc28579f-49b1-4a78-9c5f-63fbda002d2e
@vietj OK, but as I tried to show above, it seems like the right way to do this with MySQL is using a query hint:
select /*+ max_execution_time(1000) */ * from information_schema.tables
https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html#optimizer-hints-execution-time
yes it might be.
I think however that this requires to rewrite and understand the user SQL, which is practically something client implementations try to avoid because it is opening a can of worms.
On 31 May 2020, at 15:46, Gavin King [email protected] wrote:
@vietj https://github.com/vietj OK, but as I tried to show above, it seems like the right way to do this with MySQL is using a query hint:
select /*+ max_execution_time(1000) */ * from information_schema.tables — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/eclipse-vertx/vertx-sql-client/issues/668#issuecomment-636473670, or unsubscribe https://github.com/notifications/unsubscribe-auth/AABXDCRKNZFBHMLSCH4BAFDRUJNTBANCNFSM4NOYCZ7Q.
I think however that this requires to rewrite and understand the user SQL
Well, I'm pretty sure this particular query hint gets inserted right after the very first non-comment token in the query (i.e. after select, update, delete, replace, or insert)... so really not very difficult.
Anyway, it's documented here.
which is practically something client implementations try to avoid because it is opening a can of worms.
I don't think that's right. Every JDBC and ODBC driver does significant rewriting of the user-written SQL, to process parameters, and to process the escape syntax.
I don't really understand why you guys think that the Vert.x client should be so different to other database drivers in this respect.
On 31 May 2020, at 16:19, Gavin King [email protected] wrote:
I think however that this requires to rewrite and understand the user SQL
Well, I'm pretty sure this particular query hint gets inserted right after the very first non-comment token in the query (i.e. after select, update, delete, replace, or insert)... so really not very difficult.
Anyway, it's documented here https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html#optimizer-hints-overview.
- here is how PosgreSQL is opening a new connection and cancelling a request in progress: https://github.com/pgjdbc/pgjdbc/blob/14576f4bca3a2484fd4f81a0d8276ae5cab9a419/pgjdbc/src/main/java/org/postgresql/core/QueryExecutorBase.java#L164 https://github.com/pgjdbc/pgjdbc/blob/14576f4bca3a2484fd4f81a0d8276ae5cab9a419/pgjdbc/src/main/java/org/postgresql/core/QueryExecutorBase.java#L164
- here is the same for MySQL : https://github.com/mysql/mysql-connector-j/blob/66459e9d39c8fd09767992bc592acd2053279be6/src/main/core-impl/java/com/mysql/cj/CancelQueryTaskImpl.java#L95
which is practically something client implementations try to avoid because it is opening a can of worms.
I don't think that's right. Every JDBC and ODBC driver does significant rewriting of the user-written SQL, to process parameters, and to process the escape syntax.
maybe, I haven't checked, however that is something I want to avoid in SQL client
I don't really understand why you guys think that the Vert.x client should be so different to other database drivers in this respect.
— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/eclipse-vertx/vertx-sql-client/issues/668#issuecomment-636477643, or unsubscribe https://github.com/notifications/unsubscribe-auth/AABXDCX5KZB74IOMBPSMI2DRUJRPTANCNFSM4NOYCZ7Q.