MySQL Bugs: #63601: Connection Timeout Parameter in MyODBC Connector ignored

Bug #63601	Connection Timeout Parameter in MyODBC Connector ignored
Submitted:	5 Dec 2011 12:06	Modified:	19 Jan 2012 11:00
Reporter:	Konrad Kleine	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	all	OS:	Any
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any
Tags:	asterisk, connector, ignored, ODBC, timeout

Description:
Consider the following setup:

An asterisk telephony server on host A wants to store call detail records (CDRs) after every call on a MySQL server on host B. To let A write to B a MyODBC is used on A.

Everything in this setup works fine except when the connection to host B, which runs the MySQL server is down. In this case the telephony sever (asterisk) hangs for 15 minutes before it recognizes that the MySQL server is unreachable.

Now, you might say this is an asterisk problem, but in fact it's not!

We've applied an ODBC connection timeout parameter. But it's not working. Instead the telephony server recognizes a timeout only after a 15 minutes long TCP expiration timeout.

We've tried to find the place where things went wrong and it looks like it's the way the MyODBC Connector deals with connection timeouts:

http://bazaar.launchpad.net/~myodbc-developers/myodbc/5.1/view/head:/driver/options.c#L271

As you can see in the code above, the parameter is silently ignored.

How to repeat:
Since the telephony system from the description is too complicated to recreate, I simply describe more abstractly how you can reproduce the problem.

1) Setup a host A and a host B
2) Run a MySQL server on host B
3) From host A connect to host B via a MyODBC Connector
4) Keep the ODBC connection running and remove the "cable" between host A and B
5) Try to run anything on the ODBC connection (it shouldn't work even if you've set any connection timeout.)

Suggested fix:
Not only parse the connection timeout parameter but also deal with it. (http://bazaar.launchpad.net/~myodbc-developers/myodbc/5.1/view/head:/driver/options.c#L271)

Since the telephony system or whatever system is using the MyODBC Connector will be unavailable for like 15 minutes, I've risen the severity for this ticket.

Hi Konrad,

Unfortunately, you are right, the connect timeout is not currently supported by MySQL ODBC driver.

Setting "Verified" status.

Konrad,

Sorry, we have had similar bugs a few times before that was a long time ago and I forgot the details, so my previous comment has to be disregarded.

MySQL ODBC driver uses MySQL client library code functions that provide connection and transportation services. The client library has the connect timeout option (MYSQL_OPT_CONNECT_TIMEOUT). However, the way it works falls under the description of LOGIN_TIMEOUT from ODBC specification point of view mostly because SQL_ATTR_CONNECTION_TIMEOUT can be set either before or after the connection is made. MySQL client functions support only BEFORE setting.

http://msdn.microsoft.com/en-us/library/ms713605%28v=VS.85%29.aspx

So, the workaround is to use the code similar to this:

/* First set the login timeout */
SQLSetConnectAttr(hdbc, SQL_ATTR_LOGIN_TIMEOUT, (SQLPOINTER)timeout, 0);

/* Then the connect attempt can be made */
SQLDriverConnect(...);

This behavior is not going to change in the near future.
So, the status for the bug is "Unsupported".

Little all-in-one CMake project to reproduce the bug.

Attachment: odbc-connection-timeout.tar.gz (application/x-gzip, text), 5.07 KiB.

Hi,

the suggest workaround doesn't work. 

I've attached a little all-in-one demo project where I've implemented the
workaround suggested in the previous post by Bogdan.

SETUP
=====

Consider Hosts A and B where B has an
alias-interface that A uses to communicate with B. (The alias-interface is for
fallback scenarios with another third host. The alias-interface is important
here whereas the third host is not).

Let's say A has an ODBC connection with DSN "voipstat101" to B via the alias
interface "myaliasIF". Here's the odbc.ini file on Host A:

[voipstat101]
Description	= MySQL RW connection to mydatabase on myserviceip
Driver		= MySQL
Database	= mydatabase
Server		= myaliasIF
User		= myuser
Password	= secret
Port		= 3306
ConnSettings	=

The ODBC driver config looks like this (odbcinst.ini):

[MySQL]
Description     = ODBC for MySQL
Driver          = /usr/lib64/libmyodbc5.so
Setup           = /usr/lib64/libodbcmyS.so
FileUsage       = 1

PROBLEM DESCRIPTION
===================

Suppose A connected to B via DSN "voipstat101" using my program. It'll stop after
executing some queries and waits for user-input to give you some time to take down
the "myaliasIF" interface on B: ifdown myaliasIF.

Press <Enter> in my running program and watch a simple INSERT statement wait
forever (>=10 Min) before crashing.

If you want, you can bring the "myaliasIF" back up and watch my program continue it's
work.

If you don't take down the "myaliasIF" interface my program will continue without
failure until finished.

So the problem is with MyODBC blocking and not returning from a query. Btw. the
query timeout also doesn't work, because - again - it's not implemented.

REPRODUCE
=========

As I've mentioned earlier, I've included a small tar-ball that contains everything
you need. Just install "odbc.ini" and "oddbcinst.ini" to /etc and setup a MySQL DB
on a different host using the "characters.sql" schema file.

Then run "cmake . && make && ./odbc-connection-timeout" to compile and run the 
program.

NOTE ABOUT THE MYODBC SOURCE CODE
=================================

In fact when grepping through the myodbc source code from trunk, one can see that
the SQL_ATTR_CONNECTION_TIMEOUT is not implemented:

kwk@balu:~/dev/myodbc-3.51$ grep "SQL_ATTR_CONNECTION_TIMEOUT" . -r --after-context=5
./driver/options.c:        case SQL_ATTR_CONNECTION_TIMEOUT:
./driver/options.c-            {
./driver/options.c-              /*
./driver/options.c-                We don't do anything with this, but we pretend that we did
./driver/options.c-                to be consistent with Microsoft SQL Server.
./driver/options.c-              */
--
./driver/options.c:        case SQL_ATTR_CONNECTION_TIMEOUT:
./driver/options.c-            /* We don't support this option, so it is always 0. */
./driver/options.c-            *((SQLUINTEGER *) ValuePtr)= 0;
./driver/options.c-            result= SQL_SUCCESS;
./driver/options.c-            break;
./driver/options.c-

WORKAROUND
==========

When it didn't work, I've added a second thread that monitors if the connection
is dead (via SQL_ATTR_CONNECTION_DEAD):

void monitorConnectionThread(void *data)
{
    unsigned int isConnectionDead = SQL_CD_FALSE;
    do {
        printf(">> Testing Connection (SQL_ATTR_CONNECTION_DEAD)...");
        /* This call internally invokes mysql_ping which itself is blocking... too bad */
        SQLGetConnectAttr(db, SQL_ATTR_CONNECTION_DEAD, (SQLPOINTER) &isConnectionDead, SQL_IS_UINTEGER, 0);
        printf("%s\n", (isConnectionDead==SQL_CD_TRUE ? "SQL_CD_TRUE" : "SQL_CD_FALSE"));
        /* Come back in 1 seconds and check again (if needed) */
        sleep(1);
    } while(isConnectionDead == SQL_CD_FALSE);
    printf("Connection DEAD\n");
    pthread_exit((void*)3);
}

ABOUT MY WORKAROUND ATTEMPT
===========================

It turned out that internally the test for SQL_ATTR_CONNECTION_DEAD itself uses
a command that blocks (I guess it's mysql_ping):

kleine@mrburns:~/dev/myodbc-3.51$ grep "SQL_ATTR_CONNECTION_DEAD" . -r --after-context=20
./driver/options.c:        case SQL_ATTR_CONNECTION_DEAD:
./driver/options.c-            {
./driver/options.c-                /*
./driver/options.c-                  We have to check for both CR_SERVER_LOST and 
./driver/options.c-                  CR_SERVER_GONE_ERROR to report about dead connections
./driver/options.c-                */
./driver/options.c-                if ( mysql_ping( &dbc->mysql ) && 
./driver/options.c-                     (mysql_errno( &dbc->mysql ) == CR_SERVER_LOST ||
./driver/options.c-                      mysql_errno( &dbc->mysql ) == CR_SERVER_GONE_ERROR) )
./driver/options.c-                    *((SQLUINTEGER *) ValuePtr)= SQL_CD_TRUE;
./driver/options.c-                else
./driver/options.c-                    *((SQLUINTEGER *) ValuePtr)= SQL_CD_FALSE;
./driver/options.c-            }
./driver/options.c-            break;

SUMMARY
=======

Blocking queries and crashing after >=10Min is a serious problem that should be
dealt with, no matter what the actual setup looks like. It would be nice to
be at least able to set a blocking timeout like you can do with sockets.

I vote for a list of ODBC features that are aren't supported by MyODBC/MySQL.
Is there any other place for such a list than comments in the sourcecode?

I really hope, you don't simply set this bug report to "unsupported" again.
At least it would be nice to know if there's any mechanism to avoid blocking
scenarios. A workaround by modifying my example code and posting a diff would be
really appreciated!

Thank you for your time.

Regards
- Konrad

Hi Konrad,

Thanks for providng more details and the test project.
Please note that in my previous message I explained why SQL_ATTR_CONNECTION_TIMEOUT is not implemented and not supported.

I will come back to you shortly after reviewing and running your test.

Konrad, you are right. That option does not work.
The actual problem is in mysqlclient library.
This is the relevant bug report for the patch and the problem:

http://bugs.mysql.com/bug.php?id=54790

The patch was committed into mysql version 5.6.3. Unfortunately, we cannot do anything till this version becomes GA, so we could use it for ODBC driver release.

Marking this bug as a duplicate of 54790.