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

[10 Jan 2024 5:42] Jinseul Moon
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
[11 Jan 2024 10:11] MySQL Verification Team
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
[12 Jan 2024 9:48] Jinseul Moon
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."
[17 Jan 2024 6:12] Bogdan Degtyariov
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.