-
-
Notifications
You must be signed in to change notification settings - Fork 4.6k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Arithmetic Overflow Error with MSSQL Backend due to Integer 'id' Field Limitation #8634
Comments
never got any report regarding MSSQL bugs to be honest. are you suggesting that we should adjust the SQLA data types in celery to make it work properly with all back end or just MSSQL only? |
It might make sense to have a MSSQL specific change, at least for now. I think the cause may be here:
Sequence is not supported in all dialects (for example, not MySQL)... and I suspect a type mismatch between the Sequence type and Integer type is causing the issue. For example, in the case of MSSQL, it appears to be using bigint for the Sequence. Would something like this work? import sqlalchemy as sa
@sa.event.listens_for(Task.__table__, "before_create")
def before_create(target, connection, **kwargs):
if connection.dialect.name == 'mssql':
target.columns['id'].type = sa.BigInteger()
## or, maybe it would be better to eliminate the sequence and only
## add it for non-mssql types. That way, it uses a standard int
## column for the PK which should be more than enough.
@sa.event.listens_for(Task.__table__, "before_create")
def before_create(target, connection, **kwargs):
if connection.dialect.name != 'mssql':
target.columns['id'].default = sa.Sequence('task_id_sequence') Btw, is there a preferred way to customize the backend schema? For example, several of the columns are binary data (result, args, kwargs, etc.), which makes them unsearchable directly. If I modify the result column type for example to JSON, e.g., from sqlalchemy import JSON, BigInteger
models.Task.__table__.c.result.type = JSON() That works, and has several advantages for me, because I can now query the result backend db directly. |
good shares. we can learn and incorporate some of the good suggestion you are sharing here in SQLA model code base |
This feature will be a real deal. Since I am now making binary to JSON conversion manually (similar to Django rest serializers), to see what tasks are in progress and to see data passed to them. UPD: I do it even uglier, currently, by pickle.loads(string) Having tasks args, kwargs, results in json will be a nice feature. |
@dxdc If I want to use the monkeypatch you suggested while waiting for the long-term fix, where does that code need to be added (in which module)? |
@twacn you add it in the same place just before you initialize celery, e.g. from celery.backends.database import models
from celery import Celery
from sqlalchemy import BigInteger
# add monkey patch
models.Task.__table__.c.id.type = BigInteger()
models.TaskSet.__table__.c.id.type = BigInteger()
# initialize celery
celery = Celery("tasks", broker=...) |
…d Limitation Fixes celery#8634
…d Limitation and format black Fixes celery#8634
Checklist
main
branch of Celery.contribution guide
on reporting bugs.
for similar or identical bug reports.
for existing proposed fixes.
to find out if the bug was already fixed in the main branch.
in this issue (If there are none, check this box anyway).
Mandatory Debugging Information
celery -A proj report
in the issue.(if you are not able to do this, then at least specify the Celery
version affected).
main
branch of Celery.pip freeze
in the issue.to reproduce this bug.
Optional Debugging Information
and/or implementation.
result backend.
broker and/or result backend.
ETA/Countdown & rate limits disabled.
and/or upgrading Celery and its dependencies.
Related Issues and Possible Duplicates
Related Issues
Possible Duplicates
Environment & Settings
Celery version:
celery report
Output:Steps to Reproduce
Required Dependencies
Python Packages
pip freeze
Output:Other Dependencies
N/A
Minimally Reproducible Test Case
Expected Behavior
Celery crashes when creating the schema for the results backend table when using sqlalchemy. The problem appears to be a mismatch in type between sa.Sequence and sa.Integer for the id column on Task and TaskSet.
You can see a mismatch between the
id
column type (int) and thetask_id_sequence
(bigint).This simple monkey patch solves the issue, and while this could be a bug in sqlalchemy, I suspect it could be an issue with the schema definition itself so I wanted to ask here first. I've linked (above) what appears to be a related bug in SQLalchemy that was recently fixed.
Actual Behavior
The text was updated successfully, but these errors were encountered: