JCART error on SMILES update.

Hi, 

I am recieving an JCART error when I update a non-canonical smiles to an equivalent JCHEM canonicalized smiles (Just trying to canonicalize the smiles in the column without changing the structure).

The column I am updating was indexed with:

EXECUTE IMMEDIATE 'CREATE INDEX jc_idx ON structure(s_smiles) INDEXTYPE IS jchem.JC_IDXTYPE PARAMETERS(''TABLESPACE=structure_indexes,tdf=y,haltOnError=nf'')';

ORA-29877: failed in the execution of the ODCIINDEXUPDATE routine

ORA-29532: Java call terminated by uncaught Java exception: java.sql.SQLException: Exhausted Resultset

ORA-06512: at "JCHEM.JCHEM_CORE_PKG", line 278

ORA-06512: at "JCHEM.JC_IDXTYPE_IM", line 626 occured while testing smiles "<SMILES WAS HERE>" 

This error is not unique to one smiles, it is occuring for every smiles in the DB that I try and canonicalize.

Thank you,
~Mike 

Hi,

Can you copy in the result of this command to see which version you are using?

select jchem_core_pkg.getenvironment() from dual;

And can you send the corresponding log files for JChem Cartridge? They are located in the jchemsite/cartridge/logs folder. We need the jcart0.log (or jcart1.log, etc.) file and the trace0.log 
 (or trace1.log, etc.) file.

Thank you,
Tamas

SQL> select jchem_core_pkg.getenvironment() from dual;

JCHEM_CORE_PKG.GETENVIRONMENT()
--------------------------------------------------------------------------------
Oracle environment:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

JChem owner: JCHEM

JChem Server environment:
Java VM vendor: Oracle Corporation

JCHEM_CORE_PKG.GETENVIRONMENT()
--------------------------------------------------------------------------------
Java version: 1.7.0_04
Java VM version: 23.0-b21
JChem version: 5.11.3
JChem Index version: 5110000
JDBC driver version: 11.1.0.7.0-Production

===================================================================

# ls -al
total 24
drwxr-xr-x 2 oracle dba 4096 Mar 13 22:14 .
drwxr-xr-x 7 oracle dba 4096 Mar 13 22:12 ..
-rw-r--r-- 1 oracle dba 397 Mar 13 22:14 jcart0.log
-rw-r--r-- 1 oracle dba 1032 Mar 13 22:14 jcart1.log
-rw-r--r-- 1 oracle dba 397 Nov 9 13:58 jcart2.log
-rw-r--r-- 1 oracle dba 122 Nov 9 13:58 jcart3.log
-rw-r--r-- 1 oracle dba 0 Mar 13 22:14 trace0.log
-rw-r--r-- 1 oracle dba 0 Mar 13 22:14 trace1.log
-rw-r--r-- 1 oracle dba 0 Mar 13 22:14 trace1.log.lck
-rw-r--r-- 1 oracle dba 0 Nov 9 13:58 trace2.log
-rw-r--r-- 1 oracle dba 0 Nov 9 13:58 trace3.log
-rw-r--r-- 1 oracle dba 0 Nov 9 13:58 trace3.log.lck
[db01.ambryx.com:oracle:jchem] pwd=/opt/chemaxon/jchem5/cartridge/logs
# cat jcart0.log
[main/1]: 2013-03-13 22:14:14.915 chemaxon.jchem.cartridge.server.Bootstrapper main
INFO: Server control process started
[main/1]: 2013-03-13 22:14:15.463 chemaxon.jchem.cartridge.server.Bootstrapper createClasspath
INFO: Using classpath: /opt/chemaxon/jchem5/lib/jchem.jar
[main/1]: 2013-03-13 22:14:16.939 chemaxon.jchem.cartridge.server.Bootstrapper serverControl
INFO: Server process stop-ed.
[db01.ambryx.com:oracle:jchem] pwd=/opt/chemaxon/jchem5/cartridge/logs
# cat jcart1.log
[main/1]: 2013-03-13 22:14:16.493 chemaxon.jchem.cartridge.rmi.impl.AdminImpl mainNoSysExit
INFO: Connecting to localhost:1099
[main/1]: 2013-03-13 22:14:16.915 chemaxon.jchem.cartridge.rmi.impl.AdminImpl main
SEVERE: Unable to connect to JChem Server at localhost:1099
java.lang.Exception: Unable to connect to JChem Server at localhost:1099
at chemaxon.jchem.cartridge.rmi.impl.AdminImpl.getAdminServer(AdminImpl.java:396)
at chemaxon.jchem.cartridge.rmi.impl.AdminImpl.mainNoSysExit(AdminImpl.java:448)
at chemaxon.jchem.cartridge.rmi.impl.AdminImpl.main(AdminImpl.java:465)
Caused by: java.lang.Exception: Problem connecting to JChemServer: rmi://localhost:1099: RemoteException occurred in server thread; nested exception is:
chemaxon.jchem.cartridge.rmi.VersionMismatchException: JChem version mismatch: 5.10.0 in Oracle, 5.11.3 in RMI Server
at chemaxon.jchem.cartridge.rmi.client.RmiDirectory.locate(RmiDirectory.java:100)
at chemaxon.jchem.cartridge.rmi.impl.AdminImpl.getAdminServer(AdminImpl.java:390)
... 2 more
[db01.ambryx.com:oracle:jchem] pwd=/opt/chemaxon/jchem5/cartridge/logs
# cat trace0.log
[db01.ambryx.com:oracle:jchem] pwd=/opt/chemaxon/jchem5/cartridge/logs
# cat trace1.log
[db01.ambryx.com:oracle:jchem] pwd=/opt/chemaxon/jchem5/cartridge/logs
#

Hi, I tried running "server.sh stop" last night and it could not connect to JCART on 1099, so I tried editing config/jchem.properties and setting "jchem.server.host=db01" to "jchem.server.host=localhost" and that did not work either.

Here is the terminal session:

# stopjchem
+ /jcode/11/jdk/jdk1.7/bin/java -Djava.util.logging.config.class=chemaxon.jchem.cartridge.util.LoggingConfigurator -Dchemaxon.jchem.cartridge.config.file=conf/jcart.properties -Djava.awt.headless=true -classpath ./../lib/jchem.jar -XX:-OmitStackTraceInFastThrow chemaxon.jchem.cartridge.server.Bootstrapper stop
INFO: Server control process started
INFO: Using classpath: /opt/chemaxon/jchem5/lib/jchem.jar
INFO: Connecting to db01:1099
SEVERE: Unable to connect to JChem Server at db01:1099
Exiting with exit code 1...
INFO: Server process stop-ed.
+ set +x

Then I tried editing config/jchem.properties and setting:

# The host name of the JChem Server, as seen (pingable) from the Oracle host.
# In a single-host setup, it is "localhost" or 127.0.0.1.
jchem.server.host=localhost

Then I did a
# stopjchem
+ /jcode/11/jdk/jdk1.7/bin/java -Djava.util.logging.config.class=chemaxon.jchem.cartridge.util.LoggingConfigurator -Dchemaxon.jchem.cartridge.config.file=conf/jcart.properties -Djava.awt.headless=true -classpath ./../lib/jchem.jar -XX:-OmitStackTraceInFastThrow chemaxon.jchem.cartridge.server.Bootstrapper stop
INFO: Server control process started
INFO: Using classpath: /opt/chemaxon/jchem5/lib/jchem.jar
INFO: Connecting to localhost:1099
SEVERE: Unable to connect to JChem Server

Exiting with exit code 1...
INFO: Server process stop-ed.
+ set +x
[db01.ambryx.com:oracle:jchem] pwd=/opt/chemaxon/jchem5/cartridge
# ssh: connect to host server.sh port 22: Connection timed out
ssh: connect to host server.sh port 22: Connection timed out

 
and still can not connect to JCART.

Hi

JCHEM_CORE_PKG.GETENVIRONMENT()


--------------------------------------------------------------------------------
Java version: 1.7.0_04
Java VM version: 23.0-b21
JChem version: 5.11.3

It appears that you have JChem Cartridge version 5.11.3 installed, but

chemaxon.jchem.cartridge.rmi.VersionMismatchException: JChem version mismatch: 5.10.0 in Oracle, 5.11.3 in RMI Server

you also have a version 5.10.0 package lying around which you are now trying to use. This certainly explains your inability to manage the JChem Cartridge sever. (You can't manage a JCC server using a JChem package of different  version.) One way to check the version of a package is to execute

java -jar jchem/lib/jchem.jar

.

Peter

Hi,

Sorry for the confusion.  Version mismatch was just me not paying attention.  The other error is the real problem.   I just used an old version of jcart during the shutdown, which is why it failed.

MORE IMPORTANT:

We are still seeing the original error from JCART even after restarting.  (See 1st post in thread)

I am recieving an JCART error when I update a non-canonical smiles to an equivalent JCHEM canonicalized smiles (Just trying to canonicalize the smiles in the column without changing the structure). 

ORA-29877: failed in the execution of the ODCIINDEXUPDATE routine
ORA-29532: Java call terminated by uncaught Java exception: java.sql.SQLException: Exhausted Resultset
ORA-06512: at "JCHEM.JCHEM_CORE_PKG", line 278
ORA-06512: at "JCHEM.JC_IDXTYPE_IM", line 626 

From the same time period, trace1.log  gives:

WARNING: 2013-03-18 12:23:20.231 [9647549, SENOBASE, SENOBASE]: java.sql.SQLException: Exhausted Resultset
at oracle.jdbc.driver.OracleResultSetImpl.getInt(Native Method)
at chemaxon.jchem.cartridge.JCartDml.getCdIdFromRowid(JCartDml.java)
at chemaxon.jchem.cartridge.JCartDml.updateMolIndexTable(JCartDml.java)
at chemaxon.jchem.cartridge.JCartDml.updateMolIndexTable(JCartDml.java)

None of the other logs (jcart0.log - jcart3.log, or trace0.log - trace3.log) have any information from that time period.... and yes, I was looking at the corrrect log files in /opt/chemaxon/jchem5.11.3 ;-).  

[db01.ambryx.com:oracle:discover] pwd=/opt/chemaxon/jchem/cartridge/logs
# java -jar ../../lib/jchem.jar
JChem version: 5.11.3 (revision: 249801 internal id:5.11.3_b58)
Table version: 5110000 (for determining if recalculation is necessary)

To fully explain the version mismatch, if you are interested:  We keep old versions of jchem around and we point a symbolic link at the current version, which normally works great, as long as I am paying attention to what I am doing.  The issue was I did a "cd /opt/chemaxon/jchem5", when I should have just done a "cd /opt/chemaxon/jchem" which made it so I was in the wrong directory, and lead to the verasion mismatch errors.  See the directory list below, and that will all make sense. 

# cd /opt/chemaxon/
[db01.ambryx.com:oracle:discover] pwd=/opt/chemaxon
# ls -al
total 24
drwxr-xr-x 5 oracle dba 4096 Mar 14 20:36 .
drwxr-xr-x 8 root root 4096 Jun 15 2012 ..
lrwxrwxrwx 1 oracle dba 11 Nov 9 14:08 jchem -> jchem5.11.3
drwxr-xr-x 13 oracle dba 4096 Sep 19 16:36 jchem5
drwxrwxr-x 13 oracle dba 4096 Oct 19 07:13 jchem5.11.3
drwxr-xr-x 2 oracle dba 4096 Mar 14 20:36 zipfiles

Hi Mike,

Thank you for the diagnostic information and the clarification. Based on the stack trace from trace1.log, I can reconstruct part of what is happening.

I am going to give you some potentially helpful background information in the following two paragraphs.

When you index a structure column (of the base table) with the jc_idxtype index type, we create a separate table (index table) with the index data. One row in the index table holds the index data for exactly one structure in the structure column of the base table. The rows in the index table are linked to the structures through the ROWID of the row in the base  table in which the structure is stored. The index table has a column called RID holding the ROWIDs of corresponding rows in the base table. (Please, see the slide #20 of this presentation for a visualization this configuration.)

When you update a JCC-indexed structure in the base table, Oracle tells JCC about this and gives JCC the new structure along with the ROWID of the base table in which row the structure being updated was stored. We compute the index data for the new structure and use the ROWID to know which index data in the index table has to updated with the new index data.

The immediate cause of the "exhausted resultset" error you are observing is that we cannot find the ROWID in the index table. I can think of two possible conditions under which this can happen. (1) There are no rows in the index table for some reason. (2) The ROWIDs of the base table have been changed "autonomously" from the index table outside JCC's control.

I can't give any scenario which would result into the first condition -- other than the rows have been somehow deleted. The second condition may be the result of restoring the base table from a backup. (This is why recommend to recreate the JCC index after structure tables have been restored from backup.) Other reasons for the second condition may be more esoteric: moving tables between databases (and even between tablespaces?); using table types which require UROWIDs instead of the tradition ROWIDs, etc.

If none of the above scenarios ring a bell to you, the next step in finding out the cause of the issue could be to check the content of the index table: do ROWIDs of the base table have matching values in the RID column of the index table. The index table name is currently formed using the following pattern (liable to change without notice in future JCC versions): <jcc-index-name>_jcx.

Let me know if this doesn't help.

Peter

Helps a ton!

Did a :

select 'index', count(*) from jc_idx_jcx

union

select 'index flat', count(*) from jc_idx_flat_jcx

union

select 'base_table', count(*) from structure