Thanks for the tips; I'm trying the connection_validator as that is the only immediate option for me.
Is there a way to log that extension in action? I'd like to check it is working as expected (i.e. I've set it up correctly, not that Sequel is working correctly :) On Monday, 6 February 2017 15:53:04 UTC, Jeremy Evans wrote: > > 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.
