PyMySQL==0.7.11, MySQL server has gone away

Question

I have an application written on Python Tornado framework.

I wrote a script that is listening for the messages via tunnel, and when my conditions are satisfied the script connects to MySQL and doing insert or update, but after long time i am getting this error that MySQL is disconnected.

Listening to the tunnel i use pika module. so when everything is happening i get this

Pika: Could not connect to host 127.0.0.1 on port 5671

MySQL: (2006, "MySQL server has gone away (ConnectionResetError(10054, 'An existing connection was forcibly closed by the remote host', None, 10054, None))")

My code is below:

import pika

def main():
    credentials = pika.PlainCredentials('user', 'password')

    try:
        cp = pika.ConnectionParameters(
            host='127.0.0.1',
            port=5671,
            credentials=credentials,
            ssl=False,
        )

        connection = pika.BlockingConnection(cp)
        channel = connection.channel()

        def callback(ch, method, properties, body):
            if 'messageType' in properties.headers:
                message_type = properties.headers['messageType']
                if message_type in allowed_message_types:
                    result = proto_file._reflection.ParseMessage(descriptors[message_type], body)
                    if result:
                        result = protobuf_to_dict(result)
                        if message_type == '1':
                            Model.message_event(data=result)


                else:
                    print('Message type not in allowed list = ' + str(message_type))
                    print('continue listening...')

        channel.basic_consume(callback, queue='queue', no_ack=True)

        print(' [*] Waiting for messages. To exit press CTRL+C')
        channel.start_consuming()
    except Exception as e:
        print('Could not connect to host 127.0.0.1 on port 5671')
        print(str(e))

Here is model for message_event that connects to MySQL

import pymysql
import pymysql.cursors


class Model(object):
    def __init__(self, conf):
        self.conf = conf
        conv = pymysql.converters.conversions.copy()
        conv[246] = Decimal
        conv[10] = str

        self.mysql = {pymysql.connect(
            host=self.conf['host'],
            user=self.conf['user'],
            password=self.conf['password'],
            db=self.conf['db'],
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor,
            autocommit=self.conf['autocommit'],
            conv=conv)}


    def message_event(self, data):
        with self.mysql.cursor() as cursor:
            // here doing following operations
            // select, insert or update
            cursor.close()

    // and after sometime i get this
    Pika: Could not connect to host 127.0.0.1 on port 5671
    MySQL: (2006, "MySQL server has gone away (ConnectionResetError(10054, 'An existing connection was forcibly closed by the remote host', None, 10054, None))")

The main point once connected to the tunnel, must to listen infinite for the message and manipulate with the message inside MySQL, but the connection is getting lost after some time.

Will appreciate any ideas to fix this issue, if possible without changing the configuration of MySQL server.

Answer 1

I would like to post my answer that worked, only this solution worked for me

1. before connecting to mysql to check if the connection is open, if not reconnect

   if not self.mysql.open:
       self.mysql.ping(reconnect=True)