MySQL Bugs: #69416: MySQL Connector/ODBC 5.2.5 - Command Timeout does not work

Bug #69416	MySQL Connector/ODBC 5.2.5 - Command Timeout does not work
Submitted:	6 Jun 2013 14:17	Modified:	22 Dec 2017 4:40
Reporter:	Amit Patkar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.2.5	OS:	Windows (Windows 2008 Server Standard Edition)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any
Tags:	ODBC command timeout

Description:
When I execute long running query, it does not timeout after configured query timeout / command timeout value. 
I am using MySQL Server version 5.5.11

If this parameter is not supported, what is work around so that control is returned to application without any results after pre-defined time or with exception.

How to repeat:
Create DSN using MySQL Connector ODBC 5.2.5
Connect to MySQL Database from application
Set CommandTimeout=30 seconds
Execute Following Query
select sleep(300);

This query should timeout after 30 seconds with timeout exception.

Please check for duplicate: http://bugs.mysql.com/bug.php?id=57350 . Thanks.

Hi Godofredo Miguel Solorzano

Bug mentioned by you is regardin ConnectionTimeout. I am referring to command timeout, which is applicable when I execute a query from my application.

If you look at last comment in #57350, that bug was unresolved till MyODBC 5.2.2

My application have to be killed to recover from this situation. One option is that I can implement worker thread for this and kill thread when application come across this situation. But I am not sure if same situation will not occur for subsequent queries executed.

Can this be fixed or suggest some workaround? Application execution should not get stuck.

Amit,

can you please explain how exactly you set the command timeout?
Are you using SQLSetStmtOption(..., SQL_QUERY_TIMEOUT, ...); ?
Or the timeout is set through ADODB.ODBCCommand?

Please sent a code sample if possible.
Thanks.

Please check this sample code.
I am using ADODB for this module.
Connection timeout and command time out value is set to ADOConenction object

--------------- Code Sample starts here
#import "C:\Program Files\Common Files\System\ado\Msado15.dll" \
        no_namespace \
        rename("EOF","adoEOF") rename ("BOF", "adoBOF")

---- Constructor used to initialize object
CConnection::CConnection()
{
    HRESULT hr;
    m_pConnection = NULL;
    hr = m_pConnection.CreateInstance(__uuidof( Connection ) );
}

----- Function used to open connection
void CConnection::Open(CString szDSN, CString szUID, CString szPwd)
{
    /*_variant_t*/ BSTR vDsn, vUid, vPwd;

    try
    {
        vDsn = szDSN.AllocSysString();
        vUid = szUID.AllocSysString();
        vPwd = szPwd.AllocSysString();

        m_pConnection->ConnectionTimeout = GetPrivateProfileInt ("ODBC", "connectiontimeout", 30, INITIALIZATION_FILE);;
        m_pConnection->Open(vDsn, vUid, vPwd, NULL);
        m_pConnection->CommandTimeout = GetPrivateProfileInt ("ODBC", "commandtimeout", 10, INITIALIZATION_FILE);
        ::SysFreeString(vPwd);
        ::SysFreeString(vUid);
        ::SysFreeString(vDsn);
    }
    catch (_com_error e)
    {
        ::SysFreeString(vPwd);
        ::SysFreeString(vUid);
        ::SysFreeString(vDsn);
        throw e;
    }
}

----- Constructor used to initialize Recordset / Dataset
CAdoRecordset::CAdoRecordset(CConnection *pConnection)
{
    vNull.vt = VT_ERROR;
    vNull.scode = DISP_E_PARAMNOTFOUND;

    HRESULT hr;
    m_pRecordset = NULL;
    hr = m_pRecordset.CreateInstance(__uuidof( Recordset ));
m_pRecordset->PutRefActiveConnection(pConnection->m_pConnection);
}

----- Function used to open query
void CAdoRecordset::Open(CString szSql, CursorTypeEnum enmCursor, LockTypeEnum Lock)
{
    /*_variant_t*/ BSTR sql;
    sql = szSql.AllocSysString();

    try
    {
        m_pRecordset->CursorLocation = adUseClient;
        //m_pRecordset->CursorLocation = adUseServer;
        m_pRecordset->Open(sql, vNull, enmCursor, Lock, adCmdText);
        ::SysFreeString(sql);
    }
    catch (_com_error e)
    {
        ::SysFreeString(sql);
        TRACE ("Error - %s\n", (LPTSTR) e.Description());
        throw e;
    }
}

---------------- Application Open recordset 
OpenQuery()
{
    CTsapiDevice * pDevice = NULL;

    try
    {
        rsExt = new CAdoRecordset (m_pApp->m_pDBThread->m_DBConnection);

        szSql.Format("select * from datatable");

        rsExt->Open(szSql);
    }
    ctach (...)
    {
        ///////// Error
    }
}
------------ code sample ends here 

I hope this information will help to resolve this issue.

Does anyone knows if CommandTimeout is implemented for MyODBC connector on Windows.

VB Test case

Attachment: bug69416.log (application/octet-stream, text), 34.18 KiB.

ODBC Trace

Attachment: bug69416.log (application/octet-stream, text), 34.18 KiB.

VB Test case

Attachment: bug69416.vbs (application/octet-stream, text), 539 bytes.

Hi Amit,

Thank you for reporting the bug and for providing the test case.
I made a small VB/ADO test, which reproduced the same behavior.
Basically, setting the command timeout for ADODB object comes down to SQL_ATTR_QUERY_TIMEOUT, which MySQL ODBC driver does not currently support.

This is a known issue and the severity of the current bug must be re-considered.
S1 corresponds to the complete loss of service without any workarounds. Therefore, I am setting S4 (Feature Request) as more appropriate to the situation.

Please note that all network communication and interacting with MySQL server is done through MySQL Client library in ODBC driver. So, the client library has to support the query timeouts.

Unfortunately, I cannot promise you the quick resolution of this problem.

Hi Bogdan Degtyariov,

Appreciate your efforts in verifying this bug.

For my application, this is causing lots of issues. Application goes into hang state as control does not return to application. We have to kill the application and start again. This results in loss of service. More than that, it takes some time to identify the situation. Sometimes hours.

I request you to reconsider the severity and implement this feature in client library.

Is there any workaround for this? I need query to timeout if no response comes after defined time.

e.g. If I define timeout as 30 sec and query execution takes more than 30 sec, control should return to application after 30 sec.

Once again appreciate your efforts to verify this issue. And please consider fixing this issue or please help me with workaround so that I can continue running services.

Hi Amit,

Thanks for your answer.
I understand your concern and the command timeout functionality is really needed in Connector/ODBC driver. In other MySQL Connectors like Connector/NET or Connector/J it has existed for years. In Oracle we cannot make any promises to do any particular bug fix or function by any date or release, but I will try include this request into the plan for the urgent fixing. Oracle developers try to attend to the community users when possible, but due to the limited resources we must give higher priority to the paying customers requests.

So, considering the critical situation for you I am giving it the severity S2 (serious) because there are workarounds for your problem.

There are two possible situations when the query execution hangs and does not return to the application:

1. The query is still being executed on the server

In this case you have to pay special attention to the query optimization and do not allow queries to take more than a pre-defined period of time. You can monitor the slow queries using the slow query server log and in this way find out what query needs to be optimized. Unfortunately, the MySQL server does not allow setting the maximum query execution time after which the query gets automatically aborted. However, monitoring the slow queries allows to reduce the number of times when the query execution has to be aborted using the KILL command. It is also not very hard to create an application thread (or a separate application), which would run SHOW PROCESSLIST command every 15-30 seconds and kill queries with the execution time longer than some number of seconds. When the query is killed the function will return with the error.

2. The network connection has problems and the application is waiting on network I/O to complete

The Connector/ODBC driver has special options just for such situations.
READTIMEOUT=N/WRITETIMEOUT=N define the number of seconds to complete the reading or writing operations from the network. Note that these options are not available in the driver GUI and have to be set through the connection string.
For instance, the following connection string defines 20 and 30 seconds for the reading and writing operations respectfully:

DRIVER={MySQL ODBC 5.2w Driver};SERVER=myserver;UID=myuser;PWD=*****;READTIMEOUT=20;WRITETIMEOUT=30;

You can also create the DSN using mysql-installer.exe command line utility and specify all needed options.

I hope this helps while the problem is being fixed.

The feature request for the implementation of server-side statement timeouts:
http://bugs.mysql.com/bug.php?id=68252

Hi Bogdan Degtyariov,

Thanks for this update and accommodating this change in release plan.

I will implement suggested workaround and check for improvement. 

Can you please let me know how paid jobs are done? If my company decide to fund implementation of command timeout for Connector/MySQL, how this can be taken ahead?

Regards
Amit Patkar

Hi Amit,

As I already indicated in my previous post the work for implementing the server query timeouts has been started.

The best way to have some new function implemented is to buy the MySQL support contract. As a paying customer you will be entitled to the number of premium features.

http://www.mysql.com/buy-mysql/

You can contact our Sales Team

http://www.mysql.com/about/contact/

or visit the Oracle Online shop:

https://shop.oracle.com/pls/ostore/product?p1=MySQL

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/ODBC 5.3.10 release, and here's the changelog entry:

  SQL query timeout (SQL_ATTR_QUERY_TIMEOUT) support was added.

Thank you for the detailed bug report.