Article Original Creation Date: 2012-01-12

Overview

After executing SG4.2.0.0 DB update query, Double entries added in the sprt_nc_cpe_wf_state table.

Encore.logs contains following error messages:

2012-01-10 08:55:25,721 ERROR [encore.acs] class: com.supportsoft.servicegateway.cwmp.ejb.acsmanager.dao.ACSManagerOracleDAO method: setCPEWorkflowState(): 00007 - SQL Error occurred while updating unexpected update count 2 on CPE aba1630f491c3181b3f6a16b5fe7bc17
2012-01-10 08:55:25,729 ERROR [encore.acs] class: com.supportsoft.servicegateway.cwmp.ejb.acsmanager.dao.ACSManagerOracleDAO method: setCPEWorkflowState(): 00007 - SQL Error occurred while updating unexpected update count 2 on CPE aba1630f491c3181b3f6a16b5fe7bc17

Environment

• Oracle 10
• Solaris 10
• JBoss 4.0.4GA/ WL 9.2.3
• Tomcat 5.5.25

Root Cause

The database update query OracleDBChangesFrom4.1.3.0_DML_2.sql geneates another query OracleDBChangesFrom4.1.3.0_DML_2_generated.sql which runs the same database update as the OracleDBChangesFrom4.1.3.0_DML_2.sql and take hours to complete.

Executing similar update scripts causes the sprt_nc_cpe_wf_state table to have double entries.

Resolution

To remove double entries from the sprt_nc_cpe_wf_state table, follow the steps below:

1. Stop all ACS/device activity.

2. Execute the below query to purge duplicates from sprt_nc_cpe_wf_state:

   delete from sprt_nc_cpe_wf_state where rowid <> ( select min(rowid) from sprt_nc_cpe_wf_state wf where wf.nc_cpe_guid = sprt_nc_cpe_wf_state.nc_cpe_guid);

3. Reset sprt_nc_cpe_wf_state.nc_cpe_workflow_state to null:

   update sprt_nc_cpe_wf_state set nc_cpe_workflow_state=null;

4. Reset sprt_nc_cpe_connection.nc_cpe_connection to 0:

   update sprt_nc_cpe_connection set nc_cpe_connection=0;

5. Commit the changes:

   commit;

6. Restart ACS.