Bug #57350 Connection timeout cannot be set, please add to connection string options
Submitted: 9 Oct 2010 20:16 Modified: 12 Jun 2013 7:29
Reporter: Bonny Gijzen Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51, 5.1 and 5.2 OS:Windows (any)
Assigned to: Assigned Account CPU Architecture:Any

[9 Oct 2010 20:16] Bonny Gijzen
Description:
Hi,

I have had a situation where the MySQL server was unreachable.
The connection timed out after about 30 seconds.

I have searched the options for a connect timeout but I can't find one.

Can you implement connect-timeout as a connection string option?
(You can use the connect-timeout from mysql_options() function)

How to repeat:
Try and connect to a non-existing database url.

Suggested fix:
Implement connect-timeout option in the connection string, using connect-timeout from mysql_options() function.
[9 Oct 2010 21:07] Bonny Gijzen
Seems it is related to this posting:
http://forums.mysql.com/read.php?37,269255,269255
[11 Oct 2010 6:13] Bogdan Degtyariov
Hi Bonny,

The following ODBC function call makes the driver to set the timeout using
mysql_options() with MYSQL_OPT_CONNECT_TIMEOUT parameter:

SQLSetConnectAttr(hdbc, SQL_ATTR_LOGIN_TIMEOUT, (SQLPOINTER)timeout, 0));

Setting the status "Not a bug".
[11 Oct 2010 19:56] Bonny Gijzen
Hi Bogdan,

I am using ADO components with the ODBC connector.
So I don't have a way to call the mysql_options() function.

So the only solution for me is to use the connection string and pass it as an option.

So I hereby mark this as a feature request and open again.
[12 Oct 2010 8:45] Tonci Grgin
Bogdan, I have to agree with Bonny here. This should be in DSN config screen, if possible.

Assigning Lawrin for final ruling.
[28 Oct 2010 6:03] Bonny Gijzen
Lawrin, any chance you can take a look at this?
It is getting serious for me.
[28 Oct 2010 6:34] Lawrenty Novitsky
Bonny, doesn't setting ConnectionTimeout property in a Connection object work for you? If it does not - then that is bug. As far as I understand that property defines the timeframe for connection establishing. Otherwise that is feature request. Reverting back to feature request so far.
[28 Oct 2010 7:30] Bonny Gijzen
Lawrin, you're amazing!
I didn't notice that option and for sure would not have expected it would work with the ODBC connector.

Ok, I will use this option and see if it improves my situation.
I will leave this submission as a feature request for the future.

Thanks for the insights.
[21 Mar 2011 11:23] Bonny Gijzen
Lawrin,

>Bonny, doesn't setting ConnectionTimeout property in a Connection object work for you?
It seems this propery does NOT set the timeout correctly.
If I try to connect to my server on a wrong port (3307 f.e.) then it times out after ~20 seconds, regardless of what I set for "ConnectionTimeout".

I upgraded to latest connector (5.1.18) but it made no difference.
So I hereby declare this as a bug.
[11 Apr 2011 6:14] Bonny Gijzen
Busy Lawrin?
Can you (or anyone) atleast confirm this bug exists?
I will not set it as critical (because for my app it atleast is).
[11 Apr 2011 10:05] Bonny Gijzen
Sorry typo:

I will now set it as critical (because for my app it atleast is).
[13 Apr 2011 11:23] Lawrenty Novitsky
Not so fast :)
I don't think that is a bug in the driver. And there is not much we can do about it in the driver, as it is not implemented in the driver and we only set the value for it. I don't think there is bug in the timeout implementation either, but that is other story.

Can you please attach here DM trace of what is going on when you set ConnectionTimeout property?
[13 Apr 2011 12:33] Bonny Gijzen
When I do this:

con.ConnectionTimeout:=98;  //98 seconds, as a test
con.Open();

I get this in the trace:

BiljartScoreBor 700-3b0	ENTER SQLSetConnectAttrW 
		SQLHDBC             03371A68
		SQLINTEGER                 103 <SQL_ATTR_LOGIN_TIMEOUT>
		SQLPOINTER          0x00000062
		SQLINTEGER                  -6 

BiljartScoreBor 700-3b0	EXIT  SQLSetConnectAttrW  with return code 0 (SQL_SUCCESS)
		SQLHDBC             03371A68
		SQLINTEGER                 103 <SQL_ATTR_LOGIN_TIMEOUT>
		SQLPOINTER          0x00000062 (BADMEM)
		SQLINTEGER                  -6 

I am not sure how to interpret this trace but I see one of the parameters to SQLSetConnectAttrW() is set to 62h (=98).

If you need more info (or other tests) let me know.
[13 Apr 2011 13:52] Bonny Gijzen
FYI: I have just tried with 3.51 and gives the same result.
I have made a small test app which makes testing easier, here is the result:

Trying to open dbase on port 3307 using MySQL ODBC 5.1 Driver ...
[MySQL][ODBC 5.1 Driver]Can't connect to MySQL server on 'db1.biljart-score.nl' (10060)
Duration: 21152 milliseconds
Done.

Regardless what I set for ConnectionTimeout, it will always be around 20 seconds before it times out.
It seems this is the standard TCP/IP timeout on my system, because a simple telnet connection to that IP/Port gives the same timeout.
[13 Apr 2011 20:54] Lawrenty Novitsky
my interpretation is that i'm 99,5% sure that everything's fine on c/odbc side. your trace is kinda proves that. if there is a bug, it is in libmysql then. but i still doubt there is bug their either. i don't really know what to do next. perhaps you should change bug category to libmysql(but first search for login timeout related bugs). or maybe wait till i am 100% sure that c/odbc is clean here.
[15 Apr 2011 9:27] Bonny Gijzen
Ok I get it.
The ODBC connector behaves as a wrapper code for libmysql.
And the debug trace I pasted shows us that the parameter is correctly given to the function SQLSetConnectAttrW() in libmysql.

If that is so then the problem lies in libmysql (or further down along its path) and not in the connector code itself.

Anyway, I will do some research on libmysql and wait for your confirmation that the connector code itself is clean.

(Is there some way to check the version of libmysql which is used on my pc?)
[15 Apr 2011 10:14] Bonny Gijzen
Lawrin,

Are you sure that SQL_ATTR_LOGIN_TIMEOUT is the parameter we need?
I know that there are other options aswell like MYSQL_OPT_READ_TIMEOUT (READTIMEOUT as connection parameter).

I supply this parameter aswell in my connection string but it doesn't show up anywhere in my ODBC trace. I suppose it should show up, shouldn't it?
[15 Apr 2011 10:39] Lawrenty Novitsky
libmysql is statically linked to the driver

you talked about timeout for connection establishing. then SQL_ATTR_LOGIN_TIMEOUT is the right thing.
[17 Apr 2011 15:18] Bonny Gijzen
I examined the sources and all should be OK:

ODBC:

BiljartScoreBor 700-3b0	EXIT  SQLSetConnectAttrW  with return code 0 (SQL_SUCCESS)
		SQLHDBC             03371A68
		SQLINTEGER                 103 <SQL_ATTR_LOGIN_TIMEOUT>
		SQLPOINTER          0x00000062 //timeout value
		SQLINTEGER                  -6 

ODBC Connector->options.c:

SQLRETURN SQL_API
MySQLSetConnectAttr(SQLHDBC hdbc, SQLINTEGER Attribute,
                    SQLPOINTER ValuePtr, SQLINTEGER StringLengthPtr)
{

        case SQL_ATTR_LOGIN_TIMEOUT:
            {
                /* we can't change timeout values in post connect state */
               if (is_connected(dbc)) {
                  return set_conn_error(dbc, MYERR_S1011, NULL, 0);
               }
               else
               {
                  dbc->login_timeout= (SQLUINTEGER)(SQLULEN)ValuePtr;
                  return SQL_SUCCESS;
               }
            }
            break;

ODBC Connector->connect.c:

if (dbc->login_timeout)
    mysql_options(mysql, MYSQL_OPT_CONNECT_TIMEOUT,
                  (char *)&dbc->login_timeout);

libmysql->client.c:

int STDCALL
mysql_options(MYSQL *mysql,enum mysql_option option, const void *arg)
{
  DBUG_ENTER("mysql_option");
  DBUG_PRINT("enter",("option: %d",(int) option));
  switch (option) {
  case MYSQL_OPT_CONNECT_TIMEOUT:
    mysql->options.connect_timeout= *(uint*) arg;
    break;

MYSQL * STDCALL 
CLI_MYSQL_REAL_CONNECT(MYSQL *mysql,const char *host, const char *user,
		       const char *passwd, const char *db,
		       uint port, const char *unix_socket,ulong client_flag)
{
      if (my_connect(sock, t_res->ai_addr, t_res->ai_addrlen,
                     mysql->options.connect_timeout))

So as I can see the path of the timeout value is fine, it should be passed to my_connect().

I did also see that the default value of connect_timeout is set to 20.
So it seems in my case that value isn't overwritten somehow.

Could you perhaps recompile the connector code and add some debug code so we can see where it fails?
[25 Oct 2011 18:47] Jasmin Letendre
I have the same problem. Is there any chance there will be a fix available in the short term?
[30 Jan 2012 13:49] Bonny Gijzen
Hi guys,

It has been a long time already since I initially posted this.
Can this please be fixed?

I already implemented a workaround in my code to first manually open a TCP/IP connection to that specific IP/PORT and if that works I then open the connection.

But I can see some shortcomings to this implementation so I prefer to have it properly fixed on the connector side.

I can help you supply any test data or tools, and I am willing to do whatever is needed to get you started on this.

Just let me know,

Bonny
[30 Oct 2012 9:24] Bonny Gijzen
Hi,

In connector 5.2.2 this bug still exists.
What's the reason for not fixing this?

If you need any more infos I would be glad to help.
[11 Jun 2013 8:48] Bogdan Degtyariov
This is a feature request, hence the severity is to be changed to S4. 

The reason for not implementing the connection timeout is that the ODBC connection timeout (SQL_ATTR_CONNECTION_TIMEOUT) is not applicable to MySQL Server and MySQL Network Protocol.

Therefore, you can only set the ODBC Login Timeout (SQL_ATTR_LOGIN_TIMEOUT).
[11 Jun 2013 9:10] Bonny Gijzen
Bogdan,

For me as a developer it is UNBELIEVABLE that such a basic feature (of handling a TCP/IP connection timeout) will not be implemented in the ODBC driver!

I have reported this limition almost 3 years ago (9 Oct 2010) and still nothing has been changed.
I have already written my own workaround which works like a charm, something like this:

if (Is_IP_Port_Open()==YES) {Do ODBC connect} else {Report status};

So I really don't need your help/support anymore, so don't bother as far as I am concerned.

Regards from a frustrated and confused user!
[11 Jun 2013 9:51] Bogdan Degtyariov
Bonny,

Thank you for your concern.
As I have already mentioned the functionality you required can be delivered through using of SQL_ATTR_LOGIN_TIMEOUT.

Please check this MSDN article, which tells the difference between SQL_ATTR_LOGIN_TIMEOUT and SQL_ATTR_CONNECTION_TIMEOUT:
http://msdn.microsoft.com/en-us/library/windows/desktop/ms713605%28v=vs.85%29.aspx

SQL_ATTR_CONNECTION_TIMEOUT (ODBC 3.0) - An SQLUINTEGER value corresponding to the number of seconds to wait for any request on the connection to complete before returning to the application.

SQL_ATTR_LOGIN_TIMEOUT (ODBC 1.0) - An SQLUINTEGER value corresponding to the number of seconds to wait for a login request to complete before returning to the application.
[11 Jun 2013 10:01] Bonny Gijzen
Hi Bogdan,

Thank you for your reply.
I am working with the ODBC Connector so I cannot use API calls.
Instead I have to use the ODBC connection string, and there seems to be no working parameter/option to set the connection timeout.

Regards Bonny,
[11 Jun 2013 10:10] Bogdan Degtyariov
Bonny,

Thanks for your reply.
Sorry for misunderstanding. We obviously misread the nature of your problem.
People often mix the login and the connect timeout in ODBC with those in MySQL server. So, it is about adding a new feature that would allow setting the login timeout through the connection string without writing any code.
[11 Jun 2013 10:25] Bonny Gijzen
Hi Bogdan,

I am sorry if my postings were confusing.

>So, it is about adding a new feature that would allow setting the login timeout through the connection string without writing any code.

Yes exactly.
I did some research and it seems the following options are used by other drivers it seems, so perhaps you can use one of them aswell:
connection timeout=  (http://www.connectionstrings.com/mysql)
connect-timeout=

However I tried both these options and they didn't do anything (as far as I could see).

Bonny,
[11 Jun 2013 12:15] Bogdan Degtyariov
Bony, 

we are re-checking this problem.
Might be also related to http://bugs.mysql.com/bug.php?id=36225 as libmysql issue.
[12 Jun 2013 6:24] Bogdan Degtyariov
VBS test case for command line

Attachment: bug57350.vbs (application/octet-stream, text), 553 bytes.

[12 Jun 2013 6:25] Bogdan Degtyariov
Error message showing the elapsed time since the connect attempt

Attachment: error_dialog_57350.png (image/png, text), 18.59 KiB.

[12 Jun 2013 7:05] Bogdan Degtyariov
Hi Bonny,

I have uploaded the VBS test case, which can be run from the command line as follows:

c:\Windows\SysWOW64\wscript.exe bug57350.vbs

Can you please try to run it in your system?
Before doing so please edit the line 15, put the driver's name (I used it as {MySQL ODBC 5.2 Driver} instead of the standard {MySQL ODBC 5.2w Driver}) and the SERVER parameter should point to some non-existing address.

As shown on the screenshot the time elapsed is almost exactly as defined in conn.ConnectionTimeout=3. You can change the timeout and the resulting waiting time should change as well.
[12 Jun 2013 7:15] Bonny Gijzen
Error message showing elapsed time on my system (=20 secs!)

Attachment: timeout1.bmp (image/bmp, text), 147.71 KiB.

[12 Jun 2013 7:29] Bonny Gijzen
Bogdan,

Using driver 5.2.2 I still had the timeout problem.
With driver 5.2.5 it seems to be working now, glad to see it fixed!

Regards Bonny,
[12 Jun 2013 9:08] Bogdan Degtyariov
Bonnie,

Thanks for your feedback.
The problem is present in Connector/ODBC 5.2.2 because it was linked using libmysqlclient 5.5, which had problems with setting the connection timeout.

The ODBC drivers 5.2.4 and 5.2.5 use libmysqlclient 5.6 where the problem was fixed.

Now we know that setting SQL_ATTR_LOGIN_TIMEOUT is working as expected in Connector/ODBC 5.2.5. Hence, we can proceed with adding the connection string and GUI dialog options.