Skip to content

Latest commit

 

History

History
221 lines (152 loc) · 8.42 KB

H2-SETUP.md

File metadata and controls

221 lines (152 loc) · 8.42 KB
Raw

Go Back

H2 Database Engine

H2 is a Java SQL database with following main features (from the docs):

  • Very fast, open source, JDBC API
  • Embedded and server modes; in-memory databases
  • Browser based Console application

H2 is an open-source lightweight Java database, which can run in the client-server, file-based as well as in-memory/embedded database modes in Java applications. The data will not persist on the disk in-memory/embedded mode.

How to use in Spring Boot/Maven project

It's quite easy to set it up in a maven project. Just add the following dependency in the pom.xml file:

<!-- In-memory database to help with testing and development activities -->
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>${h2.version}</version>
</dependency>

Connection Modes

The following connection modes are supported:

  • Embedded mode (local connections using JDBC)
  • Server mode (remote connections using JDBC or ODBC over TCP/IP)
  • Mixed mode (local and remote connections at the same time)

Read more about the available modes

How to connect?

To connect to h2 database, here is a list, but in our application, we are connecting it as:

spring.datasource.url=jdbc:h2:file:./h2_db/${spring.datasource.name}\
  ;MODE=MySQL\
  ;AUTO_SERVER=TRUE\
  ;DB_CLOSE_DELAY=-1\
  ;DB_CLOSE_ON_EXIT=TRUE\
  ;DATABASE_TO_UPPER=FALSE\
  ;INIT=CREATE SCHEMA IF NOT EXISTS ${spring.datasource.name}\\\
  ;SET SCHEMA ${spring.datasource.name}

We are setting it up as a file based db (jdbc:h2:file) which will be located at ./h2_db/${spring.datasource.name}.

Connection Parameters

There are quite a few parameters set in that url but you may not need all of them in your setup.

  • MODE=MySQL - o use the MySQL mode, use the database URL jdbc:h2:~/test;MODE=MySQL;DATABASE_TO_LOWER=TRUE. When case-insensitive identifiers are needed append ;CASE_INSENSITIVE_IDENTIFIERS=TRUE to URL. Do not change value of DATABASE_TO_LOWER after creation of database.
  • AUTO_SERVER=TRUE - Use this if you want to connect to this h2 from multiple places (which could be different processes, or VM's on the same machine or remote connections from other machines) without having to start the server manually. You can use the same database URL independent of whether the database is already open or not. This feature doesn't work with in-memory databases. But here, we set it up like that in order to be able to connect to h2 console from browser and from within Intellij. It'll be accessible as long as your spring boot application is up and running.
  • DB_CLOSE_DELAY=-1 - By default, closing the last connection to a database closes the database. For an in-memory database, this means the content is lost. To keep the database open, add ;DB_CLOSE_DELAY=-1 to the database URL. To keep the contents of an in-memory database as long as the virtual machine is alive, use jdbc:h2:mem:test;DB_CLOSE_DELAY=-1. This may create a memory leak, when you need to remove the database, use the SHUTDOWN command. It can be executed from the Query Console.
  • DB_CLOSE_ON_EXIT=TRUE - By default, a database is closed when the last connection is closed. However, if it is never closed, the database is closed when the virtual machine exits normally, using a shutdown hook.
  • INIT - we can write any sql statements to execute or can also use *.sql scripts for this purpose
  • SET SCHEMA - Lastly, we setting the schema to be used.

Settings in the url are coming from INFORMATION_SCHEMA.SETTINGS table.

Issues faced after upgrading

After Java and Spring Boot upgrade, application startup with H2 fails.

Click to see details of `Table not found`

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException

Application startup with H2 fails. when togglz enabled and when spring.datasource.name is set to any schema (i.e. YNaMi) other than the PUBLIC schema.

It's quite strange to see such an error even when the Flyway migrations have completed successfully. So, there is no way to believe that the tables mentioned in the logs don't exist in the database.

Click here for stacktrace 
INFO 6578 --- [  restartedMain] o.f.c.i.database.base.BaseDatabaseType   : Database: jdbc:h2:mem:YNaMi (H2 2.2)
22-08-2023 14:49:56.267 [restartedMain] INFO  org.flywaydb.core.internal.database.base.BaseDatabaseType.info - 
Database: jdbc:h2:mem:YNaMi (H2 2.2)
2023-08-22T14:49:56.312+05:00  INFO 6578 --- [  restartedMain] o.f.core.internal.database.base.Schema   : 
Creating schema "YNaMi" ...
22-08-2023 14:49:56.312 [restartedMain] INFO  org.flywaydb.core.internal.database.base.Schema.info - 
Creating schema "YNaMi" ...
2023-08-22T14:49:56.315+05:00  INFO 6578 --- [  restartedMain] o.f.c.i.s.JdbcTableSchemaHistory         : 
Creating Schema History table "YNaMi"."flyway_schema_history" ...
22-08-2023 14:49:56.315 [restartedMain] INFO  org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.info - 
Creating Schema History table "YNaMi"."flyway_schema_history" ...
2023-08-22T14:49:56.341+05:00  INFO 6578 --- [  restartedMain] o.f.core.internal.command.DbMigrate      : 
Current version of schema "YNaMi": null
22-08-2023 14:49:56.341 [restartedMain] INFO  org.flywaydb.core.internal.command.DbMigrate.info - 
Current version of schema "YNaMi": null
.....
INFO  org.flywaydb.core.internal.command.DbMigrate.info - Successfully applied 4 migrations to schema "YNaMi", 
now at version v004 (execution time 00:00.028s)

ERROR org.springframework.boot.SpringApplication.reportFailure - Application run failed
org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "FeatureToggles" not found; SQL statement:

Suggested Fixes

There are lots of suggested fixes like setting the following in the properties file:

spring.flyway.default-schema=YNaMi
or
spring.flyway.baseline-on-migrate = true
or
#spring.jpa.defer-datasource-initialization=true
or
spring.flyway.enabled = false => and configure flyway manually

Or, append the following in the datasource url:

CASE_INSENSITIVE_IDENTIFIERS=TRUE;
or
SCHEMA_SEARCH_PATH=INFORMATION_SCHEMA;
or
DB_CLOSE_DELAY=-1;
or
DB_CLOSE_ON_EXIT=FALSE;

Or instead of all that, simply use the H2's default schema: i.e. spring.datasource.name=PUBLIC.

Fix which actually worked

I had to append the following in the datasource url:

;INIT=CREATE SCHEMA IF NOT EXISTS ${spring.datasource.name}\\\
;SET SCHEMA ${spring.datasource.name}

Problem was, Flyway was doing its job alright but H2 wasn't connecting to that database which was used by Flyway and hence table not found errors. It was all working fine if we use the H2's default schema i.e. the PUBLIC schema. But, we had to use our own schema, and the way to tell hibernate to stick to our schema, was to append CREATE SCHEMA and SET SCHEMA to the datasource url.

H2 creating column with character varying instead of varchar

H2 Change Log

Getting this error when trying to connect to H2 from within IntelliJ:

org.h2.message.DbException: General error: "org.h2.mvstore.MVStoreException: 
The file is locked: /Users/muhammadfaisal/Documents/projects/YNaMi/YNaMi.mv.db [2.1.210/7]"

As the documentation says; ( http://h2database.com/html/features.html#auto_mixed_mode ), used AUTO_SERVER=TRUE in the url.

Changed H2 storage strategy from in-memory to file based as H2 in embedded mode allows only one database connection at a time. So to support multiple connections, need to start H2 in server mode.

Here, multiple connections mean, connecting to our local db/H2 console from browser as well as from within IntelliJ. With embedded mode we could connect just from browser.

Go Back