I have a lua script, running on a RHEL7 host, which has been written to connect to a MS SQL Server database (using FreeTDS / unixODBC) and retrieve a value. The script generally works OK, however, if the target server is offline/unavailable, it will just hang (for approximately 6 minutes 20 seconds), before something eventually times out and an error is returned. Want I want is to get this timeout down to a couple of seconds...
I feel like this should be simple to do, but I can't seem to figure out how to specify/enforce a connection timeout to the SQL Server.
There is a timeout configuration in the /etc/freetds.conf (see below), but this seems to have no effect.
The script is normally called from nginx (openresty), which is the main reason I want to keep the timeout low, however the same hanging/timeout behaviour is observed running the script independently.
Can anyone help me solve this? I've included the script and the contents of relevant related config files below.
UPDATE: I noticed something worth mentioning during further testing - if I point my test script at an unused IP address OUTSIDE of the same subnet, my timeout when attempting to do the SQL server connection is ~6 minutes 21 seconds (as previously mentioned). If I point it at an unused IP address in the same subnet though, my timeout is consistently ~9 seconds. I'm no networking expert, but this suggests the 2 timeouts I'm actually seeing here are influenced by the network/transport layer - perhaps my local network switch is simply dropping the packets to the unknown subnet?
This doesn't solve my original problem, but thought it was worth mentioning as an update...
getvalue.lua:
local odbc = require "odbc"
local keyvalue = "some_value"
local retval = ""
CNN_DRV = {
{Driver='{FreeTDS}'};
{Server='10.10.60.100,1433'};
{Database='databasename'};
{Uid='sqlusername'};
{Pwd='sqlpassword'};
};
dbassert = odbc.assert
env,err = odbc.environment()
local cnn, err = env:driverconnect(CNN_DRV)
stmt,err = cnn:prepare("{?= call dbo.GetRetValForKeyValue(?)}")
ret = stmt:vbind_param_ulong(1, ret, odbc.PARAM_OUTPUT)
val = stmt:vbind_param_char(2, keyvalue)
dbassert(stmt:execute())
stmt:foreach(function(f1)
if tonumber(f1)
then
retval = string.format("%d", f1)
else
retval = ''
end
end
)
print(retval)
/etc/odbcinst.ini:
[FreeTDS]
Description = FreeTDS TDS driver (for Sybase/MS SQL)
Driver = /usr/lib64/libtdsodbc.so.0
Setup = /usr/lib64/libtdsS.so.2
/etc/freetds.conf:
# Global settings are overridden by those in a database
# server specific section
[global]
# TDS protocol version
; tds version = 4.2
# Whether to write a TDSDUMP file for diagnostic purposes
# (setting this to /tmp is insecure on a multi-user system)
; dump file = /tmp/freetds.log
; debug flags = 0xffff
# Command and connection timeouts
; timeout = 10
; connect timeout = 10
# If you get out-of-memory errors, it may mean that your client
# is trying to allocate a huge buffer for a TEXT field.
# Try setting 'text size' to a more reasonable limit
text size = 64512
