Apache Superset Duplicate Referential Constraint in Oracle Database Migration Script

Bug description

I encountered an issue while using Apache Superset with an Oracle database. During the database migration process, an error ORA-02274: duplicate referential constraint specifications occurs. This error is caused by the migration script automatically generated by Superset, which includes duplicate foreign key constraints.

• Steps to Reproduce: Download and install the latest version of Apache Superset. Configure Superset to use an Oracle database. Run the database migration script.

• Expected Behavior: The migration script should create the necessary tables and constraints without errors.

• Actual Behavior: The migration script fails with the following error: File "/opt/superset/venv/lib/python3.9/site-packages/superset/migrations/versions/2015-09-21_17-30_4e6a06bad7a8_init.py", line 156, in upgrade op.create_table( File "", line 8, in create_table File "", line 3, in create_table File "/opt/superset/venv/lib/python3.9/site-packages/alembic/operations/ops.py", line 1318, in create_table return operations.invoke(op) File "/opt/superset/venv/lib/python3.9/site-packages/alembic/operations/base.py", line 442, in invoke return fn(self, operation) File "/opt/superset/venv/lib/python3.9/site-packages/alembic/operations/toimpl.py", line 143, in create_table operations.impl.create_table(table, kw) File "/opt/superset/venv/lib/python3.9/site-packages/alembic/ddl/impl.py", line 369, in create_table self._exec(schema.CreateTable(table, kw)) File "/opt/superset/venv/lib/python3.9/site-packages/alembic/ddl/oracle.py", line 54, in exec result = super()._exec(construct, args, *kw) File "/opt/superset/venv/lib/python3.9/site-packages/alembic/ddl/impl.py", line 210, in _exec return conn.execute(construct, params) File "/opt/superset/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1385, in execute return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS) File "/opt/superset/venv/lib/python3.9/site-packages/sqlalchemy/sql/ddl.py", line 80, in _execute_on_connection return connection._execute_ddl( File "/opt/superset/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1477, in _execute_ddl ret = self._execute_context( File "/opt/superset/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1953, in _execute_context self._handle_dbapi_exception( File "/opt/superset/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2134, in _handle_dbapi_exception util.raise( File "/opt/superset/venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise_ raise exception File "/opt/superset/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context self.dialect.do_execute( File "/opt/superset/venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-02274: duplicate referential constraint specifications [SQL: CREATE TABLE metrics ( id INTEGER NOT NULL, metric_name VARCHAR2(512 CHAR), verbose_name VARCHAR2(1024 CHAR), metric_type VARCHAR2(32 CHAR), datasource_name VARCHAR2(255 CHAR), json CLOB, description CLOB, PRIMARY KEY (id), FOREIGN KEY(datasource_name) REFERENCES datasources (datasource_name), FOREIGN KEY(datasource_name) REFERENCES datasources (datasource_name) )

• Example of the Problematic Migration Script: op.create_table( "metrics", sa.Column("id", sa.Integer(), nullable=False), sa.Column("metric_name", sa.String(length=512), nullable=True), sa.Column("verbose_name", sa.String(length=1024), nullable=True), sa.Column("metric_type", sa.String(length=32), nullable=True), sa.Column( "datasource_name", sa.String(length=255), sa.ForeignKey("datasources.datasource_name"), nullable=True, ), sa.Column("json", sa.Text(), nullable=True), sa.Column("description", sa.Text(), nullable=True), sa.ForeignKeyConstraint(["datasource_name"], ["datasources.datasource_name"]), sa.PrimaryKeyConstraint("id"), )

• Cause of the Issue: The issue is caused by the duplication of the foreign key constraint on the datasource_name column. Both sa.ForeignKey and sa.ForeignKeyConstraint are used, leading to the ORA-02274 error. Suggested Solution: To fix this issue, the migration script should only define the foreign key constraint once. Here is the corrected version of the script: python Copy op.create_table( "metrics", sa.Column("id", sa.Integer(), nullable=False), sa.Column("metric_name", sa.String(length=512), nullable=True), sa.Column("verbose_name", sa.String(length=1024), nullable=True), sa.Column("metric_type", sa.String(length=32), nullable=True), sa.Column( "datasource_name", sa.String(length=255), nullable=True, ), sa.Column("json", sa.Text(), nullable=True), sa.Column("description", sa.Text(), nullable=True), sa.ForeignKeyConstraint(["datasource_name"], ["datasources.datasource_name"]), sa.PrimaryKeyConstraint("id"), )

• Environment: Apache Superset version: 4.0.2 Oracle Database version: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.22.0.0.0 SQLAlchemy version: 1.4.54 Alembic version: 1.13.3 OS: Ubuntu 20.04.6 LTS (running inside Docker container)

Screenshots/recordings

No response

Superset version

master / latest-dev

Python version

3.9

Node version

16

Browser

Chrome

Additional context

No response

Checklist

• [X] 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.

Comment From: dosubot[bot]

Hey there, @SuWenCheng! I'm here to help you with any bugs, questions, or contributions you have. Let's tackle this challenge together!

A similar issue was reported and closed in the Superset repository. The problem was caused by duplicate foreign key constraints in the migration script, leading to the ORA-02274 error. The suggested solution is to ensure that the foreign key constraint is defined only once in the migration script.

Here's the corrected version of the script:

op.create_table(
    "metrics",
    sa.Column("id", sa.Integer(), nullable=False),
    sa.Column("metric_name", sa.String(length=512), nullable=True),
    sa.Column("verbose_name", sa.String(length=1024), nullable=True),
    sa.Column("metric_type", sa.String(length=32), nullable=True),
    sa.Column(
        "datasource_name",
        sa.String(length=255),
        nullable=True,
    ),
    sa.Column("json", sa.Text(), nullable=True),
    sa.Column("description", sa.Text(), nullable=True),
    sa.ForeignKeyConstraint(["datasource_name"], ["datasources.datasource_name"]),
    sa.PrimaryKeyConstraint("id"),
)

This solution removes the redundant sa.ForeignKey definition and keeps only the sa.ForeignKeyConstraint ^[1].

To continue talking to Dosu, mention @dosu.

Comment From: SuWenCheng

It seems that there are many different compatibility issues with Oracle in various migration scripts. Do I have to manually fix each one individually? Is there a better way to handle this?

Comment From: dosubot[bot]

You might not need to manually fix each issue individually. Here are some strategies to handle Oracle compatibility issues in migration scripts more efficiently:

1. Automated Testing: Set up automated tests to run migration scripts against an Oracle database. This can help identify issues early and ensure compatibility.

2. Custom Migration Scripts: Create custom migration scripts tailored for Oracle. This can be done by overriding problematic scripts with Oracle-specific logic.

3. Use SQLAlchemy and Alembic Features: Leverage SQLAlchemy's and Alembic's features to handle database-specific constraints and features. This includes using reflection and operations that are compatible with Oracle.

4. Centralize Fixes: If you encounter a common pattern causing issues, centralize the fix in a utility function or shared script that can be reused across different migrations.

5. Community Contributions: Check if there are community-contributed patches or scripts that address Oracle compatibility issues. These can often be integrated into your workflow.

By implementing these strategies, you can streamline the process and reduce the need for manual fixes.

To continue talking to Dosu, mention @dosu.

Comment From: rusackas

Are you still facing this issue? It's been dormant for >6 months, and is at risk of being closed as stale. Not many of us uses Oracle (only @sumeet-singh04 from the rolodex, so we could use help validating if this is still an issue in 4.1.2

Comment From: rusackas

Closing as inactive / not-planned