DataRetrievalFailureException when saving aggregate root using MS SqlServer [DATAJDBC-344]

[spring-projects-issues]

salim achouche opened DATAJDBC-344 and commented

Problem Description -

• Saving aggregate roots without embedded entities works fine
• The issue arises when saving an aggregate root which aggregates another entity (one-to-one containment relationship)
• Spring Data JDBC seems to behave correctly as I see the correct prepared statements (insert of root entity, get generated keys, and then insert of the aggregated entity)
• It seems to me the issue arises within the GenerateKeyHold class which is receiving a "keyList" with one entry "[{GENERATED_KEYS=null}]"
• The code tests against an empty list but not against a null GENERATED_KEYS
• The database driver correctly inserts the row but then the code tries to retrieve a generated id but it is null and hence the cast exception
• I am including sample code (entity, script-creation) and the stack trace
• Note that I didn't observe this issue when using the in-memory H2 database
• I am using spring-boot-starter-data-jdbc:jar:2.1.3.RELEASE which is pulling spring-data-jdbc:jar:1.0.5.RELEASE

 

Stack Trace -

 

{{2019-03-20 17:45:35,482 DEBUG o.s.j.c.JdbcTemplate [update:891] [main] - Executing SQL update and returning generated keys
2019-03-20 17:45:35,486 DEBUG o.s.j.c.JdbcTemplate [execute:609] [main] - Executing prepared SQL statement [INSERT INTO entity_a (field1) VALUES (?)]
2019-03-20 17:45:35,590 DEBUG o.s.j.c.JdbcTemplate [update:891] [main] - Executing SQL update and returning generated keys
2019-03-20 17:45:35,595 DEBUG o.s.j.c.JdbcTemplate [execute:609] [main] - Executing prepared SQL statement [INSERT INTO entity_b (field2, entity_a) VALUES (?, ?)]
2019-03-20 17:45:35,621 ERROR c.m.s.c.l.LogUtil [error:74] [main] - org.springframework.data.relational.core.conversion.DbActionExecutionException: Failed to execute DbAction.Insert(entity=EntityB [field2=data2], propertyPath=EntityB, dependingOn=DbAction.InsertRoot(entity=EntityA [id=6, field1=data1, entityB=EntityB [field2=data2]], generatedId=6), additionalValues={}, generatedId=null)
at org.springframework.data.relational.core.conversion.DbAction.executeWith(DbAction.java:57)
at org.springframework.data.relational.core.conversion.AggregateChange.lambda$executeWith$0(AggregateChange.java:73)
at java.util.ArrayList.forEach(ArrayList.java:1249)
at org.springframework.data.relational.core.conversion.AggregateChange.executeWith(AggregateChange.java:71)
at org.springframework.data.jdbc.core.JdbcAggregateTemplate.save(JdbcAggregateTemplate.java:104)
at org.springframework.data.jdbc.repository.support.SimpleJdbcRepository.save(SimpleJdbcRepository.java:45)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:359)
at org.springframework.data.repository.core.support.RepositoryComposition.invoke(RepositoryComposition.java:200)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:644)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:608)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lambda$invoke$3(RepositoryFactorySupport.java:595)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:595)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:59)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
at com.mapr.sky.subscriptions.repository.db.tests.persistence.basic.$Proxy72.save(Unknown Source)
at com.mapr.sky.subscriptions.repository.db.tests.persistence.basic.BasicRepoTest.crudTest(BasicRepoTest.java:42)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.springframework.test.context.junit4.statements.RunBeforeTestExecutionCallbacks.evaluate(RunBeforeTestExecutionCallbacks.java:74)
at org.springframework.test.context.junit4.statements.RunAfterTestExecutionCallbacks.evaluate(RunAfterTestExecutionCallbacks.java:84)
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:251)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
.
.
Caused by: org.springframework.dao.DataRetrievalFailureException: The generated key is not of a supported numeric type. Unable to cast [null] to [java.lang.Number]
at org.springframework.jdbc.support.GeneratedKeyHolder.getKey(GeneratedKeyHolder.java:79)
at org.springframework.data.jdbc.core.DefaultDataAccessStrategy.getIdFromHolder(DefaultDataAccessStrategy.java:323)
at org.springframework.data.jdbc.core.DefaultDataAccessStrategy.insert(DefaultDataAccessStrategy.java:111)
at org.springframework.data.jdbc.core.DefaultJdbcInterpreter.interpret(DefaultJdbcInterpreter.java:61)
at org.springframework.data.relational.core.conversion.DbAction$Insert.doExecuteWith(DbAction.java:86)
at org.springframework.data.relational.core.conversion.DbAction.executeWith(DbAction.java:55)}}

---

Affects: 1.0.5 (Lovelace SR5)

Reference URL: https://stackoverflow.com/questions/55272839/dataretrievalfailureexception-when-saving-aggregate-root-using-azure-sql-server

[spring-projects-issues]

salim achouche commented

This issue has been resolved:

• Ran the spring-data-jdbc test-suite with the MSSQL profile
• Aggregation based tests all passed
• I decided to even point to my Azure database and still the tests passed
• After debugging I realized the issue has been fixed by the following commit [DATAJDBC-258|1b5854e#diff-71d86b50e3eed6f0ef8a8725e4147fe9%5D

 

NOTE -

• The bug has been fixed by catching the Data Retrieval exception
• This means every save operation for a non-root entity will trigger an exception; this constitute a performance issue as exception handling is not cheap
• The only mitigating factors here is that a save operation requires a network call (which minimized from the exception handling penalty)
• My point, I would have preferred a cleaner fix where we handle the null-key use-case gracefully
• Please let me know what you think

[spring-projects-issues]

Jens Schauder commented

I agree that a solution without exceptions being thrown would be preferable.
There is currently some work in progress for introducing dialects. 
Once these are in place we probably can add an attribute there to denote the way generated keys have to get handled

[schauder]

As commented by salim achouche this issue has been resolved.