Packet sequence number wrong with sqlalchemy + mysql with zero dates #101

max1mn · 2024-08-02T09:17:52Z

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/python@3.12/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/python@3.12/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/python@3.12/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/python@3.12/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/python@3.12/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/python@3.12/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/python@3.12/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.

The text was updated successfully, but these errors were encountered:

XiaobinZhao · 2024-09-12T03:35:17Z

same problem

XiaobinZhao · 2024-09-12T07:22:02Z

@max1mn 换 aiomysql 可以

Cycloctane · 2024-09-23T04:58:35Z

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.

asyncmy/asyncmy/converters.pyx

Line 253 in 161b2dd

cpdef datetime.date convert_date(obj):

It seems that this issue causes sqlalchemy to skip the packet without adding the sequence number, which result in wrong packet number.

long2ice · 2024-09-23T06:04:41Z

Thanks! Could you make a PR to fix that?

Cycloctane · 2024-09-23T10:24:49Z

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.

WilliamStam · 2024-10-31T08:12:45Z

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?

Cycloctane · 2024-10-31T12:39:48Z

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?

I cannot reproduce it in 0.2.9. Could you provide a minimum example?

WilliamStam · 2024-10-31T15:03:54Z

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!

trim21 mentioned this issue Sep 21, 2024

Packet sequence number wrong #89

Open

Cycloctane linked a pull request Sep 23, 2024 that will close this issue

Fix zero dates issue #104

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Packet sequence number wrong with sqlalchemy + mysql with zero dates #101

Packet sequence number wrong with sqlalchemy + mysql with zero dates #101

max1mn commented Aug 2, 2024

XiaobinZhao commented Sep 12, 2024

XiaobinZhao commented Sep 12, 2024

Cycloctane commented Sep 23, 2024

long2ice commented Sep 23, 2024

Cycloctane commented Sep 23, 2024

WilliamStam commented Oct 31, 2024 •

edited

Loading

Cycloctane commented Oct 31, 2024 •

edited

Loading

WilliamStam commented Oct 31, 2024 •

edited

Loading

Packet sequence number wrong with sqlalchemy + mysql with zero dates #101

Packet sequence number wrong with sqlalchemy + mysql with zero dates #101

Comments

max1mn commented Aug 2, 2024

XiaobinZhao commented Sep 12, 2024

XiaobinZhao commented Sep 12, 2024

Cycloctane commented Sep 23, 2024

long2ice commented Sep 23, 2024

Cycloctane commented Sep 23, 2024

WilliamStam commented Oct 31, 2024 • edited Loading

Cycloctane commented Oct 31, 2024 • edited Loading

WilliamStam commented Oct 31, 2024 • edited Loading

WilliamStam commented Oct 31, 2024 •

edited

Loading

Cycloctane commented Oct 31, 2024 •

edited

Loading

WilliamStam commented Oct 31, 2024 •

edited

Loading