Connection reset by peer: Lost connection to MySQL server during query ([Errno 54]

0

I have a python application that is querying a MySQL Database (v 8.0.28) hosted on AWS RDS. The code in the application looks something like this:

import pymysql
from sqlalchemy import create_engine
user = 'usr1'
pwd = 'pwd1'
host =  'aaaaa.us-west-1.rds.amazonaws.com'
port = 3306
database = 'db1'
engine = create_engine("mysql+pymysql://{}:{}@{}/{}".format(user,pwd,host,database))


con = engine.connect()

query = '''
        select *
        from db1.tbl1
        '''

df = pd.read_sql(query, con)
   
con.close()

The application triggers a query upon loading and query returns successful results. However, after a few minutes (2-3) of idle time, I get an error when it tries to query the DB again / establish a connection.

Error traceback:

File "/Applications/Anaconda/anaconda3/lib/python3.9/site-packages/pymysql/connections.py", line 692, in _read_packet
    packet_header = self._read_bytes(4)
  File "/Applications/Anaconda/anaconda3/lib/python3.9/site-packages/pymysql/connections.py", line 738, in _read_bytes
    raise err.OperationalError(
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2013, 'Lost connection to MySQL server during query ([Errno 54] Connection reset by peer)')
[SQL: SELECT users.id AS users_id, users.fullname AS users_fullname, users.email AS users_email, users.password AS users_password, users.name AS users_name
FROM users 
WHERE users.id = %(pk_1)s]
[parameters: {'pk_1': 1}]
(Background on this error at: https://sqlalche.me/e/14/e3q8)

We're trying to isolate the issue to whether this in on the Application side or DB host side.

https://aws.amazon.com/rds/mysql/

已提問 2 年前檢視次數 1933 次
1 個回答
0

Error traceback : 'Lost connection to MySQL server during query ([Errno 54] Connection reset by peer)'

Peer connection resets are sudden break in connections from the server (peer). Connection Resets could occur due to a number of reasons with problems originating either at the client application, DB server (RDS instance) or the network connectivity between them.

From the description provided, since the application throws the error after an idle time (2-3 minutes). It is likely the application is not gracefully terminating the older connection while one of the following scenarios being true:

  1. The MySQL timeout parameters of the RDS database is set lower than the Client connection timeout making RDS to terminate/reset the connection.

Timeout parameters in RDS MySQL - https://aws.amazon.com/blogs/database/best-practices-for-configuring-parameters-for-amazon-rds-for-mysql-part-3-parameters-related-to-security-operational-manageability-and-connectivity-timeout/

  1. The ‘max_allowed_packet’ parameter of your RDS database value is too small or queries require more memory than you have allocated for mysqld. You must increase this value if you are using large BLOB columns or long strings.

max_allowed_packet parameter - https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html

  1. Your network firewall, intermediatory proxy (RDS Proxy - IdleClientTimeout) or network load balancers (AWS ELB – IdleTimeout) has a timeout established that terminate “idle” TCP connections when there is no activity on them for a certain period of time.

IdleClientTimeout in RDS Proxy - https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy-managing.html#rds-proxy-connection-pooling-tuning.idleclienttimeout

IdleTimeout in AWS EBS - https://docs.aws.amazon.com/elasticloadbalancing/latest/classic/config-idle-timeout.html

If the issue is DB induced, you may find more information in the RDS Error logs. Connection reset may also happen due to abrupt unavailability of the DB instance or an RDS network incident. You can check the NetworkTransmitThroughput and NetworkReceiveThroughput CloudWatch metrics for a sudden drop indicative of RDS network problems, and check RDS Events and Error logs indicative of a database reboot or crash.

However, based on the the described pattern of error occurrence, it appears the problem is either at the Intermediatory Network (firewall, proxy or load balancer) or at the Application level.

AWS
支援工程師
已回答 2 年前

您尚未登入。 登入 去張貼答案。

一個好的回答可以清楚地回答問題並提供建設性的意見回饋,同時有助於提問者的專業成長。

回答問題指南