Issue with SQLALCHEMY_TRACK_MODIFICATIONS Error when Executing SQL Query Programmatically

[darshan200]

Hi @wacken89
Hi Superset Community,

I'm encountering an issue when attempting to execute SQL queries programmatically through the /execute API in Superset. The issue is related to the SQLALCHEMY_TRACK_MODIFICATIONS setting, and I'm getting the following error:

arduino
Copy
Error during run: 'SQLALCHEMY_TRACK_MODIFICATIONS'
The relevant part of the log:

vbnet
Copy
superset_app | 2025-01-17 02:16:13,169:ERROR:superset.daos.query:Error during commit: 'SQLALCHEMY_TRACK_MODIFICATIONS'
superset_app | 2025-01-17 02:16:13,169:ERROR:superset.commands.sql_lab.execute:Error saving metadata: 'SQLALCHEMY_TRACK_MODIFICATIONS'
superset_app | 2025-01-17 02:16:13,170:ERROR:superset.commands.sql_lab.execute:Error during run: 'SQLALCHEMY_TRACK_MODIFICATIONS'
The issue occurs when making a request programmatically (not via the UI), even though I have confirmed that SQLALCHEMY_TRACK_MODIFICATIONS is set to False in the config. However, the same query works when sent from the UI.

Here’s the relevant code where the error occurs:

python
Copy
@transaction()
def run(self) -> CommandResult:
try:
query = self._try_get_existing_query()
if self.is_query_handled(query):
self._execution_context.set_query(query)
status = SqlJsonExecutionStatus.QUERY_ALREADY_CREATED
else:
status = self._run_sql_json_exec_from_scratch()

    self._execution_context_convertor.set_payload(self._execution_context, status)
    self._query_dao.save_metadata(self._execution_context.query, self._execution_context_convertor.payload)
    return {"status": status, "payload": self._execution_context_convertor.serialize_payload()}
except Exception as ex:
    raise SqlLabException(self._execution_context, exception=ex) from ex

The error happens when saving metadata with this line:

python
Copy
self._query_dao.save_metadata(self._execution_context.query, self._execution_context_convertor.payload)
When I check the logs, I see the following related error:

javascript
Copy
Error during commit: 'SQLALCHEMY_TRACK_MODIFICATIONS'
I have verified that the database connection and user context are set up correctly, and the error is still occurring for backend requests while the same request works from the UI.

Can someone please help me troubleshoot this issue? Is there something I might have missed in the configuration or context setup?

Thank you!

[dosubot[bot]]

The SQLALCHEMY_TRACK_MODIFICATIONS setting in Superset is used to enable or disable the tracking of modifications of objects and emit signals in SQLAlchemy. Setting it to False is generally recommended to avoid unnecessary overhead unless you specifically need this feature for debugging or other purposes ^[1].

Given that you've set SQLALCHEMY_TRACK_MODIFICATIONS to False in your configuration, but are still encountering this error during programmatic API requests, there are a few things you might want to check:

1. Configuration Consistency: Ensure that the configuration file being used by the Superset instance handling the programmatic requests is the same as the one used for UI requests. Sometimes, different environments or instances might have different configurations.

2. Environment Variables: Check if there are any environment variables that might be overriding the configuration settings for programmatic requests.

3. Session Management: The error might be related to how the SQLAlchemy session is being managed in the context of programmatic requests. Ensure that the session is correctly initialized and that there are no conflicts or issues with session scope.

4. Code Path Differences: Investigate if there are any differences in the code paths or execution contexts between UI-based and programmatic requests that might affect how configurations are applied.

5. Error Handling: The error message suggests an issue during the commit phase. Double-check the transaction management and error handling logic to ensure that exceptions are being caught and handled appropriately.

If these steps do not resolve the issue, consider adding additional logging around the configuration loading and session management to gain more insights into what might be causing the discrepancy.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}

[darshan200]

Hi there,

I'm encountering an issue with Superset when making backend API requests to the /execute endpoint. While everything works fine when the request is made from the UI, I get an error related to SQLALCHEMY_TRACK_MODIFICATIONS during programmatic API requests. The error message is as follows:

Error during run: 'SQLALCHEMY_TRACK_MODIFICATIONS'
I have already ensured that SQLALCHEMY_TRACK_MODIFICATIONS is set to False in the configuration. However, the error persists when making requests via the backend.

Here’s what I’ve already tried:

Ensured configuration consistency between the backend and UI requests.
Checked if any environment variables are overriding configuration settings (though I didn’t find any discrepancies).
Verified session management and ensured the session is active.
Used Flask's test_request_context to simulate backend API calls.
I’m also manually setting user_id = 1 for backend requests (to use the admin context), but still encounter the error.
I would appreciate any insights into why the backend request might behave differently and any suggestions for resolving this issue.

Thanks for your help!

[dosubot[bot]]

The SQLALCHEMY_TRACK_MODIFICATIONS setting in Superset is used to control whether SQLAlchemy tracks modifications of objects and emits signals. Setting it to False is recommended to avoid unnecessary overhead. If you're encountering an error related to this setting during programmatic API requests, but not when using the UI, here are a few additional steps you might consider:

1. Configuration Loading: Double-check that the configuration file used by the Superset instance for backend requests is indeed the same as for UI requests. Sometimes, discrepancies can occur if different configurations are loaded in different contexts.

2. Flask Application Context: Ensure that the Flask application context is correctly set up when making programmatic requests. This can affect how configurations are applied and how SQLAlchemy sessions are managed.

3. SQLAlchemy Session Management: Investigate how SQLAlchemy sessions are being managed during the programmatic requests. Ensure that the session is properly initialized and committed, and that there are no conflicts with the session scope.

4. Logging and Debugging: Increase logging around the configuration loading and session management to gather more insights. This can help identify if there are any unexpected behaviors or errors occurring during the request handling.

5. Code Path Differences: Examine if there are any differences in the code paths or execution contexts between UI and backend requests that might affect configuration application.

If these steps do not resolve the issue, consider reviewing the transaction management and error handling logic to ensure that exceptions are being caught and handled appropriately. Additionally, ensure that the SQLALCHEMY_TRACK_MODIFICATIONS setting is not being overridden elsewhere in the code or by environment variables.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}