Topic
  • 5 replies
  • Latest Post - ‏2013-01-25T09:35:24Z by babinag
babinag
babinag
91 Posts

Pinned topic Item deleted by nobody issue

‏2012-10-31T08:51:54Z |
Hi all.

There is an issue.

1. There is an item, that previously exists in catalog. Item disappears.
2. Nobody has deleted it: no information about deleting from alo table

select * from alo where ALO_TIMESTAMP > to_date(
'2012-10-01',
'YYYY-MM-DD') and ALO_DESCRIPTION like 
'%Catalog%' and ALO_EVENT_NAME like 
'%DELETED'

3. There is an old version of item, that can be viewed by direct link like

http:
//host.com/classicornew.wpc?doit=modify_item_in_data_entry&catalog_id=1007&item_id=2022473&version_id=22497&ctg_version_id=0

1007 is a catalog of that item
2022473 is an id of that item
22497 is an ITM_NEXT_VERSION_ID value of last row of query


select * from itm itm1 where itm1.itm_container_id = 1007 and itm1.itm_primary_key = 
'990402'

990402 is pk of item.

How can i restore that item?
Updated on 2013-01-25T09:35:24Z at 2013-01-25T09:35:24Z by babinag
  • SystemAdmin
    SystemAdmin
    648 Posts

    Re: Item deleted by nobody issue

    ‏2012-10-31T09:20:01Z  
    Hi,

    1) You can create rows in tables based on version 22497, or
    2) Clone item with version 22497 and then change PK via a script
  • babinag
    babinag
    91 Posts

    Re: Item deleted by nobody issue

    ‏2012-10-31T09:40:26Z  
    Hi,

    1) You can create rows in tables based on version 22497, or
    2) Clone item with version 22497 and then change PK via a script
    Hi, Alexander.

    1. I tried to create row with right ITM_NEXT_VERSION_ID = 999999999, but item still is not searchable. Seems that there is some relation of itm.ITM_NEXT_VERSION_ID with other tables.
    2. Clone action button (as well as save and other normal buttons) is not available from UI link like i mentioned above.

    I have tried to create item via import, this results in a exception:
    
    CWPAP0001E:Unexpected internal error. 2012-10-31 09:48:36,923 ERROR: CWCUS0001E:com.ibm.ccd.api.catalog.item.GenericItemImpl.save(Unknown Source) com.ibm.ccd.api.catalog.item.ItemImpl.save(Unknown Source)
    

    and
    
    ERROR com.ibm.ccd.common.error.AustinException JOB_ID:5404- CWPCM0002E:Generic error / Exception: Update failed, the operation has been rolled back.CWPCM0165E:Failed to insert 
    'com.ibm.ccd.element.common.DbEntry' object using insertAll()., Exception:ORA-00001: нарушено ограничение уникальности (PIM91.ITM_1_UK)   java.sql.SQLIntegrityConstraintViolationException: ORA-00001: нарушено ограничение уникальности (PIM91.ITM_1_UK)   at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:759) at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:661) at oracle.jdbc.driver.T2CPreparedStatement.executeForDescribe(T2CPreparedStatement.java:542) at oracle.jdbc.driver.T2CPreparedStatement.executeForRows(T2CPreparedStatement.java:725) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3657) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1350) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:233) at com.ibm.ccd.common.db.Query.executeUpdate(Unknown Source) at com.ibm.ccd.common.context.common.DBContext.executeUpdate(Unknown Source) at com.ibm.ccd.common.context.common.DBContext.executeUpdateCommon(Unknown Source) at com.ibm.ccd.common.context.common.DBContext.executeUpdate(Unknown Source) at com.ibm.ccd.common.gendb.GenCtgItmItemTable.insertAll(Unknown Source) at com.ibm.ccd.content.common.Item.attemptToInsertAll(Unknown Source) at com.ibm.ccd.content.common.Item.processItemSavePartOne(Unknown Source) at com.ibm.ccd.content.common.Item.BOFtoDB(Unknown Source) at com.ibm.ccd.content.common.Item.insertAll(Unknown Source) at com.ibm.ccd.element.common.BaseObject.persist(Unknown Source) at com.ibm.ccd.element.common.PersistenceManager.persistObject(Unknown Source) at com.ibm.ccd.element.common.PersistenceManager.persistObjectBasedOnInBatch(Unknown Source) at com.ibm.ccd.element.common.PersistenceManager.persistObject(Unknown Source) at com.ibm.ccd.content.common.Item.toDB(Unknown Source) at com.ibm.ccd.content.common.Item.toDB(Unknown Source) at com.ibm.ccd.api.catalog.item.GenericItemImpl.save(Unknown Source) at com.ibm.ccd.api.catalog.item.ItemImpl.save(Unknown Source) at
    

    unique constraint in itm table:
    
    ITM_1_UK (UNIQUE): columns ITM_CONTAINER_ID, ITM_PRIMARY_KEY, ITM_NEXT_VERSION_ID
    
  • StefanR
    StefanR
    6 Posts

    Re: Item deleted by nobody issue

    ‏2012-10-31T10:08:18Z  
    Please raise a PMR with MDMCS support.

    Investigations should be done what might have caused the item to vanish. Support might help to restore item and do some sanity check.
    Please provide:
    all MDMCS logs
    details when this lost item issue was discovered
    exact MDMCS version (get_ccd_version.sh) and full softwarestack (applicationserver, DB)

    Depending on this information further instructions will be provided by support team

    kind regards
    Stefan
  • SystemAdmin
    SystemAdmin
    648 Posts

    Re: Item deleted by nobody issue

    ‏2012-10-31T11:29:20Z  
    • babinag
    • ‏2012-10-31T09:40:26Z
    Hi, Alexander.

    1. I tried to create row with right ITM_NEXT_VERSION_ID = 999999999, but item still is not searchable. Seems that there is some relation of itm.ITM_NEXT_VERSION_ID with other tables.
    2. Clone action button (as well as save and other normal buttons) is not available from UI link like i mentioned above.

    I have tried to create item via import, this results in a exception:
    <pre class="jive-pre"> CWPAP0001E:Unexpected internal error. 2012-10-31 09:48:36,923 ERROR: CWCUS0001E:com.ibm.ccd.api.catalog.item.GenericItemImpl.save(Unknown Source) com.ibm.ccd.api.catalog.item.ItemImpl.save(Unknown Source) </pre>
    and
    <pre class="jive-pre"> ERROR com.ibm.ccd.common.error.AustinException JOB_ID:5404- CWPCM0002E:Generic error / Exception: Update failed, the operation has been rolled back.CWPCM0165E:Failed to insert 'com.ibm.ccd.element.common.DbEntry' object using insertAll()., Exception:ORA-00001: нарушено ограничение уникальности (PIM91.ITM_1_UK) java.sql.SQLIntegrityConstraintViolationException: ORA-00001: нарушено ограничение уникальности (PIM91.ITM_1_UK) at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:759) at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:661) at oracle.jdbc.driver.T2CPreparedStatement.executeForDescribe(T2CPreparedStatement.java:542) at oracle.jdbc.driver.T2CPreparedStatement.executeForRows(T2CPreparedStatement.java:725) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3657) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1350) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:233) at com.ibm.ccd.common.db.Query.executeUpdate(Unknown Source) at com.ibm.ccd.common.context.common.DBContext.executeUpdate(Unknown Source) at com.ibm.ccd.common.context.common.DBContext.executeUpdateCommon(Unknown Source) at com.ibm.ccd.common.context.common.DBContext.executeUpdate(Unknown Source) at com.ibm.ccd.common.gendb.GenCtgItmItemTable.insertAll(Unknown Source) at com.ibm.ccd.content.common.Item.attemptToInsertAll(Unknown Source) at com.ibm.ccd.content.common.Item.processItemSavePartOne(Unknown Source) at com.ibm.ccd.content.common.Item.BOFtoDB(Unknown Source) at com.ibm.ccd.content.common.Item.insertAll(Unknown Source) at com.ibm.ccd.element.common.BaseObject.persist(Unknown Source) at com.ibm.ccd.element.common.PersistenceManager.persistObject(Unknown Source) at com.ibm.ccd.element.common.PersistenceManager.persistObjectBasedOnInBatch(Unknown Source) at com.ibm.ccd.element.common.PersistenceManager.persistObject(Unknown Source) at com.ibm.ccd.content.common.Item.toDB(Unknown Source) at com.ibm.ccd.content.common.Item.toDB(Unknown Source) at com.ibm.ccd.api.catalog.item.GenericItemImpl.save(Unknown Source) at com.ibm.ccd.api.catalog.item.ItemImpl.save(Unknown Source) at </pre>
    unique constraint in itm table:
    <pre class="jive-pre"> ITM_1_UK (UNIQUE): columns ITM_CONTAINER_ID, ITM_PRIMARY_KEY, ITM_NEXT_VERSION_ID </pre>
    Hi,

    As Stefan said the best solution is to open a PMR, but if you want to fix the issue by youself, you have to investigate the following table regarding the item relations: ITM, ITD, ITA, ICM
  • babinag
    babinag
    91 Posts

    Re: Item deleted by nobody issue

    ‏2013-01-25T09:35:24Z  
    As mentioned above, first solution is to remove all information about item from tables, then create new item with same pk.

    Steps:

    1. This item pk for example is "882"

    2. From DBAdmin page one need to find internal id of that item:
    
    select * from itm itm1 where itm1.itm_container_id = 1007 and itm1.itm_primary_key = 
    '882'
    


    internal id = "545279", located at ITM_ID column of itm table.

    3. Delete all item information from ITA table (latest catalog version
    is 22920, for example):
    
    delete from ita where ita_item_id = 545279 and ita_catalog_id = 1007 and ita_next_version_id between -10 and 22920 and ita_version_id between -10 and 22920 and ita_company_id = 10001
    


    4. Delete all item information from icm table:
    
    delete from icm where icm_item_id = 545279 and icm_catalog_id = 1007 and icm_next_version_id between -10 and 22920 and icm_version_id between -10 and 22920 and icm_company_id = 10001
    


    5. Delete all item information from itm table:
    
    delete from itm where itm_id = 545279 and itm_container_id = 1007 and itm_next_version_id between -10 and 22920 and itm_version_id between -10 and 22920 and itm_company_id = 10001
    


    6. Delete all item information from itd table:
    
    delete from itd where itd_item_id = 545279 and itd_container_id = 1007 and itd_next_version_id between -10 and 22920 and itd_version_id between -10 and 22920 and itd_company_id = 10001
    


    7. After this operations, one can successfully create item with pk = "882" via import or UI.

    This operation is suitable for productive env, if all attributes of item is known, but item is corrupted.