Cartridge upgrade problem - IdNotFoundException

Hi,





I'm upgrading a JChem cartridge installation from 5.1.0.2 to 5.1.0.4 on Oracle 10g on Linux by running the upgrade-evol.sh program. However, i get the following error during the installation and it fails...





INFO: JC_TRANSFORM successfully processed.


INFO: JC_MOLWEIGHTB successfully processed.


INFO: Upgrading INDEXTYPEs...


INFO: JC_IDXTYPE successfully processed.


java.sql.SQLException: ORA-29532: Java call terminated by uncaught Java exception: oracle.aurora.vm.IdNotFoundException: -1 is not the number of a user or role





        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)


        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)


        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)


        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)


        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:207)


        at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:957)


        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1170)


        at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1696)


        at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1662)


        at chemaxon.jchem.cartridge.install.Schema.executeSql(Schema.java:1441)


        at chemaxon.jchem.cartridge.install.InstallUtil.grantSocketPermissions(InstallUtil.java:118)


        at chemaxon.jchem.cartridge.install.UpgradeCmdLine.rebuildIndices(UpgradeCmdLine.java:252)


        at chemaxon.jchem.cartridge.install.UpgradeCmdLine.upgrade(UpgradeCmdLine.java:188)


        at chemaxon.jchem.cartridge.install.UpgradeCmdLine.upgrade(UpgradeCmdLine.java:36)


        at chemaxon.jchem.cartridge.install.UpgradeCmdLine.main(UpgradeCmdLine.java:293)


+ set +x








My cartridge owner is JCHEM, the role for user privileges is JCHEM_ROLE, the DBA login is SYS AS SYSDBA, and everything else is default. I've made sure that the schema owner and role are entered in upper case. Can you help with this ?





Many thanks





Alistair

Hi Alistair,





The quick, practical approach --which I'd also suggest to take-- is to leave it at that. The upgrade of the JChem Cartridge machinery in Oracle has apparently been successfully completed. What has been left to do is basically the rebuilding of the jc_idxtype indexes. In the 5.1.4 version, INDEX REBUILD on jc_idxtype indexes has a bug (fixed in the upcoming 5.1.5 release: http://www.chemaxon.com/forum/ftopic4472.html) and you would have had to drop and re-create the indexes, even if the upgrade had been completed successfully. I'd suggest to just terminate the upgrade process and proceed with dropping and re-creating the jc_idxtype indexes.





As to the concrete problem you run into: it appears as though SYS wasn't able to grant themselves the privileges required to rebuild the indexes. Perhaps, they don't need any... To be entirely honest I've tested the upgrade mainly with SYSTEM, so I have to re-check this particular scenario.





Let me know if you run into any more problems.





Thanks


Peter

Alistair,





The ORA-29532 problem you reported will be fixed in the next JChem release.





Thanks





Peter

Great, thanks Peter, i've dropped and recreated the indexes and the queries run ok. When i call jchem_core_pkg.getEnvironment it tells me i'm running JChem 5.1.4 - very exciting!





Now i'm just trying to confirm if the query is actually using the index as it doesn't seem to run faster than without. I have a table with a column of SMARTS, i have built an index using the tableType=queries parameter. When i run a jc_contains or jc_compare against the table, it runs ok, but when i examine the explain plan it tells me it's doing a full table scan (TABLE ACCESS FULL) - i don't know if i should get this message anyway because of the way the JChem indexing works ?





I've tried to set up cost estimation by running the assoc_stats.sql script as the JChem owner, but i get the error ORA-29820: the statistics type is not present





Any ideas ? Am i ok to use jc_contains and jc_compare against a column on SMARTS ?





Many thanks





Alistair

Alistair,





After replaying the upgrade from JChem 5.1.2 to JChem 5.1.4 (with SYS AS SYSDBA) then dropping and recreating indexes, query plans show the domain index being used in my environment. Please, could you post the exact SQL you are using?





Regarding Oracle error ORA-29820: please, load into the JChem owner's schema the jchem_opti_pkg package to be found in cartridge/jchem_opti.sql ...





It is not supposed to make any difference in domain index usage whether the search is on SMARTS or on SMILES. One thing that can be more or less easily missed is that operators (such as jc_compare and jc_contains) should be used whereever possible as oposed to their function counterparts (jcf_compare, jcf_contains). Operators can do domain index scan, functions cannot.





Thanks





Peter

Thanks Peter,





I have a table called POSSIBLE_REACTIONS, with a column of SMARTS called REAL_SMARTS. I have built the index using:





CREATE INDEX TESTINDEX ON POSSIBLE_REACTIONS(REAL_SMARTS) INDEXTYPE IS JCHEM.JC_IDXTYPE PARAMETERS('tableType=queries');





Then i run the queries:





SELECT * FROM POSSIBLE_REACTIONS WHERE JC_CONTAINS('c1ccccc1CCN',REAL_SMARTS) = 1





or





SELECT * FROM POSSIBLE_REACTIONS WHERE JC_COMPARE('c1ccccc1CCN',REAL_SMARTS,'t:s') = 1





Both queries run but neither seem to use the index. Any ideas ?





The jchem_opti.sql seemed to work a treat, i've gathered the stats so i'll take a look at running the calibrator next.





Many thanks





Alistair

Alistair,





Isn't the following statement that you really want to execute:

Code:

SELECT * FROM POSSIBLE_REACTIONS WHERE JC_COMPARE(REAL_SMARTS,'c1ccccc1CCN','t:r') = 1

? Notice the params being swapped and the search type changed to "superstructure".





(Domain index scan will be used only if the first parameter to the search operators is a column.)








Thanks





Peter

Bingo! That works perfectly! I had tried swapping the parameters around but forgot to change to a superstructure search.





Many thanks for you help





Alistair