时间戳

首页 > 解决方案 > 为什么在我更改超时变量后，Amazon RDS 上的 MySQL 会不断使我的连接超时？

python - 为什么在我更改超时变量后，Amazon RDS 上的 MySQL 会不断使我的连接超时？

问题描述

我通过亚马逊的 RDS 服务租用了 MySQL 数据库。我设法习惯了数据库并且可以在 Python 中操作它。我正在尝试设置连接到 MySQL 数据库的超时时间，但这样做没有运气。我通过 Python 与 MySQL 数据库的连接总是在 10-11 分钟后超时。

要编辑 MySQL 服务器的超时值，您需要访问其配置文件。我查看了 Amazon RDS 文档并通过 Amazon 了解到这是通过“参数组”完成的。我创建了一个新的参数组并将值：“connect_timeout”、“interactive_timeout”和“wait_timeout”分别设置为 1000000。我将我的数据库与这个参数组相关联，然后重新启动它。我用 Python 重新连接到我的数据库。大约 10 分钟不活动后，我的连接仍然超时。

除了在我的参数组中指定超时之外，我还尝试在 Python 中明确指定超时，但仍然没有运气。这是我要运行的代码：

import time
import mysql.connector
DataBase = 'DataBase'
Username = 'Username'
Password = 'Password'
Host = 'mydb.randomstring.region.rds.amazonaws.com'
cnx = mysql.connector.connect(
    user=Username, 
    password=Password, 
    host=Host, 
    database=DataBase,
    connect_timeout=1000000
)
mycursor = cnx.cursor()
time.sleep(900)
if cnx.is_connected():
    print('Database did not time out.')
else:
    print('Database timed out.')

>>> Database timed out.

任何人对为什么会发生这种情况有任何想法？

此外，如果我在一段时间不活动后尝试运行命令，而不是仅仅询问 Python 我的连接是否仍然打开，我会收到此超时错误。如果我在不活动后尝试发出命令，这是我得到的错误：

>>> # Establish a MySQL connection and do nothing for about 10 minutes.
>>> sql = "INSERT INTO Table (value_1, value_2) VALUES (%s, %s)"
>>> val = ('A', 'B')
>>> mycursor.execute(sql, val)
Traceback (most recent call last):
  File "C:\Users\MyUsername\AppData\Local\Programs\Python\Python37\lib\site-packages\pymysql\connections.py", line 691, in _read_bytes
    data = self._rfile.read(num_bytes)
  File "C:\Users\MyUsername\AppData\Local\Programs\Python\Python37\lib\socket.py", line 589, in readinto
    return self._sock.recv_into(b)
ConnectionResetError: [WinError 10054] An existing connection was forcibly closed by the remote host

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "<pyshell#4>", line 1, in <module>
    mycursor.execute(sql, val)
  File "C:\Users\MyUsername\AppData\Local\Programs\Python\Python37\lib\site-packages\pymysql\cursors.py", line 170, in execute
    result = self._query(query)
  File "C:\Users\MyUsername\AppData\Local\Programs\Python\Python37\lib\site-packages\pymysql\cursors.py", line 328, in _query
    conn.query(q)
  File "C:\Users\MyUsername\AppData\Local\Programs\Python\Python37\lib\site-packages\pymysql\connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "C:\Users\MyUsername\AppData\Local\Programs\Python\Python37\lib\site-packages\pymysql\connections.py", line 732, in _read_query_result
    result.read()
  File "C:\Users\MyUsername\AppData\Local\Programs\Python\Python37\lib\site-packages\pymysql\connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "C:\Users\MyUsername\AppData\Local\Programs\Python\Python37\lib\site-packages\pymysql\connections.py", line 657, in _read_packet
    packet_header = self._read_bytes(4)
  File "C:\Users\MyUsername\AppData\Local\Programs\Python\Python37\lib\site-packages\pymysql\connections.py", line 699, in _read_bytes
    "Lost connection to MySQL server during query (%s)" % (e,))
pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query ([WinError 10054] An existing connection was forcibly closed by the remote host)')

标签： pythonmysqlpython-3.xamazon-web-servicesamazon-rds

解决方案

我找到了解决方案。我正在切换到 PostgreSQL。

推荐阅读