Operational Error on MySQL RDS Instance
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.
On the client side, I am using the following stack
- Ubuntu 22.04
- Python 3.10
- SQL Alchemy 1.4.44
#!/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: 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 certainly this makes the database unsuitable for production usage. 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)
I have already opened the necessary port in the security group to allow access from my client machine. So is this a resource issue or code/credential issue? Do I need to fire up a bigger database somehow, or resort to multi-zone deployment etc.?
Additional info:
Said it in the comment already, but to make it clear, my client is a Dell Inspiron Laptop running Linux Mint, the security group (of the RDS instance) is configured to accept connection, and from the same laptop, I can connect to the mysql>
prompt without any problem. It is the sqlalchemy client that is refusing to connect sometimes.
- Neueste
- Die meisten Stimmen
- Die meisten Kommentare
Relevanter Inhalt
- AWS OFFICIALAktualisiert vor 3 Jahren
- AWS OFFICIALAktualisiert vor einem Jahr
- AWS OFFICIALAktualisiert vor 3 Jahren
- AWS OFFICIALAktualisiert vor 2 Jahren
What is the instance type of your client machine?
@Lihu Wu the client now is my laptop (Dell Inspiron, running Linux Mint Vera, basically Ubuntu 22.04), although eventually I want to run it from an on-premise headless server. I have already configured the security group policy to allow connection from my client, and can use the
mysql>
prompt on Bash (password login) without problem. It is the sqlalchemy client that is refusing to connect sometimes.