How to enable MySQL client auto re-connect with MySQLdb?
I came across PHP way of doing the trick:
my_bool reconnect = 1; mysql_options(&mysql, MYSQL_OPT_RECONNECT, &reconnect);
but no luck with MySQLdb (python-mysql).
Can anybody please give a clue? Thanks.
Asked by: Elian708 | Posted: 06-10-2021
I solved this problem by creating a function that wraps the
cursor.execute() method since that's what was throwing the
MySQLdb.OperationalError exception. The other example above implies that it is the
conn.cursor() method that throws this exception.
Answered by: Elise717 | Posted: 07-11-2021
import MySQLdb class DB: conn = None def connect(self): self.conn = MySQLdb.connect() def query(self, sql): try: cursor = self.conn.cursor() cursor.execute(sql) except (AttributeError, MySQLdb.OperationalError): self.connect() cursor = self.conn.cursor() cursor.execute(sql) return cursor db = DB() sql = "SELECT * FROM foo" cur = db.query(sql) # wait a long time for the Mysql connection to timeout cur = db.query(sql) # still works
I had problems with the proposed solution because it didn't catch the exception. I am not sure why.
I have solved the problem with the
ping(True) statement which I think is neater:
import MySQLdb con=MySQLdb.Connect() con.ping(True) cur=con.cursor()
If you are using ubuntu Linux there was a patch added to the python-mysql package that added the ability to set that same MYSQL_OPT_RECONNECT option (see here). I have not tried it though.
Unfortunately, the patch was later removed due to a conflict with autoconnect and transations (described here).
The comments from that page say: 1.2.2-7 Published in intrepid-release on 2008-06-19
python-mysqldb (1.2.2-7) unstable; urgency=low
[ Sandro Tosi ] * debian/control - list items lines in description starts with 2 space, to avoid reformat on webpages (Closes: #480341)
[ Bernd Zeimetz ] * debian/patches/02_reconnect.dpatch: - Dropping patch: Comment in Storm which explains the problem:
Answered by: Adelaide162 | Posted: 07-11-2021
# Here is another sad story about bad transactional behavior. MySQL # offers a feature to automatically reconnect dropped connections. # What sounds like a dream, is actually a nightmare for anyone who # is dealing with transactions. When a reconnection happens, the # currently running transaction is transparently rolled back, and # everything that was being done is lost, without notice. Not only # that, but the connection may be put back in AUTOCOMMIT mode, even # when that's not the default MySQLdb behavior. The MySQL developers # quickly understood that this is a terrible idea, and removed the # behavior in MySQL 5.0.3. Unfortunately, Debian and Ubuntu still # have a patch right now which *reenables* that behavior by default # even past version 5.0.3.
I needed a solution that works similarly to Garret's, but for
cursor.execute(), as I want to let
MySQLdb handle all escaping duties for me. The wrapper module ended up looking like this (usage below):
#!/usr/bin/env python import MySQLdb class DisconnectSafeCursor(object): db = None cursor = None def __init__(self, db, cursor): self.db = db self.cursor = cursor def close(self): self.cursor.close() def execute(self, *args, **kwargs): try: return self.cursor.execute(*args, **kwargs) except MySQLdb.OperationalError: self.db.reconnect() self.cursor = self.db.cursor() return self.cursor.execute(*args, **kwargs) def fetchone(self): return self.cursor.fetchone() def fetchall(self): return self.cursor.fetchall() class DisconnectSafeConnection(object): connect_args = None connect_kwargs = None conn = None def __init__(self, *args, **kwargs): self.connect_args = args self.connect_kwargs = kwargs self.reconnect() def reconnect(self): self.conn = MySQLdb.connect(*self.connect_args, **self.connect_kwargs) def cursor(self, *args, **kwargs): cur = self.conn.cursor(*args, **kwargs) return DisconnectSafeCursor(self, cur) def commit(self): self.conn.commit() def rollback(self): self.conn.rollback() disconnectSafeConnect = DisconnectSafeConnection
Using it is trivial, only the initial connect differs. Extend the classes with wrapper methods as per your MySQLdb needs.
Answered by: Ada963 | Posted: 07-11-2021
import mydb db = mydb.disconnectSafeConnect() # ... use as a regular MySQLdb.connections.Connection object cursor = db.cursor() # no more "2006: MySQL server has gone away" exceptions now cursor.execute("SELECT * FROM foo WHERE bar=%s", ("baz",))
you can separate the commit and the close for the connection...that's not cute but it does it.
Answered by: Patrick520 | Posted: 07-11-2021
class SqlManager(object): """ Class that handle the database operation """ def __init__(self,server, database, username, pswd): self.server = server self.dataBase = database self.userID = username self.password = pswd def Close_Transation(self): """ Commit the SQL Query """ try: self.conn.commit() except Sql.Error, e: print "-- reading SQL Error %d: %s" % (e.args, e.args) def Close_db(self): try: self.conn.close() except Sql.Error, e: print "-- reading SQL Error %d: %s" % (e.args, e.args) def __del__(self): print "close connection with database.." self.conn.close()
I had a similar problem with MySQL and Python, and the solution that worked for me was to upgrade MySQL to 5.0.27 (on Fedora Core 6; your system may work fine with a different version).
I tried a lot of other things, including patching the Python libraries, but upgrading the database was a lot easier and (I think) a better decision.Answered by: Brad827 | Posted: 07-11-2021
In addition to Liviu Chircu solution ... add the following method to DisconnectSafeCursor:
def __getattr__(self, name): return getattr(self.cursor, name)
and the original cursor properties like "lastrowid" will keep working.Answered by: Marcus825 | Posted: 07-11-2021
You other bet it to work around dropped connections yourself with code.
One way to do it would be the following:
Answered by: Charlie373 | Posted: 07-11-2021
import MySQLdb class DB: conn = None def connect(self): self.conn = MySQLdb.connect() def cursor(self): try: return self.conn.cursor() except (AttributeError, MySQLdb.OperationalError): self.connect() return self.conn.cursor() db = DB() cur = db.cursor() # wait a long time for the Mysql connection to timeout cur = db.cursor() # still works
Python socket client unable to re-connect
I have two buttons. Connect and Disconnect. When connect is pressed the first time, the client successfully connects to the server (localhost:4106) and when when disconnect is pressed the client disconnects. My problem is that when I press "Connect" again, the client will not re-connect to the server and I get an error message: http://i.imgur.com/dkvos.png I...
python - SSL raises version error only on re-connect attempt?
We have a server and client both running python3. The client connects to the server and authenticates upon initialisation of the client. This completes without issue. However, if the connection drops, the client catches the error (the socket.recv returning 0) and attempts to re-run the code that connects to the server). The server recieves the initial request whilst listening on its given recieving soc...