Packet sequence number wrong with sqlalchemy + mysql with zero dates #101
Description
Hello,
When NO_ZERO_DATE is set in mysql and there are rows with zero dates, driver fails with "Packet sequence number wrong" error. Please see complete example below
init.sql
grant all privileges on *.* to 'user'@'%';
flush privileges;
create database db_name;
use db_name;
create table `users` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
);
insert into `users` values (1, '0000-00-00 00:00:00');
docker-compose.yml
version: "3.7"
services:
mysql_db:
image: percona:5.7.43
container_name: mysql_db
command: ["--sql-mode="]
environment:
MYSQL_ALLOW_EMPTY_PASSWORD: true
MYSQL_USER: user
MYSQL_PASSWORD: password
volumes:
- ./init.sql:/docker-entrypoint-initdb.d/init.sql
ports:
- "13306:3306"
requirements.txt
asyncmy==0.2.9
SQLAlchemy==2.0.31
main.py
import asyncio
from datetime import datetime
from sqlalchemy import TIMESTAMP, Integer, select
from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
class Base(DeclarativeBase):
pass
class Users(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(Integer, primary_key=True, index=True)
created_at: Mapped[datetime] = mapped_column(
TIMESTAMP,
nullable=False,
)
async def run():
engine = create_async_engine("mysql+asyncmy://user:password@localhost:13306/db_name")
async with engine.connect() as conn:
await conn.execute(select(Users))
if __name__ == "__main__":
asyncio.run(run())
docker compose up --detach
pip3 install -r requirements.txt
python3 main.pyTraceback (most recent call last):
File "/Users/maxim/Projects/tmp_20240801/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1127, in _rollback_impl
self.engine.dialect.do_rollback(self.connection)
File "/Users/maxim/Projects/tmp_20240801/.venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 698, in do_rollback
dbapi_connection.rollback()
File "/Users/maxim/Projects/tmp_20240801/.venv/lib/python3.12/site-packages/sqlalchemy/dialects/mysql/asyncmy.py", line 219, in rollback
self.await_(self._connection.rollback())
File "/Users/maxim/Projects/tmp_20240801/.venv/lib/python3.12/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 132, in await_only
return current.parent.switch(awaitable) # type: ignore[no-any-return,attr-defined] # noqa: E501
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/maxim/Projects/tmp_20240801/.venv/lib/python3.12/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 196, in greenlet_spawn
value = await result
^^^^^^^^^^^^
File "asyncmy/connection.pyx", line 412, in rollback
File "asyncmy/connection.pyx", line 375, in _read_ok_packet
File "asyncmy/connection.pyx", line 627, in read_packet
asyncmy.errors.InternalError: Packet sequence number wrong - got 6 expected 1
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/Users/maxim/Projects/tmp_20240801/main.py", line 28, in <module>
asyncio.run(run())
File "/usr/local/Cellar/[email protected]/3.12.3/Frameworks/Python.framework/Versions/3.12/lib/python3.12/asyncio/runners.py", line 194, in run
return runner.run(main)
^^^^^^^^^^^^^^^^
File "/usr/local/Cellar/[email protected]/3.12.3/Frameworks/Python.framework/Versions/3.12/lib/python3.12/asyncio/runners.py", line 118, in run
return self._loop.run_until_complete(task)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/Cellar/[email protected]/3.12.3/Frameworks/Python.framework/Versions/3.12/lib/python3.12/asyncio/base_events.py", line 687, in run_until_complete
return future.result()
^^^^^^^^^^^^^^^
File "/Users/maxim/Projects/tmp_20240801/main.py", line 24, in run
async with engine.connect() as conn:
File "/Users/maxim/Projects/tmp_20240801/.venv/lib/python3.12/site-packages/sqlalchemy/ext/asyncio/engine.py", line 895, in __aexit__
await asyncio.shield(task)
File "/Users/maxim/Projects/tmp_20240801/.venv/lib/python3.12/site-packages/sqlalchemy/ext/asyncio/engine.py", line 481, in close
await greenlet_spawn(self._proxied.close)
File "/Users/maxim/Projects/tmp_20240801/.venv/lib/python3.12/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 201, in greenlet_spawn
result = context.throw(*sys.exc_info())
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/maxim/Projects/tmp_20240801/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1242, in close
self._transaction.close()
File "/Users/maxim/Projects/tmp_20240801/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2586, in close
self._do_close()
File "/Users/maxim/Projects/tmp_20240801/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2724, in _do_close
self._close_impl()
File "/Users/maxim/Projects/tmp_20240801/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2710, in _close_impl
self._connection_rollback_impl()
File "/Users/maxim/Projects/tmp_20240801/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2702, in _connection_rollback_impl
self.connection._rollback_impl()
File "/Users/maxim/Projects/tmp_20240801/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1129, in _rollback_impl
self._handle_dbapi_exception(e, None, None, None, None)
File "/Users/maxim/Projects/tmp_20240801/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2353, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/Users/maxim/Projects/tmp_20240801/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1127, in _rollback_impl
self.engine.dialect.do_rollback(self.connection)
File "/Users/maxim/Projects/tmp_20240801/.venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 698, in do_rollback
dbapi_connection.rollback()
File "/Users/maxim/Projects/tmp_20240801/.venv/lib/python3.12/site-packages/sqlalchemy/dialects/mysql/asyncmy.py", line 219, in rollback
self.await_(self._connection.rollback())
File "/Users/maxim/Projects/tmp_20240801/.venv/lib/python3.12/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 132, in await_only
return current.parent.switch(awaitable) # type: ignore[no-any-return,attr-defined] # noqa: E501
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/maxim/Projects/tmp_20240801/.venv/lib/python3.12/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 196, in greenlet_spawn
value = await result
^^^^^^^^^^^^
File "asyncmy/connection.pyx", line 412, in rollback
File "asyncmy/connection.pyx", line 375, in _read_ok_packet
File "asyncmy/connection.pyx", line 627, in read_packet
sqlalchemy.exc.InternalError: (asyncmy.errors.InternalError) Packet sequence number wrong - got 6 expected 1
(Background on this error at: https://sqlalche.me/e/20/2j85)
Exception terminating connection <AdaptedConnection <asyncmy.connection.Connection object at 0x10bd16090>>
Traceback (most recent call last):
File "/Users/maxim/Projects/tmp_20240801/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 374, in _close_connection
self._dialect.do_terminate(connection)
File "/Users/maxim/Projects/tmp_20240801/.venv/lib/python3.12/site-packages/sqlalchemy/dialects/mysql/asyncmy.py", line 312, in do_terminate
dbapi_connection.terminate()
File "/Users/maxim/Projects/tmp_20240801/.venv/lib/python3.12/site-packages/sqlalchemy/dialects/mysql/asyncmy.py", line 226, in terminate
self._connection.close()
File "asyncmy/connection.pyx", line 336, in asyncmy.connection.Connection.close
File "/usr/local/Cellar/[email protected]/3.12.3/Frameworks/Python.framework/Versions/3.12/lib/python3.12/asyncio/selector_events.py", line 1210, in close
super().close()
File "/usr/local/Cellar/[email protected]/3.12.3/Frameworks/Python.framework/Versions/3.12/lib/python3.12/asyncio/selector_events.py", line 875, in close
self._loop.call_soon(self._call_connection_lost, None)
File "/usr/local/Cellar/[email protected]/3.12.3/Frameworks/Python.framework/Versions/3.12/lib/python3.12/asyncio/base_events.py", line 795, in call_soon
self._check_closed()
File "/usr/local/Cellar/[email protected]/3.12.3/Frameworks/Python.framework/Versions/3.12/lib/python3.12/asyncio/base_events.py", line 541, in _check_closed
raise RuntimeError('Event loop is closed')
RuntimeError: Event loop is closed
The garbage collector is trying to clean up non-checked-in connection <AdaptedConnection <asyncmy.connection.Connection object at 0x10bd16090>>, which will be terminated. Please ensure that SQLAlchemy pooled connections are returned to the pool explicitly, either by calling ``close()`` or by using appropriate context managers to manage their lifecycle.
sys:1: SAWarning: The garbage collector is trying to clean up non-checked-in connection <AdaptedConnection <asyncmy.connection.Connection object at 0x10bd16090>>, which will be terminated. Please ensure that SQLAlchemy pooled connections are returned to the pool explicitly, either by calling ``close()`` or by using appropriate context managers to manage their lifecycle.