MSSQL Server - clean_duplicate_history command error

[LukeWebbJacobs]

Describe the bug
Using https://github.com/microsoft/mssql-django
Using database hosted in Azure.
Clean duplicate management command fails mid way through running.

Environment (please complete the following information):

• Windows 10
• Azure Managed SQL

Additional context

Removed 0 historical records for <class 'Apps.points.models.action_notes.action_notes.ActionNote'>
Removed 0 historical records for <class 'Apps.points.models.action_notes.action_notes.ActionNote'>
Removed 0 historical records for <class 'Apps.points.models.action_notes.action_notes.ActionNote'>
Traceback (most recent call last):
  File "F:\Python\Python39\lib\site-packages\django\db\utils.py", line 97, in inner
    return func(*args, **kwargs)
  File "F:\Python\Python39\lib\site-packages\mssql\base.py", line 612, in fetchmany
    return self.format_rows(self.cursor.fetchmany(chunk))
pyodbc.Error: ('HY010', '[HY010] [Microsoft][ODBC Driver Manager] Function sequence error (0) (SQLFetch)')

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

Traceback (most recent call last):
  File "C:\Program Files\JetBrains\PyCharm 2021.2.2\plugins\python\helpers\pycharm\django_manage.py", line 52, in <module>
    run_command()
  File "C:\Program Files\JetBrains\PyCharm 2021.2.2\plugins\python\helpers\pycharm\django_manage.py", line 46, in run_command
    run_module(manage_file, None, '__main__', True)
  File "F:\Python\Python39\lib\runpy.py", line 210, in run_module
    return _run_module_code(code, init_globals, run_name, mod_spec)
  File "F:\Python\Python39\lib\runpy.py", line 97, in _run_module_code
    _run_code(code, mod_globals, init_globals,
  File "F:\Python\Python39\lib\runpy.py", line 87, in _run_code
    exec(code, run_globals)
  File "F:\Website/django_v2\manage.py", line 22, in <module>
    main()
  File "F:\Website/django_v2\manage.py", line 18, in main
    execute_from_command_line(sys.argv)
  File "F:\Python\Python39\lib\site-packages\django\core\management\__init__.py", line 419, in execute_from_command_line
Removed 0 historical records for <class 'Apps.points.models.action_notes.action_notes.ActionNote'>
    utility.execute()
  File "F:\Python\Python39\lib\site-packages\django\core\management\__init__.py", line 413, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "F:\Python\Python39\lib\site-packages\django\core\management\base.py", line 354, in run_from_argv
    self.execute(*args, **cmd_options)
  File "F:\Python\Python39\lib\site-packages\django\core\management\base.py", line 398, in execute
    output = self.handle(*args, **options)
  File "F:\Python\Python39\lib\site-packages\simple_history\management\commands\clean_duplicate_history.py", line 57, in handle
    self._process(to_process, date_back=options["minutes"], dry_run=options["dry"])
  File "F:\Python\Python39\lib\site-packages\simple_history\management\commands\clean_duplicate_history.py", line 84, in _process
    for o in model_query.iterator():
  File "F:\Python\Python39\lib\site-packages\django\db\models\query.py", line 353, in _iterator
    yield from self._iterable_class(self, chunked_fetch=use_chunked_fetch, chunk_size=chunk_size)
  File "F:\Python\Python39\lib\site-packages\django\db\models\query.py", line 68, in __iter__
    for row in compiler.results_iter(results):
  File "F:\Python\Python39\lib\site-packages\django\db\models\sql\compiler.py", line 1118, in apply_converters
    for row in map(list, rows):
  File "F:\Python\Python39\lib\site-packages\mssql\compiler.py", line 141, in _cursor_iter
    for rows in iter((lambda: cursor.fetchmany(itersize)), sentinel):
  File "F:\Python\Python39\lib\site-packages\mssql\compiler.py", line 141, in <lambda>
    for rows in iter((lambda: cursor.fetchmany(itersize)), sentinel):
  File "F:\Python\Python39\lib\site-packages\django\db\utils.py", line 97, in inner
    return func(*args, **kwargs)
  File "F:\Python\Python39\lib\site-packages\django\db\utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "F:\Python\Python39\lib\site-packages\django\db\utils.py", line 97, in inner
    return func(*args, **kwargs)
  File "F:\Python\Python39\lib\site-packages\mssql\base.py", line 612, in fetchmany
    return self.format_rows(self.cursor.fetchmany(chunk))
django.db.utils.Error: ('HY010', '[HY010] [Microsoft][ODBC Driver Manager] Function sequence error (0) (SQLFetch)')

Process finished with exit code 1

Or run from cmd:

F:\Website\django_v2>F:\Python\Python39\python.exe manage.py clean_duplicate_history --auto
Traceback (most recent call last):
  File "F:\Python\Python39\lib\site-packages\mssql\base.py", line 488, in _set_autocommit
    self.connection.autocommit = autocommit
pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]New transaction is not allowed because there are other threads running in the session. (3988) (SQLSetConnectAttr)')

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

Traceback (most recent call last):
  File "F:\Website\django_v2\manage.py", line 22, in <module>
    main()
  File "F:\Website\django_v2\manage.py", line 18, in main
    execute_from_command_line(sys.argv)
  File "F:\Python\Python39\lib\site-packages\django\core\management\__init__.py", line 419, in execute_from_command_line
    utility.execute()
  File "F:\Python\Python39\lib\site-packages\django\core\management\__init__.py", line 413, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "F:\Python\Python39\lib\site-packages\django\core\management\base.py", line 354, in run_from_argv
    self.execute(*args, **cmd_options)
  File "F:\Python\Python39\lib\site-packages\django\core\management\base.py", line 398, in execute
    output = self.handle(*args, **options)
  File "F:\Python\Python39\lib\site-packages\simple_history\management\commands\clean_duplicate_history.py", line 57, in handle
    self._process(to_process, date_back=options["minutes"], dry_run=options["dry"])
  File "F:\Python\Python39\lib\site-packages\simple_history\management\commands\clean_duplicate_history.py", line 85, in _process
    self._process_instance(o, model, stop_date=stop_date, dry_run=dry_run)
  File "F:\Python\Python39\lib\site-packages\simple_history\management\commands\clean_duplicate_history.py", line 97, in _process_instance
    with transaction.atomic():
  File "F:\Python\Python39\lib\site-packages\django\db\transaction.py", line 207, in __enter__
    connection.set_autocommit(False, force_begin_transaction_with_broken_autocommit=True)
  File "F:\Python\Python39\lib\site-packages\django\db\backends\base\base.py", line 415, in set_autocommit
    self._set_autocommit(autocommit)
  File "F:\Python\Python39\lib\site-packages\mssql\base.py", line 488, in _set_autocommit
    self.connection.autocommit = autocommit
  File "F:\Python\Python39\lib\site-packages\django\db\utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "F:\Python\Python39\lib\site-packages\mssql\base.py", line 488, in _set_autocommit
    self.connection.autocommit = autocommit
django.db.utils.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]New transaction is not allowed because there are other threads running in the session. (3988) (SQLSetConnectAttr)')

[jwaschkau]

Changing https://github.com/jazzband/django-simple-history/blob/55850c23de03d0a066465e0b5103a4f91aaebacd/simple_history/management/commands/clean_duplicate_history.py#L96-L97

to

      for o in model_query:
          self._process_instance(o, model, stop_date=stop_date, dry_run=dry_run)

apperently fixes the problem.

What is the difference between iterating with .iterator() and without?

[jwaschkau]

I created a POC to bypass the problems if the mssql engine is used.

I think we should also check if a fix in the [mssql-django](the https://github.com/microsoft/mssql-django) library is possible.

Any suggestions how to proceed forward?

[paltman]

the iterator() creates a server-side named cursor (at least on postgres). Not sure if that is the problem here, but i have periodical exceptions with this same section of code because I'm using pgBouncer and there are issues when doing doing transactions inside the loop of an iterator() because of the service side named cursor when using pgBouncer.

Get exceptions like:

psycopg.errors.InvalidCursorName: cursor "_django_curs_140173139422080_sync_1" does not exist