6 Replies Latest reply: Sep 27, 2013 2:11 PM by Pgleghorn-Oracle RSS

    Exception during transaction in Mungo table

    Venkat Mohanam



      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
      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

          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".



          • 2. Re: Exception during transaction in Mungo table
            Venkat Mohanam

            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

              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.



              • 5. Re: Exception during transaction in Mungo table
                Venkat Mohanam

                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

                  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 and then you should be fine.