asyncmy
asyncmy copied to clipboard
long2ice
Packet sequence number wrong with sqlalchemy + mysql with zero dates
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.py
Traceback (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.
asyncmy cannot handle zero dates properly.
import asyncio
import asyncmy
async def main():
conn = await asyncmy.connect(...)
async with conn.cursor() as cur:
await cur.execute("SELECT * FROM asyncmy WHERE id=1")
result = await cur.fetchall()
conn.close()
if __name__ == "__main__":
loop = asyncio.get_event_loop()
loop.run_until_complete(main())
Traceback (most recent call last):
File "asyncmy/converters.pyx", line 160, in asyncmy.converters.convert_datetime
ValueError: year 0 is out of range
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "asyncmy/converters.pyx", line 270, in asyncmy.converters.convert_date
ValueError: invalid literal for int() with base 10: '00 00:00:00'
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "***", line 16, in <module>
loop.run_until_complete(main())
File "/usr/lib/python3.11/asyncio/base_events.py", line 653, in run_until_complete
return future.result()
^^^^^^^^^^^^^^^
File "***", line 9, in main
await cur.execute("SELECT * FROM test.asyncmy WHERE id=1")
File "asyncmy/cursors.pyx", line 179, in execute
File "asyncmy/cursors.pyx", line 364, in _query
File "asyncmy/connection.pyx", line 494, in query
File "asyncmy/connection.pyx", line 682, in _read_query_result
File "asyncmy/connection.pyx", line 1076, in read
File "asyncmy/connection.pyx", line 1147, in _read_result_packet
File "asyncmy/connection.pyx", line 1185, in _read_rowdata_packet
File "asyncmy/connection.pyx", line 1203, in asyncmy.connection.MySQLResult._read_row_from_packet
File "asyncmy/converters.pyx", line 134, in asyncmy.converters.convert_datetime
File "asyncmy/converters.pyx", line 162, in asyncmy.converters.convert_datetime
File "asyncmy/converters.pyx", line 272, in asyncmy.converters.convert_date
TypeError: Cannot convert str to datetime.date
Zero dates allowed by MySQL are invalid in python datetime. PyMySQL and aiomysql handles this issue by returning invalid datetime as str.
asyncmy reuses PyMySQL's codes in convert_date function and uses cython definition that only allows returning datetime.date type. Returning str will cause cython to raise TypeError.
https://github.com/long2ice/asyncmy/blob/161b2dd80a817efebb7c97942261afcbbc6c36f8/asyncmy/converters.pyx#L253
It seems that this issue causes sqlalchemy to skip the packet without adding the sequence number, which result in wrong packet number.
Thanks! Could you make a PR to fix that?
@long2ice I've changed return type of convert_datetime(), convert_timedelta(), convert_time() and convert_date() to object so that str can be accepted as returned value. Now asyncmy accepts invalid datetime and returns them as strings. This behavior is the same as in PyMySQL and aiomysql.
strangely i get the packet sequence wrong with select * from (select ....) anon queries with real dates in it. want to debug first before making a post.
im sorry to do this. mind just checking if query = select("*").select_from(query.subquery()) works with your test fix?
strangely i get the packet sequence wrong with
select * from (select ....) anonqueries with real dates in it. want to debug first before making a post.im sorry to do this. mind just checking if
query = select("*").select_from(query.subquery())works with your test fix?
I cannot reproduce it in 0.2.9. Could you provide a minimum example?
im sorry :( found the issue
seems that when an issue occurs while looping through the records and trying to insert it to a pydantic model and theres an error...
yet when you scroll aaaalllll the way to the top above all the this is caused by that exception stuff is the actual error. so seems in my case while the mysql connection is open and theres a pydantic error thrown in a fastapi app it probably doesnt close the connection or does something strange to it.
i originally thought it was the date fields cause if i remove them from the query it works. add them in and asyncmy complains about packets. so my minimal app sometimes worked and sometimes didnt again lol
again. sorry for wasting your time!