The Problem:
When attempting to run a test on a UserRepository, an org.springframework.dao.InvalidDataAccessResourceUsageException is encountered. The specific error is error performing isolated work; SQL [n/a]. The tests use H2 as an in-memory database, with H2 dependency configured as test-scoped, and spring.jpa.hibernate.ddl-auto=create-drop is set in the application.properties for testing, yet the exception persists.
The Solutions:
Solution 1: Rename table and use appropriate dialect
The issue is caused by the table name starting with an underscore, which is not allowed in SQL. Additionally, the hibernate dialect is set to MySQLDialect, but H2 is being used as the database. It should be set to H2Dialect instead.
Steps to resolve the issue:
- Rename the table in the @Table annotation to something that does not start with an underscore, such as "user".
- Change the hibernate dialect in the application.properties file to org.hibernate.dialect.H2Dialect.
@Table(name = "user")
...
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2Dialect
Q&A
What is the cause of the error org.springframework.dao.InvalidDataAccessResourceUsageException: error performing isolated work; SQL [n/a]?
It seems that the creation of the sequence _USER_SEQ is failing.
What is the solution for the error when using org.springframeowork.data and h2 in spring boot tests?
Changing the table name from _user to something without an underscore like user or app_user should solve the issue.
What is wrong with the code below?, why it cause an exceptions during spring boot tests?
Using org.hibernate.dialect.MySQLDialect with H2 database is wrong. Use the appropriate dialect for your H2 database like org.hibernate.dialect.H2Dialect.