通过python连接时如何更改默认的Mysql连接超时？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14726789/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I change the default Mysql connection timeout when connecting through python?
提问by Animesh Pandey
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.
我使用 python 连接到 mysql 数据库con = _mysql.connect('localhost', 'dell-pc', '', 'test')我编写的程序需要大量时间才能完全执行，即大约 10 个小时。实际上，我正在尝试从语料库中读取不同的单词。读取完成后出现超时错误。
I checked Mysql default timeouts which were:
我检查了 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 |
+----------------------------+----------+
How can I change the default timeout ?
如何更改默认超时？
采纳答案by Ivelin
Do:
做：
con.query('SET GLOBAL connect_timeout=28800')
con.query('SET GLOBAL wait_timeout=28800')
con.query('SET GLOBAL interactive_timeout=28800')
Parameter meaning (taken from MySQL Workbench in Navigator: Instance > Options File > Tab "Networking" > Section "Timeout Settings")
参数含义（取自 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_timeoutNumber of seconds the server waits for activity on an interactive connection before closing it
- wait_timeoutNumber of seconds the server waits for activity on a connection before closing it
- connect_timeout: mysqld 服务器在响应“错误握手”之前等待连接数据包的秒数
- Interactive_timeout服务器在关闭交互式连接之前等待它的活动的秒数
- wait_timeout服务器在关闭连接之前等待连接上的活动的秒数
BTW: 28800 seconds are 8 hours, so for a 10 hour execution time these values should be actually higher.
顺便说一句：28800 秒是 8 小时，因此对于 10 小时的执行时间，这些值实际上应该更高。
回答by Devart
You change default value in MySQL configuration file (option connect_timeoutin mysqldsection) -
你在MySQL配置文件（选项更改默认值connect_timeout在mysqld的部分） -
[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;
回答by nsane
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.connectcall. For example,
我知道这是一个老问题，但只是为了记录，这也可以通过将适当的连接选项作为参数传递给_mysql.connect调用来完成。例如，
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.
注意关键字参数（host、passwd 等）的使用。它们提高了代码的可读性。
For detail about different arguments that you can pass to _mysql.connect, see MySQLdb API documentation
有关可以传递给的不同参数的详细信息_mysql.connect，请参阅MySQLdb API 文档
回答by xs2rashid
MAX_EXECUTION_TIME is also an important parameter for long running queries.Will work for MySQL 5.7 or later.
MAX_EXECUTION_TIME 也是长时间运行查询的重要参数。适用于 MySQL 5.7 或更高版本。
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;
