MySQL Bugs: #113604: SQLSetStmtAttr and SQL_ATTR_QUERY_TIMEOUT does not work in 8.x ODBC Connector

Bug #113604	SQLSetStmtAttr and SQL_ATTR_QUERY_TIMEOUT does not work in 8.x ODBC Connector
Submitted:	10 Jan 2024 5:42	Modified:	12 Jan 2024 9:48
Reporter:	Jinseul Moon	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	5.3, 8.0, 8.1	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
I can see that SQL query timeout (SQL_ATTR_QUERY_TIMEOUT) support was added in MySQL Connector/ODBC 5.3.10 release note.
https://dev.mysql.com/blog-archive/mysql-connectorodbc-5-3-10-has-been-released/

However, I found that it does not actually work as expected.

I verified my code by testing with SQL server and it worked fine.

I also found one more way to make a timeout with the below query
 "SET SESSION innodb_lock_wait_timeout=XXX"

but, wondering why SQL_ATTR_QUERY_TIMEOUT setting does not work.
Is it a defect or any prerequisite to use?

I expect this timeout is for a database round-trip call.

Please let me know if I got something wrong or any solution for this.

How to repeat:
1. Make a simple C++ application using ODBC function
2. Call SQLSetStmtAttr to set SQL_ATTR_QUERY_TIMEOUT to a certain value
3. Run the application
4. Check if it works

Hello Jinseul,

Please note that on the server side the Connector/ODBC sets @@max_execution_time variable, which is subject to certain limitations:
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_execution_...
It applies to SELECT only, not applicable to stored procedure calls etc.
ODBC driver set this server variable internally because there is no apparent connection between the ODBC attribute SQL_ATTR_QUERY_TIMEOUT and MySQL server @@max_execution_time.

Regards,
Ashwini Patil

Thanks for your quick support.

Does it mean that the server side setting by @@max_execution_time takes precedence over the client side setting by SQL_ATTR_QUERY_TIMEOUT?

SQL_ATTR_QUERY_TIMEOUT didn't work not only for select statement also, delete one.

Could you also please explain more details about the last statement?
"ODBC driver set this server variable internally because there is no apparent connection between the ODBC attribute SQL_ATTR_QUERY_TIMEOUT and MySQL server @@max_execution_time."

SQL_ATTR_QUERY_TIMEOUT on the client side is implemented via setting @@max_execution_time on the server side.

As Ashwini mentioned, @@max_execution_time will only affect SELECT statements. The DELETE statements will take as long as it runs by design.

"ODBC driver set this server variable internally because there is no apparent connection between the ODBC attribute SQL_ATTR_QUERY_TIMEOUT and MySQL server @@max_execution_time."

The above means that SQL_ATTR_QUERY_TIMEOUT uses @@max_execution_time to enforce the timeout, but this fact is not documented. Though it could be found out by examining the server logs.