[Zope] Leaking Oracle connections & processes
Hi guys, We are trying to build a site with Zope and Oracle, and we see again and again a situation where Oracle connections and processes are being leaked: the more we work, the more there are, until we reach Oracle's limit for number of processes. At this point, the Connection objects close on their own, and you can't even open SQL*Plus until Zope is closed or restarted. We use Oracle's default limit on the number of processes, which is 50. We have about three users (developers), two Oracle connections in the application (one for SqlSessions and one for other data), and although we use some devious multithreading (some of our transactions call an external method which essentially URLRetrieve()s a URL from our own Zope server in a separate thread), 50 open connections should be more than enough. I have checked a little before the disaster, and it seems that not all those processes actually have connections; the problem may be unrelated to open connections, but just to processes. We do one fishy thing that may also be related: We keep some long raw data; we keep it all in one table, and other tables which need long columns hold pointers (indices) into this table. To put data into this table from Zope, we use an external method SaveLONG, which uses the database connection to access the DB (in order to use the transactionality of the whole thing). We use it in ZSQL methods as follows: INSERT INTO items ( field, long_field_ptr ) VALUES ( ) SaveLONG saves the long value in the long data table and returns its index in the table. The source for SaveLONG is this. It relies on a DB trigger to populate the LONG_ID field from a sequence. long_table_name = 'LONG_DATA' long_column_name = 'LONG_DATA' long_seq_name = 'SEQ_LONG_DATA' index_column_name = 'LONG_ID' save_command = ''' INSERT INTO %s (%s) VALUES (:content) '''% (long_table_name,long_column_name) get_key_command = 'SELECT %s.currval FROM dual' % long_seq_name # # Put a long raw on the DB # def SaveLONG(self,connection, content): connection = connection._v_database_connection.db # the real DB connection object connection.execute(save_command, content=dbi.dbiRaw(content)) connection.execute(get_key_command) return connection.fetchone()[0] We're using Zope 2.2.4 with ZOracleDA 2.1.0 and DCOracle 1.3.2 with Oracle 8.0.5 on an intel-linux (RH). Can anyone see anything wrong with what we're doing? Is anyone else experiencing this kind of problems? Thanks in advance, Shai ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
[Zope] Re: Leaking Oracle connections & processes
Shai Berger wrote: > I have checked a little before the disaster, and it seems > that not all those processes actually have connections; the problem > may be unrelated to open connections, but just to processes. > A little progress: We just had it happen again, and checked to see how many live processes and how many zombies were involved. pstree showed 108 Zope-spawned Oracle processes, but ps showed only 55 zombies. I assume, therefore, that the problem is not because of zombies; there were over 50 live processes accessing oracle. The accumulation of zombies is a problem in itself, of course, but I understand it is also a known problem. And from what I see, it is a separate problem. Our impression here -- based on not much more than gut feeling -- is that the problem is related to abandoned transactions, where an exception is raised. Thanks, Dieter, for your earlier response -- we are trying a larger cache now, and we'll see how things go. Have fun, Shai. ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
[Zope] Re: Leaking Oracle connections & processes
Dieter Maurer wrote: > > Shai Berger writes: > > ... excess Oracle processes and zombies ... > I have checked our installation and can confirm that > both problems have gone. > > We do not yet know what caused the excess Oracle processes. > After an increase of Zope's ZODB cache and an upgrade > to the newest DCOracle, the problem simply went away. > Do you have a DCOracle version later than 1.3.2? We use 1.3.2; we have increased the cache from 400 (the default) to 1000, to no avail. Our ZODB size, last I checked, was ~40M. I remember you mentioning that yours was less then 10M, I think; how much cache do you use? > We do know what caused our zombie processes: > a bug in the Oracle Java runtime library: > they had added a "NOWAIT" flag to their "wait" calls. > This ensured that a "wait" call does not release > the Zombie child but keeps it in its state. > As a consequence, > each process started from Java became a Zombie > process. Zope starts Oracle processes from Java??? All the zombies we see are Zope-spawned... a Zope zombie-collection problem has been reported independently of Oracle. > As Oracle reported, the problem was introduced in 8.1.6 > and should be fixed in 8.1.7. > ...which confuses me, because we use 8.0.5. Thanks, Shai. ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] REPOST: dtml-with doesn't work
Philip has already made it clear that this was not his problem, but still, Dieter Maurer wrote: > Just a remark: > > SQL methods do *NOT* look at the DTML namespace *AT ALL*, > just at REQUEST (or the expliciitly passed keyword arguments). > > Thus, "dtml-with", "dtml-let" and friends are all ineffective > with respect to ZSQL methods. > My experience is somewhat different: ZSQL methods in general *DO* look at the namespace just like normal DTML methods, with two exceptions: 1) The major one, is that Dieter's remark does describe (almost) correctly the behavior of and . These two tags only look at a very limited local namespace, initialized with the REQUEST and passed arguments; but even this namespace may be modified, *INSIDE* the ZSQL method, with and . However, other tags behave quite normally -- which means that a lot of the problems can be bypassed by thoughtful use of , especially using the sql_quote modifier (BTW, it is by virtue of this that you can call other methods -- even other ZSQL methods -- from within a ZSQL method). 2) Changing REQUEST does *not* update the local namespace, that is, within a ZSQL method, doesn't work; but Does. Hope this helps, Shai. ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
[Zope] DCOracle bug?
Hi guys, As I have written before, we are using DCOracle 1.3.2 / Oracle 8.0.5 on a Linux/i386. We need some LONG RAW columns, and we hold them in a special table long_data, with two columns, long_id (int) and long_data (long raw). Other tables where long data is needed hold references to this table. We have recently encountered a peculiar behavior: In Zope, if we issue a query which selects many rows with one long column each (essentially, select * from long_data ), it works; if the query selects more than one column, but only one row (essentially, select a.*,b.* from long_data a, long_data b where a.long_id=17 and b.long_id=34 ), it also works; but if we select multiple rows with multiple long columns (as in select a.*,b.* from long_data a, long_data b ) we get an Oracle error indicating a buffer is not allocated to receive the data. Has anyone seen anything like this before? Thanks, Shai. ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
[Zope] Raw post data
Hi guys, For several reasons, I would like to have direct access to raw posted data in my dtml-code (an analogue of QUERY_STRING). Can anyone please show me the light? Thanks, Shai. ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
RE: [Zope] hasRole bug or feature in 2.2.?
Dieter writes: > > If I can't safely assume any of the above, would I be better off using a > > session product to track a user after log in so I can determine their roles > > from an unprotected document? Any other ways? > If the session product uses cookies, you will have a situation > similar to cookie based authentication. Otherwise, you > will need to shift the session id often between query string > and hidden variable which is a bit tedious. One relatively less tedious way to do this would be to put the session id in the URL rather than in forms; you can do this by using an access rule along the lines of: (assuming the next component of the path is the session id, remove it from the traversal path) (somehow make sure that the session_id is valid and revive the session object. With SQLSession, may be done by (path in the next line is supposed to be replaced by something which, preferebly dynamically, retrieves the path traversed up to this point. This is needed so the session id shows up in URLs generated down the tree) What this does is translate a url of the form http://server.com/123456/real/path to http://server.com/real/path for resource-search purposes, while making sure that all calls to absolute_url() return urls of the form http://server.com/123456/... . This means that as long as you rely on absolute_url rather than relative links, you're essentially done. This ignores session initialization first time, but I hope the general idea of how to do this is enough. I don't have a live example at my fingertips, but something a lot like this was done here some time ago. Have fun, Shai. ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
[Zope] Zope gets stuck
Hi guys, We've repeatedly encountered a weird situation where Zope gets stuck. Then, when we kill one of the Zope processes, it is released and answers requests. Has anybody encountered anything like this? I know this is a little short on information, but we are still trying to figure more ourselves. The stats: Zope version: Zope 2.2.5 (binary release, python 1.5.2, linux2-x86) Python version: 1.5.2 (#10, Dec 6 1999, 12:16:27) [GCC 2.7.2.3] System Platform: linux2 We're using Oracle and ZOracleDA too, but this time they seem not to be the culprits... Thanks, Shai. ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
[Zope] A little sqltest patch: op=ne multiple
Hi guys, The following trivial patch lets dtml-sqltest make sense of multiple inequality tests, that is: Currently has the funny behavior of rendering name<>val when passed a single value, and name in (val1,val2) when a list is passed. With this fix, the latter case becomes name not in (val1,val2) (It might be considered a good idea to take care of the other ops too, but that would be non-trivial ;-) Have fun, Shai. *** sqltest.py Wed Jan 31 19:42:15 2001 --- old_sqltest.py Tue May 30 18:46:55 2000 *** *** 222,229 if len(vs) > 1: vs=join(map(str,vs),', ') - if (self.op == comparison_operators['ne']): - return "%s not in (%s)" % (self.column,vs) return "%s in (%s)" % (self.column,vs) return "%s %s %s" % (self.column, self.op, vs[0]) --- 222,227 ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )