Sequel::DatabaseDisconnectError: TinyTds::Error: Adaptive Server connection timed out

[wuarmin]

Hey,
I connect to a MSSQL server via Sequel for an ETL job and sporadically get this error.

Establish connection to db:

App.register_provider(:mssql) do
  prepare do
    require "sequel"
    require "logger"
  end

  start do
    db = Sequel.connect({
      adapter: "tinytds",
      dataserver: ENV["HOST"],
      port: ENV["PORT"],
      database: ENV["DB"],
      user: ENV["USER"],
      password: ENV["PASSWD"],
      :loggers => [Logger.new($stdout)],
    })
    register("mssql.db", db)
  end

  stop do
    App["mssql.db"].disconnect
  end
end

i.e. this code throws the error:

def all_filtered(params)
  db.fetch(query, params)
    .to_hash_groups(:number)
end

here's the backtrace:

/home/user/etl/shared/bundle/ruby/2.7.0/gems/sequel-5.56.0/lib/sequel/adapters/tinytds.rb:218:in `fields'
/home/user/etl/shared/bundle/ruby/2.7.0/gems/sequel-5.56.0/lib/sequel/adapters/tinytds.rb:218:in `block in fetch_rows'
/home/user/etl/shared/bundle/ruby/2.7.0/gems/sequel-5.56.0/lib/sequel/adapters/tinytds.rb:78:in `block in execute'
/home/user/etl/shared/bundle/ruby/2.7.0/gems/sequel-5.56.0/lib/sequel/connection_pool/threaded.rb:92:in `hold'
/home/user/etl/shared/bundle/ruby/2.7.0/gems/sequel-5.56.0/lib/sequel/database/connecting.rb:269:in `synchronize'
/home/user/etl/shared/bundle/ruby/2.7.0/gems/sequel-5.56.0/lib/sequel/adapters/tinytds.rb:47:in `execute'
/home/user/etl/shared/bundle/ruby/2.7.0/gems/sequel-5.56.0/lib/sequel/dataset/actions.rb:1093:in `execute'
/home/user/etl/shared/bundle/ruby/2.7.0/gems/sequel-5.56.0/lib/sequel/adapters/tinytds.rb:215:in `fetch_rows'
/home/user/etl/shared/bundle/ruby/2.7.0/gems/sequel-5.56.0/lib/sequel/dataset/actions.rb:152:in `each'
/home/user/etl/shared/bundle/ruby/2.7.0/gems/sequel-5.56.0/lib/sequel/dataset/actions.rb:850:in `public_send'
/home/user/etl/shared/bundle/ruby/2.7.0/gems/sequel-5.56.0/lib/sequel/dataset/actions.rb:850:in `to_hash_groups'
/home/user/etl/releases/20220707150907/lib/repositories/repo.rb:30:in `all'_filtered

Do you have a hint, what could be the reason, or, how I could workaround this? I haven't worked much with MSSQL before. Previously, I received this data from an Oracle DB, and never had any problems.

Thanks
wuarmin

[jeremyevans]

My guess would be that your query takes to long to return a response (tiny_tds default is 5 seconds: https://github.com/rails-sqlserver/tiny_tds#tinytdsclient-usage). You could try passing the :timeout database option to set a longer timeout.

[wuarmin]

Thank you for your help!