Need advices for mysqldb connection best practice

Thu Jan 20 04:04:12 EST 2011

Hi all,
I've a problem with a mysql connection. I run a client/server 
application. Every hour, clients contact the server, send datas, and the 
server updates the database. Everything works perfectly.
But after a while, I get in trouble with my db connection. I've got the 
impression my server application "mix" the cursors...
I've got this kind of strange errors in my log :
Traceback (most recent call last):
 File "/usr/local/lib/audaxis/system_serverbox.py", line 519, in 
__update_in_db
 old_serverbox.load_from_db(cursor, self.__id)
 File "/usr/local/lib/audaxis/system_serverbox.py", line 131, in 
load_from_db
 self.__uuid = row['uuid']
TypeError: 'NoneType' object is unsubscriptable
Traceback (most recent call last):
 File "/usr/local/lib/audaxis/system_server.py", line 168, in process_data
 result += my_serverbox.save_website_list(cursor)
 File "/usr/local/lib/audaxis/system_serverbox.py", line 1197, in 
save_website_list
 old_serverbox.load_from_db(cursor, self.__id)
 File "/usr/local/lib/audaxis/system_serverbox.py", line 143, in 
load_from_db
 self.__load_disk_list_from_db(cursor)
 File "/usr/local/lib/audaxis/system_serverbox.py", line 203, in 
__load_disk_list_from_db
 my_disk.load_from_db(cursor, disk_id)
 File "/usr/local/lib/audaxis/system_disk.py", line 54, in load_from_db
 self.__fs = row['fs']
KeyError: 'fs'
Traceback (most recent call last):
 File "/usr/local/lib/audaxis/system_serverbox.py", line 521, in 
__update_in_db
 __uuid_host = self.is_virtual(cursor)
 File "/usr/local/lib/audaxis/system_serverbox.py", line 339, in 
is_virtual
 result = row['uuid']
KeyError: 'uuid'
and a lot of KeyError (at every update)
The requests that produce these errors were working perfectly. And if I 
restart my server, everything is working fine again !
Here's part of my code (I removed some parts):
I create a db connection like this in my object server:
def connect():
 con = MySQLdb.connect (host = "localhost", user = "myuser", passwd 
= "good_password", db = "my_db", cursorclass=DictCursor)
 con.ping(True) <- this normally activate auto-reconnection (or I 
did a mistake ?)
 con.cursor().execute('SET AUTOCOMMIT=1')
 return con
self.__db_connection = connect()
Then, each time a client connects :
cursor = self.__db_connection.cursor()
...process the datas...
cursor.close()
So , I thought about some solutions :
- restarting the server every sometimes (but it's the worst solution in 
my mind)
- creating a connection (not only cursor) at each client connection (but 
I'm afraid it overloads the mysql server)
- trying to find where I did a mistake, and correct the bug (that why 
I'm doing by writing this list :), or send me a link that could help me 
(before writing I googled for one hour and found nothing interresting in 
my case...)
Thanks in advance for your advices !
Romaric