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

Question

I connected to a mysql database using python con = _mysql.connect('localhost', 'dell-pc', '', 'test') The program that I wrote takes a lot of time in full execution i.e. around 10 hours. Actually, I am trying to read distinct words from a corpus. After reading was finished there was a timeout error.

I checked Mysql default timeouts which were:

+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| connect_timeout            | 10       |
| delayed_insert_timeout     | 300      |
| innodb_lock_wait_timeout   | 50       |
| innodb_rollback_on_timeout | OFF      |
| interactive_timeout        | 28800    |
| lock_wait_timeout          | 31536000 |
| net_read_timeout           | 30       |
| net_write_timeout          | 60       |
| slave_net_timeout          | 3600     |
| wait_timeout               | 28800    |
+----------------------------+----------+

How can I change the default timeout ?

By the way, for those who wish to know how the above table was created, please execute the following query -- SHOW VARIABLES LIKE '%timeout'; — Arun Thundyill Saseendran, Aug 01 '18 at 19:53

score 99 · Accepted Answer · edited Oct 07 '20 at 15:52

99

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.

edited Oct 07 '20 at 15:52

Joshua Pinter

45,245
23
243
245

answered Oct 26 '13 at 18:50

Ivelin

12,293
5
37
35

4

Does `gobal` affect all conenctions which connect to db except the current connection? – zhkzyth Aug 19 '16 at 09:29
4

Would be nice to know the difference between those three, or at least have a link to the documentation. – jlh Apr 21 '17 at 09:09
2

@zhkzyth I had a similar question. This might help: https://stackoverflow.com/questions/4440336/mysql-wait-timeout Looks like `SET GLOBAL` will set the variable to all active and future connections until the next server restart. There is however a `SET SESSION` that appears to set the variable specific to the current session. There is also https://dev.mysql.com/doc/refman/5.6/en/set-variable.html for more info. – Norman Breau May 29 '17 at 16:55
@jlh This may help to know the difference between the 3 settings. https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html – Norman Breau May 29 '17 at 17:00
1

I would remove global, so it only affects the current session, usually you don't have privileges to do it with global – David Lemon Oct 03 '18 at 15:35
Mentioned further down, but you may also want to "SET global max_execution_time = 60000;" or more – Phillip Oct 07 '21 at 18:33

score 72 · Answer 2 · edited Apr 01 '15 at 14:43

72

You change default value in MySQL configuration file (option connect_timeout in mysqld section) -

[mysqld]
connect_timeout=100

If this file is not accessible for you, then you can set this value using this statement -

SET GLOBAL connect_timeout=100;

edited Apr 01 '15 at 14:43

dbepcepk

3
2

answered Feb 06 '13 at 10:38

Devart

119,203
23
166
186

Note that if you go with the config file method, the mysql client may not reflect the change, but your scripts will! See my post: http://stackoverflow.com/questions/9004400/mysql-modification-in-my-cnf-doesnt-take-effect/35186111#35186111 – BuvinJ Feb 03 '16 at 19:40
2

@dfrankow All values are in seconds. – simonwo May 24 '16 at 09:07

score 18 · Answer 3 · answered Nov 14 '16 at 14:40

I know this is an old question but just for the record this can also be done by passing appropriate connection options as arguments to the _mysql.connect call. For example,

con = _mysql.connect(host='localhost', user='dell-pc', passwd='', db='test',
          connect_timeout=1000)

Notice the use of keyword parameters (host, passwd, etc.). They improve the readability of your code.

For detail about different arguments that you can pass to _mysql.connect, see MySQLdb API documentation

score 12 · Answer 4 · edited Apr 06 '19 at 03:40

12

MAX_EXECUTION_TIME is also an important parameter for long running queries.Will work for MySQL 5.7 or later.

Check the current value

SELECT @@GLOBAL.MAX_EXECUTION_TIME, @@SESSION.MAX_EXECUTION_TIME;

Then set it according to your needs.

SET SESSION MAX_EXECUTION_TIME=2000;
SET GLOBAL MAX_EXECUTION_TIME=2000;

edited Apr 06 '19 at 03:40

Raj

22,346
14
99
142

answered Nov 02 '18 at 19:24

xs2rashid

953
12
16

How would you do it when you have another query to run ?con.execute(`SELECT @@SESSION.MAX_EXECUTION_TIME; SET SESSION MAX_EXECUTION_TIME=2000; SELECT * FROM test`) ? – CutePoison Jun 15 '20 at 07:12
5

Note, if the value is `0` that means there is no timeout enabled: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_execution_time – Joshua Pinter Oct 07 '20 at 15:54

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

4 Answers4

Linked