Will a H2 v1.4.200 jar file read/open a database created by H2 V1.3.x ?

[Michael Earl]

Hello All,

I am attempting to upgrade our H2 jar to 1.4.200.  We are currently using 1.3.164. 

Using the 1.4.200 jar, I can't successfully open a database created by 1.3.164. Is this expected?

When simply opening the database I get the following:

The specified database user/password combination is rejected: [23505][23505] Unique index or primary key violation: "PRIMARY KEY ON """".PAGE_INDEX"; SQL statement: ALTER TABLE PUBLIC.SITE_PERMISSION ADD CONSTRAINT PUBLIC.ACCESS_NAME_FK FOREIGN KEY(ACCESS_NAME) REFERENCES PUBLIC.ACCESS(ACCESS_NAME) NOCHECK [23505-200]

The database opens successfully using the 1.3.164 jar.

This is my connection string: jdbc:h2:file:C:/Projects/FlexIDS 4.9 - H2 Upgrade/FlexDB/FlexDB;MV_STORE=false

Thanks for any help

Mike.

[Noel Grandin]

you best bet is to do a full backup and restore.

It is quite possible you have latent corruption that only newer versions will complain about.

[Michael Earl]

Thanks Noel.  I'll give that a try.

[Michael Earl]

Actually, this happens with a newly created database with some default data loaded.  It's happens every time.  

It appears that there's a backward compatibility issue.  Are there known issues of this type?  

On Friday, June 26, 2020 at 2:25:48 PM UTC-7, Michael Earl wrote:

[Evgenij Ryazanov]

Hello.

Yes, there are known issues and most likely they will not be fixed, development of legacy PageStore backend was stopped many years ago, only few changes were applied since then.

Versions before 1.4.197 use incorrect indexes for referential constraints in some cases, this issue was fixed in 1.4.197, but old databases in PageStore format with this issue can't be opened by newer versions due to these changes.

Some very old versions from 1.3 series of releases use experimental format for datetime values, this format was removed, because it caused database corruption in some cases. H2 can read old data, but it still can cause corruption.

I guess there are others.

You need to use the recommended migration procedure and use SQL script as intermediate format.

https://h2database.com/html/tutorial.html#upgrade_backup_restore

Unfortunately, you may run into some issue with it too, if you used some SQL keywords as identifiers; old versions of H2 don't quote all identifiers in exported SQL scripts. H2 1.4.200 has more keywords than archaic versions, but most of them are from SQL-92.

[Michael Earl]

Hello Evgenij,

Thank you so much for the complete answer.

Thanks,

Mike.

On Friday, June 26, 2020 at 2:25:48 PM UTC-7, Michael Earl wrote:

[Michael Earl]

Hello Evgenij,

I am getting the same sql exception when running the script to create a backup file of the old (1.3) database to the new (1.4) database.  The error is:

The specified database user/password combination is rejected: [23505][23505] Unique index or primary key violation: "PRIMARY KEY ON """".PAGE_INDEX"; SQL statement: ALTER TABLE PUBLIC.SITE_PERMISSION ADD CONSTRAINT PUBLIC.ACCESS_NAME_FK FOREIGN KEY(ACCESS_NAME) REFERENCES PUBLIC.ACCESS(ACCESS_NAME) NOCHECK [23505-200]

There are actually NO rows in the SITE_PERMISSION table and only two rows in the ACCESS table.  I am not sure why there would be a primary key violation when there are no rows to index.  

The DDL for SITE_PERMISSION is as follows:

create table SITE_PERMISSION

(

ACCESS_NAME VARCHAR(100) not null,

SITE_NAME VARCHAR(5) not null,

IDS_PERMISSION_ID INTEGER not null,

constraint SITE_PERMISSION_PK

primary key (ACCESS_NAME, SITE_NAME, IDS_PERMISSION_ID),

constraint ACCESS_NAME_FK

foreign key (ACCESS_NAME) references ACCESS,

constraint IDS_PERMISSION_ID_FK

foreign key (IDS_PERMISSION_ID) references IDS_PERMISSIONS,

constraint SITE_NAME_FK

foreign key (SITE_NAME) references SITE

);

Any ideas on why this error is occuring?

Thanks,

Mike.

On Friday, June 26, 2020 at 2:25:48 PM UTC-7, Michael Earl wrote: