Packet sequence number wrong – got 1 expected 0

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)'''

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.