This discussion is archived
6 Replies Latest reply: Sep 27, 2013 7:11 AM by pgleghorn RSS

Exception during transaction in Mungo table

Venkat Mohanam Newbie
Currently Being Moderated

Hi,

 

I am getting the below error sometimes, when I try saving any Image asset. The error shows that ID is passed as null. But I am sure, I am not doing any insert/modification on this table directly. It is a normal asset creation process and default elements are used and no custom code is involved in it.

Only restart, solves this issue, but I am getting the same error again after sometime. Please let me know if it is anything related to ID generation process?

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

[2013-09-13 02:52:10,166] [fatwire.logging.cs.db] Exception executing prepared statement: INSERT INTO Image_Mungo (CS_OWNERID,STRINGVALUE,CS_ATTRID) VALUES (?,?,?)
CS_OWNERID = 1331959037911
STRINGVALUE = No
CS_ATTRID = 1324319471934
java.sql.SQLIntegrityConstraintViolationException: ORA-01400: cannot insert NULL into ("CONTRIBUTOR"."Image_MUNGO"."ID")


[2013-09-13 02:52:10,168] [cs.core.db.DBTransaction] TransactionUnit failed to execute
com.fatwire.cs.core.db.TransactionAbortException: addrows failed


[2013-09-13 02:52:10,170] [fatwire.logging.cs.db] Exception in addrows for table Image_Mungo
com.fatwire.cs.core.db.TransactionAbortException: addrows failed


[2013-09-13 02:52:11,340] [logging.cs.xcelerate.asset] Error in ComplexAsset: Error: errno=-105 on call to catalog manager for table 'Image_Mungo'
com.openmarket.basic.interfaces.AssetException: Error: errno=-105 on call to catalog manager for table 'Image_Mungo'

  • 1. Re: Exception during transaction in Mungo table
    pgleghorn Journeyer
    Currently Being Moderated

    This sounds like a form of resultset cache corruption. Do you have anywhere in your code, custom SQL that is querying the Image_Mungo table, eg ics:sql tags or ics.SQL() method? If you do, ensure that the table you specify to cache against is correctly specified as it is case sensitive, eg "image_mungo" would be wrong, it should be "Image_Mungo".

     

    Phil

  • 2. Re: Exception during transaction in Mungo table
    Venkat Mohanam Newbie
    Currently Being Moderated


    Hi Phil,

     

    I think you are right. I searched through the code dump and saw one such place, where the table name is used in a ics.sql method with all lowercase. I changed it and flushed resultset cache. Now it works. Thanks for the suggestion.

    I am curious to understand, how this has the effect on the new Image asset creation? If there is a resultset corruption, it should affect the records in the resultset  not the new records right, because they are not even in the resultset. Could you please explain this in detail? Thanks.

  • 3. Re: Exception during transaction in Mungo table
    pgleghorn Journeyer
    Currently Being Moderated

    It's a tricky one. Every table in SystemInfo has it's structure (columns, datatypes) cached in memory in resultset cache for quick reference. The table structures are requested on first access via jdbc's Connection.getMetaData(),and stored in two resultset cache tables "cached-catalogs-native" and "cached-catalogs". You can see these in the Admin UI resultset cache tool, having huge hit count because CS checks this kind of thing all the time. These entries remain in cache for some time but do expire and get invalidated, like other caches.

     

    My understanding of what happens is this (I might be wrong on some points). If an access to the table is done using this bad sql with incorrect case sensitivity, AND the entry for this table is not yet in cached-catalogs (either because we just started up and haven't accessed that table by other means yet, or that the cached entry existed but since expired/invalidated), then an entry is added to cached-catalogs using the incorrect case. As part of this entry CS also needs to determine the table type from looking it up in SystemInfo.systable, and since it queries case sensitively it finds no row so the table type isn't determined correctly. Usually this means the cached table impression acquires a phantom ID column where none exists, but in this case you seem to have the reverse. Either way, the cached table impression is wrong in some subtle way.

     

    From this point on CS may construct incorrect SQL at runtime based on that impression, which fails if the impression disagrees with the actual table. I have seen this more on tree tables like SitePlanTree, where CS complains that a value for ID column wasn't provided in the insert. Restarting (or even just flushing "cached-catalogs" and "cached-catalogs-native") should restore normality.

     

    What version are you running? This was believed to be fixed in 7.6.1 and onwards.

     

    Phil

  • 4. Re: Exception during transaction in Mungo table
    pgleghorn Journeyer
    Currently Being Moderated

    An entry in the support knowledgebase describes it well

    https://support.oracle.com/epmos/faces/DocumentDisplay?id=1458352.1

     

    Phil

  • 5. Re: Exception during transaction in Mungo table
    Venkat Mohanam Newbie
    Currently Being Moderated

    Thanks Phil for the quick useful response.By the way, we are on 7.6.1. I am not able to access the knowledgebase url. It asks for support identifier. If possible could you pls send it to vmohanam@gmail.com. Thanks.

    I will go through this and I need sometime to understand the whole concept. Thanks again.

  • 6. Re: Exception during transaction in Mungo table
    pgleghorn Journeyer
    Currently Being Moderated

    In short if you ensure that any custom SQL is using the correct case, as you have already done, then you should be fine. The fix was probably in a patch on top of 7.6.1. If you goto 7.6.2 (which is the lowest current oracle version, 7.6.1 is legacy fatwire), or later versions like 11.1.1.6.1 and 11.1.1.8.0 then you should be fine.

     

    Phil

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points