Database not found, and IFEXISTS=true, so we cant auto-create it

Question

I am getting error after opening the h2 database console. I enter database name but it is showing database not found error:

Database "C:/Users/Barlekar/onlineshoppings" not found, and IFEXISTS=true, so we cant auto-create it [90146-199] 90146/90146 (Help)

org.h2.jdbc.JdbcSQLNonTransientConnectionException: Database "C:/Users/Barlekar/onlineshoppings" not found, and IFEXISTS=true, so we cant auto-create it [90146-199]

Try previous version [1.4.198](https://mvnrepository.com/artifact/com.h2database/h2/1.4.198). I also had the problems after updateing from [1.4.188] to [1.4.199], but [1.4.198] works fine for me. — Yuriy Balakhonov, May 21 '19 at 12:49

Ivan Xue · Answer 1 · 2019-06-11T08:18:01.123

82

If you are dealing with the Spring Boot project, please change the JDBC URL jdbc:h2:~/test to jdbc:h2:mem:testdb in the login page, which is the default URL configured by Spring Boot.

edited Jun 11 '19 at 08:18

answered Jun 11 '19 at 08:03

Ivan Xue

879
6
9

Worked like a charm! – Itay Feldman Aug 16 '19 at 16:33
Thank you, but how we can change default value of JDBC URL from `jdbc:h2:~/test` to `jdbc:h2:mem:testdb`? – chill appreciator Aug 18 '19 at 19:28
@standalone once you logged in successfully, subsequent session will be populated with last successful JDBC URL I believe, and it's populated from .h2.server.properties – ganesan arunachalam Nov 02 '19 at 06:19
1

This will create in memory database and jdbc:h2:~/test is database on filesystem making it persistent. – NK_Mimrot Aug 29 '20 at 12:12

score 27 · Answer 2 · answered Apr 16 '19 at 04:57

27

Use a pre-2019 version of the H2 database dependency that auto-creates the database every time you run your standalone application. For example version 1.4.193. Your pom.xml should include this dependency:

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

answered Apr 16 '19 at 04:57

Shant Dashjian

888
11
20

4

Problematic if you'd like to have features available on later versions. – AlikElzin-kilaka Jun 06 '19 at 07:05
3

I agree with @AlikElzin-kilaka. This cannot be the solution. I don't set the H2 version because I prefer it to be dependent on the version of Spring Boot I am using. – AnonymousAngelo Jun 26 '19 at 08:16
1

as long as i use it for learning and testing purpose answer seems to be good – Deep Roy Oct 14 '21 at 04:35

score 18 · Answer 3 · answered Feb 01 '20 at 13:46

18

Just append this to the application.properties 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

answered Feb 01 '20 at 13:46

Fahad Israr

1,041
10
9

After working and failing with the frontend (the console) and other dependency tweaks, this finally worked! – Bms bharadwaj Jan 24 '21 at 19:18

joelittlejohn · Answer 4 · 2020-05-26T12:51:00.747

17

Have you upgraded H2 recently? This could be the cause.

This is related to the following H2 commit:

https://github.com/h2database/h2database/commit/8b53f3999c6c5c3d5ca29020e2657968f4f59ec4

and the change was made because of this exploit:

https://www.exploit-db.com/exploits/45506

This means that the default for H2 is now to not auto-create databases when run in standalone network mode.

If you have read and understood the above, and you still want to allow the database to be auto-created, then just add the -ifNotExists flag to your h2 start command like this:

java -cp h2*.jar org.h2.tools.Server -ifNotExists

edited May 26 '20 at 12:51

answered Mar 27 '19 at 00:43

joelittlejohn

11,665
2
41
54

3

Please give us the solution. – Bandham Manikanta May 18 '19 at 16:16
@BandhamManikanta As I wrote in the answer, just add `-ifNotExists` to the command you use to start h2. – joelittlejohn Jun 05 '19 at 15:46
@joelittlejohn, I have tried adding `-ifNotExists` to my IntelliJ runtime configuration (under "Program arguments"), but it does not seem to work. Am I doing something wrong? – AnonymousAngelo Jun 26 '19 at 08:19
@AnonymousAngelo you can also add that configuration in your application.properties like so: spring.datasource.url=jdbc:h2:mem:testdb;IFEXISTS=false" – Ivan Jul 25 '19 at 00:48
1

@Anon567, I tried that... the only I got it to work was to downgrade to an earlier version as Shaun Dashjian mentions above. – AnonymousAngelo Jul 25 '19 at 12:14
2

@AnonymousAngelo `java -cp h2*.jar org.h2.tools.Server -ifNotExists` will do the job – shvahabi May 15 '20 at 12:50
@shvahabi: please turn your comment into an answer, you'll definitely get a "correct answer" checkmark. Please also add that this changed somewhere between 1.4.197 and 1.4.200 because I've just upgraded and couldn't create a database the way I used to. – rychu May 17 '20 at 13:47
1

@rychu since it was heavily based on current answer I decided not to give a new answer, but just edit it and gave an example to make it clear. – shvahabi May 23 '20 at 11:07

score 10 · Answer 5 · answered Aug 10 '19 at 20:53

10

i changed JDBC URL to

jdbc:h2:mem:testdb

and it's work

answered Aug 10 '19 at 20:53

osama Hassan

111
1
4

2

This should be the preferred answer as of 2020. – nocdib Jan 10 '20 at 13:29
@nocdib the most liked answer is just saying the same. So no, this answer should not even exist because it's adding no value – chill appreciator Nov 17 '21 at 12:00
simple and nice. Thanks! – samivic Feb 17 '22 at 20:32

score 6 · Answer 6 · edited Apr 18 '20 at 06:03

6

Problem:

Database not found or test folder not in c:/user/username/test

Copy&paste it in application.properties file:

spring.datasource.url=jdbc:h2:~/test
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

Issue was of URL, check it. Problem solved.

edited Apr 18 '20 at 06:03

סטנלי גרונן

2,917
23
46
68

answered Apr 17 '20 at 20:30

PRATEIK UPADHYAY

61
1
3

score 3 · Answer 7 · answered Apr 26 '20 at 21:47

enter image description here

Maven dependency

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

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

MacOS Catalina 10.15.4 Worked!

score 3 · Answer 8 · answered Aug 22 '21 at 15:31

3

Make sure you also have Spring Boot Data JPA in your project.

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

answered Aug 22 '21 at 15:31

chris

2,490
4
32
56

score 3 · Answer 9 · edited Sep 28 '21 at 01:00

3

In latest versions of H2 Database, the following screenshot might help you to login into H2 database screen. While in Application run console, we can find the JDBC URL link which is required to start the H2 database console in the browser.

edited Sep 28 '21 at 01:00

karel

5,489
46
45
50

answered Sep 27 '21 at 10:51

Charan Reddy

31
1

1

So, what the answer's author should do to solve her problem... – Roberto C. Rodriguez-Hidalgo Sep 27 '21 at 13:46

score 2 · Answer 10 · answered May 29 '22 at 07:36

2

please change the JDBC URL jdbc:h2:~/test to jdbc:h2:mem:testdb in the login page, which is the default URL configured by Spring Boot.

answered May 29 '22 at 07:36

Roshan Wickramaarachchi

411
4
7

score 1 · Answer 11 · answered Dec 03 '19 at 00:27

I found that you can add a parameter to the "-ifNotExists" start options when booting up h2 db. Programmatically, you can do it this way:

Server.createTcpServer("-tcpPort", "[PORT]", "-tcpAllowOthers", "-ifNotExists").start();

If you are using the command prompt, the same applies

Nagendar M · Answer 12 · 2021-05-19T02:59:11.623

When we try from the H2DB Console, we don't see the actual error completely.

##In Console It shows:##

>Database "mem:testdb" not found, either pre-create it or allow remote database creation 
>(not recommended in secure environments) [90149-200] 90149/90149

However, when we execute the command for opening H2 Console

>    java -cp h2-1.4.200.jar org.h2.tools.Server -ifNotExists

##It shown me the actual error which port already used#

The Web Console server could not be started. Possible cause: another server is already >running at http://191.167.0.7:8082 Exception in thread "main" org.h2.jdbc.JdbcSQLNonTransientConnectionException: Exception >opening port "8082" (port may be in use), cause: "java.net.BindException: Address already >in use: bind" [90061-200] at org.h2.message.DbException.getJdbcSQLException(DbException.java:622) at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)

##Solution: Kill the process running on the port. So that port is available to H2## > Find the pid used for the port

>>D:\H2\bin>netstat -ano | findstr :8082
>>  TCP    [::]:8082              [::]:0                 LISTENING       6376

> Kill The PID

>>D:\H2\bin>taskkill /PID 6376 /F
>>SUCCESS: The process with PID 6376 has been terminated.

> Start the H2 Console now

>java -cp h2-1.4.200.jar org.h2.tools.Server -ifNotExists

This worked for me :). What I feel is the Exception shown on the console have to be more clear to show about port issue.

Thanks

h2db in-memory-database java

Please do not use bold in all your sentences. – crg May 19 '21 at 06:11 — crg, May 19 '21 at 06:11

score 1 · Answer 13 · answered Jun 03 '21 at 19:31

1

the problem is not with the url or anything, below is the rule that we have to use in this case, –

For In-memory database:please replace the URL at H2 Console as jdbc:h2:mem:testdb
For on Disk database: use the jdbc:h2:~/testdb

answered Jun 03 '21 at 19:31

Sourav singh

49
3

score 0 · Answer 14 · edited Jul 29 '19 at 07:38

By degrading the version, the H2 DB is working but table i am unable to see. Code snippet

Controller

@RestController
public class CurrencyExchangeController {

    @Autowired
    private Environment env;
    @GetMapping("/currency-exchange/from/{from}/to/{to}")
    public CurrencyExchange retriveCurrencyExchange(@PathVariable String from,@PathVariable String to)
    {
        CurrencyExchange currencyExchange = new CurrencyExchange(1000L, from, to, BigDecimal.valueOf(65));
        currencyExchange.setPort(Integer.parseInt(env.getProperty("local.server.port")));
        return currencyExchange;

    }

POJO

@Entity
public class CurrencyExchange {
    @Id
    private Long id;
    @Column(name ="currency_from")
    private String from;
    @Column(name ="currency_to")
    private String to;
    @Column(name ="conversion_multiple")
    private BigDecimal conversion;
    private int port;

Spring boot main

@SpringBootApplication
@ComponentScan(basePackages = {"com.example"})
public class CurrencyExchangeServiceApplication {

    public static void main(String[] args) throws SQLException {
        SpringApplication.run(CurrencyExchangeServiceApplication.class, args);

    }

app.prop

spring.application.name=currency-exchange-service
server.port=8000

spring.jpa.show-sql=true
spring.h2.console.enabled=true

data.sql file
insert into currency_exchange(id,currency_from,currency_to,conversion_multiple,port)
values(1001,'USD','INR',65,0);
insert into currency_exchange(id,currency_from,currency_to,conversion_multiple,port)
values(1002,'EUR','INR',75,0);

score 0 · Answer 15 · answered Aug 22 '19 at 09:09

0

first url to jdbc:h2:mem:testdb second changed the version of Spring Boot to 2.1.3 and it works

answered Aug 22 '19 at 09:09

Василий Ковальченко

11
3

Gandikota Saikoushik · Answer 16 · 2019-09-18T22:46:52.477

I have faced this issue and resolved in the following way

To use H2 Database - Your application should be running in JDK Environment, not JRE Environment , to change please use below steps :
1. Right-click your project > properties
2. Select “Java Build Path” on left, then “JRE System Library”, click Edit…
3. Select "Workspace Default JRE"
4. Click "Installed JREs"
5. If you see JRE you want in the list select it (selecting a JDK is OK too)
6. If not, click Search…, navigate to Computer > Windows C: > Program Files > Java, then click OK
7. Now you should see all installed JREs, select the one you want
8. Click OK/Finish a million times. I have used Georgie provided steps here -> https://stackoverflow.com/a/29640138

In H2 Login console, default URL come up by Spring Boot will be jdbc:h2:~/test, it should actually match with the spring boot application properties spring.datasource.url=jdbc:h2:mem:testdb so replace the url in the login console and click on connect I have used [Ivan Xue][2] provided steps here -> https://stackoverflow.com/a/56539107
Please clear target using mvn clean command and allow project to build if doesn't enforce using maven force update snapshot or mvn install and then start your spring boot app again to see the difference

score 0 · Answer 17 · answered Sep 24 '19 at 09:02

use the below properties in the pom.xml and run the application again.

    <?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

<groupId>com.in28minutes.database</groupId>
<artifactId>database-demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>

<name>database-demo</name>
<description>Demo project for Spring Boot</description>

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.0.0.RELEASE</version>
    <relativePath/> <!-- lookup parent from repository -->
</parent>

<properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    <java.version>1.8</java.version>
</properties>

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

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

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

</dependencies>

<build>
    <plugins>
        <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
        </plugin>
    </plugins>
</build>

<repositories>
    <repository>
        <id>spring-snapshots</id>
        <name>Spring Snapshots</name>
        <url>https://repo.spring.io/snapshot</url>
        <snapshots>
            <enabled>true</enabled>
        </snapshots>
    </repository>
    <repository>
        <id>spring-milestones</id>
        <name>Spring Milestones</name>
        <url>https://repo.spring.io/milestone</url>
        <snapshots>
            <enabled>false</enabled>
        </snapshots>
    </repository>
</repositories>

<pluginRepositories>
    <pluginRepository>
        <id>spring-snapshots</id>
        <name>Spring Snapshots</name>
        <url>https://repo.spring.io/snapshot</url>
        <snapshots>
            <enabled>true</enabled>
        </snapshots>
    </pluginRepository>
    <pluginRepository>
        <id>spring-milestones</id>
        <name>Spring Milestones</name>
        <url>https://repo.spring.io/milestone</url>
        <snapshots>
            <enabled>false</enabled>
        </snapshots>
    </pluginRepository>
</pluginRepositories>

score 0 · Answer 18 · answered Nov 02 '19 at 06:21

I faced this issue recently, there were two issues.

By default, it was not populated with in-memory database rather jdbc:h2:~/test.
The db name I configured in the datasource was not test.

when I changed the JDBC url to jdbc:h2:mem:{db-name-in-config}, it worked like a charm.

score 0 · Answer 19 · answered Nov 03 '19 at 02:03

0

Check The jdbc Url used in h2 console to see if it match what you have specified

answered Nov 03 '19 at 02:03

Rohit Kumar

1

1

Welcome to SO! When you reply to a questions with many other answers, try to expose the Pros of yours. – David García Bodego Nov 03 '19 at 03:12

score 0 · Answer 20 · answered Dec 07 '19 at 10:27

0

Along with h2 dependency in POM :
<dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
    </dependency>

I added jpa, crud repository dependency in POM Like given below and It worked fine for me:

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

answered Dec 07 '19 at 10:27

Pankaj

121
7

1

This is not an answer for the question. – Javad Alimohammadi Apr 23 '20 at 21:14

score 0 · Answer 21 · answered Apr 19 '20 at 19:48

Adding to @Prateik Upadhyay's answer, if you want to rename your db connection name to something else the only thing that worked for me is below. I am using spring boot 2.2.4 version. with h2 1.4.200 version.

spring.jpa.hibernate.ddl-auto=create-drop
spring.datasource.url=jdbc:h2:~/MyComponentDB;DB_CLOSE_ON_EXIT=FALSE;AUTO_SERVER=TRUE;DB_CLOSE_DELAY=-1;MODE=Oracle
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.h2.console.enabled=true

SrinivasaRao Gurram · Answer 22 · 2021-04-15T23:29:42.187

Here the question is how to enable database creation when database not exists in given path the default start of h2 database will enable ifexists=true condition.

to avoid this scenario one has to start h2 database from terminal with following option. java -cp h2-1.4.199.jar org.h2.tools.Server -ifNotExists

once u give this option. can one can create database with in the url Many times I want to create database file in a specific folder. once u start the server with flag -ifNotExists, you can use below syntax to create database folder of your choice jdbc:h2:tcp://localhost/D:/Working/Sri/Technology/h2/data/books here books is the name of the database. if doesn't exists, it will create books data base in given folder.

score 0 · Answer 23 · answered Jul 17 '21 at 06:04

I got the same error when for the very first time I tried to open the H2 console. The best way is to, change the JDBC URL and replace it with the value that is present in the terminal, the line which says ->( H2 console available at '/h2-console'. Database available at 'JDBC URL') It is the correct way to do

score 0 · Answer 24 · answered Jun 07 '22 at 01:39

0

Please use the same URL path that is given for datasource in application.properties file datasource URL should be same as given in application.properties file

answered Jun 07 '22 at 01:39

vijaya kunwar

1
1

Database not found, and IFEXISTS=true, so we cant auto-create it

24 Answers24

Linked