Open SuWenCheng opened 1 week ago
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.
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?
You might not need to manually fix each issue individually. Here are some strategies to handle Oracle compatibility issues in migration scripts more efficiently:
Automated Testing: Set up automated tests to run migration scripts against an Oracle database. This can help identify issues early and ensure compatibility.
Custom Migration Scripts: Create custom migration scripts tailored for Oracle. This can be done by overriding problematic scripts with Oracle-specific logic.
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.
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.
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.
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_dbapiexception
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