MySQL/Python terminal crashing when attempting to reconnect

Question

I'm on macOS Sierra and i'm trying to update my MySQL record together with scrapy and recently i have been getting '2006 MySQL has gone away', so i implemented the solution following this guide, but halfway through the crawling. Terminal will crash with the following:

Crash report:

python(85034,0x70000b397000) malloc: *** error for object 0x7fa52317c400: pointer being freed was not allocated
*** set a breakpoint in malloc_error_break to debug
python(85034,0x70000a78e000) malloc: *** error for object 0x7fa52317c400: double free
*** set a breakpoint in malloc_error_break to debug
python(85034,0x70000ab91000) malloc: *** error for object 0x7fa52317c400: double free
*** set a breakpoint in malloc_error_break to debug
Abort trap: 6

Code:

def connect(self):
    self.conn = MySQLdb.connect(host='127.0.0.1',
               user='root',
               passwd='<my password>',
               db='jobs',
               charset='utf8'
                )
def checkRecord(self, query, params, msg):
    try:
        cursor = self.conn.cursor()
        cursor.execute(query, params)
        fetch = cursor.fetchone()
        return fetch
    except (MySQLdb.OperationalError):
        logging.info("Attempting to reconnect for select query") 
        self.connect()
        self.checkRecord(query, params, msg)
    finally:
        logging.info(msg)
        cursor.close()

def checkCategory(self, query, params, msg):
    try:
        cursor = self.conn.cursor()
        cursor.execute(query, params)
        fetch = cursor.fetchone()
        return fetch[0]
    except (MySQLdb.OperationalError):
        logging.info("Attempting to reconnect for select query") 
        self.connect()
        self.checkRecord(query, params, msg)
    finally:
        logging.info(msg)
        cursor.close()

def insertRecord(self, query, params, msg):
    try:
        cursor = self.conn.cursor()
        cursor.execute(query, params)
    except (MySQLdb.OperationalError):
        logging.info("Attempting to reconnect for inserting record") 
        self.connect()
        self.insertRecord(query, params, msg)
    finally:
        logging.info(msg)
        cursor.close()

def updateCategory(self, query, params, msg):
    try:
        cursor = self.conn.cursor()
        cursor.execute(query, params)
    except (MySQLdb.OperationalError):
        logging.info("Attempting to reconnect for updating record") 
        self.connect()
        self.updateCategory(query, params, msg)            
    finally:
        self.conn.commit()
        logging.info(msg)
        cursor.close()

def _conditional_insert(self,tx,item):

    selectquery = "SELECT count(*) FROM dbJobs WHERE jobdetailsurl LIKE %s AND company_name LIKE %s AND title LIKE %s"
    selectparams = (item['jobdetailsurl'], item['company_name'], item['title'])
    msg = "Checking for whether record exists in Database"

    fetch = self.checkRecord(selectquery, selectparams, msg)
    ....
    ....

I also tested this code on a Windows 10 machine, the command prompt crashes as well. Any solutions to this problem?

EDIT: (would opening a new connection and closing the connection after every cursor be sufficient?)

def checkRecord(self, query, params, msg):
    try:
        self.connect()
        cursor = self.conn.cursor()
        cursor.execute(query, params)
        fetch = cursor.fetchone()
        return fetch
    except (MySQLdb.OperationalError):
        logging.info("Attempting to reconnect for select query") 
        self.connect()
        self.checkRecord(query, params)
    finally:
        cursor.close()
        self.conn.close()

    logging.info(msg)

check this, it may help https://stackoverflow.com/questions/22971247/how-to-debug-c-compiler-errors-in-python-malloc-error — Haifeng Zhang, Sep 18 '17 at 19:19
Would implementing it in such a way (edited in main post) be sufficient to prevent that situation as described by that link? — dythe, Sep 18 '17 at 19:50

score 0 · Answer 1 · answered Sep 19 '17 at 07:13

I have managed to solve this issue by creating a new connection for each query/update/insert then after that closing that connection after i'm done with it instead of leaving 1 connection up for all the queries. Tested it by running it on 2 machines the entire night and there were no more crashes.

Example:

def checkRecord(self, query, params, msg):
    try:
        self.connect()
        cursor = self.conn.cursor()
        cursor.execute(query, params)
        fetch = cursor.fetchone()
        return fetch
    except (MySQLdb.OperationalError):
        logging.info("Attempting to reconnect for select query") 
        self.connect()
        self.checkRecord(query, params)
    finally:
        cursor.close()
        self.conn.close()

    logging.info(msg)

MySQL/Python terminal crashing when attempting to reconnect

1 Answers1