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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
