MS SQL connection fails using GHC but not GHCi

[jnschaeffer]

I'm attempting to connect to a MS SQL Server database over a VPN using hdbc-odbc. Everything works fine when run in ghci, or through runhaskell. However, compiling a program in ghc and running it produces the following error upon calling connectODBC:

SqlError {seState = "[\"08S01\",\"08001\"]", seNativeError = -1, seErrorMsg = "connectODBC/sqlDriverConnect: [\"20009: [unixODBC][FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist\",\"0: [unixODBC][FreeTDS][SQL Server]Unable to connect to data source\"]"}

Looking around it appears that the error itself means the program is failing to connect at all to the SQL server, at least according to the FreeTDS user guide: http://www.freetds.org/userguide/confirminstall.htm

I tried connecting to the server using ODBC in C and it worked, and through tsql and isql as well. I wasn't able to check the tests in hdbc-odbc/testsrc as they just returned an error:

Testbasics.hs:141:8: Not in scope: `catch'

I'm not sure what's wrong, but given that the connection works in GHCi, tsql, and in hand-written C code my guess is it has something to do with FFI and the point at which connectODBC actually talks to the ODBC API.

As a side note, I've tested this against hdbc-odbc 2.3.1.1 and 2.3.1.0, and both have the same issue. Connecting to SQLite works as well.

Update: Using ghci with -fno-ghci-sandbox also causes the connection to fail. Not sure why.

[dcoutts]

Update: Using ghci with -fno-ghci-sandbox also causes the connection to fail. Not sure why.

Interesting. Ok, try as a workaround, running your app inside a forkIO from within main. Basically so it's not running on a bound thread.

[lseppala]

@jnschaeffer Did you ever find a solution that worked? I'm running into the same problem and would be thrilled with a viable workaround.

[lseppala]

I found a workaround using runInUnboundedThread from Control.Concurrency and the threaded runtime, i.e., runInUnboundedThread $ connectODBC connectionString and compiling with -threaded.

This is sufficient to prevent the connection error, but I'm not sure it's the minimal fix. I'm also unsatisfied with my understanding why this fixes the problem, and I'm hesitant to use it considering the note on appropriate use for runInUnboundedThread.

So, if anyone has a better fix, I'd be glad to hear it.

[jnschaeffer]

@lseppala That's basically what I did. In my case, I decided to use Control.Concurrent.Async (documented here) with my main defined as follows:

main :: IO ()
main = do
    a1 <- async main_
    wait a1

The actual logic for the program resides in main_. My best guess is that the ODBC connection ties up whatever thread it's on, or some low-level function the ODBC library calls can't run because the thread is already bound. It's definitely a bug (or at least poorly documented behavior), but an extra import and some code at the level of the main function is pretty painless as workarounds go.

I'd recommend using Control.Concurrent.Async for this rather than runInUnboundedThread, not least because it's made for asynchronous I/O operations as opposed to low-level thread juggling.

[lseppala]

I've figured out the root cause for this error. I'm documenting here for my own satisfaction and for anyone else who comes across this hair-pulling issue.

The SQLDriverConnect function in FreeTDS doesn't properly handle system call interrupts caused by the frequent SIGVTALRM signals generated by the Haskell runtime. My understanding is that only bound threads, such as a complied main, emit SIGVTALRM. Unbound threads—created by GHCi, runInUnboundedThread, and async—do not. Thus, spinning up the connection in a unbound thread allows it to complete.

I proved this 'SIGVTALRM' hypothesis by compiling with -rtsopts and executing an unfixed binary with +RTS -V0, which turns off all timers and alarms. connectODBC will then reliably complete without error. Running with -V0 is definitely not recommended beyond debugging—you'll lose other runtime functionality when timers are disabled.

Interestingly, it seems that this bug is pretty transient. Running an unfixed binary with strace only caused it to error some of the time. Using Network Link Conditioner to artificially slow down my network (thus making the connection take longer) also caused it to succeed most of the time. Maybe historical network speeds have masked this bug until now.

Bryan O'Sullivan has a blog post about an identical issue with a MySQL library. I've dug in a little bit to FreeTDS (what a bear!) to see if I could identify the exact bug, but I doubt I'll get very far. Perhaps a patch like Bryan's would do for now, but a) it's not platform independent, and b) this project doesn't seem to be accepting pull requests (at least recently).