[sqlalchemy] Re: Database Disconnection Detection
check out the very latest trunk and apply this patch: Index: lib/sqlalchemy/databases/oracle.py === --- lib/sqlalchemy/databases/oracle.py (revision 2487) +++ lib/sqlalchemy/databases/oracle.py (working copy) @@ -409,6 +409,8 @@ if context is not None: context._rowcount = rowcount +def is_disconnect(self, e): +return isinstance(e, self.dbapi.DatabaseError) and ORA-03114 in str(e) OracleDialect.logger = logging.class_logger(OracleDialect) see if that throws sqlalchemy.exceptions.SQLError for all disconnect cases. On Apr 3, 2007, at 12:28 PM, Greg Copeland wrote: I've done a little bit more testing and have found some interesting results. If a connection has previously been established and it then fails, I get a cx_Oracle exception. If a connection has yet to be established, I get a SQLAlchemy exception. These are the guys I'm seeing. If it would be helpful, I can provide a stack trace too. Nonetheless, I assume these cases are covered by the current, ongoing effort. cx_Oracle.DatabaseError instance at 0x1917bb6c sqlalchemy.exceptions.DBAPIError instance at 0x1917bb4c I was catching these with a generic, catchall, Exception handler but decided I couldn't live with that, out of fear I'd wind up in an infinite loop. I am now using, except (sa.exceptions.DBAPIError, cx_Oracle.DatabaseError), e:. While I don't like having the cx_Oracle exception in there, at least I have explicit handling with the unknown/unexpected case broken out now. Greg P.S. Keep up the good work guys! On Apr 2, 5:06 pm, Michael Bayer [EMAIL PROTECTED] wrote: our current work with auto-reconnect is checked in to the trunk, including exception catches around rollbacks and commits. so far it does seem to work with postgres, in a rudimental test. the oracle routine is still not checked in. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Database Disconnection Detection
this feature is not supported right now for most DB's except mysql but is being addressed in a current ongoing ticket and will be available in the next release. however, its possible that some DBAPIs do not raise the error in a manner which can ever be consistently caught; for example we've had problems getting this feature to work with Psycopg2 since the exception is thrown at unpredictable times (i.e. not within execute() or cursor() call; instead, in the middle of parsing a string or something which has nothing to do with a psycopg2 operation...we're not sure if this is some artifact of native python extensions or what). On Apr 2, 2007, at 1:07 PM, Greg Copeland wrote: I'm using sqlalcehmy 2.5 with cx_Oracle 4.2.1. What is the proper way to detect an sqlalchemy operation has lost its database connection and reconnection/retry? The manual doesn't seem to say much about the topic. When connection loss occurs, does SA throw the native dbapi exception? If not, what exception will user code see? I found that various threads on automatic reconnection and I assume SA does not currently, reliably, support automatic database reconnection? Greg --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Database Disconnection Detection
Greg, If you can send the traceback you get when such an error occurs, that would be helpful for providing Oracle support for this condition. Paul Greg Copeland wrote: I'm using sqlalcehmy 2.5 with cx_Oracle 4.2.1. What is the proper way to detect an sqlalchemy operation has lost its database connection and reconnection/retry? The manual doesn't seem to say much about the topic. When connection loss occurs, does SA throw the native dbapi exception? If not, what exception will user code see? I found that various threads on automatic reconnection and I assume SA does not currently, reliably, support automatic database reconnection? Greg --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Database Disconnection Detection
On Apr 2, 12:27 pm, Michael Bayer [EMAIL PROTECTED] wrote: this feature is not supported right now for most DB's except mysql but is being addressed in a current ongoing ticket and will be available in the next release. I'm assuming you are talking about autoreconnect here? If so, I would be curious to hear the intended direction. I cam easily imagine a variety of needs assocaited with such a feature. For example, blocking during reconnect/try is probably fine for some class of applications but problematic for others (would be for me). however, its possible that some DBAPIs do not raise the error in a manner which can ever be consistently caught; for example we've had problems getting this feature to work with Psycopg2 since the exception is thrown at unpredictable times (i.e. not within execute() or cursor() call; instead, in the middle of parsing a string or something which has nothing to do with a psycopg2 operation...we're not sure if this is some artifact of native python extensions or what). Interesting. So there is no generic method for catching this category of error with SQLAlchemy right now, assuming the DBAPI actually returns an exception? Greg On Apr 2, 2007, at 1:07 PM, Greg Copeland wrote: I'm using sqlalcehmy 2.5 with cx_Oracle 4.2.1. What is the proper way to detect an sqlalchemy operation has lost its database connection and reconnection/retry? The manual doesn't seem to say much about the topic. When connection loss occurs, does SA throw the native dbapi exception? If not, what exception will user code see? I found that various threads on automatic reconnection and I assume SA does not currently, reliably, support automatic database reconnection? Greg --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Database Disconnection Detection
Frankly I'm very surprised this class of error has not been previously addressed. I'll have to test and see what happens. I was planning on doing the defensive coding, followed by the testing to make sure it is caught and recovers correctly. Greg On Apr 2, 12:36 pm, Paul Johnston [EMAIL PROTECTED] wrote: Greg, If you can send the traceback you get when such an error occurs, that would be helpful for providing Oracle support for this condition. Paul Greg Copeland wrote: I'm using sqlalcehmy 2.5 with cx_Oracle 4.2.1. What is the proper way to detect an sqlalchemy operation has lost its database connection and reconnection/retry? The manual doesn't seem to say much about the topic. When connection loss occurs, does SA throw the native dbapi exception? If not, what exception will user code see? I found that various threads on automatic reconnection and I assume SA does not currently, reliably, support automatic database reconnection? Greg --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Database Disconnection Detection
if you care to view trac, its ticket #516. also, reasons like these are why i keep SA at a relatively low version number (0.3) despite some saying this is underestimating. in any case, as the experience with psycopg2 is illustrating, i am not totally confident the state of DBAPI is going to really let this feature work very well on all platforms, since it does not define where such an error condition should be thrown, what the specific error should be, nor is there any documented way to ping a database reliably (therefore, be less surprised about SA, we arent the only ones insufficiently addressing this). we are not blocking on reconnect/try. the error gets thrown in all cases - the connection is then removed from the pool. so while its auto-reconnecting, we are not going for seamless midstream refreshing. trying to reconnect midstream would be extremely problematic particularly that we cant make any assumptions about ongoing transactional state and such. to minimize errors like these if you think your database is going to crash a lot, put a very low pool_recycle setting on your engine...that way connections can be kept very fresh. also there has just been discusson on this list about this issue not more than a week ago so feel free to browse the archives a bit. On Apr 2, 2007, at 1:59 PM, Greg Copeland wrote: Frankly I'm very surprised this class of error has not been previously addressed. I'll have to test and see what happens. I was planning on doing the defensive coding, followed by the testing to make sure it is caught and recovers correctly. Greg On Apr 2, 12:36 pm, Paul Johnston [EMAIL PROTECTED] wrote: Greg, If you can send the traceback you get when such an error occurs, that would be helpful for providing Oracle support for this condition. Paul Greg Copeland wrote: I'm using sqlalcehmy 2.5 with cx_Oracle 4.2.1. What is the proper way to detect an sqlalchemy operation has lost its database connection and reconnection/retry? The manual doesn't seem to say much about the topic. When connection loss occurs, does SA throw the native dbapi exception? If not, what exception will user code see? I found that various threads on automatic reconnection and I assume SA does not currently, reliably, support automatic database reconnection? Greg --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Database Disconnection Detection
Please don't take my comment the wrong way. It's actually an off handed compliment. SA seems fairly robust and developmentally strong. As such, it's just surprising that something like error handling doesn't have comprehensive coverage. Now I can certainly understand why, since the DBAPI doesn't cover the bases. Any idea who we should mention this to revise the DBAPI? Anyone know if a DBAPI PEP already exists to ensure parity for SQL independant error handling? In my opinion, any failure to provide robust error handling by the DBAPI specification is contrary to the fundamental requirements of DBAPI. This defeats the porpose of DBAPI if every robust app must have DB specific error handling. Then again, I'm sure I'm preaching to the choir here. ;) Okay, so what's SA's take on this problem domain? Applcation executes a query but the database has died between the time the connection was established and the time the query was executed. Does SA attempt to return an SA specific exception to the calling application? Does it simply allow the native DB exception to be returned to the application? Meaning, despire the fact that I'm using SA, my application must be prepared to catch a cx_Oracle specific exception? Worse, you're saying cx_Oracle may not return an exception (thusly, the previous request)? Or, how about this. Application has a pool of connections. The database goes down. No valid connections exist within the cool; having been previously reaped. Application attempts to query database. What does SA return? In this case, it should know it does not have any connections within its pool. Is this documented? Is there any documention which describes the known failure modes which SA detects? If so, link please? Greg On Apr 2, 1:11 pm, Michael Bayer [EMAIL PROTECTED] wrote: if you care to view trac, its ticket #516. also, reasons like these are why i keep SA at a relatively low version number (0.3) despite some saying this is underestimating. in any case, as the experience with psycopg2 is illustrating, i am not totally confident the state of DBAPI is going to really let this feature work very well on all platforms, since it does not define where such an error condition should be thrown, what the specific error should be, nor is there any documented way to ping a database reliably (therefore, be less surprised about SA, we arent the only ones insufficiently addressing this). we are not blocking on reconnect/try. the error gets thrown in all cases - the connection is then removed from the pool. so while its auto-reconnecting, we are not going for seamless midstream refreshing. trying to reconnect midstream would be extremely problematic particularly that we cant make any assumptions about ongoing transactional state and such. to minimize errors like these if you think your database is going to crash a lot, put a very low pool_recycle setting on your engine...that way connections can be kept very fresh. also there has just been discusson on this list about this issue not more than a week ago so feel free to browse the archives a bit. On Apr 2, 2007, at 1:59 PM, Greg Copeland wrote: Frankly I'm very surprised this class of error has not been previously addressed. I'll have to test and see what happens. I was planning on doing the defensive coding, followed by the testing to make sure it is caught and recovers correctly. Greg On Apr 2, 12:36 pm, Paul Johnston [EMAIL PROTECTED] wrote: Greg, If you can send the traceback you get when such an error occurs, that would be helpful for providing Oracle support for this condition. Paul Greg Copeland wrote: I'm using sqlalcehmy 2.5 with cx_Oracle 4.2.1. What is the proper way to detect an sqlalchemy operation has lost its database connection and reconnection/retry? The manual doesn't seem to say much about the topic. When connection loss occurs, does SA throw the native dbapi exception? If not, what exception will user code see? I found that various threads on automatic reconnection and I assume SA does not currently, reliably, support automatic database reconnection? Greg --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Database Disconnection Detection
On Apr 2, 2007, at 2:31 PM, Greg Copeland wrote: Okay, so what's SA's take on this problem domain? Applcation executes a query but the database has died between the time the connection was established and the time the query was executed. Does SA attempt to return an SA specific exception to the calling application? Does it simply allow the native DB exception to be returned to the application? Meaning, despire the fact that I'm using SA, my application must be prepared to catch a cx_Oracle specific exception? Worse, you're saying cx_Oracle may not return an exception (thusly, the previous request)? SA wraps all DB exceptions in a common class called SQLError, from which the original throwable is contained. it is a descendant of the generic SQLAlchemyError. otherwise it would be impossible to write a DB-neutral application that caught errors. we've also had requests to contain the error code within, but again DBAPI doesnt define any way for this to be possible, plus its completely undocumented for every DBAPI i deal with (psycopg2, mysqldb, sqlite, cx_oracle). you can see this if you read the docstrings for the exceptions module, its on the site. Or, how about this. Application has a pool of connections. The database goes down. No valid connections exist within the cool; having been previously reaped. Application attempts to query database. What does SA return? In this case, it should know it does not have any connections within its pool. Is this documented? the pool knows how to grow itself when theres no connections, yes. it will open new connections as needed, if the connection fails, throws an error. Is there any documention which describes the known failure modes which SA detects? If so, link please? the current sqlalchemy docs are well over 100 pages, and i apologize that not every implementation detail is documented...but if they were, the docs would be 1500 pages. im afraid if you realy need to know the mechanisms of internals, youre going to have to read the source code a little bit. again, the theme here is, this is pretty normal for open source projects. as far as failure modes, if the question means, when does sqlalchemy throw an exception ? , you can get a good idea by reading the generated documentation for the exceptions module. obviously every error thrown by DBAPI gets wrapped in SQLError, SA throws errors for invalid arguments, incorrect configurations that are known to lead to problems, issues encountered during flushes, etc. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Database Disconnection Detection
On Apr 2, 12:36 pm, Paul Johnston [EMAIL PROTECTED] wrote: Greg, If you can send the traceback you get when such an error occurs, that would be helpful for providing Oracle support for this condition. Paul I assume this means that the exception is not being caught and wrapped by an SQLAlchemy.exceptions exception? As you can tell, the envionment for this test is cygwin. I also run/test on RHEL Linux. Traceback (most recent call last): File stdin, line 1, in ? File build/bdist.cygwin-1.5.20-i686/egg/sqlalchemy/engine/base.py, line 266, in execute File build/bdist.cygwin-1.5.20-i686/egg/sqlalchemy/engine/base.py, line 276, in execute_text File build/bdist.cygwin-1.5.20-i686/egg/sqlalchemy/engine/base.py, line 350, in _execute_raw File build/bdist.cygwin-1.5.20-i686/egg/sqlalchemy/engine/base.py, line 365, in _execute File build/bdist.cygwin-1.5.20-i686/egg/sqlalchemy/engine/base.py, line 254, in _autorollback File build/bdist.cygwin-1.5.20-i686/egg/sqlalchemy/engine/base.py, line 239, in _rollback_impl File build/bdist.cygwin-1.5.20-i686/egg/sqlalchemy/engine/ default.py, line 55, in do_rollback cx_Oracle.DatabaseError: ORA-03114: not connected to ORACLE To get this, I started python and interactively created and engine. I then grabbed a connection by using engine.connect(). Once I had my connection, I then pulled my network cable and issued the execute on my connection. Let me know if you want me to go about this from another direction. Greg Greg Copeland wrote: I'm using sqlalcehmy 2.5 with cx_Oracle 4.2.1. What is the proper way to detect an sqlalchemy operation has lost its database connection and reconnection/retry? The manual doesn't seem to say much about the topic. When connection loss occurs, does SA throw the native dbapi exception? If not, what exception will user code see? I found that various threads on automatic reconnection and I assume SA does not currently, reliably, support automatic database reconnection? Greg --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Database Disconnection Detection
we should probably be wrapping the commit()/rollback() etc methods for DB exceptions as well, currently its just execute() and cursor() AFAIK. On Apr 2, 2007, at 3:41 PM, Greg Copeland wrote: On Apr 2, 12:36 pm, Paul Johnston [EMAIL PROTECTED] wrote: Greg, If you can send the traceback you get when such an error occurs, that would be helpful for providing Oracle support for this condition. Paul I assume this means that the exception is not being caught and wrapped by an SQLAlchemy.exceptions exception? As you can tell, the envionment for this test is cygwin. I also run/test on RHEL Linux. Traceback (most recent call last): File stdin, line 1, in ? File build/bdist.cygwin-1.5.20-i686/egg/sqlalchemy/engine/base.py, line 266, in execute File build/bdist.cygwin-1.5.20-i686/egg/sqlalchemy/engine/base.py, line 276, in execute_text File build/bdist.cygwin-1.5.20-i686/egg/sqlalchemy/engine/base.py, line 350, in _execute_raw File build/bdist.cygwin-1.5.20-i686/egg/sqlalchemy/engine/base.py, line 365, in _execute File build/bdist.cygwin-1.5.20-i686/egg/sqlalchemy/engine/base.py, line 254, in _autorollback File build/bdist.cygwin-1.5.20-i686/egg/sqlalchemy/engine/base.py, line 239, in _rollback_impl File build/bdist.cygwin-1.5.20-i686/egg/sqlalchemy/engine/ default.py, line 55, in do_rollback cx_Oracle.DatabaseError: ORA-03114: not connected to ORACLE To get this, I started python and interactively created and engine. I then grabbed a connection by using engine.connect(). Once I had my connection, I then pulled my network cable and issued the execute on my connection. Let me know if you want me to go about this from another direction. Greg Greg Copeland wrote: I'm using sqlalcehmy 2.5 with cx_Oracle 4.2.1. What is the proper way to detect an sqlalchemy operation has lost its database connection and reconnection/retry? The manual doesn't seem to say much about the topic. When connection loss occurs, does SA throw the native dbapi exception? If not, what exception will user code see? I found that various threads on automatic reconnection and I assume SA does not currently, reliably, support automatic database reconnection? Greg --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Database Disconnection Detection
Hi, I assume this means that the exception is not being caught and wrapped by an SQLAlchemy.exceptions exception? We're going to identify the exception in a database-specific bit of code. cx_Oracle.DatabaseError: ORA-03114: not connected to ORACLE Excellent, that's the snippet I need. To get this, I started python and interactively created and engine. I then grabbed a connection by using engine.connect(). Once I had my connection, I then pulled my network cable and issued the execute on my connection. Nice. I've been restarting the database server (for MSSQL, MySQL, Postgres) but I wonder if that will yield slightly different results. Paul --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Database Disconnection Detection
connection, I then pulled my network cable and issued the execute on my connection. Nice. I've been restarting the database server (for MSSQL, MySQL, Postgres) but I wonder if that will yield slightly different results. If you need me to shutdown the database and retry, I can try that later this week. My DB server is a shared server so shutting it down for the whole group is not well receieved. ;) Paul Greg --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Database Disconnection Detection
our current work with auto-reconnect is checked in to the trunk, including exception catches around rollbacks and commits. so far it does seem to work with postgres, in a rudimental test. the oracle routine is still not checked in. On Apr 2, 2007, at 5:21 PM, Greg Copeland wrote: connection, I then pulled my network cable and issued the execute on my connection. Nice. I've been restarting the database server (for MSSQL, MySQL, Postgres) but I wonder if that will yield slightly different results. If you need me to shutdown the database and retry, I can try that later this week. My DB server is a shared server so shutting it down for the whole group is not well receieved. ;) Paul Greg --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
