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 <property name="hbm2ddl.auto" value="create"/> for tests? If not you could provide sql that will create your schema. — Mateusz Sroka
– Mateusz Sroka, Commented Apr 15, 2016 at 14:01
Where do you initialise your DB? Where do you load the SQL to create the schema (and table)? — Lawrence Tierney
– Lawrence Tierney, Commented Apr 15, 2016 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
– Coderino Javarino, Commented Apr 15, 2016 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: <property name="hibernate.hbm2ddl.auto">create</property> <property name="hbm2ddl.auto">update</property> Which creates tables as needed. I also use @Table(name="...") on my entities so it knows what table is needed. — AlexC
– AlexC, Commented Apr 15, 2016 at 15:09

Coderino Javarino · Accepted Answer · 2021-07-29 08:04:04Z

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, 2021 at 8:04

answered Apr 22, 2016 at 7:59

Coderino Javarino

2,9064 gold badges24 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Coderino Javarino Over a year ago

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??

Cootri Over a year ago

oh god, glad I've found your answer

Lawrence Osheng Over a year ago

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

Ankit Bhatnagar · Accepted Answer · 2017-09-04 05:45:49Z

23

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 4, 2017 at 5:45

answered Jun 27, 2017 at 12:16

Ankit Bhatnagar

7555 silver badges16 bronze badges

2 Comments

frankiie Over a year ago

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

coconut Over a year ago

I can't believe this worked! I only added the generate-ddl part and it works.

vs_lala · Accepted Answer · 2020-05-22 13:12:38Z

8

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

answered May 22, 2020 at 13:12

vs_lala

7452 gold badges8 silver badges18 bronze badges

3 Comments

Tinkerbell Over a year ago

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;

vs_lala Over a year ago

@Tinkerbell yeah... that one troubled me as well in the past :D

vsharma Over a year ago

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.

HARSHIT BAJPAI · Accepted Answer · 2021-07-29 06:38:57Z

3

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.

answered Jul 29, 2021 at 6:38

HARSHIT BAJPAI

7698 silver badges20 bronze badges

1 Comment

ExileNoir Over a year ago

THX the import.sql did the job for me to :)

Saurabh · Accepted Answer · 2021-12-26 19:08:19Z

2

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

answered Dec 26, 2021 at 19:08

Saurabh

5186 silver badges6 bronze badges

Comments

Omoloro · Accepted Answer · 2017-03-02 18:29:53Z

1

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.

answered Mar 2, 2017 at 18:29

Omoloro

3521 gold badge3 silver badges9 bronze badges

Comments

Shreyash · Accepted Answer · 2017-12-05 09:02:30Z

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 5, 2017 at 9:02

Shreyash

3611 gold badge3 silver badges11 bronze badges

Comments

Nick08 · Accepted Answer · 2019-02-25 19:38:02Z

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, 2019 at 19:38

Nick08

1671 gold badge3 silver badges10 bronze badges

Comments

newday · Accepted Answer · 2019-11-27 22:16:26Z

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, 2019 at 22:16

newday

3,87810 gold badges58 silver badges79 bronze badges

Comments

Kamil · Accepted Answer · 2020-09-02 09:11:24Z

0

Please try this for this problem -

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

edited Sep 2, 2020 at 9:11

Kamil

6422 gold badges14 silver badges30 bronze badges

answered Aug 14, 2020 at 11:34

vsharma

3094 silver badges3 bronze badges

Comments

ken kang · Accepted Answer · 2022-07-05 13:39:12Z

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 5, 2022 at 13:39

ken kang

31 bronze badge

1 Comment

Japhei Over a year ago

This does not really answer the question. If you have a different question, you can ask it by clicking Ask Question. To get notified when this question gets new answers, you can follow this question. Once you have enough reputation, you can also add a bounty to draw more attention to this question. - From Review

DilanG · Accepted Answer · 2023-01-17 00:23:06Z

0

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

answered Jan 17, 2023 at 0:23

DilanG

1,2451 gold badge28 silver badges42 bronze badges

Collectives™ on Stack Overflow

java h2 in-memory database error: Table not found

12 Answers 12

3 Comments

2 Comments

3 Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

12 Answers 12

3 Comments

2 Comments

3 Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related