ChemAxon Forum Archive » Storage & search: JChem Base and JChem Oracle Cartridge

Inserting SMILES to JCART indexed table is hanging:

User 7f33ec9a5c

22-05-2013 22:29:08

When attempting an insert statement, oracle just hangs on the insert statement.  


A  trace of the session produces the following:



Trace file /home/oracle/11/admin/discover/udump/diag/rdbms/discover/discover/trace/discover_ora_15098.trc


Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production


With the Partitioning, OLAP, Data Mining and Real Application Testing options


ORACLE_HOME = /jcode/11


System name:    Linux


Node name:      db01


Release:        2.6.18-308.20.1.el5


Version:        #1 SMP Tue Nov 6 04:38:29 EST 2012


Machine:        x86_64


VM name:        VMWare Version: 6


Instance name: discover


Redo thread mounted by this instance: 1


Oracle process number: 77


Unix process pid: 15098, image: oracle@db01


 


 


*** 2013-04-23 13:40:51.323


*** SESSION ID:(337.22363) 2013-04-23 13:40:51.323


*** CLIENT ID:() 2013-04-23 13:40:51.323


*** SERVICE NAME:(discover) 2013-04-23 13:40:51.323


*** MODULE NAME:(WhatIsIt v3.0.9) 2013-04-23 13:40:51.323


*** ACTION NAME:(G:\Inetpub\wwwroot\I+A\WhatIsItII) 2013-04-23 13:40:51.323


 


Exception in thread "Root Thread" java.rmi.ServerException: RemoteException occurred in server thread; nested exception is:


        chemaxon.jchem.cartridge.rmi.StructureFormatException: Unmatched ring closure number 2 in SMILES string S9248


        at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:353)


        at sun.rmi.transport.Transport$1.run(Transport.java:177)


        at sun.rmi.transport.Transport$1.run(Transport.java:174)


        at java.security.AccessController.doPrivileged(Native Method)


        at sun.rmi.transport.Transport.serviceCall(Transport.java:173)


        at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:553)


        at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:808)


        at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:667)


        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)


        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)


        at java.lang.Thread.run(Thread.java:722)


        at sun.rmi.transport.StreamRemoteCall.exceptionReceivedFromServer(StreamRemoteCall.java)


        at sun.rmi.transport.StreamRemoteCall.executeCall(StreamRemoteCall.java)


        at sun.rmi.server.UnicastRef.invoke(UnicastRef.java)


        at chemaxon.jchem.cartridge.rmi.impl.MiscellaniousImpl_Stub.standardize(MiscellaniousImpl_Stub.java)


        at chemaxon.jchem.cartridge.JFunctions.standardize(JFunctions.java)


Caused by: chemaxon.jchem.cartridge.rmi.StructureFormatException: Unmatched ring closure number 2 in SMILES string S9248


        at chemaxon.jchem.cartridge.rmi.impl.RmiExceptionHandler.checkToConvert(RmiExceptionHandler.java:84)


        at chemaxon.jchem.cartridge.rmi.impl.RmiExceptionHandler.handleException(RmiExceptionHandler.java:78)


        at chemaxon.jchem.cartridge.rmi.impl.RmiExceptionHandler.handleError(RmiExceptionHandler.java:56)


        at chemaxon.jchem.cartridge.rmi.impl.MiscellaniousImpl.standardize(MiscellaniousImpl.java:513)


        at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source)


        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)


        at java.lang.reflect.Method.invoke(Method.java:601)


        at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:322)


        at sun.rmi.transport.Transport$1.run(Transport.java:177)


        at sun.rmi.transport.Transport$1.run(Transport.java:174)


        at java.security.AccessController.doPrivileged(Native Method)


        at sun.rmi.transport.Transport.serviceCall(Transport.java:173)


        at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:553)


        at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:808)


        at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:667)


        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)


        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)


        at java.lang.Thread.run(Thread.java:722)



ChemAxon aa7c50abf8

22-05-2013 22:49:34

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


select jchem_core_pkg.getenvironment from dual;


Please, could you also post the CREATE INDEX statement used to create the JChem index involved?


Thanks


Peter

User 7f33ec9a5c

22-05-2013 23:49:32


Connected to:


Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production


With the Partitioning, OLAP, Data Mining and Real Application Testing options


 


SQL> select jchem_core_pkg.getenvironment from dual;


 


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


 


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


User 7f33ec9a5c

22-05-2013 23:51:23


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


        jchem.privman_pkg.grants_on_jcidx('PUBLIC', 'SENOBASE', 'JC_IDX', 1, 1, 1, 1);


ChemAxon aa7c50abf8

23-05-2013 12:44:08

Thank you!


Please, could you also post an INSERT statement with wich the problem can be reproduced?

User 7f33ec9a5c

23-05-2013 16:31:14

The insert statement is below, unfortunately, I can't disclose the values of the bind variables, which is probably what you need. :-(


Sorry,
~mike


-- update the structure table



       insert into structure (s_structure_id,


                             s_smiles,


                             s_mw,


                             s_exactmass,


                             s_formula,


                             s_exactmass_4_ms,


                             s_flat_smiles)


        values (vStructureID,


                vStdSMILES,


                vAMW,


                vExactMass,


                vMF,


                vExactMass4MS,


                vFlatSmiles);


 


 


ChemAxon aa7c50abf8

23-05-2013 17:09:31

Thank you, Mike!


Is the structure you mentioned in your first post the one being inserted during the hanging INSERT: S9248 ?


If it is, I think the hang may be due to some Oracle-level resource contention.


If it isn't, the hang is probably due  to some algorythmical bottleneck in calcuating the structures index data (such tautomerization).


Thanks


Peter