Upgrade issue: duplicate column name: catalog_perm

[Jockxtar]

Bug description

Hi, I'm getting an error when trying to upgrade to the lastest version (4.1.1) from 4.0.2.
I was following this guide: https://www.restack.io/docs/superset-knowledge-apache-superset-upgrade-guide#clowid9ph01r50v0u5lw7jfth
The error happens when applying database migrations; executing "superset db upgrade".

I'm guessing it's related to #30596 , but that issue is marked as closed and I got no answer when I asked (maybe because I should have mentioned someone? It's my first time reporting bugs through github).
In that post @sadpandajoe mentioned that there were fixes to migrations in 4.1.0 rc3 and rc4. It did solve OP's error, but it seems like there still is something missing because it's not working for me.

Any help?

Screenshots/recordings

No response

Superset version

master / latest-dev

Python version

3.10

Node version

16

Browser

Not applicable

Additional context

WARNI [alembic.env] SQLite Database support for metadata databases will be removed in a future version of Superset.
INFO [alembic.env] Starting the migration scripts.
INFO [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
INFO [alembic.runtime.migration] Running upgrade 4a33124c18ad -> 58d051681a3b, Add catalog_perm to tables
Traceback (most recent call last):
File "/opt/superset/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
self.dialect.do_execute(
File "/opt/superset/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
cursor.execute(statement, parameters)
sqlite3.OperationalError: duplicate column name: catalog_perm

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

Traceback (most recent call last):
File "/opt/superset/venv/bin/superset", line 8, in
sys.exit(superset())
File "/opt/superset/venv/lib/python3.10/site-packages/click/core.py", line 1157, in call
return self.main(*args, **kwargs)
File "/opt/superset/venv/lib/python3.10/site-packages/click/core.py", line 1078, in main
rv = self.invoke(ctx)
File "/opt/superset/venv/lib/python3.10/site-packages/click/core.py", line 1688, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/opt/superset/venv/lib/python3.10/site-packages/click/core.py", line 1688, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/opt/superset/venv/lib/python3.10/site-packages/click/core.py", line 1434, in invoke
return ctx.invoke(self.callback, **ctx.params)
File "/opt/superset/venv/lib/python3.10/site-packages/click/core.py", line 783, in invoke
return __callback(*args, **kwargs)
File "/opt/superset/venv/lib/python3.10/site-packages/click/decorators.py", line 33, in new_func
return f(get_current_context(), *args, **kwargs)
File "/opt/superset/venv/lib/python3.10/site-packages/flask/cli.py", line 358, in decorator
return __ctx.invoke(f, *args, **kwargs)
File "/opt/superset/venv/lib/python3.10/site-packages/click/core.py", line 783, in invoke
return __callback(*args, **kwargs)
File "/opt/superset/venv/lib/python3.10/site-packages/flask_migrate/cli.py", line 149, in upgrade
_upgrade(directory, revision, sql, tag, x_arg)
File "/opt/superset/venv/lib/python3.10/site-packages/flask_migrate/init.py", line 98, in wrapped
f(*args, **kwargs)
File "/opt/superset/venv/lib/python3.10/site-packages/flask_migrate/init.py", line 185, in upgrade
command.upgrade(config, revision, sql=sql, tag=tag)
File "/opt/superset/venv/lib/python3.10/site-packages/alembic/command.py", line 406, in upgrade
script.run_env()
File "/opt/superset/venv/lib/python3.10/site-packages/alembic/script/base.py", line 582, in run_env
util.load_python_file(self.dir, "env.py")
File "/opt/superset/venv/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 95, in load_python_file
module = load_module_py(module_id, path)
File "/opt/superset/venv/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 113, in load_module_py
spec.loader.exec_module(module) # type: ignore
File "", line 883, in exec_module
File "", line 241, in _call_with_frames_removed
File "/opt/superset/venv/lib/python3.10/site-packages/superset/extensions/../migrations/env.py", line 142, in
run_migrations_online()
File "/opt/superset/venv/lib/python3.10/site-packages/superset/extensions/../migrations/env.py", line 133, in run_migrations_online
context.run_migrations()
File "", line 8, in run_migrations
File "/opt/superset/venv/lib/python3.10/site-packages/alembic/runtime/environment.py", line 946, in run_migrations
self.get_context().run_migrations(**kw)
File "/opt/superset/venv/lib/python3.10/site-packages/alembic/runtime/migration.py", line 628, in run_migrations
step.migration_fn(**kw)
File "/opt/superset/venv/lib/python3.10/site-packages/superset/migrations/versions/2024-05-01_10-52_58d051681a3b_add_catalog_perm_to_tables.py", line 39, in upgrade
op.add_column(
File "", line 8, in add_column
File "", line 3, in add_column
File "/opt/superset/venv/lib/python3.10/site-packages/alembic/operations/ops.py", line 2142, in add_column
return operations.invoke(op)
File "/opt/superset/venv/lib/python3.10/site-packages/alembic/operations/base.py", line 442, in invoke
return fn(self, operation)
File "/opt/superset/venv/lib/python3.10/site-packages/alembic/operations/toimpl.py", line 171, in add_column
operations.impl.add_column(table_name, column, schema=schema, **kw)
File "/opt/superset/venv/lib/python3.10/site-packages/alembic/ddl/impl.py", line 337, in add_column
self._exec(base.AddColumn(table_name, column, schema=schema))
File "/opt/superset/venv/lib/python3.10/site-packages/alembic/ddl/impl.py", line 210, in _exec
return conn.execute(construct, params)
File "/opt/superset/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1385, in execute
return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
File "/opt/superset/venv/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py", line 80, in _execute_on_connection
return connection._execute_ddl(
File "/opt/superset/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1477, in _execute_ddl
ret = self._execute_context(
File "/opt/superset/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1953, in _execute_context
self.handle_dbapi_exception(
File "/opt/superset/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2134, in handle_dbapi_exception
util.raise(
File "/opt/superset/venv/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise
raise exception
File "/opt/superset/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
self.dialect.do_execute(
File "/opt/superset/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) duplicate column name: catalog_perm
[SQL: ALTER TABLE tables ADD COLUMN catalog_perm VARCHAR(1000)]
(Background on this error at: https://sqlalche.me/e/14/e3q8)

Checklist

• I have searched Superset docs and Slack and didn't find a solution to my problem.
• I have searched the GitHub issue tracker and didn't find a similar bug report.
• I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

[dosubot]

Hey @Jockxtar, I'm here to help you with any bugs, questions, or contributions you have in mind. Let's work together to solve this issue!

To resolve the "duplicate column name: catalog_perm" error during the upgrade, you should check if the catalog_perm column already exists in your database schema. This error occurs when the migration script tries to add a column that is already present.

Here's how you can address this issue:

1. Check Existing Columns: Manually inspect your database to see if the catalog_perm column is already present in the tables or slices tables.

2. Modify the Migration Script: If the column exists, you can modify the migration script to add the column only if it does not already exist. Here's an example modification:

   from sqlalchemy.engine import reflection
   
   def upgrade():
       conn = op.get_bind()
       inspector = reflection.Inspector.from_engine(conn)
   
       # Check if 'catalog_perm' column exists in 'tables'
       columns = [col['name'] for col in inspector.get_columns('tables')]
       if 'catalog_perm' not in columns:
           op.add_column(
               "tables",
               sa.Column("catalog_perm", sa.String(length=1000), nullable=True),
           )
   
       # Check if 'catalog_perm' column exists in 'slices'
       columns = [col['name'] for col in inspector.get_columns('slices')]
       if 'catalog_perm' not in columns:
           op.add_column(
               "slices",
               sa.Column("catalog_perm", sa.String(length=1000), nullable=True),
           )
   
       upgrade_catalog_perms(engines={"postgresql"})

This modification ensures that the column is only added if it does not already exist, preventing the duplicate column error ^[1].

To continue talking to Dosu, mention @dosu.

[geido]

Tagging @betodealmeida who might have some ideas

[dfoley84]

Also facing the Same issue --> 3.1.1 to 4.0.2
duplicated dashboards and ab_users, would be nice to have these checks pre-baked into the scripts..