Errors on upgrading from 5.2.6 to 5.4.1.2

Hello,

I have just tried upgrading from JChem 5.2.6 to 5.4.1.2 on x86_64 Linux (Oracle 10.2.0.4) and on the whole the process seemed to go pretty smoothly, however one error was reported as part of the upgrade and I want to check if this is serious or not.

Here is the relevant screen output :

INFO: Preparing upgrade of JChem Cartridge in JCHEM based on JCC_UPGR_STAGE...

INFO: SchemaObjectId[type=TYPE, name=JC_IDXTYPE_IM]: 23 methods in the old JChem Cartridge version, 26 methods in the new JChem Cartridge version ; 3 method(s) to add, 0 method(s) to drop

INFO: Upgrade plan has been stored in /opt/ChemAxon/jchem-5.4.1.2/cartridge/upgrade-plan.txt

SEVERE: Sql failed: create or replace operator jchem.JC_TVERSKY binding

(VARCHAR2,VARCHAR2,NUMBER,NUMBER) return NUMBER with index context, scan context JC_IDXTYPE_IM using "TVERSKY_FUNC",(CLOB,CLOB,NUMBER,NUMBER) return NUMBER with index context, scan context JC_IDXTYPE_IM using "TVERSKY_FUNCC",(BLOB,BLOB,NUMBER,NUMBER) return NUMBER with index context, scan context JC_IDXTYPE_IM using "TVERSKY_FUNCB"

SEVERE: Sql failed: alter indextype jchem.JC_IDXTYPE

add jchem.JC_DISSIMILARITY(VARCHAR2,VARCHAR2,VARCHAR2),

add jchem.JC_DISSIMILARITY(CLOB,CLOB,VARCHAR2),

add jchem.JC_DISSIMILARITY(BLOB,BLOB,VARCHAR2),

add jchem.JC_TVERSKY(VARCHAR2,VARCHAR2,NUMBER,NUMBER),

add jchem.JC_TVERSKY(CLOB,CLOB,NUMBER,NUMBER),

add jchem.JC_TVERSKY(BLOB,BLOB,NUMBER,NUMBER),

add jchem.JC_COMPARE(CLOB,BLOB,VARCHAR2),

add jchem.JC_EVALUATE(VARCHAR2,VARCHAR2,VARCHAR2),

add jchem.JC_EVALUATE(CLOB,VARCHAR2,VARCHAR2),

add jchem.JC_EVALUATE(BLOB,VARCHAR2,VARCHAR2)

INFO: Verifying schema objects...

INFO: Schema objects verified.

Hi Steve,

You can rerun the upgrade, but the error may occur again.

Please, could you post (or send me to pkovacs at chemaxon dot com) the /opt/ChemAxon/jchem-5.4.1.2/cartridge/upgrade-plan.txt file and the jcart0.log file from jchem/cartridge directory (the directory where you started the upgrade program)?

Thanks

Peter

Hello Peter,

Thanks for the quick reply.  I have emailed you the files requested, let me know if you don't get them.

Steve

Steve,

Thanks for the files.

It appears that the JC_TVERSKY operator and the supporting Tversky_Func* functions had been removed from the JCHEM owner schema. Can you confirm this? Or is it that they are there only the upgrader program doesn't detect them?

Have you tried the upgrade a second time?

Thanks

Peter

After more investigation, I've found three relevant factors:

1. Your JChem owner doesn't have the tversky operator and supporting functions

2. There is a bug in the JCC upgrader which consists of it failing to abort the upgrade on an error in upgrading a given database object

3. There is a second bug in the JCC upgrader consisting of not executing the upgrade steps for some database objects in the correct order of dependency (upgrade dependees first, dependants second).

This gives the following:

1. JCC version 5.2.6 already includes the tversky operator and supporting functions, however it is not impossible that you started from a JCC version prior to 5.2.6 did upgrades and the tversky things didn't made it into your JChem schema due to a bug in the upgrader program of some prior JCC version. All I could check (using a reasonable amount of effort) was that a clean install of JCC 5.2.6 (as opposed to a version 5.2.6 instance which is the result of upgrades from JCC versions prior to 5.2.6, which I didn't/couldn't check) should be cleanly upgradable to 5.4.1.2.

2. Due to the failure to abort (finding #2 above), those database objects on which the failing operator depends will have been installed/upgraded during the first upgrade, so a second attempt at upgrading will be successful.

Both bugs (findings #2 and #3 above) will be fixed in JChem version 5.5.1.

Regards,

Peter

Thanks Peter.

This all sounds reasonable and was pretty much what I'd thought was happening.  Two questions however :

1) I'm almost positive that this JChem was installed from 5.2.6 'clean', however the only tversky related objects I can find are TVERSKY_FUNC, TVERSKY_FUNCC and TVERSKY_FUNCB functions (and synonyms).  Comparing to another JChem installation it seems that this (problem) installation is missing the JC_TVERSKY operator.  This is a standard x86_64 Linux installation and I don't recall any errors (but it was a while ago!).  Can you think of any reason why they would be missing?

2) Given that they are missing I suppose it is possible that this may occur elsewhere.  If this is the case does it 'matter' if we are not using any tversky functions?  (i.e can the errors be ignored).

Thanks,

Steve

Steve,

The TVERSKY_FUNC* functions most probably exist as a result of the first upgrade attempt.

I cannot think of any reason why these functions and operators are missing from your JChem onwer schema.

If you don't use the tversky operators/functions, you can ignore these error messages. You actually don't have to use them, because the jc_dissimilarity operator with the dissimilarityMetric option provides the same functionality.

Peter

Hi Peter,

Thanks for this.  Is there anywhere I can look in the cartridge folder (or elsewhere) to see if this 5.2.6 JChem was upgraded from an earlier installation?  Or even the original install logs?

Steve

Hi Steve

We currently don't track the upgrade path.

Peter

Hi Steve,

JChem 5.5.1 has been released with fixes promised here.

Peter

Hi Steve,

JChem 5.5.1 has been released with fixes promised here.

Peter

Great, thanks Peter.