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 is created, and 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: <some 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 '<some value>' for key '<column name>' 

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

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.

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.

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.