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

User 2ddf1d2229

14-07-2010 15:43:18

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

ChemAxon fa971619eb

14-07-2010 16:12:20

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

User 2ddf1d2229

14-07-2010 16:43:16

Definitely IJC 5.3.4, on Mac & PC...

ChemAxon fa971619eb

15-07-2010 07:02:26

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


 

ChemAxon aa7c50abf8

15-07-2010 07:46:48

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

User 2ddf1d2229

15-07-2010 21:52:04

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

ChemAxon aa7c50abf8

15-07-2010 21:55:24

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

All the jc_idxtype indices.


Peter

User 2ddf1d2229

16-07-2010 08:02:11

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

User 2ddf1d2229

16-07-2010 09:01:11

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


ChemAxon aa7c50abf8

16-07-2010 10:41:10

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

User 2ddf1d2229

16-07-2010 11:15:12

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

ChemAxon aa7c50abf8

16-07-2010 11:48:50


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

User 2ddf1d2229

16-07-2010 12:11:07

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


ChemAxon aa7c50abf8

18-07-2010 10:52:16

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

ChemAxon aa7c50abf8

27-07-2010 20:23:16

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