Programming : Python MySQL Connector Debug

Today, I was asked to look at a server for a friend, their problem... "It just stops working after a few days"... A few days turned into "between three and five".  Doing some mathematics I found they had between 125 and 350 unique visits to the server, each unique visit represents one customer or one remote unit of their fleet.

They relay their data from these to individual database instances on one MySQL Server, so there is about 30 customers each with many unique databases.

The problem?... Well, I find this very distressing, as they open one connection for each arriving remote client, use it and then they closed it... Right... RIGHT?!??!!

import mysql.connector

l_total = 0
while (True):
    # Count
    l_total += 1
    l_res = l_total % 100
    if l_res == 0:
        print (l_total)

    # Open a connection
    con = mysql.connector.connect(user='root', password='***', host='localhost', database='Pickles')
    cursor = con.cursor()

    # Query
    query = ("SELECT * FROM VeggiePatch")
    cursor.execute(query)

    # Retrieve the data
    cursor.fetchall()

    # Close the query cursor
    cursor.close()

    # Close the Connection
    con.close()

This is my test code based on the way their production code works, as having read the error log I see the problem is in the connector constructor and delves down into the networking code.

This of course crashes after around 33,000 cycles.

They're not willing to change their script "willy-nilly", I in fact think they're petrified I've found this problem.  Googling around I don't find any official explanation of this error, only anecdotal forum posts about the MySQL Connector not cleaning up after itself and so reusing the sockets fails over time.

The better solution is to garbage collect the connection each cycle...

import mysql.connector
import gc

l_total = 0
while (True):
    # Count
    l_total += 1
    l_res = l_total % 100
    if l_res == 0:
        print (l_total)

    # Open a connection
    con = mysql.connector.connect(user='root', password='***', host='localhost', database='Pickles')
    cursor = con.cursor()

    # Query
    query = ("SELECT * FROM Tickets")
    cursor.execute(query)

    # Retrieve the data
    cursor.fetchall()

    # Close the query cursor
    cursor.close()

    # Close the Connection
    con.close()
    con = None

    gc.collect()

I also tried to garbage collect each time I printed the the "total", each 100 passes, but this still crashed, the fixed loop here has so far done just under half a million cycles without issue....