[mod_python] Very odd behavior using mysqldb

Eric Brunson wrote:
> I've been working on a project using mod_python to access a mysql 
> database using the MySQLdb API.  I know mod_python is still considered 
> beta under Apache 2, so I thought I'd bring an issue to the attention of 
> the list.
> 
> I was seeing some very bizarre results when generating dynamic content 
> from the database, which I first attributed to PSP caching, but then 
> investigated further when I switched to using the publisher handler.  It 
> may be the way I've set up my modules and database connections, but I 
> haven't convinced myself that I really understand the problem and until 
> I do, I'm not going to be happy about it.  I have found a workaround 
> that I can live with, but I don't really like it that much because it 
> requires constantly connecting to and disconnection from the database.
> 
> The problem was simply getting old, wrong results interleaved with new, 
> correct results after updating the database.  Simply put, I'm displaying 
> data from a table.  My index.py imports a module for generating HTML 
> which, in turn, includes a module for doing database access.  The odd 
> thing about how I set up the database access, and I think it's pertinent 
> to the problem, was that I automatically connect to the database when I 
> import the database module.  Like this:
> 
> ----- database.py -----
> import MySQLdb
> from mod_python import apache
> 
> def Connect():
>    return MySQLdb.connect( 'mysql://me:pass@localhost/mydatabase' )
> 
> dbc = Connect()
> 
> def someExampleAccessFunction():
>    global dbc
>    curs = dbc.cursor()
>    curs.execute( "select count(0) from sometable" )
>    result = curs.fetchall()[0][0]
>    curs.close()
>    apache.log_error( "result was %s" % result )
>    return result
> 
> ----- end database.py -----
> 
> Let's disregard whether that's good programming practice or not, the 
> fact is, I could query that value several times by doing a page reload, 
> then delete a record from the database and commit the change, then 
> subsequent page loads would interleave old results with new results, 
> seemingly cyclically.
> 
> The key to my confusion is that the error logs for apache would show a 
> message for every page reload, and the result from the query would agree 
> with whatever data was displayed on the page, correct or incorrect.  
> Meanwhile a direct query to the database from the command line would 
> always return the correct result from the database.
> 
> My best guess so far is that multiple threads in apache get multiple 
> copies of mod_python, each with it's own copy of the imported database 
> module in memory and each module maintaining a different database 
> connection that is somehow caching results.  I've never seen this 
> behavior in any application until I tried this under mod_python.  I was 
> able to force the correct results to always be returned by getting rid 
> of the module variable, dbc, and moving the mysqldb.connect() call to 
> inside each function call, opening the database connection, executing my 
> sql, then closing the database connection.  This is less than optimal, 
> since I may make several database queries during each page load, so 
> later today I'll try making the database connection in the index.py, 
> then passing the connection handle to each of the calls that need it.  
> Also a little less than optimal, but tolerable.
> 
> Does anyone have any insight into this behavior?  It's more of an 
> academic question at this point, but I wonder if it may lead to an 
> improvement if we were able to identify the problem and implement a 
> transparent fix.  Also, feel free to let me know if I'm completely 
> offbase with my theory of what's going on under the hood.

Your assessment with respect to having multiple copies of mod_python, 
each with its own copy of the imported modules and data is correct. It 
is not a bug but rather the way apache works. Depending on which 
apache-mpm you are using (either prefork or worker on Fedora) and your 
configuration, you may have anywhere from a few, to dozens of 
independent child processes. Indeed if you are using the prefork mpm on 
a heavily loaded server you could have hundreds of processes. In such 
cases using a persistent db connection will likely cause problems with 
exhausting the number of available mysql connections. Persistent 
connections in such a scenario are discouraged in general - not just in 
mod_python.

As to your particular problem, I wonder if you are using transactions, 
but are not committing immediately after an update? The process which 
apache chooses to to handle a request is random. Say for example you 
update in process A without a commit. The next request may be handled by 
  another process, which will see the old data. A subsequent request may 
then come in but be handled by process A again, which will see the new 
data since it is using the same connection as that used for the update. 
Thus you will get the interleaving effect.

This is not unique to db connections. Don't be fooled into thinking that 
module-level attributes are global across your application as they are 
not. In anything other than winnt-mpm (which is a single, threaded 
process), you will have multiple, independent copies of that variable.

Opening and closing your db connection for each request is the usual 
solution, as you've discovered. It is possible to do connection pooling, 
but you need to be a little smarter about it ensure you don't run into 
problems or exhaust the number of mysql connections configured. Don't 
forget that leaving open a connection consumes memory resources as well.

This is not to say that you need to open and close the connection each 
time you want to access the database within a request. The usual method 
is to stuff your db connection into the request object itself. For example:

def handler(req):
     req.db = MySQLdb.connect( 'mysql://me:pass@localhost/mydatabase' )
     req.register_cleanup(close_db, req.db)
     delete_stuff(req)
     add_stuff(req)
     req.write('stuff done')
     return apache.OK

def add_stuff(req):
     cur = req.db.cursor()
     cur.execute("insert into stuff (foo) values ('bar')")
     cur.close()

def add_stuff(req):
     cur = req.db.cursor()
     cur.execute("DELETE from stuff where foo = 'baz'")
     cur.close()

def close_db(db):
     db.close()

Notice the use of register_cleanup() to register a function which will 
close the connection, rather than explicitly closing it. This ensures 
that your db connection will always be closed, even if an exception 
occurs somewhere in your code.

Jim