RailsSpawnMethod and DB connections

[Josh French]

I've run into an issue with RailsSpawnMethod and multiple connections
when using the ruby-oci8 driver. Each new process re-uses the existing
connection, and somehow this is triggering internal errors in our
Oracle DB. If I set RailsSpawnMethod to conservative, the errors are
eliminated; but then we can't take advantage of quicker spawn times or
RubyEE.

I've been in touch with the author of the ruby-oci8 driver and he
wasn't sure if this could be fixed within the driver itself; he
suggested "the connection should be closed before spawning a process
and a new connection should be established for the new process." Is it
possible to open a new connection per process without resorting to
RailsSpawnMethod = conservative?

Thanks,
Josh

[Hongli Lai]

His suggestion is correct, the fix should be in the application, not the driver.

There's no "post-fork" hook right now, but one thing that you could do
is to close the connection at the end of environment.rb, then every
time you need the database you can re-establish the connection if you
notice that the connection is closed.

--
Phusion | The Computer Science Company

Web: http://www.phusion.nl/
E-mail: in...@phusion.nl
Chamber of commerce no: 08173483 (The Netherlands)

[Josh French]

Thanks for the tip, we're now able to use the default spawn method
without error!

Much obliged,
Josh

On Dec 21, 8:30 am, "Hongli Lai" <hongli...@gmail.com> wrote:

> E-mail: i...@phusion.nl

[Cynthia Kiser]

Quoting Josh French <jo...@digitalpulp.com>:
>
> Thanks for the tip, we're now able to use the default spawn method
> without error!

Would you mind posting your code back to the list. Right now I am
using my legacy Oracle database read-only from my Rails apps (hosted
with mongrel and with Passenger). I haven't seen any problems -
probably because I am not writing to Oracle - but was considering
building a Rails app that reads and writes to that database.

(And, by the way, what version of Oracle are you using and what were
the errors you were seeing?)
--
Cynthia Kiser
c...@caltech.edu

[Josh French]

Hi Cynthia,

We're connecting with read-only access to an Oracle 10g RAC cluster,
further details of which I would have to ask our DBA about. Our issue
occurs when two long-running queries overlap, e.g. a second page
request begins a query before an earlier, independent request is done
talking to the database. This would reliably trigger an ORA-03106,
"fatal two-task communication protocol error" which I am told is an
internal database error.

As mentioned, the error is caused by a single connection getting
cached and re-used for new processes. This may in fact be due to the
way our application initializes itself -- we need to connect to
multiple databases, so I've got the app pre-loading some abstract
classes that merely handle various connections. (This way concrete
descendants can inherit the appropriate connection when they
eventually get instantiated.)

So, given that we're loading this class ahead of time:

class Oracle < ActiveRecord::Base
self.abstract_class = true
establish_connection "oracle_#{RAILS_ENV}"
end

The solution was to add the following line to the very end of
environment.rb:

Oracle.connection.disconnect!

We're using the oracle-enhanced adapter, which provides automatic
reconnection if it detects the connection has been dropped; I do not
believe the regular Oracle adapter provides this. Because of this we
have not needed to do any additional checking to make sure the Oracle
connection is re-established when needed -- each new process
automatically establishes a new connection, and we have been unable to
reproduce the error since.

j

On Dec 23, 10:15 pm, Cynthia Kiser <c...@caltech.edu> wrote: