gmane.comp.python.cherrypy

JB | 15 Nov 00:17

occasional database errors


I have written a web application for my company using cherrypy, python
2.5 and mysql. I access my DB with mySQLdb python module for the app
and with phpMyAdmin for the management.

The app is hosted on a machine within our network domain and is
available internally only. In fact it's not even hosted on a server
box, just run on a machine and accessed by the machines name
http://computername/webapp.

I started to get random database connection errors from mySQLdb lately
when I had never gotten them before:

[14/Nov/2008:12:06:57] HTTP Traceback (most recent call last):
  File "C:\Python25\lib\site-packages\cherrypy\_cprequest.py", line
606, in respond
    cherrypy.response.body = self.handler()
  File "C:\Python25\lib\site-packages\cherrypy\_cpdispatch.py", line
24, in __call__
    return self.callable(*self.args, **self.kwargs)
  File "C:/Server/htdocs/hal\HubPage.py", line 413, in index
    firstname, lastname = self.getUser(username)
  File "C:/Server/htdocs/hal\root.py", line 386, in getUser
    conn, curs = self.dbConnect()
  File "C:/Server/htdocs/hal\root.py", line 459, in dbConnect
    conn = MySQLdb.connect( host='localhost', user='root',
passwd='****', db='*****' )
  File "C:\Python25\lib\site-packages\MySQLdb\__init__.py", line 74,
in Connect
    return Connection(*args, **kwargs)

(Continue reading)

headers

Lakin Wecker | 15 Nov 00:42

Re: occasional database errors

On Fri, Nov 14, 2008 at 4:21 PM, JB <jbravado-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:

File "C:\Python25\lib\site-packages\MySQLdb\connections.py", line
170, in __init__
super(Connection, self).__init__(*args, **kwargs2)
OperationalError: (2003, "Can't connect to MySQL server on
'localhost' (10048)")

Well, the error is definitely connection related. The normal suspects are you're out of connections, or the database has crashed (isn't running anymore), or the network is down. Considering that you're connecting to localhost, I doubt the network is down.

More often than not a user receives the 500 error, can refresh a
couple times, and then all is well. So the problem seems random, and
happens throughout the whole application. The traceback above is just
one instance of this. I first thought that maybe the DB has some sort
of "max connections" setting but this isn't the issue. At most I'll
have 35-40 users on my web app at a time, though this is rare.

Are you sure this isn't the issue? In my experience this usually is the issue. How did you diagnose it to not be the issue? Even with only 34-40 users, if you have a long connection timeout and the app leaves connections open you might run into this issue frequently.

Lakin

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "cherrypy-users" group.
To post to this group, send email to cherrypy-users-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to cherrypy-users+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at http://groups.google.com/group/cherrypy-users?hl=en
-~----------~----~----~----~------~----~------~--~---

headers

Tim Roberts | 15 Nov 00:43

Re: occasional database errors


JB wrote:
> I have written a web application for my company using cherrypy, python
> 2.5 and mysql. I access my DB with mySQLdb python module for the app
> and with phpMyAdmin for the management.
>
> The app is hosted on a machine within our network domain and is
> available internally only. In fact it's not even hosted on a server
> box, just run on a machine and accessed by the machines name
> http://computername/webapp.
>
> I started to get random database connection errors from mySQLdb lately
> when I had never gotten them before:
>
> [14/Nov/2008:12:06:57] HTTP Traceback (most recent call last):
>   File "C:\Python25\lib\site-packages\cherrypy\_cprequest.py", line
> 606, in respond
>     cherrypy.response.body = self.handler()
>   File "C:\Python25\lib\site-packages\cherrypy\_cpdispatch.py", line
> 24, in __call__
>     return self.callable(*self.args, **self.kwargs)
>   File "C:/Server/htdocs/hal\HubPage.py", line 413, in index
>     firstname, lastname = self.getUser(username)
>   File "C:/Server/htdocs/hal\root.py", line 386, in getUser
>     conn, curs = self.dbConnect()
>   File "C:/Server/htdocs/hal\root.py", line 459, in dbConnect
>     conn = MySQLdb.connect( host='localhost', user='root',
> passwd='****', db='*****' )
>   File "C:\Python25\lib\site-packages\MySQLdb\__init__.py", line 74,
> in Connect
>     return Connection(*args, **kwargs)
>   File "C:\Python25\lib\site-packages\MySQLdb\connections.py", line
> 170, in __init__
>     super(Connection, self).__init__(*args, **kwargs2)
> OperationalError: (2003, "Can't connect to MySQL server on
> 'localhost' (10048)")
>   

10048 is WSAEADDRINUSE.  That's odd.  Are you doing any kind of
connection management at all, or do you fetch a connection for each
request, and release it when you are done?  If you are just allowing the
connections to time out, it's possible you have run out of local port
numbers.  You might investigate one of the database connection pool
managers; since CP is threaded, you can reuse a single connection for
all of the requests handled by any one thread.

--

-- 
Tim Roberts, timr@...
Providenza & Boekelheide, Inc.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "cherrypy-users" group.
To post to this group, send email to cherrypy-users@...
To unsubscribe from this group, send email to cherrypy-users+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/cherrypy-users?hl=en
-~----------~----~----~----~------~----~------~--~---

headers

John Riddle | 15 Nov 00:59

Re: occasional database errors

I'm quite the newbie to cherrypy and database management! I would say that my problem could very very likely be my lack of connection management and that the culprit is that I'm not managing them hardly at all. Optimizing this should definitely be my next priority. Is there any literature or "best practices" information you could point me to?

Hold the laughter (because I really have no idea about best practices with this) but any information I need from my DB I simply make a connection, do a query, work with my data, and that's the end of my page/function.

This whole web app creation was basically dumped in my lap and it's been learn as you go. =P Any info would be greatly appreciated.
Thanks

On Fri, Nov 14, 2008 at 3:43 PM, Tim Roberts <timr-EQQNi8F+HVEAvxtiuMwx3w@public.gmane.org> wrote:

JB wrote:
> I have written a web application for my company using cherrypy, python
> 2.5 and mysql. I access my DB with mySQLdb python module for the app
> and with phpMyAdmin for the management.
>
> The app is hosted on a machine within our network domain and is
> available internally only. In fact it's not even hosted on a server
> box, just run on a machine and accessed by the machines name
> http://computername/webapp.
>
> I started to get random database connection errors from mySQLdb lately
> when I had never gotten them before:
>
> [14/Nov/2008:12:06:57] HTTP Traceback (most recent call last):
> File "C:\Python25\lib\site-packages\cherrypy\_cprequest.py", line
> 606, in respond
> cherrypy.response.body = self.handler()
> File "C:\Python25\lib\site-packages\cherrypy\_cpdispatch.py", line
> 24, in __call__
> return self.callable(*self.args, **self.kwargs)
> File "C:/Server/htdocs/hal\HubPage.py", line 413, in index
> firstname, lastname = self.getUser(username)
> File "C:/Server/htdocs/hal\root.py", line 386, in getUser
> conn, curs = self.dbConnect()
> File "C:/Server/htdocs/hal\root.py", line 459, in dbConnect
> conn = MySQLdb.connect( host='localhost', user='root',
> passwd='****', db='*****' )
> File "C:\Python25\lib\site-packages\MySQLdb\__init__.py", line 74,
> in Connect
> return Connection(*args, **kwargs)
> File "C:\Python25\lib\site-packages\MySQLdb\connections.py", line
> 170, in __init__
> super(Connection, self).__init__(*args, **kwargs2)
> OperationalError: (2003, "Can't connect to MySQL server on
> 'localhost' (10048)")
>

10048 is WSAEADDRINUSE. That's odd. Are you doing any kind of
connection management at all, or do you fetch a connection for each
request, and release it when you are done? If you are just allowing the
connections to time out, it's possible you have run out of local port
numbers. You might investigate one of the database connection pool
managers; since CP is threaded, you can reuse a single connection for
all of the requests handled by any one thread.

--
Tim Roberts, timr-EQQNi8F+HVEAvxtiuMwx3w@public.gmane.org
Providenza & Boekelheide, Inc.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "cherrypy-users" group.
To post to this group, send email to cherrypy-users-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to cherrypy-users+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at http://groups.google.com/group/cherrypy-users?hl=en
-~----------~----~----~----~------~----~------~--~---

headers

Tim Roberts | 15 Nov 01:47

Re: occasional database errors

John Riddle wrote:

I'm quite the newbie to cherrypy and database management! I would say that my problem could very very likely be my lack of connection management and that the culprit is that I'm not managing them hardly at all. Optimizing this should definitely be my next priority. Is there any literature or "best practices" information you could point me to?

Hold the laughter (because I really have no idea about best practices with this) but any information I need from my DB I simply make a connection, do a query, work with my data, and that's the end of my page/function.

Well, laughter is not appropriate, because this kind of thing wouldn't occur to you unless you had seen it once.

If you haven't explicit closed the database connection, then the socket is held in a "reserved" state, based on the assumption that you might fire it up again. It can take many minutes before the socket times out and closes. During that time, the local socket number can't be used by anyone else.

If you happen to get several thousand requests before it times out, you can use up all of the local socket numbers.

I recommend that you download the DBUtils package, http://pypi.python.org/pypi/DBUtils. I have found that the DBUtils.PersistentDB object serves me well with CherryPy. I use it with Postgres, but it should work equally well with MySQL. I do this in a file called db.py:

import psycopg
from DBUtils.PersistentDB import PersistentDB

pool = PersistentDB( psycopg, 0, host='...', database='...', user='...', password='...' )

def cursor():
    db = pool.connection()
    db.rollback()
    return db.cursor()

def rollback():
    pool.connection().rollback()

def commit():
    pool.connection().commit()

Then, in my CherryPy server, I do:
     import db
     ...
    def index( self, ... ):
            cur = db.cursor()
            ...

-- Tim Roberts, timr-EQQNi8F+HVEAvxtiuMwx3w@public.gmane.org Providenza & Boekelheide, Inc.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "cherrypy-users" group.
To post to this group, send email to cherrypy-users-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to cherrypy-users+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at http://groups.google.com/group/cherrypy-users?hl=en
-~----------~----~----~----~------~----~------~--~---

headers

John Riddle | 20 Nov 02:25

Re: occasional database errors

I'd like to try and work out a solution using mySQLdb first, and if that fails I'll switch modules.

Can you explain to me the basic idea of rollback? MySQLdb has a conn.rollback() definition too, though I'm not 100% on what it's actually doing. It looks like above you perform this rollback everytime a connection to the DB is established? I have a function that creates a connection and cursor object and returns them, should I be calling rollback inside this function as well?

I also went through all my code and put in the line "conn.close()" anywhere a connection was being made to the DB. Even with this manual close I'm still getting the same error. Anymore thoughts?

-John

On Fri, Nov 14, 2008 at 4:47 PM, Tim Roberts <timr-EQQNi8F+HVEAvxtiuMwx3w@public.gmane.org> wrote:

John Riddle wrote:
I'm quite the newbie to cherrypy and database management! I would say that my problem could very very likely be my lack of connection management and that the culprit is that I'm not managing them hardly at all. Optimizing this should definitely be my next priority. Is there any literature or "best practices" information you could point me to?

Hold the laughter (because I really have no idea about best practices with this) but any information I need from my DB I simply make a connection, do a query, work with my data, and that's the end of my page/function.

Well, laughter is not appropriate, because this kind of thing wouldn't occur to you unless you had seen it once.

If you haven't explicit closed the database connection, then the socket is held in a "reserved" state, based on the assumption that you might fire it up again. It can take many minutes before the socket times out and closes. During that time, the local socket number can't be used by anyone else.

If you happen to get several thousand requests before it times out, you can use up all of the local socket numbers.

I recommend that you download the DBUtils package, http://pypi.python.org/pypi/DBUtils. I have found that the DBUtils.PersistentDB object serves me well with CherryPy. I use it with Postgres, but it should work equally well with MySQL. I do this in a file called db.py:
import psycopg
from DBUtils.PersistentDB import PersistentDB

pool = PersistentDB( psycopg, 0, host='...', database='...', user='...', password='...' )

def cursor():
    db = pool.connection()
    db.rollback()
    return db.cursor()

def rollback():
    pool.connection().rollback()

def commit():
    pool.connection().commit()

Then, in my CherryPy server, I do:
     import db
     ...
    def index( self, ... ):
            cur = db.cursor()
            ...

-- Tim Roberts, timr-EQQNi8F+HVEAvxtiuMwx3w@public.gmane.org Providenza & Boekelheide, Inc.

headers

Tim Roberts | 20 Nov 02:54

Re: occasional database errors

John Riddle wrote:
> I'd like to try and work out a solution using mySQLdb first, and if
> that fails I'll switch modules.
>
> Can you explain to me the basic idea of rollback? MySQLdb has a
> conn.rollback() definition too, though I'm not 100% on what it's
> actually doing. It looks like above you perform this rollback
> everytime a connection to the DB is established? I have a function
> that creates a connection and cursor object and returns them, should I
> be calling rollback inside this function as well?

The issue is with "transactions".  When I get a cursor and make a bunch
of changes, nothing is actually committed to the permanent database
until I do a "commit".  If something does wrong in the middle, so that I
need to cancel the whole set, I do a "rollback".  Many database
connectors use "autocommit", so that every action you take is
immediately made permanent.  In that case, the commit and rollback calls
are not necessary.

The extra rollbacks in my script are, ahem, a bit of superstition.  I
was having issues where one transaction would get an error (which
invalidates all future operations until you get a rollback or commit),
and fail without committing, and because of the pooling, the NEXT web
request would get an error.  I don't think this was the right solution,
but I never backed it out.

> I also went through all my code and put in the line "conn.close()"
> anywhere a connection was being made to the DB. Even with this manual
> close I'm still getting the same error. Anymore thoughts?

No.  Depending on how they are created, a socket can remain in a "dead
not not buried" state even after the connection is closed, but I have to
admit I'm reaching here.

--

-- 
Tim Roberts, timr@...
Providenza & Boekelheide, Inc.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "cherrypy-users" group.
To post to this group, send email to cherrypy-users@...
To unsubscribe from this group, send email to cherrypy-users+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/cherrypy-users?hl=en
-~----------~----~----~----~------~----~------~--~---

headers

John Riddle | 20 Nov 03:12

Re: occasional database errors

Some more googling suggested I run a netstat and see if I have a bunch of sockets in the "TIME_WAIT" state, which I do.

I'm reading through http://smallvoid.com/article/winnt-tcpip-max-limit.html to see if it reveals anything useful.

On Wed, Nov 19, 2008 at 5:54 PM, Tim Roberts <timr-EQQNi8F+HVEAvxtiuMwx3w@public.gmane.org> wrote:

John Riddle wrote:
> I'd like to try and work out a solution using mySQLdb first, and if
> that fails I'll switch modules.
>
> Can you explain to me the basic idea of rollback? MySQLdb has a
> conn.rollback() definition too, though I'm not 100% on what it's
> actually doing. It looks like above you perform this rollback
> everytime a connection to the DB is established? I have a function
> that creates a connection and cursor object and returns them, should I
> be calling rollback inside this function as well?

The issue is with "transactions". When I get a cursor and make a bunch
of changes, nothing is actually committed to the permanent database
until I do a "commit". If something does wrong in the middle, so that I
need to cancel the whole set, I do a "rollback". Many database
connectors use "autocommit", so that every action you take is
immediately made permanent. In that case, the commit and rollback calls
are not necessary.

The extra rollbacks in my script are, ahem, a bit of superstition. I
was having issues where one transaction would get an error (which
invalidates all future operations until you get a rollback or commit),
and fail without committing, and because of the pooling, the NEXT web
request would get an error. I don't think this was the right solution,
but I never backed it out.

> I also went through all my code and put in the line "conn.close()"
> anywhere a connection was being made to the DB. Even with this manual
> close I'm still getting the same error. Anymore thoughts?

No. Depending on how they are created, a socket can remain in a "dead
not not buried" state even after the connection is closed, but I have to
admit I'm reaching here.

--
Tim Roberts, timr-EQQNi8F+HVEAvxtiuMwx3w@public.gmane.org
Providenza & Boekelheide, Inc.

headers

John Riddle | 20 Nov 04:02

Re: occasional database errors

I think I may have this a little more pinpointed.
I can replicate the error with this little snippet:

import cherrypy, MySQLdb

for i in range(10000):
    conn = MySQLdb.connect( host='localhost', user='*****', passwd='*****', db='mydb' )
    curs = conn.cursor()
    print i
    conn.close()

It tops out (throws the same error) at 3945 connections, which is close enough to the default windows tcpip MaxUserPorts setting of 3977.

Currently, anywhere I need information from my db I create a connection and cursor object. Should I be creating one connection object per user session instead? What's the "usual" workflow?

I'm also looking into registry keys I can modify to handle the socket connections staying alive.

On Wed, Nov 19, 2008 at 6:12 PM, John Riddle <jbravado-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:

Some more googling suggested I run a netstat and see if I have a bunch of sockets in the "TIME_WAIT" state, which I do.

I'm reading through http://smallvoid.com/article/winnt-tcpip-max-limit.html to see if it reveals anything useful.

On Wed, Nov 19, 2008 at 5:54 PM, Tim Roberts <timr-EQQNi8F+HVEAvxtiuMwx3w@public.gmane.org> wrote:

John Riddle wrote:
> I'd like to try and work out a solution using mySQLdb first, and if
> that fails I'll switch modules.
>
> Can you explain to me the basic idea of rollback? MySQLdb has a
> conn.rollback() definition too, though I'm not 100% on what it's
> actually doing. It looks like above you perform this rollback
> everytime a connection to the DB is established? I have a function
> that creates a connection and cursor object and returns them, should I
> be calling rollback inside this function as well?

The issue is with "transactions". When I get a cursor and make a bunch
of changes, nothing is actually committed to the permanent database
until I do a "commit". If something does wrong in the middle, so that I
need to cancel the whole set, I do a "rollback". Many database
connectors use "autocommit", so that every action you take is
immediately made permanent. In that case, the commit and rollback calls
are not necessary.

The extra rollbacks in my script are, ahem, a bit of superstition. I
was having issues where one transaction would get an error (which
invalidates all future operations until you get a rollback or commit),
and fail without committing, and because of the pooling, the NEXT web
request would get an error. I don't think this was the right solution,
but I never backed it out.

> I also went through all my code and put in the line "conn.close()"
> anywhere a connection was being made to the DB. Even with this manual
> close I'm still getting the same error. Anymore thoughts?

No. Depending on how they are created, a socket can remain in a "dead
not not buried" state even after the connection is closed, but I have to
admit I'm reaching here.

--
Tim Roberts, timr-EQQNi8F+HVEAvxtiuMwx3w@public.gmane.org
Providenza & Boekelheide, Inc.

headers

Tim Roberts | 20 Nov 19:12

Re: occasional database errors

John Riddle wrote:
> I think I may have this a little more pinpointed.
> I can replicate the error with this little snippet:
>
> import cherrypy, MySQLdb
>
> for i in range(10000):
>     conn = MySQLdb.connect( host='localhost', user='*****',
> passwd='*****', db='mydb' )
>     curs = conn.cursor()
>     print i
>     conn.close()
>
> It tops out (throws the same error) at 3945 connections, which is
> close enough to the default windows tcpip MaxUserPorts setting of 3977.
>
> Currently, anywhere I need information from my db I create a
> connection and cursor object. Should I be creating one connection
> object per user session instead? What's the "usual" workflow?

Connections are somewhat heavyweight; it can take a lot of time
(relatively speaking) to establish a connection.  As a result, your
design should minimize the number of connections.  In a CGI scheme, you
generally create one connection per request, then pull as many cursors
as you need from that one connection.  In a long-term process, like
cherrypy, you can do even better using connection pooling, as I
described earlier.

> I'm also looking into registry keys I can modify to handle the socket
> connections staying alive.

That works for your system, but you certainly do not want to go around
messing with the network configurations on other workstations.  The
RIGHT answer is to use connection pooling.

--

-- 
Tim Roberts, timr@...
Providenza & Boekelheide, Inc.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "cherrypy-users" group.
To post to this group, send email to cherrypy-users@...
To unsubscribe from this group, send email to cherrypy-users+unsubscribe@...
For more options, visit this group at http://groups.google.com/group/cherrypy-users?hl=en
-~----------~----~----~----~------~----~------~--~---

headers

Paweł Stradomski | 20 Nov 10:18

Re: occasional database errors


W liście John Riddle z dnia czwartek 20 listopada 2008:
> I think I may have this a little more pinpointed.
> I can replicate the error with this little snippet:
>
> import cherrypy, MySQLdb
>
> for i in range(10000):
>     conn = MySQLdb.connect( host='localhost', user='*****', passwd='*****',
> db='mydb' )
>     curs = conn.cursor()
>     print i
>     conn.close()
>
> It tops out (throws the same error) at 3945 connections, which is close
> enough to the default windows tcpip MaxUserPorts setting of 3977.
>

You could try creating one db connection per cherrypy thread. Of course you 
would need to make sure of proper cleanup (maybe using this hack presented 
above with starting request processing with a rollback()).

--

-- 
Paweł Stradomski

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "cherrypy-users" group.
To post to this group, send email to cherrypy-users@...
To unsubscribe from this group, send email to cherrypy-users+unsubscribe <at> googlegroups.com
For more options, visit this group at http://groups.google.com/group/cherrypy-users?hl=en
-~----------~----~----~----~------~----~------~--~---