Warning when upgrading DB

Bug description

Installed superset using conda and, while running superset db upgrade I get some warnings:

envs/superset/lib/python3.11/contextlib.py:144: SAWarning: WARNING: SQL-parsed foreign key constraint '('datasource_name', 'datasources', 'datasource_name')' could not be located in PRAGMA foreign_keys for table metrics
  next(self.gen)
envs/superset/lib/python3.11/site-packages/superset/migrations/versions/2024-01-17_13-09_96164e3017c6_tagged_object_unique_constraint.py:58: SAWarning: WARNING: SQL-parsed foreign key constraint '('database_id', 'dbs', 'id')' could not be located in PRAGMA foreign_keys for table tab_state
  metadata.reflect(bind=bind)
envs/superset/lib/python3.11/site-packages/superset/migrations/versions/2024-01-17_13-09_96164e3017c6_tagged_object_unique_constraint.py:58: SAWarning: WARNING: SQL-parsed foreign key constraint '('latest_query_id', 'query', 'client_id')' could not be located in PRAGMA foreign_keys for table tab_state
  metadata.reflect(bind=bind)
envs/superset/lib/python3.11/site-packages/superset/migrations/versions/2024-01-17_13-09_96164e3017c6_tagged_object_unique_constraint.py:58: SAWarning: WARNING: SQL-parsed foreign key constraint '('database_id', 'dbs', 'id')' could not be located in PRAGMA foreign_keys for table table_schema
  metadata.reflect(bind=bind)

How to reproduce the bug

micromamba env create --name superset -c conda-forge superset pillow psycopg2-binary
export FLASK_APP=superset
export SUPERSET_CONFIG_PATH=/var/superset/config.py (the same as in here)
superset db upgrade

Screenshots/recordings

No response

Superset version

4.0.1

Python version

3.11

Node version

I don't know

Browser

Firefox

Additional context

No response

Checklist

[X] I have searched Superset docs and Slack and didn't find a solution to my problem.
[X] I have searched the GitHub issue tracker and didn't find a similar bug report.
[X] 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.

Hey there, @fgvieira! I'm here to assist you with any bugs, questions, or contributions you might have for the repository. Let me know how I can help you!

To resolve the warnings encountered when running superset db upgrade after installing Superset using conda, you can follow the steps outlined in the migration script provided. The script ensures that any duplicates are removed and a unique constraint is created on the tagged_object table.

Here is the relevant code from the migration script:

def upgrade():
    bind = op.get_bind()  # Get the database connection bind

    # Reflect the current database state to get existing tables
    metadata.reflect(bind=bind)

    # Delete duplicates if any
    min_id_subquery = (
        select(
            [
                func.min(tagged_object_table.c.id).label("min_id"),
                tagged_object_table.c.tag_id,
                tagged_object_table.c.object_id,
                tagged_object_table.c.object_type,
            ]
        )
        .group_by(
            tagged_object_table.c.tag_id,
            tagged_object_table.c.object_id,
            tagged_object_table.c.object_type,
        )
        .alias("min_ids")
    )

    delete_query = tagged_object_table.delete().where(
        tagged_object_table.c.id.notin_(select([min_id_subquery.c.min_id]))
    )

    bind.execute(delete_query)

    # Create unique constraint
    utils.create_unique_constraint(op, index_id, table_name, uix_columns)

This script performs the following actions:

Reflects the current database state to get existing tables.
Deletes any duplicate entries in the tagged_object table.
Creates a unique constraint on the tagged_object table to prevent future duplicates.

Ensure that you have the migration_utils module available and properly configured, as it is used to create and drop the unique constraint.

Additionally, after running the migration script, you should follow the standard procedure for upgrading Superset's metadata database:

superset db upgrade
superset init

While upgrading Superset should not delete your charts and dashboards, it is recommended to backup your metadata database before upgrading. For production environments, consider upgrading in a staging environment first and then upgrading production during off-peak hours.

Refer to the UPDATING.md file for any potential downtime or breaking changes that might affect your deployment. For example, adding an index to query.sql_editor_id or updating database columns to use the MediumText type might require some downtime on large deployments. Always review the latest updates and changes to ensure a smooth upgrade process ^[1]^[2]^[3].

To continue talking to Dosu, mention @dosu.

apache / superset