Solved: CUCM 8.5 Error1: Update failed. Could not insert new row - duplicate value in a UNIQUE INDEX column....

jairochavez · ‎02-11-2015

Cisco Unified CM Administration
System version: 8.5.1.10000-26

Unable to change or delete phone (Directory Number). Example: "9521" I cannot reuse it nor delete it.

Update failed. Could not insert new row - duplicate value in a UNIQUE INDEX column (Unique Index:x_device_name).

The item with key "2cdc3023-3cb5-7552-611d-096019bfb505" was not found in the database. Please select another item to view.

The item with key "53e6a1ae-9a91-c97a-6d58-6f7a746a744c" was not found in the database. Please select another item to view.

The item with key "7ec8b0dc-ccc7-92e2-2849-eae07c2dc926" was not found in the database. Please select another item to view.

Any idea to remove it or information on how to run SQL queries for delete duplicate DN's...

Thanks JCM

Aditya Gupta · ‎03-27-2015

Hi

This clearly looks to be a DB issue.

Although Replication says 2, but clearly CUCM are not in sync.

You can use the following sql query on the cucm to delete these numbers.

run sql delete from numplan where pkid='2cdc3023-3cb5-7552-611d-096019bfb505'

Repeate the above query by changing the pkid's .

Also , i would suggest to reset your CUCM replication once, whenever possible

Regards

Aditya Gupta

View solution in original post

Manish Gogna · ‎02-11-2015

Hi,

Please check the Error: "Update failed. Could not insert new row - duplicate value in a UNIQUE INDEX column" of the following link

http://www.cisco.com/c/en/us/support/docs/unified-communications/unified-communications-manager-callmanager/18772-extension-mobility.html#dupl

HTH

Manish

jairochavez · ‎03-27-2015

Manish

http://www.cisco.com/c/en/us/support/docs/unified-communications/unified-communications-manager-callmanager/18772-extension-mobility.html#dupl

Error: "Update failed. Could not insert new row - duplicate value in a UNIQUE INDEX column"

If a phone configured for Extension Mobility (EM) is deleted when a user is logged, the CallManager allows the Administrator to delete the phone through bulk administration. When an attempt is made to add the phone back with the EM option along with "Log Out Profile" checked, it may give this error message: Update failed. Could not insert new row - duplicate value in a UNIQUE INDEX column.

Note: Removing a phone that has a logged in user through the phone page is prevented by the GUI.

Solution- This problem seems to happen when the deleted phone is configured for EM and a user is logged in at the time of deletion. In order to resolve this issue, remove the old ADP[mac-address] profile from the route plan report.

This is documented in Cisco Bug ID CSCsj63279 (registered customers only) .

Problem: Extension Mobility cannot be turned on for a Cisco 7900 IP Phone, and this error message appears:
```
Update failed. Could not insert new row - duplicate value in a UNIQUE INDEX column
```
The root cause of the problem is the intermittent failure to delete the auto-generated device profiles (ADP) for a phone. If you delete phones that are configured for extension mobility with the logout profile set to use current, it leaves an ADP in the database. This results in the inability to reinsert this phone into the database.
Solution: In order to workaround this issue, complete these steps:
1. Use CallManager Administration in order to find and delete the orphaned auto-generated device profile. In order to do this:
  1. Choose Device > Device Settings > Device Profile.
  2. Click Find > All.
  3. Delete the ones that are not associated to any of the IP phones.
2. Restart the device.

I can't remove the old ADP[mac-address] profile from the route plan report.

how run an sql query on the CUCM to delete this key?

The item with key "2cdc3023-3cb5-7552-611d-096019bfb505" was not found in the database. Please select another item to view.
The item with key "42c4f64e-378f-e450-5afb-2558b40939bd" was not found in the database. Please select another item to view.
The item with key "53e6a1ae-9a91-c97a-6d58-6f7a746a744c" was not found in the database. Please select another item to view.
The item with key "7ec8b0dc-ccc7-92e2-2849-eae07c2dc926" was not found in the database. Please select another item to view.
The item with key "af8733c6-ec63-0ceb-70a4-dc11e327e7e0" was not found in the database. Please select another item to view.
The item with key "c5946ca3-c2ee-174b-1db1-16be662b5a73" was not found in the database. Please select another item to view.

https://X.x.X.X/ccmadmin/directoryNumberEdit.do?key=c5946ca3-c2ee-174b-1db1-16be662b5a73

Aditya Gupta · ‎03-27-2015

Hi

Looking at the screenshot you send above, there are multiple DN with same pattern in similar partition.

This is normally not allowed in CUCM and it would throw exception. Seems like database issue.

Can you please check the following :

DB Replication is fine in your CUCM cluster

Also can u get me output of following sql query from CUCM pub:

run sql select * from numplan where dnorpattern='9521'

Regards

aditya gupta

jairochavez · ‎03-27-2015

My cluster:

┌ ---> Suscriber 1

Publisher ---|

└---> Suscriber 2

Publisher (10.X.X.1) no error

Suscriber (10.X.X.2) 1 error

Suscriber (10.X.X.3) 2 error

Error: "Update failed. Could not insert new row - duplicate value in a UNIQUE INDEX column"

admin:utils dbreplication status

-------------------- utils dbreplication status --------------------

Replication status check is now running in background.
Use command 'utils dbreplication runtimestate' to check its progress

The final output will be in file cm/trace/dbl/sdi/ReplicationStatus.2015_03_27_14_08_10.out

Please use "file view activelog cm/trace/dbl/sdi/ReplicationStatus.2015_03_27_14_08_10.out " command to see the output
admin:
admin:utils dbreplication run
admin:utils dbreplication runtimestate

DB and Replication Services: ALL RUNNING

Cluster Replication State: Only available on the PUB

DB Version: ccm8_5_1_10000_26
Number of replicated tables: 530

Cluster Detailed View from SUB (3 Servers):

PING REPLICATION REPL. DBver& REPL. REPLICATION SETUP
SERVER-NAME IP ADDRESS (msec) RPC? STATUS QUEUE TABLES LOOP? (RTMT)
----------- ------------ ------ ---- ----------- ----- ------- ----- -----------------
CUCM-Pub1 10.X.X.1 0.511 Yes Connected 0 match Yes (2)
CUCM-Sus3 10.X.X.3 0.311 Yes Connected 0 match Yes (2)
CUCM-Sus2 10.X.X.2 0.032 Yes Connected 0 match Yes (2)

admin:

jairochavez · ‎03-27-2015

*****PUBLISHER*****

admin:run sql select * from numplan where dnorpattern='9521'
pkid fkroutepartition dnorpattern tkpatternusage cfbdestination cfnadestination fkroutefilter tknetworklocation fkdigitdiscardinstruction prefixdigitsout blockenable tkmixer calledpartytransformationmask fkcallingsearchspace_sharedlineappear fkdialplan fkcallingsearchspace_translation callingpartytransformationmask patternurgency tkstatus_usefullyqualcallingpartynum fkcallingsearchspace_cfb fkcallingsearchspace_cfna dialplanwizardgenid userholdmohaudiosourceid networkholdmohaudiosourceid callforwardexpansionmask tkautoanswer personalroutingenabled devicefailuredn fkcallingsearchspace_devicefailure callingpartyprefixdigits fkcallingsearchspace_mwi fkvoicemessagingprofile cfbvoicemailenabled cfnavoicemailenabled cfdfvoicemailenabled fkaarneighborhood withtag withvalueclause description cfnaduration tkpatternprecedence cfaptvoicemailenabled cfaptdestination tkreleasecausevalue fkcallingsearchspace_cfapt tkpresentationbit_connectedline tkpresentationbit_callingname tkpresentationbit_connectedname tkpresentationbit_callingline supportoverlapsending cfaptduration iscallable fkcallmanager alertingname authorizationcoderequired authorizationlevelrequired cfbintdestination cfbintvoicemailenabled cfnaintdestination cfnaintvoicemailenabled clientcoderequired cssforcfa fkcallingsearchspace_cfbint fkcallingsearchspace_pff fkcallingsearchspace_pffint pff_cfb pff_cfna pffdestination pffintdestination pffintvoicemailenabled pffvoicemailenabled fkcallingsearchspace_reroute fkmatrix_presence fkcallingsearchspace_cfnaint ismessagewaitingon outsidedialtone deviceoverride allowcticontrolflag alertingnameascii resettoggle tkreset aardestinationmask aarkeepcallhistory aarvoicemailenabled cfhrdn cfhrintdn cfhrintvmenabled cfhrvmenabled cfurdestination cfurintdestination cfurintvoicemailenabled cfurvoicemailenabled fkcallingsearchspace_cfhr fkcallingsearchspace_cfhrint fkcallingsearchspace_cfur fkcallingsearchspace_cfurint fkcallingsearchspace_revert hrduration hrinterval iknumplan_parkcode revertdestination cfhrduration tkdevicesecuritymode_minimumallowed tkcfacssactivationpolicy fkresourceprioritynamespace tknumberingplan_called tkpriofnumber_called tkpriofnumber_calling tknumberingplan_calling fkdevice_intercomdefault dnorpatternipv6 tkstatus_partyentrancetone parkmonforwardnoretrievedn parkmonforwardnoretrieveintdn parkmonforwardnoretrieveintvmenabled parkmonforwardnoretrievevmenabled fkcallingsearchspace_pkmonfwdnoret fkcallingsearchspace_pkmonfwdnoretint parkmonreversiontimer tkpatternrouteclass routenexthopbycgpn routeonuserpart usecallercss fkexternalcallcontrolprofile
==== ================ =========== ============== ============== =============== ============= ================= ========================= =============== =========== ======= ============================= ===================================== ========== ================================ ============================== ============== ==================================== ======================== ========================= =================== ======================== =========================== ======================== ============ ====================== =============== ================================== ======================== ======================== ======================= =================== ==================== ==================== ================= ======= =============== =========== ============ =================== ===================== ================ =================== ========================== =============================== ============================= =============================== ============================= ===================== ============= ========== ============= ============ ========================= ========================== ================= ====================== ================== ======================= ================== ========= =========================== ======================== =========================== ======= ======== ============== ================= ====================== =================== ============================ ================= ============================ ================== =============== ============== =================== ================= =========== ======= ================== ================== =================== ====== ========= ================ ============= =============== ================== ======================= ==================== ========================= ============================ ========================= ============================ =========================== ========== ========== ================== ================= ============ =================================== ======================== =========================== ====================== ==================== ===================== ======================= ======================== =============== ========================== ========================== ============================= ==================================== ================================= ================================== ===================================== ===================== =================== ================== =============== ============ ============================
admin:

*****SUSCRIBER*****

admin:run sql select * from numplan where dnorpattern = '9521'
pkid fkroutepartition dnorpattern tkpatternusage cfbdestination cfnadestination fkroutefilter tknetworklocation fkdigitdiscardinstruction prefixdigitsout blockenable tkmixer calledpartytransformationmask fkcallingsearchspace_sharedlineappear fkdialplan fkcallingsearchspace_translation callingpartytransformationmask patternurgency tkstatus_usefullyqualcallingpartynum fkcallingsearchspace_cfb fkcallingsearchspace_cfna dialplanwizardgenid userholdmohaudiosourceid networkholdmohaudiosourceid callforwardexpansionmask tkautoanswer personalroutingenabled devicefailuredn fkcallingsearchspace_devicefailure callingpartyprefixdigits fkcallingsearchspace_mwi fkvoicemessagingprofile cfbvoicemailenabled cfnavoicemailenabled cfdfvoicemailenabled fkaarneighborhood withtag withvalueclause description cfnaduration tkpatternprecedence cfaptvoicemailenabled cfaptdestination tkreleasecausevalue fkcallingsearchspace_cfapt tkpresentationbit_connectedline tkpresentationbit_callingname tkpresentationbit_connectedname tkpresentationbit_callingline supportoverlapsending cfaptduration iscallable fkcallmanager alertingname authorizationcoderequired authorizationlevelrequired cfbintdestination cfbintvoicemailenabled cfnaintdestination cfnaintvoicemailenabled clientcoderequired cssforcfa fkcallingsearchspace_cfbint fkcallingsearchspace_pff fkcallingsearchspace_pffint pff_cfb pff_cfna pffdestination pffintdestination pffintvoicemailenabled pffvoicemailenabled fkcallingsearchspace_reroute fkmatrix_presence fkcallingsearchspace_cfnaint ismessagewaitingon outsidedialtone deviceoverride allowcticontrolflag alertingnameascii resettoggle tkreset aardestinationmask aarkeepcallhistory aarvoicemailenabled cfhrdn cfhrintdn cfhrintvmenabled cfhrvmenabled cfurdestination cfurintdestination cfurintvoicemailenabled cfurvoicemailenabled fkcallingsearchspace_cfhr fkcallingsearchspace_cfhrint fkcallingsearchspace_cfur fkcallingsearchspace_cfurint fkcallingsearchspace_revert hrduration hrinterval iknumplan_parkcode revertdestination cfhrduration tkdevicesecuritymode_minimumallowed tkcfacssactivationpolicy fkresourceprioritynamespace tknumberingplan_called tkpriofnumber_called tkpriofnumber_calling tknumberingplan_calling fkdevice_intercomdefault dnorpatternipv6 tkstatus_partyentrancetone parkmonforwardnoretrievedn parkmonforwardnoretrieveintdn parkmonforwardnoretrieveintvmenabled parkmonforwardnoretrievevmenabled fkcallingsearchspace_pkmonfwdnoret fkcallingsearchspace_pkmonfwdnoretint parkmonreversiontimer tkpatternrouteclass routenexthopbycgpn routeonuserpart usecallercss fkexternalcallcontrolprofile
==================================== ==================================== =========== ============== ============== =============== ============= ================= ========================= =============== =========== ======= ============================= ===================================== ========== ================================ ============================== ============== ==================================== ==================================== ==================================== =================== ======================== =========================== ======================== ============ ====================== =============== ==================================== ======================== ======================== ==================================== =================== ==================== ==================== ================= ======= =============== ================ ============ =================== ===================== ================ =================== ========================== =============================== ============================= =============================== ============================= ===================== ============= ========== ============= ========================= ========================= ========================== ================= ====================== ================== ======================= ================== ========= ==================================== ==================================== ==================================== ======= ======== ============== ================= ====================== =================== ============================ ==================================== ==================================== ================== =============== ============== =================== ========================= =========== ======= ================== ================== =================== ====== ========= ================ ============= =============== ================== ======================= ==================== ========================= ============================ ==================================== ==================================== =========================== ========== ========== ================== ================= ============ =================================== ======================== =========================== ====================== ==================== ===================== ======================= ======================== =============== ========================== ========================== ============================= ==================================== ================================= ================================== ===================================== ===================== =================== ================== =============== ============ ============================
2cdc3023-3cb5-7552-611d-096019bfb505 bef3f747-8810-9e4a-6b83-b7dbe2ea3ed1 9521 2 NULL 0 NULL NULL f NULL NULL a407c0ed-ea21-a49a-ed4a-cb17374d12c7 NULL NULL NULL f 2 7b937af9-402d-c29d-0578-417c3fe37d50 7b937af9-402d-c29d-0578-417c3fe37d50 NULL 1 1 NULL 0 f NULL NULL NULL 00000000-1111-0000-0000-000000000000 t t f NULL 15 5 f 0 NULL 0 0 0 0 f NULL t NULL f 0 t t f NULL 7b937af9-402d-c29d-0578-417c3fe37d50 NULL NULL f f f f NULL ad243d17-98b4-4118-8feb-5ff2e1b781ac 7b937af9-402d-c29d-0578-417c3fe37d50 f f f t f 3 t f NULL NULL f f t t NULL NULL 7b937af9-402d-c29d-0578-417c3fe37d50 7b937af9-402d-c29d-0578-417c3fe37d50 NULL NULL NULL NULL NULL NULL NULL 0 NULL 0 0 0 0 NULL 2 f f NULL NULL NULL 0 f f t NULL
7ec8b0dc-ccc7-92e2-2849-eae07c2dc926 bef3f747-8810-9e4a-6b83-b7dbe2ea3ed1 9521 2 NULL 0 NULL NULL f NULL NULL a407c0ed-ea21-a49a-ed4a-cb17374d12c7 NULL NULL NULL f 2 NULL NULL NULL 1 1 NULL 0 f NULL NULL NULL 00000000-1111-0000-0000-000000000000 f f f NULL 9521 extension 15 5 f 0 NULL 0 0 0 0 f NULL t NULL 9521 Extension f 0 f f f NULL NULL NULL NULL f f f f NULL ad243d17-98b4-4118-8feb-5ff2e1b781ac NULL f f f t 9521 Extension f 3 t f NULL NULL f f f f NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL 0 0 0 0 NULL 2 f f NULL NULL NULL 0 f f t NULL
c5946ca3-c2ee-174b-1db1-16be662b5a73 bef3f747-8810-9e4a-6b83-b7dbe2ea3ed1 9521 2 NULL 0 NULL NULL f NULL NULL a407c0ed-ea21-a49a-ed4a-cb17374d12c7 NULL NULL NULL f 2 NULL NULL NULL 1 1 NULL 0 f NULL NULL NULL 00000000-1111-0000-0000-000000000000 f f f NULL NULL 5 f 0 NULL 0 0 0 0 f NULL t NULL f 0 f f f NULL NULL NULL NULL f f f f NULL ad243d17-98b4-4118-8feb-5ff2e1b781ac NULL f f f t t 3 t f NULL NULL f f f f NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL 0 0 0 0 NULL 2 f f NULL NULL NULL 0 f f t NULL
af8733c6-ec63-0ceb-70a4-dc11e327e7e0 bef3f747-8810-9e4a-6b83-b7dbe2ea3ed1 9521 2 NULL 0 NULL NULL f NULL NULL 7b937af9-402d-c29d-0578-417c3fe37d50 NULL NULL NULL f 2 7b937af9-402d-c29d-0578-417c3fe37d50 7b937af9-402d-c29d-0578-417c3fe37d50 NULL 1 1 NULL 0 f NULL NULL NULL 16c584ea-6a81-4138-9ea1-af452de8f75a t t f NULL ext9521 20 5 f 0 NULL 0 0 0 0 f NULL t NULL test f 0 t t f NULL 7b937af9-402d-c29d-0578-417c3fe37d50 NULL NULL f f f f NULL ad243d17-98b4-4118-8feb-5ff2e1b781ac 7b937af9-402d-c29d-0578-417c3fe37d50 f f f t test f 2 t f NULL NULL f f t t NULL NULL 7b937af9-402d-c29d-0578-417c3fe37d50 7b937af9-402d-c29d-0578-417c3fe37d50 NULL 0 0 NULL NULL NULL NULL 0 NULL 0 0 0 0 NULL 2 f f NULL NULL 0 0 f f t NULL
42c4f64e-378f-e450-5afb-2558b40939bd bef3f747-8810-9e4a-6b83-b7dbe2ea3ed1 9521 2 NULL 0 NULL NULL f NULL NULL NULL NULL NULL NULL f 2 NULL NULL NULL NULL NULL NULL 0 f NULL NULL NULL NULL f f f NULL ultima NULL 5 f 0 NULL 0 0 0 0 f NULL t NULL f 0 f f f NULL NULL NULL NULL f f f f NULL ad243d17-98b4-4118-8feb-5ff2e1b781ac NULL f f f t t 2 t f NULL NULL f f f f NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL 0 0 0 0 NULL 2 f f NULL NULL NULL 0 f f t NULL
53e6a1ae-9a91-c97a-6d58-6f7a746a744c bef3f747-8810-9e4a-6b83-b7dbe2ea3ed1 9521 2 NULL 0 NULL NULL f NULL NULL 08ddb818-9578-96d3-19be-c81d02082d4c NULL NULL NULL f 2 7b937af9-402d-c29d-0578-417c3fe37d50 7b937af9-402d-c29d-0578-417c3fe37d50 NULL 1 1 NULL 0 f 7b937af9-402d-c29d-0578-417c3fe37d50 NULL NULL 00000000-1111-0000-0000-000000000000 t t f NULL Extension 1 15 5 f 0 NULL 0 0 0 0 f NULL f NULL Extension 1 f 0 t t f NULL 7b937af9-402d-c29d-0578-417c3fe37d50 7b937af9-402d-c29d-0578-417c3fe37d50 7b937af9-402d-c29d-0578-417c3fe37d50 f f f f NULL ad243d17-98b4-4118-8feb-5ff2e1b781ac 7b937af9-402d-c29d-0578-417c3fe37d50 f f f t Extension 1 t 2 t f NULL NULL f f t t NULL NULL 7b937af9-402d-c29d-0578-417c3fe37d50 7b937af9-402d-c29d-0578-417c3fe37d50 NULL NULL NULL NULL NULL NULL NULL 0 NULL 0 0 0 0 NULL 2 f f NULL NULL NULL 0 f f t NULL
47f1fe63-639f-9201-fbe1-6a60a08d530a bef3f747-8810-9e4a-6b83-b7dbe2ea3ed1 9521 2 NULL 0 NULL NULL f NULL NULL 7b937af9-402d-c29d-0578-417c3fe37d50 NULL NULL NULL f 2 NULL NULL NULL 1 1 NULL 0 f NULL NULL NULL 00000000-1111-0000-0000-000000000000 f f f NULL 9521 Test NULL 5 f 0 NULL 0 0 0 0 f NULL t NULL 9521 Test f 0 f f f NULL NULL NULL NULL f f f f NULL ad243d17-98b4-4118-8feb-5ff2e1b781ac NULL f f f t 9521 Test t 3 t f NULL NULL f f f f NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL 0 0 0 0 NULL 2 f f NULL NULL NULL 0 f f t NULL
admin:

jairochavez · ‎03-27-2015

*****PUBLISHER*****

admin:
admin:run sql select pkid from numplan where dnorpattern='9521'
pkid
====
admin:

*****SUSCRIBER*****

admin:
admin:run sql select pkid from numplan where dnorpattern='9521'
pkid
====================================
2cdc3023-3cb5-7552-611d-096019bfb505
7ec8b0dc-ccc7-92e2-2849-eae07c2dc926
c5946ca3-c2ee-174b-1db1-16be662b5a73
af8733c6-ec63-0ceb-70a4-dc11e327e7e0
42c4f64e-378f-e450-5afb-2558b40939bd
53e6a1ae-9a91-c97a-6d58-6f7a746a744c
47f1fe63-639f-9201-fbe1-6a60a08d530a
admin:

Aditya Gupta · ‎03-27-2015

Hi

This clearly looks to be a DB issue.

Although Replication says 2, but clearly CUCM are not in sync.

You can use the following sql query on the cucm to delete these numbers.

run sql delete from numplan where pkid='2cdc3023-3cb5-7552-611d-096019bfb505'

Repeate the above query by changing the pkid's .

Also , i would suggest to reset your CUCM replication once, whenever possible

Regards

Aditya Gupta

dpkline01 · ‎02-18-2015

You may be able to check dependency records for the phone and see what is referencing it. Its under the related links drop down.

CUCM 8.5 Error1: Update failed. Could not insert new row - duplicate value in a UNIQUE INDEX column. Error2: The item with key "XXX" was not found in the database. Please select another item to view.

Error: "Update failed. Could not insert new row - duplicate value in a UNIQUE INDEX column"

Error: "Update failed. Could not insert new row - duplicate value in a UNIQUE INDEX column"