Hello there, I'm using Sequel through Rom, and connecting to Oracle 11g. In production, we're running on Heroku and, after about an hour of idle time on our application, Heroku starts to timeout when running queries against the database. Prior to that idle time of > 1 hour, queries run fine. From the stacktrace I can grab (see below), it seems that the we're getting stuck when calling __execute in the OCI8 cursor.
I'm really struggling to address this, as it _appears_ that the connection is still considered a valid one by Sequel (and OCI8?) but when actually executing any query against that connection, it will timeout. This stack trace was pulled using rack-timeout, as otherwise Heroku hits the 30s timeout limit and just reports a timed out request, not the actual error. However, after that timeout, we also then see the second stack trace indicating that the connection is not actually connected to Oracle. Again, I'm not sure why Sequel will consider it a valid connection, if it is timing out/disconnected according to OCI8? Of note, we intercept the Sequel::DatabaseDisconnectError error and disconnect all Sequel connections at that point (using Rom#container.disconnect) but subsequent queries still timeout and the only recourse is to restart the Heroku dynos. I've come to the mailing list first to see if anyone has any suggestions/experience with this sort of thing in Oracle, so any and all feedback would be appreciated. Even if that means looking elsewhere to database configuration etc. Thanks in advance for any help, Andy Shipman ``` Feb 06 11:51:31 /app/vendor/bundle/ruby/2.2.0/gems/ruby-oci8-2.2.3/lib/oci8/cursor.rb:126:in `__execute' Feb 06 11:51:31 /app/vendor/bundle/ruby/2.2.0/gems/ruby-oci8-2.2.3/lib/oci8/cursor.rb:126:in `exec' Feb 06 11:51:31 /app/vendor/bundle/ruby/2.2.0/gems/ruby-oci8-2.2.3/lib/oci8/oci8.rb:276:in `exec_internal' Feb 06 11:51:31 /app/vendor/bundle/ruby/2.2.0/gems/ruby-oci8-2.2.3/lib/oci8/oci8.rb:267:in `exec' Feb 06 11:51:31 /app/vendor/bundle/ruby/2.2.0/gems/sequel-4.43.0/lib/sequel/adapters/oracle.rb:91:in `block (2 levels) in _execute' Feb 06 11:51:31 (eval):11:in `call' Feb 06 11:51:31 (eval):11:in `block (2 levels) in log_connection_yield' Feb 06 11:51:31 /app/vendor/bundle/ruby/2.2.0/gems/activesupport-4.2.7.1/lib/active_support/notifications.rb:164:in `block in instrument' Feb 06 11:51:31 /app/vendor/bundle/ruby/2.2.0/gems/activesupport-4.2.7.1/lib/active_support/notifications/instrumenter.rb:20:in `instrument' Feb 06 11:51:31 /app/vendor/bundle/ruby/2.2.0/gems/activesupport-4.2.7.1/lib/active_support/notifications.rb:164:in `instrument' Feb 06 11:51:31 (eval):5:in `block in log_connection_yield' Feb 06 11:51:31 /app/vendor/bundle/ruby/2.2.0/gems/sequel-4.43.0/lib/sequel/database/logging.rb:44:in `log_connection_yield' Feb 06 11:51:31 (eval):4:in `log_connection_yield' Feb 06 11:51:31 /app/vendor/bundle/ruby/2.2.0/gems/sequel-4.43.0/lib/sequel/adapters/oracle.rb:91:in `block in _execute' Feb 06 11:51:31 /app/vendor/bundle/ruby/2.2.0/gems/sequel-4.43.0/lib/sequel/database/connecting.rb:280:in `block in synchronize' Feb 06 11:51:31 /app/vendor/bundle/ruby/2.2.0/gems/sequel-4.43.0/lib/sequel/connection_pool/threaded.rb:107:in `hold' Feb 06 11:51:31 /app/vendor/bundle/ruby/2.2.0/gems/sequel-4.43.0/lib/sequel/database/connecting.rb:280:in `synchronize' Feb 06 11:51:31 /app/vendor/bundle/ruby/2.2.0/gems/sequel-4.43.0/lib/sequel/adapters/oracle.rb:82:in `_execute' Feb 06 11:51:31 /app/vendor/bundle/ruby/2.2.0/gems/sequel-4.43.0/lib/sequel/adapters/oracle.rb:67:in `execute' Feb 06 11:51:31 /app/vendor/bundle/ruby/2.2.0/gems/sequel-4.43.0/lib/sequel/dataset/actions.rb:1064:in `execute' Feb 06 11:51:31 /app/vendor/bundle/ruby/2.2.0/gems/sequel-4.43.0/lib/sequel/adapters/oracle.rb:364:in `fetch_rows' Feb 06 11:51:31 /app/vendor/bundle/ruby/2.2.0/gems/sequel-4.43.0/lib/sequel/dataset/actions.rb:149:in `each' Feb 06 11:51:31 /app/vendor/bundle/ruby/2.2.0/gems/sequel-4.43.0/lib/sequel/dataset/actions.rb:84:in `columns!' Feb 06 11:51:31 /app/vendor/bundle/ruby/2.2.0/gems/sequel-4.43.0/lib/sequel/dataset/actions.rb:74:in `columns' Feb 06 11:51:31 /app/vendor/bundle/ruby/2.2.0/gems/rom-sql-0.9.1/lib/rom/plugins/relation/sql/base_view.rb:16:in `block (2 levels) in inherited' ``` ``` Feb 06 12:02:05 2017-02-06 12:02:04 - Sequel::DatabaseDisconnectError - OCIError: ORA-03114: not connected to ORACLE: Feb 06 12:02:05 stmt.c:82:in oci8lib_220.so Feb 06 12:02:05 /app/vendor/bundle/ruby/2.2.0/gems/ruby-oci8-2.2.3/lib/oci8/cursor.rb:28:in `initialize' Feb 06 12:02:05 /app/vendor/bundle/ruby/2.2.0/gems/ruby-oci8-2.2.3/lib/oci8/oci8.rb:175:in `new' Feb 06 12:02:05 /app/vendor/bundle/ruby/2.2.0/gems/ruby-oci8-2.2.3/lib/oci8/oci8.rb:175:in `parse_internal' Feb 06 12:02:05 /app/vendor/bundle/ruby/2.2.0/gems/ruby-oci8-2.2.3/lib/oci8/oci8.rb:168:in `parse' Feb 06 12:02:05 /app/vendor/bundle/ruby/2.2.0/gems/ruby-oci8-2.2.3/lib/oci8/oci8.rb:275:in `exec_internal' Feb 06 12:02:05 /app/vendor/bundle/ruby/2.2.0/gems/ruby-oci8-2.2.3/lib/oci8/oci8.rb:267:in `exec' Feb 06 12:02:05 /app/vendor/bundle/ruby/2.2.0/gems/sequel-4.43.0/lib/sequel/adapters/oracle.rb:91:in `block (2 levels) in _execute' ``` -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
