On Monday, February 6, 2017 at 7:45:59 AM UTC-8, Andy Shipman wrote: > > 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' > ``` >
The best way to fix this type of thing is to fix the underlying issue, and don't have Oracle automatically drop inactive connections. If that is not possible, you could always just try to use the connection more often than every hour (cron job or something similar). If you can't do either, you should probably look at the connection_validator extension (http://sequel.jeremyevans.net/rdoc-plugins/files/lib/sequel/extensions/connection_validator_rb.html). Thanks, Jeremy -- 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.
