Issue
I am trying to create multitenant application with single db and seperate schemas for tenants. And I don't want to loose my data after every startup. This is my application.properties file
logging.level.root=info
spring.h2.console.enabled=true
spring.datasource.url=jdbc:h2:mem:multi-tenant
spring.jpa.hibernate.ddl-auto=update
spring.liquibase.change-log=classpath:db/changelog/changelog-master.xml
And here is my logs
Connected to the target VM, address: '127.0.0.1:53999', transport: 'socket'
. ____ _ __ _ _
/\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
\\/ ___)| |_)| | | | | || (_| | ) ) ) )
' |____| .__|_| |_|_| |_\__, | / / / /
=========|_|==============|___/=/_/_/_/
:: Spring Boot :: (v2.7.2)
2022-08-11 13:02:28.342 INFO 25732 --- [ main] c.l.multitenant.MultiTenantApplication : Starting MultiTenantApplication using Java 18.0.1.1 on Manu with PID 25732 (D:\JAVA\multi-tenant - updated\target\classes started by itsma in D:\JAVA\multi-tenant - updated)
2022-08-11 13:02:28.342 INFO 25732 --- [ main] c.l.multitenant.MultiTenantApplication : No active profile set, falling back to 1 default profile: "default"
2022-08-11 13:02:29.534 INFO 25732 --- [ main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JPA repositories in DEFAULT mode.
2022-08-11 13:02:29.613 INFO 25732 --- [ main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 53 ms. Found 1 JPA repository interfaces.
2022-08-11 13:02:30.507 INFO 25732 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat initialized with port(s): 8080 (http)
2022-08-11 13:02:30.522 INFO 25732 --- [ main] o.apache.catalina.core.StandardService : Starting service [Tomcat]
2022-08-11 13:02:30.522 INFO 25732 --- [ main] org.apache.catalina.core.StandardEngine : Starting Servlet engine: [Apache Tomcat/9.0.65]
2022-08-11 13:02:30.742 INFO 25732 --- [ main] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring embedded WebApplicationContext
2022-08-11 13:02:30.742 INFO 25732 --- [ main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 2321 ms
2022-08-11 13:02:30.993 INFO 25732 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2022-08-11 13:02:31.286 INFO 25732 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
2022-08-11 13:02:31.302 INFO 25732 --- [ main] o.s.b.a.h2.H2ConsoleAutoConfiguration : H2 console available at '/h2-console'. Database available at 'jdbc:h2:mem:multi-tenant'
2022-08-11 13:02:31.893 INFO 25732 --- [ main] liquibase.database : Set default schema name to PUBLIC
2022-08-11 13:02:32.253 INFO 25732 --- [ main] liquibase.lockservice : Successfully acquired change log lock
2022-08-11 13:02:32.929 INFO 25732 --- [ main] liquibase.changelog : Creating database history table with name: PUBLIC.DATABASECHANGELOG
2022-08-11 13:02:32.929 INFO 25732 --- [ main] liquibase.changelog : Reading from PUBLIC.DATABASECHANGELOG
Running Changeset: db/changelog/changelog-v1.0.xml::1::manu
2022-08-11 13:02:33.102 INFO 25732 --- [ main] liquibase.changelog : SQL in file db/changelog/data/data.sql executed
2022-08-11 13:02:33.102 INFO 25732 --- [ main] liquibase.changelog : ChangeSet db/changelog/changelog-v1.0.xml::1::manu ran successfully in 32ms
2022-08-11 13:02:33.117 INFO 25732 --- [ main] liquibase.lockservice : Successfully released change log lock
2022-08-11 13:02:33.321 INFO 25732 --- [ main] o.hibernate.jpa.internal.util.LogHelper : HHH000204: Processing PersistenceUnitInfo [name: default]
2022-08-11 13:02:33.384 INFO 25732 --- [ main] org.hibernate.Version : HHH000412: Hibernate ORM core version 5.6.10.Final
2022-08-11 13:02:33.525 INFO 25732 --- [ main] o.hibernate.annotations.common.Version : HCANN000001: Hibernate Commons Annotations {5.1.2.Final}
2022-08-11 13:02:33.682 INFO 25732 --- [ main] org.hibernate.dialect.Dialect : HHH000400: Using dialect: org.hibernate.dialect.H2Dialect
2022-08-11 13:02:34.138 INFO 25732 --- [ main] o.h.e.t.j.p.i.JtaPlatformInitiator : HHH000490: Using JtaPlatform implementation: [org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform]
2022-08-11 13:02:34.138 INFO 25732 --- [ main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2022-08-11 13:02:34.530 WARN 25732 --- [ main] JpaBaseConfiguration$JpaWebConfiguration : spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning
2022-08-11 13:02:35.064 INFO 25732 --- [ main] o.s.b.a.e.web.EndpointLinksResolver : Exposing 1 endpoint(s) beneath base path '/actuator'
2022-08-11 13:02:35.143 INFO 25732 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat started on port(s): 8080 (http) with context path ''
2022-08-11 13:02:35.174 INFO 25732 --- [ main] c.l.multitenant.MultiTenantApplication : Started MultiTenantApplication in 7.407 seconds (JVM running for 8.296)
2022-08-11 13:02:37.086 INFO 25732 --- [nio-8080-exec-7] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring DispatcherServlet 'dispatcherServlet'
2022-08-11 13:02:37.086 INFO 25732 --- [nio-8080-exec-7] o.s.web.servlet.DispatcherServlet : Initializing Servlet 'dispatcherServlet'
2022-08-11 13:02:37.087 INFO 25732 --- [nio-8080-exec-7] o.s.web.servlet.DispatcherServlet : Completed initialization in 1 ms
Why is ddl-auto=update not working?
Solution
You use an in memory database configuration.
This means that when the application starts up an embedded database will be created not in permanent memory but in RAM.
This also means that when the application is shut down the memory it took in RAM is cleaned, therefore any information relative with this database is also lost.
H2 database offers another configuration in the url which will make sure that the data that database holds, is not stored in RAM but in permanent storage HDD. This way the application could be shut down and the data which the database contained, will not be lost.
The database however will stop functioning when the application is shut down, as the process that runs the database belongs to the main process of starting your application. But when the application starts up again and database process is started also, the database will see all the data that it contained before the application was shutdown.
The config is the following:
spring.datasource.url= jdbc:h2:/data/multi-tenant;AUTO_SERVER=TRUE
Multiple processes can access the same database without having to start the server manually. To do that, append ;AUTO_SERVER=TRUE to the database URL. 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.
Use the same URL for all connections to this database. Internally, when using this mode, the first connection to the database is made in embedded mode, and additionally a server is started internally (as a daemon thread). If the database is already open in another process, the server mode is used automatically. The IP address and port of the server are stored in the file .lock.db, that's why in-memory databases can't be supported.
The application that opens the first connection to the database uses the embedded mode, which is faster than the server mode. Therefore the main application should open the database first if possible. The first connection automatically starts a server on a random port. This server allows remote connections, however only to this database (to ensure that, the client reads .lock.db file and sends the random key that is stored there to the server). When the first connection is closed, the server stops. If other (remote) connections are still open, one of them will then start a server (auto-reconnect is enabled automatically).
All processes need to have access to the database files. If the first connection is closed (the connection that started the server), open transactions of other connections will be rolled back (this may not be a problem if you don't disable autocommit). Explicit client/server connections (using jdbc:h2:tcp:// or ssl://) are not supported. This mode is not supported for in-memory databases.
Answered By - Panagiotis Bougioukos
Answer Checked By - Marilyn (JavaFixing Volunteer)
