Add connection pool for database connections

Pull connectins from the pool when doing queries in a thread

mysql-python (and all mysql drivers I can find) have a threadsafety
value of 1 (see PEP-249) which makes it unsafe to use the same
connection from more than one thread.

Trivia in particular used to do this extensively, which is what prompted
https://gitlab.com/rizon/mysql-python/commit/c09cfd229176bd280bfb0384aaed7d07c42bbc0a,
however this fix is not correct because both mysql_query and
mysql_store_result both need to be called in the mutex lock, which this
doesn't do. However this fixes segfaults from causing libmysqlclient
going multithreaded, so it works well enough in production.

diff --git a/pyva/config.example.ini b/pyva/config.example.ini
index e6229bd1039662c83396a2e615a3be78f654853c..2d0ffec44b554e3d583271d92279a2c7fe7dd052 100644 (file)
--- a/pyva/config.example.ini
+++ b/pyva/config.example.ini
@@ -24,7 +24,6 @@ host: 192.168.1.2
 user: adam
 passwd: moo
 db: pypsd
-sock: /var/lib/mysql/mysql.sock
 
 [ctcp]
 nick: py-ctcp

diff --git a/pyva/populate-zipcodes.py b/pyva/populate-zipcodes.py
index 0b7a1602e47426a6c4e8f8a88ed8604d25d688bc..e5c2c2fe202fe20ea5373508c6e406956e416bce 100755 (executable)
--- a/pyva/populate-zipcodes.py
+++ b/pyva/populate-zipcodes.py
@@ -41,7 +41,6 @@ dbx = db.connect(
     user=config.get('database', 'user'),
     passwd=config.get('database', 'passwd'),
     db=config.get('database', 'db'),
-    unix_socket=config.get('database', 'sock')
 )
 dbx.autocommit(True)  # no need to have transactions
 dbp = dbx.cursor()

diff --git a/pyva/pyva/src/main/python/core.py b/pyva/pyva/src/main/python/core.py
index 978c9ffcc150cd085971a0a5558744c3f4b957b4..19a77284b4712a1baa8234d0ce0441ff42d22aec 100644 (file)
--- a/pyva/pyva/src/main/python/core.py
+++ b/pyva/pyva/src/main/python/core.py
@@ -1,9 +1,9 @@
 import ConfigParser
 import codecs
 from istring import istring
-import MySQLdb as db
 import logging
 import logging.handlers
+import pool
 
 config = ConfigParser.ConfigParser()
 config.readfp(codecs.open("config.ini", "r"))
@@ -12,15 +12,8 @@ anope_major = int(config.get('services', 'anope_major'))
 if anope_major not in [1, 2]:
        raise Exception('Unknown anope major version %s' % anope_major) 
 
-dbx = db.connect(
-       host=config.get('database', 'host'),
-       user=config.get('database', 'user'),
-       passwd=config.get('database', 'passwd'),
-       db=config.get('database', 'db'),
-       unix_socket=config.get('database','sock')
-)
-dbx.ping(True)
-dbx.autocommit(True) #no need to have transactions
+dbpool = pool.DBPool(config)
+dbx = dbpool.get_connection()
 
 logfile = config.get('logging', 'logfile')
 loglevel = getattr(logging, config.get('logging', 'level').upper())

diff --git a/pyva/pyva/src/main/python/internets/cmd_user.py b/pyva/pyva/src/main/python/internets/cmd_user.py
index e21c3a13cca0ffbda905f616eac4077b096db77e..7e0dedb29b5147ccf37f305496047364f78d2ba6 100644 (file)
--- a/pyva/pyva/src/main/python/internets/cmd_user.py
+++ b/pyva/pyva/src/main/python/internets/cmd_user.py
@@ -3,6 +3,7 @@ import re
 from datetime import datetime, timedelta
 from xml.parsers.expat import ExpatError
 
+import core
 from pseudoclient.cmd_manager import *
 from utils import *
 from internets_utils import *
@@ -15,9 +16,14 @@ from api.steam import SteamException
 def get_citystate_from_zipcode(self, zipcode):
        """Return [city,state] for the given U.S. zip code (if database has been imported)"""
        try:
-               self.dbp.execute("SELECT city, state FROM zipcode_citystate WHERE zipcode=%s", [int(zipcode)])
-               city, state = self.dbp.fetchone()
-               return city, state
+               con = core.dbpool.get_connection()
+               try:
+                       cursor = con.cursor()
+                       cursor.execute("SELECT city, state FROM zipcode_citystate WHERE zipcode=%s", [int(zipcode)])
+                       city, state = cursor.fetchone()
+                       return city, state
+               finally:
+                       core.dbpool.put_connection(con)
        except:
                return None
 

diff --git a/pyva/pyva/src/main/python/plugin.py b/pyva/pyva/src/main/python/plugin.py
index 0d28a7748db79c6b6ebf31ebb2831d66adae1f35..bcf8b8793d017c4c6d5f49825a3f56f50d69a635 100644 (file)
--- a/pyva/pyva/src/main/python/plugin.py
+++ b/pyva/pyva/src/main/python/plugin.py
@@ -13,7 +13,7 @@ class AcidPlugin(object):
                self.logchan = config.get('control', 'channel')
                self.log = logging.getLogger(__name__)
                self.config = config
-               self.dbp = dbx.cursor()
+               self.dbp = dbx.cursor() # do these have to be closed?
 
        def start(self):
                pass

diff --git a/pyva/pyva/src/main/python/pool.py b/pyva/pyva/src/main/python/pool.py
new file mode 100644 (file)
index 0000000..1b70d7a
--- /dev/null
+++ b/pyva/pyva/src/main/python/pool.py
@@ -0,0 +1,36 @@
+import threading
+import MySQLdb as db
+
+class DBPool(object):
+       _lock = threading.RLock()
+       _connections = []
+
+       def __init__(self, conf):
+               self.config = conf
+               self.add_connection()
+
+       def add_connection(self):
+               con = db.connect(
+                       host=self.config.get('database', 'host'),
+                       user=self.config.get('database', 'user'),
+                       passwd=self.config.get('database', 'passwd'),
+                       db=self.config.get('database', 'db'),
+               )
+               con.ping(True)
+               con.autocommit(True) #no need to have transactions
+
+               with self._lock:
+                       self._connections.append(con)
+
+       def get_connection(self):
+               with self._lock:
+                       if len(self._connections) == 0:
+                               self.add_connection()
+                       return self._connections.pop()
+
+       def put_connection(self, con):
+               with self._lock:
+                       if con not in self._connections:
+                               self._connections.append(con)
+               
+

diff --git a/pyva/pyva/src/main/python/pseudoclient/sys_base.py b/pyva/pyva/src/main/python/pseudoclient/sys_base.py
index fac4e199b6ad308021b8cb112f43006370a6cabe..98c8c2b43a830d07c436fa1aea43da0b690d12fc 100644 (file)
--- a/pyva/pyva/src/main/python/pseudoclient/sys_base.py
+++ b/pyva/pyva/src/main/python/pseudoclient/sys_base.py
@@ -1,5 +1,5 @@
 import threading
-import MySQLdb as db
+import core
 
 class Subsystem(object):
        #--------------------------------------------------------------#
@@ -31,15 +31,7 @@ class Subsystem(object):
                self.reload()
 
        def db_open(self):
-               self.conn = db.connect(
-                       host=self.module.config.get('database', 'host'),
-                       user=self.module.config.get('database', 'user'),
-                       passwd=self.module.config.get('database', 'passwd'),
-                       db=self.module.config.get('database', 'db'),
-                       unix_socket=self.module.config.get('database','sock')
-               )
-               self.conn.ping(True)
-               self.conn.autocommit(True)
+               self.conn = core.dbpool.get_connection()
                self.cursor = self.conn.cursor()
 
        def db_close(self):
@@ -48,7 +40,7 @@ class Subsystem(object):
                        self.cursor = None
                
                if self.conn != None:
-                       self.conn.close()
+                       core.dbpool.put_connection(self.conn)
                        self.conn = None
 
        def reload(self):