"superset db migrate" fails in init-db job

tapanhalani commented 2 years ago

A clear and concise description of what the bug is.

How to reproduce the bug

(1.) Successfully installed superset on a managed kubernetes cluster using superset helm chart (v0.7.0) (https://github.com/apache/superset/blob/superset-helm-chart-0.7.0/helm/superset/values.yaml), with docker image version set to apache/superset:1.5.0 Database used is postgresql 14.4

This setup has been working fine for 3 months now.

(2.) Today, while upgrading the image to 2.0.0 tag, the init-db job is failing with the following logs:

Defaulting to user installation because normal site-packages is not writeable
Requirement already satisfied: psycopg2-binary==2.9.1 in /usr/local/lib/python3.8/site-packages (2.9.1)
Requirement already satisfied: redis==3.5.3 in /usr/local/lib/python3.8/site-packages (3.5.3)
WARNING: You are using pip version 21.2.4; however, version 22.2.2 is available.
You should consider upgrading via the '/usr/local/bin/python -m pip install --upgrade pip' command.
Upgrading DB schema...
logging was configured successfully
2022-08-10 11:37:28,958:INFO:superset.utils.logging_configurator:logging was configured successfully
2022-08-10 11:37:28,965:INFO:root:Configured event logger of type <class 'superset.utils.log.DBEventLogger'>
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
Loaded your LOCAL configuration at [/app/pythonpath/superset_config.py]
Initializing roles...
logging was configured successfully
2022-08-10 11:37:36,904:INFO:superset.utils.logging_configurator:logging was configured successfully
2022-08-10 11:37:36,912:INFO:root:Configured event logger of type <class 'superset.utils.log.DBEventLogger'>
2022-08-10 11:37:40,432:ERROR:flask_appbuilder.security.sqla.manager:Add View Menu Error: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "ab_view_menu_pkey"
DETAIL:  Key (id)=(109) already exists.

[SQL: INSERT INTO ab_view_menu (id, name) VALUES (nextval('ab_view_menu_id_seq'), %(name)s) RETURNING ab_view_menu.id]
[parameters: {'name': 'AdvancedDataType'}]
(Background on this error at: http://sqlalche.me/e/13/gkpj)
2022-08-10 11:37:40,506:ERROR:flask_appbuilder.security.sqla.manager:Add View Menu Error: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "ab_view_menu_pkey"
DETAIL:  Key (id)=(110) already exists.

[SQL: INSERT INTO ab_view_menu (id, name) VALUES (nextval('ab_view_menu_id_seq'), %(name)s) RETURNING ab_view_menu.id]
[parameters: {'name': 'AdvancedDataType'}]
(Background on this error at: http://sqlalche.me/e/13/gkpj)
2022-08-10 11:37:40,553:ERROR:flask_appbuilder.security.sqla.manager:Creation of Permission View Error: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "ab_view_menu_pkey"
DETAIL:  Key (id)=(111) already exists.

[SQL: INSERT INTO ab_view_menu (id, name) VALUES (nextval('ab_view_menu_id_seq'), %(name)s) RETURNING ab_view_menu.id]
[parameters: {'name': 'AdvancedDataType'}]
(Background on this error at: http://sqlalche.me/e/13/gkpj)
2022-08-10 11:37:41,671:ERROR:flask_appbuilder.security.sqla.manager:Creation of Permission View Error: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "ab_permission_view_pkey"
DETAIL:  Key (id)=(253) already exists.

[SQL: INSERT INTO ab_permission_view (id, permission_id, view_menu_id) VALUES (nextval('ab_permission_view_id_seq'), %(permission_id)s, %(view_menu_id)s) RETURNING ab_permission_view.id]
[parameters: {'permission_id': 1, 'view_menu_id': 102}]
(Background on this error at: http://sqlalche.me/e/13/gkpj)
Syncing role definition
2022-08-10 11:37:47,781:INFO:superset.security.manager:Syncing role definition
Syncing Admin perms
2022-08-10 11:37:47,921:INFO:superset.security.manager:Syncing Admin perms
Syncing Alpha perms
2022-08-10 11:37:49,060:INFO:superset.security.manager:Syncing Alpha perms
Syncing Gamma perms
2022-08-10 11:37:51,019:INFO:superset.security.manager:Syncing Gamma perms
Loaded your LOCAL configuration at [/app/pythonpath/superset_config.py]
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1256, in _execute_context
    self.dialect.do_executemany(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 912, in do_executemany
    cursor.executemany(statement, parameters)
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "ab_permission_view_role_pkey"
DETAIL:  Key (id)=(639) already exists.

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 1128, in __call__
    return self.main(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/flask/cli.py", line 601, in main
    return super().main(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/click/core.py", line 1053, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python3.8/site-packages/click/core.py", line 1659, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/local/lib/python3.8/site-packages/click/core.py", line 1395, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python3.8/site-packages/click/core.py", line 754, in invoke
    return __callback(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/click/decorators.py", line 26, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/flask/cli.py", line 445, in decorator
    return __ctx.invoke(f, *args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/click/core.py", line 754, in invoke
    return __callback(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/click/decorators.py", line 26, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/flask/cli.py", line 445, in decorator
    return __ctx.invoke(f, *args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/click/core.py", line 754, in invoke
    return __callback(*args, **kwargs)
  File "/app/superset/cli/main.py", line 62, in init
    security_manager.sync_role_definitions()
  File "/app/superset/security/manager.py", line 719, in sync_role_definitions
    self.set_role("Gamma", self._is_gamma_pvm)
  File "/app/superset/security/manager.py", line 814, in set_role
    self.get_session.merge(role)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/scoping.py", line 163, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2160, in merge
    self._autoflush()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1633, in _autoflush
    util.raise_(e, with_traceback=sys.exc_info()[2])
  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/orm/session.py", line 1622, in _autoflush
    self.flush()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2540, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2682, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.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/orm/session.py", line 2642, in _flush
    flush_context.execute()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 422, in execute
    rec.execute(self)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 540, in execute
    self.dependency_processor.process_saves(uow, states)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/dependency.py", line 1176, in process_saves
    self._run_crud(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/dependency.py", line 1239, in _run_crud
    connection.execute(statement, secondary_insert)
  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 1256, in _execute_context
    self.dialect.do_executemany(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 912, in do_executemany
    cursor.executemany(statement, parameters)
sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
(psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "ab_permission_view_role_pkey"
DETAIL:  Key (id)=(639) already exists.

[SQL: INSERT INTO ab_permission_view_role (id, permission_view_id, role_id) VALUES (nextval('ab_permission_view_role_id_seq'), %(permission_view_id)s, %(role_id)s)]
[parameters: ({'permission_view_id': 192, 'role_id': 4}, {'permission_view_id': 194, 'role_id': 4}, {'permission_view_id': 195, 'role_id': 4}, {'permission_view_id': 198, 'role_id': 4}, {'permission_view_id': 206, 'role_id': 4}, {'permission_view_id': 207, 'role_id': 4}, {'permission_view_id': 209, 'role_id': 4}, {'permission_view_id': 210, 'role_id': 4}, {'permission_view_id': 211, 'role_id': 4}, {'permission_view_id': 213, 'role_id': 4})]
(Background on this error at: http://sqlalche.me/e/13/gkpj)

Environment

(please complete the following information):

Docker image version: 2.0.0
superset version: 1.0

Checklist

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

[x] I have checked the superset logs for python stacktraces and included it here as text if there are any.
[x] I have reproduced the issue with at least the latest released version of superset.
[x] I have checked the issue tracker for the same issue and I haven't found one similar.

joaocc commented 2 years ago

Hi. Has anyone picked up on this? Thx

adimyth commented 1 year ago

Has anyone been able to resolve this yet?

chathawee commented 1 year ago

Hi all, It might be help you. I have resolved it by checking the max seq id value of ab_permission_view_role table and update nextval(ab_permission_view_role_id_seq) table by using below command.

SELECT nextval('ab_permission_view_role_id_seq')
SELECT currval('ab_permission_view_role_id_seq')
SELECT max(id) FROM ab_permission_view_role

After update it, the superset init is back to normal.

rusackas commented 8 months ago

I'm not sure if this is still an issue in current versions of Superset (3.x). If it is, we can re-open this, or feel free to open a new issue with updated context and a reproducible case using example data. We're no longer supporting Superset 2.x or prior, and it's been a while since this thread saw any activity, so I'm closing this as stale.

CosminCioranu commented 1 month ago

I can confirm it works. The issues arises sometimes in 4.x at least on fresh install with mariadb+galera.

Please find below a small script to adjust the values.

  set @lastVar=(SELECT lastval(ab_permission_view_id_seq));
  set @nextVar=(SELECT nextval(ab_permission_view_id_seq));
  set @mm=(select max(id) m FROM ab_permission_view);
  select @mm, @lastVar, @nextVar, if (@lastVar<@mm,@mm,@lastVar) setTo;
  EXECUTE IMMEDIATE CONCAT('SELECT SETVAL(`ab_permission_view_id_seq`, ', @mm, ',true)');

Hi all, It might be help you. I have resolved it by checking the max seq id value of ab_permission_view_role table and update nextval(ab_permission_view_role_id_seq) table by using below command.

SELECT nextval('ab_permission_view_role_id_seq')

SELECT currval('ab_permission_view_role_id_seq')

SELECT max(id) FROM ab_permission_view_role

After update it, the superset init is back to normal.

villebro commented 3 weeks ago

For the users running into this, have you defined FAB_ROLES in your superset_config.py?

apache / superset