Index help, structure(s) could not be indexed.

User 952e1d9361

06-08-2009 11:49:37

Hi,


I am creating an index using this SQL :


create index struct_jchem_idx on CHEM_TAB (structure)   


indextype is jchem.jc_idxtype  


parameters('tableType=anyStructures,fp_bit=2,pat_length=6,fp_size=24,haltOnError=n,duplicateFiltering=n')


The table has a number of NULL structures and may have duplicates which is not something I am able to control at this time, so I have included the "24,haltOnError=n,duplicateFiltering=n" options to try and 'ignore' this and create the index.  However running the SQL generates the following error : 








SQL Error: ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine

ORA-29532: Java call terminated by uncaught Java exception: java.rmi.ServerException: RemoteException occurred in server thread; nested exception is: 

java.rmi.RemoteException: 10 structure(s) could not be indexed. Please, check the JChem Server logs.

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

ORA-06512: at "JCHEM.JC_IDXTYPE_IM", line 17

29855. 00000 -  "error occurred in the execution of ODCIINDEXCREATE routine"

*Cause:    Failed to successfully execute the ODCIIndexCreate routine.



However I cannot seem to see any reference to errors in the log files.  This may be due to my logging setup though which I have not changed from the default - is there something I need to change to record these errors?


JChem is 5.2, Linux, Oracle 10.2.0.4.



Thanks,


Steve H


P.S - sorry about the formatting, I can't seem to add line breaks and change fonts using my browser (Safari 4, Mac OS 10.5).

ChemAxon aa7c50abf8

06-08-2009 16:12:37

Hi Steve,


Did you look in the jchem/cartridge/logs directory? For example, for the following sequence


create table t1(id number primary key, structure varchar2(4000))
insert into t1 values(1, 'c1ccccc1');
insert into t1 values(2, 'zazi');
insert into t1 values(3, 'c1ccccc1Br');
commit;

create index jcxt1 on t1(structure) indextype is pkovacs_52branch.jc_idxtype
parameters('tableType=anyStructures,fp_bit=2,pat_length=6,fp_size=24,haltOnError=n,duplicateFiltering=n')


I get a similar error message as you and I find in the jchem/cartridge/logs/jcart1.log file the following entry (for the structure zazi):


Aug 6, 2009 6:01:58 PM chemaxon.jchem.cartridge.servlets.indexing.structhandlers.CalculateInsertHandler handleException
SEVERE: Cannot recognize format (base64 or chime?)
Unrecognized file contents:
zazi: ROWID=AAAgXhAAEAACOBMAAB, zazi
Let me know if this helps.


Regards,


Peter


 


 

User 952e1d9361

06-08-2009 18:50:28

Hi Peter,


Thanks for this, and this is what I would expect to see but the log seems to just 'stop' after the server has started :



06-Aug-2009 13:32:57 chemaxon.jchem.cartridge.server.Bootstrapper serverControl


INFO: Server process start-ed.


Is the last line in there.

Is there some logging 'level' that I need to set somewhere to ensure this gets written out?


Thanks,

Steve


ChemAxon aa7c50abf8

06-08-2009 20:06:09

Steve,


There must be at least one more log file in the jchem/cartridge/logs directory which contains this sequence:


INFO: Listening on network interface


The log file you're quoting is generated by the bootstrapper process which starts the actual JChem Server process. The information you're interested in is output to the second log file by the JChem Server process. (The bootstrapper's logfile is most probably the jcart0.log file, the JChem Server's log file is the typically the jcart1.log file.)


Regarding logging levels: the information you're interested in is logged at SEVER/ERROR level which is turned on by default. Please, attach the jchem/cartridge/conf/logging.properties file, so I can check and see if these log items have inadvertently been filtered out.


Thanks


Peter

User 952e1d9361

06-08-2009 20:37:26

Thanks Peter,


I've checked jcart0, 1 and 2.log and can't see anything useful (thanks for the tip about the different purposes of the files, I had presumed they were just rotated).


Properties file is attached.


Steve


 

ChemAxon aa7c50abf8

07-08-2009 18:25:44

Steve,


Have you restarted JChem Server since you created the index? After restart, the log files get overwritten.


Cheers,


Peter

User 952e1d9361

07-08-2009 20:02:53

Hi Peter,


I restarted it to see if errors got dumped to the console, and to get 'clean' log files, yes.  But this was done prior to repeating the create index command so any errors should have been recorded.


Thanks,


Steve


 

ChemAxon aa7c50abf8

08-08-2009 18:00:28

Steve,


We could try and see what happens if we increase logging verbosity compared to the default. After removing the comment ('#') from the beginning of the line


# chemaxon.jchem.level = FINEST


in the logging.properties file (I am attaching the modified logging.properties file) and executing the sample commands above I get the log output you can find in the attached jcart1.log file. If you have a larger table, you'll also get a log item after each 100th indexed structure stating the progress.


If, after indexing with the modified the logging.properties file, you can't see any useful information in the log files, please send them to me for inspection. (Please, restart JChem Server after changing the logging properties to make them effective.)


Thanks


Peter

User 952e1d9361

08-08-2009 22:11:42

Thanks Peter, I was being a bit stupid and looking in the wrong log files (I have two JChems running on this server...)


Sorry to have wasted your time.


Steve


 




User 952e1d9361

17-11-2009 12:50:10

Hello again,


I'd like to resurrect this thread if possible.  I am getting the error described above again and have tracked down the errors but am not getting any clues as to why 3 structures can't be indexed :


17-Nov-2009 12:19:54 chemaxon.jchem.cartridge.servlets.indexing.structhandlers.CalculateInsertHandler handleException


 


SEVERE: Problem indexing structure with ROWID AABYVmAAEAAD5p2AAF: 0


17-Nov-2009 12:20:01 chemaxon.jchem.cartridge.servlets.indexing.structhandlers.CalculateInsertHandler handleException


SEVERE: Problem indexing structure with ROWID AABYVmAAEAAD6WBAAB: 0


17-Nov-2009 12:20:01 chemaxon.jchem.cartridge.servlets.indexing.structhandlers.CalculateInsertHandler handleException


SEVERE: Problem indexing structure with ROWID AABYVmAAEAAD6WBAAC: 0



Just a fairly generic "Problem indexing structure" message.



Is there a way I can 'pre-check' to see if a structure can indexed before issuing the create index statement.  Even though I have set haltOnError=n the create index still fails and is left in an unusable state requiring intervention to get it working again.



Many thanks,


Steve H


 


 


JCHEM.JCHEM_CORE_PKG.GETENVIRONMENT()                                


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


Oracle environment:                                                  


Oracle Database 10g Release 10.2.0.4.0 - 64bit Production            


PL/SQL Release 10.2.0.4.0 - Production                               


CORE 10.2.0.4.0 Production                                       


TNS for Linux: 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_13                                               


Java VM version: 11.3-b02                                            


JChem version: 5.2.0                                                 


JChem Index version: 5020005                                         


JDBC driver version: 11.1.0.7.0-Production 



 


 

ChemAxon aa7c50abf8

17-11-2009 14:13:44

Hi Steve


The expected behaviour with haltOnError=n is that all structures in the indexed column are processed and the ROWIDs of eventual problematic structures are logged. If there were any problems during indexing, the process fails at the end (and leaves the resulting index in a failed state) to alert the user that there were problems during indexing. I assume this is the behaviour you're getting, correct?


If so, the next question is: what behaviour would you like to see? Would you like to see an option which gives you a "usable" index?


Starting with JChem version 5.3, a "structure checker" function will be made available as Chemical Terms which can be used to check various aspects of structures on correctness. I assume it can be configured so as to cover most of the problems which may cause indexing to fail. But ultimately nothing will do the job of discovering potential indexing problems as well as indexing itself.


Thanks


Peter

User 952e1d9361

17-11-2009 14:33:23

Hi Peter,


Thanks for the quick reply.


Yes, ideally, i'd like to have a usable index at the end, even if some structures could not be indexed.  The 'structure checker' sounds useful though.


Can I email you the MOL file in question so you can tell me what is 'wrong' with it?  It opens up in various other bits of software such as Isis, ChemDraw and Symyx Draw but I can get JChem or Marvin to understand it.


Thanks,


Steve


 

ChemAxon aa7c50abf8

17-11-2009 14:43:15

Yes, ideally, i'd like to have a usable index at the end, even if some structures could not be indexed. 

OK. I'll let you know when it is implemented.


Can I email you the MOL file in question so you can tell me what is 'wrong' with it? 

Sure, please, send it to me (pkovacs _at_ chemaxon _dot_ com), we definitely would like to have a look.


Thanks


Peter

ChemAxon aa7c50abf8

18-01-2010 09:15:46

JChem 5.3 has been released with a new option to the haltOnError index parameter: nf. As the linked documentation describes, with the nf option indexing will complete "successfully" in the sense that the index will be left in a usable state (as if no error had been occurred). Errors are logged as with the n option.