Structure search problem after 5.3.3->5.3.5 upgrade...

Hello Tim & Co,

I upgraded our JChem installation from 5.3.3 to 5.3.5 just now and the cartridge upgrade seemed to go OK according to the script output (attached). Post upgrade, general text searching within IJC 5.3.4 is OK but structure searching (error text attached) gives an immediate error - I haven't see this one before - any ideas?

KR & TIA,

Andy

Which version of IJC are you using for this?

I suspect you are using 5.3.1, and need to upgrade to 5.3.4 so that the JChem tables are compatible with the cartridge version you upgraded to?

Tim

Definitely IJC 5.3.4, on Mac & PC...

Hi Andy,

could you check if you have a table called JCHEMPROPERTIES_CR (or whatever name you gave the property table plus _CR) and see if it has a CACHE_ID column.

Also, for all structure tables, and JChem cartridge indexes look for the table named *_UL and see if it has a CACHE_ID column,

Something seems to be wrong with one or the other of these.

Tim

 

Hi Andy,

The problem is that the upgrade script fails to recognize that indices need to be upgraded. You have to manually rebuild the indices:

ALTER INDEX <index-name> REBUILD

This bug will be fixed in the next minor release.

Sorry for the inconvenience.

Peter

Thanks for the replies again.

Tim:   The JCHEMPROPERTIES_CR table exists and it has a CACHE_ID field.  Most of the *_UL tables have a CACHE_ID field but some don't, can't see a pattern at this stage...

Peter:  Which indexes are we talking about - all the indexes in the IJC schema?

 

KR & TIA,

 

Andy

Which indexes are we talking about - all the indexes in the IJC schema?

All the jc_idxtype indices.

Peter

OK, I'll let you know what happens...

I did:

     select 'alter index '||index_name||' rebuild;' from dba_indexes where index_name like 'JC%';

and ran the resulting series of 77 "alter index ... rebuild" commands, all succeeded.

Unfortunately the structure searching still gives the same error, as attached.

KR,

Andy

Andy,

Things somehow don't appear to add up here. JChem 5.3.3 already included the cache-id feature. JChem Cartridge 5.3.3 would throw the same error during search, if the CACHE_ID column was missing. Did you ever use JChem Cartridge 5.3.3 for searching?

Thanks

Peter

Thanks for your patience Peter.

Yes, structure searching was fine at 5.3.3 - I trained somebody in IJC usage on Tuesday.

How difficult would it be to do a complete reinstall of JChem & Cartridge on the server?

KR,

Andy

Andy,

Please, could you post the output of the following statement:

select jchem_core_pkg.getenvironment from dual;

?

Silly question, but...: Do you have multiple JChem Cartridge installations? Is there any chance that you connect to a different one with sqlplus (or whatever you use to execute bare-bones SQL commands) than with IJC?

Thanks

Peter

We only have 1 cartridge installation, here is the select output:

 

SQL> select jchem_core_pkg.getenvironment from dual;

GETENVIRONMENT

-------------------------------------------------------

Oracle environment:

Oracle Database 10g Release 10.2.0.4.0 - Production

PL/SQL Release 10.2.0.4.0 - Production

CORE    10.2.0.4.0      Production

TNS for 32-bit Windows: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 - Production

JChem Server environment:

Java VM vendor: Sun Microsystems Inc.

Java version: 1.6.0_14

Java VM version: 14.0-b16

GETENVIRONMENT

-------------------------------------------------------

JChem version: 5.3.5

JChem Index version: 5030300

JDBC driver version: 11.1.0.7.0-Production

Off-line discussion and further analysis led to the current "working hypothesis" that materialized views might not behave as expected during upgrade and/or index rebuild. We are checking this hypothesis. (FS#11477)

Peter

I have tested the upgrade both from 5.3.1 to 5.3.6 and from 5.3.3 to 5.3.6 using a simple materialized view. Both upgrade appeared to go with the view as expected (without problem).

I suspect that the problem might be more due to (a) inconsistent entries in the JChemProperties table unrelated to materialized views and (b) JChem current lack of robustness in face of such inconsistencies -- than the upgrade strictly taken.

Peter