SuWenCheng opened a new issue, #30591:
URL: https://github.com/apache/superset/issues/30591
### 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 "<string>", line 8, in create_table
File "<string>", 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.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
