title: FreeTDS and PyODBC Random Connection Disconnects from server
body: <p>For some reason, the following stack trace first shows up after roughly 16 hours or longer after starting the process and then shows up every hour or two after. I have other jobs on other servers that are daily, hourly, and every few minutes and they don't get this stack trace. The only difference I can see is this code has multiple jobs per scheduler whereas the others don't.</p>
  
  <p>Stack trace:  </p>
  
  <pre><code>Traceback (most recent call last):
    File "app.py", line 24, in _run_client
      h.run_hourly(start, end)
    File "/home/foo/anaconda2/lib/python2.7/site-packages/foo/client/bar/helper.py", line 153, in run_hourly
      _run(_HOURLY_REQUEST, frequency, start, end, duration)
    File "/home/foo/anaconda2/lib/python2.7/site-packages/foo/client/bar/helper.py", line 117, in _run
      bars = dr.get_bars(frequency)
    File "/home/foo/anaconda2/lib/python2.7/site-packages/foo/client/bar/data_requests.py", line 35, in get_bars
      df = pd.read_sql(query, _ENGINE, params=params)
    File "/home/foo/anaconda2/lib/python2.7/site-packages/pandas/io/sql.py", line 415, in read_sql
  chunksize=chunksize)
    File "/home/foo/anaconda2/lib/python2.7/site-packages/pandas/io/sql.py", line 1084, in read_query
      result = self.execute(*args)
    File "/home/foo/anaconda2/lib/python2.7/site-packages/pandas/io/sql.py", line 975, in execute
      return self.connectable.execute(*args, **kwargs)
    File "/home/foo/anaconda2/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2055, in execute
      return connection.execute(statement, *multiparams, **params)
    File "/home/foo/anaconda2/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 945, in execute
      return meth(self, multiparams, params)
    File "/home/foo/anaconda2/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection
      return connection._execute_clauseelement(self, multiparams, params)
    File "/home/foo/anaconda2/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
  compiled_sql, distilled_params
    File "/home/foo/anaconda2/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
  context)
    File "/home/foo/anaconda2/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1386, in _handle_dbapi_exception
      self._autorollback()
    File "/home/foo/anaconda2/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 824, in _autorollback
      self._root._rollback_impl()
    File "/home/foo/anaconda2/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 703, in _rollback_impl
      self._handle_dbapi_exception(e, None, None, None, None)
    File "/home/foo/anaconda2/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1315, in _handle_dbapi_exception
      exc_info
    File "/home/foo/anaconda2/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 202, in raise_from_cause
      reraise(type(exception), exception, tb=exc_tb, cause=cause)
    File "/home/foo/anaconda2/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 701, in _rollback_impl
      self.engine.dialect.do_rollback(self.connection)
    File "/home/foo/anaconda2/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 439, in do_rollback
      dbapi_connection.rollback()
  DBAPIError: (pyodbc.Error) ('08S01', '[08S01] [FreeTDS][SQL Server]Write to the server failed (20006) (SQLEndTran)')
  </code></pre>
  
  <p>The servers match in regards to the versions mentioned below.</p>
  
  <p>Versions:<br>
  OS: Ubuntu 16.04.1<br>
  FreeTDS Package Versions: freetds-common:0.91-6.1build1, tdsodbc:amd64:0.91-6.1build1
  PyODBC: 3.0.10<br>
  SQL Alchemy: 1.1.4</p>
  
  <p>TSQL Output:</p>
  
  <pre><code>Compile-time settings (established with the "configure" script)
                          Version: freetds v0.91
           freetds.conf directory: /etc/freetds
   MS db-lib source compatibility: no
      Sybase binary compatibility: yes
                    Thread safety: yes
                    iconv library: yes
                      TDS version: 4.2
                            iODBC: no
                         unixodbc: yes
            SSPI "trusted" logins: no
                         Kerberos: yes
  </code></pre>
  
  <p>Test code:</p>
  
  <p>app.py</p>
  
  <pre><code>from datetime import datetime, timedelta
  from dateutil import relativedelta
  import traceback
  
  from apscheduler.schedulers.blocking import BlockingScheduler
  from bar import helper as h
  
  
  def _run_client(resolution):
      try:
          if resolution == "hourly":
              # Had to create a temporary variable to make native datetimes
              t = datetime.utcnow() - timedelta(hours=1)
              end = datetime(t.year, t.month, t.day, t.hour)
              start = end - timedelta(hours=1)
              h.run_hourly(start, end)
          elif resolution == "daily":
              # Had to create a temporary variable to make native datetimes
              t = datetime.utcnow().date() - timedelta(days=1)
              end = datetime(t.year, t.month, t.day)
              start = end - timedelta(days=1)
              h.run_daily(start, end)
          else:
              # Had to create a temporary variable to make native datetimes
              t = datetime.utcnow().date().replace(
                  day=1) - relativedelta.relativedelta(months=1)
              end = datetime(t.year, t.month, t.day)
              start = end - relativedelta.relativedelta(months=1)
              h.run_monthly(start, end)
      except:
          print "Current run failed:\n%s" % traceback.format_exc()
  
  
  def _get_hourly_job(sched):
      args = ["hourly"]
      job = sched.add_job(_run_client, args=args, trigger="cron", hour="*", minute="0")
      return job
  
  
  def _get_daily_job(sched):
      args = ["daily"]
      job = sched.add_job(_run_client, args=args, trigger="cron", hour="4", minute="0")
      return job
  
  
  def _get_monthly_job(sched):
      args = ["monthly"]
      job = sched.add_job(_run_client, args=args, trigger="cron", day="1", hour="0", minute="0")
      return job
  
  if __name__ == "__main__":
      sched = BlockingScheduler()
      hourly_job = _get_hourly_job(sched)
      daily_job = _get_daily_job(sched)
      monthly_job = _get_monthly_job(sched)
  
      try:
          sched.start()
      except:
          # Remove the jobs from memory since they finished
          hourly_job.remove()
          daily_job.remove()
          monthly_job.remove()
  
          sched.shutdown()
  </code></pre>
  
  <p>helper.py</p>
  
  <pre><code>from datetime import timedelta
  
  import data_requests as dr
  
  _HOURLY_REQUEST = Foo()
  _HOURLY_REQUEST.resolution = "hourly"
  
  _DAILY_REQUEST = Foo()
  _DAILY_REQUEST.resolution = "daily"
  
  _MONTHLY_REQUEST = Foo()
  _MONTHLY_REQUEST.resolution = "monthly"
  
  
  def _run(request, frequency, start, end, duration):
      bars = dr.get_bars(frequency)
  
      if bars.empty: 
          return None
      print "Bars = %i" % len(bars)
  
  
  def run_daily(start, end):
      frequency = 86400
      duration = timedelta(hours=4)
      _run(_DAILY_REQUEST, frequency, start, end, duration)
  
  
  def run_hourly(start, end):
      frequency = 3600
      duration = timedelta(minutes=30)
      _run(_HOURLY_REQUEST, frequency, start, end, duration)
  
  
  def run_monthly(start, end):
      frequency = 1209600
      duration = timedelta(days=1)
      _run(_MONTHLY_REQUEST, frequency, start, end, duration)
  </code></pre>
  
  <p>data_requests.py</p>
  
  <pre><code>import pandas as pd
  from sqlalchemy import create_engine, exc
  from sqlalchemy.sql import text
  
  _DB = "mssql+pyodbc://foo@stg-foo:FooBar@stg-foo.database.secure.windows.net:1433/foo?driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so&amp;tds_version=7.2"
  _ENGINE = create_engine(_DB)
  
  
  def get_bars(frequency):
      query = text("""SELECT h.foo_id, s.id AS bar_id, u.timezone
                  FROM foo h
                  INNER JOIN bar s ON h.foo_id = s.foo_id
                  AND s.frequency &lt;= :frequency
                  INNER JOIN test u ON u.id = h.test_id""")
      params = {
          "frequency": int(frequency)
      }
  
      try:
          df = pd.read_sql(query, _ENGINE, params=params)
      except exc.DBAPIError, e:
          # If connection is invalid (e.g. database restarted) execute the query
          # again
          if e.connection_invalidated:
              df = pd.read_sql(query, _ENGINE, params=params)
          else:
              raise e
  
      return df
  </code></pre>
  
  <p>I tried pymssql, but that segfaults with the latest version (2.1.3, freetds version 1.00.9), whereas pyodbc doesn't. I think this is a driver issue, but am having issues trying to work around it.</p>
  
  <p>In addition, I followed SQL Alchemy's documentation on how to handle connection disconnects, but looking at the stack trace, it doesn't seem to be hitting the logic. Instead, it hits the else block and raises the error.</p>