MSSQL issue after updating from 0.3 to 0.4

[vodnokoki]

After upgrading to version 0.4, there is an issue with following trace:

  File "C:\Python37\lib\site-packages\apscheduler\schedulers\background.py", line 33, in start
    BaseScheduler.start(self, *args, **kwargs)
  File "C:\Python37\lib\site-packages\apscheduler\schedulers\base.py", line 162, in start
    self._real_add_job(job, jobstore_alias, replace_existing)
  File "C:\Python37\lib\site-packages\apscheduler\schedulers\base.py", line 870, in _real_add_job
    store.update_job(job)
  File "C:\Python37\lib\site-packages\django_apscheduler\jobstores.py", line 211, in update_job
    id=job.id
  File "C:\Python37\lib\site-packages\django\db\models\query.py", line 402, in get
    num = len(clone)
  File "C:\Python37\lib\site-packages\django\db\models\query.py", line 256, in __len__
    self._fetch_all()
  File "C:\Python37\lib\site-packages\django\db\models\query.py", line 1242, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "C:\Python37\lib\site-packages\django\db\models\query.py", line 55, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "C:\Python37\lib\site-packages\django\db\models\sql\compiler.py", line 1087, in execute_sql
    sql, params = self.as_sql()
  File "C:\Python37\lib\site-packages\sql_server\pyodbc\compiler.py", line 263, in as_sql
    raise NotSupportedError('FOR UPDATE OF is not supported on this database backend.')
django.db.utils.NotSupportedError: FOR UPDATE OF is not supported on this database backend.

I am using:

• django-mssql-backend==2.8.1 to access SQL server database.
• Django==2.2.7

[jcass77]

Looks like mssql does not support select_for_update.

Not sure how to solve this: the APScheduler events do not arrive in sequence and it is not unusual to receive a "submission" event after an "execution" event for the same job. Without the lock there would be a proliferation of DjangoJobExecutions that still show as awaiting execution, even though they have long since completed (this was the case in versions < 0.4.0).

[jcass77]

...if it is just the of=("self",) parameter that is causing the issue then we might have a shot at resolving since we don't have that many related objects that will be covered by the lock.

If you can test the above and submit a PR that would be great - don't have mssql installed locally unfortunately.

[vodnokoki]

also noticed, while migrations, i have this error:

• NotImplementedError: the backend doesn't support altering from/to AutoField.
  Here is limitation from django-mssql-backend:
• https://github.com/ESSolutions/django-mssql-backend/blob/master/README.rst#limitations

While creating db schema in the moment of migration 0.3 to 0.4, failed with removing name attribute from django_apscheduler_djangojob table.

Also noticed that django_apscheduler_djangojob.Id in sql server is INT type and it is Autoincrement, and it can not save unique text as it is specified in model.

[jcass77]

Sorry I wasn't aware of those limitations when I made the changes.

This probably is not very helpful, but it does not look like MSSQL is officially supported by Django.

Perhaps a fork that can be supported by the MSSQL community would be appropriate?

[vodnokoki]

You mean fork from 0.3.0 version?

[jcass77]

I think v0.3.1 was the last version on which MSSQL sort of worked.

If you did not experience any major issues on v0.3.1 it would be easiest to just keep using that version. If you have time / capacity / appetite you could also create a fork of v0.4.0 and try to retro-fit it for MSSQL yourself.

Going forward, this project will probably only focus on supporting the DB backends that are also officially supported by Django.

[vodnokoki]

Thanks @jcass77 , i will fork v0.3.1

[7ooL]

I have same issue.

Updated Debian 9 to 10 for python 3.6 to 3.7.3 support.
have apscheduler (3.6.3) and django (3.1) using 10.3.23-MariaDB-0+deb10u1 Debian 10

everything worked well before the Debian upgrade.
Error notifying listener Traceback (most recent call last): File "/home/ha/.local/lib/python3.7/site-packages/apscheduler/schedulers/base.py", line 831, in _dispatch_event cb(event) File "/home/ha/.local/lib/python3.7/site-packages/django_apscheduler/jobstores.py", line 125, in handle_error_event exception=exception, File "/home/ha/.local/lib/python3.7/site-packages/django_apscheduler/models.py", line 158, in atomic_update_or_create ).get(job_id=job_id, run_time=run_time) File "/home/ha/.local/lib/python3.7/site-packages/django/db/models/query.py", line 425, in get num = len(clone) File "/home/ha/.local/lib/python3.7/site-packages/django/db/models/query.py", line 269, in __len__ self._fetch_all() File "/home/ha/.local/lib/python3.7/site-packages/django/db/models/query.py", line 1303, in _fetch_all self._result_cache = list(self._iterable_class(self)) File "/home/ha/.local/lib/python3.7/site-packages/django/db/models/query.py", line 53, in __iter__ results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size) File "/home/ha/.local/lib/python3.7/site-packages/django/db/models/sql/compiler.py", line 1141, in execute_sql sql, params = self.as_sql() File "/home/ha/.local/lib/python3.7/site-packages/django/db/models/sql/compiler.py", line 560, in as_sql raise NotSupportedError('FOR UPDATE OF is not supported on this database backend.') django.db.utils.NotSupportedError: FOR UPDATE OF is not supported on this database backend.

Exception in thread APScheduler: Traceback (most recent call last): File "/usr/lib/python3.7/threading.py", line 917, in _bootstrap_inner self.run() File "/usr/lib/python3.7/threading.py", line 865, in run self._target(*self._args, **self._kwargs) File "/home/ha/.local/lib/python3.7/site-packages/apscheduler/schedulers/blocking.py", line 30, in _main_loop wait_seconds = self._process_jobs() File "/home/ha/.local/lib/python3.7/site-packages/apscheduler/schedulers/base.py", line 995, in _process_jobs jobstore.update_job(job) File "/home/ha/.local/lib/python3.7/site-packages/django_apscheduler/jobstores.py", line 211, in update_job id=job.id File "/home/ha/.local/lib/python3.7/site-packages/django/db/models/query.py", line 425, in get num = len(clone) File "/home/ha/.local/lib/python3.7/site-packages/django/db/models/query.py", line 269, in __len__ self._fetch_all() File "/home/ha/.local/lib/python3.7/site-packages/django/db/models/query.py", line 1303, in _fetch_all self._result_cache = list(self._iterable_class(self)) File "/home/ha/.local/lib/python3.7/site-packages/django/db/models/query.py", line 53, in __iter__ results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size) File "/home/ha/.local/lib/python3.7/site-packages/django/db/models/sql/compiler.py", line 1141, in execute_sql sql, params = self.as_sql() File "/home/ha/.local/lib/python3.7/site-packages/django/db/models/sql/compiler.py", line 560, in as_sql raise NotSupportedError('FOR UPDATE OF is not supported on this database backend.') django.db.utils.NotSupportedError: FOR UPDATE OF is not supported on this database backend.