Scheduler gets stuck if running a trigger is interrupted by an SQL Server shutdown

[hbpe-aarhus]

Looks like some maintenance was performed on the SQL Server that I used for testing. Obviously it's expected for the trigger to fail, but it looks as if the Scheduler got stuck as no more triggers were run after the interruption.

Cloning into 'C:\Users\SvcRpaMBA001\Desktop\Scheduler_Repos\1b54a4da-149a-4932-bb70-f3d403309ab1'...
remote: Azure Repos
remote: Found 87 objects to send. (14 ms)
Unpacking objects: 100% (87/87), 24.31 KiB | 96.00 KiB/s, done.
Collecting uv
  Using cached uv-0.5.2-py3-none-win_amd64.whl.metadata (11 kB)
Using cached uv-0.5.2-py3-none-win_amd64.whl (15.3 MB)
Installing collected packages: uv
Successfully installed uv-0.5.2
Exception in Tkinter callback
Traceback (most recent call last):
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\engine\base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\engine\default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]SHUTDOWN er i gang. (6005) (SQLExecDirectW)')

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

Traceback (most recent call last):
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\OpenOrchestrator\database\db_util.py", line 576, in get_next_single_trigger
    return session.scalar(query)
           ^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\orm\session.py", line 2399, in scalar
    return self._execute_internal(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\orm\session.py", line 2236, in _execute_internal
    result: Result[Any] = compile_state_cls.orm_execute_statement(
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\orm\context.py", line 293, in orm_execute_statement
    result = conn.execute(
             ^^^^^^^^^^^^^
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\engine\base.py", line 1418, in execute
    return meth(
           ^^^^^
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\sql\elements.py", line 515, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\engine\base.py", line 1640, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\engine\base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\engine\base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\engine\base.py", line 2353, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\engine\base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\engine\default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]SHUTDOWN er i gang. (6005) (SQLExecDirectW)')
[SQL: SELECT TOP 1 [Single_Triggers].id, [Triggers].id AS id_1, [Triggers].trigger_name, [Triggers].process_name, [Triggers].last_run, [Triggers].process_path, [Triggers].process_args, [Triggers].process_status, [Triggers].is_git_repo, [Triggers].is_blocking, [Triggers].type, [Single_Triggers].next_run
FROM [Triggers] JOIN [Single_Triggers] ON [Triggers].id = [Single_Triggers].id
WHERE [Triggers].process_status = ? AND [Single_Triggers].next_run <= ? ORDER BY [Single_Triggers].next_run]
[parameters: ('IDLE', datetime.datetime(2024, 11, 19, 20, 4, 28, 233015))]
(Background on this error at: https://sqlalche.me/e/20/f405)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\engine\base.py", line 1127, in _rollback_impl
    self.engine.dialect.do_rollback(self.connection)
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 3117, in do_rollback
    super().do_rollback(dbapi_connection)
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\engine\default.py", line 698, in do_rollback
    dbapi_connection.rollback()
pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]SHUTDOWN er i gang. (6005) (SQLEndTran)')

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

Traceback (most recent call last):
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\tkinter\__init__.py", line 1967, in __call__
    return self.func(*args)
           ^^^^^^^^^^^^^^^^
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\tkinter\__init__.py", line 861, in callit
    func(*args)
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\OpenOrchestrator\scheduler\run_tab.py", line 117, in loop
    check_triggers(app)
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\OpenOrchestrator\scheduler\run_tab.py", line 172, in check_triggers
    job = runner.poll_triggers(app)
          ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\OpenOrchestrator\scheduler\runner.py", line 36, in poll_triggers
    next_single_trigger = db_util.get_next_single_trigger()
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
or\database\db_util.py", line 568, in get_next_single_trigger
    with _get_session() as session:
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\orm\session.py", line 1796, in __exit__
    self.close()
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\orm\session.py", line 2507, in close
    self._close_impl(invalidate=False)
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\orm\session.py", line 2576, in _close_impl
    transaction.close(invalidate)
  File "<string>", line 2, in close
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\orm\state_changes.py", line 139, in _go
    ret_value = fn(self, *arg, **kw)
                ^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\orm\session.py", line 1408, in close
    transaction.close()
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\engine\base.py", line 2586, in close
    self._do_close()
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\engine\base.py", line 2724, in _do_close
    self._close_impl()
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\engine\base.py", line 2710, in _close_impl
    self._connection_rollback_impl()
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\engine\base.py", line 2702, in _connection_rollback_impl
    self.connection._rollback_impl()
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\engine\base.py", line 1129, in _rollback_impl
    self._handle_dbapi_exception(e, None, None, None, None)
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\engine\base.py", line 2353, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\engine\base.py", line 1127, in _rollback_impl
    self.engine.dialect.do_rollback(self.connection)
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 3117, in do_rollback
    super().do_rollback(dbapi_connection)
  File "C:\Users\SvcRpaMBA001\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\engine\default.py", line 698, in do_rollback
    dbapi_connection.rollback()
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]SHUTDOWN er i gang. (6005) (SQLEndTran)')
(Background on this error at: https://sqlalche.me/e/20/f405)

[ghbm-itk]

I also have this problem. It should be fixed in the coming update :)

[jduust]

@ghbm-itk Will the fix also retry connection every 5 minutes or so in case of a sql breakdown?

[ghbm-itk]

The fix should say "Oh I couldn't connect to the database, carry on" and then wait the normal 6 seconds before trying again

[ghbm-itk]

Although it might not fix a potential issues where the database shuts down while a robot is running. I don't think I have experienced that yet.