aminghadersohi opened a new pull request, #44704:
URL: https://github.com/apache/superset/pull/44704
### SUMMARY
`OracleEngineSpec` did not implement `get_cancel_query_id` / `cancel_query`.
When a user stops an Oracle query in SQL Lab, `cancel_query()` finds no cancel
handle and reports "Could not cancel query", and the statement keeps running on
the database.
This PR implements both hooks:
- `get_cancel_query_id` runs on SQL Lab's cursor before the statements and
returns `"<sid>,<serial#>,<instance>"`.
- The serial number comes from `DBMS_DEBUG_JDWP.CURRENT_SESSION_SERIAL`.
It is executable by PUBLIC, so no grant on `V$SESSION` is required.
- The instance number makes the cancel reach the right instance on RAC.
- If the session cannot be read, it returns `None` instead of failing the
query.
- `cancel_query` validates the id (`^\d+,\d+,\d+$`) and runs `ALTER SYSTEM
CANCEL SQL '<sid>, <serial#>, @<instance>'` on the new connection used for the
stop.
- The running statement fails with ORA-01013, and the session stays usable.
- Oracle requires the `ALTER SYSTEM` privilege for this, even for the
user's own sessions. Without it, the method returns `False`, so SQL Lab keeps
reporting that the query could not be cancelled rather than claiming a cancel
that did not happen.
### TESTING INSTRUCTIONS
Unit tests were added to `tests/unit_tests/db_engine_specs/test_oracle.py`:
- the id query, and `None` on failure;
- the exact cancel statement;
- `False` when the database refuses;
- malformed ids rejected without executing anything.
Verified manually against Oracle Database Free 23.26 (python-oracledb 2.4.1,
thin mode) through SQL Lab's own path: `execute_sql_statements` in a worker,
stopped via `QueryDAO.stop_query`, with a long-running cross join.
- With `ALTER SYSTEM` granted: the stop succeeds, and the worker receives
`ORA-01013` about 2 s later. `V$SESSION` no longer shows the statement active.
The query ends as `stopped`.
- Without the privilege: the stop reports that it could not cancel
(ORA-01031), and the statement is still active on the server.
- Before this change, the stop failed and the statement was still running
minutes later.
### ADDITIONAL INFORMATION
- [ ] Has associated issue:
- [ ] Required feature flags:
- [ ] Changes UI
- [ ] Includes DB Migration (follow approval process in
[SIP-59](https://github.com/apache/superset/issues/13351))
- [ ] Introduces new feature or API
- [ ] Removes existing feature or API
--
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]
