Skip to content
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.

Sign up for GitHub

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

Jump to bottom

MSSQL as a Celery 5.4.0 broker fails with arithmetic overflow error #2222

Open
rborer opened this issue Jan 9, 2025 · 0 comments
Open

MSSQL as a Celery 5.4.0 broker fails with arithmetic overflow error #2222

rborer opened this issue Jan 9, 2025 · 0 comments

Comments

@rborer
Copy link

rborer commented Jan 9, 2025 •
edited
Loading

Hi everyone,

I am trying to rely on MS SQL server as a broker for my Celery setup (integrated within a Django application), and starting up the worker fails with the following SQL error:

sqlalchemy.exc.DataError: (pyodbc.DataError) ('22003', '[22003] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Arithmetic overflow error converting expression to data type int. (8115) (SQLExecDirectW)')
[SQL: INSERT INTO kombu_queue (id, name) OUTPUT inserted.id VALUES (NEXT VALUE FOR queue_id_sequence, ?)]
[parameters: ('celery',)]
Full Stacktrace 

 -------------- celery@8c295fb120de v5.4.0 (opalescent)
--- ***** -----
-- ******* ---- Linux-6.10.14-linuxkit-x86_64-with-glibc2.36 2025-01-09 11:45:42
- *** --- * ---
- ** ---------- [config]
- ** ---------- .> app:         quies:0x7f00559c5090
- ** ---------- .> transport:   sqla+mssql+pyodbc://SA:**@db_quies/quies
- ** ---------- .> results:
- *** --- * --- .> concurrency: 12 (prefork)
-- ******* ---- .> task events: ON
--- ***** -----
 -------------- [queues]
                .> celery           exchange=celery(direct) key=celery


[2025-01-09 11:45:42,248: WARNING/MainProcess] /opt/venv/lib/python3.11/site-packages/celery/worker/consumer/consumer.py:508: CPendingDeprecationWarning: The broker_connection_retry configuration setting will no longer determine
whether broker connection retries are made during startup in Celery 6.0 and above.
If you wish to retain the existing behavior for retrying connections on startup,
you should set broker_connection_retry_on_startup to True.
  warnings.warn(

[2025-01-09 11:45:42,274: WARNING/MainProcess] /opt/venv/lib/python3.11/site-packages/celery/worker/consumer/consumer.py:508: CPendingDeprecationWarning: The broker_connection_retry configuration setting will no longer determine
whether broker connection retries are made during startup in Celery 6.0 and above.
If you wish to retain the existing behavior for retrying connections on startup,
you should set broker_connection_retry_on_startup to True.
  warnings.warn(

[2025-01-09 11:45:42,344: CRITICAL/MainProcess] Unrecoverable error: DataError("(pyodbc.DataError) ('22003', '[22003] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Arithmetic overflow error converting expression to data type int. (8115) (SQLExecDirectW)')")
Traceback (most recent call last):
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 941, in do_execute
    cursor.execute(statement, parameters)
pyodbc.DataError: ('22003', '[22003] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Arithmetic overflow error converting expression to data type int. (8115) (SQLExecDirectW)')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/opt/venv/lib/python3.11/site-packages/celery/worker/worker.py", line 202, in start
    self.blueprint.start(self)
  File "/opt/venv/lib/python3.11/site-packages/celery/bootsteps.py", line 116, in start
    step.start(parent)
  File "/opt/venv/lib/python3.11/site-packages/celery/bootsteps.py", line 365, in start
    return self.obj.start()
           ^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/celery/worker/consumer/consumer.py", line 340, in start
    blueprint.start(self)
  File "/opt/venv/lib/python3.11/site-packages/celery/bootsteps.py", line 116, in start
    step.start(parent)
  File "/opt/venv/lib/python3.11/site-packages/celery/worker/consumer/tasks.py", line 38, in start
    c.task_consumer = c.app.amqp.TaskConsumer(
                      ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/celery/app/amqp.py", line 274, in TaskConsumer
    return self.Consumer(
           ^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/kombu/messaging.py", line 402, in __init__
    self.revive(self.channel)
  File "/opt/venv/lib/python3.11/site-packages/kombu/messaging.py", line 424, in revive
    self.declare()
  File "/opt/venv/lib/python3.11/site-packages/kombu/messaging.py", line 438, in declare
    queue.declare()
  File "/opt/venv/lib/python3.11/site-packages/kombu/entity.py", line 617, in declare
    self._create_queue(nowait=nowait, channel=channel)
  File "/opt/venv/lib/python3.11/site-packages/kombu/entity.py", line 626, in _create_queue
    self.queue_declare(nowait=nowait, passive=False, channel=channel)
  File "/opt/venv/lib/python3.11/site-packages/kombu/entity.py", line 655, in queue_declare
    ret = channel.queue_declare(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/kombu/transport/virtual/base.py", line 537, in queue_declare
    self._new_queue(queue, **kwargs)
  File "/opt/venv/lib/python3.11/site-packages/kombu/transport/sqlalchemy/__init__.py", line 159, in _new_queue
    self._get_or_create(queue)
  File "/opt/venv/lib/python3.11/site-packages/kombu/transport/sqlalchemy/__init__.py", line 152, in _get_or_create
    self.session.commit()
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2028, in commit
    trans.commit(_to_root=True)
  File "<string>", line 2, in commit
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
    ret_value = fn(self, *arg, **kw)
                ^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 1313, in commit
    self._prepare_impl()
  File "<string>", line 2, in _prepare_impl
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
    ret_value = fn(self, *arg, **kw)
                ^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 1288, in _prepare_impl
    self.session.flush()
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 4352, in flush
    self._flush(objects)
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 4487, in _flush
    with util.safe_reraise():
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 4448, in _flush
    flush_context.execute()
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/orm/unitofwork.py", line 466, in execute
    rec.execute(self)
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/orm/unitofwork.py", line 642, in execute
    util.preloaded.orm_persistence.save_obj(
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/orm/persistence.py", line 93, in save_obj
    _emit_insert_statements(
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/orm/persistence.py", line 1233, in _emit_insert_statements
    result = connection.execute(
             ^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
    return meth(
           ^^^^^
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1640, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2355, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/opt/venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 941, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DataError: (pyodbc.DataError) ('22003', '[22003] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Arithmetic overflow error converting expression to data type int. (8115) (SQLExecDirectW)')
[SQL: INSERT INTO kombu_queue (id, name) OUTPUT inserted.id VALUES (NEXT VALUE FOR queue_id_sequence, ?)]
[parameters: ('celery',)]
(Background on this error at: https://sqlalche.me/e/20/9h9h)

Environment & Settings

Output of celery report

software -> celery:5.4.0 (opalescent) kombu:5.4.2 py:3.11.2
            billiard:4.2.1 sqlalchemy:2.0.36
platform -> system:Linux arch:64bit
            kernel version:6.10.14-linuxkit imp:CPython
loader   -> celery.loaders.app.AppLoader
settings -> transport:sqla results:django-db

Relevant Django configuration

CELERY_RESULT_BACKEND = "django-db"
CELERY_BROKER_URL = "sqla+mssql+pyodbc://SA:***@db_host/database?driver=ODBC+Driver+18+for+SQL+Server&TrustServerCertificate=yes"

Related Issues and Possible Duplicates

Possibly related to:

My understanding & workaround

It looks like there is a type mismatch between the sequence output (bigint) and the id column type (int). I had a look at SQLAlchemy documentation for SQL server which states the following:

For integer primary key generation, SQL Server’s IDENTITY construct should generally be preferred vs. sequence.

Workaround

Based on the above, it is possible to discard the sequence usage and rely on the IDENTITY construct by using the following piece of code:

from celery import Celery
from kombu.transport.sqlalchemy import models

# Replace sequence with SQL Server’s IDENTITY construct
models.Queue.id.default = None
models.Message.id.default = None

app = Celery("XXX")

Let me know this issue report lack some information and I will happily add them.

Thanks in advance.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@rborer