On 03/31/2017 05:19 AM, SG wrote:
I have a system composed by client where runs sqlalchemy and a remote
server where runs MySQL server database.
With sqlalchemy I use to setup session:
|
# db_host is the ip address of remote server
db_url ='mysql+mysqldb://db_user:db_pswd@db_host/db_name'
db_engine =create_engine(db_url)
db_session_factory =sessionmaker(bind=db_engine)
db_session =scoped_session(db_session_factory)
|
and later, whenever I have data to dump I do something like:
|
# dumping phase
db_session.add(data)
db_session.commit()
|
I would need to handle, from the client side, an unexpected
disconnection of the server from the network (e.g. it switches off, the
internet goes down or merely I unplug the ethernet cable...).
I would like to get an exception or something to handle this during the
dumping phase, for instance setting a connection timeout on client side.
In case of exception I could dump the data on a local database for instance.
If server goes down during dumping phase, it will wait about 15 minutes
and then sqlalchemy raises an exception like:
|
sqlalchemy.exc.OperationalError:(OperationalError)(2013,'Lost
connection to MySQL server during query')
sqlalchemy.exc.OperationalError:(OperationalError)(2003,"Can't
connect to MySQL server on...)
|
According to:
http://stackoverflow.com/questions/1209640/how-do-you-control-mysql-timeouts-from-sqlalchemy
That time of 15 minutes is related to the TCP level configuration of the
client, and I could reduce it for instance with:
|
~# sysctl net.ipv4.tcp_retries2=1
|
In this way I could get the exception in 1 second, but this
configuration change for all the TCP connections of the operating
system, that is undesirable.
Is there a way to control the connection timeout on client side only for
the sqlalchemy session?
And get an exception if the server cannot be reached during the dumping
phase, after a limited time duration (e.g. after 1 second, I don't want
to wait 15 minutes)?
Per MySQL documentation at
https://dev.mysql.com/doc/refman/5.6/en/mysql-options.html:
MYSQL_OPT_READ_TIMEOUT (argument type: unsigned int *)
The timeout in seconds for each attempt to read from the server. There
are retries if necessary, so the total effective timeout value is three
times the option value. You can set the value so that a lost connection
can be detected earlier than the TCP/IP Close_Wait_Timeout value of 10
minutes.
I'm not sure this works in every case, I believe we had a case where we
were trying to detect a disconnect in the case of a virtual IP being
moved and this was insufficient, but I think for the simple "network
down" case this might be enough. In any case, that page has all the
options there are, so that's what we have to work with.
Both mysqlclient (and probably the legacy mysql-python, which you
shouldn't use) accept this as "read_timeout", and it is also accepted by
pymysql.
https://mysqlclient.readthedocs.io/en/latest/user_guide.html#functions-and-attributes
Thanks
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.
---
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
