DataIntegrityViolationException instead of DuplicateKeyException

Question

DataIntegrityViolationException instead of DuplicateKeyException

bdeneuter opened this issue 2 years ago · 9 comments

Hi,

We are in the process of upgrading to Spring Boot 3 and see stacktraces in our logs that are caused by shedlock.
It seems that Spring throws a DataIntegrityViolationException and not a DuplicateKeyException anymore.
In our own code we made a workaround to also catch DataIntegrityViolationException and check if the message contains "Duplicate entry".

When the message of the exception contains "Duplicate entry", the exception should be logged on DEBUG level and not ERROR.

This is the stacktrace:

org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO shedlock(name, lock_until, locked_at, locked_by) VALUES(?, TIMESTAMPADD(MICROSECOND, ?, UTC_TIMESTAMP(3)), UTC_TIMESTAMP(3), ?)]; Duplicate entry 'updateAutomationCounts' for key 'shedlock.PRIMARY'
	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:117)
	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
	at net.javacrumbs.shedlock.provider.jdbctemplate.JdbcTemplateStorageAccessor.execute(JdbcTemplateStorageAccessor.java:117)
	at net.javacrumbs.shedlock.provider.jdbctemplate.JdbcTemplateStorageAccessor.insertRecord(JdbcTemplateStorageAccessor.java:68)
	at net.javacrumbs.shedlock.support.StorageBasedLockProvider.doLock(StorageBasedLockProvider.java:82)
	at net.javacrumbs.shedlock.support.StorageBasedLockProvider.lock(StorageBasedLockProvider.java:65)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
	at java.base/java.lang.reflect.Method.invoke(Method.java:578)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:211)
	at jdk.proxy2/jdk.proxy2.$Proxy215.lock(Unknown Source)
	at net.javacrumbs.shedlock.core.DefaultLockingTaskExecutor.executeWithLock(DefaultLockingTaskExecutor.java:63)
	at net.javacrumbs.shedlock.spring.aop.MethodProxyScheduledLockAdvisor$LockingInterceptor.invoke(MethodProxyScheduledLockAdvisor.java:86)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:752)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:752)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:703)
	at BatchingAutomationCounterScheduler$$SpringCGLIB$$0.updateCounts(<generated>)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
	at java.base/java.lang.reflect.Method.invoke(Method.java:578)
	at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:84)
	at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
	at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:577)
	at java.base/java.util.concurrent.FutureTask.runAndReset(FutureTask.java:358)
	at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642)
	at java.base/java.lang.Thread.run(Thread.java:1589)
Caused by: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'updateAutomationCounts' for key 'shedlock.PRIMARY'
	at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117)
	at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:555)
	at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:339)
	at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1061)
	at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1009)
	at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1320)
	at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:994)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
	at org.springframework.jdbc.core.JdbcTemplate.lambda$update$2(JdbcTemplate.java:965)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:651)
	... 34 common frames omitted

Answer 1 · 2022-12-09T16:05:38.000Z

Thanks for feedback, I have noticed that too. I am working on version 5.x.x which is built on top of Spring 6 and I have already fixed it there. I will check if it makes sense to backport it to version 4.x.x

Answer 2 · 2022-12-09T17:31:25.000Z

Thx, when 5.x.x is released we will upgrade to it

Answer 3 · 2022-12-10T20:22:40.000Z

Hi, version 5.0.0 just released, please let me know if it helped

Answer 4 · 2022-12-10T20:47:28.000Z

If you are using Oracle, it might be caused by this spring-projects/spring-framework#29673

Answer 5 · 2022-12-10T21:03:53.000Z

It's actually this spring-projects/spring-framework#29511

Answer 6 · 2022-12-10T21:06:21.000Z

I will fix it in 5.0.1

Answer 7 · 2022-12-13T19:56:39.000Z

spring-projects/spring-framework@4c69892

Answer 8 · 2022-12-15T10:30:09.000Z

Fixed in Spring 6.0.3 https://github.com/spring-projects/spring-framework/releases/tag/v6.0.3

Answer 9 · 2022-12-15T20:21:49.000Z

Still broken for MsSQL