Spring Add Databricks to sql-error-codes

I would like to use Databricks directly for the SQL error codes without creating a custom file: https://github.com/spring-projects/spring-framework/blob/main/spring-jdbc/src/main/resources/org/springframework/jdbc/support/sql-error-codes.xml

The SQL code states are declared here:

https://docs.databricks.com/aws/en/error-messages/sqlstates

Comment From: jhoeller

Note that we actually use SQLExceptionSubclassTranslator by default now, ignoring our internally shipped sql-error-codes.xml file. SQLErrorCodeSQLExceptionTranslator only kicks in for a user-declared sql-error-codes.xml file in the root of the classpath.

So our default mapping file is effectively legacy. From that perspective, there is limited value in us extending that default sql-error-codes.xml with a further database. This would not apply on its own anymore but just as a base definition for a user-declared file. In other words, you will still have to create a custom file in the root of the classpath (it may remain empty though if all you want to is to rely on the default sql-error-codes.xml mapping in spring-jdbc.jar).

Is there specific error detection that you would expect for Databricks that you do not get out of SQLException subclass analysis already? Additionally, SQLExceptionSubclassTranslator is standards-based but has a couple of extra checks for common cannot-acquire-lock and duplicate-key error codes as well. This is usually sufficient for common exception differentiation purposes, with no actual need for SQL error code mappings for a specific database.

As far as I can tell, Databricks supports 23505 for duplicate-key scenarios which our SQLExceptionSubclassTranslator detects already. And Databricks does not have any specific locking error codes, so there is nothing to specifically detect in that area.

Comment From: MelleD

Thanks for the quick explanation and detailed answer. I haven't had an SQL translator yet, but I discovered the file and wondered if I needed to handle it for Databricks.

I saw also this here :).

    public SQLErrorCodes getErrorCodes(String databaseName) {
        Assert.notNull(databaseName, "Database product name must not be null");

        SQLErrorCodes sec = this.errorCodesMap.get(databaseName);
        if (sec == null) {
            for (SQLErrorCodes candidate : this.errorCodesMap.values()) {
                if (PatternMatchUtils.simpleMatch(candidate.getDatabaseProductNames(), databaseName)) {
                    sec = candidate;
                    break;
                }
            }
        }
        if (sec != null) {
            checkCustomTranslatorRegistry(databaseName, sec);
            if (logger.isDebugEnabled()) {
                logger.debug("SQL error codes for '" + databaseName + "' found");
            }
            return sec;
        }

        // Could not find the database among the defined ones.
        if (logger.isDebugEnabled()) {
            logger.debug("SQL error codes for '" + databaseName + "' not found");
        }
        return new SQLErrorCodes();
    }

I would use the default SQLErrorCodes for now and create a ticket if a special case occurs again.

Comment From: MelleD

@jhoeller maybe i figured out the issue. Iam using jOOQ and this is directly using only the sql state error translator for Databricks, because the db name is null therefore https://github.com/spring-projects/spring-boot/blob/main/spring-boot-project/spring-boot-autoconfigure/src/main/java/org/springframework/boot/autoconfigure/jooq/DefaultExceptionTranslatorExecuteListener.java#L116

Maybe this looks like an bug, because also when the db name is not provided it should be using the the default SQLErrorCodeSQLExceptionTranslator which has an fallback to SQLExceptionSubclassTranslator. And SQLExceptionSubclassTranslator has an fallback then to SQLStateSQLExceptionTranslator and then it could work with the complete fallback chain.

Also the javadoc gives the hin "This translator is commonly used as a fallback behind a primary translator such as SQLErrorCodeSQLExceptionTranslator or SQLExceptionSubclassTranslator. "

So IMHO this line should changed to:

        return (dbName != null) ? new SQLErrorCodeSQLExceptionTranslator(dbName)
                    : new SQLErrorCodeSQLExceptionTranslator();

What do you think?

and maybe the default contructor could provide a empty new SQLErrorCodes() if it is null, or this should also be handled in the auto config

Comment From: jhoeller

@MelleD I wasn't ware that there was such a special error translation setup for JOOQ. Indeed, that line in Spring Boot's auto-configured translator should change as follows, matching the core Spring fallback chain:

return (dbName != null) ? new SQLErrorCodeSQLExceptionTranslator(dbName) : new SQLExceptionSubclassTranslator();

Without a database name given, SQLErrorCodeSQLExceptionTranslator itself cannot perform any meaningful lookup, so it effectively falls back to SQLExceptionSubclassTranslator. For that reason, the code should choose that fallback directly like core Spring does.

FWIW we used to fall back to SQLStateSQLExceptionTranslator in former times but changed this to SQLExceptionSubclassTranslator a few years ago for all core Spring JDBC exception translation setups.

Please report the suggestion above to the Spring Boot project: https://github.com/spring-projects/spring-boot/issues