search

findmypast-oss / mssqlex

Microsoft SQL Server Adapter for Elixir
Apache License 2.0
42 stars 22 forks source link

Implement ping for better disconnection detection/recovery #1

Closed jbachhardie closed 7 years ago

jbachhardie commented 7 years ago

We should be using ping/1 to send the DB a test query (I think SELECT 1 would be good) to check connectivity, this would allow all connections in a pool, for example, to correctly disconnect and attempt to reconnect when connectivity to the database server is lost.

dideler commented 7 years ago

It seems like the ODBC driver already supports idle connection resiliency?

(Note this pinging behaviour is surpising for some users and could be undesirable for them.)

jbachhardie commented 7 years ago

You're right that pinging is potentially problematic, however idle connection resiliency is only supported by SQL Server 2014 and later while we look to support back to SQL Server 2008 by default.

How often the ping occurs can be configured by passing in the :idle_timeout option. Users may increase this value to decrease load on the database and I believe DBConnection even supports passing :infinity for the ping to never happen.

reddog commented 7 years ago

@dideler Thanks for referencing our issue.

@jbachhardie Useful information about the :idle_timout setting, thanks for that.

I think what we'd like to achieve is something along the lines of "auto-recovering persistent connections without the SELECT 1 load", if you see what I mean!

In our particular case, I believe that we're fine in establishing multiple, persistent connections, but the SELECT 1 ping is undesirable. There are cases where we might loose database connectivity (including if we don't do the repeated SELECT 1 pings).

Is there something straightforward or a common pattern that we could use to recover from dropped database connections (ie detect when they're dropped and re-connect at query time)?