Timeout keyword ignored in pyodbc.connect()

[ltoshea]

Hi all,
This is linked to an old closed issue but I wanted to re-raise.
Simply if a connection cannot be made to a DB, for example a firewall is blocking the attempt my script will just hang forever. I've looked around at the docs and stack overflow and a lot of people seem to be reporting the keyword doesn't work / is ignored.

For example:

try:
        conn = pyodbc.connect(CONN_STRING,timeout=5)
except:
        print "Connetion to Database timed out"
        sys.exit(1)

[mkleehammer]

The timeout isn't ignored -- it is used to set SQL_ATTR_LOGIN_TIMEOUT. There is also a SQL_ATTR_CONNECTION_TIMEOUT that could be set, so perhaps I should set it too.

The SQL_ATTR_CONNECTION_TIMEOUT affects all queries, not just connection attempts, so I was trying to keep them separate. Right now it is only set after connecting when you use cnxn.timeout=4.

I tested setting both before a login with SQL Server on Windows and PostgreSQL on OS X and neither worked. This seems to be a bug in the drivers.

I'm going to close this, but if you can find any information please reopen. I'm happy to put a fix in if there is one.

[kvikshaug]

From ODBC API docs on SQL_ATTR_LOGIN_TIMEOUT:

... the number of seconds to wait for a login request to complete before returning to the application.

I don't quite understand why this doesn't timeout for certain connection problems, e.g. firewall block like in @ltoshea s case, or when server sends no response. But it doesn't, and users who have to deal with unreliable connections will sometimes experience indefinite blocks, instead of the expected timeout.

This makes error handling difficult; I see also users struggling with this in #11.

I replaced LOGIN with CONNECTION attribute in this block, which did successfully timeout for me testing with FreeTDS against a server which accepts the TCP connection but never responds.

My suggestion would be to expose both SQL_ATTR_LOGIN_TIMEOUT and SQL_ATTR_CONNECTION_TIMEOUT attributes, perhaps by adding a connection_timeout argument to pyodbc.connect. This way pyodbc doesn't have to make this choice for the user.

Another suggestion might be to also set SQL_ATTR_CONNECTION_TIMEOUT during login, but reset it afterwards, keeping the old behaviour of setting cnxn.timeout=4 so that this doesn't affect queries.

What do you think @mkleehammer ?

[nullco]

Im stuck with this also, any workaround for the moment? Default connection timeout seems to be too high and my entire app hangs cause of this

Thanks

[andreiroman]

Hi,

What worked for me was getting the latest version of pyodbc from github(not through pip install pyodbc) and using attrs_before dictionary argument for the connect function, just like this:

SQL_ATTR_CONNECTION_TIMEOUT = 113
login_timeout = 1
connection_timeout = 3
connection = pyodbc.connect(connection_string, timeout=login_timeout, attrs_before={SQL_ATTR_CONNECTION_TIMEOUT : connection_timeout})

That 113 can be found in sqlext.h file(my path: /usr/include/sqlext.h).
Note that by using this dictionary you are able to change many more settings.

If you encounter a problem with "PyInt_AsInt" you can change it to "PyInt_AsLong", like i did here: andreiroman@3a5c1ad

[piyushiitg]

Hi @andreiroman,

Can you tell me which pyodbc version you have tested "attrs_before". Currently I am using 3.0.7 and your workaround is not working for us.

Thanks

[andreiroman]

Hello @piyushiitg,

Version: 3.0.11b16(latest version). You could follow these steps to get it:

1. uninstall pyodbc
2. download https://github.com/mkleehammer/pyodbc and extract
3. navigate to the new folder and do "pip install ./pyodbc"

Hope it helps!

[piyushiitg]

Hi @andreiroman

If I set SQL_ATTR_CONNECTION_TIMEOUT this variable inside below block. Is there any issue. Because after setting this my timeout issue is solved.

if (timeout > 0)
{
Py_BEGIN_ALLOW_THREADS
ret = SQLSetConnectAttr(hdbc, SQL_ATTR_LOGIN_TIMEOUT, (SQLPOINTER)timeout, SQL_IS_UINTEGER);
ret = SQLSetConnectAttr(hdbc, SQL_ATTR_CONNECTION_TIMEOUT, (SQLPOINTER)timeout, SQL_IS_UINTEGER);
Py_END_ALLOW_THREADS
if (!SQL_SUCCEEDED(ret))
RaiseErrorFromHandle("SQLSetConnectAttr(SQL_ATTR_LOGIN_TIMEOUT)", hdbc, SQL_NULL_HANDLE);
}

Thanks
PIyush

[andreiroman]

Hi @piyushiitg,

There shouldn't be any problem at all for now, but it would be a good idea to keep an eye on this module until the next version is available via pip install and you can use the attrs_before then.

[allen-munsch]

Hung process using pyodbc.

info :

python2.7/site-packages/pyodbc-4.0.22-py2.7-macosx-10.13-intel.egg

before :

pyodbc.connect(cnxn_string, timeout=2)

debug :

sudo bash -c 'source /Users/stuff-intern1/.zshrc && dtruss -a python pystuff/loaders/sql.py'

...

12908/0x7a9cb:     39548       4      1 close_nocancel(0x3)      = 0 0
12908/0x7a9cb:     39591      16     12 socketpair(0x1, 0x1, 0x0)        = 0 0
12908/0x7a9cb:     39650      32     27 socket(0x2, 0x1, 0x0)        = 5 0
12908/0x7a9cb:     39664       8      3 setsockopt(0x5, 0xFFFF, 0x8)         = 0 0
12908/0x7a9cb:     39666       4      0 setsockopt(0x5, 0xFFFF, 0x1022)      = 0 0
12908/0x7a9cb:     39667       3      0 setsockopt(0x5, 0x6, 0x1)        = 0 0
12908/0x7a9cb:     39690       4      1 ioctl(0x5, 0x8004667E, 0x7FFEE561FCA4)       = 0 0
12908/0x7a9cb:     39791      97     93 connect(0x5, 0x7F902F425670, 0x10)       = -1 Err#36

with info from this thread:

    SQL_ATTR_CONNECTION_TIMEOUT = 113
    login_timeout = 1
    connection_timeout = 1
    connection = pyodbc.connect(cnxn_string, 
                                timeout=login_timeout, 
                                attrs_before={
                                SQL_ATTR_CONNECTION_TIMEOUT: connection_timeout})

After:

python pystuff/loaders/sql.py
('08S01', u'[08S01] [FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist (20009) (SQLDriverConnect)')

This was through pip install. Looks like the attrs_before stuff works great.

Thanks.

[mkleehammer]

Thanks for the update. I'm going to reopen this and apply the timeout to CONNECTION in connect automatically. It would make sense for the connection timeout to be applied while connecting and the login only after connecting and sending login credentials.

Be aware that the timeout is, I believe, also applied to getting results from queries. If you set it to 2 seconds and have a query that takes 4, you'll get an error instead of results. You might want to reset the timeout using something like cnxn.timeout=4 afterwards.

I think I'll set the attribute if passed to the connect function, but then revert it to its previous value when the connect completes. I think people would expect it to apply to the login, but not to queries afterwards.

[jerrycl]

Actually - there's not much information in this thread about what drivers are being used. Recently I had a patch accepted at FreeTDS which fixes a bug with the SQL_ATTR_LOGIN_TIMEOUT value. The driver was storing it, but not using it.

So if (1) you're using FreeTDS, and (2) you install the latest 'master', this shouldn't be a problem anymore.

[yevgeniyclaudio]

Hi! I have pyodbc 4.0.23, and timeout on LOGIN is not working! Tried to use "attrs_before" - same result(( Is it work only with pyodbc 3.0.11b? The problem is very actual, loses sense of taste great pyodbc! Can anyone help me!

[v-chojas]

@yevgeniyclaudio What driver are you using? Please describe your environment in more detail, as explained above it may be the ODBC driver which is not recognising the setting of timeout.

[yevgeniyclaudio]

v-chojas, thanks for reply! I have Microsoft Win Server 2003 R2, python 2.7.3 32x, driver SQL Server Native Client 10.0, pyodbc-4.0.23! Pyodbc built with VCForPython27.msi! I take only pyodbc.pyd from /build/lib.win32-2.7/ and put it in /Python27/lib/ . Maybe I have some more files copy to python/lib directory from /build? Did install py pip, shows version 4.0.23 but timeout not work

[alexnbferreira]

Hello,

Has this issue been resolved? I've been using pyodbc on both MySQL and SQL Servers and it appears the timeout attribute on the connect method is always ignored.

More info:

• Tested both on Windows 10 64bit and Windows Server 2016
• SQL Server driver: ODBC Driver 13 for SQL Server
• MySQL driver: MySQL ODBC 8.0 Unicode Driver
• Python 3.7 64-bit
• Using pydobc version with included fix for execute fastmany included in Fix for issue 450 #467

[v-chojas]

You can post an ODBC trace for more information.

[ernestomi]

Im looking forward to the resolution! :)

In the meantime, as an alternative to the SQL_ATTR_CONNECTION_TIMEOUT solution kindly proposed by @allen-munsch and @andreiroman, it is also possible to modify the connection via SQL after you open it

SET statement_timeout = 10000

[alexnbferreira]

Im looking forward to the resolution! :)

In the meantime, as an alternative to the SQL_ATTR_CONNECTION_TIMEOUT solution kindly proposed by @allen-munsch and @andreiroman, it is also possible to modify the connection via SQL after you open it

SET statement_timeout = 10000

Hi ernesto, I believe the timeout being discussed here is the login timeout and not the statement execution timeout. The first is the time the driver waits while it's trying to connect to eht DB, the second is the time it waits for a given command to finish executing.

[ernestomi]

Im looking forward to the resolution! :)
In the meantime, as an alternative to the SQL_ATTR_CONNECTION_TIMEOUT solution kindly proposed by @allen-munsch and @andreiroman, it is also possible to modify the connection via SQL after you open it
SET statement_timeout = 10000

Hi ernesto, I believe the timeout being discussed here is the login timeout and not the statement execution timeout. The first is the time the driver waits while it's trying to connect to eht DB, the second is the time it waits for a given command to finish executing.

Thanks Alex,

My understanding from the above was that indeed the original issue was reported for login timeouts but that the ticket is now about applying SQL_ATTR_CONNECTION_TIMEOUT through pyodbc's interface. I also understood that SQL_ATTR_CONNECTION_TIMEOUT is intended to be a timeout for the connection (in any query), which if true would solve the issue that I have of not being able to set connection timeouts for the psqlodbc driver. This is why I suggested a workaround.

SQL_ATTR_CONNECTION_TIMEOUT affects all queries, not just connection attempts, so I was trying to keep them separate.

Please do excuse if I have misunderstood the intent of the thread and ignore my above comment. I was just trying to contribute.

[swarnveer]

Hello Guys,

I was stuck with this timeout thing too so somehow reached here. I've tried the workaround mentioned here.

And I am using this to connect:

SQL_ATTR_CONNECTION_TIMEOUT = 113

cnxn = pyodbc.connect(CONNECTION_STRING,timeout=1,attrs_before={SQL_ATTR_CONNECTION_TIMEOUT : 1})

However, still for some connections timeout is not working as expected.

Timeout worked correctly when exception was:

('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]Invalid connection. (14) (SQLDriverConnect); [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Invalid Instance()). (14); [08001] [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute (0)')

However, the program took too long to respond(ignoring timeout) when below error is encountered:

('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. (17) (SQLDriverConnect); [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). (53); [08001] [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute (0)')

My pyodbc version is '4.0.30'

Can anyone help me with the workaround for this ?

[v-chojas]

That's a very old ODBC driver, many bugs have been fixed since. The fact that it's warning about invalid connection string attribute also suggests you should check if the connection string is valid for it. If you're connecting to SQL Server, try the latest Microsoft ODBC Driver for SQL Server.

[swarnveer]

Actually I know the connection won't get established because of port issue in the server. The idea here is I am connecting hundreds of SQL server through my script and some may not get connected. I just don't want it to spend more than 1 second if connection is not getting established in that time. That's why I need timeout variable working for each case.

[swarnveer]

Actually I know the connection won't get established because of port issue in the server. The idea here is I am connecting hundreds of SQL server through my script and some may not get connected. I just don't want it to spend more than 1 second if connection is not getting established in that time. That's why I need timeout variable working for each case.

I am not concerned why connection is not getting established. I am concerned about the working of timeout.

[swarnveer]

I updated latest ODBC driver and timeout issue still persists:

('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53]. (53) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (53)')

[v-chojas]

What is in the connection string? "Invalid connection string attribute" suggests you may have something else incorrect there.

If you set the login timeout that low, it may be taking long inside OS, and only when it returns can the driver check and return the timeout.

[swarnveer]

Is there a workaround for this ? What is the lowest possible timeout that can work ?

There is nothing wrong with the connection string. Its with the destination server, port is not opened for remote connections as I have already mentioned earlier.

[v-chojas]

It depends on various factors such as OS version and configuration.

There is nothing wrong with the connection string.

The "Invalid connection string attribute" warning clearly shows otherwise.

[swarnveer]

SQL_ATTR_CONNECTION_TIMEOUT = 113
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=XX.XXX.X.XXX;PORT=1433;DATABASE=master;UID=XXXX;PWD=XXXX;',
                                      timeout=1,attrs_before={SQL_ATTR_CONNECTION_TIMEOUT : 1})

('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53]. (53) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (53)')

Reason I said connection string is fine because I said I am connection hundreds of servers in a loops and it is working for many

[v-chojas]

PORT is not a valid connection string keyword, and neither is timeout,
Please see the documentation: https://docs.microsoft.com/en-us/sql/connect/odbc/dsn-connection-string-attribute

[swarnveer]

Timeout is not used as a part of connection string. It is used as a parameter to connect method of pyodbc.

[keitherskine]

@swarnveer , I'm not exactly sure what the fundamental issue is with the connection timeouts, but here are a few thoughts:

• On the pyodbc.connect() call, it shouldn't be necessary to set both the timeout parameter and the attrs_before parameter with {SQL_ATTR_CONNECTION_TIMEOUT : 1}. Those two parameters do exactly the same thing. Providing just the timeout parameter should suffice (for the connection that is, executing queries with a timeout is a different matter).
• When the connection attempt times out (whatever the timeout value is), pyodbc will raise a pyodbc.OperationalError error. That can't be avoided.
• If the pyodbc error message includes "Login timeout expired", then that shows the connection attempt timed out. As far as I can tell, there doesn't seem to be any other way of detecting a timeout.
• Setting a timeout value does not mean the connection attempt will timeout after exactly the specified number of seconds. There is inevitably lag, so the connection may take a few more seconds to actually give up the attempt. The minimum value you can set the timeout to is one second, but in that case you shouldn't expect connections to timeout immediately after one second.
• Be aware of connection pooling which may be a factor here. This is where, under the hood, closed connections are not actually closed but left open for re-use (this is the default behavior, but it can be switched off if you wish). This probably doesn't impact your scenario but it's worth knowing about.

[alexnbferreira]

The problem here is that the timeout parameter is completely ignored by the driver when connecting. You can try 1 second, 3 seconds, 60 seconds, 300 seconds, it doesn't matter: if the connection is bound to timeout, it will always happen after around 60 seconds.

I just accepted the problem and got used to it. Haven't used the driver in more than a year but the problem has been happening for a few years already...

[keitherskine]

Yes, that's true, @alexnbferreira . Unfortunately, if the driver doesn't support timeouts, there's not much pyodbc can do about that. In my tests with the SQL Server "ODBC Driver 17 for SQL Server" driver, setting the timeout parameter did make a difference to how long it took before the connection attempt failed, so the timeout parameter does work in some cases.

[rayrapetyan]

"ODBC Driver for SQL Server" (both 13 and latest 17) doesn't have any timeout capabilities built in. So if you're using e.g. turbodbc, you're completely out of luck.

However, if you use pyodbc, you can use "timeout" param:

pyodbc.connect('conn_str', timeout=X)

and it will "simulate" mssql LoginTimeout behavior (same built into e.g. PHP driver and described here: https://docs.microsoft.com/en-us/sql/connect/php/connection-options)

[keitherskine]

Closed due to inactivity.

[cklat]

If it's fine I'd like to reopen this issue because I'm struggling with the timeout as well.

My setup ist the following:

Windows 11
ODBC Driver 17
pyodbc@4.0.39

What I'm trying to achieve is to let pyodbc let the OperationalError too throw much earlier when it cannot establish a connection to the mssql db.

No matter how I'm trying to set a timeout value, it seems to be ignored.
pyodbc tries to establish a connection for at least 30 seconds (didn't do any time stoppage to be honest), although I might set the timeout value to 1 second. This also, I believe, cannot be a matter of lag.

Based on the information in this issue I'm not quite sure if I should change the driver for my goal or not.

Can anybody help to clarify what the situation with the timeout parameter is nowadays?

Thanks!

[v-chojas]

I assume you're using ODBC Driver 17 for SQL Server, since this topic is driver-specific. Which timeout are you using, login timeout or connection timeout?

[cklat]

Sorry for being imprecise but yes, that's correct. ODBC Driver 17 for SQL Server

I wasn't aware of there being a login timeout and a connection timeout.

I'm trying to set the timeout that eventually leads me to a ODBC Driver 17 for SQL Server pyodbc OperationalError with the message:

'08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53]. (53) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (53)'

I'm trying to set the timeout according to the documentation (https://github.com/mkleehammer/pyodbc/wiki/The-pyodbc-Module#other-parameters) in the .connect() function with the parameter the following way:

pyodbc.connect(CONNECTION_STRING, timeout=1)

but this does not seem to work.

Granted, I do not know which of either of the timeouts I'm setting with this parameter. Therefore, this might be the wrong way.

[v-chojas]

See https://github.com/mkleehammer/pyodbc/blob/7b4e27781c7f1b75995ecca73d3018c929f4adef/src/connection.cpp#L1071

That's connection timeout, not login timeout. You'll need to use attrs_before with SQL_ATTR_LOGIN_TIMEOUT if you want to set the login timeout.

[cklat]

Thank you!

At some point, I was going this way as well but immediately dumped it because it wasn't working. But since this seems to be the way, I would appreciate if you could be help making it work.

So going this way, I'm connecting with the following statement:

pyodbc.connect(CONNECTION_STRING, attrs_before={'SQL_ATTR_LOGIN_TIMEOUT': 1})

which gives me the following error:

TypeError: Attribute dictionary keys must be integers

Setting SQL_ATTR_LOGIN_TIMEOUT without the quotes is not working as SQL_ATTR_LOGIN_TIMEOUT is not defined.

[v-chojas]

Use 103.

[cklat]

I'm sorry but I'm not following you. What do you mean by 103 and how to use it?

[v-chojas]

SQL_ATTR_LOGIN_TIMEOUT is attribute number 103. Take that in context of the error message you received.

[cklat]

I'm afraid that this solution doesn't help either.

That's how I changed the code line to for establishing the connection with your suggestion:

pyodbc.connect(CONNECTION_STRING, attrs_before={103: 1})

If that's the way how it should be implemented I don't see any change regarding the time passing until the error is thrown.
It still takes at least 30 sec or so. In some occassions even more than 60 or 70 secs. Very weird.

Might there be any other problem that I'm not aware of?

[v-chojas]

Check ODBC trace to see whether it is setting the attribute correctly.
You can also use strace to see where it is spending the time.

[Deepcuts]

My ugly solution to the timeout problem is checking with sockets

    import socket
    import customtkinter as ctk

    def so_something_with_sql(self):
        server = '192.168.100.210'
        port = 1433
        conn_str = (
            'DRIVER={SQL Server};'
            'SERVER=192.168.100.210\\SQLEXPRESS;'
            'DATABASE=somedatabase;'
            'UID=user;'
            'PWD=password'
        )
        def is_server_reachable(host, port, timeout=2):
            try:
                with socket.create_connection((host, port), timeout):
                    return True
            except (socket.timeout, ConnectionRefusedError, OSError):
                return False
        if not is_server_reachable(server, port):
            self.error_label.configure(text="Could not connect to SQL server", text_color="red")
            return
       # continue with the code in case we got connected