Upgrade from preset/superset to apache/superset not working

[yogeshkk]

We have preset/superset working but as it is getting deprecated need to migrate to apache/superset. we are working on POC of upgrade. I have created a new superset and I am using a copy of the old database and I did run the migration from https://superset.apache.org/docs/installation/upgrading-superset still when we try to add a dashboard it gives an error

Expected results

We should able to create a new dashboard.

Actual results

}
Sorry, something went wrong
500 - Internal Server Error
Stacktrace
        Traceback (most recent call last):
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.NotNullViolation: null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (2021-03-24 10:01:36.019135, 2021-03-24 10:01:36.019155, null, [ untitled dashboard ], null, 54, 54, null, null, null, null, f, c6cf7e47-31d0-476c-95a3-49b9a3fd275f).

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

Traceback (most recent call last):
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 2447, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1952, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python3.7/site-packages/flask_cors/extension.py", line 165, in wrapped_function
    return cors_after_request(app.make_response(f(args, kwargs)))
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1821, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/local/lib/python3.7/site-packages/flask/_compat.py", line 39, in reraise
    raise value
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1950, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1936, in dispatch_request
    return self.view_functions[rule.endpoint](req.view_args)
  File "/usr/local/lib/python3.7/site-packages/flask_appbuilder/security/decorators.py", line 109, in wraps
    return f(self, args, *kwargs)
  File "/app/superset/views/dashboard/views.py", line 123, in new
    db.session.commit()
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/scoping.py", line 163, in do
    return getattr(self.registry(), name)(args, **kwargs)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1042, in commit
    self.transaction.commit()
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 504, in commit
    self.prepare_impl()
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 483, in prepare_impl
    self.session.flush()
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2536, in flush
    self.flush(objects)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2678, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit
    with_traceback=exc_tb,
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 182, in raise
    raise exception
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2638, in flush
    flush_context.execute()
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py", line 422, in execute
    rec.execute(self)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py", line 589, in execute
    uow,
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 245, in save_obj
    insert,
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 1136, in _emit_insert_statements
    statement, params
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1130, in _execute_clauseelement
    distilled_params,
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1317, in _execute_context
    e, statement, parameters, cursor, context
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1511, in _handle_dbapi_exception
    sqlalchemy_exception, with_traceback=exc_info[2], from=e
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (psycopg2.errors.NotNullViolation) null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (2021-03-24 10:01:36.019135, 2021-03-24 10:01:36.019155, null, [ untitled dashboard ], null, 54, 54, null, null, null, null, f, c6cf7e47-31d0-476c-95a3-49b9a3fd275f).

[SQL: INSERT INTO dashboards (uuid, created_on, changed_on, dashboard_title, position_json, description, css, json_metadata, slug, published, created_by_fk, changed_by_fk) VALUES (%(uuid)s, %(created_on)s, %(changed_on)s, %(dashboard_title)s, %(position_json)s, %(description)s, %(css)s, %(json_metadata)s, %(slug)s, %(published)s, %(created_by_fk)s, %(changed_by_fk)s) RETURNING dashboards.id]
[parameters: {'uuid': UUID('c6cf7e47-31d0-476c-95a3-49b9a3fd275f'), 'created_on': datetime.datetime(2021, 3, 24, 10, 1, 36, 19135), 'changed_on': datetime.datetime(2021, 3, 24, 10, 1, 36, 19155), 'dashboard_title': '[ untitled dashboard ]', 'position_json': None, 'description': None, 'css': None, 'json_metadata': None, 'slug': None, 'published': False, 'created_by_fk': 54, 'changed_by_fk': 54}]
(Background on this error at: http://sqlalche.me/e/13/gkpj)

Screenshots

If applicable, add screenshots to help explain your problem.

How to reproduce the bug

1. Install preset/superset and configure it.
2. Install apache/superset use the same DB
3. Run migration and DB upgrade.
4. try to create a dashboard.

Environment

(please complete the following information):
preset/superset

• superset version: Superset 0.999.0dev
• python version: Python 3.7.9

apache/superset

• superset version: Superset 0.999.0dev
• python version: Python 3.7.9

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.
• I have checked the issue tracker for the same issue and I haven't found one similar.

Additional context

Add any other context about the problem here.

[junlincc]

what do you mean by preset/superset? are you using the SaaS version. sorry I'm confused.

[yogeshkk]

Thanks for the reply @junlincc

We are using preset/superset docker image https://hub.docker.com/r/preset/superset/ now it is deprecated so I am trying with apache/superset image.

[slayerjain]

I'm also facing the same issue.

In my case, I migrated from the chart default bitnami postrgres 11 to Amazon RDS postgres 9.6 using pg_dump. This is what I get when adding a new dashboard:

Sorry, something went wrong
500 - Internal Server Error
Stacktrace
        Traceback (most recent call last):
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.NotNullViolation: null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (2021-03-26 09:09:22.983, 2021-03-26 09:09:22.983012, null, [ untitled dashboard ], null, 1, 1, null, null, null, null, f, ff805fbf-5ada-4044-abb8-59037c4ecaa6).


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

Traceback (most recent call last):
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 2447, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1952, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python3.7/site-packages/flask_cors/extension.py", line 165, in wrapped_function
    return cors_after_request(app.make_response(f(*args, **kwargs)))
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1821, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/local/lib/python3.7/site-packages/flask/_compat.py", line 39, in reraise
    raise value
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1950, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1936, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/usr/local/lib/python3.7/site-packages/flask_appbuilder/security/decorators.py", line 109, in wraps
    return f(self, *args, **kwargs)
  File "/app/superset/views/dashboard/views.py", line 123, in new
    db.session.commit()
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/scoping.py", line 163, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1042, in commit
    self.transaction.commit()
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 504, in commit
    self._prepare_impl()
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 483, in _prepare_impl
    self.session.flush()
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2536, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2678, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    with_traceback=exc_tb,
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2638, in _flush
    flush_context.execute()
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py", line 422, in execute
    rec.execute(self)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py", line 589, in execute
    uow,
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 245, in save_obj
    insert,
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 1136, in _emit_insert_statements
    statement, params
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1130, in _execute_clauseelement
    distilled_params,
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1317, in _execute_context
    e, statement, parameters, cursor, context
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1511, in _handle_dbapi_exception
    sqlalchemy_exception, with_traceback=exc_info[2], from_=e
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (psycopg2.errors.NotNullViolation) null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (2021-03-26 09:09:22.983, 2021-03-26 09:09:22.983012, null, [ untitled dashboard ], null, 1, 1, null, null, null, null, f, ff805fbf-5ada-4044-abb8-59037c4ecaa6).

[SQL: INSERT INTO dashboards (uuid, created_on, changed_on, dashboard_title, position_json, description, css, json_metadata, slug, published, created_by_fk, changed_by_fk) VALUES (%(uuid)s, %(created_on)s, %(changed_on)s, %(dashboard_title)s, %(position_json)s, %(description)s, %(css)s, %(json_metadata)s, %(slug)s, %(published)s, %(created_by_fk)s, %(changed_by_fk)s) RETURNING dashboards.id]
[parameters: {'uuid': UUID('ff805fbf-5ada-4044-abb8-59037c4ecaa6'), 'created_on': datetime.datetime(2021, 3, 26, 9, 9, 22, 983000), 'changed_on': datetime.datetime(2021, 3, 26, 9, 9, 22, 983012), 'dashboard_title': '[ untitled dashboard ]', 'position_json': None, 'description': None, 'css': None, 'json_metadata': None, 'slug': None, 'published': False, 'created_by_fk': 1, 'changed_by_fk': 1}]
(Background on this error at: http://sqlalche.me/e/13/gkpj)

[slayerjain]

@yogeshkk so I found why this was happening. the id column type is SERIAL and that created sequences. In a healthy superset installation those sequences were present eg:

ab_permission_id_seq
ab_view_menu_id_seq
ab_role_id_seq
ab_user_id_seq
ab_register_user_id_seq
ab_permission_view_id_seq
ab_user_role_id_seq
ab_permission_view_role_id_seq
tables_id_seq
clusters_id_seq
dashboards_id_seq
dbs_id_seq
datasources_id_seq
columns_id_seq
metrics_id_seq
slices_id_seq
sql_metrics_id_seq
table_columns_id_seq
dashboard_slices_id_seq
logs_id_seq
url_id_seq
css_templates_id_seq
favstar_id_seq
dashboard_user_id_seq
slice_user_id_seq
query_id_seq
access_request_id_seq
keyvalue_id_seq
saved_query_id_seq
annotation_layer_id_seq
annotation_id_seq
user_attribute_id_seq
dashboard_email_schedules_id_seq
slice_email_schedules_id_seq
sqlatable_user_id_seq
druiddatasource_user_id_seq
tag_id_seq
tagged_object_id_seq
tab_state_id_seq
table_schema_id_seq
row_level_security_filters_id_seq
rls_filter_roles_id_seq
rls_filter_tables_id_seq
alerts_id_seq
alert_logs_id_seq
alert_owner_id_seq
sql_observations_id_seq
cache_keys_id_seq
report_schedule_id_seq
report_execution_log_id_seq
report_recipient_id_seq
report_schedule_user_id_seq
dynamic_plugin_id_seq

But after pg_dump, the sequences were missing along with the link (column_default) in respective tables. So it looks like this in my problematic setup:

ab_permission_id_seq
ab_permission_view_id_seq
ab_permission_view_role_id_seq
ab_register_user_id_seq
ab_role_id_seq
ab_user_id_seq
ab_user_role_id_seq
ab_view_menu_id_seq

[yogeshkk]

@slayerjain Awasom. I will have a look for the missing seq. I did run migration thought it will bring DB up to the state if missing something.