H2 DB Corruption: java.lang.IllegalStateException: Chunk 1936 not found [1.4.200/9]

[mk]

Hi,

we are using H2 1.4.200 with Spring Boot 2.2.2 and are experiencing from time to time database chunk corruptions. Therefore the database is not usable anymore. We know that our database is corrupt since recovery leads to: "IllegalStateException: Store header is corrupt". We get the following stack trace on restart:

2020-02-20 08:21:28.900 [main] ERROR com.zaxxer.hikari.pool.HikariPool(595) - HikariPool-1 - Exception during pool initialization.

org.h2.jdbc.JdbcSQLNonTransientException: Allgemeiner Fehler: "java.lang.IllegalStateException: Chunk 1936 not found [1.4.200/9]"

General error: "java.lang.IllegalStateException: Chunk 1936 not found [1.4.200/9]" [50000-200]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:505)

at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)

at org.h2.message.DbException.get(DbException.java:194)

at org.h2.message.DbException.convert(DbException.java:347)

at org.h2.engine.Database.openDatabase(Database.java:333)

at org.h2.engine.Database.<init>(Database.java:301)

at org.h2.engine.Engine.openSession(Engine.java:74)

at org.h2.engine.Engine.openSession(Engine.java:192)

at org.h2.engine.Engine.createSessionAndValidate(Engine.java:171)

at org.h2.engine.Engine.createSession(Engine.java:166)

at org.h2.engine.Engine.createSession(Engine.java:29)

at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:340)

at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:173)

at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:152)

at org.h2.Driver.connect(Driver.java:69)

at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138)

at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:353)

at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:201)

at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:473)

at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:562)

at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115)

at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112)

at org.flywaydb.core.internal.jdbc.JdbcUtils.openConnection(JdbcUtils.java:56)

at org.flywaydb.core.internal.jdbc.JdbcConnectionFactory.<init>(JdbcConnectionFactory.java:80)

at org.flywaydb.core.Flyway.execute(Flyway.java:438)

at org.flywaydb.core.Flyway.migrate(Flyway.java:149)

at org.springframework.boot.autoconfigure.flyway.FlywayMigrationInitializer.afterPropertiesSet(FlywayMigrationInitializer.java:65)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1855)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1792)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:595)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517)

at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:323)

at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)

at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:321)

at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)

at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:310)

at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:227)

at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveNamedBean(DefaultListableBeanFactory.java:1155)

at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveBean(DefaultListableBeanFactory.java:416)

at org.springframework.beans.factory.support.DefaultListableBeanFactory.getBean(DefaultListableBeanFactory.java:349)

at org.springframework.beans.factory.support.DefaultListableBeanFactory.getBean(DefaultListableBeanFactory.java:342)

at org.springframework.orm.jpa.EntityManagerFactoryUtils.findEntityManagerFactory(EntityManagerFactoryUtils.java:121)

at org.springframework.orm.jpa.EntityManagerFactoryAccessor.setBeanFactory(EntityManagerFactoryAccessor.java:155)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeAwareMethods(AbstractAutowireCapableBeanFactory.java:1818)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1783)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:595)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517)

at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:323)

at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)

at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:321)

at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)

at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:276)

at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1287)

at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1207)

at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:885)

at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:789)

at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:539)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1338)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1177)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:557)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517)

at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:323)

at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)

at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:321)

at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)

at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:276)

at org.springframework.beans.factory.support.DefaultListableBeanFactory.addCandidateEntry(DefaultListableBeanFactory.java:1503)

at org.springframework.beans.factory.support.DefaultListableBeanFactory.findAutowireCandidates(DefaultListableBeanFactory.java:1467)

at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveMultipleBeans(DefaultListableBeanFactory.java:1358)

at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1245)

at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1207)

at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredMethodElement.inject(AutowiredAnnotationBeanPostProcessor.java:715)

at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:116)

at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessProperties(AutowiredAnnotationBeanPostProcessor.java:399)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1422)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:594)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517)

at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:323)

at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)

at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:321)

at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)

at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:409)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1338)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1177)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:557)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517)

at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:323)

at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)

at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:321)

at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)

at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:276)

at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1287)

at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1207)

at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredMethodElement.inject(AutowiredAnnotationBeanPostProcessor.java:715)

at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:116)

at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessProperties(AutowiredAnnotationBeanPostProcessor.java:399)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1422)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:594)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517)

at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:323)

at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)

at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:321)

at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)

at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:409)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1338)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1177)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:557)

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517)

at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:323)

at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)

at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:321)

at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:207)

at org.springframework.boot.web.servlet.ServletContextInitializerBeans.getOrderedBeansOfType(ServletContextInitializerBeans.java:211)

at org.springframework.boot.web.servlet.ServletContextInitializerBeans.getOrderedBeansOfType(ServletContextInitializerBeans.java:202)

at org.springframework.boot.web.servlet.ServletContextInitializerBeans.addServletContextInitializerBeans(ServletContextInitializerBeans.java:96)

at org.springframework.boot.web.servlet.ServletContextInitializerBeans.<init>(ServletContextInitializerBeans.java:85)

at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.getServletContextInitializerBeans(ServletWebServerApplicationContext.java:253)

at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.selfInitialize(ServletWebServerApplicationContext.java:227)

at org.springframework.boot.web.embedded.tomcat.TomcatStarter.onStartup(TomcatStarter.java:53)

at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5135)

at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)

at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1384)

at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1374)

at java.base/java.util.concurrent.FutureTask.run(Unknown Source)

at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75)

at java.base/java.util.concurrent.AbstractExecutorService.submit(Unknown Source)

at org.apache.catalina.core.ContainerBase.startInternal(ContainerBase.java:909)

at org.apache.catalina.core.StandardHost.startInternal(StandardHost.java:841)

at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)

at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1384)

at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1374)

at java.base/java.util.concurrent.FutureTask.run(Unknown Source)

at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75)

at java.base/java.util.concurrent.AbstractExecutorService.submit(Unknown Source)

at org.apache.catalina.core.ContainerBase.startInternal(ContainerBase.java:909)

at org.apache.catalina.core.StandardEngine.startInternal(StandardEngine.java:262)

at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)

at org.apache.catalina.core.StandardService.startInternal(StandardService.java:421)

at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)

at org.apache.catalina.core.StandardServer.startInternal(StandardServer.java:930)

at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)

at org.apache.catalina.startup.Tomcat.start(Tomcat.java:459)

at org.springframework.boot.web.embedded.tomcat.TomcatWebServer.initialize(TomcatWebServer.java:107)

at org.springframework.boot.web.embedded.tomcat.TomcatWebServer.<init>(TomcatWebServer.java:88)

at org.springframework.boot.web.embedded.tomcat.TomcatServletWebServerFactory.getTomcatWebServer(TomcatServletWebServerFactory.java:438)

at org.springframework.boot.web.embedded.tomcat.TomcatServletWebServerFactory.getWebServer(TomcatServletWebServerFactory.java:191)

at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.createWebServer(ServletWebServerApplicationContext.java:180)

at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.onRefresh(ServletWebServerApplicationContext.java:153)

at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:544)

at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:141)

at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:747)

at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:397)

at org.springframework.boot.SpringApplication.run(SpringApplication.java:315)

at org.springframework.boot.builder.SpringApplicationBuilder.run(SpringApplicationBuilder.java:140)

at com.cenarion.svnplus.app.Application.main(Application.java:79)

at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.base/java.lang.reflect.Method.invoke(Unknown Source)

at org.springframework.boot.loader.MainMethodRunner.run(MainMethodRunner.java:48)

at org.springframework.boot.loader.Launcher.launch(Launcher.java:87)

at org.springframework.boot.loader.Launcher.launch(Launcher.java:51)

at org.springframework.boot.loader.PropertiesLauncher.main(PropertiesLauncher.java:597)

at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.base/java.lang.reflect.Method.invoke(Unknown Source)

at com.exe4j.runtime.LauncherEngine.launch(LauncherEngine.java:85)

at com.exe4j.runtime.WinLauncher.main(WinLauncher.java:94)

at com.install4j.runtime.launcher.WinLauncher.main(WinLauncher.java:25)

Caused by: java.lang.IllegalStateException: Chunk 1936 not found [1.4.200/9]

at org.h2.mvstore.DataUtils.newIllegalStateException(DataUtils.java:950)

at org.h2.mvstore.MVStore.getChunk(MVStore.java:1230)

at org.h2.mvstore.MVStore.readBufferForPage(MVStore.java:1214)

at org.h2.mvstore.MVStore.readPage(MVStore.java:2209)

at org.h2.mvstore.MVMap.readPage(MVMap.java:672)

at org.h2.mvstore.Page$NonLeaf.getChildPage(Page.java:1043)

at org.h2.mvstore.Cursor.hasNext(Cursor.java:53)

at org.h2.mvstore.MVMap$2$1.hasNext(MVMap.java:802)

at org.h2.mvstore.db.LobStorageMap.init(LobStorageMap.java:109)

at org.h2.engine.Database.open(Database.java:791)

at org.h2.engine.Database.openDatabase(Database.java:307)

... 165 common frames omitted

Before the corruption there was also another error which leads to the closing of the database. After the restart we get chunk problem above:

2020-01-31 08:13:49 database: flush

org.h2.message.DbException: Allgemeiner Fehler: "java.lang.IllegalStateException: java.lang.NullPointerException [1.4.200/3]"

General error: "java.lang.IllegalStateException: java.lang.NullPointerException [1.4.200/3]" [50000-200]

at org.h2.message.DbException.get(DbException.java:194)

at org.h2.message.DbException.convert(DbException.java:347)

at org.h2.mvstore.db.MVTableEngine$1.uncaughtException(MVTableEngine.java:93)

at org.h2.mvstore.MVStore.handleException(MVStore.java:2877)

at org.h2.mvstore.MVStore.panic(MVStore.java:481)

at org.h2.mvstore.MVStore.store(MVStore.java:1338)

at org.h2.mvstore.MVStore.store(MVStore.java:1312)

at org.h2.mvstore.MVStore.tryCommit(MVStore.java:1272)

at org.h2.mvstore.MVStore.writeInBackground(MVStore.java:2780)

at org.h2.mvstore.MVStore$BackgroundWriterThread.run(MVStore.java:3290)

Caused by: org.h2.jdbc.JdbcSQLNonTransientException: Allgemeiner Fehler: "java.lang.IllegalStateException: java.lang.NullPointerException [1.4.200/3]"

General error: "java.lang.IllegalStateException: java.lang.NullPointerException [1.4.200/3]" [50000-200]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:505)

at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)

... 10 more

Caused by: java.lang.IllegalStateException: java.lang.NullPointerException [1.4.200/3]

at org.h2.mvstore.DataUtils.newIllegalStateException(DataUtils.java:950)

... 5 more

Caused by: java.lang.NullPointerException

at org.h2.mvstore.MVStore.acceptChunkOccupancyChanges(MVStore.java:1612)

at org.h2.mvstore.MVStore.storeNow(MVStore.java:1433)

at org.h2.mvstore.MVStore.store(MVStore.java:1334)

... 4 more

2020-01-31 08:13:50 jdbc[3]: exception

org.h2.jdbc.JdbcSQLNonTransientConnectionException: Die Datenbank ist bereits geschlossen

The database has been closed [90098-200]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:622)

at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)

at org.h2.message.DbException.get(DbException.java:194)

at org.h2.engine.Session.getTransaction(Session.java:1792)

at org.h2.engine.Session.startStatementWithinTransaction(Session.java:1815)

at org.h2.command.Command.executeQuery(Command.java:190)

at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:114)

at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)

at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)

at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57)

at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.getResultSet(AbstractLoadPlanBasedLoader.java:390)

at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeQueryStatement(AbstractLoadPlanBasedLoader.java:163)

at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:104)

at org.hibernate.loader.entity.plan.AbstractLoadPlanBasedEntityLoader.load(AbstractLoadPlanBasedEntityLoader.java:197)

at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:4350)

at org.hibernate.event.internal.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:569)

at org.hibernate.event.internal.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:537)

at org.hibernate.event.internal.DefaultLoadEventListener.load(DefaultLoadEventListener.java:208)

at org.hibernate.event.internal.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:332)

at org.hibernate.event.internal.DefaultLoadEventListener.doOnLoad(DefaultLoadEventListener.java:108)

at org.hibernate.event.internal.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:74)

at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:118)

at org.hibernate.internal.SessionImpl.fireLoadNoChecks(SessionImpl.java:1168)

at org.hibernate.internal.SessionImpl.internalLoad(SessionImpl.java:1033)

at org.hibernate.type.EntityType.resolveIdentifier(EntityType.java:687)

at org.hibernate.type.EntityType.resolve(EntityType.java:464)

at org.hibernate.type.ManyToOneType.resolve(ManyToOneType.java:240)

at org.hibernate.engine.internal.TwoPhaseLoad.doInitializeEntity(TwoPhaseLoad.java:226)

at org.hibernate.engine.internal.TwoPhaseLoad.initializeEntity(TwoPhaseLoad.java:160)

at org.hibernate.loader.Loader.initializeEntitiesAndCollections(Loader.java:1179)

at org.hibernate.loader.Loader.processResultSet(Loader.java:1028)

at org.hibernate.loader.Loader.doQuery(Loader.java:964)

at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354)

at org.hibernate.loader.Loader.doList(Loader.java:2815)

at org.hibernate.loader.Loader.doList(Loader.java:2797)

at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2629)

at org.hibernate.loader.Loader.list(Loader.java:2624)

at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:506)

at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:396)

at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:219)

at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1396)

at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1558)

at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1526)

at org.hibernate.query.Query.getResultList(Query.java:165)

at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.base/java.lang.reflect.Method.invoke(Unknown Source)

at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:409)

at com.sun.proxy.$Proxy181.getResultList(Unknown Source)

at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:126)

at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:88)

at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:154)

at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:142)

at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:618)

at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:605)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:80)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:366)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:99)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:149)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)

at com.sun.proxy.$Proxy166.findDeletedOlderThan(Unknown Source)

at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.base/java.lang.reflect.Method.invoke(Unknown Source)

at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)

at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)

at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)

at com.sun.proxy.$Proxy166.findDeletedOlderThan(Unknown Source)

at com.cenarion.svnplus.commons.service.GutachtenService.lambda$initBinCleanup$0(GutachtenService.java:82)

at com.cenarion.svnplus.commons.security.SecurityContextAwareThreadPoolTaskScheduler.lambda$schedule$3(SecurityContextAwareThreadPoolTaskScheduler.java:91)

at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)

at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:93)

at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)

at java.base/java.util.concurrent.FutureTask.run(Unknown Source)

at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)

at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at java.base/java.lang.Thread.run(Unknown Source)

Caused by: java.lang.IllegalStateException: java.lang.NullPointerException [1.4.200/3]

at org.h2.mvstore.DataUtils.newIllegalStateException(DataUtils.java:950)

at org.h2.mvstore.MVStore.store(MVStore.java:1338)

at org.h2.mvstore.MVStore.store(MVStore.java:1312)

at org.h2.mvstore.MVStore.tryCommit(MVStore.java:1272)

at org.h2.mvstore.MVStore.writeInBackground(MVStore.java:2780)

at org.h2.mvstore.MVStore$BackgroundWriterThread.run(MVStore.java:3290)

Caused by: java.lang.NullPointerException

at org.h2.mvstore.MVStore.acceptChunkOccupancyChanges(MVStore.java:1612)

at org.h2.mvstore.MVStore.storeNow(MVStore.java:1433)

at org.h2.mvstore.MVStore.store(MVStore.java:1334)

... 4 more

Can you please help us with this issue?

[ciphe...@gmail.com]

I'm experiencing the same issue (have been since spring, actually), and finally recreated it manually, so I have additonal data to report.

1) The frequency of this error depends on the execution environment. I have got identical H2 versions running at 3 sites. At 2 sites, the error is possible but extremely rare. At the third site, it happens "almost weekly".

2) For me, it seems certain that the error is not about corrupt data, but about *corruption during recovery*. Today I got the error 2 times while recovering data, and got the data recovered (with no problems) on the third attempt. [ Sounds crazy, but I was fully awake! :) ]

3) Attached is the output it produced for me today. Please note how the chunk numbers differ! I cannot explain how that's possible.

4) However, I also get another scenario of a similar same error - truncated output in backup files. At this point, I think I've been unable to manually recreate that one, and haven't got a trace. Still searching.

[ciphe...@gmail.com]

P.S. To be sure it's not something that people have already solved, I will upgrade the server that experiences the error often, to the latest H2 version, and the try repeatedly on first opportunity (late evening / night).

[ciphe...@gmail.com]

I have now got the same error on the guaranteed-newest version (1.4.200/9).

What would be more practical, going into source code to look for the issue, or trying a development version of H2 from Github?

--- trace below ---

Stopping h2: === OK ===
Starting h2: === OK ===
Error: org.h2.jdbc.JdbcSQLNonTransientException: General error: "java.lang.IllegalStateException: Chunk 414 not found [1.4.200/9]"; SQL statement:
RUNSCRIPT FROM '/opt/script.sql' [50000-200]

        at org.h2.message.DbException.getJdbcSQLException(DbException.java:505)
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
        at org.h2.message.DbException.get(DbException.java:194)
        at org.h2.message.DbException.convert(DbException.java:347)

        at org.h2.command.Command.executeUpdate(Command.java:259)
        at org.h2.server.TcpServerThread.process(TcpServerThread.java:406)
        at org.h2.server.TcpServerThread.run(TcpServerThread.java:183)
        at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: java.lang.IllegalStateException: Chunk 414 not found [1.4.200/9]

        at org.h2.mvstore.DataUtils.newIllegalStateException(DataUtils.java:950)
        at org.h2.mvstore.MVStore.getChunk(MVStore.java:1230)
        at org.h2.mvstore.MVStore.readBufferForPage(MVStore.java:1214)
        at org.h2.mvstore.MVStore.readPage(MVStore.java:2209)
        at org.h2.mvstore.MVMap.readPage(MVMap.java:672)
        at org.h2.mvstore.Page$NonLeaf.getChildPage(Page.java:1043)
        at org.h2.mvstore.Cursor.hasNext(Cursor.java:53)

        at org.h2.mvstore.tx.TransactionMap$CommittedIterator.fetchNext(TransactionMap.java:779)
        at org.h2.mvstore.tx.TransactionMap$TMIterator.next(TransactionMap.java:952)
        at org.h2.mvstore.db.MVPrimaryIndex$MVStoreCursor.next(MVPrimaryIndex.java:469)
        at org.h2.mvstore.db.MVTable.rebuildIndexBlockMerge(MVTable.java:434)
        at org.h2.mvstore.db.MVTable.rebuildIndex(MVTable.java:393)
        at org.h2.mvstore.db.MVTable.addIndex(MVTable.java:370)
        at org.h2.command.ddl.CreateIndex.update(CreateIndex.java:107)
        at org.h2.command.dml.RunScriptCommand.execute(RunScriptCommand.java:79)
        at org.h2.command.dml.RunScriptCommand.update(RunScriptCommand.java:58)
        at org.h2.command.CommandContainer.update(CommandContainer.java:198)
        at org.h2.command.Command.executeUpdate(Command.java:251)
        ... 3 more

[Andreas Reichel]

Good Morning.

On Sun, 2021-02-21 at 21:32 -0800, ciphe...@gmail.com wrote:

rying a development version of H2 from Github?

While I am not a H2 Developper and can only speak for my own experience:

Running several large H2 databases we have had similar corruption issues with 1.4.200+ and never again since we switched to 2.0.201+ (until Feb/March 2020).

In my opinion, the current 2.0.201+ snapshot is the most robust and correct H2 database and I prefer this "unstable" develepmoent snapshot over the actually released versions.

Best regards

Andreas 

[Silvio]

We use H2 in production heavily and would love to upgrade but as long as there is no actual version released we can only use 2.0.x for testing purposes. If only we could get some ball park estimate about when 2.0.201 will be released with some highlights of improvements we can expect and preferably some information about compatibility (or lack thereof) with 1.4.200 that would be great. We are planning the roll-out of a major new version of our software which will involve major customer data migrations. If a production release of 2.0.201 is imminent we could adjust our release schedule to include the upgrade. If it will take another 6-12 months we cannot. But as it is we can only guess which, to be honest, really sucks.

[Andreas Reichel]

Silvio,

we are in a very similar situation. Banking software, financial accounting, not really the place where you want to experiment a lot. Our large customers all run Oracle (which deserves its own place in hell) but for smaller banks less than 100k accounts H2 can provide a nice alternative.

I am actually happy to hear from you because sometimes it looks like not many other people/companies run H2 in a similar scenario.

We also have to put a lot of effort in versioning and upgrading our own VBox software accross various customers and so are aware of the burden, which any kind of versioning, backward compatibility and migration actually is.

So with all understanding for the developpers and the scarce resources, from an end-user's point of view the current versioning schema is not optimal and we were close to abandon H2 once simply because the maintenance and upgrading procedure became too cumbersome.

Eventually we have decided for ourselves, that there is no actual reliable version schema: We have experienced the current 2.0.201+ development branch as much more robust and stable and correct than 1.4.200 and also there were many "breaking changes" in between (e. g. new reserved keywords, NEXTVAL vs. NEXT VALUE FOR etc.) that for us it works better to be as close as possible to the upstream development. We see H2 now as an very agile, rolling realise software.

I believe, the H2 developpers doe a fantastic job regarding the software itself but seem to lack experience or interest in practically running/using H2 in a corporate environment as a replacement for Postgres or Oracle. Maybe this is just out of scope.

 

My advise: If you stick with 1.4.200 longer, you will likely face a big migration effort in your own software/Schema Definition when switching to 2.0.201 eventually.

Consider using 2.0.201 and establish a weekly procedure of exporting to SQL script and re-importing into the next version, followed by a large batch processing test including reports. This works for us at least and has reduce the headache.

Best regards

Andreas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/00a5d74e-6a53-4ebd-a9a5-0b9af8fecfc3n%40googlegroups.com.

[Wojciech Marciniak]

Don't hesitate to use H2 not only for < 100k records. It can handle far more. But upgrade may produce issues, so be careful.

[Silvio]

Andreas,

Thank you for sharing your experience. First of all let me stress that I also believe the HS developers do a great job. It is a great tool that is invaluable to us. I only wish we could have a bit shorter and more predictable release cycle, especially in situations where the current release version contains non-trivial bugs.

Unfortunately using the development branch is not an option for us. Escrow clauses in some of our license agreements compel us to maintain an up to date repository of our software binaries and all required libraries and run-times. Additional clauses prohibit the use of any pre-release versions or outdated versions of libraries or run-times. Apart from that our software is a multi-tenant server system that is used in 24/7 production environments making upgrades tricky.

Our use-case is quite specific. Users create projects on the fly and each of those is a script + data-repository that is a clone of one of the project templates. Each template (and each copy thereof) is a self-containing system that has its own user interfaces and resources including an embedded H2 database. Since a couple of thousand of such projects are "active" at the same time the core server application instance dynamically loads/unloads them on-demand. We use a multi-database/url connection pool that does something similar with database connections, resulting in an average of ~50-200 concurrent connections to ~10-50 databases.

Although some of these database are several Gb in size most of them are quite small. A manual dump/restore is already possible but since it is in-process the same H2 version is used for the SQL dump and the recreation of the database. Perhaps if there would be an easy trick to use 1.4.200 for dumping and then 2.0.201 for recreating we could build this into the connection pool and do this on demand. It would be great if we could somehow use both versions simultaneously inside the same process or if there would be sufficient backward compatibility built-in for doing an SQL-dump of an older database from inside a newer H2.

On the upside I have managed to steer us clear of Oracle and SQLServer. Postgres served us well in days long gone by but for our copy-and-open-unlimited-databases-simultaneously use case there is no better match than embedded H2. To be fair HSQLDB also works fine but it is a lot slower than H2 in my experience.

Cheers,

Silvio

[Andreas Reichel]

Good Morning Silvio.

On Tue, 2021-02-23 at 13:18 -0800, Silvio wrote:

Thank you for sharing your experience. First of all let me stress that I also believe the HS developers do a great job. It is a great tool that is invaluable to us. I only wish we could have a bit shorter and more predictable release cycle, especially in situations where the current release version contains non-trivial bugs.

Spot on, I second this.

Unfortunately using the development branch is not an option for us. Escrow clauses in some of our license agreements compel us to maintain an up to date repository of our software binaries and all required libraries and run-times. Additional clauses prohibit the use of any pre-release versions or outdated versions of libraries or run-times. Apart from that our software is a multi-tenant server system that is used in 24/7 production environments making upgrades tricky.

I do understand your legal requirements, however in my opinion it is an illusion that sticking with 1.4.200 was compliant with these requirements: The H2 developpers stated clearly that each version is just a snapshot of the development branch (in numerical order) and that there was no support or bug-fixing for older releases.

In fact, all errors we found and reported were affecting 1.4.200 but have been fixed in the development branch 2.0.201 (only).

If you found the reason for the mentioned corruption and published a patch, then I doubt they would release another 1.4 version.

You could fork your own H2 repository though and just release H4-1.0 though in order to be escrow compliant. (In fact we have been discussing that internally already because of the "release schedule").

Our use-case is quite specific. Users create projects on the fly and each of those is a script + data-repository that is a clone of one of the project templates. 

At this part you could expect some effort on your end when switching to 2.0.201: they introduced some more Preserved Keywords (which broke our age-old Schema Definition) and also (re-)moved some Oracle compatibility from the H2 mode into the Oracle Mode strictly (NEXT VALUE FOR vs. NEXTVAL). We had to rewrite a few statements and also change our code around Sequences. Maybe some other stuff I do not recall.

Each template (and each copy thereof) is a self-containing system that has its own user interfaces and resources including an embedded H2 database. Since a couple of thousand of such projects are "active" at the same time the core server application instance dynamically loads/unloads them on-demand. We use a multi-database/url connection pool that does something similar with database connections, resulting in an average of ~50-200 concurrent connections to ~10-50 databases.

Although some of these database are several Gb in size most of them are quite small. A manual dump/restore is already possible but since it is in-process the same H2 version is used for the SQL dump and the recreation of the database. Perhaps if there would be an easy trick to use 1.4.200 for dumping and then 2.0.201 for recreating we could build this into the connection pool and do this on demand.

We have been facing similar challenges and so have built our own migration tool: https://github.com/manticore-projects/H2MigrationTool

There is a Command Line mode and an UI. If you want to integrate it into the connection pool, you can strip the UI and use the static method:

MigrationTool.migrate(

          String versionFrom,

          String versionTo,

          String databaseFileName,

          String user,

          String password,

          String scriptFileName,

          String compression,

          String upgradeOptions,

          boolean overwrite,

          boolean force)

          throws Exception

It supports migration of DB folder, which can contain may H2 DBs.

 It would be great if we could somehow use both versions simultaneously inside the same process or if there would be sufficient backward compatibility built-in for doing an SQL-dump of an older database from inside a newer H2.

Have a look into the code, it shows how to manage the different H2 Drivers and load/unload them dynamically.

On the upside I have managed to steer us clear of Oracle and SQLServer. Postgres served us well in days long gone by but for our copy-and-open-unlimited-databases-simultaneously use case there is no better match than embedded H2.

So true. Although I must say that only H2 2.0.201 provides the important features neded for a Oracle replacement: working table links and WITH clauses. 

And I am still missing the SQL Tuner and a proper execution plan (I know, I am dreaming).

 To be fair HSQLDB also works fine but it is a lot slower than H2 in my experience.

For us, even more important is the "Oracle compatibility" in H2. While our product is RDBMS agnostic and works with all major DBs, each product will need its own handling or SQL statement where not compliant with the standard.

In case of H2 compared to Oracle, we do not need any particular adjustments any more (since 2.0.201), while MS SQL Server has basically its own DDL/query definition file.

Running the same software and SQL on Oracle (on large servers) and H2 (on a 2 core 4GB virtual machine) is unparalleled and priceless.

All the best

Andreas

[Andreas Reichel]

Good Morning.

On Tue, 2021-02-23 at 05:33 -0800, Wojciech Marciniak wrote:

Don't hesitate to use H2 not only for < 100k records.

I wrote "accounts" (not records) , and each account with average 1'000 balance records and maybe 10'000 postings will give quite some data volume.

I have experimented with H2 databases up to 20 GByte large and they perform surprisingly well as long as you avoid large DML with indexes or constraints.

There is an open ticket on "INSERT INTO ... SELECT .. FROM ...".

Best regards

Andreas

[Silvio]

Hello Andreas,

I do understand your legal requirements, however in my opinion it is an illusion that sticking with 1.4.200 was compliant with these requirements: The H2 developpers stated clearly that each version is just a snapshot of the development branch (in numerical order) and that there was no support or bug-fixing for older releases.

In fact, all errors we found and reported were affecting 1.4.200 but have been fixed in the development branch 2.0.201 (only).

If you found the reason for the mentioned corruption and published a patch, then I doubt they would release another 1.4 version.

You could fork your own H2 repository though and just release H4-1.0 though in order to be escrow compliant. (In fact we have been discussing that internally already because of the "release schedule").

Generally speaking you are correct. I know that H2 has no bug fixing on released versions and the release mechanism is "forward only". But we have specified this explicitly in our contracts and have restricted ourselves to versions that were deemed release worthy by the H2 devs and presented as such on the website. At that time (I am speaking about the 1.2.x and early 1.3.x period) releases where much more frequent so that gave us ample leeway. I wish they would just in name release some of the more stable snapshots. I need no specific guarantees about stability as the currently released version also fails at times. We have built all kinds of safe guards around that already (going as far as doing redundant transaction logging that enables us to completely recreate a database with all its contents when it gets wrecked with close to zero data loss).

In theory we could renegotiate but as you probably know it is often risky to try to renegotiate terms that where once accepted and never looked at again if they are favorable to you already.

 

At this part you could expect some effort on your end when switching to 2.0.201: they introduced some more Preserved Keywords (which broke our age-old Schema Definition) and also (re-)moved some Oracle compatibility from the H2 mode into the Oracle Mode strictly (NEXT VALUE FOR vs. NEXTVAL). We had to rewrite a few statements and also change our code around Sequences. Maybe some other stuff I do not recall.

Our database abstraction layers should shield us from most changes. SQL statements use fully quoted names and we do not use sequences. Earlier H2 versions used to incorrectly drop quotes from DDL statements internally serialized by H2 giving us errors when reopening such databases but the devs solved that quickly in the next release at the time.

 

We have been facing similar challenges and so have built our own migration tool: https://github.com/manticore-projects/H2MigrationTool

There is a Command Line mode and an UI. If you want to integrate it into the connection pool, you can strip the UI and use the static method:

MigrationTool.migrate(

          String versionFrom,

          String versionTo,

          String databaseFileName,

          String user,

          String password,

          String scriptFileName,

          String compression,

          String upgradeOptions,

          boolean overwrite,

          boolean force)

          throws Exception

It supports migration of DB folder, which can contain may H2 DBs.

That could be very useful to us if we could do this in a separate class loader so we can have one (or several threads) converting databases while having the main server working with already converted databases using 2.0.201.

 

Have a look into the code, it shows how to manage the different H2 Drivers and load/unload them dynamically.

Thank you. I will.

 

So true. Although I must say that only H2 2.0.201 provides the important features neded for a Oracle replacement: working table links and WITH clauses. 

And I am still missing the SQL Tuner and a proper execution plan (I know, I am dreaming).

For us, even more important is the "Oracle compatibility" in H2. While our product is RDBMS agnostic and works with all major DBs, each product will need its own handling or SQL statement where not compliant with the standard.

In case of H2 compared to Oracle, we do not need any particular adjustments any more (since 2.0.201), while MS SQL Server has basically its own DDL/query definition file.

Running the same software and SQL on Oracle (on large servers) and H2 (on a 2 core 4GB virtual machine) is unparalleled and priceless.

We also target multiple RDBMS systems but H2 is one of the primary targets in the abstraction layer so we do not use any of its compatibility modes. And apart from HSQLDB we do not actively use any of the other currently supported systems (PostgreSQL, SQLServer, MySQL) any more. I never bothered to include Oracle. I used that quite heavily in the 8.x/9.x days and that was enough for me to never touch it again.

Thanks again for sharing your thoughts. I will certainly look into your conversion tool and will report back here with whatever we come up with.

And I am still hoping to hear something soon about what we can expect the coming months...

Kind regards,

Silvio

 

[ciphe...@gmail.com]

Thank you for the useful discussion. :) Sorry for my long absence, I was not able to find the bug myself, so I come back without a patch, and will consider instead migrating to the development version.