Random loose connection when method Cleanup() is called

Bug #487076 reported by Mindtilt on 2009-11-23
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
PySQLPool
Won't Fix
Critical
Nikoleta Verbeck

Bug Description

Sometimes i have the error:
OperationalError: (2013, 'Lost connection to MySQL server during query')
I notice the error is due the Cleanup() call. If i dont call this method, the issue is gone, but the connection dont return to the pull after the thread finish.
When execute the code:
Module Connections.py

import PySQLPool

class Connections(object):

    __connection=None
    __pool = None

    def __init__(self,host='localhost',username='xxxx',password='xxxxxxxx',schema='xxxxx'):
       self.__connection = PySQLPool.getNewConnection(host, username, password, schema)
       self.__pool=PySQLPool.getNewPool()
       self.__pool.GetConnection(self.__connection)
       self.__pool.maxActiveConnections=10

    def returnQuery(self):
        query= PySQLPool.getNewQuery(self.__connection,True)

        return query
    def clean(self):

        self.__pool.Cleanup()
    def close(self):
        self.__pool.terminatePool()
    def commit(self):
        self.__pool.commitPool()

module test.py

import Connections
import threading

con = Connections.Connections()

class comunidade(threading.Thread):
    def run(self):

        query = con.returnQuery()
        for i in range(1,3000):
            query.Query('select nmComunidade from orkutcomunidade limit 10')
            for row in query.record:
                print row['nmComunidade']

        con.clean()#This line is producing the error

class cliente(threading.Thread):
    def run(self):

        query = con.returnQuery()
        for i in range(1,8000):
            query.Query('select cdRamo from cliente limit 10')
            for row in query.record:
                print row['cdRamo']

        con.clean()#This line is producing the error

c=comunidade()
ci=cliente()
c.start()
ci.start()

Mindtilt (rcamati) wrote :

I tried spam more threads and also got this error:
Exception in thread Thread-4:
Traceback (most recent call last):
  File "C:\Arquivos de programas\python25\lib\threading.py", line 486, in __bootstrap_inner
    self.run()
  File "D:\Development\eclipse\workspace\python\PloonOrkut\src\dao\OrkutComunidade.py", line 35, in run
    query.Query('select cdRamo from cliente limit 10')
  File "C:\Arquivos de programas\python25\lib\site-packages\PySQLPool\PySQLQuery.py", line 122, in Query
    raise self.lastError
KeyError: '42f5f7741502c8caf55eec077bb1a70b'

Changed in pysqlpool:
importance: Undecided → Critical
assignee: nobody → Nick Verbeck (nerdynick)
milestone: none → 0.3.5
Nikoleta Verbeck (nerdynick) wrote :

Have a couple questions for you that may resolve the problem.

Not sure what your default connection timeout is on your server but have you tried setting

>> PySQLPool.PySQLConnection.connection_timeout = datetime.timedelta(seconds=20)

By default this is set to 20secs, and may be whats causing the connection loss. This var is the check interval that PySQLPool uses to figure out when to validate the connections to see if they may be dead. From the looks of your code this might not be it bases on the number of queries you are performing per sec.

Also what version are you using. Is it the current downloaded tar/zip or the latest trunk check out. If its the latest download we do have a bunch of threading issues that have just recently been resolved that may be the cause to this error. They are currently sitting in the trunk as of last night. I will push out the actual release in for form of a Download and PyPi install sometime tonight.

I will also continue to investigate this from my side as well.

Mindtilt (rcamati) wrote :

I tested with 0.3.1 and 0.3.4(from goolge code) tar versions and got same issue.
The versions 0.3.1 and 0.3.4 creates a new connection id for each thread and kill inactive (and sometimes random kill an active) connection when the method PySQLPool.cleanupPool() is called.
I follow your advice and get the last truk version and notice a different behavior. The lastest trunk check out create one connection for all threads. It appears to be stable and thread safe. I notice no issues when PySQLPool.cleanupPool() method is called.

Mindtilt (rcamati) wrote :

One more quote:(from last trunk check out) This behavior 1 connection for all threads, does not implement the connection pool right? Mysqlclient cant manage more than 1 operation per connection so all operations will be sequencial.

Changed in pysqlpool:
status: New → In Progress
Nikoleta Verbeck (nerdynick) wrote :

Closing this one out as your other Bug ticket should resolve your question about this matter.

Bug #488928 - https://bugs.launchpad.net/pysqlpool/+bug/488928

In general based on your code example you need to not keep track of the PySQLQuery object outside of the thread, but ask for a new 1 per thread. This will cause PySQLPool to open more connections as threads need them.

Changed in pysqlpool:
status: In Progress → Won't Fix
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers