Login | Register For Free | Help
Search for: (Advanced)

Mailing List Archive: Zope: DB

mysql / zope db connection issue

 

 

Zope db RSS feed   Index | Next | Previous | View Threaded


jokeefe at jamesokeefe

Apr 4, 2006, 10:32 PM

Post #1 of 18 (5400 views)
Permalink
mysql / zope db connection issue

Hi,

I have written a series of zope pages for a person to update their
record in our mysql database. They use a zpt->python->db sql->db
driver->db flow and work pretty well.

Unfortunately, if enough people use it, the number of open db
connections grows until it hits a limit and the db stops accepting
connections. I have seen that there is a connection issue with zope
and mysql, but have not been able to find a solution. Is there one?

Also, I put in some exception handling to trap any db errors and email
the post if there are any. However, i am not sure it is working. I
performed a test to the db update from my page with the db off and got
a Database Error that wasn't caught by my exception handler. I am not
sure why since I thought Exception would be the root of Database
Errors. My code looks like this:

try:
result = context.updatePerson(**query_args)
return text
except Exception:
try:
mto = "<to address>"
mfrom = "<from address>"
subject = "Updated supporter information"
body = printed
context.MailHost.simple_send(mto, mfrom, subject, body)
print result
return text
except Exception:
errorText = '<h1>Ooooppps!</h1><p>There was an error submitting
your information to our database and our backup logging method.
Please email the <a href="mailto:webmaster@<domain>">webmaster</a>
that you got an error so that we can track it down and fix
it.</p><p>Thanks!</p>'
print errorText
return printed
return errorText

The traceback error is:

Exception Type Database Error
Exception Value updatePerson is not connected to a database

Any insights would be most welcome.

Thanks!

Jamie
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


chris at simplistix

Apr 5, 2006, 12:19 AM

Post #2 of 18 (5242 views)
Permalink
Re: mysql / zope db connection issue [In reply to]

Jamie O'Keefe wrote:
> try:
> result = context.updatePerson(**query_args)
> return text
> except Exception:

This is insane code. If you want to get mailed errors, look at the
MailingLogger product.


> The traceback error is:
>
> Exception Type Database Error
> Exception Value updatePerson is not connected to a database

What version of Zope/MySQL/mysqldb/ZMySQLDA are you using?

Chris

--
Simplistix - Content Management, Zope & Python Consulting
- http://www.simplistix.co.uk

_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


charlie at egenix

Apr 5, 2006, 2:12 AM

Post #3 of 18 (5233 views)
Permalink
Re: mysql / zope db connection issue [In reply to]

On 2006-04-05 at 07:32:46 [+0200], Jamie O'Keefe <jokeefe [at] jamesokeefe>
wrote:
> Hi,
>
> I have written a series of zope pages for a person to update their
> record in our mysql database. They use a zpt->python->db sql->db
> driver->db flow and work pretty well.
>
> Unfortunately, if enough people use it, the number of open db
> connections grows until it hits a limit and the db stops accepting
> connections. I have seen that there is a connection issue with zope
> and mysql, but have not been able to find a solution. Is there one?

How many concurrent users do you have? I suspect this is not a Zope issue.
Is it possible for you to use our mxODBCZopeDA which gives fine-grained
control over connection pools?

I think you will get further by running Zope in debug mode and seeing
exactly where the problem is, ie. why the connection is being dropped.

Charlie
--
Charlie Clark
eGenix.com

Professional Python Services directly from the Source
>>> Python/Zope Consulting and Support ... http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
________________________________________________________________________
2005-10-17: Released mxODBC.Zope.DA 1.0.9 http://zope.egenix.com/

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


dieter at handshake

Apr 5, 2006, 10:30 AM

Post #4 of 18 (5258 views)
Permalink
Re: mysql / zope db connection issue [In reply to]

Jamie O'Keefe wrote at 2006-4-5 01:32 -0400:
>I have written a series of zope pages for a person to update their
>record in our mysql database. They use a zpt->python->db sql->db
>driver->db flow and work pretty well.
>
>Unfortunately, if enough people use it, the number of open db
>connections grows until it hits a limit and the db stops accepting
>connections. I have seen that there is a connection issue with zope
>and mysql, but have not been able to find a solution. Is there one?

We are using MySQL and Zope (via "ZMySQLDB") and do not see this problem.
Apparently, Zope handles connections in principle correctly...


--
Dieter
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


jokeefe at jamesokeefe

Apr 5, 2006, 10:52 AM

Post #5 of 18 (5250 views)
Permalink
Re: mysql / zope db connection issue [In reply to]

Hi Chris

On 4/5/06, Chris Withers <chris [at] simplistix> wrote:
> Jamie O'Keefe wrote:
> > try:
> > result = context.updatePerson(**query_args)
> > return text
> > except Exception:
>
> This is insane code. If you want to get mailed errors, look at the
> MailingLogger product.

Did. What if you don't know what the errors could be?

> > The traceback error is:
> >
> > Exception Type Database Error
> > Exception Value updatePerson is not connected to a database
>
> What version of Zope/MySQL/mysqldb/ZMySQLDA are you using?

Zope is 2.6.4
MySql is 3.22.32
ZMySQLDA 2.0.8

i don't know the version of mysqldb.

Thanks.

Jamie
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


jokeefe at jamesokeefe

Apr 5, 2006, 10:54 AM

Post #6 of 18 (5238 views)
Permalink
Re: mysql / zope db connection issue [In reply to]

Hi Dieter,

On 4/5/06, Dieter Maurer <dieter [at] handshake> wrote:
> Jamie O'Keefe wrote at 2006-4-5 01:32 -0400:
> >I have written a series of zope pages for a person to update their
> >record in our mysql database. They use a zpt->python->db sql->db
> >driver->db flow and work pretty well.
> >
> >Unfortunately, if enough people use it, the number of open db
> >connections grows until it hits a limit and the db stops accepting
> >connections. I have seen that there is a connection issue with zope
> >and mysql, but have not been able to find a solution. Is there one?
>
> We are using MySQL and Zope (via "ZMySQLDB") and do not see this problem.
> Apparently, Zope handles connections in principle correctly...

What version of MySQL are you using?

One thing I just noticed is that if I connect in via the unix shell
client, quit and login again, the number of connections goes up. Not
unusual. In a slow period i will need to see if the number goes down
as well.

Jamie
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


jokeefe at jamesokeefe

Apr 5, 2006, 11:01 AM

Post #7 of 18 (5252 views)
Permalink
Re: mysql / zope db connection issue [In reply to]

> > What version of Zope/MySQL/mysqldb/ZMySQLDA are you using?
>
> Zope is 2.6.4
> MySql is 3.22.32
> ZMySQLDA 2.0.8
>
> i don't know the version of mysqldb.

I think this is 0.9.

Jamie
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


chris at simplistix

Apr 6, 2006, 12:51 AM

Post #8 of 18 (5238 views)
Permalink
Re: mysql / zope db connection issue [In reply to]

Jamie O'Keefe wrote:
> On 4/5/06, Chris Withers <chris [at] simplistix> wrote:
>> Jamie O'Keefe wrote:
>>> try:
>>> result = context.updatePerson(**query_args)
>>> return text
>>> except Exception:
>> This is insane code. If you want to get mailed errors, look at the
>> MailingLogger product.
>
> Did. What if you don't know what the errors could be?

Huh? MailingLogger mails log entries, you don't need to know what any
errors are. Just tick the box in the error_log object that says "copy
errors to the event log" and you'll get mailed everything...

>>> The traceback error is:
>>>
>>> Exception Type Database Error
>>> Exception Value updatePerson is not connected to a database
>> What version of Zope/MySQL/mysqldb/ZMySQLDA are you using?
>
> Zope is 2.6.4

Good grief. Upgrade. No-one can help you on such an ancient version.

> MySql is 3.22.32

The same is probably true for MySQL...

Chris

--
Simplistix - Content Management, Zope & Python Consulting
- http://www.simplistix.co.uk

_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


charlie at egenix

Apr 6, 2006, 2:14 AM

Post #9 of 18 (5253 views)
Permalink
Re: mysql / zope db connection issue [In reply to]

On 2006-04-06 at 09:51:49 [+0200], Chris Withers <chris [at] simplistix>
wrote:
> >>> Exception Type Database Error
> >>> Exception Value updatePerson is not connected to a database
> >> What version of Zope/MySQL/mysqldb/ZMySQLDA are you using?
> >
> > Zope is 2.6.4
>
> Good grief. Upgrade. No-one can help you on such an ancient version.

Chris, the underlying code for ZopeDA's hasn't changed for years so it
shouldn't make much difference what version of Zope he is running. There
may, of course, be other reasons for running a more recent release.

> > MySql is 3.22.32
>
> The same is probably true for MySQL...

This still should not be causing this problem. Jamie, you should be talking
to the DBA or looking at the DB side of things. Is the DB running on the
same machine as Zope or on the network? What are connection times set to?
It might simply be that MySQL is running out of connections although this
usually means that the whole machine runs slow.

Charlie
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


jokeefe at jamesokeefe

Apr 6, 2006, 7:34 AM

Post #10 of 18 (5229 views)
Permalink
Re: mysql / zope db connection issue [In reply to]

On 4/6/06, Charlie Clark <charlie [at] egenix> wrote:
> This still should not be causing this problem. Jamie, you should be talking
> to the DBA or looking at the DB side of things.

Well it is a volunteer operation, so we don't specifically have a dba,
but will talk with the person who set up the db.

> Is the DB running on the
> same machine as Zope or on the network?

Yes.

> What are connection times set to?

Do you mean the connection timeout time?

> It might simply be that MySQL is running out of connections although this
> usually means that the whole machine runs slow.

We upped the # of connections and I have seen 51 connections open at
one time. Not sure the rate at which people are accessing the form
and submitting it, but I believe it may be 25-50 a day.

Jamie
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


charlie at egenix

Apr 6, 2006, 8:53 AM

Post #11 of 18 (5242 views)
Permalink
Re: mysql / zope db connection issue [In reply to]

On 2006-04-06 at 16:34:01 [+0200], Jamie O'Keefe <jokeefe [at] jamesokeefe>
wrote:
> On 4/6/06, Charlie Clark <charlie [at] egenix> wrote:
> > This still should not be causing this problem. Jamie, you should be
> > talking
> > to the DBA or looking at the DB side of things.
>
> Well it is a volunteer operation, so we don't specifically have a dba,
> but will talk with the person who set up the db.
>
> > Is the DB running on the
> > same machine as Zope or on the network?
>
> Yes.
>
> > What are connection times set to?
>
> Do you mean the connection timeout time?

Yes. The default for these are very high which often causes problems for
applications that don't implement connection pooling. However, as I
previously said this is unlikely to be the source of the problem.

> > It might simply be that MySQL is running out of connections although this
> > usually means that the whole machine runs slow.
>
> We upped the # of connections and I have seen 51 connections open at
> one time. Not sure the rate at which people are accessing the form
> and submitting it, but I believe it may be 25-50 a day.

At that rate there is no reason on the Zope side for the connection being
dropped. There could be a problem with the underlying Python driver and thus
the ZopeDA so you might want to install the latest version of both:

http://sourceforge.net/project/showfiles.php?group_id=22307

MySQL 3.2 is still supported.

But you should also run Zope in debug mode and see what gets written to the
error log.

Charlie
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


dieter at handshake

Apr 6, 2006, 10:14 AM

Post #12 of 18 (5240 views)
Permalink
Re: mysql / zope db connection issue [In reply to]

Jamie O'Keefe wrote at 2006-4-5 13:54 -0400:
> ...
>What version of MySQL are you using?

An old one...

We switched to Postgres, MySQL is used only for legacy components.

--
Dieter
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


jokeefe at jamesokeefe

Apr 6, 2006, 11:46 AM

Post #13 of 18 (5231 views)
Permalink
Re: mysql / zope db connection issue [In reply to]

Hi Dieter,

> We switched to Postgres, MySQL is used only for legacy components.

Isn't postgres linux only? We use FreeBSD.

Jamie
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


jokeefe at jamesokeefe

Apr 6, 2006, 11:47 AM

Post #14 of 18 (5236 views)
Permalink
Re: mysql / zope db connection issue [In reply to]

Thanks for the suggestions, Charlie.

Will try as you suggested.

Jamie

On 4/6/06, Charlie Clark <charlie [at] egenix> wrote:
>
> On 2006-04-06 at 16:34:01 [+0200], Jamie O'Keefe <jokeefe [at] jamesokeefe>
> wrote:
> > On 4/6/06, Charlie Clark <charlie [at] egenix> wrote:
> > > This still should not be causing this problem. Jamie, you should be
> > > talking
> > > to the DBA or looking at the DB side of things.
> >
> > Well it is a volunteer operation, so we don't specifically have a dba,
> > but will talk with the person who set up the db.
> >
> > > Is the DB running on the
> > > same machine as Zope or on the network?
> >
> > Yes.
> >
> > > What are connection times set to?
> >
> > Do you mean the connection timeout time?
>
> Yes. The default for these are very high which often causes problems for
> applications that don't implement connection pooling. However, as I
> previously said this is unlikely to be the source of the problem.
>
> > > It might simply be that MySQL is running out of connections although this
> > > usually means that the whole machine runs slow.
> >
> > We upped the # of connections and I have seen 51 connections open at
> > one time. Not sure the rate at which people are accessing the form
> > and submitting it, but I believe it may be 25-50 a day.
>
> At that rate there is no reason on the Zope side for the connection being
> dropped. There could be a problem with the underlying Python driver and thus
> the ZopeDA so you might want to install the latest version of both:
>
> http://sourceforge.net/project/showfiles.php?group_id=22307
>
> MySQL 3.2 is still supported.
>
> But you should also run Zope in debug mode and see what gets written to the
> error log.
>
> Charlie
>


--
Jamie O'Keefe
www.jamesokeefe.org
jokeefe.typepad.com - my personal blog
jokeefe.typepad.com/green_politics/ - my political blog
jokeefe.typepad.com/nonviolence/ - my blog on nonviolence
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


lists at zopyx

Apr 6, 2006, 11:52 AM

Post #15 of 18 (5239 views)
Permalink
Re: mysql / zope db connection issue [In reply to]

--On 6. April 2006 14:46:36 -0400 Jamie O'Keefe <jokeefe [at] jamesokeefe>
wrote:

> Hi Dieter,
>
>> We switched to Postgres, MySQL is used only for legacy components.
>

Huh...google will tell you that it runs even on Windows.

-aj


-----------------------------------------------------------------------
- Andreas Jung ZOPYX Ltd. & Co KG -
- E-mail: info [at] zopyx Web: www.zopyx.com, www.zopyx.de -
-----------------------------------------------------------------------


charlie at egenix

Apr 6, 2006, 12:51 PM

Post #16 of 18 (5251 views)
Permalink
Re: mysql / zope db connection issue [In reply to]

On 2006-04-06 at 20:46:36 [+0200], Jamie O'Keefe <jokeefe [at] jamesokeefe>
wrote:
> Hi Dieter,
>
> > We switched to Postgres, MySQL is used only for legacy components.
>
> Isn't postgres linux only? We use FreeBSD.

PostgreSQL was developed at the University of California, Berkeley and runs
on a huge range of problems. I prefer it over MySQL but I am not convinced
that switching to PostgreSQL will solve your problems and, of course, will
involve all the work of migration.

Charlie
--
Charlie Clark
eGenix.com

Professional Python Services directly from the Source
>>> Python/Zope Consulting and Support ... http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
________________________________________________________________________
2005-10-17: Released mxODBC.Zope.DA 1.0.9 http://zope.egenix.com/

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


lists at zopyx

Apr 6, 2006, 9:42 PM

Post #17 of 18 (5230 views)
Permalink
Re: mysql / zope db connection issue [In reply to]

--On 6. April 2006 21:51:05 +0200 Charlie Clark <charlie [at] egenix> wrote:

>
> On 2006-04-06 at 20:46:36 [+0200], Jamie O'Keefe
> <jokeefe [at] jamesokeefe> wrote:
>> Hi Dieter,
>>
>> > We switched to Postgres, MySQL is used only for legacy components.
>>
>> Isn't postgres linux only? We use FreeBSD.
>
> PostgreSQL was developed at the University of California, Berkeley and
> runs on a huge range of problems.

problems? you mean systems :-)

-aj


dieter at handshake

Apr 7, 2006, 10:03 AM

Post #18 of 18 (5241 views)
Permalink
Re: mysql / zope db connection issue [In reply to]

Andreas Jung wrote at 2006-4-6 20:52 +0200:
>>[Dieter]
>>> We switched to Postgres, MySQL is used only for legacy components.
>
>Huh...google will tell you that it runs even on Windows.

I probably should have written: we currently use MySQL only for
remaining legacy components.
I did not want to say anything in general about MySQL.

--
Dieter
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db

Zope db RSS feed   Index | Next | Previous | View Threaded
 
 


Interested in having your list archived? Contact Gossamer Threads
 
  Web Applications & Managed Hosting Powered by Gossamer Threads Inc.