java h2 in-memory database error: Table not found

Question

I tried googling around, but the solution to almost all this kind of questions was to add ;DB_CLOSE_DELAY=-1, however it does not solve anything for me.

Here is my test class

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = {Main.class})
public class Testas {

    @Autowired
    @Qualifier("managerImplementation")
    private ClassifierManager manager;

    @Test
    public void testManager(){
        ClassifierGroupEntity cge = new ClassifierGroupEntity();
        manager.saveClassifierGroup(cge);
    }
}

Manager class

@Service("managerImplementation")
public class ClassifierManagerImpl implements ClassifierManager{

    @Autowired
    private ClassifierGroupEntityRepository groupEntityRepository;

    @Autowired
    private ClassifierEntityRepository entityRepository;

    @Autowired 
    private ClassifierValueEntityRepository valueEntityRepository;

    @Override
    public ClassifierGroupEntity getClassifierGroup(long id) {
        return groupEntityRepository.findOne(id);
    }

    @Override
    public ClassifierGroupEntity getClassifierGroup(String code) {
        return groupEntityRepository.findByCode(code);
    }

    @Override
    public ClassifierGroupEntity saveClassifierGroup(ClassifierGroupEntity entity) {
        return groupEntityRepository.save(entity);
    }

    @Override
    public void deleteClassifierGroup(long id) {
        groupEntityRepository.delete(id);
    }

    @Override
    public ClassifierEntity getClassifier(long id) {
        return entityRepository.findOne(id);
    }

    @Override
    public ClassifierEntity getClassifier(String code) {
        return entityRepository.findByCode(code);
    }

    @Override
    public ClassifierEntity saveClassifier(ClassifierEntity entity) {
        return entityRepository.save(entity);
    }

    @Override
    public void deleteClassifier(long id) {
        entityRepository.delete(id);
    }

    @Override
    public ClassifierValueEntity getClassifierValue(long id) {
        return valueEntityRepository.findOne(id);
    }

    @Override
    public ClassifierValue getClassifierValue(String classifiedCode, String valueCode) {
        return null;
    }

    @Override
    public ClassifierValueEntity saveClassifierValue(ClassifierValueEntity entity) {
        return valueEntityRepository.save(entity);
    }

    @Override
    public void deleteClassifierValue(long id) {
        valueEntityRepository.delete(id);
    }


}

And finally properties file

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

Launching the test throws me

org.h2.jdbc.JdbcSQLException: Table "CLASSIFIER_GROUP_ENTITY" not found; SQL statement:
insert into classifier_group_entity (id, code, modified_details, modified_time, modified_user_id, order, revision, valid_details, valid_from, valid_till, parent_id) values (null, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [42102-191]

I don't know if I should provide anything else, please tell me if I do. I appreciate your help in advance.

This means that your H2 in memory database is not initialized. If you are using Hibernate you can use for tests? If not you could provide sql that will create your schema. — Mateusz Sroka, Apr 15 '16 at 14:01
Where do you initialise your DB? Where do you load the SQL to create the schema (and table)? — Lawrence Tierney, Apr 15 '16 at 14:10
I don't, I guess? I think spring jpa creates them for me. I mean it worked fine for me the first time I was doing it (create interface that extends CrudRepository, and then just ask for it's implementation with @Autowired). Sorry if that sounds stupid, I am fresh to all this stuff. — Coderino Javarino, Apr 15 '16 at 14:18
Somewhere in your hibernate config you need to enable ability to create schema if missing. In my hibernate.cfg.xml under /hibernate-configuration/session-factory I have: create update Which creates tables as needed. I also use @Table(name="...") on my entities so it knows what table is needed. — AlexC, Apr 15 '16 at 15:09

Coderino Javarino · Accepted Answer · 2021-07-29T08:04:04.293

25

Table was not found, because there was an error at the start when trying to create it. And the error was due to the fact, that one of the ClassifierGroupEntity fields was named 'order', which is one of reserved words in SQL, and thus the generated SQL statement by Spring was syntactically incorrect.

edited Jul 29 '21 at 08:04

answered Apr 22 '16 at 07:59

Coderino Javarino

2,819
4
21
43

Generic error. The only useful thing was something like "Invalid SQL sentence: ..." few dozens lines above. But then if you see that, I don't think you need any more googling. `Unlikely help others` How is telling people to check their field names unlikely to help others?? – Coderino Javarino Nov 05 '16 at 16:24
1

You saved me hours ... Thanks – Maelig Nov 14 '16 at 15:53
1

oh god, glad I've found your answer – Cootri Aug 15 '18 at 11:38
Thanks, mine was a mis-configuration of hibernate dialect for my test profile and hence the generated ddl statements included some mysql specific ddl statements – Lawrence Osheng Jun 29 '20 at 08:12

Ankit Bhatnagar · Answer 2 · 2017-09-04T05:45:49.273

22

Have these properties in your application.properties file in the src/test/resources folder:

spring.jpa.generate-ddl=true

spring.jpa.hibernate.ddl-auto=create

This has to be over-ridden because it's not kept create rather kept none or validate after the first ddl creation.

edited Sep 04 '17 at 05:45

answered Jun 27 '17 at 12:16

Ankit Bhatnagar

745
5
16

tried all the ways found on the internet and almost gave up. fortunately, decide to try this one as the last, and it works. thank you – frankiie Mar 26 '23 at 20:34

score 8 · Answer 3 · answered May 22 '20 at 13:12

I always use the below configuration for H2 Database, Flyway, Spring Boot JPA before writing integration tests - https://gist.github.com/vslala/d412156e5840fafa1b9f61aae5b20951

Put below configuration in your src/test/resources/application.properties file.

# Datasource configuration for jdbc h2
# this is for file based persistent storage
# spring.datasource.url=jdbc:h2:file:/data/demo

# For in-memory storage
spring.datasource.url=jdbc:h2:mem:testdb;MODE=MySQL;DB_CLOSE_DELAY=-1;IGNORECASE=TRUE;
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=vslala
spring.datasource.password=simplepass
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

# This has to be over-ridden because
# it's not kept create rather kept none or validate after the first ddl creation.
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=create

# This is for FlyWay configuration
spring.flyway.url=jdbc:h2:mem:testdb
spring.flyway.schemas=testdb
spring.flyway.user=vslala
spring.flyway.password=simplepass

Voting up! all I needed was to tell flyway to ignore case spring.flyway.url=jdbc:h2:mem:testdb;MODE=MySQL;DB_CLOSE_DELAY=-1;IGNORECASE=TRUE; — Tinkerbell, Aug 03 '20 at 08:03
@Tinkerbell yeah... that one troubled me as well in the past :D — vs_lala, Aug 04 '20 at 11:50
I was stucked in same problem from few hours but not found correct solution after searching a lot. Finally thank you for the solution. I added MODE=MySQL to make it work. — vsharma, Aug 14 '20 at 11:33

score 3 · Answer 4 · answered Jul 29 '21 at 06:38

I might be a little late to the party, but I faced exactly the same error and I tried pretty much every solution mentioned here and on other websites such as DATABASE_TO_UPPER=false;DB_CLOSE_DELAY=-1; DB_CLOSE_ON_EXIT=FALSE; IGNORECASE=TRUE

But nothing worked for me. What worked for me was renaming data.sql to import.sql

I found it here - https://stackoverflow.com/a/53179547/8219358

Or

For Spring Boot 2.4+ use spring.jpa.defer-datasource-initialization=true in application.properties (mentioned here - https://stackoverflow.com/a/68086707/8219358)

I realize other solutions are more logical but none of them worked for me and this did.

THX the import.sql did the job for me to :) – ExileNoir Nov 08 '21 at 16:50 — ExileNoir, Nov 08 '21 at 16:50

score 1 · Answer 5 · answered Mar 02 '17 at 18:29

I got this after adding a few columns to my table. The persistence generator created a few more fields in my entity.

I noticed this in my logs.

ERROR 13691 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000389: Unsuccessful: create table comment
ERROR 13691 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : Scale($"0") must not be bigger than precision("-1")

It turns out that the when my IDE's persistence generator mapped my TEXT field it gave it a length of -1.

@Basic
@Column(name = "address", nullable = true, length = -1)
public String getAddress() {
  return address;
}

Removing the length attribute solved the problem for me.

score 1 · Answer 6 · answered Dec 26 '21 at 19:08

add spring.jpa.defer-datasource-initialization=true

don't forget to add these values in application.properties/application.yaml file

spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.show-sql=true
spring.h2.console.enabled=true
spring.jpa.defer-datasource-initialization=true

score 0 · Answer 7 · answered Dec 05 '17 at 09:02

0

I encountered the same problem while I was writing unit tests for my own application. After many tries, I was not able to get my application test cases running. I was running on version 1.4.191 and then I upgraded to 1.4.196 and it started working.

answered Dec 05 '17 at 09:02

Shreyash

367
1
3
11

score 0 · Answer 8 · answered Feb 25 '19 at 19:38

0

In my case I was dumb and forgot to actually put in a spring.datasource.url

For some reason JPA doesn't throw an error if it can't find an actual database url. Weird

answered Feb 25 '19 at 19:38

Nick08

157
1
2
10

score 0 · Answer 9 · answered Nov 27 '19 at 22:16

0

In my case, I use lower case syntax for columns when the query is defined but the columns in the H2 database are defined in the upper case. I was expecting to ignore the case sensitivity but it is not the case with H2 databases.

answered Nov 27 '19 at 22:16

newday

3,842
10
54
79

score 0 · Answer 10 · edited Sep 02 '20 at 09:11

0

Please try this for this problem -

spring.datasource.url=jdbc:h2:mem:testdb;MODE=MySQL;DB_CLOSE_DELAY=-1;IGNORECASE=TRUE;

edited Sep 02 '20 at 09:11

Kamil

485
1
10
24

answered Aug 14 '20 at 11:34

vsharma

299
3
3

score 0 · Answer 11 · answered Jul 05 '22 at 13:39

0

In my case, I used liquibase to update the data. get the same error, so I used the doule quote " to wrap the table name. it worked.

answered Jul 05 '22 at 13:39

ken kang

3
1

This does not really answer the question. If you have a different question, you can ask it by clicking [Ask Question](https://stackoverflow.com/questions/ask). To get notified when this question gets new answers, you can [follow this question](https://meta.stackexchange.com/q/345661). Once you have enough [reputation](https://stackoverflow.com/help/whats-reputation), you can also [add a bounty](https://stackoverflow.com/help/privileges/set-bounties) to draw more attention to this question. - [From Review](/review/late-answers/32177739) – Japhei Jul 09 '22 at 18:49

score 0 · Answer 12 · answered Jan 17 '23 at 00:23

What I found was default springboot is adding underscore to every table name and column name when executing this sqls.

Ex - actial table name -> testTable, springboot is converting this into -> test_table when executing sql.

You can disable this default behaviour by adding following property in your application.yml

 spring:
  jpa:
    hibernate:
      naming:
        physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

java h2 in-memory database error: Table not found

12 Answers12

Linked