Spring 6: Unexpected exception DataIntegrityViolationException logged at 1st attempt to get the lock (if it already exists in DB)

[multanis63]

1. Which version do you use: 5.2.0
2. Which Lock Provider: shedlock-provider-jdbc-template (oracle)
3. ShedLock configuration: -
4. ShedLock logs:

2023-04-11T09:28:22.241+02:00 ReferenceDataApplication ERROR 12824 --- [ scheduling-1] n.j.s.p.j.JdbcTemplateStorageAccessor : Unexpected exception org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO shedlock(name, lock_until, locked_at, locked_by) VALUES(?, SYS_EXTRACT_UTC(SYSTIMESTAMP) + ?, SYS_EXTRACT_UTC(SYSTIMESTAMP), ?)]; ORA-00001: unique constraint (NDANIELS.SYS_C009862) violated at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:87) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70) at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1539) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:960) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:981) at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:328) at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:333) at net.javacrumbs.shedlock.provider.jdbctemplate.JdbcTemplateStorageAccessor.lambda$execute$0(JdbcTemplateStorageAccessor.java:119) at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) at net.javacrumbs.shedlock.provider.jdbctemplate.JdbcTemplateStorageAccessor.execute(JdbcTemplateStorageAccessor.java:119) at net.javacrumbs.shedlock.provider.jdbctemplate.JdbcTemplateStorageAccessor.insertRecord(JdbcTemplateStorageAccessor.java:68) at net.javacrumbs.shedlock.support.StorageBasedLockProvider.doLock(StorageBasedLockProvider.java:79) at net.javacrumbs.shedlock.support.StorageBasedLockProvider.lock(StorageBasedLockProvider.java:62)

Expected behavior
No error log at startup is lock already exists in DB

Actual behavior
After migration to Spring 6, Shedlock started printing this error on each startup. This is because Spring 6 changed the default exception translator (SQLExceptionSubclassTranslator in the stack above instead of SQLErrorCodeSQLExceptionTranslator).
From the Spring migration doc:
"Spring's default JDBC exception translator is the JDBC 4 based SQLExceptionSubclassTranslator now. SQLErrorCodeSQLExceptionTranslator kicks in for user-provided sql-error-codes.xml files still. It can pick up Spring's legacy default error code mappings as well when triggered by a (potentially empty) user-provided file in the root of the classpath, or by explicit SQLErrorCodeSQLExceptionTranslator setup."

We could add an empty 'error code mapping' but I guess it would be better that Shedlock doesn't print such error by default. (put the log to debug as for DuplicateKeyException ?)

[lukas-krecan]

Seems to be related to #1272 Which version of Spring Boot do you use? I recommend upgrading to the latest one.

[multanis63]

Ha indeed it seems the same issue, sorry I didn't find it.
I'm using spring boot 3.0.5, which is the latest version.

Unfortunately, spring-boot 3.0.5 still uses Spring 6.0.2...

I guess this bug can be closed then. I can live with that until next spring-boot release.

Thanks a lot for your fast answer !

[lukas-krecan]

You can downgrade ShedLock to 5.1.0 (it contains workaround)

[multanis63]

Perfect I'll use 5.1.0 in the meantime.

Many thanks again !