[GitHub] [airflow] fungss commented on issue #17374: The new weberser does not seem to care about AIRFLOW__CORE__SQL_ALCHEMY_SCHEMA

[GitBox <gi...@apache.org>]

fungss commented on issue #17374:
URL: https://github.com/apache/airflow/issues/17374#issuecomment-1366134382

   The solution provided by @shicholas does not work if MSSQL is used. MSSQL does not have schema search path implemented. Please find below the returned error message for your reference.
   
   
   > \> airflow db reset
   /Users/ronaldfung/Projects/airflow/venv/lib/python3.9/site-packages/airflow/models/base.py:27 DeprecationWarning: The sql_alchemy_schema option in [core] has been moved to the sql_alchemy_schema option in [database] - the old setting has been used, but please update your config.
   DB: <my_mssql_connection_string>
   This will drop existing tables if they exist. Proceed? (y/n)y
   [2022-12-28 03:33:35,789] {db.py:1598} INFO - Dropping tables that exist
   [2022-12-28 03:33:38,615] {migration.py:204} INFO - Context impl MSSQLImpl.
   [2022-12-28 03:33:38,615] {migration.py:207} INFO - Will assume transactional DDL.
   [2022-12-28 03:33:38,649] {migration.py:204} INFO - Context impl MSSQLImpl.
   [2022-12-28 03:33:38,649] {migration.py:207} INFO - Will assume transactional DDL.
   Traceback (most recent call last):
     File "/Users/ronaldfung/Projects/airflow/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
       self.dialect.do_execute(
     File "/Users/ronaldfung/Projects/airflow/venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
       cursor.execute(statement, parameters)
   pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The specified schema name "dbo" either does not exist or you do not have permission to use it. (2760) (SQLExecDirectW)')
   The above exception was the direct cause of the following exception:
   Traceback (most recent call last):
     File "/Users/ronaldfung/Projects/airflow/venv/bin/airflow", line 8, in <module>
       sys.exit(main())
     File "/Users/ronaldfung/Projects/airflow/venv/lib/python3.9/site-packages/airflow/__main__.py", line 39, in main
       args.func(args)
     File "/Users/ronaldfung/Projects/airflow/venv/lib/python3.9/site-packages/airflow/cli/cli_parser.py", line 52, in command
       return func(*args, **kwargs)
     File "/Users/ronaldfung/Projects/airflow/venv/lib/python3.9/site-packages/airflow/cli/commands/db_command.py", line 46, in resetdb
       db.resetdb(skip_init=args.skip_init)
     File "/Users/ronaldfung/Projects/airflow/venv/lib/python3.9/site-packages/airflow/utils/session.py", line 75, in wrapper
       return func(*args, session=session, **kwargs)
     File "/Users/ronaldfung/Projects/airflow/venv/lib/python3.9/site-packages/airflow/utils/db.py", line 1611, in resetdb
       initdb(session=session)
     File "/Users/ronaldfung/Projects/airflow/venv/lib/python3.9/site-packages/airflow/utils/session.py", line 72, in wrapper
       return func(*args, **kwargs)
     File "/Users/ronaldfung/Projects/airflow/venv/lib/python3.9/site-packages/airflow/utils/db.py", line 697, in initdb
       _create_db_from_orm(session=session)
     File "/Users/ronaldfung/Projects/airflow/venv/lib/python3.9/site-packages/airflow/utils/db.py", line 682, in _create_db_from_orm
       _create_flask_session_tbl()
     File "/Users/ronaldfung/Projects/airflow/venv/lib/python3.9/site-packages/airflow/utils/db.py", line 677, in _create_flask_session_tbl
       db.create_all()
     File "/Users/ronaldfung/Projects/airflow/venv/lib/python3.9/site-packages/flask_sqlalchemy/__init__.py", line 1094, in create_all
       self._execute_for_all_tables(app, bind, 'create_all')
     File "/Users/ronaldfung/Projects/airflow/venv/lib/python3.9/site-packages/flask_sqlalchemy/__init__.py", line 1086, in _execute_for_all_tables
       op(bind=self.get_engine(app, bind), **extra)
     File "/Users/ronaldfung/Projects/airflow/venv/lib/python3.9/site-packages/sqlalchemy/sql/schema.py", line 4930, in create_all
       bind._run_ddl_visitor(
     File "/Users/ronaldfung/Projects/airflow/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3228, in _run_ddl_visitor
       conn._run_ddl_visitor(visitorcallable, element, **kwargs)
     File "/Users/ronaldfung/Projects/airflow/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2211, in _run_ddl_visitor
       visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
     File "/Users/ronaldfung/Projects/airflow/venv/lib/python3.9/site-packages/sqlalchemy/sql/visitors.py", line 524, in traverse_single
       return meth(obj, **kw)
     File "/Users/ronaldfung/Projects/airflow/venv/lib/python3.9/site-packages/sqlalchemy/sql/ddl.py", line 851, in visit_metadata
       self.traverse_single(
     File "/Users/ronaldfung/Projects/airflow/venv/lib/python3.9/site-packages/sqlalchemy/sql/visitors.py", line 524, in traverse_single
       return meth(obj, **kw)
     File "/Users/ronaldfung/Projects/airflow/venv/lib/python3.9/site-packages/sqlalchemy/sql/ddl.py", line 895, in visit_table
       self.connection.execute(
     File "/Users/ronaldfung/Projects/airflow/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1380, in execute
       return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
     File "/Users/ronaldfung/Projects/airflow/venv/lib/python3.9/site-packages/sqlalchemy/sql/ddl.py", line 80, in _execute_on_connection
       return connection._execute_ddl(
     File "/Users/ronaldfung/Projects/airflow/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1472, in _execute_ddl
       ret = self._execute_context(
     File "/Users/ronaldfung/Projects/airflow/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
       self._handle_dbapi_exception(
     File "/Users/ronaldfung/Projects/airflow/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
       util.raise_(
     File "/Users/ronaldfung/Projects/airflow/venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 210, in raise_
       raise exception
     File "/Users/ronaldfung/Projects/airflow/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
       self.dialect.do_execute(
     File "/Users/ronaldfung/Projects/airflow/venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
       cursor.execute(statement, parameters)
   sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The specified schema name "dbo" either does not exist or you do not have permission to use it. (2760) (SQLExecDirectW)')
   [SQL: 
   CREATE TABLE session (
           id INTEGER NOT NULL IDENTITY, 
           session_id VARCHAR(255) NULL, 
           data VARBINARY(max) NULL, 
           expiry DATETIME NULL, 
           PRIMARY KEY (id), 
           UNIQUE (session_id)
   )
   ]
   (Background on this error at: https://sqlalche.me/e/14/f405)


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org