Control connection timeout of MySQL client from SQLAlchemy

[SG]

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)?

Thanks

[mike bayer]

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 sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.