search

spring-projects / spring-boot

Spring Boot helps you to create Spring-powered, production-grade applications and services with absolute minimum fuss.
https://spring.io/projects/spring-boot
Apache License 2.0
75.22k stars 40.7k forks source link

org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; when trying to delete entities #22599

Closed nikobearrr closed 4 years ago

nikobearrr commented 4 years ago

We are trying to update our springboot application from 2.1.0 to 2.3.2 All goes good except for some specific queries that we have. Our queries work up until 2.1.16, but not on 2.2.0

Our query is as following:

    @Modifying
    @Transactional
    @Query("DELETE FROM MainTable mt WHERE mt.special.id = :special_id")
    int deleteBySpecialId(@Param("special_id") Long special_id);

For springboot <= 2.1.16 that works. For springboot >= 2.2.0 it throws: org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [delete from main_table cross join special special1_ where id=?]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement

I tried to read the release notes of 2.2.0 here, but I cannot find anything related to this, thus I think it might be a bug.

When I run both versions (2.1.16 and 2.2.0) with spring.jpa.show-sql=true I can see it produces different queries:

2.1.16: Hibernate: delete from MainTable where special=? 2.2.0: Hibernate: delete from MainTable cross join Special special1_ where id=?

Minimal setup to replicate the issue is as following:

MainTable:

@Entity
@Table
public class MainTable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToOne
    @JoinColumn(name = "special")
    @NotFound(action = NotFoundAction.IGNORE)
    private Special special;

    public void setSpecial(Special special) {
        this.special = special;
    }
}

Special:

@Entity
@Table
public class Special {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String value;

    public Long getId() {
        return id;
    }
}

MainTableRepository:

@Repository
public interface MainTableRepository extends CrudRepository<MainTable, Long> {

    @Modifying
    @Transactional
    @Query("DELETE FROM MainTable mt WHERE mt.special.id = :special_id")
    int deleteBySpecialId(@Param("special_id") Long special_id);
}

SpecialRepository:

@Repository
public interface SpecialRepository extends CrudRepository<Special, Long> {
}

Test:

@RunWith(SpringRunner.class)
@DataJpaTest
public class MainTableRepositoryTest {

    @Autowired
    private MainTableRepository mainTableRepository;

    @Autowired
    private SpecialRepository specialRepository;

    @Test
    @Transactional
    @Modifying
    public void test() {
        Special special = new Special();
        MainTable mainTable = new MainTable();

        mainTable.setSpecial(special);

        mainTableRepository.save(mainTable);
        specialRepository.save(special);

        assertEquals(1, mainTableRepository.count());
        assertEquals(1, specialRepository.count());

        mainTableRepository.deleteBySpecialId(special.getId());

        Assert.isTrue(true);
    }
}

test application.properties:

spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1
spring.datasource.username=sa
spring.datasource.password=sa

pom.xml:

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.2.0.RELEASE</version>
</parent>
<dependencies>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-security</artifactId>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-actuator</artifactId>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>

    <dependency>
        <groupId>org.springframework.security</groupId>
        <artifactId>spring-security-web</artifactId>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>

    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <scope>test</scope>
    </dependency>

</dependencies>

That test passes when ran with the lower version of springboot, but fails with 2.2.0, 2.2.9, 2.3.0 and 2.3.2

This is the full exception:

org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [delete from main_table cross join special special1_ where id=?]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement

    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:280)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:254)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:528)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:153)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:149)
    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.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
    at com.sun.proxy.$Proxy130.deleteBySpecialId(Unknown Source)
    at mytestproject.MainTableRepositoryTest.test(MainTableRepositoryTest.java:43)
    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)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
    at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:33)
    at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:230)
    at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:58)
Caused by: org.hibernate.exception.SQLGrammarException: could not prepare statement
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:186)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareStatement(StatementPreparerImpl.java:81)
    at org.hibernate.hql.internal.ast.exec.BasicExecutor.doExecute(BasicExecutor.java:87)
    at org.hibernate.hql.internal.ast.exec.BasicExecutor.execute(BasicExecutor.java:59)
    at org.hibernate.hql.internal.ast.exec.DeleteExecutor.execute(DeleteExecutor.java:113)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:454)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:377)
    at org.hibernate.internal.SessionImpl.executeUpdate(SessionImpl.java:1434)
    at org.hibernate.query.internal.AbstractProducedQuery.doExecuteUpdate(AbstractProducedQuery.java:1623)
    at org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1605)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$ModifyingExecution.doExecute(JpaQueryExecution.java:238)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:88)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:154)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:142)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:618)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:605)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:353)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:99)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
    ... 38 more
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "DELETE FROM MAIN_TABLE CROSS[*] JOIN SPECIAL SPECIAL1_ WHERE ID=? "; SQL statement:
delete from main_table cross join special special1_ where id=? [42000-199]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:451)
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:427)
    at org.h2.message.DbException.get(DbException.java:205)
    at org.h2.message.DbException.get(DbException.java:181)
    at org.h2.message.DbException.getSyntaxError(DbException.java:229)
    at org.h2.command.Parser.getSyntaxError(Parser.java:989)
    at org.h2.command.Parser.prepareCommand(Parser.java:686)
    at org.h2.engine.Session.prepareLocal(Session.java:627)
    at org.h2.engine.Session.prepareCommand(Session.java:565)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1292)
    at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:77)
    at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:349)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$1.doPrepare(StatementPreparerImpl.java:90)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:176)
    ... 58 more
wilkinsona commented 4 years ago

Thanks for the detailed issue report. You are using Spring Data JPA which is managed as a separate project. They use JIRA for issue tracking. Can you please open an issue over there and comment here with a link to it so that we can follow along?

nikobearrr commented 4 years ago

Hi @wilkinsona, thanks for letting me know. I have created an issue here: https://jira.spring.io/browse/DATAJPA-1763

nikobearrr commented 4 years ago

And based on what Mark said in the JPA issue, it's not up to Spring JPA. So I have opened a new issue at hibernate here: https://hibernate.atlassian.net/browse/HHH-14122