CMDB Identification Payload error - "Insertion failed with error Error during insert of cmdb_ci...", where node logs show "FAILED TRYING TO EXECUTE ON CONNECTION" "Duplicate entry 'XXX' for key 'XXX'"Issue When a payload is given to the CMDB Identification and Reconciliation API to insert a CI, you may see the error: Insertion failed with error Error during insert of <table name> That error doesn't tell you why the insert failed, and there are many different reasons for this. You need to look in the app node logs too. In there you may see something like: FAILED TRYING TO EXECUTE ON CONNECTION ... Duplicate entry '<some value>' for key '<column name>' For example: You may see it buried in a Discovery Pattern log: 2020-01-20 21:00:53: Identification CI Errors:Insertion failed with error Error during insert of cmdb_ci_cmp_resource (prd-standard-public-access-acl),Insertion failed with error Error during insert of cmdb_ci_cmp_resource (arn:aws:acm:us-east-1:337473618329:certificate/db68872f-d85b-4644-8aa6-83d6714a1d43),Insertion failed with error Error during insert of cmdb_ci_cmp_resource (E29TM5GDLIM2I4),Insertion failed with error Error during insert of cmdb_ci_cmp_resource (E2S7XP0RM8V8U7),Insertion failed with error Error during insert of cmdb_ci_cmp_resource (E3VS4AWE7G2OMH),Insertion failed with error Error during insert of cmdb_ci_cmp_resource (ED16G2GSDDC18),Insertion failed with error Error during insert of cmdb_ci_cmp_resource (EGE7BGIT6E1WJ),Insertion failed with error Error during insert of cmdb_ci_cmp_resource (EGGTMLZ6GJBAA),Insertion failed with error Error during insert of cmdb_ci_cmp_resource (EYUX5I1VHVCQT),Insertion failed with error Error during insert of cmdb_ci_cmp_resource (ebs-snapper-3374736... And the app node log to go with it: FAILED TRYING TO EXECUTE ON CONNECTION glide.21 (connpid=1331800): com.glide.db.BatchStatement$StatementContainer@dcabca,com.glide.db.BatchStatement$StatementContainer@1bc50d8java.sql.BatchUpdateException: (conn=1331800) Duplicate entry 'AWS::RDS::DBSecurityGroup' for key 'a_str_35': java.sql.SQLException: java.sql.BatchUpdateException: (conn=1331800) Duplicate entry 'AWS::RDS::DBSecurityGroup' for key 'a_str_35': com.glide.db.StatementBatcher.getSQLException(StatementBatcher.java:547)com.glide.db.StatementBatcher.commitBatch(StatementBatcher.java:502)ReleaseAny feature using the Identification Engine to insert/update CIs.CauseThe cause is: a SQL-level Unique constraint on the CMDB fielda record already exists with the same value in that field Using information from the example error above, in this case we have an insert into table cmdb_ci_cmp_resource, and a unique constraint on field a_str_35, but this could in theory be any CI class or field.ResolutionIdentify the real field name If the 'key' is 'PRIMARY', this means the sys_id field, and this shouldn't happen. The IRE ought to be updating where the sys_id already exists, not inserting. This may be a symptom of bad Database Triggers.The 'key' may be an alias and not the real field name. The CMDB has been using the The Table Per Partition (TPP) Extension Model since the Jakarta version. This can re-use columns in the SQL database table for multiple fields, when the fields are defined in separate and independent branches of the CMDB tree. Some alias column names will start "a_" (e.g. 'a_str_35'), but others may have the original real column name from before the field was subsequently re-used for other fields.That means the SQL level column name (key) shown in the node logs error may not be named the same as the the application-level field name, and may be used by other application level fields in other classes of the CMDB. The mapping between dictionary field name and actual sql table column is in the sys_storage_alias table. Open a list of sys_storage_aliasFilter on: Storage Alias IS <key name in node log error>, AND Table IS <table name from IRE error>The 'Element Name' is the field name from the point of view of the CI class we were inserting into Check that field's Unique settings in the Dictionary Navigate to System Definition - Dictionary (sys_dictionary)Filter on: Table IS <table name from IRE error>, AND Column name IS <Element name from storage alias table>Open that record and check if this Field is set as Unique in the dictionaryIf this Field is set as Unique in the dictionary: Should it be Unique? Check the "Show File Properties" from the header menu. Perhaps this is a customisation that needs reverting?We don't recommend making identification fields unique in the CMDB. See KB0780914 Why setting CMDB/Asset Serial Number field as Unique to prevent duplicates is not a good ideaCheck if a record does exist in this table with this value in this field.This would explain the error. You now need to work out why you are trying to insert another with the same value. Perhaps it is correct to want to insert this record, and the unique constraint is not wanted. The solution would usually be to remove the unique constraint. If it is NOT set Unique in the dictionary: It is possible that another field in the CMDB is sharing this column, and is set as Unique, because it does need to be unique. The unique index added by that field will also apply to this field, even though you don't want it unique. This is a bug and should not happen. Find which other fields use this alias, and then check the Dictionary record of each. Open a list of sys_storage_aliasFilter on: Storage Alias IS <key name in node log error>Group by 'Element Name', to see what other field names share this alias If you do find one set as Unique, the usual solution in this situation is to have ServiceNow Tech Support help do this: find another field of the same type that could be shared instead, which doesn't have a unique constraintFor each record of this class (and sub-classes) only, copy the field value from the old field to the new field.Update sys_db_alias to use the new field for this CI class/table, plus all child classes.