Latest `databricks-sql-connector` causing failure with `Configuration AUTOCOMMIT is not available. SQLSTATE: 42K0I`

[kacpermuda]

Apache Airflow Provider(s)

databricks

Versions of Apache Airflow Providers

apache-airflow-providers-databricks==7.7.4

Apache Airflow version

Tested on both latest AF2 and 3

Operating System

ubuntu

Deployment

Astronomer

Deployment details

No response

What happened

databricks-sql-connector==4.2.0 released Friday November 14th introduced changes to commit/autocommit properties on Databricks Connection object (databricks/databricks-sql-python#704). Because of that changes, when using Databricks operator like DatabricksSqlOperator I'm receiving an error: databricks.sql.exc.TransactionError: Failed to set autocommit to False: [CONFIG_NOT_AVAILABLE] Configuration AUTOCOMMIT is not available. SQLSTATE: 42K0I. Regardless of whether I try to use autocommit=False or True, the problem is that the Databricks is not accepting setting this config with SQL query. The Base DbApiHook set_autocomit method is setting the autocommit on dbx conn object, before it did not have this property so it passed and was ignored, but now that's implemented, it started to fail.

What you think should happen instead

I should be able to execute query on DBX without errors.

How to reproduce

Run any query with DatabricksSqlOperator and databricks-sql-connector==4.2.0.

Anything else

Not sure if the problem is within my DBX instance, if we should somehow work around this in Airflow or maybe the changes to databricks connection are to be adjusted.

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[kmarq]

Being hit by this as well.

[tkeller-moxe]

We are also hit by this, We are just pinning databricks-sql-connector to 4.1.4 to get around this for now.
Seems like this feature is not generally available in Databricks yet?

[kacpermuda]

We are also hit by this, We are just pinning databricks-sql-connector to 4.1.4 to get around this for now.

Did the same for now.

Seems like this feature is not generally available in Databricks yet?

Maybe that's the case. Could not even find a mention about this config in databricks docs

[kacpermuda]

This is the response I got on the PR:

So it's a feature, not a bug. We can consider updating the Databricks hook code, but since it's probably going to be available soon for all, it may not be worth it. I'll close the ticket for now, temp solution is to pin the connector version and wait for this feature to be released. If somebody wants to modify the hook - feel free to reopen this ticket.

[tkeller-moxe]

Curiosity would be do we expect the hook to support older DBX runtimes without pinning the package? Seems like that might continue to be a sore point.
Thanks for aggregating the details.

[potiuk]

Curiosity would be do we expect the hook to support older DBX runtimes without pinning the package? Seems like that might continue to be a sore point.
Thanks for aggregating the details.

Gemerally you are supposed to use constraints where you install airflow and if you look at 3.1.3 constraintsL https://github.com/apache/airflow/blob/constraints-3.1.3/constraints-3.13.txt#L255 - you have 4.1.4 version of the databricks-sql-connector.

@kacpermuda -> do you think we should limit future provider to limit it unti they fix it ? It seems a bit odd to have it breaking by the client BTW. it seems still like a bug if they release client that is broken temporarily for some people - and it requries to enroll. Maybe we should limit it temporarily and open an an issue with databrick that this is not fixed and so that they let us know when it's all generaly available so that we can remove it ? WDYT?

I do not know how long it can take and how disruptive it is, but IMHO this is wrong to have something that fails if your account is not enrolled to private beta.

[kmarq]

I've raised this with our Databricks account team as we were looking at enrolling in this preview. I don't know how they'll proceed, but agreed this seems like a broken release on their part.

[potiuk]

I've raised this with our Databricks account team as we were looking at enrolling in this preview. I don't know how they'll proceed, but agreed this seems like a broken release on their part.

Thanks @kmarq ! Let us know about the outcome. We are about 2 weeks from releasing new version of providers and likely weeks away from releasing 3.1.4, so we still have time to implement a mitigation of sorts