search

FirebirdSQL / fdb

Firebird Driver for Python
https://www.firebirdsql.org/en/devel-python-driver/
Other
60 stars 26 forks source link

Detection of closed connections #15

Closed o3bvv closed 4 years ago

o3bvv commented 4 years ago

Hello!

I'd like to ask for advice regarding detection of close/state DB connections: if a connection is established, but then there's a network issue or DB service was restarted, what is the correct way to detect that?

E.g., you have an instance of fdb.fbcore.Connection called conn with a connection established. But the db has gone suddenly. Connection's .closed property will still report the connection to be open:

>>> conn.closed
False

However, trying to perform any query will throw a fdb.fbcore.DatabaseError:

fdb.fbcore.DatabaseError: ('Error while preparing SQL statement:\n- SQLCODE: -902\n- Unable to complete network request to host
 "127.0.0.1".\n- Error writing data to the connection.', -902, 335544721)

Is it possible to throw a subclass of ConnectionError in such a case and set .closed to True? Currently it looks like the only way to handle that is to parse the error message of DatabaseError.

Or maybe fdb provides an approach where connections pool or automatic reconnection is possible?

Thanks

pcisar commented 4 years ago

First, the 'closed' property has nothing to do with state of the low-level connection to the server. It indicates that the attachment API handle used by Connection object was disposed (which normally happens in close()) and is no longer valid.

Second, remapping the exception from DatabaseError to something else would be cumbersome. The low-level connection may break any time, even in middle of any operation, so it would be necessary to wrap almost all methods in try...except block, parse the error (note that broken connection may have several forms, and broken TCP is not the only one error that renders the connection effectively unusable) and raise new one. I considered such option in the past (to support more exception types defined by Python DB API 2.0 spec) and rejected it. It would just bloat the driver for marginal gain.

Third, although FDB provides hooks that allow implementation of connection pools, it's not possible to do "reconnection in background" simply because the driver is not designed to support that. It's simply not possible to reliably transfer state between two Connection objects (mind that it's not just connection, but also transactions, cursors etc. that are linked to the connection), it's better to drop it all and create new ones.

And finally, the FDB driver is now considered as legacy, and further development (except maintenance) was stopped in favor of new driver (see https://github.com/FirebirdSQL/python3-driver). The new driver uses new interface-based API introduced in Firebird 3, that among others provides ping() method on IAttachment interface that could be helpful for your needs (there is no way how client can detect broken connection without attempt to do something with server in old API that FDB uses). The new driver development is close to first release, but it works only with FB 3+ and Python 3.8+.

asfernandes commented 4 years ago

Old interface has fb_ping too.

pcisar commented 4 years ago

Hmm, nice to discover yet another undocumented old API function :) However, it's supported only by FB 3+