Duplicate Referential Constraint in Oracle Database Migration Script

[SuWenCheng]

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

• 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 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.

[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?

[dosubot]

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.