Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Database error when upgrading from 1.4.1 to master #19100

Closed
2 of 3 tasks
maudrid opened this issue Mar 10, 2022 · 5 comments · Fixed by #19543
Closed
2 of 3 tasks

Database error when upgrading from 1.4.1 to master #19100

maudrid opened this issue Mar 10, 2022 · 5 comments · Fixed by #19543
Labels
#bug Bug report v1.5

Comments

@maudrid
Copy link

maudrid commented Mar 10, 2022

I though to report this so that it could be fixed in the next release.
When I upgrade an existing 1.4.1 instance that uses postgreSQL for metadata storage, I get an error and cannot use previously existing dashboards. (sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column dbs.allow_file_upload does not exist)
The same error appears in the supserset init stage, so I will include the log below/

How to reproduce the bug

  1. in my docker-compose file I change from image: apache/superset:1.4.1 to image: apache/superset:master
    Currently this is the DIGEST:sha256:23094797144b33a717c8d1d950825116bed97c1c4d6342d708b359279a31d83c
  2. docker-compose down -> docker-compose up -d
  3. docker exec superset-app superset fab create-admin --username user --firstname user --lastname '' --email user@user.user --password pasword
  4. docker exec superset-app superset db upgrade
  5. docker exec superset-app superset init

Expected results

Superset works as normal

Actual results

SQL error from the DB and superset dashboards do not work

Logs

docker exec superset-app superset fab create-admin --username user --firstname user --lastname '' --email user@user.user --password pasword
 logging was configured successfully
 2022-03-10 09:55:37,802:INFO:superset.utils.logging_configurator:logging was configured successfully
 2022-03-10 09:55:37,819:INFO:root:Configured event logger of type <class 'superset.utils.log.DBEventLogger'>
 ---==== Start loading custom configuration ====---
 Loaded your security configuration at [/app/security.py]
 Loaded your LOCAL configuration at [/app/superset_config.py]
 Recognized OpenID Authentication.
 Error! User already exists user
 /usr/local/lib/python3.8/site-packages/flask_caching/__init__.py:201: UserWarning: Flask-Caching: CACHE_TYPE is set to null, caching is effectively disabled.
   warnings.warn(

docker exec superset-app superset db upgrade
 logging was configured successfully
 2022-03-10 09:55:46,495:INFO:superset.utils.logging_configurator:logging was configured successfully
 2022-03-10 09:55:46,507:INFO:root:Configured event logger of type <class 'superset.utils.log.DBEventLogger'>
 /usr/local/lib/python3.8/site-packages/flask_caching/__init__.py:201: UserWarning: Flask-Caching: CACHE_TYPE is set to null, caching is effectively disabled.
   warnings.warn(
 INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
 INFO  [alembic.runtime.migration] Will assume transactional DDL.
 INFO  [alembic.runtime.migration] Running upgrade aea15018d53b -> abe27eaf93db, add_extra_config_column_to_alerts
 INFO  [alembic.runtime.migration] Running upgrade abe27eaf93db -> 3ba29ecbaac5, Change datatype of type in BaseColumn
 INFO  [alembic.runtime.migration] Running upgrade 3ba29ecbaac5 -> fe23025b9441, rename_big_viz_total_form_data_fields
 INFO  [alembic.runtime.migration] Running upgrade fe23025b9441 -> 31bb738bd1d2, move_pivot_table_v2_legacy_order_by_to_timeseries_limit_metric
 INFO  [alembic.runtime.migration] Running upgrade 31bb738bd1d2 -> bb38f40aa3ff, Add force_screenshot to alerts/reports
 INFO  [alembic.runtime.migration] Running upgrade bb38f40aa3ff -> c53bae8f08dd, add_saved_query_foreign_key_to_tab_state
 Revision ID: c53bae8f08dd
 Revises: bb38f40aa3ff
 Create Date: 2021-12-15 15:05:21.845777
 INFO  [alembic.runtime.migration] Running upgrade c53bae8f08dd -> 5fd49410a97a, Add columns for external management
 INFO  [alembic.runtime.migration] Running upgrade 5fd49410a97a -> 5afbb1a5849b, add_embedded_dahshoard_table
 INFO  [alembic.runtime.migration] Running upgrade 5afbb1a5849b -> b8d3a24d9131, New dataset models
 INFO  [alembic.runtime.migration] Running upgrade b8d3a24d9131 -> b5a422d8e252, fix query and saved_query null schema
 INFO  [alembic.runtime.migration] Running upgrade b5a422d8e252 -> ab9a9d86e695, deprecate time_range_endpoints
 INFO  [alembic.runtime.migration] Running upgrade ab9a9d86e695 -> 7293b0ca7944, change_adhoc_filter_b_from_none_to_empty_array
 ---==== Start loading custom configuration ====---
 Loaded your security configuration at [/app/security.py]
 Loaded your LOCAL configuration at [/app/superset_config.py]

docker exec superset-app superset init
 logging was configured successfully
 2022-03-10 09:55:56,873:INFO:superset.utils.logging_configurator:logging was configured successfully
 2022-03-10 09:55:56,884:INFO:root:Configured event logger of type <class 'superset.utils.log.DBEventLogger'>
 /usr/local/lib/python3.8/site-packages/flask_caching/__init__.py:201: UserWarning: Flask-Caching: CACHE_TYPE is set to null, caching is effectively disabled.
   warnings.warn(
 Syncing role definition
 2022-03-10 09:56:02,586:INFO:superset.security.manager:Syncing role definition
 Syncing Admin perms
 2022-03-10 09:56:02,610:INFO:superset.security.manager:Syncing Admin perms
 Syncing Alpha perms
 2022-03-10 09:56:02,852:INFO:superset.security.manager:Syncing Alpha perms
 Syncing Gamma perms
 2022-03-10 09:56:03,073:INFO:superset.security.manager:Syncing Gamma perms
 Syncing granter perms
 2022-03-10 09:56:03,324:INFO:superset.security.manager:Syncing granter perms
 Syncing sql_lab perms
 2022-03-10 09:56:03,564:INFO:superset.security.manager:Syncing sql_lab perms
 Fetching a set of all perms to lookup which ones are missing
 2022-03-10 09:56:03,761:INFO:superset.security.manager:Fetching a set of all perms to lookup which ones are missing
 Creating missing datasource permissions.
 2022-03-10 09:56:03,954:INFO:superset.security.manager:Creating missing datasource permissions.
 ---==== Start loading custom configuration ====---
 Loaded your security configuration at [/app/security.py]
 Loaded your LOCAL configuration at [/app/superset_config.py]
 Traceback (most recent call last):
   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
     self.dialect.do_execute(
   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
     cursor.execute(statement, parameters)
 psycopg2.errors.UndefinedColumn: column dbs.allow_file_upload does not exist
 LINE 1: ...thod, dbs.allow_run_async AS dbs_allow_run_async, dbs.allow_...
                                                              ^
 
 
 The above exception was the direct cause of the following exception:
 
 Traceback (most recent call last):
   File "/usr/local/bin/superset", line 33, in <module>
     sys.exit(load_entry_point('apache-superset', 'console_scripts', 'superset')())
   File "/usr/local/lib/python3.8/site-packages/click/core.py", line 829, in __call__
     return self.main(*args, **kwargs)
   File "/usr/local/lib/python3.8/site-packages/flask/cli.py", line 586, in main
     return super(FlaskGroup, self).main(*args, **kwargs)
   File "/usr/local/lib/python3.8/site-packages/click/core.py", line 782, in main
     rv = self.invoke(ctx)
   File "/usr/local/lib/python3.8/site-packages/click/core.py", line 1259, in invoke
     return _process_result(sub_ctx.command.invoke(sub_ctx))
   File "/usr/local/lib/python3.8/site-packages/click/core.py", line 1066, in invoke
     return ctx.invoke(self.callback, **ctx.params)
   File "/usr/local/lib/python3.8/site-packages/click/core.py", line 610, in invoke
     return callback(*args, **kwargs)
   File "/usr/local/lib/python3.8/site-packages/click/decorators.py", line 21, in new_func
     return f(get_current_context(), *args, **kwargs)
   File "/usr/local/lib/python3.8/site-packages/flask/cli.py", line 426, in decorator
     return __ctx.invoke(f, *args, **kwargs)
   File "/usr/local/lib/python3.8/site-packages/click/core.py", line 610, in invoke
     return callback(*args, **kwargs)
   File "/usr/local/lib/python3.8/site-packages/click/decorators.py", line 21, in new_func
     return f(get_current_context(), *args, **kwargs)
   File "/usr/local/lib/python3.8/site-packages/flask/cli.py", line 426, in decorator
     return __ctx.invoke(f, *args, **kwargs)
   File "/usr/local/lib/python3.8/site-packages/click/core.py", line 610, in invoke
     return callback(*args, **kwargs)
   File "/app/superset/cli/main.py", line 61, in init
     security_manager.sync_role_definitions()
   File "/app/superset/security/manager.py", line 736, in sync_role_definitions
     self.create_missing_perms()
   File "/app/superset/security/manager.py", line 678, in create_missing_perms
     merge_pv("datasource_access", datasource.get_perm())
   File "/app/superset/connectors/sqla/models.py", line 633, in get_perm
     return f"[{self.database}].[{self.table_name}](id:{self.id})"
   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py", line 294, in __get__
     return self.impl.get(instance_state(instance), dict_)
   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py", line 730, in get
     value = self.callable_(state, passive)
   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/strategies.py", line 759, in _load_for_state
     return self._emit_lazyload(
   File "<string>", line 1, in <lambda>
   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/strategies.py", line 847, in _emit_lazyload
     q(session)
   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/ext/baked.py", line 615, in _load_on_pk_identity
     result = list(bq.for_session(self.session).params(**params))
   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/ext/baked.py", line 444, in __iter__
     return q._execute_and_instances(context)
   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3560, in _execute_and_instances
     result = conn.execute(querycontext.statement, self._params)
   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
     return meth(self, multiparams, params)
   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
     return connection._execute_clauseelement(self, multiparams, params)
   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement
     ret = self._execute_context(
   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
     self._handle_dbapi_exception(
   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
     util.raise_(
   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
     raise exception
   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
     self.dialect.do_execute(
   File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
     cursor.execute(statement, parameters)
 sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column dbs.allow_file_upload does not exist
 LINE 1: ...thod, dbs.allow_run_async AS dbs_allow_run_async, dbs.allow_...
                                                              ^
 
 [SQL: SELECT dbs.uuid AS dbs_uuid, 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.configuration_method AS dbs_configuration_method, dbs.allow_run_async AS dbs_allow_run_async, dbs.allow_file_upload AS dbs_allow_file_upload, dbs.allow_ctas AS dbs_allow_ctas, dbs.allow_cvas AS dbs_allow_cvas, 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.encrypted_extra AS dbs_encrypted_extra, dbs.impersonate_user AS dbs_impersonate_user, dbs.server_cert AS dbs_server_cert, dbs.is_managed_externally AS dbs_is_managed_externally, dbs.external_url AS dbs_external_url, dbs.created_by_fk AS dbs_created_by_fk, dbs.changed_by_fk AS dbs_changed_by_fk 
 FROM dbs 
 WHERE dbs.id = %(param_1)s]
 [parameters: {'param_1': 1}]
 (Background on this error at: http://sqlalche.me/e/13/f405)

Environment

  • browser type and version: Any
  • superset version: master
  • python version: using the superset docker image
  • node.js version: using the superset docker image
  • any feature flags active:
    FEATURE_FLAGS = {
    'ENABLE_TEMPLATE_PROCESSING': True,
    'DASHBOARD_RBAC': True,
    'DASHBOARD_CROSS_FILTERS': True,
    'VERSIONED_EXPORT': True,
    'DASHBOARD_NATIVE_FILTERS': True,
    'ROW_LEVEL_SECURITY': True,
    }

Checklist

Make sure to follow these steps before submitting your issue - thank you!

  • I have checked the superset logs for python stacktraces and included it here as text if there are any.
  • I have reproduced the issue with at least the latest released version of superset.
    No this is in master branch. The released version works fine.
  • I have checked the issue tracker for the same issue and I haven't found one similar.
@maudrid maudrid added the #bug Bug report label Mar 10, 2022
@EBoisseauSierra
Copy link
Contributor

EBoisseauSierra commented Mar 26, 2022

I can replicate on c7f9060a2 (and still on 5ae7e5499 — to where master currently points towards), with bare metal install, and without any feature flag activated.

Previously:

$ superset db upgrade
[…]
INFO  [alembic.runtime.migration] Running upgrade 6766938c6065 -> 58df9d617f14, add_on_saved_query_delete_tab_state_null_constraint"

Seems linked to #16756, but actual migration that hasn't worked properly is b92d69a6643c — from #17275.

FWIW, I'm using PostgreSQL as back-end db though.

@vdobes
Copy link

vdobes commented Mar 31, 2022

I got the same problem.
Trying to update my 1.4.1 version to the latest master, and I got the same error.
I'm using docker version with PostgreSQL DB.

My Log is similar:

([SQL: SELECT count(*) AS count_1 ,
FROM (SELECT dbs.changed_on AS dbs_changed_on, dbs.id AS dbs_id, dbs.database_name AS dbs_database_name, dbs.sqlalchemy_uri AS dbs_sqlalchemy_uri, dbs.password AS dbs_password, dbs.expose_in_sqllab AS dbs_expose_in_sqllab, dbs.allow_run_async AS dbs_allow_run_async, dbs.allow_file_upload AS dbs_allow_file_upload, dbs.allow_ctas AS dbs_allow_ctas, dbs.allow_cvas AS dbs_allow_cvas, 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.created_by_fk AS dbs_created_by_fk, created_by.id AS created_by_id, created_by.first_name AS created_by_first_name, created_by.last_name AS created_by_last_name ,
FROM dbs LEFT OUTER JOIN ab_user AS created_by ON dbs.created_by_fk = created_by.id) AS anon_1],
(Background on this error at: http://sqlalche.me/e/13/f405),
Traceback (most recent call last):,
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context,
    self.dialect.do_execute(,
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute,
    cursor.execute(statement, parameters),
psycopg2.errors.UndefinedColumn: column dbs.allow_file_upload does not exist,
LINE 2: ...llab, dbs.allow_run_async AS dbs_allow_run_async, dbs.allow_...,
                                                             ^,
The above exception was the direct cause of the following exception:,

Traceback (most recent call last):,
  File "/usr/local/lib/python3.8/site-packages/flask_appbuilder/api/__init__.py", line 85, in wraps,
    return f(self, *args, **kwargs),
  File "/usr/local/lib/python3.8/site-packages/flask_appbuilder/api/__init__.py", line 155, in wraps,
    return f(self, *args, **kwargs),
  File "/usr/local/lib/python3.8/site-packages/flask_appbuilder/api/__init__.py", line 1619, in get_list,
    return self.get_list_headless(**kwargs),
  File "/app/superset/utils/log.py", line 245, in wrapper,
    value = f(*args, **kwargs),
  File "/app/superset/views/base_api.py", line 419, in get_list_headless,
    duration, response = time_function(super().get_list_headless, **kwargs),
  File "/app/superset/utils/core.py", line 1452, in time_function,
    response = func(*args, **kwargs),
  File "/usr/local/lib/python3.8/site-packages/flask_appbuilder/api/__init__.py", line 1512, in get_list_headless,
    count, lst = self.datamodel.query(,
  File "/usr/local/lib/python3.8/site-packages/flask_appbuilder/models/sqla/interface.py", line 461, in query,
    count = self.query_count(query, filters, select_columns),
  File "/usr/local/lib/python3.8/site-packages/flask_appbuilder/models/sqla/interface.py", line 382, in query_count,
    return self._apply_inner_all(,
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3803, in count,
    return self.from_self(col).scalar(),
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3523, in scalar,
    ret = self.one(),
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3490, in one,
    ret = self.one_or_none(),
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3459, in one_or_none,
    ret = list(self),
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3535, in __iter__,
    return self._execute_and_instances(context),
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3560, in _execute_and_instances,
    result = conn.execute(querycontext.statement, self._params),
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1011, in execute,
    return meth(self, multiparams, params),
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection,
    return connection._execute_clauseelement(self, multiparams, params),
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement,
    ret = self._execute_context(,
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context,
    self._handle_dbapi_exception(,
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception,
    util.raise_(,
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_,
    raise exception,
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context,
    self.dialect.do_execute(,
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute,
    cursor.execute(statement, parameters),
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column dbs.allow_file_upload does not exist,
LINE 2: ...llab, dbs.allow_run_async AS dbs_allow_run_async, dbs.allow_...,)

@mxalis
Copy link

mxalis commented Apr 5, 2022

Had the same issue when updating from 1.4.2 (deployed on Kubernetes using the Helm chart) to latest master. PostgreSQL as the database.

@villebro villebro added the v1.5 label Apr 6, 2022
@villebro
Copy link
Member

villebro commented Apr 6, 2022

Ok, I've found the issue. It appears migrations b92d69a6643c and 0ca9e5f1dacd have been skipped in the 1.4 release, causing the upgrade from 1.4 to master or 1.5 to fail.
See the different down revisions of f9847149153d in 1.4 and master:

revision = "f9847149153d"
down_revision = "32646df09c64"

revision = "f9847149153d"
down_revision = "0ca9e5f1dacd"

What we need to do is move b92d69a6643c and 0ca9e5f1dacd in front of abe27eaf93db on master and 1.5, after which this should work. I'll get to work on this now.

@villebro
Copy link
Member

villebro commented Apr 6, 2022

Fix PR: #19543

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
#bug Bug report v1.5
Projects
None yet
Development

Successfully merging a pull request may close this issue.

5 participants