[Zope] Database connectors

[Dario Lopez-Kästen]

Maciej Wisniowski said the following on 11/17/2006 10:41 AM:
>> rieh25 said the following on 11/16/2006 09:12 PM:
>>> I wanted to do it because for some reason, my oracle connector (DCOracle)
>>> keeps disconnecting, and so I thought maybe it would be possible to define a
>>> python method to periodically check on it and reconnect it if necessary.
>>>
> 
> 
>> * for various reasons, the connecions get dropped sometimes, and we need
>> to catch that and restart the affected node. Hence the scripts.
> Do you know these reasons?

yes, we sometimes get deadlocks in the database, net outages and general
misbehavior of DCO2. This is on zope2.6; we have not been able to use
ChrisW's latest modifications to the the python side of DCO2, because
for our use cases it hanged even worse. This was some two years ago, and
now that I have gotten permission to work on the site again, the current
state of affairs being acceptable, I have not researched the subject.

If everything goes the way I want, we will not be using neither ZSQL not
DCO2 connections in the future; we will use SQLAlchemy to create a
standard python library that connects to the database with cx_Oracle,
and zope will only see Python objects from our custom library. The
library will manage the connections to Oracle, and be independent of
ZODB transactions (more or less).

> 
> Seems to me that your script only checks one opened connection.

Sorry, I forgot to mention the following:

It is indeed not the case that it checks a connection, in fact it only
checks the success of calls to a ZSQL method.

The connection checking comes from the fact that that each ZSQL method
is bound to a particular connection object. While it is possible to
unset which connection object a ZSQL method is bound to, both by
accident or on purpose, that case is not covered here (we cannot cater
for human errors :-)
em.

> 
> Say you have Oracle_database_connection object that is connected to:
> xxx/yyy at zzz.
> When you call context.Oracle_database_connection() (and ZSQLMethods
> do that) DCOracle2 checks if there is volatile attribute:
> _v_database_connection. If no, then DCOracle2 creates connection to
> Oracle and assigns it to _v_database_connection. Finally
> _v_database_connection is returned.
> 
> Volatile attributes are associated with connections to ZODB. So every
> connection to ZODB will have it's own copy of _v_database_connection.
> 
> The result is that, you're checking only one, connection to
> Oracle (assigned to currently used ZODB connection), and it is possible
> that other oracle connections (assigned to different ZODB connections)
> are broken.

Yes, but my purpose is not so fine grained. What I do is to call a ZSQL
method and see if it executes a piece of SQL successfully. If it does
then everything is OK. This script is checked every five minutes for
each node (for the whole site it gets checked roughly once per minute).

So, it only checks for the current active connection, like you describe,
but since we are only interested in per node stability, not per
zodb-connection stability, we brutally restart any node that displays
any kind of problem.

It works for us (we have six nodes, so we can "afford" to do that, and
we also have quite a lot of traffic, so errors show themselves pretty
quickly).

This presents a problem with sessions, if they are not shared across all
 nodes, either by being stored in the RDBMS, for instance, or stored on
the ZEO-server (there are options to configure zope to use the ZEO
server to storet the temp database).

> You may sometimes see the above behaviour by clicking on 'Close' button
> in Oracle_database_connection object. After closing you'll see that
> connection state is closed. But try hitting refresh in your browser for
> a few times. You'll likely see sometimes that connection is 'Open',
> because your request will use different connection to ZODB.
> 
> As Chris said 'Open/Close' button is almost useless in DCOracle2. It is
> good only to check whether connection string is valid.

indeed,and even that it does kind of crappy some times :-)

> I have modified version of DCOracle2 that has some errors corrected and
> is able to do a reconnect on a broken connections. It displays error
> once, and then reconnects. It also uses a kind of pool of connections,
> so Open/Close button is working as expected. If you're interested then I
> may try to publish this code.

Even though I won't be needing it for the current project, I think it
would be nice to have it published anyway.

There is still a need for ZSQL based oracle connections, and DCO2 is not
actively maintained any more, at least officially - incidentally this is
one of the reasons we are trying to migrate from DCO2 in the future.

The only current Zope database adapter that I know of for oracle is only
for Zope3, and that one uses cx_oracle (IIRC).

Seeing that Zope2 is still alive, kicking and in perfectly good health I
think that many people would like an improved DCO2 adapter.

/dario

-- 
-- -------------------------------------------------------------------
Dario Lopez-Kästen, IT Systems & Services Chalmers University of Tech.
Lyrics applied to programming & application design:
"emancipate yourself from mental slavery" - redemption song, b. marley