Database connectors
Could somebody tell me how to close/open a database connection through python instructions? Thanks in advance...
--On 16. November 2006 11:33:06 -0600 Roberto Edwins <robertoedwins@gmail.com> wrote:
Could somebody tell me how to close/open a database connection through python instructions?
What's the usecase? DAs usually connect automatically?! -aj
Roberto Edwins wrote:
Could somebody tell me how to close/open a database connection through python instructions?
Thanks in advance...
Just do a google on your database + python. Of course, it would be better if you found your database + zope - because (at least zope2) has a nice array of database adapters as products for Zope that are ready to go (Postgres, mySql, and Interbase come to mind as examples) Good luck, David H ps: in the future try to be more specific, eg: what db are you using, whats your platform ... and since this is a Zope list we kinda assume you want a zope db adapter - not just a python one.
Could somebody tell me how to close/open a database connection through python instructions? If you want to close database connections from Zope then it may be not so easy. Depends on DatabaseAdapter how it is resolved. I mean that Zope DatabaseAdapters usually use volatile attributes like _v_db_connection bound to specific ZODB connection's - thus this may be difficult to definitely close all opened connections.
There is Zope-DB list for database specific questions. -- Maciej Wisniowski
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. I imagined it wouldn't be so easy, but anyways I think I may be capable of convincing ppl at work to buy a mxodbc license... thanks. Maciej Wisniowski wrote:
Could somebody tell me how to close/open a database connection through python instructions? If you want to close database connections from Zope then it may be not so easy. Depends on DatabaseAdapter how it is resolved. I mean that Zope DatabaseAdapters usually use volatile attributes like _v_db_connection bound to specific ZODB connection's - thus this may be difficult to definitely close all opened connections.
There is Zope-DB list for database specific questions.
-- Maciej Wisniowski _______________________________________________ Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )
-- View this message in context: http://www.nabble.com/Database-connectors-tf2644648.html#a7387390 Sent from the Zope - General mailing list archive at Nabble.com.
I wanted to do it because for some reason, my oracle connector (DCOracle) keeps disconnecting By DCOracle you mean DCOracle2?
What do you mean by 'keeps disconnecting'. What kind of errors do you see. We're using DCO2 - but so far we had only few accidents that something was disconnected and it was never because of DCOracle2. Reasons was usually: 1. Oracle database was restarted or database killed some sessions 2. Firewall killed connections 3. Oracle database had timeout for connections. Do you have error traceback or something like that?
and so I thought maybe it would be possible to define a python method to periodically check on it and reconnect it if necessary. There already is a method that checks whether a connection is opened. AFAIR it is in DCOracle.py - isOpen() but it isn't so easy to use this when connection is broken - doesn't work as expected. I have modified version of DCOracle2 that, in general, is changed to reconnect after incidents like those in 1,2,3 above happen. But this is only kind of workaround of the real problem that causes connections to die.
I imagined it wouldn't be so easy, but anyways I think I may be capable of convincing ppl at work to buy a mxodbc license... mxODBC is good but AFAIR it doesn't have StoredProcedures support (only select some_funtion() from dual and such things).
If you can please tell us what errors do you have from DCOracle2 - exactly - then we'll be possibly able to help you. What Zope version do you use? Do you have Zope-DB list? I think this thread should be moved there. -- Maciej Wisniowski
rieh25 wrote:
I wanted to do it because for some reason, my oracle connector (DCOracle) keeps disconnecting,
DCOracle is broken ;-) The errors you are seeing are likely due to a silly timeout being set on the TNS listener by your oracle dbas. Tell them to remove the timeout and your problems will likely go away. The open/close button on a ZOracleDA actually does nothing except change the state of the button ;-) cheers, Chris -- Simplistix - Content Management, Zope & Python Consulting - http://www.simplistix.co.uk
Chris Withers escreveu:
rieh25 wrote:
I wanted to do it because for some reason, my oracle connector (DCOracle) keeps disconnecting,
DCOracle is broken ;-)
The errors you are seeing are likely due to a silly timeout being set on the TNS listener by your oracle dbas. Tell them to remove the timeout and your problems will likely go away.
The open/close button on a ZOracleDA actually does nothing except change the state of the button ;-)
What do you think about ZcxOracleDA[1]? I don't use Oracle, but I spoke with Wilton and he told me that it was working fine. Please try it, may be a good solution. [1] http://www.tom.pro.br/componentes Regards, -- Jean Ferri _______________________________________________________ Novidade no Yahoo! Mail: receba alertas de novas mensagens no seu celular. Registre seu aparelho agora! http://br.mobile.yahoo.com/mailalertas/
What do you think about ZcxOracleDA[1]?
I don't use Oracle, but I spoke with Wilton and he told me that it was working fine. Please try it, may be a good solution.
[1] http://www.tom.pro.br/componentes I've tested this some time ago. It works but, at the time I checked, this, there were no StoredProcedures support. I'm not sure if it has changed. I've even written e-mail to prof. Wilton Alcentar but with no answer (or it was lost somewhere - spam filters or something?). Since then, in my company we have changed ZcxOracleDA, and now we have version that supports StoredProcedures in a similiar way as DCOracle2 does. It has not been tested yet, we had no time.
In general cxOracle is nice because it works under windows and linux without problems. Do you know whether it is possible to contact with prof. Alcentar? I've resigned after this one, unanswered e-mail, but I think it would be nice to publish this project (with his and our code) on sourceforge (or google code). -- Maciej Wisniowski
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.
i one ancient app that i am trying to modernise, I have a python script that gets called by wget with cron every N minutes. That script calls a zsql that does a select from dual, and the script catches any errors. The set up is a s follows: * My site uses several DCO2 connections objects, one for each of a bunch of schemas in the database. We serve the site in a cluster of 6 nodes (using zeo - nothing fancy: 4 nodes from one machine, 2 nodes from another machine). * for various reasons, the connecions get dropped sometimes, and we need to catch that and restart the affected node. Hence the scripts. * All my DCO2 connection objects are at the root of the site, so they are easy to acquire. * in the root of the site, I have a folder "heartbeat". That folder contains one ZSQL method for each of the DCO2 connection objects, and one Python script that is used to check one or all of the connections' status. The sql in the ZSQL methods is supersimple: --<begin sql>-- select 'name_of_dco2_connection', sysdate from dual --<end sql>-- and the Python script looks like this: --<begin script>--- ## Script (Python) "index" ##bind container=container ##bind context=context ##bind namespace= ##bind script=script ##bind subpath=traverse_subpath ##parameters=name=None ##title= ## """ This script checks the status of connection 'name'. If 'name' is None, check all connections """ if name is None: # Check all connections. Get list of zsql methods in # the same folder as this script. cobjs = [obj for obj in context.ZopeFind(container, obj_metatypes=('Z SQL Method',))] for o in cobjs: # call each script: o[0] is the name, # date is the result from the script try: sys, date = (o[0], o[1]()[0].sysdate) print sys, str(date) except ConflictError: # apparently, ConflictErrors need to be passed on raise except Exception, Msg: # oops, something Oracle-y messed up print o[0], str(Exception), str(Msg) # return the results return printed # try individual names try: # see if the connection exists (actually we # check if there is a zsql named 'name' con = container[name] except KeyError: print "There is no connection named %s"%name return printed try: # found it, check it sys, date = name, con()[0].sysdate print sys, str(date) return printed except ConflictError: # apparently, ConflictErrors need to be passed on raise except Exception, Msg: print name, str(Exception), str(Msg) return printed # We shouldn't be here, so aliens must have # hijacked us print "ALL YOUR BASE ARE BELONG TO US!" return printed --<end script>-- I have cron job that calls a shell script that uses wget to call www.<insert your site here>.com/heartbeat/index that shell script compares the output from wget to decied if there is an error or if the connections are ok. If there is an error, we restart the node that had problems. Hope this helps, it works for us :-) /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
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?
Seems to me that your script only checks one opened connection. Say you have Oracle_database_connection object that is connected to: xxx/yyy@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. 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. 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. -- Maciej Wisniowski
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@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
Dario Lopez-Kästen said the following on 11/17/2006 01:07 PM:
Do you know these reasons?
yes, we sometimes get deadlocks in the database, net outages and general misbehavior of DCO2.
Sorry, i am casting too much blame on DCO2 here; in reality we have a combination of zope2.6, large file uploads and downloads, not necessarily good code on our end, AND quite a lot of DCO2 strangeness. /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
Dario Lopez-Kästen said the following on 11/17/2006 01:07 PM:
Do you know these reasons? yes, we sometimes get deadlocks in the database, net outages and general misbehavior of DCO2.
Sorry, i am casting too much blame on DCO2 here; in reality we have a combination of zope2.6, large file uploads and downloads, not necessarily good code on our end, AND quite a lot of DCO2 strangeness. You say deadlocks... Do you use DCOracle2 Stored Procedures?? If so, then there is a bug in it's definition that may cause Oracle deadlocks.
In SP.py there is function __call__ and code like: try: # Note, this does not do result promotion like the DA query will # which is probably bad. OracleDates in particular look like # DateTimes but arent at all the same! results = apply(self._v_proc,args,kw) return self._lobConvert(self._v_db, results) except: self._v_proc = None raise # Reraise error AFAIR you may change this to: except: self._abort() self._v_proc = None raise # Reraise error Possibly you may need to change these too: def _abort(self): self.db.rollback() to: def _abort(self): if self.db: self.db.rollback() Otherwise, when during request you've called only one stored procedure (no other zsqlmethods etc.) and this SP raised an exception, there may be no rollback to Oracle. -- Maciej Wisniowski
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. I don't remember exactly but code from connection_leak branch or something like that didn't work for me too.
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). Nice, but I'm curious how fast is this and how it deals with complicated procedures etc. I have a lot of logic in database sometimes.
The connection checking comes from the fact that that each ZSQL method is bound to a particular connection object. But 'particular connection object' still may have (and usually has) few oracle connections opened.
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. Not necessarily because some other connections for 'particular connection object' may be broken, so script may execute correctly while different person will execute same ZSQLMethod and will get errors.
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). If this works then it is acceptable solution :)
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). I checked a bit setup with session shared between ZEO Clients and with Pound load balancer. Worked really nice. Restart of one zeo client was invisible for the user.
The only current Zope database adapter that I know of for oracle is only for Zope3, and that one uses cx_oracle (IIRC). We have cxOracleDA for Zope 2.8.x but it was not tested too much yet.
-- Maciej Wisniowski
Dario Lopez-Kästen wrote at 2006-11-17 13:07 +0100:
... yes, we sometimes get deadlocks in the database, net outages and general misbehavior of DCO2.
But "DCO2" is not responsible for your deadlocks... Depends what version of DCOracle Dario has, but I've found that StoredProcedures may cause deadlocks, at last in DCO2 version I used. I described this in my previous mail in this topic.
That was problem when during one request there was only one call to a StoredProcedure. During that call database connection was created (_v_database_connection) and SP raised exception. Then publisher aborts and rollbacks everything. But first it destroyed database_connection and then Publisher calls abort() for SP - but there is no connection at this moment so no rollback happens. It is because StoredProcedures in DCOracle2 are a bit strange. They're resource managers itself, but they're using another resource managers from db.py. I've seen this alive in my environment, but this might be something wrong with my DCOracle2 version. I'll be glad if somebody may confirm this. -- Maciej Wisniowski
Maciej Wisniowski wrote at 2006-11-17 22:12 +0100:
... That was problem when during one request there was only one call to a StoredProcedure. During that call database connection was created (_v_database_connection) and SP raised exception.
Then publisher aborts and rollbacks everything. But first it destroyed database_connection and then Publisher calls abort() for SP - but there is no connection at this moment so no rollback happens.
Have you seen this really? In fact, the "_v_database_connection" is *NOT* necessary to abort(rollback) commit the transaction: The call to the stored procedure must register with Zope's transaction system. This registration contains a reference to the connection via which the database transaction can be controlled: i.e. aborted (rolled back) or committed. After the registration, the "_v_database_connection" can disapear without effect to the transaction cleanup.
It is because StoredProcedures in DCOracle2 are a bit strange. They're resource managers itself, but they're using another resource managers from db.py.
Okay, that might be a problem. Do you understand why they are resource managers themselves. But, note that "db.py" is below the "_v_database_connection". If the stored procedure stores a reference to the instance defined in "db.py", then the "_v_database_connection" is again not relevant. -- Dieter
Then publisher aborts and rollbacks everything. But first it destroyed database_connection and then Publisher calls abort() for SP - but there is no connection at this moment so no rollback happens.
Have you seen this really? Yes, but as I said I don't remember exactly what was the error. So maybe I've messed something. I'll try to repeat this and give exact description.
But, note that "db.py" is below the "_v_database_connection". If the stored procedure stores a reference to the instance defined in "db.py", then the "_v_database_connection" is again not relevant. Stored procedure does this:
db = self._v_db = getattr(self,self.connection)() # Get connection So seems that self._v_db is a reference to _v_database_connection. If transaction caused new "_v_database_connection" to be created, then is this possible that during this transaction rollback, there is something like: del("_v_database_connection")? Or maybe because of same reference being held in SP.py self._v_db it doesn't happen? I wonder if it is possible that __del__ from db.py is executed: def __del__(self): self.cursor = None # Break reference self.db = None and then, althought self._v_db in SP.py exists, it has no self.db??
Do you understand why they are resource managers themselves. Yes and not. I mean I understand what resource managers are in general, but I don't understand why StoredProcedures from DCO2 are transaction managers itself. Why they're not using transaction machinery from db.py. At last it still uses same self.db. Strange because when during one request you have calls to StoredProcedures and to ZSQLMethods you'll get commits or rollback in oracle twice. Isn't it?
-- Maciej Wisniowski
In fact, the "_v_database_connection" is *NOT* necessary to abort(rollback) commit the transaction:
I have procedure WYJATEK in Oracle that simply raises exception. I've created StoredProcedure in Zope called 'wyjatek' and navigated to 'Test' page. Then I've restarted Zope, and after restart I've just hit 'Test' button. Below is full pdb session (Zope running with one thread only) starting from : /opt/Zope/2.8.8/lib/python/transaction/_transaction.py(492)abort() In short: there are two resource managers registered: 1. <Connection at b62f228c> 2. <MultiObjectResourceAdapter for <Procedure at wyjatek> at -1241707860> When 1. is aborted, then in it's _abort method I have: self._registered_objects == [<Procedure at wyjatek>] and the code below is executed: for obj in self._registered_objects: oid = obj._p_oid (...) self._cache.invalidate(oid) Later, 2. is being aborted and in SP.py _abort I have: self._v_db.db.rollback() AttributeError: '_v_db' So there is no rollback to Oracle. SP.py __call__ is: self._register() # uses: transaction.get().register(Surrogate(self)) try: # Note, this does not do result promotion like the DA query will # which is probably bad. OracleDates in particular look like # DateTimes but arent at all the same! results = apply ( self._v_proc, args, kw ) return results #return self._lobConvert(results) except: logger.error('Error in dco2 procedure') self._v_proc = None raise # Reraise error Is this because of: self._cache.invalidate(oid)? Maciej Wisniowski Full pdb session: 2006-11-21 08:04:16 INFO Zope Ready to handle requests 2006-11-21 08:04:16 DEBUG txn.16386 new transaction 2006-11-21 08:04:16 INFO _transaction abort 2006-11-21 08:04:16 INFO _transaction [] (...) 2006-11-21 08:04:24 DEBUG txn.16386 new transaction 2006-11-21 08:04:24 DEBUG DCOracle2 - DCOracle2.py Created new DCO2 connection <Products.DCOracle2.DCOracle2.DCOracle2.connection instance at 0xb5fd0dac> 2006-11-21 08:04:24 ERROR DCOracle2 - SP.py Error in dco2 procedure 2006-11-21 08:04:24 ERROR Zope.SiteErrorLog http://eurolinux:8091/testOracleDRTest/wyjatek/manage_testZOracleStoredProce... Traceback (most recent call last): File "/opt/Zope/2.8.8/lib/python/ZPublisher/Publish.py", line 114, in publish request, bind=1) File "/opt/Zope/2.8.8/lib/python/ZPublisher/mapply.py", line 88, in mapply if debug is not None: return debug(object,args,context) File "/opt/Zope/2.8.8/lib/python/ZPublisher/Publish.py", line 40, in call_object result=apply(object,args) # Type s<cr> to step into published object. File "/opt/Zope/instancje/oracletest288/Products/DCOracle2/SP.py", line 341, in manage_testZOracleStoredProcedure v = apply(self,[],REQUEST.form) File "/opt/Zope/instancje/oracletest288/Products/DCOracle2/SP.py", line 312, in __call__ results = apply(self._v_proc,args,kw) File "/opt/Zope/instancje/oracletest/Products/DCOracle2/DCOracle2/DCOracle2.py", line 1563, in __call__ cursor.execute(sql,__plist=args) File "/opt/Zope/instancje/oracletest/Products/DCOracle2/DCOracle2/DCOracle2.py", line 1023, in execute result = self._cursor.execute() DatabaseError: (20001, 'ORA-20001: BlaBlaBla\nORA-06512: at "TEST.WYJATEKFUNC", line 6\nORA-06512: at line 1') 2006-11-21 08:04:24 INFO _transaction abort 2006-11-21 08:04:24 INFO _transaction [<Connection at b62f228c>, <MultiObjectResourceAdapter for <Procedure at wyjatek> at -1241707860>] --Return--
/usr/local/lib/python2.3/pdb.py(992)set_trace()->None -> Pdb().set_trace() (Pdb) n /opt/Zope/2.8.8/lib/python/transaction/_transaction.py(492)abort() -> for rm in self._resources: (Pdb) l 487 self._synchronizers.map(lambda s: s.beforeCompletion(self)) 488 489 tb = None 490 logger.info(str(self._resources)) 491 import pdb;pdb.set_trace() 492 -> for rm in self._resources: 493 try: 494 rm.abort(self) 495 except: 496 if tb is None: 497 t, v, tb = sys.exc_info() (Pdb) p self._resources [<Connection at b62f228c>, <MultiObjectResourceAdapter for <Procedure at wyjatek> at -1241707860>] (Pdb) n /opt/Zope/2.8.8/lib/python/transaction/_transaction.py(493)abort() -> try: (Pdb) n /opt/Zope/2.8.8/lib/python/transaction/_transaction.py(494)abort() -> rm.abort(self) (Pdb) s --Call-- /opt/Zope/2.8.8/lib/python/ZODB/Connection.py(338)abort() -> def abort(self, transaction): (Pdb) l 333 ########################################################################## 334 335 ########################################################################## 336 # Data manager (ISavepointDataManager) methods 337 338 -> def abort(self, transaction): 339 """Abort a transaction and forget all changes.""" 340 341 # The order is important here. We want to abort registered 342 # objects before we process the cache. Otherwise, we may un-add 343 # objects added in savepoints. If they've been modified since (Pdb) n /opt/Zope/2.8.8/lib/python/ZODB/Connection.py(348)abort() -> self._abort() (Pdb) l 343 # objects added in savepoints. If they've been modified since 344 # the savepoint, then they won't have _p_oid or _p_jar after 345 # they've been unadded. This will make the code in _abort 346 # confused. 347 348 -> self._abort() 349 350 if self._savepoint_storage is not None: 351 self._abort_savepoint() 352 353 self._tpc_cleanup() (Pdb) s --Call-- /opt/Zope/2.8.8/lib/python/ZODB/Connection.py(355)_abort() -> def _abort(self): (Pdb) n /opt/Zope/2.8.8/lib/python/ZODB/Connection.py(358)_abort() -> for obj in self._registered_objects: (Pdb) l 353 self._tpc_cleanup() 354 355 def _abort(self): 356 """Abort a transaction and forget all changes.""" 357 358 -> for obj in self._registered_objects: 359 oid = obj._p_oid 360 assert oid is not None 361 if oid in self._added: 362 del self._added[oid] 363 del obj._p_jar (Pdb) p self._registered_objects [<Procedure at wyjatek>] (Pdb) n /opt/Zope/2.8.8/lib/python/ZODB/Connection.py(359)_abort() -> oid = obj._p_oid (Pdb) n /opt/Zope/2.8.8/lib/python/ZODB/Connection.py(360)_abort() -> assert oid is not None (Pdb) n /opt/Zope/2.8.8/lib/python/ZODB/Connection.py(361)_abort() -> if oid in self._added: (Pdb) p self._added {} (Pdb) l 356 """Abort a transaction and forget all changes.""" 357 358 for obj in self._registered_objects: 359 oid = obj._p_oid 360 assert oid is not None 361 -> if oid in self._added: 362 del self._added[oid] 363 del obj._p_jar 364 del obj._p_oid 365 else: 366 (Pdb) n /opt/Zope/2.8.8/lib/python/ZODB/Connection.py(386)_abort() -> self._cache.invalidate(oid) (Pdb) l 381 # go ahead and invalidate now. Fortunately, it's 382 # pretty unlikely that the object we are invalidating 383 # was invalidated by another thread, so the risk of a 384 # reread is pretty low. 385 386 -> self._cache.invalidate(oid) 387 388 def _tpc_cleanup(self): 389 """Performs cleanup operations to support tpc_finish and tpc_abort.""" 390 self._conflicts.clear() 391 if not self._synch: (Pdb) s /opt/Zope/2.8.8/lib/python/ZODB/Connection.py(358)_abort() -> for obj in self._registered_objects: (Pdb) n --Return-- /opt/Zope/2.8.8/lib/python/ZODB/Connection.py(358)_abort()->None -> for obj in self._registered_objects: (Pdb) n /opt/Zope/2.8.8/lib/python/ZODB/Connection.py(350)abort() -> if self._savepoint_storage is not None: (Pdb) l 345 # they've been unadded. This will make the code in _abort 346 # confused. 347 348 self._abort() 349 350 -> if self._savepoint_storage is not None: 351 self._abort_savepoint() 352 353 self._tpc_cleanup() 354 355 def _abort(self): (Pdb) n /opt/Zope/2.8.8/lib/python/ZODB/Connection.py(353)abort() -> self._tpc_cleanup() (Pdb) s --Call-- /opt/Zope/2.8.8/lib/python/ZODB/Connection.py(388)_tpc_cleanup() -> def _tpc_cleanup(self): (Pdb) n /opt/Zope/2.8.8/lib/python/ZODB/Connection.py(390)_tpc_cleanup() -> self._conflicts.clear() (Pdb) l 385 386 self._cache.invalidate(oid) 387 388 def _tpc_cleanup(self): 389 """Performs cleanup operations to support tpc_finish and tpc_abort.""" 390 -> self._conflicts.clear() 391 if not self._synch: 392 self._flush_invalidations() 393 self._needs_to_join = True 394 self._registered_objects = [] 395 (Pdb) n /opt/Zope/2.8.8/lib/python/ZODB/Connection.py(391)_tpc_cleanup() -> if not self._synch: (Pdb) n /opt/Zope/2.8.8/lib/python/ZODB/Connection.py(393)_tpc_cleanup() -> self._needs_to_join = True (Pdb) n /opt/Zope/2.8.8/lib/python/ZODB/Connection.py(394)_tpc_cleanup() -> self._registered_objects = [] (Pdb) n --Return-- /opt/Zope/2.8.8/lib/python/ZODB/Connection.py(394)_tpc_cleanup()->None -> self._registered_objects = [] (Pdb) n --Return-- /opt/Zope/2.8.8/lib/python/ZODB/Connection.py(353)abort()->None -> self._tpc_cleanup() (Pdb) n /opt/Zope/2.8.8/lib/python/transaction/_transaction.py(492)abort() -> for rm in self._resources: (Pdb) n /opt/Zope/2.8.8/lib/python/transaction/_transaction.py(493)abort() -> try: (Pdb) n /opt/Zope/2.8.8/lib/python/transaction/_transaction.py(494)abort() -> rm.abort(self) (Pdb) p rm <MultiObjectResourceAdapter for <Procedure at wyjatek> at -1241707860> (Pdb) s --Call-- /opt/Zope/2.8.8/lib/python/transaction/_transaction.py(563)abort() -> def abort(self, txn): (Pdb) l 558 self.ncommitted += 1 559 560 def tpc_vote(self, txn): 561 self.manager.tpc_vote(txn) 562 563 -> def abort(self, txn): 564 tb = None 565 for o in self.objects: 566 try: 567 self.manager.abort(o, txn) 568 except: (Pdb) n /opt/Zope/2.8.8/lib/python/transaction/_transaction.py(564)abort() -> tb = None (Pdb) n /opt/Zope/2.8.8/lib/python/transaction/_transaction.py(565)abort() -> for o in self.objects: (Pdb) p self.objects [<Shared.DC.ZRDB.TM.Surrogate instance at 0xb5fd0c6c>] (Pdb) n /opt/Zope/2.8.8/lib/python/transaction/_transaction.py(566)abort() -> try: (Pdb) n /opt/Zope/2.8.8/lib/python/transaction/_transaction.py(567)abort() -> self.manager.abort(o, txn) (Pdb) s --Call-- /opt/Zope/instancje/oracletest288/Products/DCOracle2/SP.py(158)abort() -> def abort(self, *ignored): (Pdb) l 153 154 if self._v_finalize: 155 try: self._finish() 156 finally: self._v_registered=0 157 158 -> def abort(self, *ignored): 159 try: self._abort() 160 finally: self._v_registered=0 161 162 tpc_abort = abort 163 (Pdb) p self._v_db *** AttributeError: <exceptions.AttributeError instance at 0xb5fd09ec> (Pdb) n /opt/Zope/instancje/oracletest288/Products/DCOracle2/SP.py(159)abort() -> try: self._abort() (Pdb) s --Call-- /opt/Zope/instancje/oracletest288/Products/DCOracle2/SP.py(219)_abort() -> def _abort(self, *ignored): (Pdb) l 214 self._v_db.db.commit() 215 except Exception, e: 216 logger.error( 'Exception in SP.py, _finish: %s' % (e) ) 217 print 'Exception in SP.py, _finish: %s' % (e) 218 219 -> def _abort(self, *ignored): 220 try: 221 logger.info('_abort') 222 self._v_db.db.rollback() 223 except Exception, e: 224 logger.error( 'Exception in SP.py, _abort: %s' % (e) ) (Pdb) p self._v_db *** AttributeError: <exceptions.AttributeError instance at 0xb603988c> (Pdb) n /opt/Zope/instancje/oracletest288/Products/DCOracle2/SP.py(220)_abort() -> try: (Pdb) n /opt/Zope/instancje/oracletest288/Products/DCOracle2/SP.py(221)_abort() -> logger.info('_abort') (Pdb) n 2006-11-21 08:08:18 INFO DCOracle2 - SP.py _abort /opt/Zope/instancje/oracletest288/Products/DCOracle2/SP.py(222)_abort() -> self._v_db.db.rollback() (Pdb) n AttributeError: '_v_db' /opt/Zope/instancje/oracletest288/Products/DCOracle2/SP.py(222)_abort() -> self._v_db.db.rollback() (Pdb) l 217 print 'Exception in SP.py, _finish: %s' % (e) 218 219 def _abort(self, *ignored): 220 try: 221 logger.info('_abort') 222 -> self._v_db.db.rollback() 223 except Exception, e: 224 logger.error( 'Exception in SP.py, _abort: %s' % (e) ) 225 226 def description(self): 227 if getattr(self,"_v_proc",None) is None: self._connect() (Pdb) n /opt/Zope/instancje/oracletest288/Products/DCOracle2/SP.py(223)_abort() -> except Exception, e: (Pdb) n /opt/Zope/instancje/oracletest288/Products/DCOracle2/SP.py(224)_abort() -> logger.error( 'Exception in SP.py, _abort: %s' % (e) ) (Pdb) n 2006-11-21 08:08:52 ERROR DCOracle2 - SP.py Exception in SP.py, _abort: _v_db --Return--
/opt/Zope/instancje/oracletest288/Products/DCOracle2/SP.py(224)_abort()->None -> logger.error( 'Exception in SP.py, _abort: %s' % (e) ) (Pdb) n
/opt/Zope/instancje/oracletest288/Products/DCOracle2/SP.py(160)abort() -> finally: self._v_registered=0 (Pdb) n --Return--
/opt/Zope/instancje/oracletest288/Products/DCOracle2/SP.py(160)abort()->None -> finally: self._v_registered=0 (Pdb) n
/opt/Zope/2.8.8/lib/python/transaction/_transaction.py(565)abort() -> for o in self.objects: (Pdb) n /opt/Zope/2.8.8/lib/python/transaction/_transaction.py(575)abort() -> if tb is not None: (Pdb) n --Return-- /opt/Zope/2.8.8/lib/python/transaction/_transaction.py(575)abort()->None -> if tb is not None: (Pdb) n /opt/Zope/2.8.8/lib/python/transaction/_transaction.py(492)abort() -> for rm in self._resources: (Pdb) n /opt/Zope/2.8.8/lib/python/transaction/_transaction.py(501)abort() -> if self._manager: (Pdb) n /opt/Zope/2.8.8/lib/python/transaction/_transaction.py(502)abort() -> self._manager.free(self) (Pdb) l 497 t, v, tb = sys.exc_info() 498 self.log.error("Failed to abort resource manager: %s", 499 rm, exc_info=sys.exc_info()) 500 501 if self._manager: 502 -> self._manager.free(self) 503 504 self._synchronizers.map(lambda s: s.afterCompletion(self)) 505 506 self.log.debug("abort") 507 (Pdb) p self._manager <transaction._manager.ThreadTransactionManager object at 0xb715aaec> (Pdb) n /opt/Zope/2.8.8/lib/python/transaction/_transaction.py(504)abort() -> self._synchronizers.map(lambda s: s.afterCompletion(self)) (Pdb) n /opt/Zope/2.8.8/lib/python/transaction/_transaction.py(506)abort() -> self.log.debug("abort") (Pdb) c 2006-11-21 08:09:33 DEBUG txn.16386 abort 2006-11-21 08:09:33 DEBUG txn.16386 new transaction 2006-11-21 08:09:33 INFO _transaction abort 2006-11-21 08:09:33 INFO _transaction [] --Return-- /usr/local/lib/python2.3/pdb.py(992)set_trace()->None -> Pdb().set_trace()
Maciej Wisniowski wrote at 2006-11-21 09:56 +0100:
In fact, the "_v_database_connection" is *NOT* necessary to abort(rollback) commit the transaction:
I have procedure WYJATEK in Oracle that simply raises exception. ....
You have convinced me.... Something silly is happening in the storage procedure handling of DCO2.
Is this because of: self._cache.invalidate(oid)?
"invalidate" invalidates (ghosts) the passed in object(s). Invalidation means that the object looses all its content (including volatile variables) and must be reloaded from the ZODB on the next access (of course, the "_v_" variables are lost). -- Dieter
You have convinced me....
Something silly is happening in the storage procedure handling of DCO2.
I've checked same function but with ZSQLMethod: select wyjatek() from dual In this case I have only one resource manager that is being aborted: 2006-11-22 06:34:04 INFO _transaction abort 2006-11-22 06:34:04 INFO _transaction [<MultiObjectResourceAdapter for <Products.DCOracle2.db.DB instance at 0xb6bee28c> at -1241336788>] --Return--
/usr/local/lib/python2.3/pdb.py(992)set_trace()->None -> Pdb().set_trace() (Pdb) n /opt/Zope/2.8.8/lib/python/transaction/_transaction.py(492)abort() -> for rm in self._resources: (Pdb) p self._resources [<MultiObjectResourceAdapter for <Products.DCOracle2.db.DB instance at 0xb6bee28c> at -1241336788>]
and everything is OK then -- Maciej Wisniowski
Dieter Maurer said the following on 11/17/2006 08:28 PM:
Dario Lopez-Kästen wrote at 2006-11-17 13:07 +0100:
... yes, we sometimes get deadlocks in the database, net outages and general misbehavior of DCO2.
But "DCO2" is not responsible for your deadlocks...
yes, I know that, we've had other problems with DCO2 (it has been a while since I researched this, and our current brute force solution works for us). We do not use Stored Procedures (that are called from Zope anyway). We have tracked the deadlocks down to a simple select-update pair that happens all the time, but we cannot understand why that woudl cause a problem - the sql is a simple as it gets and cannot be made any simpler. We are not sure if it is DCO2 that is in error or if it is that particular database instance. We are going to migrate to a new 10g instance and see if that helps. We do know that the deadlocks have increased as application usage has increased (it has increased by a factor 2 for each year). /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
Dario Lopez-Kästen wrote:
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.
Indeed, I needed solid reproducible test cases and my expertise in generating these in the weird world of Zope 2's DA's was not so good back then ;-)
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).
This sounds like an excellent plan, and one which I'm hoping to explore with a pure Zope 3 project I'm working on. I'd recommend having a look at this: http://svn.zope.org/z3c.zalchemy/ ...which should still mean you don't have to worry about transactions :-) cheers, Chris -- Simplistix - Content Management, Zope & Python Consulting - http://www.simplistix.co.uk
rieh25 wrote at 2006-11-16 12:12 -0800:
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.
Many DA catch the exceptions that result from a lost connection and then reconnect automatically. I have seen such code in the "DCOracle2" DA as well. Note, that simply reconnecting and redoing the query is conceptionally wrong. It may lead to only half committed transactions. After the reconnect, an exception derived from "ZODB.POSException.ConflictError" should be raised which causes the ZPublisher to redo the complete transaction. -- Dieter
participants (9)
-
Andreas Jung -
Chris Withers -
Dario Lopez-Kästen -
David H -
Dieter Maurer -
Jean Rodrigo Ferri -
Maciej Wisniowski -
rieh25 -
Roberto Edwins