Getting the below error in MariaDb and its causing timeout issue : Debugged it with removing skip-name-resolve, granting privileges to user@localhost, and in pyton create_engine used pool_pre_ping = True and pool_recycle=3600 but nothing seems to solve the issue. MariaDb version is 10.4.20 and Python is 3.9.Its not a multithreaded application as well. Any pointers on solving debugging this issue would be helpful.
This is the connection getting created:
db_connection_str = 'mysql+pymysql://root:root@localhost:3306/collection'
sql_engine = create_engine(db_connection_str,pool_pre_ping=True pool_recycle=3600)
def get_engine():
engine = create_engine(db_connection_str, pool_pre_ping=True,pool_recycle=3600)
return engine
The engine is used to get the connection as 'sql_engine.connect()' or used with 'Session = sessionmaker(bind=sql_engine) session = Session()'
Error :
”’
(Background on this error at: https://sqlalche.me/e/14/2j85)
Traceback (most recent call last):
File "D:Pythonlibsite-packagessqlalchemyenginebase.py", line 3212, in _wrap_pool_connect
return fn()
File "D:Pythonlibsite-packagessqlalchemypoolbase.py", line 307, in connect
return _ConnectionFairy._checkout(self)
File "D:Pythonlibsite-packagessqlalchemypoolbase.py", line 767, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "D:Pythonlibsite-packagessqlalchemypoolbase.py", line 425, in checkout
rec = pool._do_get()
File "D:Pythonlibsite-packagessqlalchemypoolimpl.py", line 146, in _do_get
self._dec_overflow()
File "D:Pythonlibsite-packagessqlalchemyutillanghelpers.py", line 72, in __exit__
with_traceback=exc_tb,
File "D:Pythonlibsite-packagessqlalchemyutilcompat.py", line 207, in raise_
raise exception
File "D:Pythonlibsite-packagessqlalchemypoolimpl.py", line 143, in _do_get
return self._create_connection()
File "D:Pythonlibsite-packagessqlalchemypoolbase.py", line 253, in _create_connection
return _ConnectionRecord(self)
File "D:Pythonlibsite-packagessqlalchemypoolbase.py", line 368, in __init__
self.__connect()
File "D:Pythonlibsite-packagessqlalchemypoolbase.py", line 611, in __connect
pool.logger.debug("Error on connect(): %s", e)
File "D:Pythonlibsite-packagessqlalchemyutillanghelpers.py", line 72, in __exit__
with_traceback=exc_tb,
File "D:Pythonlibsite-packagessqlalchemyutilcompat.py", line 207, in raise_
raise exception
File "D:Pythonlibsite-packagessqlalchemypoolbase.py", line 605, in __connect
connection = pool._invoke_creator(self)
File "D:Pythonlibsite-packagessqlalchemyenginecreate.py", line 578, in connect
return dialect.connect(*cargs, **cparams)
File "D:Pythonlibsite-packagessqlalchemyenginedefault.py", line 584, in connect
return self.dbapi.connect(*cargs, **cparams)
File "D:Pythonlibsite-packagespymysqlconnections.py", line 353, in __init__
self.connect()
File "D:Pythonlibsite-packagespymysqlconnections.py", line 632, in connect
self._get_server_information()
File "D:Pythonlibsite-packagespymysqlconnections.py", line 1055, in _get_server_information
packet = self._read_packet()
File "D:Pythonlibsite-packagespymysqlconnections.py", line 707, in _read_packet
% (packet_number, self._next_seq_id)
pymysql.err.InternalError: Packet sequence number wrong - got 1 expected 0
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "C:/xampp/htdocs/prod/collectionaccounting/bot_run.py", line 142, in <module>
main()
File "C:/xampp/htdocs/prod/collectionaccounting/bot_run.py", line 138, in main
BotRunLogger.log(level='success', msg='Bot Run completed successfully')
File "C:xampphtdocsprodcollectionaccountingAppLoggerBotRunLogger.py", line 12, in log
settings = config.get_settings()
File "C:xampphtdocsprodcollectionaccountingApiconfig.py", line 112, in get_settings
result = session.query(Settings).all()
File "D:Pythonlibsite-packagessqlalchemyormquery.py", line 2712, in all
return self._iter().all()
File "D:Pythonlibsite-packagessqlalchemyormquery.py", line 2850, in _iter
execution_options={"_sa_orm_load_options": self.load_options},
File "D:Pythonlibsite-packagessqlalchemyormsession.py", line 1688, in execute
conn = self._connection_for_bind(bind)
File "D:Pythonlibsite-packagessqlalchemyormsession.py", line 1530, in _connection_for_bind
engine, execution_options
File "D:Pythonlibsite-packagessqlalchemyormsession.py", line 747, in _connection_for_bind
conn = bind.connect()
File "D:Pythonlibsite-packagessqlalchemyenginebase.py", line 3166, in connect
return self._connection_cls(self, close_with_result=close_with_result)
File "D:Pythonlibsite-packagessqlalchemyenginebase.py", line 96, in __init__
else engine.raw_connection()
File "D:Pythonlibsite-packagessqlalchemyenginebase.py", line 3245, in raw_connection
return self._wrap_pool_connect(self.pool.connect, _connection)
File "D:Pythonlibsite-packagessqlalchemyenginebase.py", line 3216, in _wrap_pool_connect
e, dialect, self
File "D:Pythonlibsite-packagessqlalchemyenginebase.py", line 2070, in _handle_dbapi_exception_noconnection
sqlalchemy_exception, with_traceback=exc_info[2], from_=e
File "D:Pythonlibsite-packagessqlalchemyutilcompat.py", line 207, in raise_
raise exception
File "D:Pythonlibsite-packagessqlalchemyenginebase.py", line 3212, in _wrap_pool_connect
return fn()
File "D:Pythonlibsite-packagessqlalchemypoolbase.py", line 307, in connect
return _ConnectionFairy._checkout(self)
File "D:Pythonlibsite-packagessqlalchemypoolbase.py", line 767, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "D:Pythonlibsite-packagessqlalchemypoolbase.py", line 425, in checkout
rec = pool._do_get()
File "D:Pythonlibsite-packagessqlalchemypoolimpl.py", line 146, in _do_get
self._dec_overflow()
File "D:Pythonlibsite-packagessqlalchemyutillanghelpers.py", line 72, in __exit__
with_traceback=exc_tb,
File "D:Pythonlibsite-packagessqlalchemyutilcompat.py", line 207, in raise_
raise exception
File "D:Pythonlibsite-packagessqlalchemypoolimpl.py", line 143, in _do_get
return self._create_connection()
File "D:Pythonlibsite-packagessqlalchemypoolbase.py", line 253, in _create_connection
return _ConnectionRecord(self)
File "D:Pythonlibsite-packagessqlalchemypoolbase.py", line 368, in __init__
self.__connect()
File "D:Pythonlibsite-packagessqlalchemypoolbase.py", line 611, in __connect
pool.logger.debug("Error on connect(): %s", e)
File "D:Pythonlibsite-packagessqlalchemyutillanghelpers.py", line 72, in __exit__
with_traceback=exc_tb,
File "D:Pythonlibsite-packagessqlalchemyutilcompat.py", line 207, in raise_
raise exception
File "D:Pythonlibsite-packagessqlalchemypoolbase.py", line 605, in __connect
connection = pool._invoke_creator(self)
File "D:Pythonlibsite-packagessqlalchemyenginecreate.py", line 578, in connect
return dialect.connect(*cargs, **cparams)
File "D:Pythonlibsite-packagessqlalchemyenginedefault.py", line 584, in connect
return self.dbapi.connect(*cargs, **cparams)
File "D:Pythonlibsite-packagespymysqlconnections.py", line 353, in __init__
self.connect()
File "D:Pythonlibsite-packagespymysqlconnections.py", line 632, in connect
self._get_server_information()
File "D:Pythonlibsite-packagespymysqlconnections.py", line 1055, in _get_server_information
packet = self._read_packet()
File "D:Pythonlibsite-packagespymysqlconnections.py", line 707, in _read_packet
% (packet_number, self._next_seq_id)
sqlalchemy.exc.InternalError: (pymysql.err.InternalError) Packet sequence number wrong - got 1 expected 0
(Background on this error at: https://sqlalche.me/e/14/2j85)'''
Advertisement
Answer
Used poolclass=NullPool in create_engine. This has fixed the Packet Sequence error in this case.The app does not stop with Packet Sequence error anymore.