Geert JM Vanderkelen

Automatic reconnect in MySQL Connector/Python?

Automatic reconnect in MySQL Connector/Python?

There have been some request to have some reconnect possibilities in Connector/Python. I’m wondering now whether there should be some automatic reconnect on certain errors within the database driver.

My personal feeling is to have no automatic reconnect within Connector/Python and the programmer has to come up with retrying transactions herself.

For example:

	cnx.disconnect() # For testing..
	tries = 2
	while tries > 0:
		tries -= 1
		try:
			cursor.execute("INSERT INTO t1 (c1) VALUES ('ham')")
			cnx.commit()
		except mysql.connector.InterfaceError:
			if tries == 0:
				print "Failed inserting data after retrying"
				break
			else:
				print "Reconnecting.."
				cnx.reconnect()
		else:
			break

The above mimics how you would handle transactions and trying them reconnecting. I have ideas how to get this into Connector/Python, but it would not really fit PEP-249.

Would the above use case of reconnecting be enough?

8 thoughts on “Automatic reconnect in MySQL Connector/Python?

  1. Ulf Wendel

    There aint no safe and water-proof way to detect transaction boundaries. Do not do automatic reconnect by default. Put, big, big warning into the manual for those who want it.

  2. Volans

    The auto-reconnection should be useful only if it where able to redo a failed query, reopening the connection and doing the same query/transaction failed.

    But in addition to transaction boundaries, there are many other circumstances to keep in mind like the connection encoding, various timeouts, and potentially any other MySQL session o global parameter that can be changed at runtime after the connection was established.
    In which state would you reopen the connection in those cases?

    Another particular case should be if the server parameters are changed from the first connection… the code maybe check those settings while connecting, the auto-reconnection can’t do it with the same logic the application does.

    For those reasons for me too the aut.oreconnection should not be implemented.

  3. Mark Callaghan

    Connections go away in the real world. One common reason is idle timeouts. I have now wasted 15+ minutes reading too many responses on the web from people standing on a soapbox telling me how evil auto-reconnect is. I just want to use it for a stupid monitoring script. Add it, make it off by default.

  4. Joe

    “Would the above use case of reconnecting be enough?”

    So, let me get this straight… your proposed solution is for me to back and wrap every one of my cursor.execute() statments with the 14 extra lines you’ve suggested?

  5. Keith

    A lack of auto-reconnect I can understand given the issues mentioned, but why doesn’t mysql connector/python allow django to reconnect to the database on each request as it should if CONN_MAX_AGE is 0? After bouncing the mysql server with django 1.6.5, mysql connector/python 1.2.2, mysql server 5.5.37, apache worker 2.2.22, and python 3.3.5 on an otherwise stock and up-to-date ubuntu 12.04 setup, database access fails with File “/usr/local/lib/python3.3/dist-packages/mysql/connector/connection.py” in cursor 1328 raise errors.OperationalError(“MySQL Connection not available.”) and a web server restart is requred. After that access work just fine again.

Leave a Reply

Your email address will not be published. Required fields are marked *

95 − 90 =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>