Migration 0004_auto_20200717_1043 fails on MS SQL Server

[edarchis]

Describe the bug / feature
When running on MS SQL Server, the migration 0004 that removes the autoincrement fails.
I know that SQL Server is not an officially supported backend but apart from this specific issue, it works quite well.
I would therefore appreciate suggestions on how to handle this issue.

To Reproduce
Run on MS SQL Server, I could provide detailed instructions if needed on how to do this.
manage.py migrate

Running migrations:
  Applying django_apscheduler.0001_initial... OK
  Applying django_apscheduler.0002_auto_20180412_0758... OK
  Applying django_apscheduler.0003_auto_20200716_1632... OK
  Applying django_apscheduler.0004_auto_20200717_1043...Traceback (most recent call last):
  File "./manage.py", line 15, in <module>
    execute_from_command_line(sys.argv)
  File ".../venv/lib/python3.8/site-packages/django/core/management/__init__.py", line 401, in execute_from_command_line
    utility.execute()
  File ".../openimis/venv/lib/python3.8/site-packages/django/core/management/__init__.py", line 395, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File ".../venv/lib/python3.8/site-packages/django/core/management/base.py", line 328, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/Users/darcher/Dev/openimis/venv/lib/python3.8/site-packages/django/core/management/base.py", line 369, in execute
    output = self.handle(*args, **options)
  File "/Users/darcher/Dev/openimis/venv/lib/python3.8/site-packages/django/core/management/base.py", line 83, in wrapped
    res = handle_func(*args, **kwargs)
  File "/Users/darcher/Dev/openimis/venv/lib/python3.8/site-packages/django/core/management/commands/migrate.py", line 231, in handle
    post_migrate_state = executor.migrate(
  File "/Users/darcher/Dev/openimis/venv/lib/python3.8/site-packages/django/db/migrations/executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File ".../venv/lib/python3.8/site-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File ".../venv/lib/python3.8/site-packages/django/db/migrations/executor.py", line 245, in apply_migration
    state = migration.apply(state, schema_editor)
  File ".../venv/lib/python3.8/site-packages/django/db/migrations/migration.py", line 124, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File ".../venv/lib/python3.8/site-packages/django/db/migrations/operations/fields.py", line 249, in database_forwards
    schema_editor.alter_field(from_model, from_field, to_field)
  File ".../venv/lib/python3.8/site-packages/django/db/backends/base/schema.py", line 564, in alter_field
    self._alter_field(model, old_field, new_field, old_type, new_type,
  File ".../venv/lib/python3.8/site-packages/sql_server/pyodbc/schema.py", line 247, in _alter_field
    raise NotImplementedError("the backend doesn't support altering from/to %s." % t.__name__)
NotImplementedError: the backend doesn't support altering from/to AutoField.

Expected behavior

One solution is to run ./manage.py squashmigrations django_apscheduler 0008. That produces a migration path to go straight to 0008 without doing every step. You also need to remove the python function from one of the migrations because it is updating existing data that is not present in that direct path. However, when running this with the package installed from pypi, it will write the squashed migration in .../venv/lib/python3.8/site-packages/django_apscheduler/migrations/0001_squashed_0008_remove_djangojobexecution_started.py which is a bit dirty and might not be writable in some cloud env for example. And I cannot ship it with my app.

Would be open to including this kind of squashed migration in the main package (it is in fact nice for all users and doesn't affect existing users) ? Or should we try to fork the module, adding that migration ?

I tried to include the squashed migration in my application folder but that didn't work.

Any other suggestions ?

Here is the exact squashed migration that I am using: https://gist.github.com/edarchis/b0cbe886265c55352fa34c3491032797

[jcass77]

This sounds similar to #93. I don't have any specific advice on how to get around the MSSQL incompatibilities.

The reality is that there are probably a handful of required features that the 3rd party MSSQL backend does not currently support, and issues related to this seem to crop up every now and again.

The best approach might be for somebody from the MSSQL community to create an MSSQL-compatible fork of this project that can be maintained in parallel, and hopefully merged back in one day when Django adds support for MSSQL or the MSSQL 3rd party package achieves feature parity with the official Django backends.

[edarchis]

I did not encounter the select for update issue. In this case, the backend would have worked fine had the jobexecution model not switched from AutoField to BigAutoField. The blame is Microsoft not supporting such a basic feature, the Django backend cannot do much about it.