Why H2 file database data get cleared every time

Question

I use H2 database for a test application, using Spring boot. Every time, when I restart the spring boot app, the data in H2 gets cleared. I am using a file instead of memory. I set the spring.jpa.hibernate.ddl-auto=update in application.properties too. Here is my application.properties file

spring.datasource.url=jdbc:h2:file:./data/demo spring.datasource.driverClassName=org.h2.Driver spring.datasource.username=sa spring.datasource.password=password spring.jpa.database-platform=org.hibernate.dialect.H2Dialect spring.h2.console.enabled=true spring.jpa.hibernate.ddl-auto=update

What am I doing wrong here. It saves data fine. But once I shut down the app, all data get cleared.

H2 is in memory database, it will get clear for every shutdown — Ryuzaki L
– Ryuzaki L, Commented Jul 10, 2019 at 6:43
to persist and not clear the database on exit: spring.datasource.url=jdbc:h2:~/test;DB_CLOSE_ON_EXIT=FALSE — Paizo
– Paizo, Commented Jul 10, 2019 at 7:30
It can write to a file too. so it will not be cleared after the shutdown. I found my mistake. look my answer below — Shehan Simen
– Shehan Simen, Commented Jul 11, 2019 at 4:16
I have the same problem with same application properties DB_CLOSE_ON_EXIT=FALSE fix the issue even I am using file not mem — mikegrep
– mikegrep, Commented Jul 1, 2023 at 19:48

Koray Tugay · Accepted Answer · 2020-02-23 15:54:56Z

I found the mistake I was doing. I had data.sql file in resources and every time Spring boot starts the app, it runs this script. In that script I was dropping and recreating all the tables. Once I removed those sql statements, it works perfect. Data is persistent to the file and won't be erased after the server restart.

I dont think you have found correct mistake. For me I am not droping and creating table using data.sql, still my data was lost. Adding DB_CLOSE_ON_EXIT=FALSE helped to persist data even after closing the application

Romil Patel · Accepted Answer · 2019-07-10 07:40:24Z

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 content of an in-memory database as long as the virtual machine is alive, use jdbc:h2:mem:test;DB_CLOSE_DELAY=-1.; DB_CLOSE_ON_EXIT=FALSE;

H2 Feature Document

You can write it to a file, so data will persistent. that is why I had this line. spring.datasource.url=jdbc:h2:file:./data/demo

Collectives™ on Stack Overflow

Why H2 file database data get cleared every time

2 Answers 2

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related