CODEWITHSUNDEEP
pythonmysqlmysql-python
Matthew Moisen
Matthew Moisen

Reputation: 18289

Why does this code not reconnect to the remote mysql server when the connection is interrupted?

I have a python script that I use on my Raspberry Pi to record temperature data of my homebrew. It stores the temperature data in both a local mysql database, and a remote mysql database (my personal website) so that I can see the data when I'm outside of my home's LAN.

However, about once or twice a day (sometimes two days), the remote connection is dropped. I assume the GoDaddy server I host my remote website on drops connections that last over some time limit. I surround the call to the DB with a try/catch, and if the db call fails, I try to reinitialize the connection to the database and call again. (I wait 10 times before this, just in case the remote server actually went down).

However, the code doesn't reinitialize the connection, and does not ever again successfully update the remote database. If I kill restart the script, it works fine; so I know its not that the remote server is down.

What is the logic error in the following code?

import MySQLdb

class Connection:
    MYSQL = -1
    MONGO = -2
    def __init__(self, name, type):
        self.name = name
        self.type = type


class MySQLConnection(Connection):
    def __init__(self, name, type):
        Connection.__init__(self, name, type)

class DB:
    local_mysql = None
    remote_mysql = None
    local_mongo = None
    connections = []
    count = 0

   @staticmethod
    def connect(name, host, user, password, database):
        print 'database = ' + database
        try:
            con = MySQLConnection(name, Connection.MYSQL)
            con.con = MySQLdb.connect(host,user,password,database)
            con.host = host
            DB.connections.append(con)
            return con
       except Exception, ex:
            print("Mysql connection to " + host + " failed to initialize.")
            print("Reason: " + str(ex))
            return False

    @staticmethod
    def execute(connection, temperature_list):
        try:
            cursor = connection.con.cursor()
            for temperature in temperature_list:
                cursor.execute(temperature.sql, temperature.values_clause)

            connection.con.commit()
        except Exception, ex:
            print(connection.host + " mysql not updated. Retry in " + str(DB.count))
            print("reason = " + str(ex))
            # retry with same connection 10 times in case remote server is just down
            if DB.count < 10:
                DB.count = DB.count + 1
            else:
                DB.count = 0
                if connection.name == "remote_mysql":
                    # This doesn't establish a new connection
                    DB.remote_mysql = DB.connect('remotemysql', 'www.url.com', 'user', 'passwd', 'dnmae')

#Start script here

DB.local_mysql = DB.connect('localmysql', 'www.url.com', 'user', 'passwd', 'dnmae')
DB.remote_mysql = DB.connect('remotemysql', 'www.url.com', 'user', 'passwd', 'dnmae')

while True: 
    #... Do stuff here, namely parse and convert temperature data

    DB.execute(DB.local_mysql, temperature_list)
    DB.execute(DB.remote_mysql, temperature_list)

Update:

The specific exception message is:

20006, 'MySQL server has gone away'

I'm under the impression that the above code resets the database, but it is not doing it. Interestingly, I happen to have two raspberry pis with identical code, and only one of them right now is displaying this error message, while the other is still connected and is fine.

Upvotes: 0

Views: 807

Answers (1)

Donnie
Donnie

Reputation: 113

I had the same issue and even though after a ping to the mysql server, to see if the database was still reachable, in order to reconnect if the connection was lost when i was about to force a reconnection I was getting the 'MySql server has gone away' message. I was able to solve my problem adding these queries before executing my code

SET SESSION connect_timeout=28800
SET SESSION wait_timeout=28800
SET SESSION interactive_timeout=28800

I hope it helps anybody bumping into this issue. Best regards.

Upvotes: 1

Related Questions