ORA-12537: TNS:connection closed

[Mattia]

Hi everyone,

I'm having this annoying problem (both with the latest 1.1.9 and with
the previous 1.1.8 version):
/!\ FAILSAFE /!\ Thu Jan 08 14:56:28 +0900 2009
Status: 500 Internal Server Error
ORA-12537: TNS:connection closed
env.c:257:in oci8lib.so
/opt/ruby-enterprise-1.8.6-20080810/lib/ruby/gems/1.8/gems/ruby-
oci8-1.0.3/lib/oci8.rb:229:in `initialize'
.../activerecord-oracle_enhanced-adapter-1.1.9/lib/active_record/
connection_adapters/oracle_enhanced_adapter.rb:988:in `new'
.../activerecord-oracle_enhanced-adapter-1.1.9/lib/active_record/
connection_adapters/oracle_enhanced_adapter.rb:988:in `new_connection'
.../activerecord-oracle_enhanced-adapter-1.1.9/lib/active_record/
connection_adapters/oracle_enhanced_adapter.rb:1025:in `initialize'
.../activerecord-oracle_enhanced-adapter-1.1.9/lib/active_record/
connection_adapters/oracle_enhanced_adapter.rb:52:in `new'
.../activerecord-oracle_enhanced-adapter-1.1.9/lib/active_record/
connection_adapters/oracle_enhanced_adapter.rb:52:in
`oracle_enhanced_connection'
.../activerecord/lib/active_record/connection_adapters/abstract/
connection_pool.rb:215:in `send'
.../activerecord/lib/active_record/connection_adapters/abstract/
connection_pool.rb:215:in `new_connection'
(I trimmed parts of the path in the hope to make it more readable but
ggroups will wrap it anyway I).

It's intermittent, I can work a little bit until the connection goes
bang and the time between having to restart the application to refresh
the connection varies, couldn't see any pattern.
It's not a network issue as I have sql+ open all the time with no
connection issues.
This is the latest entry in sqlnet.log:

***********************************************************************
Fatal NI connect error 12537, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.x.x.x)(PORT=1521))
(CONNECT_DATA=(SID=MYSID)(SERVER=DEDICATED)(CID=(PROGRAM=railsapp)
(HOST=xxx)(USER=me))))

VERSION INFORMATION:
TNS for Linux: Version 10.2.0.1.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 10.2.0.1.0 -
Production
Time: 08-JAN-2009 14:56:28
Tracing not turned on.
Tns error struct:
ns main err code: 12537
TNS-12537: TNS:connection closed
ns secondary err code: 12560
nt main err code: 507
TNS-00507: Connection closed
nt secondary err code: 0

tnsping-ing the SID is fine too. I'm using instantclient (Client
Shared Library 32-bit - 10.2.0.4.0).
Also, the rails application is running under apache+mod_rails, rails
is 2.2.0 and as you can see above I'M using ruby enterprise.

Has anyone experienced anything like this?

Thanks
-- mattia

[Raimonds Simanovskis]

Which version of mod_rails (or now mod_passenger) you are using?
And do you use RailsSpawnMethod=smart or conservative ?

If you use RailsSpawnMethod=smart (which means that Rails application
is loaded in memory and after that forked into several worker
processes to save memory using Ruby EE copy-on-write feature) then I
found the following suggestion in Passenger discussion forums (http://
groups.google.com/group/phusion-passenger/browse_thread/thread/
b00d4770bc1d00b8):

Add at the end of environment.rb
ActiveRecord::Base.connection.disconnect!

The other option is to try RailsSpawnMethod=conservative (if you have
enough memory and do not need to use Ruby EE memory saving
enhancements).

In this way you will force to disconnect from database before forking
and therefore ensure that each child worker process will open new
database connection.
So maybe this is the issue in your case. I have not yet tried
Passenger for large volume applications therefore I am just
guessing :)

Raimonds

[Mattia]

Hi

On Jan 9, 2:03 am, Raimonds Simanovskis

<raimonds.simanovs...@gmail.com> wrote:
> Which version of mod_rails (or now mod_passenger) you are using?
> And do you use RailsSpawnMethod=smart or conservative ?

2.0.3 and smart. Now that you mentioned I upgraded to 2.0.6 (although
I can't see any relevant change in the release announcements) to give
it a go.

> If you use RailsSpawnMethod=smart (which means that Rails application
> is loaded in memory and after that forked into several worker
> processes to save memory using Ruby EE copy-on-write feature) then I
> found the following suggestion in Passenger discussion forums (http://
> groups.google.com/group/phusion-passenger/browse_thread/thread/
> b00d4770bc1d00b8):
>
> Add at the end of environment.rb
> ActiveRecord::Base.connection.disconnect!
>
> The other option is to try RailsSpawnMethod=conservative (if you have
> enough memory and do not need to use Ruby EE memory saving
> enhancements).
>
> In this way you will force to disconnect from database before forking
> and therefore ensure that each child worker process will open new
> database connection.
> So maybe this is the issue in your case. I have not yet tried
> Passenger for large volume applications therefore I am just
> guessing :)

Thanks for the pointers, I'll try the suggestions one by one and see
if I can reproduce the issue. For now I'll just see if mod_rails 2.0.6
makes any improvement in this area then move on with AR tricks or the
spawn method.
I'll post the results.
BTW: I'm not really running a large application, actually I'm just
doing some development but still I get those disconnections.

One more question: isn't the enhanced adapter supposed to (silently?)
reconnect on this kind of failures?

Thanks
-- mattia

[Mattia]

On Jan 9, 10:06 am, Mattia <malat...@gmail.com> wrote:
...

> > In this way you will force to disconnect from database before forking
> > and therefore ensure that each child worker process will open new
> > database connection.
> > So maybe this is the issue in your case. I have not yet tried
> > Passenger for large volume applications therefore I am just
> > guessing :)
>
> Thanks for the pointers, I'll try the suggestions one by one and see
> if I can reproduce the issue. For now I'll just see if mod_rails 2.0.6
> makes any improvement in this area then move on with AR tricks or the
> spawn method.
> I'll post the results.
> BTW: I'm not really running a large application, actually I'm just
> doing some development but still I get those disconnections.

errr... unfortunately none of them is working.
I'm currently running with
RailsSpawnMethod conservative
_and_ the AR disconnect trick in my environment and I still can see
disconnections.
I can try running it (i.e.: doing my development) with mongrel and see
if the same thing happens there. Or, if you think it might help
pointing in the right direction, I could run with ruby MRI.

Otherwise, do you have any other suggestion on how to get useful
information about the disconnection?

thanks
-- mattia

[Raimonds Simanovskis]

Based on Oracle error messages that you posted (TNS-12537, TNS-00507)
it seems that the issue is in communications between Oracle Instant
Client and listener on database server. Do you have any DBA who could
investigate on server side why the listener is dropping these
connections? And btw which Oracle server version are you using?

I tried to google for these error codes - there are many discussion
forum articles about them but most of them are caused by client or
server version mismatches.

Just for interest you could try to use MRI and Mongrels to see if you
will get the same issue, currently for me it seems that it shouldn't
be Ruby runtime related.

One additional thing that you could try is to install different
version of Oracle Instant Client - e.g. 10.2.0.3 or 10.1.0.5. I
remember that some time ago on one server I had some stability issues
with 10.2.x and then I downgraded Instant Client to 10.1.x

Raimonds

[Mattia]

On Jan 19, 8:47 am, Raimonds Simanovskis
<raimonds.simanovs...@gmail.com> wrote:
...

> I tried to google for these error codes - there are many discussion
> forum articles about them but most of them are caused by client or
> server version mismatches.

I found the cause, I build the ruby-oci8 driver with instant client
and was running it linked the the oracle client libraries (same
version).
It's a couple of days now that it doesn't die on me so I'm pretty
confident this was the cause.

Thanks and sorry for the noise
-- mattia

[Mattia]

On Jan 28, 11:45 am, Mattia <malat...@gmail.com> wrote:
> On Jan 19, 8:47 am, Raimonds Simanovskis<raimonds.simanovs...@gmail.com> wrote:
>
> ...
>
> > I tried to google for these error codes - there are many discussion
> > forum articles about them but most of them are caused by client or
> > server version mismatches.
>
> I found the cause, I build the ruby-oci8 driver with instant client
> and was running it linked the the oracle client libraries (same
> version).

let me rephrase that: I thought I found the cause :(
Just a little bit of high activity (i.e. an autocompleter or a live
search) will kill the connection.
I engaged the DBAs let's see.

-- mattia

[Mattia]

On Feb 6, 7:07 pm, Mattia <malat...@gmail.com> wrote:
...

> I engaged the DBAs let's see.

Ok, I got something useful by further inspection with tcpdump and a
DBA :)
Looks like the problem is that Oracle is getting too many connections
and refuses (as it should) to open new ones.

Now the question is why is the rails application trying to open a new
connection?
When the "connection closed" error happens this is the situation on
the machine I'm running the application on:

266 Open connections to the Oracle server
$ netstat -ntap | grep 1521.*ESTABLISHED | wc -l
266

Not as many rails/apache processes though:
$ ps xa | grep rails | wc -l
7
$ ps xa | grep apache | wc -l
13

Now, I'll try to find the time to dig into the enhanced AR code and
see if I find anything, I'd really appreciate if you could give me
directions to get quickly started.
Or do you happen to have some idea where to look already?

thanks
-- mattia

[Raimonds Simanovskis]

I do not have yet big experience with running Apache mod_passenger in
production (still using Mongrels) but as far as I understand:
if you have RailsSpawnMethod conservative
then as I posted previously I saw that it is recommended to add at the

end of environment.rb
ActiveRecord::Base.connection.disconnect!

As a result each spawned mod_passenger process will reestablish one
connection to database. So you should have so many connections to the
database how much mod_passenger processes you have.

If you use -p flag for netstat then you see which process is owner for
each TCP connection - please investigate what are these other
processes that have this connection to database server port 1521.
Maybe there are other non-Rails processes that connect to the same
database?

Raimonds

[Mattia]

On Mar 12, 4:49 am, Raimonds Simanovskis

<raimonds.simanovs...@gmail.com> wrote:
> I do not have yet big experience with running Apache mod_passenger in
> production (still using Mongrels) but as far as I understand:

In mongrel I have the exact same problem.

> if you have RailsSpawnMethod conservative

yes

> then as I posted previously I saw that it is recommended to add at the
> end of environment.rb
> ActiveRecord::Base.connection.disconnect!

It doesn't make any difference wrt the behaviour I experience here.

> As a result each spawned mod_passenger process will reestablish one
> connection to database. So you should have so many connections to the
> database how much mod_passenger processes you have.

this is where things start to get weird. After 4 requests I have 4
connections and 1 passenger process:
$ sudo netstat -ntap | grep 1521.*ESTABLISHED
tcp 0 0 10.67.17.58:50886 10.66.72.100:1521
ESTABLISHED 27035/jnx
tcp 0 0 10.67.17.58:50885 10.66.72.100:1521
ESTABLISHED 27035/jnx
tcp 0 0 10.67.17.58:50890 10.66.72.100:1521
ESTABLISHED 27035/jnx
tcp 0 0 10.67.17.58:50889 10.66.72.100:1521
ESTABLISHED 27035/jnx
$ ps 27035
PID TTY STAT TIME COMMAND
27035 ? S 0:02 Rails: /home/mdo/devel/rails-apps/jnx

-- mattia

[KUBO Takehiro]

On Thu, Mar 12, 2009 at 10:56 AM, Mattia <mala...@gmail.com> wrote:
>> if you have RailsSpawnMethod conservative
>
> yes
>
>> then as I posted previously I saw that it is recommended to add at the
>> end of environment.rb
>> ActiveRecord::Base.connection.disconnect!
>
> It doesn't make any difference wrt the behaviour I experience here.
>
>> As a result each spawned mod_passenger process will reestablish one
>> connection to database. So you should have so many connections to the
>> database how much mod_passenger processes you have.
>
> this is where things start to get weird. After 4 requests I have 4
> connections and 1 passenger process:

Connection Pooling? The default value is 5.
http://guides.rubyonrails.org/2_2_release_notes.html#connection-pooling

[Mattia]

On Mar 12, 10:56 am, Mattia <malat...@gmail.com> wrote:
> On Mar 12, 4:49 am, Raimonds Simanovskis
>
> <raimonds.simanovs...@gmail.com> wrote:
> > I do not have yet big experience with running Apache mod_passenger in
> > production (still using Mongrels) but as far as I understand:
>
> In mongrel I have the exact same problem.

turns out it was related to rails 2.2.0.
I just upgraded to 2.2.2 and everything seems fine: 1 mongrel -> 1
connection :)

What I'm still wondering is why I couldn't reproduce the leaking
behavior with a postgres database (and adapter of course).
I guess I will never know...

-- mattia

[Mattia]

On Mar 12, 1:26 pm, KUBO Takehiro <kubo.takeh...@gmail.com> wrote:

definitely not. I threw some puts in the active record connection
pooling code and the pool always had one connection so there was
definitely something taking the cached connection out of it and never
returning it.
Also, if you look at my previous post you'll notice that I got to 266
connections (and growing) for 7 rails processes. :)

Thanks,
-- mattia

[Mattia]

On Mar 12, 3:51 pm, Mattia <malat...@gmail.com> wrote:
...

> turns out it was related to rails 2.2.0.

Found!
http://rails.lighthouseapp.com/projects/8994-ruby-on-rails/tickets/1335-connection-pool-creating-a-new-connection-for-every-http-request
as is being said in the post this was observable on Oracle mainly:
"In development mode it is an issue since under oracle it causes
hundreds of connections to rack up and eventually locks up the
database (MySQL is nice since it does some housekeeping and cleans up
the connections)."

Fixed in commit
[c42c24b83d69f1dbc444c72a42d08313f6201650] Don't leave open dangling
connections in development mode. [#1335 state:resolved]

Good to know I guess.
-- mattia