ORA-04091: table ... is mutating

User a7faa21887

21-11-2011 18:26:49

Hi, all.


We use JChem 5.7.0.0 in 10.2.0.5 RAC Database (x86_64)


While importing some structures (see attachment) we've got such error message:


ORA-29875: failed in the execution of the ODCIINDEXINSERT routine
ORA-29532: Java call terminated by uncaught Java exception: java.rmi.RemoteException: Error while getting the next hits; nested exception is:
    oracle.jdbc.driver.OracleSQLException: ORA-04091: table PORTAL.MD_MODEL_STRUCTS is mutating, trigger/function may not see it
ORA-06512: at "JCHEM_PORTAL.JCHEM_CLOB_PKG", line 112
ORA-06512: at "JCHEM_PORTAL.JC_IDXTYPE_IM", line 843


Pavel

ChemAxon aa7c50abf8

21-11-2011 19:59:42

Pavel,


Thank you for reporting this problem!


Please, could you post (or send me to pkovacs at chemaxon dot com) the corresponding Java stack trace (with the ORA-04091 error) from jchem/cartridge/logs?


Thanks


Peter

User a7faa21887

22-11-2011 10:59:20










pkovacs wrote:

Pavel,


Thank you for reporting this problem!


Please, could you post (or send me to pkovacs at chemaxon dot com) the corresponding Java stack trace (with the ORA-04091 error) from jchem/cartridge/logs?


Thanks


Peter



Sure, Peter.


But I can't see any stack trace with 'mutating' words in log files :(


Should I probably use specific parameters in logging.properties for this?


And does these parameters in logging.properties will be applied without stop/start JChem server?


Pavel

ChemAxon aa7c50abf8

22-11-2011 11:41:21

Pavel,


The Java stack trace is supposed to be logged by defaul for these kinds of errorst.


Did you look for ORA-04091? (But looking for "mutating" should also work, indeed.)


You have a JChem Cartridge server on each Oracle RAC node, correct? Did you look on each of the nodes?


Regarding your question about logging configuration: starting with JChem version 5.6.1 you don't have to restart the JCC server, it is enough to execute server.sh reload. (Before 5.6.1, reload worked only for entries in jchem/cartridge/conf/logging.properties which stood for specific logger -- usually for specific Java classes.)


Peter

User a7faa21887

22-11-2011 11:57:44










pkovacs wrote:

Pavel,


The Java stack trace is supposed to be logged by defaul for these kinds of errorst.


Did you look for ORA-04091? (But looking for "mutating" should also work, indeed.)


You have a JChem Cartridge server on each Oracle RAC node, correct? Did you look on each of the nodes?


Regarding your question about logging configuration: starting with JChem version 5.6.1 you don't have to restart the JCC server, it is enough to execute server.sh reload. (Before 5.6.1, reload worked only for entries in jchem/cartridge/conf/logging.properties which stood for specific logger -- usually for specific Java classes.)


Peter



Yes, I've grepped ORA-04091 too on both RAC nodes - there is no result :(


It's cool that I don't need to restart server.


I've copied parameters (without any comments) from logging.properties file (I didn't touch it after installation):


handlers= java.util.logging.FileHandler java.util.logging.ConsoleHandler
#handlers= java.util.logging.FileHandler, java.util.logging.ConsoleHandler
.level = INFO
java.util.logging.FileHandler.pattern = jcart%u.log
java.util.logging.FileHandler.limit = 50000000
java.util.logging.FileHandler.count = 1
#java.util.logging.FileHandler.formatter = java.util.logging.XMLFormatter
java.util.logging.FileHandler.formatter = chemaxon.jchem.cartridge.util.SimpleFormatterTh
java.util.logging.FileHandler.level = FINEST
java.util.logging.ConsoleHandler.level = INFO
java.util.logging.ConsoleHandler.formatter = chemaxon.util.logging.SimplestFormatter

# chemaxon.jchem.db.JChemSearch.stats.level = FINE
# chemaxon.jchem.cartridge.rmi.impl.JchemSearchResultImpl.level = FINEST
# chemaxon.jchem.db.level = FINEST
# chemaxon.jchem.level = FINEST
chemaxon.jchem.cartridge.ServerControl.level = FINEST
chemaxon.jchem.cartridge.install.level = FINEST
# chx.jchem.cartridge.util.cpools.CommonsDataSource.handlers = java.util.logging.FileHandler
# chx.jchem.cartridge.util.cpools.CommonsDataSource.level = FINEST


Pavel

ChemAxon aa7c50abf8

22-11-2011 20:45:38


Looking more closely at the error message (and the code where it appears), it is possible after all that the error actually occurred on the Oracle side. (A RemoteException is thrown at a place in the code where a "local" exception should be thrown. This was what confused me.)


Please, could you check in the Oracle session trace files (in the "udump" directory) if there is any error message mentioning ORA-04091?


Many thanks and sorry for the confusion!


Peter


User a7faa21887

22-11-2011 22:05:54

No problem, Peter.


I've sent you trace file on your email.


Pavel

ChemAxon aa7c50abf8

28-11-2011 23:27:30

Thank you, Pavel! This is a JChem Cartridge bug. The fix is targeted for version 5.9.


In the meantime, the work around is to reindex the table without the duplicateFiltering parameter and execute manually the duplicate search before insert.


Peter

User a7faa21887

30-11-2011 17:19:45










pkovacs wrote:

Thank you, Pavel! This is a JChem Cartridge bug. The fix is targeted for version 5.9.


In the meantime, the work around is to reindex the table without the duplicateFiltering parameter and execute manually the duplicate search before insert.


Peter



Workaround works well.


Pavel

ChemAxon 61b4fee994

15-02-2012 14:11:06

Dear Pavel,


We can only fix the problem in the 5.10 version, but the fix will be deifinitely part of that version.


Regards,


Tamas

ChemAxon 61b4fee994

18-06-2012 10:08:26

Hi Pavel,


JChem 5.10.0 with the fix is released.


Tamas

User a7faa21887

18-06-2012 10:28:44

Yes, I've already installed it.


And I think it's resolved in any case.


Thanks