Need advices for mysqldb connection best practice

[Romaric DEFAUX]

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