Reconnecting MySQL on timeout

Question

I have a Python program which runs on background for weeks, and does database queries every once in a while. For that, I am using the ORM peewee (version 2.2.1). I am using MySQL as a backend.

Lately I've encountered a recurring problem with accessing the DB, usually after days of running the program. The error which is raised by peewee is

peewee.OperationalError: (2006, 'MySQL server has gone away')

The traceback is deep in peewee. I post it here, but as my virtualenv makes filenames too long, I am shortening them:

  File ".../local/lib/python2.7/site-packages/peewee.py", line 2910, in save
    ret_pk = self.insert(**field_dict).execute()
  File ".../local/lib/python2.7/site-packages/peewee.py", line 2068, in execute
    return self.database.last_insert_id(self._execute(), self.model_class)
  File ".../local/lib/python2.7/site-packages/peewee.py", line 1698, in _execute
    return self.database.execute_sql(sql, params, self.require_commit)
  File ".../local/lib/python2.7/site-packages/peewee.py", line 2232, in execute_sql
    self.commit()
  File ".../local/lib/python2.7/site-packages/peewee.py", line 2104, in __exit__
    reraise(new_type, new_type(*exc_value.args), traceback)
  File ".../local/lib/python2.7/site-packages/peewee.py", line 2223, in execute_sql
    res = cursor.execute(sql, params or ())
  File ".../local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File ".../local/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
peewee.OperationalError: (2006, 'MySQL server has gone away')

Possible solution attempts I've found:

• In this question, one of the comments suggest pinging the MySQL server every once in a while to keep it (the connection?) alive. I am not sure how to do it via the ORM, though. (should I simply SELECT 1 every hour, say?)
• In this github peewee issue, which was opened 4 months ago, the same error is referred, though, it is claimed there that it is solved (and I am using a newer version).
• In a 7 year old issue of trac, one suggestion is to increase the timeout of MySQL for 3 days.
• In this forum discussion, the option of increasing MySQL's timeout is suggested, but an alternative of "using the autoReconnect option for the MySQL JDBC connector" is offered. I tried to figure out if such an option exists for Python's MySQLdb module, but couldn't find.
• I have found this MySQL reference page on reconnection behaviour, but it's a bit complicated for my understanding of MySQL (usually I work only with ORMs), and I don't know how to apply any of it from peewee.

Even if I'm able to ping the database to keep the connection alive for longer periods, I think it is considered a bad practice to keep a connection alive when one doesn't really need it. Is there any way to reopen the connection via the ORM? I consider both pinging and increasing the timeout of MySQL as workarounds, while a real solution would be to reconnect when needed (and a real solution is what I'm asking for).

Shenitashenk

Check the value of wait_timeout in MySQL – Tapster

I guess it is 8 hours; isn't that the default? – Shenitashenk

Just manually call db.close() when you're done with the connection. – Antiquate

I'm never "done" with it; the program just keeps running. Still, I don't think it's a good practice to keep the connection alive if I'm not using it; and any timeout I'll set might not be large enough. – Shenitashenk

Answer 1

You have to catch the exception and based on which error, reconnect or to do something else. Whether it is a connection time out, or a network problem or the MySQL had to be restarted.

The below (pseudoish) code shows how you could do that, but there is more to it. You'll want to try a few times and then bail out, or maybe try every 2 minutes or so.

while True:
    try:
        # do your database stuff
    except peewee.OperationalError as exc:
        # Oops! We have to try to reconnect

Does not really matter whether you use an ORM or not. However, an ORM might offer this functionality.

Dialectician

That looks indeed like the proper thing to do. However, it was not easy to accomplish; there was no single method I could find that I could wrap with such a try... except structure, each time the error was raised from a different place. That's what nice - and problematic - about ORMs; they become almost transparent in your code. Anyway, I ended up pinging the server, and hopefully peewee people will implement an inner optional reconnect method one day. – Shenitashenk

In such extreme cases, you can put the try/except around the main() method of your script. – Dialectician

No, I'll never do that... It is not a script, it is a huge multi-threaded software... I just claim it's the responsibility of the ORM, otherwise I must "invade" its territory, making it redundant. – Shenitashenk

I've found that a generic "reconnect and try again" approach doesn't always work. specifically if the query you are re-trying is a prepared statement. If it is prepared, it would need to be "re-prepared" after the connection is established which can be trickier than you'd think depending on abstractions in place. – Bead

Answer 2

I had the same problem and for peewee using MySQLdb I got the following solution when initialize the MySQL database instance:

db = MySQLDatabase(db_name, user=db_username, passwd=db_password, host=db_host, port=db_port)
db.get_conn().ping(True)

where for the ping function there is:

Checks whether or not the connection to the server is working. If it has gone down, an automatic reconnection is attempted.

This function can be used by clients that remain idle for a long while, to check whether or not the server has closed the connection and reconnect if necessary.

New in 1.2.2: Accepts an optional reconnect parameter. If True, then the client will attempt reconnection. Note that this setting is persistent. By default, this is on in MySQL<5.0.3, and off thereafter.

Non-standard. You should assume that ping() performs an implicit rollback; use only when starting a new transaction. You have been warned.

in the db.get_conn().ping.__doc__. Mind that db.get_conn().ping(True) has to be used if you create another connection again. So if you reconnect (through db.connect() for example) you must repeat the ping.

Aphorism

Pinging before running a query is considered an anti-pattern that wastes resources and is unreliable: percona.com/blog/2010/05/05/… – Lambertson

Every time you ping the database, a cute dolphin is killed. – Bungle

Answer 3

I have resolved this issue.

My solution is use mysql connection pool PooledMySQLDatabase from playhouse.pool module.

please read: https://github.com/coleifer/peewee/issues/239

from peewee import *
from playhouse.pool import *

Symbol

Just a side note: this module is considered experimental, and it may be dangerous to apply it for production. – Shenitashenk