XiaobinZhao
commented
1 month ago same problem
Open max1mn opened 3 months ago
XiaobinZhao
commented
1 month ago same problem
XiaobinZhao
commented
1 month ago @max1mn 换 aiomysql 可以
Cycloctane
commented
1 month ago 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.dateZero 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.
It seems that this issue causes sqlalchemy to skip the packet without adding the sequence number, which result in wrong packet number.
long2ice
commented
1 month ago Thanks! Could you make a PR to fix that?
Cycloctane
commented
1 month ago 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
commented
6 days ago 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
commented
6 days ago 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?
WilliamStam
commented
6 days ago 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!
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
docker-compose.yml
requirements.txt
main.py