Problems with upgrade from 1.3.174 to 1.4.181

[Roar Nestegard]

Upgrade from 1.3.174 to 1.4.181 results in an error when opening some of our databases. Everything worked just fine before the upgrade but after some of the database could not open and we got an exception like the the one in Appendix 1.

I investigated the issue and found that there have been more cases like this around 1.3.17x. One solution that was mentioned was to use Recover/RunScript and recreate the database. This worked for one of the databases but resulted in the error found in Appendix 2 for one of the databases.

I'm kind of stuck, if there is no solution to this problem I'll have to stay on 1.3.174 and never update. Even with a Recover/RunScript solution I'll have a serious problem because we must do this on 100+ installations.

Any suggestions?

Regards,

Roar Nestegard

=============== Appendix 1 ==================

2014-10-16 09:36:29,361 [[iPOJO] pool-1-thread-1] FATAL com.xxx.xxx.datastore.impl.h2.DatabaseManagerProvider - Failed to open/create database for: jdbc:h2:./data/h2/health

org.h2.jdbc.JdbcSQLException: Unique index or primary key violation: "CONSTRAINT_53_INDEX_D ON PUBLIC.HEALTH_MONITOR(ASSETID) VALUES ( /* 1998 */ 'a9eaca5e-c2c6-4e58-9a94-8c4a0ad3885b' )"; SQL statement:

ALTER TABLE PUBLIC.HEALTH_ACK ADD CONSTRAINT PUBLIC.CONSTRAINT_53 FOREIGN KEY(ASSETID) REFERENCES PUBLIC.HEALTH_MONITOR(ASSETID) ON DELETE CASCADE NOCHECK [23505-181]

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

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

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

at org.h2.index.BaseIndex.getDuplicateKeyException(BaseIndex.java:102)

at org.h2.index.PageBtree.find(PageBtree.java:120)

at org.h2.index.PageBtreeLeaf.addRow(PageBtreeLeaf.java:150)

at org.h2.index.PageBtreeLeaf.addRowTry(PageBtreeLeaf.java:103)

at org.h2.index.PageBtreeIndex.addRow(PageBtreeIndex.java:96)

at org.h2.index.PageBtreeIndex.add(PageBtreeIndex.java:87)

at org.h2.table.RegularTable.addRowsToIndex(RegularTable.java:352)

at org.h2.table.RegularTable.addIndex(RegularTable.java:277)

at org.h2.command.ddl.AlterTableAddConstraint.createIndex(AlterTableAddConstraint.java:287)

at org.h2.command.ddl.AlterTableAddConstraint.tryUpdate(AlterTableAddConstraint.java:236)

at org.h2.command.ddl.AlterTableAddConstraint.update(AlterTableAddConstraint.java:72)

at org.h2.engine.MetaRecord.execute(MetaRecord.java:58)

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

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

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

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

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

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

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

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

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

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

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

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

at org.h2.jdbcx.JdbcDataSource.getJdbcConnection(JdbcDataSource.java:190)

at org.h2.jdbcx.JdbcDataSource.getXAConnection(JdbcDataSource.java:353)

at org.h2.jdbcx.JdbcDataSource.getPooledConnection(JdbcDataSource.java:385)

at org.h2.jdbcx.JdbcConnectionPool.getConnectionNow(JdbcConnectionPool.java:227)

at org.h2.jdbcx.JdbcConnectionPool.getConnection(JdbcConnectionPool.java:199)

=============== Appendix 2 ==================

2014-10-14 15:41:26,666 [[iPOJO] pool-1-thread-1] FATAL com.xxx.xxx.datastore.impl.h2.DatabaseManagerProvider - Failed to open/create database for: jdbc:h2:./data/h2/health

org.h2.jdbc.JdbcSQLException: Unsupported database file version or invalid file header in file "E:/CeeViewServer/data/h2/health.h2.db" [90048-181]

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

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

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

at org.h2.store.FileStore.init(FileStore.java:195)

at org.h2.engine.Database.openFile(Database.java:530)

at org.h2.store.PageStore.openExisting(PageStore.java:328)

at org.h2.store.PageStore.open(PageStore.java:288)

at org.h2.engine.Database.getPageStore(Database.java:2386)

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

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

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

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

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

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

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

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

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

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

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

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

at org.h2.jdbcx.JdbcDataSource.getJdbcConnection(JdbcDataSource.java:190)

at org.h2.jdbcx.JdbcDataSource.getXAConnection(JdbcDataSource.java:353)

at org.h2.jdbcx.JdbcDataSource.getPooledConnection(JdbcDataSource.java:385)

at org.h2.jdbcx.JdbcConnectionPool.getConnectionNow(JdbcConnectionPool.java:227)

at org.h2.jdbcx.JdbcConnectionPool.getConnection(JdbcConnectionPool.java:199)

[Thomas Mueller]

Hi,

I would probably upgrade to version 1.3.176, or if you upgrade to version 1.4.x, then append ";mv_store=false" to the database URL. That way, the database file format stays the same.

As for the specific problem, I can't help I'm afraid. Both exceptions are strange. What database URL do you use?

Regards,

Thomas

--
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 post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Roar Nestegard]

Hi,

I discovered just after I posted that 1.4.x was a beta version so I upgraded to 1.3.176 and to my surprise I got exactly the same error as described in Appendix 1 in my first post.

/Roar


Exception using 1.3.176

2014-10-17 10:31:17,279 [[iPOJO] pool-1-thread-1] FATAL com.xxx.xxx.datastore.impl.h2.DatabaseManagerProvider - Failed to open/create database for: jdbc:h2:./data/h2/health

org.h2.jdbc.JdbcSQLException: Unique index or primary key violation: "PRIMARY KEY ON """".PAGE_INDEX"; SQL statement:

ALTER TABLE PUBLIC.HEALTH_REASON ADD CONSTRAINT PUBLIC.CONSTRAINT_8 FOREIGN KEY(HEALTHINDEXID) INDEX PUBLIC.CONSTRAINT_INDEX_8 REFERENCES PUBLIC.HEALTH_INDEX(HEALTHINDEXID) ON DELETE CASCADE NOCHECK [23505-176]

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

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

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

at org.h2.index.PageDataIndex.getNewDuplicateKeyException(PageDataIndex.java:166)

at org.h2.index.PageDataIndex.add(PageDataIndex.java:144)

at org.h2.store.PageStore.addMeta(PageStore.java:1808)

at org.h2.index.PageBtreeIndex.<init>(PageBtreeIndex.java:64)

at org.h2.table.RegularTable.addIndex(RegularTable.java:235)

at org.h2.command.ddl.AlterTableAddConstraint.createIndex(AlterTableAddConstraint.java:280)

at org.h2.command.ddl.AlterTableAddConstraint.tryUpdate(AlterTableAddConstraint.java:229)

at org.h2.command.ddl.AlterTableAddConstraint.update(AlterTableAddConstraint.java:73)

at org.h2.engine.MetaRecord.execute(MetaRecord.java:59)

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

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

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

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

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

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

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

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

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

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

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

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

at org.h2.jdbcx.JdbcDataSource.getJdbcConnection(JdbcDataSource.java:191)

at org.h2.jdbcx.JdbcDataSource.getXAConnection(JdbcDataSource.java:354)

at org.h2.jdbcx.JdbcDataSource.getPooledConnection(JdbcDataSource.java:386)

at org.h2.jdbcx.JdbcConnectionPool.getConnectionNow(JdbcConnectionPool.java:228)

at org.h2.jdbcx.JdbcConnectionPool.getConnection(JdbcConnectionPool.java:200)

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.