springboot 2.3.0 while connecting to h2 database

Question

In Springboot 2.3.0.RELEASE I am getting the the following error while connecting to h2 database in the console

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

Can you show us the full string starting with jdbc: ? You seem to do it right but maybe a typo ? It should be: jdbc:h2:mem:test_mem , and the fact it tells you it's a remote db is suspicious. — Pierre, May 18 '20 at 08:31
H2 Console from third-party projects doesn't allow database creation any more due to security reasons and shows such error message, there is nothing suspicious. But specified JDBC URL should point to existing database created by application. — Evgenij Ryazanov, May 18 '20 at 10:55

score 70 · Answer 1 · answered May 29 '20 at 19:48

70

You can fix this by setting the spring.datasource.url property like so:

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

Prior to Spring Boot 2.3.0-RELEASE this was the default, but I'm not sure where it's set. As of 2.3.0-RELEASE, the schema looks to be a randomly generated GUID.

answered May 29 '20 at 19:48

chacewells

891
5
4

Thank you very much Chacewells. Its working fine with your guidance. – ramesh b May 31 '20 at 07:25

score 42 · Answer 2 · edited Jun 17 '21 at 12:52

42

Actually, your h2 databse is looking for a file called test.mv.db. But that file was not present in your user directory. So, that it just slapping you and asking you to pre-create it in that path.

Note: That is the root file where our H2 DB store all our information.

Add below line in your application.properties file
spring.datasource.url = jdbc:h2:mem:testdb
Go to your user directory, in my case, it is (C:\Users\subra)
Create a new file called test.mv.db and saved it under all file option like below.
Now restart your app.
Done

Reference Screenshot:

My Problem:

My Problem

The Result

edited Jun 17 '21 at 12:52

gurkan

884
4
16
25

answered Jun 12 '21 at 16:38

Subramanian

421
4
3

> So, that it just slapping you and asking you to pre-create it in that path. It's asking him to create the database mem:testdb, not a file test.mv.db. Why? I had the same issue and when I created this file, it worked! – BovineScatologist Aug 10 '22 at 14:32
Extremely clear and helpful! – user8779054 Oct 14 '22 at 08:48
thanks, this was exactly my issue. In Ubuntu, going to /home/ directory and creating the file fixed the issue – ABHINEET SINGH Jan 27 '23 at 03:07
Awesome. The file is "testdb.mv.db" if connect to jdbc:h2:~testdb – Ishmael Mavor Raines Jul 20 '23 at 17:07
Thank you. This saved my time. Perform the same if you are using the Mac too, and the issue will be resolved. – aevin Aug 14 '23 at 11:47

Yoshita Mahajan · Answer 3 · 2020-09-22T08:36:48.107

35

Step 1. In application.properties:

spring.h2.console.enabled=true
spring.datasource.url=jdbc:h2:mem:testdb

Step 2. Start your Spring Boot App and open:

http://localhost:8080/h2-console/

If you still face issue try pasting the URL value which you mentioned in application.properties jdbc:h2:mem:testdb in

JDBC URL of h2-console

Then you wont face below mentioned issue Database h2 not found, either pre-create it or allow remote database creation (not recommended in secure environments) [90149-200] 90149/90149 (Help)

edited Sep 22 '20 at 08:36

answered Sep 22 '20 at 07:45

Yoshita Mahajan

443
4
6

2

I don't understand , "try pasting the URL", can you provide an example? JDBC URL of h2-console – mattsmith5 Apr 29 '21 at 23:10
@mattsmith5 op probably meant that paste the URL(jdbc:h2:mem:testdb) to the console's `JDBC URL` section. – Sep 05 '21 at 00:05

kanik · Answer 4 · 2022-11-04T12:05:46.143

25

You are not able to connect to database because you are using old JDBC URL. Every time you start a spring project, JDBC URL changes as well.

Solution: Copy the JDBC URL from console every time you want to connect to a database or just use below property in application.properties file spring.datasource.url=jdbc:h2:mem:testdb

edited Nov 04 '22 at 12:05

answered Sep 18 '20 at 06:55

kanik

381
3
6

Yes, this the main reason of my problem. And setting a name prevents this action repeating again and again – Onur Demir Nov 08 '20 at 13:33

score 15 · Answer 5 · answered Dec 11 '20 at 14:38

15

Another reason for the error could be a the missing JDBC dependency:

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

I got the exact same error and this was the issue in my case.

answered Dec 11 '20 at 14:38

Krangerich

293
3
6

1

After adding this dependency I was able to see in app log the database url. Thanks! – Dane411 Dec 19 '20 at 02:34
yes, it works we also need jdbc to connect – Long tran Aug 23 '23 at 01:27

score 6 · Answer 6 · answered Aug 09 '20 at 18:35

In Spring Boot 2.3.0 the property spring.datasource.generate-unique-name is set to true. This enables the random generation of the schema name.

By setting it to false the database is generated as in previous versions (jdbc:h2:mem:testdb). I find this way preferable, without the need to specify the URL manually.

score 6 · Answer 7 · edited Sep 06 '20 at 14:45

6

Create a file with a .mv.db extension in your project's folder. Make sure that the file's name ends with .mv.db otherwise H2 console does not recognize the file (I know it doesn't make sense and I have no explanation why :D). After creating the file, enter jdbc:h2:./devdb to h2 console's JDBC URL like this:

Check this image out

Then you should be OK!

edited Sep 06 '20 at 14:45

SHR

7,940
9
38
57

answered Sep 06 '20 at 14:26

thekirmizi

89
1
2

Johnny155 · Answer 8 · 2020-08-13T07:55:04.613

3

Step 1. Download h2 database engine from here:

https://www.h2database.com/html/main.html

Step 2. In application.properties:

spring.h2.console.enabled=true

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

Step 3. Start your Spring Boot App and open:

http://localhost:8080/h2-console/

It must be working fine for you.

edited Aug 13 '20 at 07:55

answered Aug 13 '20 at 07:41

Johnny155

51
6

score 3 · Answer 9 · edited Oct 06 '21 at 04:19

3

Use h2 dependency with the old version (1.4.192).

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

edited Oct 06 '21 at 04:19

Tyler2P

2,324
26
22
31

answered Oct 05 '21 at 17:21

NAVED HASHMY

31
1

score 3 · Answer 10 · edited Oct 28 '21 at 06:28

3

I added "test.mv.db" file into "C:\Users\YourUserName" then restart the server and it's worked.

edited Oct 28 '21 at 06:28

Suraj Rao

29,388
11
94
103

answered Oct 28 '21 at 06:11

Cagdas Karabulut

31
3

works perfictly fine ! – roudlek Feb 17 '23 at 17:34

score 2 · Answer 11 · answered Sep 04 '20 at 00:42

2

Since 2.3.0, default value of generate-unique-name is true.

You can revert to the previous behavior by following setting:

spring.datasource.generate-unique-name=false

see also: https://stackoverflow.com/a/62750194/4506703

answered Sep 04 '20 at 00:42

DEWA Kazuyuki - 出羽和之

2,453
14
57

score 2 · Answer 12 · edited Jan 27 '21 at 12:34

Even I came across the same set of errors when started learning data persistence with h2 database. The solution is in the console log itself. The database name is auto-generated by and can be found in the spring logs. The reason behind the error is after from 2.3.0 version release onwards if the URL is not mentioned its auto-generated.

Here is the log message which includes database URL: INFO 16096 --- [ restartedMain] o.s.b.a.h2.H2ConsoleAutoConfiguration : H2 console available at '/h2-console'. Database available at 'jdbc:h2:mem:f1dfeade-60dd-4809-8978-42547353b2f3'

score 2 · Answer 13 · edited Nov 15 '21 at 22:30

2

Add property to application.properties:

spring.datasource.generate-unique-name=false

I had also missed adding the following dependencies to pom.xml:

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

edited Nov 15 '21 at 22:30

Dmitry Rakovets

557
1
6
15

answered Aug 20 '21 at 16:56

Tharindu Krishan

117
1
7

The missing part was we must beed to add JPA dependency – PAA Jul 06 '22 at 06:58

Imane Erguiti · Answer 14 · 2021-09-12T22:46:34.370

2

I had the same problem and I got it solved this way

Go to application.properties
Add the following :

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

now you can use sa for username and password for password

PS : that's usually the by default configuration that spring boot uses

edited Sep 12 '21 at 22:46

answered Sep 12 '21 at 22:39

Imane Erguiti

107
1
4

This solution worked for me, but after set the password in the h2 console – Gerson C Filho Jan 21 '23 at 13:00

score 1 · Answer 15 · answered Apr 03 '21 at 09:06

In case this helps anybody. My issue was because I have this in my application.properties

spring.autoconfigure.exclude=org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration

Not sure why I added this config before. But once this is taken out, it solves my issue

score 1 · Answer 16 · answered Jul 15 '21 at 08:19

Adding Spring-boot started JDBC solved my issue.

https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-jdbc/2.5.2

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
    <version>2.5.2</version>
</dependency>

score 1 · Answer 17 · answered Jul 27 '22 at 08:06

It's error of H2-Database:

Solution -

Let's suppose ~/Test-H-2/Project-X/DB-files path of your project (Database files)

create myTest.mv.db file in ~/Test-H-2/Project-X/DB-files folder
Add jdbc:h2:~/Test-H-2/Project-X/DB-files/myTest to JDBC URL in H2-Database console

OR

simply you can create ~\test.mv.db file & add jdbc:h2:~/test in to JDBC URL in H2-Database console

score 1 · Answer 18 · answered Jan 08 '23 at 11:46

1

Remove the spring-boot-devtools dependency from pom.xml and restart the application. for me, this worked.

answered Jan 08 '23 at 11:46

Dhruv Bhavsar

11
1

Happened to me as well. – Mohit Sehgal May 05 '23 at 19:08

score 1 · Answer 19 · answered Mar 07 '23 at 11:01

1

copy the jdbc url --> jdbc:h2:mem:test like below. then your problem will be solved.

h2-console

answered Mar 07 '23 at 11:01

Prasad Matha

11
1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Mar 17 '23 at 03:43

score 0 · Answer 20 · answered Jul 10 '20 at 05:34

i had the same problem while creating schema for h2 database in spring version 2.3.0 ...sometimes the database would connect but could not show me my structure of table so i tried chaneging the spring version and it worked like an magic try changeing spring version to 2.1.15.RELEASAE

score 0 · Answer 21 · edited May 30 '21 at 15:26

Replace the default JDBC url of h2-console from application.properties to use JDBC Url=jdbc:h2:mem:testdb Below is the application.properties

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

score 0 · Answer 22 · answered May 28 '21 at 13:38

0

I had same issue in windows 10. Try to replace

spring.datasource.url=jdbc:h2:~/testdb

with

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

answered May 28 '21 at 13:38

egemen

779
12
23

score 0 · Answer 23 · answered Jul 05 '21 at 08:01

0

Add these two properties in application.property file

spring.h2.console.enabled=true
spring.datasource.url=jdbc:h2:mem:testdb

type url: localhost:<portnumber>/h2-console in browser You will get a page regrading login database Remove existing JDBC url in the page and put this jdbc:h2:mem:testdb

answered Jul 05 '21 at 08:01

rrlinus

39
2

This seems to be the same answer as: https://stackoverflow.com/a/64005228/4214684 – RCB Jul 05 '21 at 12:23

score 0 · Answer 24 · answered Jul 18 '21 at 11:15

0

I connect h2 by jdbc:h2:mem:testdb but not jdbc:h2:~/testdb

answered Jul 18 '21 at 11:15

qqqqDonl

23
7

score 0 · Answer 25 · answered Dec 20 '21 at 09:45

my solution for this problem is:

in case you you didn't made a database folder in home directory (in windows under the: C:\Users*USERNAME\test* || in Linux: under the: ~/test) make it and add below lines to application.properties:

spring.jpa.hibernate.ddl-auto=create-drop
spring.datasource.url=jdbc:h2:~/test
spring.jpa.hibernate.ddl-auto=create-drop

it should help you to login.

((use blank username and password for H2))

oldium · Answer 26 · 2022-03-05T19:48:08.257

Adding to answers, which mention auto-generated database name in Spring Boot 2.3+ – this is the way how to get the generated name into H2 Console programatically in Spring Boot, so that you can keep the generated database name. It basically gets the first H2 database source and updates/generates the H2 Console configuration file ~/.h2.server.properties, which is then loaded by H2 Console when it is first accessed.

Configure pom.xml to use H2 types directly:

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

Enable H2 Console in application.properties (true is a default value):

spring.h2.console.enabled=true

Code to use auto-generated database name:

import java.io.OutputStream;
import java.sql.Connection;
import java.util.List;
import java.util.Objects;
import java.util.Properties;
import java.util.stream.Collectors;

import javax.sql.DataSource;

import org.h2.engine.Constants;
import org.h2.store.fs.FileUtils;
import org.h2.util.SortedProperties;
import org.springframework.beans.factory.ObjectProvider;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.context.annotation.Configuration;

@Configuration
@AutoConfigureAfter(DataSourceAutoConfiguration.class)
public class H2ConsoleDatabaseAutoConfigurator {

  @Autowired
  public void configure(ObjectProvider<DataSource> dataSource) throws Exception
  {
    Properties properties = SortedProperties.loadProperties(Constants.SERVER_PROPERTIES_DIR + "/" + Constants.SERVER_PROPERTIES_NAME);

    List<String> urls = dataSource.orderedStream().map((available) -> {
      try (Connection connection = available.getConnection()) {
        if (connection.getMetaData().getURL().startsWith("jdbc:h2:mem:")) {
          return connection.getMetaData().getURL() + "|" + connection.getMetaData().getUserName();
        } else {
          return null;
        }
      }
      catch (Exception ex) {
        return null;
      }
    }).filter(Objects::nonNull).collect(Collectors.toList());

    if (urls.size() > 0)
    {
      for (int i = 0;; i++)
      {
        String value = properties.getProperty(String.valueOf(i), null);
        if (value == null || value.startsWith("Local H2|")) {
          properties.setProperty(String.valueOf(i), "Local H2|org.h2.Driver|" + urls.get(0));
          break;
        }
      }

      OutputStream out = FileUtils.newOutputStream(
          Constants.SERVER_PROPERTIES_DIR + "/" + Constants.SERVER_PROPERTIES_NAME, false);
      properties.store(out, "H2 Server Properties");
      out.close();
    }
  }
}

The console will contain the current H2 name as Local H2 menu entry:

The code is a composite of sources from H2 Console and Spring Boot H2 Console Autoconfiguration.

score 0 · Answer 27 · answered Jul 09 '22 at 11:36

0

NOTE:->spring.datasource.url=jdbc:h2:mem:nilesh-->Should be same in console(JDBC URL)

application.properties

spring.h2.console.enabled=true
spring.datasource.platform=h2
spring.datasource.url=jdbc:h2:mem:nilesh

answered Jul 09 '22 at 11:36

Sharad Rakesh Maddheshiya

29
1
7

Suprem Vanam · Answer 28 · 2022-08-20T19:57:21.153

Adding JPA Dependency to pom.xml fixed this error for me.

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

Here is my application.properties file, just for your reference.

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

score 0 · Answer 29 · answered Aug 29 '22 at 10:52

I am using Spring 2.7.3. I had the same issue with mem:testdb not found. After trying out all the different solutions I could find in Stackoverflow, the only solution that worked for me was adding both "spring.datasource.url=jdbc:h2:mem:testdb" AND "spring.datasource.generate-unique-name=false" in my application properties file.

I thought that by adding just the "spring.datasource.url=jdbc:h2:mem:testdb" should stop spring from generating unique name everytime I restart the application but apparently not. Manually creating the test.mv file did not work for me either. Hope this helps.

score 0 · Answer 30 · answered Oct 09 '22 at 20:28

Looks like everybody trying their own ways but solution to problem is NOT configuration but PHYSICAL. See @Subramanian answer.

Somehow , If you want to connect via console, Spring is unable to create a PHYSICAL file testdb.mv.db in root folder, i.e C:/USERS/testdb.mv.db. I went to C:/Users/SHEKHAR , and created a New > text file ( don't save it untill you rename it to testdb.mv.db or .mv.db. Restart Springboot, "Test Connection" is Green and Connect , takes me to Console GUI.

score -2 · Answer 31 · answered Aug 01 '21 at 16:43

-2

Please use this below solution it's working.

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.

answered Aug 01 '21 at 16:43

Ankush Thakur

147
3
4

This is the same solution as in [this other answer](https://stackoverflow.com/a/67739929/2227743) (and mentioned in others). – Eric Aya Aug 01 '21 at 16:47

springboot 2.3.0 while connecting to h2 database

31 Answers31

My Problem:

The Result

Linked