Errors on creating indexes following upgrade to v3.2.3

Hi





I am in the process of rebuilding our test environment using the latest version of JChem (v3.2.3) & find that on database columns where I was able to build indexes previously (Using JChem v3.1.5) I now get errors due to invalid SMILES.





I have pasted below an example of this error.





Our test environment is built from an Oracle export of our live database that has JChem v3.1.5 installed over Oracle 9i. The new test environment is a fresh build based upon Oracle 10gRel2 and JChem v3.2.3.





On attempting to create the JChem indexes in this new environment only 2 of the 11 finished without error. All the other CREATE INDEX statements eventually failed with errors similar to the one shown below.





As we would like to eventually migrate our live environment up to this version (& thus limit the migration time and have confidence in it working)





Is there a way we can get these indexes to build from fresh without needing to fix this data?








SQL>


SQL> CREATE INDEX SSD.IDX_VERSION_J ON SSD.VERSION


2 (ISOSMILES)


3 INDEXTYPE IS JCHEM.JC_IDXTYPE


4 PARAMETERS('TABLESPACE=INDX_SSD');


CREATE INDEX SSD.IDX_VERSION_J ON SSD.VERSION


*


ERROR at line 1:


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


ORA-29532: Java call terminated by uncaught Java exception:


java.lang.Exception: The following exception has been thrown by the servlet:


Exception: An error occurred while processing the structure


(ROWID=AAAM0kABDAAAmraAAt)


'.*[Sn](C1CCCCC1)(C2CCCCC2)C3CCCCC3.*[Sn](C1CCCCC1)(C2CCCCC2)C3CCCCC3':


Inserting a query or Markush structure is not allowed for table:


"SSD.IDX_VERSION_J_JCX"


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


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

Hi,





Please, read the following thread: http://www.chemaxon.com/forum/ftopic2514.html .





Please, also checkout the new index parameter tableType (http://www.chemaxon.com/jchem/doc/guide/cartridge/index.html#index)





Basically, you have to set tableType to anyStructures in order to get the old behaviour.





Let me know if this doesn't help.





Peter

Hi,





I created a small test table containing one of the offending SMILES. (In this case "*CC(*)c1ccccc1"). I first attempted to create the domain index as normal without the extra parameter & I got the expected error. I then added "PARAMETERS('tableType=anyStructures')" to the index creation statement and re-ran the test. This time the index created without error.





However on attempting to create indexes on the fully populated tables, the I now get the following error.





ORA-03113: end-of-file on communication channel





CREATE INDEX ENUM.IDX_J_POOL


ON ENUM.LIB_POOL


(SMILES)


INDEXTYPE IS JCHEM.JC_IDXTYPE


PARAMETERS('TABLESPACE=indx_enum,tableType=anyStructures')





The error occours immediatly and the database connection is killed.





I do not know if this is the same error or different I have tried restarted Tomcat / database but to no avail.





Thanks





Alex

If this is on a 64-bit 10gR2, please see http://www.chemaxon.com/forum/ftopic2496.html .





Peter

No the server is running Linux Redhat ES4 on a 32 bit machine. The chip details 2 x Intel Pentium III Xeon. L2 Cache 2MB. Processor Speed 900MHz. The machine is a Dell Power Edge 6400/900

Is there any information indicating a problem in the Oracle session trace or in the alert log?

*** 2007-01-29 17:32:35.778


ksedmp: internal or fatal error


ORA-07445: exception encountered: core dump [qxithcdo()+176] [SIGSEGV] [Address not mapped to object] [0x98] [] []


Current SQL statement for this session:


CREATE INDEX ENUM.IDX_J_POOL


ON ENUM.LIB_POOL


(SMILES)


INDEXTYPE IS JCHEM.JC_IDXTYPE


PARAMETERS('TABLESPACE=indx_enum,tableType=anyStructures')





*** 2007-01-29 17:35:15.940


ksedmp: internal or fatal error


ORA-07445: exception encountered: core dump [qxithcdo()+176] [SIGSEGV] [Address not mapped to object] [0x98] [] []


Current SQL statement for this session:


CREATE INDEX ENUM.IDX_J_POOL


ON ENUM.LIB_POOL


(SMILES)


INDEXTYPE IS JCHEM.JC_IDXTYPE


PARAMETERS('TABLESPACE=indx_enum,tableType=anyStructures')

I wonder if the size (or content) of the tables is really a discriminating factor in this problem. The index creation was, perhaps, successful on the small table only because you tried it once. Please, could try and (1) drop the index on the small table and (2) recreate the index on it and repeat this at least 3 times in a raw. (Or 10 times in a raw, if it is possible.)





In the meantime, I am going to try creating a test environment similar to yours in order to reproduce the problem.





Thanks


Peter

By "RAW" do you mean the datatype of the column holiding the SMILES should by of type "RAW"?

Oh no sorry. I meant "in a row", ie. consecutively.





Peter

I added 17 rows to the test table then ran a script to drop and recreate the index 10 times. It ran without error.

Please, could you tell me the patch level of your Oracle installation?





Thanks


Peter

Oracle Database 10g Rel 2 10.2.0.1.0 Standard Edition





This is fresh install. No patches have been applied on top.

I do not think I have ever tested JChem Cartridge with an unpatched 10gR2. Since I have to do a fresh 32-bit 10gR2 install anyway, I will test with it unpatched first.





P.

If you let me know the patch level that you have tested on, I will download and apply accordingly.

I have tested it on Windows with Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production.





I see that you have Standard, I am also going to test with that one instead of EE.





BTW, could you, please, also post the output of 'uname -a' (kernel version).





Thanks


Peter

Linux UKSAP11 2.6.9-42.0.3.ELsmp #1 SMP Mon Sep 25 17:28:02 EDT 2006 i686 i686 i386 GNU/Linux

I have indexed 12xNCI (more than 3 million structure) without problem with Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production. (I have realized just now that I had to restart the Oracle installation [for minor config problems] enough times to forget to change the type from the default EE to Standard. I will reinstall it as Standard and try again -- I do not think being EE or Standard matters, but who knows....)





Any luck with the patches?





Approximately how long does CREATE INDEX run on the large table before aborting? Does it fail within, say, 10 seconds?





Thanks


Peter

The index fails around 10 seconds. Re the patches, I have downloaded the patchset to take the server upto 10.2.0.3. I intend to apply it today.

I have installed the oracle patch set 10.2.0.3 and have the same error. I was wondering whether I should try an install of JChem 3.1.5 to see if I can get that working?

Yes, I was also about to suggest that. Please, try the earlier JChem version which worked for you in another environment.





Thanks


Peter

I have de-installed JChem v3.2.3 and installed JChem v3.1.7. This is the same version of JChem that is in our live environment. Now the index build scripts appear to be working. No errors yet...

Hi Alex,





I have installed the 10.2.0.1 Standard Edition and started indexing a large table (10 minutes ago) and encountered no problem so far.





Is there any trace of an error in the Tomcat logs? (in <tomcat-home>/logs primarily in the catalina.out file.)





How long does it normally take to index the larger table whose indexing aborted with JChem 3.2.3 after 10 seconds?





Thanks


Peter

Hi





I checked the tomcat logs for around the same time as that for the errors in the Oracle logs and found nothing of interest.





With regards building the index on our large table I cannot give you precise times as we left it running overnight, but it took several hours to run. 3 maybe 5 hours to build.





One thinig that we did note was that we created a table with 700 rows and attempted to build the index on this. This gave us the same error. When we emptied the table then built the index, it built fine. & on re-inserting back the 700 rows, there was no problem either. We ended up with the indexed table and all the rows. It just would not let us build the index with the data present.

Thanks for the input Alex!





In the meantime, indexing of the 3 million structure has completed on my end without any problem. Please, could you post the initialization parameters for your database?





Thanks


Peter

init.ora file

Hi,





I have attempted to attach the init.ora file to the last post and can't seem to see it. If I have not done this correctly, let me know & I can send it on as an email...

Please, send it to me: peter dot kovacs at chemaxon dot hu





Thanks


Peter

Hi Alex,





I was not able to reproduce the problem even with your initialization parameters. Please, could you contact Oracle to see if they can be of any help in this problem?





Thanks


Peter

Hi Alex,





One more thing I would suggest is to install Oracle Database 10g Products from the Oracle Database Companion CD. This appears to solve consistently reproducible problems with Oracle 10gR2 64-bit (mentioned earlier in this topic) as well as a difficult-to-reproduce problem with Oracle 10gR2 32-bit on Windows.





Have you looked at the backtrace of the core dump generated? Does it appear to be a JVM problem? (I assume it is.)





Thanks


Peter

Hi Alex,





I have found something on Metalink which may be relevant:


ORA-07445: Core Dump [Qxithcdo()+172]While Creating Index (https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=432666.1)

Quote:

The failing module is qxithcdo - QXITH Check for Disjoint Operator set, which could indicate that the column is already indexed (or that Oracle thinks it is). [...] If this has happened, you may be able to clear it by dropping the index again, using the 'FORCE' option: drop index <index_name> force; This should enable a subsequent create index to run to completion

Peter