id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
463	[patch] new mysql.DatabaseWrapper - solving multiple MySQL problems	eugene@…	Adrian Holovaty	"This patch solves a problem of multiple errors while hosting Django app with FastCGI and MySQL. I was running it for 24 hours without any problems.

Some highlights:

1) Django uses the same connection for all threads. It breaks MySQL leading to numerous random CR_SERVER_GONE_ERROR and CR_SERVER_LOST errors. Every independently talking entity should have its own connection. I've implemented mysql.!DatabaseWrapper using dictionary to keep one connection per thread.

2) During request for new connection, old connections are examined. If thread is gone, its connection is closed and garbage-collected.

3) MySQL connection can time-out depending on MySQL server settings. The standard practice is to ping() stored connections before use. My implementation does it for every retrieved connection.

Some potential problems:

1) I rename threads, which request connections, to make them unique. If some other code relies on thread names, it may be broken.

2) 24 hour testing is not enough for production quality code. Changes are very small and compact. They can be verified manually. But still it is not a full blown system test under different hosting scenarios.

3) Please take a look at the code and verify that it is optimal --- my python experience is limited, I could implement something in sub-optimal way.

The patch:

{{{
Index: mysql.py
===================================================================
--- mysql.py	(revision 629)
+++ mysql.py	(working copy)
@@ -11,6 +11,9 @@
 from MySQLdb.constants import FIELD_TYPE
 import types
 
+import thread, threading
+from sets import Set
+
 DatabaseError = Database.DatabaseError
 
 django_conversions = conversions.copy()
@@ -23,32 +26,78 @@
 
 class DatabaseWrapper:
     def __init__(self):
-        self.connection = None
+        self.connections = {}
+        self.threads = Set()
+        self.lock = thread.allocate_lock()
         self.queries = []
+        
+    def _get_connection(self):
+        self.lock.acquire()
+        try:
+            # find existing connection
+            id = threading.currentThread().getName()
+            if id in self.connections:
+                connection = self.connections[id]
+                connection.ping()
+                return connection
+            # normalize thread name
+            if id != 'MainThread':
+                id = str(thread.get_ident())
+                threading.currentThread().setName(id)
+            # remove deadwood
+            dead = self.threads - Set([x.getName() for x in threading.enumerate()])
+            for name in dead:
+                self.connections[name].close()
+                del self.connections[name]
+            self.threads -= dead
+            # create new connection
+            from django.conf.settings import DATABASE_USER, DATABASE_NAME, DATABASE_HOST, DATABASE_PASSWORD
+            connection = Database.connect(user=DATABASE_USER, db=DATABASE_NAME,
+                    passwd=DATABASE_PASSWORD, host=DATABASE_HOST, conv=django_conversions)
+            self.connections[id] = connection
+            self.threads.add(id)
+            return connection
+        finally:
+            self.lock.release()
 
     def cursor(self):
-        from django.conf.settings import DATABASE_USER, DATABASE_NAME, DATABASE_HOST, DATABASE_PASSWORD, DEBUG
-        if self.connection is None:
-            self.connection = Database.connect(user=DATABASE_USER, db=DATABASE_NAME,
-                passwd=DATABASE_PASSWORD, host=DATABASE_HOST, conv=django_conversions)
+        connection = self._get_connection()
+        from django.conf.settings import DEBUG
         if DEBUG:
-            return base.CursorDebugWrapper(self.connection.cursor(), self)
-        return self.connection.cursor()
+            return base.CursorDebugWrapper(connection.cursor(), self)
+        return connection.cursor()
 
     def commit(self):
-        self.connection.commit()
+        self.lock.acquire()
+        try:
+            id = threading.currentThread().getName()
+            if id in self.connections:
+                self.connections[id].commit()
+        finally:
+            self.lock.release()
 
     def rollback(self):
-        if self.connection:
-            try:
-                self.connection.rollback()
-            except Database.NotSupportedError:
-                pass
+        self.lock.acquire()
+        try:
+            id = threading.currentThread().getName()
+            if id in self.connections:
+                try:
+                    self.connections[id].rollback()
+                except Database.NotSupportedError:
+                    pass
+        finally:
+            self.lock.release()
 
     def close(self):
-        if self.connection is not None:
-            self.connection.close()
-            self.connection = None
+        self.lock.acquire()
+        try:
+            id = threading.currentThread().getName()
+            if id in self.connections:
+                connection = self.connections[id]
+                connection.close()
+                del self.connections[id]
+        finally:
+            self.lock.release()
 
 def get_last_insert_id(cursor, table_name, pk_name):
     cursor.execute(""SELECT LAST_INSERT_ID()"")
}}}"	defect	closed	Core (Other)		major	duplicate			Unreviewed	1	0	0	0	0	0