How to Change the Default MySQL Connection Timeout When Connecting Through Python

How can I change the default Mysql connection timeout when connecting through python?

Do:

con.query('SET GLOBAL connect_timeout=28800')
con.query('SET GLOBAL interactive_timeout=28800')
con.query('SET GLOBAL wait_timeout=28800')

Parameter meaning (taken from MySQL Workbench in Navigator: Instance > Options File > Tab "Networking" > Section "Timeout Settings")

• connect_timeout: Number of seconds the mysqld server waits for a connect packet before responding with 'Bad handshake'
• interactive_timeout Number of seconds the server waits for activity on an interactive connection before closing it
• wait_timeout Number of seconds the server waits for activity on a connection before closing it

BTW: 28800 seconds are 8 hours, so for a 10 hour execution time these values should be actually higher.

MySQL Connector for Python

According to MySQL Connector/Python :: 6 Connector/Python Connection Arguments in the official documentation:

To set a timeout value for connections, use connection_timeout.

How to set MySQLdb Connection timeout to infinity in python

The problem is the connection is closed from the db server side, what you have to do is;

• Changing the timeout of mysql
  Or more usefull
• Just reconnect to the db again in your loop

If you use linux you can use cron to launch your script every X sec, if you use windows use the scheduling task service to launch the script when you desire.

How to setup a connection timeout depending of the user login in MySQL

There's no per-user timeout configuration, but you can set the wait_timeout value dynamically. That is, after you make a connection as a given user, you can issue a statement to change the timeout value to what you want it to be for that user's session.

Try the following experiment in the mysql command-line client:

mysql> SHOW VARIABLES LIKE 'wait_timeout';

...shows 28800 (i.e. 8 hours), which is the default wait_timout.

mysql> SET SESSION wait_timeout = 60;
mysql> SHOW VARIABLES LIKE 'wait_timeout';

...shows 60.

Then you can quit the session, reconnect, and again the default wait_timeout is 28800. So it's limited to the scope of the current session.

You can also open a second window and start a separate mysql client session, to prove that changing the wait_timeout in one session does not affect other concurrent sessions.

Python mysql connection timeout AFTER connection has been made with RDS via lambda

Your task timeout is probably set to 5 seconds.

The task is timing out because it's taking too long to complete, not because of any communication problem with the DB.

Increase the timeout setting or make the function run faster.

Reconnecting MySQL on timeout

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.

MySQLdb initial connection timeout

You can pass connect_timeout argument to MySQLdb.connect function. Quote from docs:

connect_timeout

Abort if connect is not completed within given number
of seconds.

Default: no timeout (?)

Why does MySQL on Amazon RDS keep timing out my connections after I change timeout variables?

I found a solution. I am switching to PostgreSQL.

---

Related Topics