Airflow initdb fails on MSSQL

[aneesh-joseph]

Apache Airflow version: 1.10.10

Kubernetes version (if you are using kubernetes) (use kubectl version):

Environment:

• Cloud provider or hardware configuration:
• OS (e.g. from /etc/os-release):
• Kernel (e.g. uname -a):
• Install tools:
• Others:

What happened:

If we configure airflow to use an MSSQL metadatabase and try to execute airflow initdb
it fails with the below error .

INFO  [alembic.runtime.migration] Running upgrade 6e96a59344a4 -> 74effc47d867, change datetime to datetime2(6) on MSSQL tables
INFO  [alembic.runtime.migration] Running upgrade 939bb1e647c8 -> 004c1210f153, increase queue name size limit
INFO  [alembic.runtime.migration] Running upgrade c8ffec048a3b -> a56c9515abdc, Remove dag_stat table
INFO  [alembic.runtime.migration] Running upgrade a56c9515abdc, 004c1210f153, 74effc47d867, b3b105409875 -> 08364691d074, Merge the four heads back together
INFO  [alembic.runtime.migration] Running upgrade 08364691d074 -> fe461863935f, increase_length_for_connection_password
INFO  [alembic.runtime.migration] Running upgrade fe461863935f -> 7939bcff74ba, Add DagTags table
INFO  [alembic.runtime.migration] Running upgrade 7939bcff74ba -> a4c2fd67d16b, add pool_slots field to task_instance
INFO  [alembic.runtime.migration] Running upgrade a4c2fd67d16b -> 852ae6c715af, Add RenderedTaskInstanceFields table
Traceback (most recent call last):
  File "/home/airflow/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
    cursor, statement, parameters, context
  File "/home/airflow/.local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 588, in do_execute
    cursor.execute(statement, parameters)
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]'JSON_VALID' is not a recognized built-in function name. (195) (SQLExecDirectW)")

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

Traceback (most recent call last):
  File "/home/airflow/.local/bin/airflow", line 37, in <module>
    args.func(args)
  File "/home/airflow/.local/lib/python3.7/site-packages/airflow/bin/cli.py", line 1150, in resetdb
    db.resetdb(settings.RBAC)
  File "/home/airflow/.local/lib/python3.7/site-packages/airflow/utils/db.py", line 420, in resetdb
    initdb(rbac)
  File "/home/airflow/.local/lib/python3.7/site-packages/airflow/utils/db.py", line 323, in initdb
    upgradedb()
  File "/home/airflow/.local/lib/python3.7/site-packages/airflow/utils/db.py", line 386, in upgradedb
    command.upgrade(config, 'heads')
  File "/home/airflow/.local/lib/python3.7/site-packages/alembic/command.py", line 298, in upgrade
    script.run_env()
  File "/home/airflow/.local/lib/python3.7/site-packages/alembic/script/base.py", line 489, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/home/airflow/.local/lib/python3.7/site-packages/alembic/util/pyfiles.py", line 98, in load_python_file
    module = load_module_py(module_id, path)
  File "/home/airflow/.local/lib/python3.7/site-packages/alembic/util/compat.py", line 184, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 728, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "/home/airflow/.local/lib/python3.7/site-packages/airflow/migrations/env.py", line 96, in <module>
    run_migrations_online()
  File "/home/airflow/.local/lib/python3.7/site-packages/airflow/migrations/env.py", line 90, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/home/airflow/.local/lib/python3.7/site-packages/alembic/runtime/environment.py", line 846, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/home/airflow/.local/lib/python3.7/site-packages/alembic/runtime/migration.py", line 520, in run_migrations
    step.migration_fn(**kw)
  File "/home/airflow/.local/lib/python3.7/site-packages/airflow/migrations/versions/852ae6c715af_add_rendered_task_instance_fields_table.py", line 48, in upgrade
    conn.execute("SELECT JSON_VALID(1)").fetchone()
  File "/home/airflow/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 976, in execute
    return self._execute_text(object_, multiparams, params)
  File "/home/airflow/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1151, in _execute_text
    parameters,
  File "/home/airflow/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1288, in _execute_context
    e, statement, parameters, cursor, context
  File "/home/airflow/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1482, in _handle_dbapi_exception
    sqlalchemy_exception, with_traceback=exc_info[2], from_=e
  File "/home/airflow/.local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
    raise exception
  File "/home/airflow/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
    cursor, statement, parameters, context
  File "/home/airflow/.local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 588, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]'JSON_VALID' is not a recognized built-in function name. (195) (SQLExecDirectW)")
[SQL: SELECT JSON_VALID(1)]
(Background on this error at: http://sqlalche.me/e/f405)

I know that MSSQL is not officially recommended, but would like to get this sorted, will try to raise a PR for the same.

What you expected to happen:

Initdb works successfully with an MSSSQL DB

How to reproduce it:
Refer the details above

Anything else we need to know:

[mik-laj]

You don't have to create an issue if you want to submit PR. You only need PR if you want to report a bug or request a new feature, but you don't want to report PR or you want to inform other people about your issue.

[kaxil]

Fixed in #8385 , Will be available for 1.10.11