During CMDB CI Class Models and Discovery and Service Mapping Patterns application installation/update, some tables under TPP hierarchy are created in TPC structure - Known Error

During CMDB CI Class Models and Discovery and Service Mapping Patterns application installation/update, some tables under TPP hierarchy are created in TPC structure Description When additional CMDB Class tables are added as part of the installation of the CMDB CI Class Models [sn_cmdb_ci_class] or Discovery and Service Mapping Patterns [sn_itom_pattern] applications, some of the tables can be unusable. These apps are also automatically upgraded during an instance upgrade, and these upgrades adding additional tables has also caused the problem.

When a table is created wrongly, an additional SQL table may be created, and some storage alias entries mapped to that table, as if it were a Table-Per-Class extended table hierarchy, when the CMDB is in fact Table-Per-Partition hierarchy.

You can confirm if this affects you by checking the sys_storage_alias table in the instance. All the tables within cmdb_ci that are added by these apps have "Table" names starting "cmdb_ci_..", and the "Storage table name" should be cmdb, or cmdb$par1, cmdb$par2 etc. only. The "Storage table name" should not be the same as the "Table" name in a TPP CMDB.

Symptoms:

When opening a CI form or list view, you may see one or more red errors along the top of the form like this (but probably a different field/column and conn number):

Syntax Error or Access Rule Violation detected by database ((conn=23113) Unknown column 'cmdb0.duplicate_of' in 'where clause') It is necessary to look in the app node localhost logs for the full error.

Records may fail to be inserted into the affected table during Discovery, and that can be confirmed by attempting to insert a record from a form. Inserts by Discovery into these tables, are most likely being done by Patterns, via the IRE, and so the Discovery log may show something like:

Failed Exploring CI Pattern, Pattern name: , To Check Pattern Log... And looking in that Pattern log, you may see the errors from the pattern point of view, possibly for failure to Insert the application CI.

Identification CI Errors: Insertion failed with error Error during insert of cmdb_ci_... The detailed IRE and database error logs in the App node localhost log will show more detail, in this case due to duplicate values. This error has also been seen on forms.

FAILED TRYING TO EXECUTE ON CONNECTION ... Duplicate entry ' ' for key ' ' Alternatively, a Discovery Sensor (for probes or patterns) may fail at a particular line of code, leaving the error in the ecc_queue input Error field and discovery log. The error may not actually state the error. e.g.

Sensor error when processing Horizontal Pattern : ----------------------------- Stack: Message:

----------------------------- (sys_script_include.778011130a0a0b2500c4595ad1d1d768.script; line 53) CMDB Dashboard Health jobs may also display no data, and show errors in the localhost log like the following:

SEVERE *** ERROR *** FAILED TRYING TO EXECUTE ON CONNECTION glide.3 (connpid=366537): SELECT cmdb0.`attested_date`, cmdb0.`operational_status`, cmdb... Syntax Error or Access Rule Violation detected by database ((conn=366537) Unknown column 'cmdb0.attested_date' in 'field list')

com.glide.db.GlideSQLException: FAILED TRYING TO EXECUTE ON CONNECTION glide.3 (connpid=366537): SELECT cmdb0.`attested_date`, cmdb0.`operational_status`, ... FROM (cmdb cmdb0 INNER JOIN cmdb$par1 cmdb_par10 ON cmdb0.`sys_id` = cmdb_par10.`sys_id` ) WHERE cmdb0.`sys_class_name_1` = ' cmdb_ci_db_ora_pdb_instance ' AND c... Syntax Error or Access Rule Violation detected by database ((conn=366537) Unknown column 'cmdb0.attested_date' in 'field list' )

at com.glide.db.DBIError.wrapAndThrow(DBIError.java:153) at com.glide.db.DBIError.handleException(DBIError.java:86) at com.glide.db.DBI.executeStatement(DBI.java:1110) ... at com.snc.cmdb.health.MetricManager.getTotals(MetricManager.java:256) at com.snc.cmdb.health.ScoreProcessorCmdb.getTotals(ScoreProcessorCmdb.java:94) at com.snc.cmdb.health.ScoreCalculator.calculateScoreForEachSubMetric(ScoreCalculator.java:171) at com.snc.cmdb.health.ScoreCalculator.calculateScores(ScoreCalculator.java:143) at com.snc.cmdb.health.ScoreCalculator.calculateScoresSynchronized(ScoreCalculator.java:96) at com.snc.cmdb.health.ScoreManager.processCmdbScoreCalculation(ScoreManager.java:58) at com.snc.cmdb.health.ScoreManager.processScoreCalculation(ScoreManager.java:25) at com.snc.cmdb.health.MetricProcessor.process(MetricProcessor.java:141) ... Potentially affected tables:

CMDB CI Class Models [sn_cmdb_ci_class] adds these tables (as of v1.13.0):

Label Name Extends table Aircraft cmdb_ci_aircraft Transport Type AVI Controller cmdb_ci_avi_controller Virtual Machine Object AVI Controller Cluster cmdb_ci_avi_controller_cluster Virtual Machine Object AVI Service Engine cmdb_ci_avi_service_engine Virtual Machine Object Unique Certificate cmdb_ci_certificate Configuration Item OpenStack Domain cmdb_ci_cloud_openstack_domain Virtual Machine Object OpenStack Endpoint cmdb_ci_cloud_openstack_endpoint Virtual Machine Object OpenStack Service cmdb_ci_cloud_openstack_service Virtual Machine Object Converged Infrastructure cmdb_ci_converged_infra Hardware Custom Application cmdb_ci_custom_application Configuration Item Display Device cmdb_ci_display IoT Device Drone cmdb_ci_drone IoT Device Firewall Cluster cmdb_ci_firewall_cluster Cluster Fortinet Firewall Cluster cmdb_ci_firewall_cluster_fortinet Firewall Cluster Juniper Firewall Cluster cmdb_ci_firewall_cluster_juniper Firewall Cluster Firewall Device cmdb_ci_firewall_device IP Firewall Fortinet Firewall Device cmdb_ci_firewall_device_fortinet Firewall Device Firewall Device Group cmdb_ci_firewall_device_group Configuration Item Juniper Firewall Device cmdb_ci_firewall_device_juniper Firewall Device Palto Alto Firewall Device cmdb_ci_firewall_device_palo_alto Firewall Device Firewall Manager cmdb_ci_firewall_manager Configuration Item Panorama Firewall Manager cmdb_ci_firewall_manager_panorama Firewall Manager Firewall Security Policy cmdb_ci_firewall_sec_policy Configuration Item Panorama Firewall Security Policy cmdb_ci_firewall_sec_policy_panorama Firewall Security Policy Game Console cmdb_ci_game_console Multimedia Device IBM HMC Server cmdb_ci_hmc_server Server Human Machine Interface cmdb_ci_hmi Manufacturing Device IBM Frame cmdb_ci_ibm_frame Server Imaging Device cmdb_ci_imaging IoT Device IoT Device cmdb_ci_iot Hardware IoT Gateway cmdb_ci_iot_gateway Converged Infrastructure IP Camera cmdb_ci_ip_camera Imaging Device Kubernetes Cluster cmdb_ci_kubernetes_cluster Kubernetes Component Kubernetes Component cmdb_ci_kubernetes_component Configuration Item Kubernetes Namespace cmdb_ci_kubernetes_namespace Kubernetes Component Kubernetes Node cmdb_ci_kubernetes_node Kubernetes Component Kubernetes Pod cmdb_ci_kubernetes_pod Kubernetes Component Kubernetes Service cmdb_ci_kubernetes_service Kubernetes Component Kubernetes Volume cmdb_ci_kubernetes_volume Kubernetes Component IBM LPAR Instance cmdb_ci_lpar_instance Virtual Machine Instance LPAR Resource cmdb_ci_lpar_resource Configuration Item Custom Application Configuration Item cmdb_ci_m2m_custom_application_ci Manufacturing Device cmdb_ci_manufacturing IoT Device Media Player cmdb_ci_media_player Multimedia Device Medical Device cmdb_ci_medical IoT Device Medical Diagnostic Device cmdb_ci_med_diagnostic Medical Device Medical Measuring Device cmdb_ci_med_measuring Medical Device Medical Monitoring Device cmdb_ci_med_monitoring Medical Device Medical Support Device cmdb_ci_med_support Medical Device Medical Therapeutic Device cmdb_ci_med_theraputic Medical Device Display Monitor Control cmdb_ci_monitor_control Multimedia Device Multimedia Device cmdb_ci_multimedia IoT Device NSX Load Balancer cmdb_ci_nsx_lb Load Balancer Nutanix Cluster cmdb_ci_nutanix_cluster Virtual Machine Object Nutanix Controller VM cmdb_ci_nutanix_controller_vm Application Nutanix Host cmdb_ci_nutanix_host Virtualization Server Nutanix Prism Central cmdb_ci_nutanix_prism_central Virtual Machine Object Nutanix Storage Container cmdb_ci_nutanix_storage_container Storage Volume Nutanix Storage Pool cmdb_ci_nutanix_storage_pool Storage Pool Nutanix Virtual Machine Instance cmdb_ci_nutanix_vm_instance Virtual Machine Instance Payment Device cmdb_ci_payment IoT Device Process Logic Controller cmdb_ci_plc Manufacturing Device Point of Sale Device cmdb_ci_pos Payment Device IBM HMC Processor pool cmdb_ci_processor_pool Virtual Machine Object RHV Cluster cmdb_ci_rhv_cluster RHV Object RHV LDC cmdb_ci_rhv_ldc Logical Datacenter RHV Manager cmdb_ci_rhv_manager Application RHV Object cmdb_ci_rhv_object Virtual Machine Object RHV Server cmdb_ci_rhv_server Virtualization Server RHV Virtual Machine Instance cmdb_ci_rhv_vm_instance Virtual Machine Instance Single Board Computing cmdb_ci_sbc IoT Device Security Device cmdb_ci_security IoT Device Ship cmdb_ci_ship Transport Type Smart Television cmdb_ci_stv Display Device Train cmdb_ci_train Transport Type Transport Type cmdb_ci_transport Configuration Item Vehicle cmdb_ci_vehicle Transport Type Wearable Technology cmdb_ci_wearable IoT Device Discovery and Service Mapping Patterns [sn_itom_pattern] adds these (as of v1.0.66):

Label Name Extends table APIGee EP to get targets host by source url cmdb_ci_apigee_endpoint Endpoint Application Server Resource cmdb_ci_application_server_resource Application APIGee Service cmdb_ci_appl_apigee_srv Application Jboss Fuse cmdb_ci_appl_jboss_fuse Application Oracle Golden Gate cmdb_ci_appl_oracle_golden_gate Application Oracle Golden Gate Extract Process cmdb_ci_appl_ora_gg_extract Application Oracle Golden Gate Replicat Process cmdb_ci_appl_ora_gg_replicat Application AWS S3 Endpoint cmdb_ci_aws_s3_endpoint Endpoint Coldfusion Application cmdb_ci_cf_application Application Server Resource Cloud Authentication cmdb_ci_cloud_authentication Application AWS Cloud ECS Cluster cmdb_ci_cloud_ecs_cluster Virtual Machine Object AWS Cloud ECS Service cmdb_ci_cloud_ecs_service Virtual Machine Object AWS Cloud ECS Task cmdb_ci_cloud_ecs_task Virtual Machine Object AWS Cloud ECS TaskDefinition cmdb_ci_cloud_ecs_task_definition Virtual Machine Object Cloud Function cmdb_ci_cloud_function Application Cloud Gateway cmdb_ci_cloud_gateway Application Cloud Key Pair cmdb_ci_cloud_key_pair Configuration Item Cloud Location Group cmdb_ci_cloud_location_group Virtual Machine Object Cloud Object Storage cmdb_ci_cloud_object_storage Application IBM Cloud Organizations cmdb_ci_cloud_org Configuration Item Cloud Service Account cmdb_ci_cloud_service_account Virtual Machine Object IBM Cloud Space cmdb_ci_cloud_space Configuration Item Cluster Resource Group cmdb_ci_cluster_resource_group Configuration Item Coldfusion App Inclusion cmdb_ci_coldfusion_app_inclusion Inclusion Endpoint Coldfusion Server cmdb_ci_coldfusion_server Application Server Security Group Rule cmdb_ci_compute_security_group_rule Virtual Machine Object Hardware Type cmdb_ci_compute_template Virtual Machine Object Informix Catalog cmdb_ci_db_informix_catalog Database Instance Informix Catalog Inclusion cmdb_ci_db_informix_catalog_inclusion Inclusion Endpoint Informix Instance cmdb_ci_db_informix_instance Database Instance DB Instance Size cmdb_ci_db_instance_size Database Instance Oracle PDB Instance cmdb_ci_db_ora_pdb_instance Database Instance Cloud Disk Type cmdb_ci_disk_type Virtual Machine Object Docker Container cmdb_ci_docker_container Operating-system-level Virtualization Container DynamoDB Global Table cmdb_ci_dynamodb_global_table Database Instance DynamoDB Table cmdb_ci_dynamodb_table Database Instance AWS Resource Name Endpoint cmdb_ci_endpoint_arn Endpoint Informix Server Endpoint cmdb_ci_endpoint_informix_server Endpoint Google Datacenter cmdb_ci_google_datacenter Logical Datacenter IBM Datacenter cmdb_ci_ibm_datacenter Logical Datacenter Instance template cmdb_ci_instance_template Virtual Machine Object Cloud Load Balancer Health Service cmdb_ci_lb_health_service Virtual Machine Object Load Balancer Service cmdb_ci_lb_service Configuration Item Network ACL Rule cmdb_ci_network_acl_rule Virtual Machine Object OpenShift Build Config cmdb_ci_openshift_build_conf Kubernetes Component OpenShift Deployed Config cmdb_ci_openshift_dep_conf Kubernetes Component OpenShift Docker Image Repository cmdb_ci_openshift_docker_images_repository Kubernetes Component OpenShift Group cmdb_ci_openshift_group Kubernetes Component OpenShift Image cmdb_ci_openshift_images Kubernetes Component OpenShift Image Stream cmdb_ci_openshift_images_stream Kubernetes Component OpenShift Project cmdb_ci_openshift_project Kubernetes Component OpenShift Route cmdb_ci_openshift_route Kubernetes Component OpenShift Source2Image cmdb_ci_openshift_source_2_image Kubernetes Component OpenShift User cmdb_ci_openshift_user Kubernetes Component CloudFoundry Application cmdb_ci_pcf_appl CloudFoundry Component CloudFoundry Component cmdb_ci_pcf_component Configuration Item CloudFoundry Domain cmdb_ci_pcf_domain CloudFoundry Component CloudFoundry Cluster cmdb_ci_pcf_foundation CloudFoundry Component CloudFoundry Organization cmdb_ci_pcf_organization CloudFoundry Component CloudFoundry Quota cmdb_ci_pcf_quota CloudFoundry Component CloudFoundry Route cmdb_ci_pcf_route CloudFoundry Component Cloud Foundry Service cmdb_ci_pcf_service CloudFoundry Component Cloud Foundry Service Plan cmdb_ci_pcf_service_plan CloudFoundry Component CloudFoundry Space cmdb_ci_pcf_space CloudFoundry Component CloudFoundry Space Service Instance cmdb_ci_pcf_space_instance CloudFoundry Component Resource Group cmdb_ci_resource_group Virtual Machine Object SAP System cmdb_ci_sap_sid Application Pure Storage File Share cmdb_ci_storage_fileshare_pure Storage File Share Pure Storage Processor cmdb_ci_storage_processor_pure Configuration Item Virtual Machine Instance cmdb_ci_vm_instance Virtual Machine Object Steps to Reproduce

This hasn't so far been reproduced in controlled conditions.

Where this has happened, 1 or more tables are affected, but not the same tables in each case.

Workaround This problem is currently under review and has no workaround. To receive notifications when more information will become available, subscribe to this Known Error article by clicking the Subscribe button at the top right of this form. If you are able to upgrade, review the Fixed In or Intended Fix Version fields to determine whether any versions have a planned or permanent fix.

If the production instance is not affected, then sub-production instances can be fixed by re-cloning over them from production.

Each case has been different, and required a fix designed specifically for the tables affected and the instance configuration, and so no generic fix script is available. Please contact Tech Support as soon as you have identified this issue, as help will be needed for a low-level fix.