asyncmy
asyncmy copied to clipboard
long2ice
OverflowError: Python int too large to convert to C long
Python 3.9.7 (default, Sep 16 2021, 16:59:28) [MSC v.1916 64 bit (AMD64)] :: Anaconda, Inc. on win32 sqlalchemy 1.4.29
one statement is : where(paymentTime <= result) ,the result is 1642664534000. when I change the statement to where(paymentTime <= result/1000),there is no exception.
thanks.
Traceback (most recent call last): File "G:\ProgramData\Anaconda3\lib\site-packages\uvicorn\protocols\http\httptools_impl.py", line 375, in run_asgi result = await app(self.scope, self.receive, self.send) File "G:\ProgramData\Anaconda3\lib\site-packages\uvicorn\middleware\proxy_headers.py", line 75, in call return await self.app(scope, receive, send) File "G:\ProgramData\Anaconda3\lib\site-packages\fastapi\applications.py", line 211, in call await super().call(scope, receive, send) File "G:\ProgramData\Anaconda3\lib\site-packages\starlette\applications.py", line 112, in call await self.middleware_stack(scope, receive, send) File "G:\ProgramData\Anaconda3\lib\site-packages\starlette\middleware\errors.py", line 181, in call raise exc File "G:\ProgramData\Anaconda3\lib\site-packages\starlette\middleware\errors.py", line 159, in call await self.app(scope, receive, _send) File "G:\ProgramData\Anaconda3\lib\site-packages\starlette\exceptions.py", line 82, in call raise exc File "G:\ProgramData\Anaconda3\lib\site-packages\starlette\exceptions.py", line 71, in call await self.app(scope, receive, sender) File "G:\ProgramData\Anaconda3\lib\site-packages\starlette\routing.py", line 656, in call await route.handle(scope, receive, send) File "G:\ProgramData\Anaconda3\lib\site-packages\starlette\routing.py", line 259, in handle await self.app(scope, receive, send) File "G:\ProgramData\Anaconda3\lib\site-packages\starlette\routing.py", line 61, in app response = await func(request) File "G:\ProgramData\Anaconda3\lib\site-packages\fastapi\routing.py", line 226, in app raw_response = await run_endpoint_function( File "G:\ProgramData\Anaconda3\lib\site-packages\fastapi\routing.py", line 159, in run_endpoint_function return await dependant.call(**values) File "H:\gitlabproject\pyfreepro\ormtest.\sql_app\main.py", line 44, in getmax1 db_user = await crud.getmax1(db, id=payTradeId) File "H:\gitlabproject\pyfreepro\ormtest.\sql_app\crud.py", line 88, in getmax1 newresult = await db.execute(newstmt) File "G:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\ext\asyncio\session.py", line 211, in execute return await greenlet_spawn( File "G:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\util_concurrency_py3k.py", line 134, in greenlet_spawn result = context.throw(*sys.exc_info()) File "G:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\orm\session.py", line 1692, in execute result = conn._execute_20(statement, params or {}, execution_options) File "G:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1614, in _execute_20 return meth(self, args_10style, kwargs_10style, execution_options) File "G:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\sql\elements.py", line 325, in _execute_on_connection return connection._execute_clauseelement( File "G:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1481, in _execute_clauseelement ret = self._execute_context( File "G:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1845, in _execute_context self.handle_dbapi_exception( File "G:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 2030, in handle_dbapi_exception util.raise(exc_info[1], with_traceback=exc_info[2]) File "G:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\util\compat.py", line 207, in raise raise exception File "G:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1802, in execute_context self.dialect.do_execute( File "G:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\engine\default.py", line 732, in do_execute cursor.execute(statement, parameters) File "G:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\dialects\mysql\asyncmy.py", line 92, in execute return self.await(self._execute_async(operation, parameters)) File "G:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\util_concurrency_py3k.py", line 76, in await_only return current.driver.switch(awaitable) File "G:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\util_concurrency_py3k.py", line 129, in greenlet_spawn value = await result File "G:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\dialects\mysql\asyncmy.py", line 104, in _execute_async result = await self._cursor.execute(operation, parameters) File "asyncmy\cursors.pyx", line 178, in execute File "asyncmy\cursors.pyx", line 156, in asyncmy.cursors.Cursor.mogrify File "asyncmy\cursors.pyx", line 129, in genexpr File "asyncmy\cursors.pyx", line 129, in genexpr File "asyncmy\connection.pyx", line 426, in asyncmy.connection.Connection.literal File "asyncmy\connection.pyx", line 419, in asyncmy.connection.Connection.escape File "asyncmy\converters.pyx", line 10, in asyncmy.converters.escape_item File "asyncmy\converters.pyx", line 25, in asyncmy.converters.escape_item File "asyncmy\converters.pyx", line 48, in asyncmy.converters.escape_int OverflowError: Python int too large to convert to C long
As far as I can see, this is caused by the number which is bigger than long long's range in C, remove the long long in cpdef should work cause that tells cython to use python's PyLongObject instead of long long
By changing this function
cpdef escape_int(long long value, mapping=None):
return str(value)
to
cpdef escape_int(object value, mapping=None):
return str(value)
It might works, however, this may lead to a performance degradation due to the price that interact with PyObject.
I have the same problem.
It occurs on windows(64bit) when parsing bigint. It's ok in Linux
I have the same problem. It occurs on windows(64bit) when parsing bigint. It's ok in Linux
i use python 3.9 amd64
I have the same problem. Unfortunately, this puts an end to using asyncmy for discord bot.
Windows 10 64 bit Python 3.10 amd64
Here is the traceback/logs:
INFO sqlalchemy.engine.Engine BEGIN (implicit)
INFO sqlalchemy.engine.Engine INSERT INTO queue_data (message_id, task_id, `index`) VALUES (%s, %s, %s)
INFO sqlalchemy.engine.Engine (1099700317114863759, 7, 0)
result = await self._cursor.execute(operation, parameters)
OverflowError: Python int too large to convert to C unsigned long
Traceback (most recent call last):
File "venv\lib\site-packages\sqlalchemy\dialects\mysql\asyncmy.py", line 104, in _execute_async
INFO:sqlalchemy.engine.Engine:COMMIT
SQLAlchemy model:
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, BigInteger
Base = declarative_base()
class BaseModel(Base):
__abstract__ = True
class QueueData(BaseModel):
__tablename__ = "queue_data"
message_id = Column(BigInteger, nullable=False, primary_key=True, unique=True)
task_id = Column(Integer, nullable=False)
index = Column(Integer, nullable=False)
0.2.8rc1 also raises an exception.
With new SQLAlchemy 2.0.12 the error is the same.
Here is a simple code:
import asyncio
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker
from sqlalchemy.orm import declarative_base
from sqlalchemy import Column, Integer, BigInteger
Base = declarative_base()
class BaseModel(Base):
__abstract__ = True
class QueueData(BaseModel):
__tablename__ = "queue_data"
message_id = Column(BigInteger, nullable=False, primary_key=True, unique=True)
task_id = Column(Integer, nullable=False)
index = Column(Integer, nullable=False)
async def main():
engine = create_async_engine("mysql+asyncmy://user:pass@localhost:3307/bot", echo=True)
await engine.connect()
made_session = async_sessionmaker(engine)
async with engine.begin() as connection:
await connection.run_sync(lambda engine: Base.metadata.create_all(engine, checkfirst=True))
async with made_session() as session:
session.add(QueueData(message_id=int(5e+18), task_id=7, index=0))
await session.commit()
if __name__ == '__main__':
asyncio.run(main())
And here is its output:
2023-05-08 04:33:04,766 INFO sqlalchemy.engine.Engine SELECT DATABASE()
2023-05-08 04:33:04,766 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-05-08 04:33:04,768 INFO sqlalchemy.engine.Engine SELECT @@sql_mode
2023-05-08 04:33:04,768 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-05-08 04:33:04,769 INFO sqlalchemy.engine.Engine SELECT @@lower_case_table_names
2023-05-08 04:33:04,769 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-05-08 04:33:04,775 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-05-08 04:33:04,775 INFO sqlalchemy.engine.Engine DESCRIBE `bot`.`queue_data`
2023-05-08 04:33:04,775 INFO sqlalchemy.engine.Engine [raw sql] ()
await engine.connect()
OverflowError: Python int too large to convert to C unsigned long
Exception ignored in: 'asyncmy.protocol.MysqlPacket.read_uint64'
Traceback (most recent call last):
File "...\site-packages\sqlalchemy\dialects\mysql\asyncmy.py", line 107, in _execute_async
result = await self._cursor.execute(operation, parameters)
OverflowError: Python int too large to convert to C unsigned long
2023-05-08 04:33:04,777 INFO sqlalchemy.engine.Engine COMMIT
2023-05-08 04:33:04,780 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-05-08 04:33:04,781 INFO sqlalchemy.engine.Engine INSERT INTO queue_data (message_id, task_id, `index`) VALUES (%s, %s, %s)
2023-05-08 04:33:04,781 INFO sqlalchemy.engine.Engine [generated in 0.00017s] (5000000000000000000, 7, 0)
2023-05-08 04:33:04,783 INFO sqlalchemy.engine.Engine COMMIT
What's the result of python -c 'from asyncmy.version import __VERSION__;print(__VERSION__)'?
> python -c 'from asyncmy.version import __VERSION__;print(__VERSION__)'
0.2.8-rc1
I already said the version in the previous comment.
0.2.8rc1 also raises an exception. With new SQLAlchemy 2.0.12 the error is the same.
Yes, I just confirm it. But I really can't repeated it.
2023-05-08 20:29:47,580 INFO sqlalchemy.engine.Engine SELECT DATABASE()
2023-05-08 20:29:47,580 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-05-08 20:29:47,581 INFO sqlalchemy.engine.Engine SELECT @@sql_mode
2023-05-08 20:29:47,581 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-05-08 20:29:47,581 INFO sqlalchemy.engine.Engine SELECT @@lower_case_table_names
2023-05-08 20:29:47,581 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-05-08 20:29:47,584 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-05-08 20:29:47,585 INFO sqlalchemy.engine.Engine DESCRIBE `test`.`queue_data`
2023-05-08 20:29:47,585 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-05-08 20:29:47,586 INFO sqlalchemy.engine.Engine
CREATE TABLE queue_data (
message_id BIGINT NOT NULL AUTO_INCREMENT,
task_id INTEGER NOT NULL,
`index` INTEGER NOT NULL,
PRIMARY KEY (message_id),
UNIQUE (message_id)
)
2023-05-08 20:29:47,586 INFO sqlalchemy.engine.Engine [no key 0.00009s] ()
The garbage collector is trying to clean up non-checked-in connection <AdaptedConnection <asyncmy.connection.Connection object at 0x10f841d60>>, which will be dropped, as it cannot be safely 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.
/Users/long2ice/PycharmProjects/asyncmy/examples/main.py:24: SAWarning: The garbage collector is trying to clean up non-checked-in connection <AdaptedConnection <asyncmy.connection.Connection object at 0x10f841d60>>, which will be dropped, as it cannot be safely 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.
await engine.connect()
2023-05-08 20:29:47,618 INFO sqlalchemy.engine.Engine COMMIT
2023-05-08 20:29:47,621 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-05-08 20:29:47,622 INFO sqlalchemy.engine.Engine INSERT INTO queue_data (message_id, task_id, `index`) VALUES (%s, %s, %s)
2023-05-08 20:29:47,622 INFO sqlalchemy.engine.Engine [generated in 0.00016s] (5000000000000000000, 7, 0)
2023-05-08 20:29:47,623 INFO sqlalchemy.engine.Engine COMMIT
Hmm, maybe it depends on the version of mysql and how you run it?
I ran it in WSL2(Ubuntu 22.04) through the latest(8.0.33) docker image mysql.
Can you replicate this exception by running on Windows/Linux? Something just tells me that the problem lies in primitive data types. That's why we have an overflow error.
Hi @long2ice, I experience the same problem with unsigned big ints (values over 2^63 - 1)
MRE
import asyncio
import asyncmy
async def create_table(cursor):
query = """
CREATE TABLE IF NOT EXISTS foo (
bar BIGINT UNSIGNED PRIMARY KEY
)
"""
await cursor.execute(query)
async def insert_overflow(cursor):
query = """
INSERT INTO foo (bar) VALUES (%s)
"""
params = (2**64 - 1,)
await cursor.execute(query, params)
async def main():
conn = await asyncmy.connect(
host="db", port=3306, user="mysql1", password="mysql1", database="mysql1"
)
async with conn.cursor() as cursor:
await create_table(cursor)
await insert_overflow(cursor)
if __name__ == "__main__":
asyncio.run(main())
It yields the same error as in the initial post in this thread
File "asyncmy/cursors.pyx", line 177, in execute
File "asyncmy/cursors.pyx", line 155, in asyncmy.cursors.Cursor.mogrify
File "asyncmy/cursors.pyx", line 128, in genexpr
File "asyncmy/cursors.pyx", line 128, in genexpr
File "asyncmy/connection.pyx", line 421, in asyncmy.connection.Connection.literal
File "asyncmy/connection.pyx", line 414, in asyncmy.connection.Connection.escape
File "asyncmy/converters.pyx", line 11, in asyncmy.converters.escape_item
File "asyncmy/converters.pyx", line 26, in asyncmy.converters.escape_item
File "asyncmy/converters.pyx", line 49, in asyncmy.converters.escape_int
OverflowError: Python int too large to convert to C long
Solution
I think it can be solved in at least 2 ways
- Removing C typing in the
escape_intfunction
cpdef str escape_int(long long value, mapping: dict = None):
return str(value)
would become
cpdef str escape_int(value, mapping: dict = None):
return str(value)
This may lead to a performance degradation as mentioned by @synodriver.
- Introducing new C typed escape function for unsigned big ints
cpdef str escape_bigint_unsigned(unsigned long long value, mapping: dict = None):
return str(value)
which would be applied for the problematic values in escape_item
cpdef escape_item(val, str charset, mapping: dict = None):
if mapping is None:
mapping = encoders
encoder = mapping.get(type(val))
# Fallback to default when no encoder found
if not encoder:
try:
encoder = mapping[str]
except KeyError:
raise TypeError("no default type converter defined")
if encoder in (escape_dict, escape_sequence):
val = encoder(val, charset, mapping)
else:
val = encoder(val, mapping)
return val
would become
MAX_SIGNED_INT = 2**63 - 1
cpdef escape_item(val, str charset, mapping: dict = None):
if mapping is None:
mapping = encoders
type_ = type(val)
if type_ is int and val > MAX_SIGNED_INT:
encoder = escape_bigint_unsigned
else:
encoder = mapping.get(type_)
# Fallback to default when no encoder found
if not encoder:
try:
encoder = mapping[str]
except KeyError:
raise TypeError("no default type converter defined")
if encoder in (escape_dict, escape_sequence):
val = encoder(val, charset, mapping)
else:
val = encoder(val, mapping)
return val
Here the performance could be impacted by additional comparisons.
Evaluation
I ran your benchmarks against current asyncmy dev branch and both modifications with COUNT=100000 including new column for random integers up to 2^63 - 1.
| current | no typing | new escape | |
|---|---|---|---|
| insert | 13.98002 | 14.37959 | 13.95373 |
| select | 0.83498 | 0.86191 | 0.84803 |
| update | 13.13448 | 12.84223 | 12.93422 |
| delete | 13.28940 | 13.04953 | 12.59234 |
I don't think differences on this benchmark are significant.
Hope this can help
