search

apache / superset

Apache Superset is a Data Visualization and Data Exploration Platform
https://superset.apache.org/
Apache License 2.0
61.92k stars 13.57k forks source link

upgrade from 2.0.1 to 2.1.0.rc3 fails #23483

Closed SaruWiz closed 8 months ago

SaruWiz commented 1 year ago

A clear and concise description of what the bug is.

How to reproduce the bug

pip based install on Debian Bullseye 11.6

  1. pip install apache-superset==2.1.0rc3 - Successful !

  2. superset db upgrade /superset_root/lib/python3.9/site-packages/flask_appbuilder/models/sqla/interface.py:67: SAWarning: relationship 'SqlaTable.slices' will copy column tables.id to column slices.datasource_id, which conflicts with relationship(s): 'Slice.table' (copies tables.id to slices.datasource_id). If this is not the intention, consider if these relationships should be linked with back_populates, or if viewonly=True should be applied to one or more if they are read-only. For the less common case that foreign key constraints are partially overlapping, the orm.foreign() annotation can be used to isolate the columns that should be written towards. To silence this warning, add the parameter 'overlaps="table"' to the 'SqlaTable.slices' relationship. (Background on this error at: https://sqlalche.me/e/14/qzyx) for prop in class_mapper(obj).iterate_properties: INFO [alembic.runtime.migration] Context impl MySQLImpl. INFO [alembic.runtime.migration] Will assume transactional DDL. INFO [alembic.runtime.migration] Running upgrade e09b4ae78457 -> f3afaf1f11f0, add_unique_name_desc_rls INFO [alembic.runtime.migration] Running upgrade f3afaf1f11f0 -> 7fb8bca906d2, permalink_rename_filterState INFO [alembic.runtime.migration] Running upgrade 7fb8bca906d2 -> cdcf3d64daf4, Add user_id and dttm composite index to Log model INFO [alembic.runtime.migration] Running upgrade cdcf3d64daf4 -> c747c78868b6, Migrating legacy TreeMap INFO [alembic.runtime.migration] Running upgrade c747c78868b6 -> 06e1e70058c7, Migrating legacy Area INFO [alembic.runtime.migration] Running upgrade 06e1e70058c7 -> a39867932713, query_context_to_mediumtext INFO [alembic.runtime.migration] Running upgrade a39867932713 -> 409c7b420ab0, add created_by_fk as owner INFO [alembic.runtime.migration] Running upgrade 409c7b420ab0 -> ffa79af61a56, rename report_schedule.extra to extra_json INFO [alembic.runtime.migration] Running upgrade ffa79af61a56 -> 6d3c6f9d665d, fix_table_chart_conditional_formatting_colors INFO [alembic.runtime.migration] Running upgrade 6d3c6f9d665d -> 291f024254b5, drop_column_allow_multi_schema_metadata_fetch INFO [alembic.runtime.migration] Running upgrade 291f024254b5 -> deb4c9d4a4ef, parameters in saved queries INFO [alembic.runtime.migration] Running upgrade deb4c9d4a4ef -> 4ce1d9b25135, remove_filter_bar_orientation INFO [alembic.runtime.migration] Running upgrade 4ce1d9b25135 -> f3c2d8ec8595, create_ssh_tunnel_credentials_tbl

  3. superset init Loaded your LOCAL configuration at [/superset_root/superset_config.py] logging was configured successfully 2023-03-24 08:39:16,598:INFO:superset.utils.logging_configurator:logging was configured successfully 2023-03-24 08:39:16,620:INFO:root:Configured event logger of type <class 'superset.utils.log.DBEventLogger'> We haven't found any Content Security Policy (CSP) defined in the configurations. Please make sure to configure CSP using the TALISMAN_ENABLED and TALISMAN_CONFIG keys or any other external software. Failing to configure CSP have serious security implications. Check https://developer.mozilla.org/en-US/docs/Web/HTTP/CSP for more information. You can disable this warning using the CONTENT_SECURITY_POLICY_WARNING key. 2023-03-24 08:39:16,622:WARNING:superset.initialization:We haven't found any Content Security Policy (CSP) defined in the configurations. Please make sure to configure CSP using the TALISMAN_ENABLED and TALISMAN_CONFIG keys or any other external software. Failing to configure CSP have serious security implications. Check https://developer.mozilla.org/en-US/docs/Web/HTTP/CSP for more information. You can disable this warning using the CONTENT_SECURITY_POLICY_WARNING key. /superset_root/lib/python3.9/site-packages/flask_appbuilder/models/sqla/interface.py:67: SAWarning: relationship 'SqlaTable.slices' will copy column tables.id to column slices.datasource_id, which conflicts with relationship(s): 'Slice.table' (copies tables.id to slices.datasource_id). If this is not the intention, consider if these relationships should be linked with back_populates, or if viewonly=True should be applied to one or more if they are read-only. For the less common case that foreign key constraints are partially overlapping, the orm.foreign() annotation can be used to isolate the columns that should be written towards. To silence this warning, add the parameter 'overlaps="table"' to the 'SqlaTable.slices' relationship. (Background on this error at: https://sqlalche.me/e/14/qzyx) for prop in class_mapper(obj).iterate_properties: 2023-03-24 08:39:20,274:ERROR:flask_appbuilder.security.sqla.manager:Add View Menu Error: (sqlalchemy.exc.OperationalError) (MySQLdb.OperationalError) (4091, "Unknown SEQUENCE: 'ab_view_menu_id_seq'") [SQL: INSERT INTO ab_view_menu (id, name) VALUES (%s, %s)] [parameters: [{'name': 'AvailableDomains'}]] (Background on this error at: https://sqlalche.me/e/14/e3q8) 2023-03-24 08:39:20,292:ERROR:flask_appbuilder.security.sqla.manager:Add View Menu Error: (sqlalchemy.exc.OperationalError) (MySQLdb.OperationalError) (4091, "Unknown SEQUENCE: 'ab_view_menu_id_seq'") [SQL: INSERT INTO ab_view_menu (id, name) VALUES (%s, %s)] [parameters: [{'name': 'AvailableDomains'}]] (Background on this error at: https://sqlalche.me/e/14/e3q8) 2023-03-24 08:39:20,301:ERROR:flask_appbuilder.security.sqla.manager:Creation of Permission View Error: (sqlalchemy.exc.OperationalError) (MySQLdb.OperationalError) (4091, "Unknown SEQUENCE: 'ab_view_menu_id_seq'") [SQL: INSERT INTO ab_view_menu (id, name) VALUES (%s, %s)] [parameters: [{'name': 'AvailableDomains'}]] (Background on this error at: https://sqlalche.me/e/14/e3q8) 2023-03-24 08:39:20,516:ERROR:flask_appbuilder.security.sqla.manager:Add Permission: (sqlalchemy.exc.OperationalError) (MySQLdb.OperationalError) (4091, "Unknown SEQUENCE: 'ab_permission_id_seq'") [SQL: INSERT INTO ab_permission (id, name) VALUES (%s, %s)] [parameters: [{'name': 'can_get_or_create_dataset'}]] (Background on this error at: https://sqlalche.me/e/14/e3q8) 2023-03-24 08:39:20,522:ERROR:flask_appbuilder.security.sqla.manager:Creation of Permission View Error: (sqlalchemy.exc.OperationalError) (MySQLdb.OperationalError) (4091, "Unknown SEQUENCE: 'ab_permission_id_seq'") [SQL: INSERT INTO ab_permission (id, name) VALUES (%s, %s)] [parameters: [{'name': 'can_get_or_create_dataset'}]] (Background on this error at: https://sqlalche.me/e/14/e3q8) 2023-03-24 08:39:20,553:ERROR:flask_appbuilder.security.sqla.manager:Add Permission: (sqlalchemy.exc.OperationalError) (MySQLdb.OperationalError) (4091, "Unknown SEQUENCE: 'ab_permission_id_seq'") [SQL: INSERT INTO ab_permission (id, name) VALUES (%s, %s)] [parameters: [{'name': 'can_duplicate'}]] (Background on this error at: https://sqlalche.me/e/14/e3q8) 2023-03-24 08:39:20,559:ERROR:flask_appbuilder.security.sqla.manager:Creation of Permission View Error: (sqlalchemy.exc.OperationalError) (MySQLdb.OperationalError) (4091, "Unknown SEQUENCE: 'ab_permission_id_seq'") [SQL: INSERT INTO ab_permission (id, name) VALUES (%s, %s)] [parameters: [{'name': 'can_duplicate'}]] (Background on this error at: https://sqlalche.me/e/14/e3q8) 2023-03-24 08:39:20,703:ERROR:flask_appbuilder.security.sqla.manager:Add Permission: (sqlalchemy.exc.OperationalError) (MySQLdb.OperationalError) (4091, "Unknown SEQUENCE: 'ab_permission_id_seq'") [SQL: INSERT INTO ab_permission (id, name) VALUES (%s, %s)] [parameters: [{'name': 'can_get_column_values'}]] (Background on this error at: https://sqlalche.me/e/14/e3q8) 2023-03-24 08:39:20,705:ERROR:flask_appbuilder.security.sqla.manager:Creation of Permission View Error: (sqlalchemy.exc.OperationalError) (MySQLdb.OperationalError) (4091, "Unknown SEQUENCE: 'ab_permission_id_seq'") [SQL: INSERT INTO ab_permission (id, name) VALUES (%s, %s)] [parameters: [{'name': 'can_get_column_values'}]] (Background on this error at: https://sqlalche.me/e/14/e3q8) 2023-03-24 08:39:20,760:ERROR:flask_appbuilder.security.sqla.manager:Add View Menu Error: (sqlalchemy.exc.OperationalError) (MySQLdb.OperationalError) (4091, "Unknown SEQUENCE: 'ab_view_menu_id_seq'") [SQL: INSERT INTO ab_view_menu (id, name) VALUES (%s, %s)] [parameters: [{'name': 'Explore'}]] (Background on this error at: https://sqlalche.me/e/14/e3q8) 2023-03-24 08:39:20,765:ERROR:flask_appbuilder.security.sqla.manager:Add View Menu Error: (sqlalchemy.exc.OperationalError) (MySQLdb.OperationalError) (4091, "Unknown SEQUENCE: 'ab_view_menu_id_seq'") [SQL: INSERT INTO ab_view_menu (id, name) VALUES (%s, %s)] [parameters: [{'name': 'Explore'}]] (Background on this error at: https://sqlalche.me/e/14/e3q8) 2023-03-24 08:39:20,767:ERROR:flask_appbuilder.security.sqla.manager:Creation of Permission View Error: (sqlalchemy.exc.OperationalError) (MySQLdb.OperationalError) (4091, "Unknown SEQUENCE: 'ab_view_menu_id_seq'") [SQL: INSERT INTO ab_view_menu (id, name) VALUES (%s, %s)] [parameters: [{'name': 'Explore'}]] (Background on this error at: https://sqlalche.me/e/14/e3q8) 2023-03-24 08:39:20,824:ERROR:flask_appbuilder.security.sqla.manager:Add Permission: (sqlalchemy.exc.OperationalError) (MySQLdb.OperationalError) (4091, "Unknown SEQUENCE: 'ab_permission_id_seq'") [SQL: INSERT INTO ab_permission (id, name) VALUES (%s, %s)] [parameters: [{'name': 'can_execute_sql_query'}]] (Background on this error at: https://sqlalche.me/e/14/e3q8) 2023-03-24 08:39:20,826:ERROR:flask_appbuilder.security.sqla.manager:Creation of Permission View Error: (sqlalchemy.exc.OperationalError) (MySQLdb.OperationalError) (4091, "Unknown SEQUENCE: 'ab_permission_id_seq'") [SQL: INSERT INTO ab_permission (id, name) VALUES (%s, %s)] [parameters: [{'name': 'can_execute_sql_query'}]] (Background on this error at: https://sqlalche.me/e/14/e3q8) 2023-03-24 08:39:20,831:ERROR:flask_appbuilder.security.sqla.manager:Add Permission: (sqlalchemy.exc.OperationalError) (MySQLdb.OperationalError) (4091, "Unknown SEQUENCE: 'ab_permission_id_seq'") [SQL: INSERT INTO ab_permission (id, name) VALUES (%s, %s)] [parameters: [{'name': 'can_export_csv'}]] (Background on this error at: https://sqlalche.me/e/14/e3q8) 2023-03-24 08:39:20,833:ERROR:flask_appbuilder.security.sqla.manager:Creation of Permission View Error: (sqlalchemy.exc.OperationalError) (MySQLdb.OperationalError) (4091, "Unknown SEQUENCE: 'ab_permission_id_seq'") [SQL: INSERT INTO ab_permission (id, name) VALUES (%s, %s)] [parameters: [{'name': 'can_export_csv'}]] (Background on this error at: https://sqlalche.me/e/14/e3q8) 2023-03-24 08:39:20,838:ERROR:flask_appbuilder.security.sqla.manager:Add Permission: (sqlalchemy.exc.OperationalError) (MySQLdb.OperationalError) (4091, "Unknown SEQUENCE: 'ab_permission_id_seq'") [SQL: INSERT INTO ab_permission (id, name) VALUES (%s, %s)] [parameters: [{'name': 'can_get_results'}]] (Background on this error at: https://sqlalche.me/e/14/e3q8) 2023-03-24 08:39:20,840:ERROR:flask_appbuilder.security.sqla.manager:Creation of Permission View Error: (sqlalchemy.exc.OperationalError) (MySQLdb.OperationalError) (4091, "Unknown SEQUENCE: 'ab_permission_id_seq'") [SQL: INSERT INTO ab_permission (id, name) VALUES (%s, %s)] [parameters: [{'name': 'can_get_results'}]] (Background on this error at: https://sqlalche.me/e/14/e3q8) 2023-03-24 08:39:20,992:ERROR:flask_appbuilder.security.sqla.manager:Add Permission: (sqlalchemy.exc.OperationalError) (MySQLdb.OperationalError) (4091, "Unknown SEQUENCE: 'ab_permission_id_seq'") [SQL: INSERT INTO ab_permission (id, name) VALUES (%s, %s)] [parameters: [{'name': 'can_samples'}]] (Background on this error at: https://sqlalche.me/e/14/e3q8) 2023-03-24 08:39:20,994:ERROR:flask_appbuilder.security.sqla.manager:Creation of Permission View Error: (sqlalchemy.exc.OperationalError) (MySQLdb.OperationalError) (4091, "Unknown SEQUENCE: 'ab_permission_id_seq'") [SQL: INSERT INTO ab_permission (id, name) VALUES (%s, %s)] [parameters: [{'name': 'can_samples'}]] (Background on this error at: https://sqlalche.me/e/14/e3q8) 2023-03-24 08:39:21,031:ERROR:flask_appbuilder.security.sqla.manager:Add View Menu Error: (sqlalchemy.exc.OperationalError) (MySQLdb.OperationalError) (4091, "Unknown SEQUENCE: 'ab_view_menu_id_seq'") [SQL: INSERT INTO ab_view_menu (id, name) VALUES (%s, %s)] [parameters: [{'name': 'Explore'}]] (Background on this error at: https://sqlalche.me/e/14/e3q8) 2023-03-24 08:39:21,035:ERROR:flask_appbuilder.security.sqla.manager:Add View Menu Error: (sqlalchemy.exc.OperationalError) (MySQLdb.OperationalError) (4091, "Unknown SEQUENCE: 'ab_view_menu_id_seq'") [SQL: INSERT INTO ab_view_menu (id, name) VALUES (%s, %s)] [parameters: [{'name': 'Explore'}]] (Background on this error at: https://sqlalche.me/e/14/e3q8) 2023-03-24 08:39:21,037:ERROR:flask_appbuilder.security.sqla.manager:Creation of Permission View Error: (sqlalchemy.exc.OperationalError) (MySQLdb.OperationalError) (4091, "Unknown SEQUENCE: 'ab_view_menu_id_seq'") [SQL: INSERT INTO ab_view_menu (id, name) VALUES (%s, %s)] [parameters: [{'name': 'Explore'}]] (Background on this error at: https://sqlalche.me/e/14/e3q8) 2023-03-24 08:39:21,040:ERROR:flask_appbuilder.security.sqla.manager:Add View Menu Error: (sqlalchemy.exc.OperationalError) (MySQLdb.OperationalError) (4091, "Unknown SEQUENCE: 'ab_view_menu_id_seq'") [SQL: INSERT INTO ab_view_menu (id, name) VALUES (%s, %s)] [parameters: [{'name': 'Explore'}]] (Background on this error at: https://sqlalche.me/e/14/e3q8) 2023-03-24 08:39:21,044:ERROR:flask_appbuilder.security.sqla.manager:Add View Menu Error: (sqlalchemy.exc.OperationalError) (MySQLdb.OperationalError) (4091, "Unknown SEQUENCE: 'ab_view_menu_id_seq'") [SQL: INSERT INTO ab_view_menu (id, name) VALUES (%s, %s)] [parameters: [{'name': 'Explore'}]] (Background on this error at: https://sqlalche.me/e/14/e3q8) 2023-03-24 08:39:21,047:ERROR:flask_appbuilder.security.sqla.manager:Creation of Permission View Error: (sqlalchemy.exc.OperationalError) (MySQLdb.OperationalError) (4091, "Unknown SEQUENCE: 'ab_view_menu_id_seq'") [SQL: INSERT INTO ab_view_menu (id, name) VALUES (%s, %s)] [parameters: [{'name': 'Explore'}]] (Background on this error at: https://sqlalche.me/e/14/e3q8) 2023-03-24 08:39:21,192:ERROR:flask_appbuilder.security.sqla.manager:Creation of Permission View Error: (sqlalchemy.exc.OperationalError) (MySQLdb.OperationalError) (4091, "Unknown SEQUENCE: 'ab_permission_view_id_seq'") [SQL: INSERT INTO ab_permission_view (id, permission_id, view_menu_id) VALUES (%s, %s, %s)] [parameters: [{'permission_id': 39, 'view_menu_id': 50}]] (Background on this error at: https://sqlalche.me/e/14/e3q8) Syncing role definition 2023-03-24 08:39:21,329:INFO:superset.security.manager:Syncing role definition Syncing Admin perms 2023-03-24 08:39:21,340:INFO:superset.security.manager:Syncing Admin perms Syncing Alpha perms 2023-03-24 08:39:21,456:INFO:superset.security.manager:Syncing Alpha perms Syncing Gamma perms 2023-03-24 08:39:21,590:INFO:superset.security.manager:Syncing Gamma perms Syncing granter perms 2023-03-24 08:39:21,746:INFO:superset.security.manager:Syncing granter perms Syncing sql_lab perms 2023-03-24 08:39:22,201:INFO:superset.security.manager:Syncing sql_lab perms Traceback (most recent call last): File "/superset_root/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1880, in _execute_context self.dialect.do_executemany( File "/superset_root/lib/python3.9/site-packages/sqlalchemy/dialects/mysql/mysqldb.py", line 180, in do_executemany rowcount = cursor.executemany(statement, parameters) File "/superset_root/lib/python3.9/site-packages/MySQLdb/cursors.py", line 239, in executemany self.rowcount = sum(self.execute(query, arg) for arg in args) File "/superset_root/lib/python3.9/site-packages/MySQLdb/cursors.py", line 239, in self.rowcount = sum(self.execute(query, arg) for arg in args) File "/superset_root/lib/python3.9/site-packages/MySQLdb/cursors.py", line 206, in execute res = self._query(query) File "/superset_root/lib/python3.9/site-packages/MySQLdb/cursors.py", line 319, in _query db.query(q) File "/superset_root/lib/python3.9/site-packages/MySQLdb/connections.py", line 254, in query _mysql.connection.query(self, query) MySQLdb.ProgrammingError: (1146, "Table 'superset.ab_permission_view_role_id_seq' doesn't exist")

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

Traceback (most recent call last): File "/superset_root/bin/superset", line 11, in load_entry_point('apache-superset==2.1.0rc3', 'console_scripts', 'superset')() File "/superset_root/lib/python3.9/site-packages/click/core.py", line 1130, in call return self.main(*args, kwargs) File "/superset_root/lib/python3.9/site-packages/flask/cli.py", line 567, in main return super().main(args, kwargs) File "/superset_root/lib/python3.9/site-packages/click/core.py", line 1055, in main rv = self.invoke(ctx) File "/superset_root/lib/python3.9/site-packages/click/core.py", line 1657, in invoke return _process_result(sub_ctx.command.invoke(sub_ctx)) File "/superset_root/lib/python3.9/site-packages/click/core.py", line 1404, in invoke return ctx.invoke(self.callback, ctx.params) File "/superset_root/lib/python3.9/site-packages/click/core.py", line 760, in invoke return __callback(args, kwargs) File "/superset_root/lib/python3.9/site-packages/click/decorators.py", line 26, in new_func return f(get_current_context(), *args, kwargs) File "/superset_root/lib/python3.9/site-packages/flask/cli.py", line 407, in decorator return __ctx.invoke(f, *args, *kwargs) File "/superset_root/lib/python3.9/site-packages/click/core.py", line 760, in invoke return __callback(args, kwargs) File "/superset_root/lib/python3.9/site-packages/click/decorators.py", line 26, in new_func return f(get_current_context(), *args, kwargs) File "/superset_root/lib/python3.9/site-packages/flask/cli.py", line 407, in decorator return __ctx.invoke(f, *args, *kwargs) File "/superset_root/lib/python3.9/site-packages/click/core.py", line 760, in invoke return __callback(args, kwargs) File "/superset_root/lib/python3.9/site-packages/superset/cli/main.py", line 62, in init security_manager.sync_role_definitions() File "/superset_root/lib/python3.9/site-packages/superset/security/manager.py", line 792, in sync_role_definitions self.set_role("sql_lab", self._is_sql_lab_pvm) File "/superset_root/lib/python3.9/site-packages/superset/security/manager.py", line 885, in set_role self.get_session.merge(role) File "", line 2, in merge File "/superset_root/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 3047, in merge self._autoflush() File "/superset_root/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 2261, in autoflush util.raise(e, with_traceback=sys.exc_info()[2]) File "/supersetroot/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise raise exception File "/superset_root/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 2250, in _autoflush self.flush() File "/superset_root/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 3446, in flush self._flush(objects) File "/superset_root/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 3586, in _flush transaction.rollback(_capture_exception=True) File "/supersetroot/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in exit compat.raise( File "/supersetroot/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise raise exception File "/superset_root/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 3546, in _flush flush_context.execute() File "/superset_root/lib/python3.9/site-packages/sqlalchemy/orm/unitofwork.py", line 456, in execute rec.execute(self) File "/superset_root/lib/python3.9/site-packages/sqlalchemy/orm/unitofwork.py", line 579, in execute self.dependency_processor.process_saves(uow, states) File "/superset_root/lib/python3.9/site-packages/sqlalchemy/orm/dependency.py", line 1182, in process_saves self._run_crud( File "/superset_root/lib/python3.9/site-packages/sqlalchemy/orm/dependency.py", line 1245, in _run_crud connection.execute(statement, secondary_insert) File "/superset_root/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1380, in execute return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS) File "/superset_root/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection return connection._execute_clauseelement( File "/superset_root/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement ret = self._execute_context( File "/superset_root/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context self._handle_dbapi_exception( File "/superset_root/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapiexception util.raise( File "/supersetroot/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise raise exception File "/superset_root/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1880, in _execute_context self.dialect.do_executemany( File "/superset_root/lib/python3.9/site-packages/sqlalchemy/dialects/mysql/mysqldb.py", line 180, in do_executemany rowcount = cursor.executemany(statement, parameters) File "/superset_root/lib/python3.9/site-packages/MySQLdb/cursors.py", line 239, in executemany self.rowcount = sum(self.execute(query, arg) for arg in args) File "/superset_root/lib/python3.9/site-packages/MySQLdb/cursors.py", line 239, in self.rowcount = sum(self.execute(query, arg) for arg in args) File "/superset_root/lib/python3.9/site-packages/MySQLdb/cursors.py", line 206, in execute res = self._query(query) File "/superset_root/lib/python3.9/site-packages/MySQLdb/cursors.py", line 319, in _query db.query(q) File "/superset_root/lib/python3.9/site-packages/MySQLdb/connections.py", line 254, in query _mysql.connection.query(self, query) sqlalchemy.exc.ProgrammingError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (MySQLdb.ProgrammingError) (1146, "Table 'superset.ab_permission_view_role_id_seq' doesn't exist") [SQL: INSERT INTO ab_permission_view_role (id, permission_view_id, role_id) VALUES (nextval(ab_permission_view_role_id_seq), %s, %s)] [parameters: ((147, 6), (153, 6), (63, 6), (68, 6), (66, 6), (98, 6), (114, 6), (126, 6) ... displaying 10 of 14 total bound parameter sets ... (151, 6), (152, 6))] (Background on this error at: https://sqlalche.me/e/14/f405)

  1. /superset_root/bin/gunicorn -w 4 -k gevent --timeout 120 -b 0.0.0.0:6666 --limit-request-line 0 --limit-request-field_size 0 "superset.app:create_app()"

  2. See error

After login to UI there is an alert: There was an issue fetching your recent activity

At this point the charts and dashboards work as expected. However when editing a chart there is a "Missing dataset Warning" offering swap dataset.

Database connection Test works as expected.

SQL Lab query returns "Database Error" Forbidden with exception being thrown..

(MySQLdb.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (superset.tab_state, CONSTRAINTtab_state_latest_query_id_fkeyFOREIGN KEY (latest_query_id) REFERENCESquery(client_id) ON DELETE SET NULL)') [SQL: UPDATE tab_state SET changed_on=%s, latest_query_id=%s, changed_by_fk=%s WHERE tab_state.id = %s] [parameters: (datetime.datetime(2023, 3, 24, 8, 46, 28, 440763), 'ztiX2scW7', 7, 3)] (Background on this error at: https://sqlalche.me/e/14/gkpj) Traceback (most recent call last): File "/superset_root/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context self.dialect.do_execute( File "/superset_root/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute cursor.execute(statement, parameters) File "/superset_root/lib/python3.9/site-packages/MySQLdb/cursors.py", line 206, in execute res = self._query(query) File "/superset_root/lib/python3.9/site-packages/MySQLdb/cursors.py", line 319, in _query db.query(q) File "/superset_root/lib/python3.9/site-packages/MySQLdb/connections.py", line 254, in query _mysql.connection.query(self, query) MySQLdb.IntegrityError: (1452, 'Cannot add or update a child row: a foreign key constraint fails (superset.tab_state, CONSTRAINTtab_state_latest_query_id_fkeyFOREIGN KEY (latest_query_id) REFERENCESquery(client_id`) ON DELETE SET NULL)')

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

Traceback (most recent call last): File "/superset_root/lib/python3.9/site-packages/flask/app.py", line 1517, in full_dispatch_request rv = self.dispatch_request() File "/superset_root/lib/python3.9/site-packages/flask/app.py", line 1503, in dispatch_request return self.ensure_sync(self.view_functions[rule.endpoint])(*req.view_args) File "/superset_root/lib/python3.9/site-packages/flask_appbuilder/security/decorators.py", line 181, in wraps return f(self, args, **kwargs) File "/superset_root/lib/python3.9/site-packages/superset/views/sql_lab/views.py", line 219, in put db.session.query(TabState).filter_by(id=tab_state_id).update(fields) File "/superset_root/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 3306, in update result = self.session.execute( File "/superset_root/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1714, in execute result = conn._execute_20(statement, params or {}, execution_options) File "/superset_root/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_20 return meth(self, args_10style, kwargs_10style, execution_options) File "/superset_root/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection return connection._execute_clauseelement( File "/superset_root/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement ret = self._execute_context( File "/superset_root/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context self._handle_dbapi_exception( File "/superset_root/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapiexception util.raise( File "/supersetroot/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise raise exception File "/superset_root/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context self.dialect.do_execute( File "/superset_root/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute cursor.execute(statement, parameters) File "/superset_root/lib/python3.9/site-packages/MySQLdb/cursors.py", line 206, in execute res = self._query(query) File "/superset_root/lib/python3.9/site-packages/MySQLdb/cursors.py", line 319, in _query db.query(q) File "/superset_root/lib/python3.9/site-packages/MySQLdb/connections.py", line 254, in query _mysql.connection.query(self, query) sqlalchemy.exc.IntegrityError: (MySQLdb.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (superset.tab_state, CONSTRAINT tab_state_latest_query_id_fkey FOREIGN KEY (latest_query_id) REFERENCES query (client_id) ON DELETE SET NULL)') [SQL: UPDATE tab_state SET changed_on=%s, latest_query_id=%s, changed_by_fk=%s WHERE tab_state.id = %s] [parameters: (datetime.datetime(2023, 3, 24, 8, 46, 28, 440763), 'ztiX2scW7', 7, 3)] (Background on this error at: https://sqlalche.me/e/14/gkpj) 2023-03-24 08:46:28,441:ERROR:superset.views.base:(MySQLdb.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (superset.tab_state, CONSTRAINT tab_state_latest_query_id_fkey FOREIGN KEY (latest_query_id) REFERENCES query (client_id) ON DELETE SET NULL)') [SQL: UPDATE tab_state SET changed_on=%s, latest_query_id=%s, changed_by_fk=%s WHERE tab_state.id = %s] [parameters: (datetime.datetime(2023, 3, 24, 8, 46, 28, 440763), 'ztiX2scW7', 7, 3)] (Background on this error at: https://sqlalche.me/e/14/gkpj) Traceback (most recent call last): File "/superset_root/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context self.dialect.do_execute( File "/superset_root/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute cursor.execute(statement, parameters) File "/superset_root/lib/python3.9/site-packages/MySQLdb/cursors.py", line 206, in execute res = self._query(query) File "/superset_root/lib/python3.9/site-packages/MySQLdb/cursors.py", line 319, in _query db.query(q) File "/superset_root/lib/python3.9/site-packages/MySQLdb/connections.py", line 254, in query _mysql.connection.query(self, query) MySQLdb.IntegrityError: (1452, 'Cannot add or update a child row: a foreign key constraint fails (superset.tab_state, CONSTRAINT tab_state_latest_query_id_fkey FOREIGN KEY (latest_query_id) REFERENCES query (client_id) ON DELETE SET NULL)')

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

Traceback (most recent call last): File "/superset_root/lib/python3.9/site-packages/flask/app.py", line 1517, in full_dispatch_request rv = self.dispatch_request() File "/superset_root/lib/python3.9/site-packages/flask/app.py", line 1503, in dispatch_request return self.ensure_sync(self.view_functions[rule.endpoint])(*req.view_args) File "/superset_root/lib/python3.9/site-packages/flask_appbuilder/security/decorators.py", line 181, in wraps return f(self, args, **kwargs) File "/superset_root/lib/python3.9/site-packages/superset/views/sql_lab/views.py", line 219, in put db.session.query(TabState).filter_by(id=tab_state_id).update(fields) File "/superset_root/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 3306, in update result = self.session.execute( File "/superset_root/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1714, in execute result = conn._execute_20(statement, params or {}, execution_options) File "/superset_root/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_20 return meth(self, args_10style, kwargs_10style, execution_options) File "/superset_root/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection return connection._execute_clauseelement( File "/superset_root/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement ret = self._execute_context( File "/superset_root/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context self._handle_dbapi_exception( File "/superset_root/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapiexception util.raise( File "/supersetroot/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise raise exception File "/superset_root/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context self.dialect.do_execute( File "/superset_root/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute cursor.execute(statement, parameters) File "/superset_root/lib/python3.9/site-packages/MySQLdb/cursors.py", line 206, in execute res = self._query(query) File "/superset_root/lib/python3.9/site-packages/MySQLdb/cursors.py", line 319, in _query db.query(q) File "/superset_root/lib/python3.9/site-packages/MySQLdb/connections.py", line 254, in query _mysql.connection.query(self, query) sqlalchemy.exc.IntegrityError: (MySQLdb.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (superset.tab_state, CONSTRAINT tab_state_latest_query_id_fkey FOREIGN KEY (latest_query_id) REFERENCES query (client_id) ON DELETE SET NULL)') [SQL: UPDATE tab_state SET changed_on=%s, latest_query_id=%s, changed_by_fk=%s WHERE tab_state.id = %s] [parameters: (datetime.datetime(2023, 3, 24, 8, 46, 28, 440763), 'ztiX2scW7', 7, 3)] (Background on this error at: https://sqlalche.me/e/14/gkpj)`

Environment

(please complete the following information):

Checklist

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

mdeshmu commented 1 year ago

@eschutho ^

tooptoop4 commented 1 year ago

i'm getting same issue with postgres backend

alanorth commented 1 year ago

Upgrading from 2.0.1 to 2.1.0 failed for me as well:

# systemctl stop superset
# su - superset
$ mv venv venv-2.0.1
$ python3.8 -m venv venv
$ source ./venv/bin/activate
$ pip install --upgrade setuptools pip wheel
# Make sure we install the EXACT dependencies listed in Superset 2.1.0's requirements.txt
$ curl -sS https://raw.githubusercontent.com/apache/superset/2.1.0/requirements/base.txt | tail -n +10 | awk -v ORS=" " '/^[A-z]/{print}' | xargs pip install apache-superset==2.1.0
$ pip install pillow mysqlclient gevent
$ export PYTHONPATH=/home/superset
$ export FLASK_APP=superset
$ superset
$ superset db upgrade
$ superset init
Loaded your LOCAL configuration at [/home/superset/superset_config.py]
...
(MySQLdb.ProgrammingError) (1146, "Table 'superset.ab_permission_view_role_id_seq' doesn't exist")
[SQL: INSERT INTO ab_permission_view_role (id, permission_view_id, role_id) VALUES (nextval(ab_permission_view_role_id_seq), %s, %s)]
[parameters: ((460, 6), (462, 6), (148, 6), (186, 6), (228, 6), (463, 6), (457, 6), (458, 6)  ... displaying 10 of 14 total bound parameter sets ...  (585, 6), (805, 6))]
(Background on this error at: https://sqlalche.me/e/14/f405)

Log: superset-init.log

P.S. @SaruWiz you should clean up your bug report to use Markdown formatting so it's easier to read.

grifonas commented 1 year ago

Getting the same issue when upgrading 2.0.1 > 2.1.0. I'm using Postgres as well.

prb219 commented 1 year ago

I am seeing the same issue with 2.1.0 with MySQL as the metadata store.

eschutho commented 1 year ago

My guess is that you have a row in your tab_state that is referencing a query that has been deleted/no longer exists. We can edit this migration (I'm pretty certain that it's this one: https://github.com/apache/superset/pull/19243/files) to account for this and drop the tab state if this happens. If someone wants to validate this and has access to edit your metadata, I'd see if your db is in this state as I described and try to delete the tab_state with the null query relationship. If anyone wants to update the migration and submit a PR for a long-term fix, I'd also be happy to review.

rodionxabibov commented 1 year ago

When updating a superset from 2.0 -> 2.1 I get the same error, is there a solution to this problem?

Pooort commented 1 year ago

My guess is that you have a row in your tab_state that is referencing a query that has been deleted/no longer exists. We can edit this migration (I'm pretty certain that it's this one: https://github.com/apache/superset/pull/19243/files) to account for this and drop the tab state if this happens. If someone wants to validate this and has access to edit your metadata, I'd see if your db is in this state as I described and try to delete the tab_state with the null query relationship. If anyone wants to update the migration and submit a PR for a long-term fix, I'd also be happy to review.

@eschutho I've made this. tab_state table is empty, but still the same issue.

And we already have overlaps="table": https://github.com/apache/superset/blob/65fb8e10ba065c9037a7058544ec491a8b5a2051/superset/connectors/base/models.py#L164

The current alembic version num is: f3c2d8ec8595

rodionxabibov commented 1 year ago

I added overlap="table" and cleared the entries in the tab_state table, and I keep looking for one in the bug(1146, "The table 'supersetdb.ab_permission_view_id_seq' does not exist") after superset init

rodionxabibov commented 1 year ago

Has anyone been able to find a solution to this problem?

dpgaspar commented 1 year ago

for troubleshooting purposes, using postgres, after running superset db upgrade, make sure the sequence exists using:

 SELECT * 
FROM pg_sequences 
WHERE schemaname = 'public' 
  AND sequencename = 'ab_permission_view_id_seq';

then make sure all FAB tables exist:

SELECT table_name
FROM information_schema.tables
WHERE table_name LIKE 'ab_%';

for troubleshooting also, try running superset init on your instance without upgrading to check that the same error occurs

rodionxabibov commented 1 year ago

Superset is connected to mysql db Upgrading from 2.0 to 2.0.1 does not cause errors

superset init

Loaded your LOCAL configuration at [/opt/xxx/superset/config/superset_config.py]
logging was configured successfully
2023-08-28 15:28:14,718:INFO:superset.utils.logging_configurator:logging was configured successfully
2023-08-28 15:28:14,726:INFO:root:Configured event logger of type <class 'superset.utils.log.DBEventLogger'>
We haven't found any Content Security Policy (CSP) defined in the configurations. Please make sure to configure CSP using the TALISMAN_CONFIG key or any other external software. Failing to configure CSP have serious security implications. Check https://developer.mozilla.org/en-US/docs/Web/HTTP/CSP for more information. You can disable this warning using the CONTENT_SECURITY_POLICY_WARNING key.
2023-08-28 15:28:14,727:WARNING:superset.initialization:We haven't found any Content Security Policy (CSP) defined in the configurations. Please make sure to configure CSP using the TALISMAN_CONFIG key or any other external software. Failing to configure CSP have serious security implications. Check https://developer.mozilla.org/en-US/docs/Web/HTTP/CSP for more information. You can disable this warning using the CONTENT_SECURITY_POLICY_WARNING key.
Syncing role definition
2023-08-28 15:28:16,718:INFO:superset.security.manager:Syncing role definition
Syncing Admin perms
2023-08-28 15:28:16,728:INFO:superset.security.manager:Syncing Admin perms
Syncing Alpha perms
2023-08-28 15:28:17,193:INFO:superset.security.manager:Syncing Alpha perms
Syncing Gamma perms
2023-08-28 15:28:17,551:INFO:superset.security.manager:Syncing Gamma perms
Syncing granter perms
2023-08-28 15:28:17,924:INFO:superset.security.manager:Syncing granter perms
Syncing sql_lab perms
2023-08-28 15:28:18,293:INFO:superset.security.manager:Syncing sql_lab perms
Fetching a set of all perms to lookup which ones are missing
2023-08-28 15:28:18,655:INFO:superset.security.manager:Fetching a set of all perms to lookup which ones are missing
Creating missing datasource permissions.
2023-08-28 15:28:19,018:INFO:superset.security.manager:Creating missing datasource permissions.
Creating missing database permissions.
2023-08-28 15:28:19,029:INFO:superset.security.manager:Creating missing database permissions.
Cleaning faulty perms
2023-08-28 15:28:19,032:INFO:superset.security.manager:Cleaning faulty perms

updating the python environment pip install apache-superset --upgrade 

pip list | grep superset
apache-superset        2.1.0

superset upgrade

Loaded your LOCAL configuration at [/opt/xxx/superset/config/superset_config.py]
logging was configured successfully
2023-08-28 15:34:19,908:INFO:superset.utils.logging_configurator:logging was configured successfully
2023-08-28 15:34:19,915:INFO:root:Configured event logger of type <class 'superset.utils.log.DBEventLogger'>
We haven't found any Content Security Policy (CSP) defined in the configurations. Please make sure to configure CSP using the TALISMAN_ENABLED and TALISMAN_CONFIG keys or any other external software. Failing to configure CSP have serious security implications. Check https://developer.mozilla.org/en-US/docs/Web/HTTP/CSP for more information. You can disable this warning using the CONTENT_SECURITY_POLICY_WARNING key.
2023-08-28 15:34:19,915:WARNING:superset.initialization:We haven't found any Content Security Policy (CSP) defined in the configurations. Please make sure to configure CSP using the TALISMAN_ENABLED and TALISMAN_CONFIG keys or any other external software. Failing to configure CSP have serious security implications. Check https://developer.mozilla.org/en-US/docs/Web/HTTP/CSP for more information. You can disable this warning using the CONTENT_SECURITY_POLICY_WARNING key.
/opt/xxx/superset/lib64/python3.8/site-packages/flask_appbuilder/models/sqla/interface.py:67: SAWarning: relationship 'SqlaTable.slices' will copy column tables.id to column slices.datasource_id, which conflicts with relationship(s): 'Slice.table' (copies tables.id to slices.datasource_id). If this is not the intention, consider if these relationships should be linked with back_populates, or if viewonly=True should be applied to one or more if they are read-only. For the less common case that foreign key constraints are partially overlapping, the orm.foreign() annotation can be used to isolate the columns that should be written towards.   To silence this warning, add the parameter 'overlaps="table"' to the 'SqlaTable.slices' relationship. (Background on this error at: https://sqlalche.me/e/14/qzyx)
  for prop in class_mapper(obj).iterate_properties:
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade e09b4ae78457 -> f3afaf1f11f0, add_unique_name_desc_rls
INFO  [alembic.runtime.migration] Running upgrade f3afaf1f11f0 -> 7fb8bca906d2, permalink_rename_filterState
INFO  [alembic.runtime.migration] Running upgrade 7fb8bca906d2 -> cdcf3d64daf4, Add user_id and dttm composite index to Log model
INFO  [alembic.runtime.migration] Running upgrade cdcf3d64daf4 -> c747c78868b6, Migrating legacy TreeMap
INFO  [alembic.runtime.migration] Running upgrade c747c78868b6 -> 06e1e70058c7, Migrating legacy Area
INFO  [alembic.runtime.migration] Running upgrade 06e1e70058c7 -> a39867932713, query_context_to_mediumtext
INFO  [alembic.runtime.migration] Running upgrade a39867932713 -> 409c7b420ab0, add created_by_fk as owner
INFO  [alembic.runtime.migration] Running upgrade 409c7b420ab0 -> ffa79af61a56, rename report_schedule.extra to extra_json
INFO  [alembic.runtime.migration] Running upgrade ffa79af61a56 -> 6d3c6f9d665d, fix_table_chart_conditional_formatting_colors
INFO  [alembic.runtime.migration] Running upgrade 6d3c6f9d665d -> 291f024254b5, drop_column_allow_multi_schema_metadata_fetch
INFO  [alembic.runtime.migration] Running upgrade 291f024254b5 -> deb4c9d4a4ef, parameters in saved queries
INFO  [alembic.runtime.migration] Running upgrade deb4c9d4a4ef -> 4ce1d9b25135, remove_filter_bar_orientation
INFO  [alembic.runtime.migration] Running upgrade 4ce1d9b25135 -> f3c2d8ec8595, create_ssh_tunnel_credentials_tbl

superset init

Loaded your LOCAL configuration at [/opt/xxx/superset/config/superset_config.py]
logging was configured successfully
2023-08-28 15:34:56,026:INFO:superset.utils.logging_configurator:logging was configured successfully
2023-08-28 15:34:56,033:INFO:root:Configured event logger of type <class 'superset.utils.log.DBEventLogger'>
We haven't found any Content Security Policy (CSP) defined in the configurations. Please make sure to configure CSP using the TALISMAN_ENABLED and TALISMAN_CONFIG keys or any other external software. Failing to configure CSP have serious security implications. Check https://developer.mozilla.org/en-US/docs/Web/HTTP/CSP for more information. You can disable this warning using the CONTENT_SECURITY_POLICY_WARNING key.
2023-08-28 15:34:56,033:WARNING:superset.initialization:We haven't found any Content Security Policy (CSP) defined in the configurations. Please make sure to configure CSP using the TALISMAN_ENABLED and TALISMAN_CONFIG keys or any other external software. Failing to configure CSP have serious security implications. Check https://developer.mozilla.org/en-US/docs/Web/HTTP/CSP for more information. You can disable this warning using the CONTENT_SECURITY_POLICY_WARNING key.
/opt/xxx/superset/lib64/python3.8/site-packages/flask_appbuilder/models/sqla/interface.py:67: SAWarning: relationship 'SqlaTable.slices' will copy column tables.id to column slices.datasource_id, which conflicts with relationship(s): 'Slice.table' (copies tables.id to slices.datasource_id). If this is not the intention, consider if these relationships should be linked with back_populates, or if viewonly=True should be applied to one or more if they are read-only. For the less common case that foreign key constraints are partially overlapping, the orm.foreign() annotation can be used to isolate the columns that should be written towards.   To silence this warning, add the parameter 'overlaps="table"' to the 'SqlaTable.slices' relationship. (Background on this error at: https://sqlalche.me/e/14/qzyx)
  for prop in class_mapper(obj).iterate_properties:
2023-08-28 15:34:57,299:ERROR:flask_appbuilder.security.sqla.manager:Add View Menu Error: (sqlalchemy.exc.ProgrammingError) (MySQLdb.ProgrammingError) (1146, "Table 'supersetdb.ab_view_menu_id_seq' doesn't exist")
[SQL: INSERT INTO ab_view_menu (id, name) VALUES (%s, %s)]
[parameters: [{'name': 'AvailableDomains'}]]
(Background on this error at: https://sqlalche.me/e/14/f405)
2023-08-28 15:34:57,304:ERROR:flask_appbuilder.security.sqla.manager:Add View Menu Error: (sqlalchemy.exc.ProgrammingError) (MySQLdb.ProgrammingError) (1146, "Table 'supersetdb.ab_view_menu_id_seq' doesn't exist")
[SQL: INSERT INTO ab_view_menu (id, name) VALUES (%s, %s)]
[parameters: [{'name': 'AvailableDomains'}]]
(Background on this error at: https://sqlalche.me/e/14/f405)
2023-08-28 15:34:57,306:ERROR:flask_appbuilder.security.sqla.manager:Creation of Permission View Error: (sqlalchemy.exc.ProgrammingError) (MySQLdb.ProgrammingError) (1146, "Table 'supersetdb.ab_view_menu_id_seq' doesn't exist")
[SQL: INSERT INTO ab_view_menu (id, name) VALUES (%s, %s)]
[parameters: [{'name': 'AvailableDomains'}]]
(Background on this error at: https://sqlalche.me/e/14/f405)
2023-08-28 15:34:57,355:ERROR:flask_appbuilder.security.sqla.manager:Add Permission: (sqlalchemy.exc.ProgrammingError) (MySQLdb.ProgrammingError) (1146, "Table 'supersetdb.ab_permission_id_seq' doesn't exist")
[SQL: INSERT INTO ab_permission (id, name) VALUES (%s, %s)]
[parameters: [{'name': 'can_get_or_create_dataset'}]]
(Background on this error at: https://sqlalche.me/e/14/f405)
2023-08-28 15:34:57,357:ERROR:flask_appbuilder.security.sqla.manager:Creation of Permission View Error: (sqlalchemy.exc.ProgrammingError) (MySQLdb.ProgrammingError) (1146, "Table 'supersetdb.ab_permission_id_seq' doesn't exist")
[SQL: INSERT INTO ab_permission (id, name) VALUES (%s, %s)]
[parameters: [{'name': 'can_get_or_create_dataset'}]]
(Background on this error at: https://sqlalche.me/e/14/f405)
2023-08-28 15:34:57,366:ERROR:flask_appbuilder.security.sqla.manager:Add Permission: (sqlalchemy.exc.ProgrammingError) (MySQLdb.ProgrammingError) (1146, "Table 'supersetdb.ab_permission_id_seq' doesn't exist")
[SQL: INSERT INTO ab_permission (id, name) VALUES (%s, %s)]
[parameters: [{'name': 'can_duplicate'}]]
(Background on this error at: https://sqlalche.me/e/14/f405)
2023-08-28 15:34:57,367:ERROR:flask_appbuilder.security.sqla.manager:Creation of Permission View Error: (sqlalchemy.exc.ProgrammingError) (MySQLdb.ProgrammingError) (1146, "Table 'supersetdb.ab_permission_id_seq' doesn't exist")
[SQL: INSERT INTO ab_permission (id, name) VALUES (%s, %s)]
[parameters: [{'name': 'can_duplicate'}]]
(Background on this error at: https://sqlalche.me/e/14/f405)
2023-08-28 15:34:57,415:ERROR:flask_appbuilder.security.sqla.manager:Add Permission: (sqlalchemy.exc.ProgrammingError) (MySQLdb.ProgrammingError) (1146, "Table 'supersetdb.ab_permission_id_seq' doesn't exist")
[SQL: INSERT INTO ab_permission (id, name) VALUES (%s, %s)]
[parameters: [{'name': 'can_get_column_values'}]]
(Background on this error at: https://sqlalche.me/e/14/f405)
2023-08-28 15:34:57,417:ERROR:flask_appbuilder.security.sqla.manager:Creation of Permission View Error: (sqlalchemy.exc.ProgrammingError) (MySQLdb.ProgrammingError) (1146, "Table 'supersetdb.ab_permission_id_seq' doesn't exist")
[SQL: INSERT INTO ab_permission (id, name) VALUES (%s, %s)]
[parameters: [{'name': 'can_get_column_values'}]]
(Background on this error at: https://sqlalche.me/e/14/f405)
2023-08-28 15:34:57,451:ERROR:flask_appbuilder.security.sqla.manager:Add View Menu Error: (sqlalchemy.exc.ProgrammingError) (MySQLdb.ProgrammingError) (1146, "Table 'supersetdb.ab_view_menu_id_seq' doesn't exist")
[SQL: INSERT INTO ab_view_menu (id, name) VALUES (%s, %s)]
[parameters: [{'name': 'Explore'}]]
(Background on this error at: https://sqlalche.me/e/14/f405)
2023-08-28 15:34:57,455:ERROR:flask_appbuilder.security.sqla.manager:Add View Menu Error: (sqlalchemy.exc.ProgrammingError) (MySQLdb.ProgrammingError) (1146, "Table 'supersetdb.ab_view_menu_id_seq' doesn't exist")
[SQL: INSERT INTO ab_view_menu (id, name) VALUES (%s, %s)]
[parameters: [{'name': 'Explore'}]]
(Background on this error at: https://sqlalche.me/e/14/f405)
2023-08-28 15:34:57,457:ERROR:flask_appbuilder.security.sqla.manager:Creation of Permission View Error: (sqlalchemy.exc.ProgrammingError) (MySQLdb.ProgrammingError) (1146, "Table 'supersetdb.ab_view_menu_id_seq' doesn't exist")
[SQL: INSERT INTO ab_view_menu (id, name) VALUES (%s, %s)]
[parameters: [{'name': 'Explore'}]]
(Background on this error at: https://sqlalche.me/e/14/f405)
2023-08-28 15:34:57,497:ERROR:flask_appbuilder.security.sqla.manager:Add Permission: (sqlalchemy.exc.ProgrammingError) (MySQLdb.ProgrammingError) (1146, "Table 'supersetdb.ab_permission_id_seq' doesn't exist")
[SQL: INSERT INTO ab_permission (id, name) VALUES (%s, %s)]
[parameters: [{'name': 'can_get_results'}]]
(Background on this error at: https://sqlalche.me/e/14/f405)
2023-08-28 15:34:57,499:ERROR:flask_appbuilder.security.sqla.manager:Creation of Permission View Error: (sqlalchemy.exc.ProgrammingError) (MySQLdb.ProgrammingError) (1146, "Table 'supersetdb.ab_permission_id_seq' doesn't exist")
[SQL: INSERT INTO ab_permission (id, name) VALUES (%s, %s)]
[parameters: [{'name': 'can_get_results'}]]
(Background on this error at: https://sqlalche.me/e/14/f405)
2023-08-28 15:34:57,504:ERROR:flask_appbuilder.security.sqla.manager:Add Permission: (sqlalchemy.exc.ProgrammingError) (MySQLdb.ProgrammingError) (1146, "Table 'supersetdb.ab_permission_id_seq' doesn't exist")
[SQL: INSERT INTO ab_permission (id, name) VALUES (%s, %s)]
[parameters: [{'name': 'can_export_csv'}]]
(Background on this error at: https://sqlalche.me/e/14/f405)
2023-08-28 15:34:57,505:ERROR:flask_appbuilder.security.sqla.manager:Creation of Permission View Error: (sqlalchemy.exc.ProgrammingError) (MySQLdb.ProgrammingError) (1146, "Table 'supersetdb.ab_permission_id_seq' doesn't exist")
[SQL: INSERT INTO ab_permission (id, name) VALUES (%s, %s)]
[parameters: [{'name': 'can_export_csv'}]]
(Background on this error at: https://sqlalche.me/e/14/f405)
2023-08-28 15:34:57,512:ERROR:flask_appbuilder.security.sqla.manager:Add Permission: (sqlalchemy.exc.ProgrammingError) (MySQLdb.ProgrammingError) (1146, "Table 'supersetdb.ab_permission_id_seq' doesn't exist")
[SQL: INSERT INTO ab_permission (id, name) VALUES (%s, %s)]
[parameters: [{'name': 'can_execute_sql_query'}]]
(Background on this error at: https://sqlalche.me/e/14/f405)
2023-08-28 15:34:57,513:ERROR:flask_appbuilder.security.sqla.manager:Creation of Permission View Error: (sqlalchemy.exc.ProgrammingError) (MySQLdb.ProgrammingError) (1146, "Table 'supersetdb.ab_permission_id_seq' doesn't exist")
[SQL: INSERT INTO ab_permission (id, name) VALUES (%s, %s)]
[parameters: [{'name': 'can_execute_sql_query'}]]
(Background on this error at: https://sqlalche.me/e/14/f405)
2023-08-28 15:34:57,638:ERROR:flask_appbuilder.security.sqla.manager:Add Permission: (sqlalchemy.exc.ProgrammingError) (MySQLdb.ProgrammingError) (1146, "Table 'supersetdb.ab_permission_id_seq' doesn't exist")
[SQL: INSERT INTO ab_permission (id, name) VALUES (%s, %s)]
[parameters: [{'name': 'can_samples'}]]
(Background on this error at: https://sqlalche.me/e/14/f405)
2023-08-28 15:34:57,640:ERROR:flask_appbuilder.security.sqla.manager:Creation of Permission View Error: (sqlalchemy.exc.ProgrammingError) (MySQLdb.ProgrammingError) (1146, "Table 'supersetdb.ab_permission_id_seq' doesn't exist")
[SQL: INSERT INTO ab_permission (id, name) VALUES (%s, %s)]
[parameters: [{'name': 'can_samples'}]]
(Background on this error at: https://sqlalche.me/e/14/f405)
2023-08-28 15:34:57,669:ERROR:flask_appbuilder.security.sqla.manager:Add View Menu Error: (sqlalchemy.exc.ProgrammingError) (MySQLdb.ProgrammingError) (1146, "Table 'supersetdb.ab_view_menu_id_seq' doesn't exist")
[SQL: INSERT INTO ab_view_menu (id, name) VALUES (%s, %s)]
[parameters: [{'name': 'Explore'}]]
(Background on this error at: https://sqlalche.me/e/14/f405)
2023-08-28 15:34:57,672:ERROR:flask_appbuilder.security.sqla.manager:Add View Menu Error: (sqlalchemy.exc.ProgrammingError) (MySQLdb.ProgrammingError) (1146, "Table 'supersetdb.ab_view_menu_id_seq' doesn't exist")
[SQL: INSERT INTO ab_view_menu (id, name) VALUES (%s, %s)]
[parameters: [{'name': 'Explore'}]]
(Background on this error at: https://sqlalche.me/e/14/f405)
2023-08-28 15:34:57,675:ERROR:flask_appbuilder.security.sqla.manager:Creation of Permission View Error: (sqlalchemy.exc.ProgrammingError) (MySQLdb.ProgrammingError) (1146, "Table 'supersetdb.ab_view_menu_id_seq' doesn't exist")
[SQL: INSERT INTO ab_view_menu (id, name) VALUES (%s, %s)]
[parameters: [{'name': 'Explore'}]]
(Background on this error at: https://sqlalche.me/e/14/f405)
2023-08-28 15:34:57,677:ERROR:flask_appbuilder.security.sqla.manager:Add View Menu Error: (sqlalchemy.exc.ProgrammingError) (MySQLdb.ProgrammingError) (1146, "Table 'supersetdb.ab_view_menu_id_seq' doesn't exist")
[SQL: INSERT INTO ab_view_menu (id, name) VALUES (%s, %s)]
[parameters: [{'name': 'Explore'}]]
(Background on this error at: https://sqlalche.me/e/14/f405)
2023-08-28 15:34:57,681:ERROR:flask_appbuilder.security.sqla.manager:Add View Menu Error: (sqlalchemy.exc.ProgrammingError) (MySQLdb.ProgrammingError) (1146, "Table 'supersetdb.ab_view_menu_id_seq' doesn't exist")
[SQL: INSERT INTO ab_view_menu (id, name) VALUES (%s, %s)]
[parameters: [{'name': 'Explore'}]]
(Background on this error at: https://sqlalche.me/e/14/f405)
2023-08-28 15:34:57,683:ERROR:flask_appbuilder.security.sqla.manager:Creation of Permission View Error: (sqlalchemy.exc.ProgrammingError) (MySQLdb.ProgrammingError) (1146, "Table 'supersetdb.ab_view_menu_id_seq' doesn't exist")
[SQL: INSERT INTO ab_view_menu (id, name) VALUES (%s, %s)]
[parameters: [{'name': 'Explore'}]]
(Background on this error at: https://sqlalche.me/e/14/f405)
2023-08-28 15:34:57,843:ERROR:flask_appbuilder.security.sqla.manager:Creation of Permission View Error: (sqlalchemy.exc.ProgrammingError) (MySQLdb.ProgrammingError) (1146, "Table 'supersetdb.ab_permission_view_id_seq' doesn't exist")
[SQL: INSERT INTO ab_permission_view (id, permission_id, view_menu_id) VALUES (%s, %s, %s)]
[parameters: [{'permission_id': 62, 'view_menu_id': 7}]]
(Background on this error at: https://sqlalche.me/e/14/f405)
Syncing role definition
2023-08-28 15:34:57,951:INFO:superset.security.manager:Syncing role definition
Syncing Admin perms
2023-08-28 15:34:57,961:INFO:superset.security.manager:Syncing Admin perms
Syncing Alpha perms
2023-08-28 15:34:58,438:INFO:superset.security.manager:Syncing Alpha perms
Syncing Gamma perms
2023-08-28 15:34:58,922:INFO:superset.security.manager:Syncing Gamma perms
Syncing granter perms
2023-08-28 15:34:59,395:INFO:superset.security.manager:Syncing granter perms
Syncing sql_lab perms
2023-08-28 15:35:00,052:INFO:superset.security.manager:Syncing sql_lab perms
Traceback (most recent call last):
  File "/opt/xxx/superset/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1799, in _execute_context
    self.dialect.do_executemany(
  File "/opt/xxx/superset/lib64/python3.8/site-packages/sqlalchemy/dialects/mysql/mysqldb.py", line 193, in do_executemany
    rowcount = cursor.executemany(statement, parameters)
  File "/opt/xxx/superset/lib64/python3.8/site-packages/MySQLdb/cursors.py", line 239, in executemany
    self.rowcount = sum(self.execute(query, arg) for arg in args)
  File "/opt/xxx/superset/lib64/python3.8/site-packages/MySQLdb/cursors.py", line 239, in <genexpr>
    self.rowcount = sum(self.execute(query, arg) for arg in args)
  File "/opt/xxx/superset/lib64/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/opt/xxx/superset/lib64/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/opt/xxx/superset/lib64/python3.8/site-packages/MySQLdb/connections.py", line 254, in query
    _mysql.connection.query(self, query)
MySQLdb.ProgrammingError: (1146, "Table 'supersetdb.ab_permission_view_role_id_seq' doesn't exist")

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

Traceback (most recent call last):
  File "/opt/xxx/superset/bin/superset", line 8, in <module>
    sys.exit(superset())
  File "/opt/xxx/superset/lib64/python3.8/site-packages/click/core.py", line 1128, in __call__
    return self.main(*args, **kwargs)
  File "/opt/xxx/superset/lib64/python3.8/site-packages/flask/cli.py", line 567, in main
    return super().main(*args, **kwargs)
  File "/opt/xxx/superset/lib64/python3.8/site-packages/click/core.py", line 1053, in main
    rv = self.invoke(ctx)
  File "/opt/xxx/superset/lib64/python3.8/site-packages/click/core.py", line 1659, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/opt/xxx/superset/lib64/python3.8/site-packages/click/core.py", line 1395, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/opt/xxx/superset/lib64/python3.8/site-packages/click/core.py", line 754, in invoke
    return __callback(*args, **kwargs)
  File "/opt/xxx/superset/lib64/python3.8/site-packages/click/decorators.py", line 26, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/opt/xxx/superset/lib64/python3.8/site-packages/flask/cli.py", line 407, in decorator
    return __ctx.invoke(f, *args, **kwargs)
  File "/opt/xxx/superset/lib64/python3.8/site-packages/click/core.py", line 754, in invoke
    return __callback(*args, **kwargs)
  File "/opt/xxx/superset/lib64/python3.8/site-packages/click/decorators.py", line 26, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/opt/xxx/superset/lib64/python3.8/site-packages/flask/cli.py", line 407, in decorator
    return __ctx.invoke(f, *args, **kwargs)
  File "/opt/xxx/superset/lib64/python3.8/site-packages/click/core.py", line 754, in invoke
    return __callback(*args, **kwargs)
  File "/opt/xxx/superset/lib64/python3.8/site-packages/superset/cli/main.py", line 62, in init
    security_manager.sync_role_definitions()
  File "/opt/xxx/superset/lib64/python3.8/site-packages/superset/security/manager.py", line 792, in sync_role_definitions
    self.set_role("sql_lab", self._is_sql_lab_pvm)
  File "/opt/xxx/superset/lib64/python3.8/site-packages/superset/security/manager.py", line 885, in set_role
    self.get_session.merge(role)
  File "<string>", line 2, in merge
  File "/opt/xxx/superset/lib64/python3.8/site-packages/sqlalchemy/orm/session.py", line 2971, in merge
    self._autoflush()
  File "/opt/xxx/superset/lib64/python3.8/site-packages/sqlalchemy/orm/session.py", line 2241, in _autoflush
    util.raise_(e, with_traceback=sys.exc_info()[2])
  File "/opt/xxx/superset/lib64/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/opt/xxx/superset/lib64/python3.8/site-packages/sqlalchemy/orm/session.py", line 2230, in _autoflush
    self.flush()
  File "/opt/xxx/superset/lib64/python3.8/site-packages/sqlalchemy/orm/session.py", line 3367, in flush
    self._flush(objects)
  File "/opt/xxx/superset/lib64/python3.8/site-packages/sqlalchemy/orm/session.py", line 3507, in _flush
    transaction.rollback(_capture_exception=True)
  File "/opt/xxx/superset/lib64/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/opt/xxx/superset/lib64/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/opt/xxx/superset/lib64/python3.8/site-packages/sqlalchemy/orm/session.py", line 3467, in _flush
    flush_context.execute()
  File "/opt/xxx/superset/lib64/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 456, in execute
    rec.execute(self)
  File "/opt/xxx/superset/lib64/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 579, in execute
    self.dependency_processor.process_saves(uow, states)
  File "/opt/xxx/superset/lib64/python3.8/site-packages/sqlalchemy/orm/dependency.py", line 1182, in process_saves
    self._run_crud(
  File "/opt/xxx/superset/lib64/python3.8/site-packages/sqlalchemy/orm/dependency.py", line 1245, in _run_crud
    connection.execute(statement, secondary_insert)
  File "/opt/xxx/superset/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1306, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/opt/xxx/superset/lib64/python3.8/site-packages/sqlalchemy/sql/elements.py", line 325, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/opt/xxx/superset/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1498, in _execute_clauseelement
    ret = self._execute_context(
  File "/opt/xxx/superset/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context
    self._handle_dbapi_exception(
  File "/opt/xxx/superset/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception
    util.raise_(
  File "/opt/xxx/superset/lib64/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/opt/xxx/superset/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1799, in _execute_context
    self.dialect.do_executemany(
  File "/opt/xxx/superset/lib64/python3.8/site-packages/sqlalchemy/dialects/mysql/mysqldb.py", line 193, in do_executemany
    rowcount = cursor.executemany(statement, parameters)
  File "/opt/xxx/superset/lib64/python3.8/site-packages/MySQLdb/cursors.py", line 239, in executemany
    self.rowcount = sum(self.execute(query, arg) for arg in args)
  File "/opt/xxx/superset/lib64/python3.8/site-packages/MySQLdb/cursors.py", line 239, in <genexpr>
    self.rowcount = sum(self.execute(query, arg) for arg in args)
  File "/opt/xxx/superset/lib64/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/opt/xxx/superset/lib64/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/opt/xxx/superset/lib64/python3.8/site-packages/MySQLdb/connections.py", line 254, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.ProgrammingError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
(MySQLdb.ProgrammingError) (1146, "Table 'supersetdb.ab_permission_view_role_id_seq' doesn't exist")
[SQL: INSERT INTO ab_permission_view_role (id, permission_view_id, role_id) VALUES (nextval(ab_permission_view_role_id_seq), %s, %s)]
[parameters: ((19, 6), (2, 6), (165, 6), (164, 6), (79, 6), (109, 6), (128, 6), (132, 6)  ... displaying 10 of 14 total bound parameter sets ...  (167, 6), (169, 6))]
(Background on this error at: https://sqlalche.me/e/14/f405)

I connect to the mysql database, the ab_permission_view_id_seq table does not exist

connection string in the superset configuration:

SQLALCHEMY_DATABASE_URI = "mysql://superset:x.x.x.x.@x.x.x.x/superset"

dpgaspar commented 1 year ago

@rodionxabibov please post your environment sqlalchemy and mysql driver versions, or just post the entire pip freeze output.

rodionxabibov commented 1 year ago

@dpgaspar 

mysqlclient==2.2.0
SQLAlchemy==1.4.49

pip freeze:

aiohttp==3.8.1
aiosignal==1.2.0
alembic==1.6.5
amqp==5.1.0
apache-superset==2.1.0
apispec==3.3.2
async-generator==1.10
async-timeout==4.0.2
asynch==0.2.2
attrs==21.2.0
Babel==2.9.1
backoff==1.11.1
bcrypt==4.0.1
billiard==3.6.4.0
bleach==3.3.1
Brotli==1.0.9
cachelib==0.4.1
celery==5.2.2
certifi==2022.6.15
cffi==1.15.1
charset-normalizer==2.0.4
ciso8601==2.3.0
click==8.0.4
click-didyoumean==0.3.0
click-plugins==1.1.1
click-repl==0.2.0
clickhouse-cityhash==1.0.2.4
clickhouse-driver==0.2.0
clickhouse-sqlalchemy==0.2.4
colorama==0.4.4
commonmark==0.9.1
convertdate==2.4.0
cron-descriptor==1.2.24
croniter==1.0.15
cryptography==39.0.0
cx-Oracle==8.3.0
dataclasses==0.6
Deprecated==1.2.13
deprecation==2.1.0
dnspython==2.1.0
email-validator==1.1.3
Flask==2.1.3
Flask-AppBuilder==4.3.0
Flask-Babel==1.0.0
Flask-Caching==1.10.1
Flask-Compress==1.13
Flask-JWT-Extended==4.3.1
Flask-Limiter==3.4.1
Flask-Login==0.6.0
Flask-Migrate==3.1.0
Flask-SQLAlchemy==2.5.1
flask-talisman==1.0.0
Flask-WTF==1.0.1
frozenlist==1.3.0
func-timeout==4.3.5
geographiclib==1.52
geopy==2.2.0
graphlib-backport==1.0.3
greenlet==2.0.2
gunicorn==20.1.0
h11==0.13.0
hashids==1.3.1
hijri-converter==2.2.4
holidays==0.17.2
humanize==3.11.0
idna==3.2
importlib-metadata==4.12.0
importlib-resources==5.8.0
isodate==0.6.0
itsdangerous==2.1.1
Jinja2==3.0.3
jsonschema==3.2.0
kombu==5.2.4
korean-lunar-calendar==0.2.1
leb128==1.0.5
limits==3.5.0
lz4==4.3.2
Mako==1.1.4
Markdown==3.3.4
markdown-it-py==3.0.0
MarkupSafe==2.1.1
marshmallow==3.13.0
marshmallow-enum==1.5.1
marshmallow-sqlalchemy==0.23.1
mdurl==0.1.2
msgpack==1.0.2
multidict==5.1.0
mysqlclient==2.2.0
numpy==1.23.5
ordered-set==4.1.0
outcome==1.2.0
packaging==21.3
pandas==1.5.3
paramiko==2.11.0
parsedatetime==2.6
pgsanity==0.2.9
Pillow==9.2.0
polyline==1.4.0
prison==0.2.1
prompt-toolkit==3.0.28
psycopg2-binary==2.9.3
pyarrow==10.0.1
pycparser==2.20
Pygments==2.16.1
PyJWT==2.4.0
PyMeeus==0.5.11
PyNaCl==1.5.0
pyOpenSSL==22.0.0
pyparsing==3.0.6
pyrsistent==0.16.1
PySocks==1.7.1
python-dateutil==2.8.2
python-dotenv==0.19.0
python-editor==1.0.4
python-geohash==0.8.5
pytz==2021.3
PyYAML==5.4.1
redis==3.5.3
requests==2.28.1
rich==12.0.1
selenium==3.141.0
simplejson==3.17.3
six==1.16.0
slack-sdk==3.18.3
slackclient==2.5.0
sniffio==1.2.0
sortedcontainers==2.4.0
SQLAlchemy==1.4.49
SQLAlchemy-Utils==0.38.3
sqlparse==0.4.3
sshtunnel==0.4.0
tabulate==0.8.9
trio==0.21.0
trio-websocket==0.9.2
typing_extensions==4.4.0
tzlocal==2.1
urllib3==1.26.6
vine==5.0.0
wcwidth==0.2.5
webencodings==0.5.1
Werkzeug==2.1.2
wrapt==1.14.1
wsproto==1.1.0
WTForms==2.3.3
WTForms-JSON==0.3.3
XlsxWriter==3.0.7
yarl==1.6.3
zipp==3.8.1
zstd==1.5.5.1
rodionxabibov commented 1 year ago

I checked the connection using mysql-connector-python SQLALCHEMY_DATABASE_URI = "mysql+mysqlconnector://superset:x.x.x.x.@x.x.x.x/superset" and pymysql SQLALCHEMY_DATABASE_URI = "mysql+pymysql://superset:x.x.x.x.@x.x.x.x/superset" but I also get an error

sqlalchemy.exc.ProgrammingError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
(pymysql.err.ProgrammingError) (1146, "Table 'supersetdb.ab_permission_view_role_id_seq' doesn't exist")
[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': 19, 'role_id': 6}, {'permission_view_id': 2, 'role_id': 6}, {'permission_view_id': 165, 'role_id': 6}, {'permission_view_id': 164, 'role_id': 6}, {'permission_view_id': 79, 'role_id': 6}, {'permission_view_id': 109, 'role_id': 6}, {'permission_view_id': 128, 'role_id': 6}, {'permission_view_id': 132, 'role_id': 6}  ... displaying 10 of 14 total bound parameter sets ...  {'permission_view_id': 167, 'role_id': 6}, {'permission_view_id': 169, 'role_id': 6})]
(Background on this error at: https://sqlalche.me/e/14/f405)
rodionxabibov commented 1 year ago

I used mysql-connector-odbc && sqlalchemy==1.4.35 && pyodbc and changed the connection string to mysql SQLALCHEMY_DATABASE_URL = "mysql+pyodbc://superset:xxxxxxxx@xxxxxxx/superset " and the database was updated && init without errors

tullis commented 1 year ago

I am also affected by this bug, attempting to upgrade from Superset 1.5.3 to 2.1.1.

When running superset init I receive the following error:

MySQLdb.ProgrammingError: (1146, "Table 'superset_staging.ab_permission_view_role_id_seq' doesn't exist")

We can see that the error is correct, that table is not present.

MariaDB [superset_staging]> SELECT table_name
    -> FROM information_schema.tables
    -> WHERE table_name LIKE 'ab_%';
+-------------------------+
| table_name              |
+-------------------------+
| ab_user_role            |
| ab_user                 |
| ab_permission_view_role |
| ab_role                 |
| ab_register_user        |
| ab_permission_view      |
| ab_view_menu            |
| ab_permission           |
| ab_user_role            |
| ab_user                 |
| ab_permission_view_role |
| ab_role                 |
| ab_register_user        |
| ab_permission_view      |
| ab_view_menu            |
| ab_permission           |
| ab_user_role            |
| ab_user                 |
| ab_permission_view_role |
| ab_role                 |
| ab_register_user        |
| ab_permission_view      |
| ab_view_menu            |
| ab_permission           |
| ab_user_role            |
| ab_user                 |
| ab_permission_view_role |
| ab_role                 |
| ab_register_user        |
| ab_permission_view      |
| ab_view_menu            |
| ab_permission           |
| ab_user_role            |
| ab_user                 |
| ab_permission_view_role |
| ab_role                 |
| ab_register_user        |
| ab_permission_view      |
| ab_view_menu            |
| ab_permission           |
+-------------------------+
40 rows in set (0.004 sec)

Database: MariaDB 10.4.18

sqlalchemy: 1.4.36 (I have also tried 1.4.35) mysqlclient: 2.2.0

The tab_state table is empty:

MariaDB [superset_staging]> select count(*) from tab_state;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.001 sec)

The superset db upgrade completed with the following output:

(venv) superset@an-tool1005:~$ superset db upgrade
Loaded your LOCAL configuration at [/etc/superset/superset_config.py]
logging was configured successfully
2023-09-18 23:35:54,609:INFO:superset.utils.logging_configurator:logging was configured successfully
2023-09-18 23:35:54,619:INFO:root:Configured event logger of type <class 'superset.utils.log.DBEventLogger'>
We haven't found any Content Security Policy (CSP) defined in the configurations. Please make sure to configure CSP using the TALISMAN_ENABLED and TALISMAN_CONFIG keys or any other external software. Failing to configure CSP have serious security implications. Check https://developer.mozilla.org/en-US/docs/Web/HTTP/CSP for more information. You can disable this warning using the CONTENT_SECURITY_POLICY_WARNING key.
2023-09-18 23:35:54,620:WARNING:superset.initialization:We haven't found any Content Security Policy (CSP) defined in the configurations. Please make sure to configure CSP using the TALISMAN_ENABLED and TALISMAN_CONFIG keys or any other external software. Failing to configure CSP have serious security implications. Check https://developer.mozilla.org/en-US/docs/Web/HTTP/CSP for more information. You can disable this warning using the CONTENT_SECURITY_POLICY_WARNING key.
No PIL installation found
2023-09-18 23:35:54,857:INFO:superset.utils.screenshots:No PIL installation found
/srv/deployment/analytics/superset/venv/lib/python3.9/site-packages/flask_appbuilder/models/sqla/interface.py:67: SAWarning: relationship 'SqlaTable.slices' will copy column tables.id to column slices.datasource_id, which conflicts with relationship(s): 'Slice.table' (copies tables.id to slices.datasource_id). If this is not the intention, consider if these relationships should be linked with back_populates, or if viewonly=True should be applied to one or more if they are read-only. For the less common case that foreign key constraints are partially overlapping, the orm.foreign() annotation can be used to isolate the columns that should be written towards.   To silence this warning, add the parameter 'overlaps="table"' to the 'SqlaTable.slices' relationship. (Background on this error at: https://sqlalche.me/e/14/qzyx)
  for prop in class_mapper(obj).iterate_properties:
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 58df9d617f14 -> 2ed890b36b94, rm_time_range_endpoints_from_qc
INFO  [alembic.runtime.migration] Running upgrade 2ed890b36b94 -> b0d0249074e4, deprecate time_range_endpoints v2
INFO  [alembic.runtime.migration] Running upgrade 2ed890b36b94 -> 8b841273bec3, sql_lab_models_database_constraint_updates
INFO  [alembic.runtime.migration] Running upgrade 8b841273bec3, b0d0249074e4 -> 9d8a8d575284, merge point
INFO  [alembic.runtime.migration] Running upgrade 9d8a8d575284 -> cecc6bf46990, rm_time_range_endpoints_2
INFO  [alembic.runtime.migration] Running upgrade cecc6bf46990 -> ad07e4fdbaba, rm_time_range_endpoints_from_qc_3
slices updated with no time_range_endpoints: 1398
INFO  [alembic.runtime.migration] Running upgrade ad07e4fdbaba -> a9422eeaae74, new_dataset_models_take_2
>> Copy 184 physical tables to sl_tables...
>> Copy 1,411 SqlaTable to sl_datasets...
   Copy dataset owners...
   Link physical datasets with tables...
>> Copy 8,159 table columns to sl_columns...
   Link all columns to sl_datasets...
>> Copy 1,552 metrics to sl_columns...
   Link metric columns to datasets...
>> Run postprocessing on 9,711 columns
   [Column 1 to 9,711] 8,459 may be updated
   Assign table column relations...
>> Run postprocessing on 1411 datasets
   Process dataset 1~1411...                              
>> Assign new UUIDs to tables...

Adding uuids for `sl_tables`...
Done. Assigned 184 uuids in 0.013s.

>> Drop intermediate columns...
INFO  [alembic.runtime.migration] Running upgrade a9422eeaae74 -> cbe71abde154, fix report schedule and execution log
INFO  [alembic.runtime.migration] Running upgrade cbe71abde154 -> 6f139c533bea, adding advanced data type to column models
INFO  [alembic.runtime.migration] Running upgrade 6f139c533bea -> e786798587de, Delete None permissions
Going to delete a data access permission [datasource_access on [None].[joal-hive-presto-Untitled Query-SJe4g_j_PG](id:5)]
<snip>
Going to delete a data access permission [schema_access on [None].[event]]
<snip>
INFO  [alembic.runtime.migration] Running upgrade e786798587de -> e09b4ae78457, Resize key_value blob
INFO  [alembic.runtime.migration] Running upgrade e09b4ae78457 -> f3afaf1f11f0, add_unique_name_desc_rls
INFO  [alembic.runtime.migration] Running upgrade f3afaf1f11f0 -> 7fb8bca906d2, permalink_rename_filterState
INFO  [alembic.runtime.migration] Running upgrade 7fb8bca906d2 -> cdcf3d64daf4, Add user_id and dttm composite index to Log model
INFO  [alembic.runtime.migration] Running upgrade cdcf3d64daf4 -> c747c78868b6, Migrating legacy TreeMap
INFO  [alembic.runtime.migration] Running upgrade c747c78868b6 -> 06e1e70058c7, Migrating legacy Area
INFO  [alembic.runtime.migration] Running upgrade 06e1e70058c7 -> a39867932713, query_context_to_mediumtext
INFO  [alembic.runtime.migration] Running upgrade a39867932713 -> 409c7b420ab0, add created_by_fk as owner
INFO  [alembic.runtime.migration] Running upgrade 409c7b420ab0 -> ffa79af61a56, rename report_schedule.extra to extra_json
INFO  [alembic.runtime.migration] Running upgrade ffa79af61a56 -> 6d3c6f9d665d, fix_table_chart_conditional_formatting_colors
INFO  [alembic.runtime.migration] Running upgrade 6d3c6f9d665d -> 291f024254b5, drop_column_allow_multi_schema_metadata_fetch
INFO  [alembic.runtime.migration] Running upgrade 291f024254b5 -> deb4c9d4a4ef, parameters in saved queries
INFO  [alembic.runtime.migration] Running upgrade deb4c9d4a4ef -> 4ce1d9b25135, remove_filter_bar_orientation
INFO  [alembic.runtime.migration] Running upgrade 4ce1d9b25135 -> f3c2d8ec8595, create_ssh_tunnel_credentials_tbl
INFO  [alembic.runtime.migration] Running upgrade f3c2d8ec8595 -> 9c2a5681ddfd, convert key-value entries to json
(venv) superset@an-tool1005:~$
rodionxabibov commented 1 year ago

As a result, the transition from mariadb 10.4 > mysql 5.7 helped me

tullis commented 1 year ago

As a result, the transition from mariadb 10.4 > mysql 5.7 helped me

Thanks @rodionxabibov - I'm not able to do that very easily, given that this is a production instance. It might be possible for me to migrate to PostgreSQL, but I'm not aware of an easy migration path for MariaDB -> PostgreSQL

In the mantime, I tried downgrading the mysqlclient package to 2.1.1 but it didn't make a difference.

rabindragogoi commented 1 year ago

While if my metastore (mysql-db) is empty, freshly installed superset-2.1.1 works good and UI is up. But when I upgrade it from 1.5.2 to 2.1.1, it fails in db upgrade. below is the error enounterd. image

rabindragogoi commented 1 year ago

As a result, the transition from mariadb 10.4 > mysql 5.7 helped me

So you mean changing the metastore from mariadb to mysql worked with you?

rodionxabibov commented 1 year ago

My update procedure:

rabindragogoi commented 11 months ago

@rodionxabibov : Unfortunately we cannot replace mariaDB with mysql as MetadataDB . Is there any other workaround that can be done so that it upgrades successfully with mariaDB?

Or will Superset 3.0.0 work if we upgrade from 1.5.2 to 3.0.0 ?

eschutho commented 11 months ago

There seems to be an issue with sqlalchemy 1.4 which was bumped in Superset 2.1.0 and MariaDB's functionality of Sequence. Here's a related thread: https://github.com/apache/airflow/issues/24247#issuecomment-1147581509 Some people show their workarounds for MariaDB to get around running the sequence commands. I was able to find an update in sqlalchemy-continuum thread about fixing the issue, perhaps in sqlalchemy 2.0. Let us know if for now you're able to fix the issue with any of the workarounds from the suggestions in the airflow thread.

eschutho commented 11 months ago

Or will Superset 3.0.0 work if we upgrade from 1.5.2 to 3.0.0 ?

@rabindragogoi, I don't think that 3.0.0 will fix the issue. What driver are you using for your metadata (maria) db?

tullis commented 11 months ago

@eschutho - Thanks for this additional information. I will also try some of the workarounds from the airflow thread, as you suggest.

Incidentally, if we were to attempt to migrate our Superset metadata db from MariaDB to PostgreSQL (as mentioned in https://github.com/apache/superset/issues/23483#issuecomment-1726113359) - would you have any suggestions as to the best approach to take? Thanks.

rabindragogoi commented 11 months ago

@eschutho : Thanks for the airflow link that you have provided. It helped me to get the idea and fix the issue. i am able to upgrade superset db from 1.5.2 to 2.1.1 being mariadb as my metastore db.

Below are the steps I followed:

  1. I created all the the missing sequence as below.

DROP SEQUENCE IF EXISTS ab_permission_id_seq; CREATE SEQUENCE ab_permission_id_seq start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE = InnoDB; SELECT SETVAL(ab_permission_id_seq, 105, 0);

DROP SEQUENCE IF EXISTS ab_view_menu_id_seq; CREATE SEQUENCE ab_view_menu_id_seq start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE = InnoDB; SELECT SETVAL(ab_view_menu_id_seq, 8014, 0);

DROP SEQUENCE IF EXISTS ab_permission_view_id_seq; CREATE SEQUENCE ab_permission_view_id_seq start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE = InnoDB; SELECT SETVAL(ab_permission_view_id_seq, 8195, 0);

DROP SEQUENCE IF EXISTS ab_permission_view_role_id_seq; CREATE SEQUENCE ab_permission_view_role_id_seq start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE = InnoDB; SELECT SETVAL(ab_permission_view_role_id_seq, 1238, 0);

  1. For each table ab_permission_view, ab_permission_view_role, ab_view_menu and ab_permission I extracted the max id columns value and same I placed in the create sequence command as the start index. (else will return a duplicate value.).

Cheers !!!!

tullis commented 11 months ago

Thank you @eschutho and @rabindragogoi - I can confirm that the above workaround worked for me too.

I had to increment each of the SELECT max(id) values by one before using it in the corresponding SELECT SETVAL statement, but that makes sense.

eschutho commented 11 months ago

Perfect, I'm glad that this was able to unblock you both!

tullis commented 11 months ago

I've found another missing sequence in my upgraded 2.1.1 instance. I attempted to add the Alpha role to my account and received the following error. image

Oct 12 16:15:15 an-tool1005 superset[3608822]: 2023-10-12 16:15:15,189:ERROR:flask_appbuilder.models.sqla.interface:Edit record error: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
Oct 12 16:15:15 an-tool1005 superset[3608822]: (sqlalchemy.exc.ProgrammingError) (MySQLdb.ProgrammingError) (1146, "Table 'superset_staging.ab_user_role_id_seq' doesn't exist")
Oct 12 16:15:15 an-tool1005 superset[3608822]: [SQL: INSERT INTO ab_user_role (id, user_id, role_id) VALUES (%s, %s, %s)]
Oct 12 16:15:15 an-tool1005 superset[3608822]: [parameters: [{'user_id': 447, 'role_id': 3}]]
Oct 12 16:15:15 an-tool1005 superset[3608822]: (Background on this error at: https://sqlalche.me/e/14/f405)
Oct 12 16:15:15 an-tool1005 superset[3608822]: Traceback (most recent call last):
Oct 12 16:15:15 an-tool1005 superset[3608822]:   File "/srv/deployment/analytics/superset/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1897, in _cursor_execute
Oct 12 16:15:15 an-tool1005 superset[3608822]:     self.dialect.do_execute(cursor, statement, parameters, context)
Oct 12 16:15:15 an-tool1005 superset[3608822]:   File "/srv/deployment/analytics/superset/venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
Oct 12 16:15:15 an-tool1005 superset[3608822]:     cursor.execute(statement, parameters)
Oct 12 16:15:15 an-tool1005 superset[3608822]:   File "/srv/deployment/analytics/superset/venv/lib/python3.9/site-packages/MySQLdb/cursors.py", line 179, in execute
Oct 12 16:15:15 an-tool1005 superset[3608822]:     res = self._query(mogrified_query)
Oct 12 16:15:15 an-tool1005 superset[3608822]:   File "/srv/deployment/analytics/superset/venv/lib/python3.9/site-packages/MySQLdb/cursors.py", line 330, in _query
Oct 12 16:15:15 an-tool1005 superset[3608822]:     db.query(q)
Oct 12 16:15:15 an-tool1005 superset[3608822]:   File "/srv/deployment/analytics/superset/venv/lib/python3.9/site-packages/MySQLdb/connections.py", line 255, in query
Oct 12 16:15:15 an-tool1005 superset[3608822]:     _mysql.connection.query(self, query)
Oct 12 16:15:15 an-tool1005 superset[3608822]: MySQLdb.ProgrammingError: (1146, "Table 'superset_staging.ab_user_role_id_seq' doesn't exist")

Presumably I can just create the sequence like I did for ab_permission_id_seq, ab_view_menu_id_seq, ab_permission_view_id_seq and ab_permission_view_role_id_seq, but it would be better if we could fix the broken migrations with the mariadb dialect.

Is there an easy way to check what sequences should be present?

sfirke commented 8 months ago

Given that version 2.1 reached end of life this week with the release of 3.1.0, and that the original issue seems to have been fixed here, I'm going to mark this issue closed.

deds commented 8 months ago

Just noting here that this still shows up for me trying to upgrade from 2.0.0 to 3.1.0. All four sequences are missing.

I'm also interested to know whether normal operations that does inserts into these tables would continue with the auto increment field for mariadb or start using the sequences? Because if it does, then this would break again at some point.