Does somebody know something about this Sequel::DatabaseDisconnectError: TinyTds::Error

[wuarmin]

Hey 👋

I'm running a Hanami 2.2 app using Sequel 5.93.0 with TinyTDS to connect to SQL Server. Occasionally I get:

Sequel::DatabaseDisconnectError: TinyTds::Error: failed dbsqlsend() function
	/home/user/bundle/ruby/3.4.0/gems/sequel-5.93.0/lib/sequel/adapters/tinytds.rb:74:in 'TinyTds::Client#execute'
	/home/user/bundle/ruby/3.4.0/gems/sequel-5.93.0/lib/sequel/adapters/tinytds.rb:74:in 'block (2 levels) in Sequel::TinyTDS::Database#execute'
	/home/user/bundle/ruby/3.4.0/gems/sequel-5.93.0/lib/sequel/extensions/error_sql.rb:63:in 'Sequel::ErrorSQL#log_connection_yield'
	/home/user/bundle/ruby/3.4.0/gems/rom-sql-3.7.0/lib/rom/plugins/relation/sql/instrumentation.rb:83:in 'block (2 levels) in define_log_connection_yield'
	/home/user/bundle/ruby/3.4.0/gems/dry-monitor-1.0.1/lib/dry/monitor/clock.rb:15:in 'Dry::Monitor::Clock#measure'
	/home/user/bundle/ruby/3.4.0/gems/dry-monitor-1.0.1/lib/dry/monitor/notifications.rb:29:in 'Dry::Monitor::Notifications#instrument'
	/home/user/bundle/ruby/3.4.0/gems/rom-sql-3.7.0/lib/rom/plugins/relation/sql/instrumentation.rb:82:in 'block in define_log_connection_yield'
	/home/user/bundle/ruby/3.4.0/gems/sequel-5.93.0/lib/sequel/adapters/tinytds.rb:73:in 'block in Sequel::TinyTDS::Database#execute'
	/home/user/bundle/ruby/3.4.0/gems/sequel-5.93.0/lib/sequel/connection_pool/timed_queue.rb:90:in 'Sequel::TimedQueueConnectionPool#hold'
	/home/user/bundle/ruby/3.4.0/gems/sequel-5.93.0/lib/sequel/database/connecting.rb:283:in 'Sequel::Database#synchronize'
	/home/user/bundle/ruby/3.4.0/gems/sequel-5.93.0/lib/sequel/adapters/tinytds.rb:47:in 'Sequel::TinyTDS::Database#execute'
	/home/user/bundle/ruby/3.4.0/gems/sequel-5.93.0/lib/sequel/dataset/actions.rb:1197:in 'Sequel::Dataset#execute'
	/home/user/bundle/ruby/3.4.0/gems/sequel-5.93.0/lib/sequel/adapters/tinytds.rb:215:in 'Sequel::TinyTDS::Dataset#fetch_rows'
	/home/user/bundle/ruby/3.4.0/gems/sequel-5.93.0/lib/sequel/dataset/actions.rb:164:in 'Sequel::Dataset#each'
	/home/user/bundle/ruby/3.4.0/gems/sequel-5.93.0/lib/sequel/dataset/actions.rb:474:in 'Enumerable#map'
	/home/user/bundle/ruby/3.4.0/gems/sequel-5.93.0/lib/sequel/dataset/actions.rb:474:in 'Sequel::Dataset#map'
	/home/user/app/slices/davinci_api/repos/db_booking_repo.rb:20:in 'Enumerator#each'
	/home/user/app/slices/davinci_api/repos/db_booking_repo.rb:20:in 'Enumerable#group_by'
	/home/user/app/slices/davinci_api/repos/db_booking_repo.rb:20:in 'DavinciAPI::Repos::DBBookingRepo#deserialize_bookings'
	/home/user/app/slices/davinci_api/repos/db_booking_repo.rb:14:in 'DavinciAPI::Repos::DBBookingRepo#all_filtered'
	/home/user/app/slices/davinci_api/repos/booking_repo.rb:33:in 'DavinciAPI::Repos::BookingRepo#all_filtered'
	/home/user/app/slices/inhouse_api/repos/base_api_repo.rb:16:in 'block in InhouseAPI::Repos::BaseAPIRepo#all_filtered'
	/home/user/app/slices/inhouse_api/repos/base_api_repo.rb:15:in 'Array#each'
	/home/user/app/slices/inhouse_api/repos/base_api_repo.rb:15:in 'Enumerable#reduce'
	/home/user/app/slices/inhouse_api/repos/base_api_repo.rb:15:in 'InhouseAPI::Repos::BaseAPIRepo#all_filtered'
	/home/user/bundle/ruby/3.4.0/gems/dry-monads-1.9.0/lib/dry/monads/do.rb:131:in 'block in all_filtered'
	/home/user/bundle/ruby/3.4.0/gems/dry-monads-1.9.0/lib/dry/monads/do/mixin.rb:40:in 'Dry::Monads::Do::Mixin#call'
	/home/user/bundle/ruby/3.4.0/gems/dry-monads-1.9.0/lib/dry/monads/do.rb:131:in 'all_filtered'
	/home/user/app/slices/inhouse_api/actions/bookings/index.rb:39:in 'InhouseAPI::Actions::Bookings::Index#filter_bookings'
	/home/user/app/slices/inhouse_api/actions/bookings/index.rb:28:in 'InhouseAPI::Actions::Bookings::Index#handle'
	/home/user/bundle/ruby/3.4.0/gems/hanami-controller-2.2.0/lib/hanami/action.rb:329:in 'block in Hanami::Action#call'
	/home/user/bundle/ruby/3.4.0/gems/hanami-controller-2.2.0/lib/hanami/action.rb:310:in 'Kernel#catch'
	/home/user/bundle/ruby/3.4.0/gems/hanami-controller-2.2.0/lib/hanami/action.rb:310:in 'Hanami::Action#call'
	/home/user/bundle/ruby/3.4.0/gems/hanami-2.2.1/lib/hanami/slice/routing/resolver.rb:78:in 'block in Hanami::Slice::Routing::Resolver#resolve_slice_action'
	/home/user/bundle/ruby/3.4.0/gems/hanami-router-2.2.0/lib/hanami/router.rb:109:in 'Hanami::Router#call'
	/home/user/bundle/ruby/3.4.0/gems/hanami-router-2.2.0/lib/hanami/middleware/body_parser.rb:43:in 'Hanami::Middleware::BodyParser#call'
	/home/user/app/slices/inhouse_api/middlewares/auth.rb:15:in 'InhouseAPI::Middlewares::Auth#call'
	/home/user/bundle/ruby/3.4.0/gems/hanami-router-2.2.0/lib/hanami/middleware/body_parser.rb:43:in 'Hanami::Middleware::BodyParser#call'
	/home/user/bundle/ruby/3.4.0/gems/hanami-router-2.2.0/lib/hanami/middleware/body_parser.rb:43:in 'Hanami::Middleware::BodyParser#call'
	/home/user/bundle/ruby/3.4.0/gems/hanami-router-2.2.0/lib/hanami/middleware/body_parser.rb:43:in 'Hanami::Middleware::BodyParser#call'
	/home/user/bundle/ruby/3.4.0/gems/hanami-router-2.2.0/lib/hanami/middleware/body_parser.rb:43:in 'Hanami::Middleware::BodyParser#call'
	/home/user/bundle/ruby/3.4.0/gems/hanami-router-2.2.0/lib/hanami/middleware/body_parser.rb:43:in 'Hanami::Middleware::BodyParser#call'
	/home/user/bundle/ruby/3.4.0/gems/hanami-router-2.2.0/lib/hanami/middleware/body_parser.rb:43:in 'Hanami::Middleware::BodyParser#call'
	/home/user/bundle/ruby/3.4.0/gems/rack-2.2.17/lib/rack/method_override.rb:24:in 'Rack::MethodOverride#call'
	/home/user/bundle/ruby/3.4.0/gems/hanami-2.2.1/lib/hanami/middleware/render_errors.rb:57:in 'Hanami::Middleware::RenderErrors#call'
	/home/user/bundle/ruby/3.4.0/gems/dry-monitor-1.0.1/lib/dry/monitor/rack/middleware.rb:36:in 'block in Dry::Monitor::Rack::Middleware#call'
	/home/user/bundle/ruby/3.4.0/gems/dry-monitor-1.0.1/lib/dry/monitor/clock.rb:15:in 'Dry::Monitor::Clock#measure'
	/home/user/bundle/ruby/3.4.0/gems/dry-monitor-1.0.1/lib/dry/monitor/rack/middleware.rb:36:in 'Dry::Monitor::Rack::Middleware#call'
	/home/user/bundle/ruby/3.4.0/gems/hanami-router-2.2.0/lib/hanami/middleware/app.rb:40:in 'Hanami::Middleware::App#call'
	/home/user/bundle/ruby/3.4.0/gems/hanami-2.2.1/lib/hanami/slice.rb:791:in 'Hanami::Slice::ClassMethods#call'
	/home/user/bundle/ruby/3.4.0/gems/puma-6.6.0/lib/puma/configuration.rb:279:in 'Puma::Configuration::ConfigMiddleware#call'
	/home/user/bundle/ruby/3.4.0/gems/puma-6.6.0/lib/puma/request.rb:99:in 'block in Puma::Request#handle_request'
	/home/user/bundle/ruby/3.4.0/gems/puma-6.6.0/lib/puma/thread_pool.rb:390:in 'Puma::ThreadPool#with_force_shutdown'
	/home/user/bundle/ruby/3.4.0/gems/puma-6.6.0/lib/puma/request.rb:98:in 'Puma::Request#handle_request'
	/home/user/bundle/ruby/3.4.0/gems/puma-6.6.0/lib/puma/server.rb:472:in 'Puma::Server#process_client'
	/home/user/bundle/ruby/3.4.0/gems/puma-6.6.0/lib/puma/server.rb:254:in 'block in Puma::Server#run'

It usually happens during a query in one of my repos after the app has been idle for a while. My DB and network seem fine otherwise.

What could cause this error, and how can I prevent or handle it in Sequel/TinyTDS?

Thanks

[jeremyevans]

This happens because the socket is no longer valid. Sequel correctly recognizes this condition as a disconnect, and removes the connection from the pool. However, the error is still raised, because it is not a condition that can automatically be retried safely.

The best way to fix this is to ensure that idle database sockets are not automatically disconnected. If that is not possible, you can use the connection_validator extension (https://sequel.jeremyevans.net/rdoc-plugins/files/lib/sequel/extensions/connection_validator_rb.html). Be sure to read the extension documentation and configure an appropriate connection validation timeout.

[wuarmin]

Thank you @jeremyevans , great answer.