H2 Database not found error: 90146. H2 database is not created on start

Question

Just created a simple spring-boot project from the spring initializer. I went to add a local h2 db for testing and am unable to login. Seems that it cannot create the test db when starting up but cannot figure out why this may be the case.

spring:
  h2:
    console:
      enabled: true
      path: /h2
  datasource:
    url: jdbc:h2:mem:testdb;
    username: sa
    password:
    driver-class-name: org.h2.Driver
    platform: h2
  jpa:
    show-sql: true
    hibernate:
      ddl-auto: create
    properties:
      hibernate:
        dialect=org:
          hibernate:
            dialect:
              H2Dialect: org.hibernate.dialect.H2Dialect

Database "mem:testdb" not found, and IFEXISTS=true, so we cant auto-create it [90146-199] 90146/90146

Commutate

Not sure if it matters but you got a semicolon in the datasource url – Mistiemistime

Answer 1

In earlier version of spring, default url will be jdbc:h2:mem:testdb and testdb was created by default. from 2.3.0 onwards, if url is not mentioned it will auto generate database name. auto generated database name can be found in the spring logs.

Tanishatanitansy

for me it is the twelfth message from the top (in the console). Message is as follows: o.s.b.a.h2.H2ConsoleAutoConfiguration : H2 console available at '/h2-console'. Database available at 'jdbc:h2:mem:36963133-9e0a-40bf-bac6-ed118afb3a97' I just copied and pasted the location into the console and connected. – Virility

Great tip! I found it in the log as well. – Trioecious

Answer 2

I had the same error and I found these to be helpful:

Adding a pre-2019 version to the pom.xml file as below:

<dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <version>1.4.193</version>
</dependency>

This fixes the error but isn't the right way to do it. The newer version of H2 Database do not create a new database as it doesn't exist by default and are enabled to false for security purposes.

A better way would be adding making changes to url as:

jdbc:h2:mem:testdb;IFEXISTS=FALSE;

Hope it helps. I made changes in my application.properties file.

Spore

URL changing method worked for me. Thank you very much – Aweinspiring

How to access from h2-console? – Blida

The console works fine after change from 2.4.2 to 2.4.193 or change url to jdbc:h2:mem:testdb with 2.4.2, also work fine for me. – Johnathanjohnathon

Answer 3

just append this to your application.properties file.

spring.datasource.url=jdbc:h2:mem:testdb
spring.data.jpa.repositories.bootstrap-mode=default

Apposition

Answer 4

As Stuck said.

Simply remove the semicolon:

wrong:    jdbc:h2:mem:testdb;
correct:  jdbc:h2:mem:testdb

Amiss

Answer 5

I have the same problem with windows. I just change spring.datasource.url in application property file to jdbc:h2:file:C:/data/sample and run project. after that set JDBC URL to jdbc:h2:file:C:/data/sample. look like picture JDBC URL

Duodenum

Answer 6

I had a same problem and I changed the version of Spring Boot to 2.1.3 and it works

Six

Answer 7

wrong:

spring.datasource.url=jdbc:h2:mem:testdb

This below solution worked for me

spring.datasource.url=jdbc:h2:file:~/test;

Helotism

Answer 8

If you are using spring boot in order to use h2 DB, make sure you have dependencies on your pom.xml file

<dependency>
     <groupId>org.springframework.boot</groupId>
     <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
     <groupId>com.h2database</groupId>
     <artifactId>h2</artifactId>
     <scope>runtime</scope>
</dependency>

Your application.yml makes it possible to access h2 DB by URI /h2-console preceded by server URL and to connect to DB named "testdb" with username "sa" and no password, after the application has been started on the server.

Capital

Answer 9

maybe you have to setting about h2.

In application.properties

spring.h2.console.enabled=true
spring.h2.console.settings.web-allow-others=true
spring.datasource.generate-unique-name = false 
spring.h2.console.path=/h2-console
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver

when you put these codes in Application.properties you can see how it work!

Illogicality