disssid opened a new issue #9294: Superset with SQL Server as backend URL: https://github.com/apache/incubator-superset/issues/9294 Trying to get Superset working with SQL server (running in a docker container) as backend ### Expected results Superset UI ### Actual results 1. `docker exec -it superset superset-init` - Creating an admin account - Successful (Queried the table to see the user successfully inserted) - Database upgrade - Failed (Error: same as the one in below additional logs) 2. Tried manual upgrade using - `superset db upgrade` - Failed 3. When I tried to manually initialize using - `superset init` ``` superset@7d839629ab4a:~$ superset init Loaded your LOCAL configuration at [/etc/superset/superset_config.py] /usr/local/lib/python3.6/site-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use "pip install psycopg2-binary" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>. """) 2020-03-12 13:24:30,849:INFO:root:logging was configured successfully 2020-03-12 13:24:31,037:INFO:root:Configured event logger of type <class 'superset.utils.log.DBEventLogger'> 2020-03-12 13:24:31,403:DEBUG:asyncio:Using selector: EpollSelector Traceback (most recent call last): File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context cursor, statement, parameters, context File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute cursor.execute(statement, parameters) pyodbc.ProgrammingError: ('42S02', "[42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'dbs'. (208) (SQLExecDirectW)") The above exception was the direct cause of the following exception: Traceback (most recent call last): File "/usr/local/bin/superset", line 31, in <module> cli() File "/usr/local/lib/python3.6/site-packages/click/core.py", line 722, in __call__ return self.main(*args, **kwargs) File "/usr/local/lib/python3.6/site-packages/flask/cli.py", line 586, in main return super(FlaskGroup, self).main(*args, **kwargs) File "/usr/local/lib/python3.6/site-packages/click/core.py", line 697, in main rv = self.invoke(ctx) File "/usr/local/lib/python3.6/site-packages/click/core.py", line 1066, in invoke return _process_result(sub_ctx.command.invoke(sub_ctx)) File "/usr/local/lib/python3.6/site-packages/click/core.py", line 895, in invoke return ctx.invoke(self.callback, **ctx.params) File "/usr/local/lib/python3.6/site-packages/click/core.py", line 535, in invoke return callback(*args, **kwargs) File "/usr/local/lib/python3.6/site-packages/click/decorators.py", line 17, in new_func return f(get_current_context(), *args, **kwargs) File "/usr/local/lib/python3.6/site-packages/flask/cli.py", line 426, in decorator return __ctx.invoke(f, *args, **kwargs) File "/usr/local/lib/python3.6/site-packages/click/core.py", line 535, in invoke return callback(*args, **kwargs) File "/usr/local/lib/python3.6/site-packages/superset/cli.py", line 51, in init utils.get_example_database() File "/usr/local/lib/python3.6/site-packages/superset/utils/core.py", line 976, in get_example_database return get_or_create_db("examples", db_uri) File "/usr/local/lib/python3.6/site-packages/superset/utils/core.py", line 960, in get_or_create_db db.session.query(models.Database).filter_by(database_name=database_name).first() File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3228, in first ret = list(self[0:1]) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3018, in __getitem__ return list(res) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3330, in __iter__ return self._execute_and_instances(context) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3355, in _execute_and_instances result = conn.execute(querycontext.statement, self._params) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 988, in execute return meth(self, multiparams, params) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement distilled_params, File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context e, statement, parameters, cursor, context File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception util.raise_from_cause(sqlalchemy_exception, exc_info) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 152, in reraise raise value.with_traceback(tb) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context cursor, statement, parameters, context File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42S02', "[42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'dbs'. (208) (SQLExecDirectW)") [SQL: SELECT TOP 1 dbs.created_on AS dbs_created_on, dbs.changed_on AS dbs_changed_on, dbs.id AS dbs_id, dbs.verbose_name AS dbs_verbose_name, dbs.database_name AS dbs_database_name, dbs.sqlalchemy_uri AS dbs_sqlalchemy_uri, dbs.password AS dbs_password, dbs.cache_timeout AS dbs_cache_timeout, dbs.select_as_create_table_as AS dbs_select_as_create_table_as, dbs.expose_in_sqllab AS dbs_expose_in_sqllab, dbs.allow_run_async AS dbs_allow_run_async, dbs.allow_csv_upload AS dbs_allow_csv_upload, dbs.allow_ctas AS dbs_allow_ctas, dbs.allow_dml AS dbs_allow_dml, dbs.force_ctas_schema AS dbs_force_ctas_schema, dbs.allow_multi_schema_metadata_fetch AS dbs_allow_multi_schema_metadata_fetch, dbs.extra AS dbs_extra, dbs.perm AS dbs_perm, dbs.impersonate_user AS dbs_impersonate_user, dbs.created_by_fk AS dbs_created_by_fk, dbs.changed_by_fk AS dbs_changed_by_fk FROM dbs WHERE dbs.database_name = ?] [parameters: ('examples',)] (Background on this error at: http://sqlalche.me/e/f405) ``` Additional Logs (when trying to upgrade the database): ``` INFO [alembic.runtime.migration] Context impl MSSQLImpl. INFO [alembic.runtime.migration] Will assume transactional DDL. INFO [alembic.runtime.migration] Running upgrade -> 4e6a06bad7a8, Init INFO [alembic.runtime.migration] Running upgrade 4e6a06bad7a8 -> 5a7bad26f2a7, empty message INFO [alembic.runtime.migration] Running upgrade 5a7bad26f2a7 -> 1e2841a4128, empty message INFO [alembic.runtime.migration] Running upgrade 1e2841a4128 -> 2929af7925ed, TZ offsets in data sources INFO [alembic.runtime.migration] Running upgrade 2929af7925ed -> 289ce07647b, Add encrypted password field INFO [alembic.runtime.migration] Running upgrade 289ce07647b -> 1a48a5411020, adding slug to dash INFO [alembic.runtime.migration] Running upgrade 1a48a5411020 -> 315b3f4da9b0, adding log model INFO [alembic.runtime.migration] Running upgrade 315b3f4da9b0 -> 55179c7f25c7, sqla_descr INFO [alembic.runtime.migration] Running upgrade 55179c7f25c7 -> 12d55656cbca, is_featured INFO [alembic.runtime.migration] Running upgrade 12d55656cbca -> 2591d77e9831, user_id INFO [alembic.runtime.migration] Running upgrade 2591d77e9831 -> 8e80a26a31db, empty message INFO [alembic.runtime.migration] Running upgrade 8e80a26a31db -> 7dbf98566af7, empty message INFO [alembic.runtime.migration] Running upgrade 7dbf98566af7 -> 43df8de3a5f4, empty message INFO [alembic.runtime.migration] Running upgrade 43df8de3a5f4 -> d827694c7555, css templates INFO [alembic.runtime.migration] Running upgrade d827694c7555 -> 430039611635, log more INFO [alembic.runtime.migration] Running upgrade 430039611635 -> 18e88e1cc004, making audit nullable INFO [alembic.runtime.migration] Running upgrade 18e88e1cc004 -> 836c0bf75904, cache_timeouts INFO [alembic.runtime.migration] Running upgrade 18e88e1cc004 -> a2d606a761d9, adding favstar model INFO [alembic.runtime.migration] Running upgrade a2d606a761d9, 836c0bf75904 -> d2424a248d63, empty message INFO [alembic.runtime.migration] Running upgrade d2424a248d63 -> 763d4b211ec9, fixing audit fk INFO [alembic.runtime.migration] Running upgrade d2424a248d63 -> 1d2ddd543133, log dt INFO [alembic.runtime.migration] Running upgrade 1d2ddd543133, 763d4b211ec9 -> fee7b758c130, empty message INFO [alembic.runtime.migration] Running upgrade fee7b758c130 -> 867bf4f117f9, Adding extra field to Database model INFO [alembic.runtime.migration] Running upgrade 867bf4f117f9 -> bb51420eaf83, add schema to table model INFO [alembic.runtime.migration] Running upgrade bb51420eaf83 -> b4456560d4f3, change_table_unique_constraint Traceback (most recent call last): File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context cursor, statement, parameters, context File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute cursor.execute(statement, parameters) pyodbc.ProgrammingError: ('42S02', "[42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'alembic_version'. (208) (SQLExecDirectW)") The above exception was the direct cause of the following exception: Traceback (most recent call last): File "/usr/local/bin/superset", line 31, in <module> cli() File "/usr/local/lib/python3.6/site-packages/click/core.py", line 722, in __call__ return self.main(*args, **kwargs) File "/usr/local/lib/python3.6/site-packages/flask/cli.py", line 586, in main return super(FlaskGroup, self).main(*args, **kwargs) File "/usr/local/lib/python3.6/site-packages/click/core.py", line 697, in main rv = self.invoke(ctx) File "/usr/local/lib/python3.6/site-packages/click/core.py", line 1066, in invoke return _process_result(sub_ctx.command.invoke(sub_ctx)) File "/usr/local/lib/python3.6/site-packages/click/core.py", line 1066, in invoke return _process_result(sub_ctx.command.invoke(sub_ctx)) File "/usr/local/lib/python3.6/site-packages/click/core.py", line 895, in invoke return ctx.invoke(self.callback, **ctx.params) File "/usr/local/lib/python3.6/site-packages/click/core.py", line 535, in invoke return callback(*args, **kwargs) File "/usr/local/lib/python3.6/site-packages/click/decorators.py", line 17, in new_func return f(get_current_context(), *args, **kwargs) File "/usr/local/lib/python3.6/site-packages/flask/cli.py", line 426, in decorator return __ctx.invoke(f, *args, **kwargs) File "/usr/local/lib/python3.6/site-packages/click/core.py", line 535, in invoke return callback(*args, **kwargs) File "/usr/local/lib/python3.6/site-packages/flask_migrate/cli.py", line 134, in upgrade _upgrade(directory, revision, sql, tag, x_arg) File "/usr/local/lib/python3.6/site-packages/flask_migrate/__init__.py", line 95, in wrapped f(*args, **kwargs) File "/usr/local/lib/python3.6/site-packages/flask_migrate/__init__.py", line 280, in upgrade command.upgrade(config, revision, sql=sql, tag=tag) File "/usr/local/lib/python3.6/site-packages/alembic/command.py", line 276, in upgrade script.run_env() File "/usr/local/lib/python3.6/site-packages/alembic/script/base.py", line 475, in run_env util.load_python_file(self.dir, "env.py") File "/usr/local/lib/python3.6/site-packages/alembic/util/pyfiles.py", line 90, in load_python_file module = load_module_py(module_id, path) File "/usr/local/lib/python3.6/site-packages/alembic/util/compat.py", line 177, in load_module_py spec.loader.exec_module(module) File "<frozen importlib._bootstrap_external>", line 678, in exec_module File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed File "/usr/local/lib/python3.6/site-packages/superset/migrations/env.py", line 116, in <module> run_migrations_online() File "/usr/local/lib/python3.6/site-packages/superset/migrations/env.py", line 108, in run_migrations_online context.run_migrations() File "<string>", line 8, in run_migrations File "/usr/local/lib/python3.6/site-packages/alembic/runtime/environment.py", line 839, in run_migrations self.get_context().run_migrations(**kw) File "/usr/local/lib/python3.6/site-packages/alembic/runtime/migration.py", line 369, in run_migrations head_maintainer.update_to_step(step) File "/usr/local/lib/python3.6/site-packages/alembic/runtime/migration.py", line 595, in update_to_step self._update_version(from_, to_) File "/usr/local/lib/python3.6/site-packages/alembic/runtime/migration.py", line 541, in _update_version == literal_column("'%s'" % from_) File "/usr/local/lib/python3.6/site-packages/alembic/ddl/mssql.py", line 36, in _exec result = super(MSSQLImpl, self)._exec(construct, *args, **kw) File "/usr/local/lib/python3.6/site-packages/alembic/ddl/impl.py", line 134, in _exec return conn.execute(construct, *multiparams, **params) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 988, in execute return meth(self, multiparams, params) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement distilled_params, File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context e, statement, parameters, cursor, context File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception util.raise_from_cause(sqlalchemy_exception, exc_info) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 152, in reraise raise value.with_traceback(tb) File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context cursor, statement, parameters, context File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42S02', "[42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'alembic_version'. (208) (SQLExecDirectW)") [SQL: UPDATE alembic_version SET version_num='b4456560d4f3' WHERE alembic_version.version_num = 'bb51420eaf83'] (Background on this error at: http://sqlalche.me/e/f405) ``` ### Environment - superset version: `0.35.2` - python version: `3.6.10` - sqlalchemy version - `1.3.6` - Host OS - `Ubuntu 18.04.4 LTS` - SQL Server - docker image `mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04` ### Checklist Make sure these boxes are checked before submitting your issue - thank you! - [Y] I have checked the superset logs for python stacktraces and included it here as text if there are any. - [Y] I have reproduced the issue with at least the latest released version of superset. - [Y] I have checked the issue tracker for the same issue and I haven't found one similar. ### Additional context - Installed the necessary packages ``` sudo apt-get install unixodbc-dev pip install pyodbc ``` - Installed [SQL Server 2017 ODBC driver](https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15) - SQL Alchemy URI: `mssql+pyodbc://{username}:{password}@{hostname}/{dbname}?driver=ODBC Driver 17 for SQL Server`
---------------------------------------------------------------- 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. For queries about this service, please contact Infrastructure at: [email protected] With regards, Apache Git Services --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
