Operational Error on MySQL AWS RDS Instance

Question

The heading pretty much describes it all, here are some more information on the Amazon RDS I am using.

Engine: MySQL Community 8.0.28
Class db.r6g.large
Multi-AZ no. The data volume is very small, not even a few megs, as I am just doing some PoC to test the connectivity.
Latency from MySQL commandline: 10ms
Availability zone: ap-southeast-1
Set up the inbound traffic rule for port 3306 of the relevant security group.

These are the client details

Ubuntu 22.04
Python 3.10
SQL Alchemy 1.4.44
mysql Ver 8.0.32-0ubuntu0.22.04.2 for Linux on x86_64 ((Ubuntu))
Geographic location: Singapore

Here is a sample code snippet (hopefully, self-explanatory)

#!/usr/bin/env python3
# encoding: utf-8
uri:str = 'mysql+mysqlconnector://della:random_password@tensorflow-dump.apj-xjge.ap-southeast-1.rds.amazonaws.com:3306/convnet'
with create_engine(url=uri).connect() as out_client: # This line throws error
    logging.info(msg=f'Connected to {uri}.')        
    results.to_sql(name='machine_name', con=out_client,
                                            if_exists='append', index=False)
    logging.info(msg=f'Pushed data.')

So, I am encountering the operational error on a sporadic basis, once every 3-4 attempts of running the above code. It is not reproducible, but makes the database unsuitable for production usage. When I do get the error, the flow waits for about 12 minutes at the create_engine command before throwing the error.

Here is the stacktrace.

Traceback (most recent call last):
  File "/home/della/supply-chain-dev/clustering_results_postprocess/src/push.py", line 119, in <module>
    asyncio.run(main=main())
  File "/usr/lib/python3.10/asyncio/runners.py", line 44, in run
    return loop.run_until_complete(main)
  File "/usr/lib/python3.10/asyncio/base_events.py", line 646, in run_until_complete
    return future.result()
  File "/home/della/supply-chain-dev/clustering_results_postprocess/src/push.py", line 111, in main
    with create_engine(url=out_dbase_uri).connect() as out_client:
  File "/home/della/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3315, in connect
    return self._connection_cls(self, close_with_result=close_with_result)
  File "/home/della/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 96, in __init__
    else engine.raw_connection()
  File "/home/della/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3394, in raw_connection
    return self._wrap_pool_connect(self.pool.connect, _connection)
  File "/home/della/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3364, in _wrap_pool_connect
    Connection._handle_dbapi_exception_noconnection(
  File "/home/della/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2198, in _handle_dbapi_exception_noconnection
    util.raise_(
  File "/home/della/.local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 210, in raise_
    raise exception
  File "/home/della/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3361, in _wrap_pool_connect
    return fn()
  File "/home/della/.local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 325, in connect
    return _ConnectionFairy._checkout(self)
  File "/home/della/.local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 888, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/della/.local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 491, in checkout
    rec = pool._do_get()
  File "/home/della/.local/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 145, in _do_get
    with util.safe_reraise():
  File "/home/della/.local/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/home/della/.local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 210, in raise_
    raise exception
  File "/home/della/.local/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
    return self._create_connection()
  File "/home/della/.local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 271, in _create_connection
    return _ConnectionRecord(self)
  File "/home/della/.local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 386, in __init__
    self.__connect()
  File "/home/della/.local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 684, in __connect
    with util.safe_reraise():
  File "/home/della/.local/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/home/della/.local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 210, in raise_
    raise exception
  File "/home/della/.local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 680, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/home/della/.local/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 578, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/della/.local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 598, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/home/della/.local/lib/python3.10/site-packages/mysql/connector/pooling.py", line 293, in connect
    return CMySQLConnection(*args, **kwargs)
  File "/home/della/.local/lib/python3.10/site-packages/mysql/connector/connection_cext.py", line 118, in __init__
    self.connect(**kwargs)
  File "/home/della/.local/lib/python3.10/site-packages/mysql/connector/abstracts.py", line 1178, in connect
    self._open_connection()
  File "/home/della/.local/lib/python3.10/site-packages/mysql/connector/connection_cext.py", line 293, in _open_connection
    raise get_mysql_exception(
sqlalchemy.exc.OperationalError: (mysql.connector.errors.OperationalError) 2013 (HY000): Lost connection to MySQL server at 'reading authorization packet', system error: 104
(Background on this error at: https://sqlalche.me/e/14/e3q8)

So I am not sure if it is a

resource issue (need a machine with more RAM, or more powerful database instance)
something to do with credentials, connection string, port firewall etc. (in that case, the code would fail with a guarantee)
merely the internet connection at my work is not reliable (there is no other obvious sign of the connection being snappy, the usual apps, browsing etc. work very well).

When I try with mysql> command line, the connectivity works very well consistently.

RolandoMySQLDBA · Answer 1 · 2023-03-12T17:51:21.670

From the looks of the code you gave in the post, you did not set the connect_time or the pool_recycle value for the create_engine command.

Please note that the mysql command line is a different client-side platform. There are the variables interactive_timeout and wait_timeout, which are usually set very high by defaults (28800 (which is in seconds), is also 8 hours). So you would not get a session time out so intermittently.

RECOMMENDATION

You should login to the RDS Instance and run this

mysql> SELECT @@global.interactive_timeout,@@global.wait_timeout;

Then, set your pool_recycle to whichever value is smaller.

score 0 · Answer 2 · answered Mar 01 '23 at 03:17

0

This sounds like a networking/routing issue because of the system error 104.

answered Mar 01 '23 at 03:17

Brian Estrada

114
7

Operational Error on MySQL AWS RDS Instance

2 Answers2

RECOMMENDATION