当通过 python 连接时，如何更改默认的 Mysql 连接超时？

我使用 python con = _mysql.connect('localhost', 'dell-pc', '', 'test')连接到 mysql 数据库 我编写的程序在完全执行时需要很多时间，也就是大约10个小时。事实上，我正在尝试从语料库中读出不同的单词。 读取结束后，出现超时错误。

我检查了 Mysql 的默认超时:

+----------------------------+----------+
| 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    |
+----------------------------+----------+

如何更改默认超时？

397286 次浏览
小开

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;
小开
最佳答案

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.

小开

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

小开

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;

提交答案