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

Question

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/

Faisal_K · Answer

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/

2. 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

3. 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.

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

Contenus pertinents