Global Sync failed - errors regarding ItemID cannot be null seen in the DMService.log file

Products

• Network Observability
• CA Performance Management

Issue/Introduction

Case A:

 

| Exception thrown by sync task: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO temp_multiple_items (ItemID) SELECT ItemID FROM dst_network_path GROUP BY ItemID HAVING COUNT(*) > 1]; Column 'ItemID' cannot be null
java.util.concurrent.ExecutionException: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO temp_multiple_items (ItemID) SELECT ItemID FROM dst_network_path GROUP BY ItemID HAVING COUNT(*) > 1]; Column 'ItemID' cannot be null
at java.util.concurrent.FutureTask.report(Unknown Source) ~[?:?]
at java.util.concurrent.FutureTask.get(Unknown Source) ~[?:?]
at com.ca.im.portal.dm.productsync.SyncPhaseExecutor.execute(SyncPhaseExecutor.java:72) ~[classes/:?]
at com.ca.im.portal.dm.productsync.DataSourcePoller.pollLoopInternal(DataSourcePoller.java:309) [classes/:?]
at com.ca.im.portal.dm.productsync.DataSourcePoller.pollLoop(DataSourcePoller.java:254) [classes/:?]
at com.ca.im.portal.dm.productsync.DataSourcePoller$2.run(DataSourcePoller.java:559) [classes/:?]
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) [?:?]
at java.util.concurrent.FutureTask.run(Unknown Source) [?:?]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source) [?:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [?:?]
at java.lang.Thread.run(Unknown Source) [?:?]
Caused by: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO temp_multiple_items (ItemID) SELECT ItemID FROM dst_network_path GROUP BY ItemID HAVING COUNT(*) > 1]; Column 'ItemID' cannot be null

 

 

Case B:

 

ERROR | pool-5-thread-1          | 2025-06-05 18:21:21,698 | com.ca.im.portal.dm.productsync.DataSourcePoller
      | Exception thrown by sync task: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO temp_multiple_items (ItemID) SELECT ItemID FROM dst_tunnel GROUP BY ItemID HAVING COUNT(*) > 1]; Column 'ItemID' cannot be null
java.util.concurrent.ExecutionException: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO temp_multiple_items (ItemID) SELECT ItemID FROM dst_tunnel GROUP BY ItemID HAVING COUNT(*) > 1]; Column 'ItemID' cannot be null
at java.util.concurrent.FutureTask.report(Unknown Source) ~[?:?]
at java.util.concurrent.FutureTask.get(Unknown Source) ~[?:?]
at com.ca.im.portal.dm.productsync.SyncPhaseExecutor.execute(SyncPhaseExecutor.java:72) ~[classes/:?]
at com.ca.im.portal.dm.productsync.DataSourcePoller.pollLoopInternal(DataSourcePoller.java:309) [classes/:?]
at com.ca.im.portal.dm.productsync.DataSourcePoller.pollLoop(DataSourcePoller.java:254) [classes/:?]
at com.ca.im.portal.dm.productsync.DataSourcePoller$2.run(DataSourcePoller.java:559) [classes/:?]
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) [?:?]
at java.util.concurrent.FutureTask.run(Unknown Source) [?:?]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source) [?:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [?:?]
at java.lang.Thread.run(Unknown Source) [?:?]
Caused by: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO temp_multiple_items (ItemID) SELECT ItemID FROM dst_tunnel GROUP BY ItemID HAVING COUNT(*) > 1]; Column 'ItemID' cannot be null

Environment

DX NetOps Performance Management: Any version

Resolution

Case A:

Bring down the Portal Services.

systemctl stop caperf*

Make proper backups of the netqosportal database before proceeding.

https://techdocs.broadcom.com/us/en/ca-enterprise-software/it-operations-management/performance-management/24-3/administrating/performance-center-administration/back-up-performance-center.html

Then login to MySQL and run these queries:

use netqosportal;

create table deleteme(sourceid int unsigned, localid varchar(32), itemid int unsigned);

insert into deleteme select sourceid, localid, itemid from item_identifiers where localid in (select localid from dst_network_path where itemid is null);

delete from item_identifiers where (sourceid, localid, itemid) in (select sourceid, localid, itemid from deleteme);

delete from items where itemid in (select itemid from deleteme);

update ds_items set itemid=NULL  where (sourceid, localid, itemid) in (select sourceid, localid, itemid from deleteme);

drop table deleteme;

 

Start portal services and wait for Global Sync to run, should no longer throw an error.

systemctl start caperfcenter_sso caperfcenter_eventmanager caperfcenter_devicemanager; sleep 15; systemctl start caperfcenter_console

 

Case B:

Bring down the Portal Services.

systemctl stop caperf*

Make proper backups of the netqosportal database before proceeding.

https://techdocs.broadcom.com/us/en/ca-enterprise-software/it-operations-management/performance-management/24-3/administrating/performance-center-administration/back-up-performance-center.html

Then login to MySQL and run these queries:

mysql> use netqosportal;

mysql> create table deleteme(sourceid int unsigned, localid varchar(32), itemid int unsigned);

mysql> insert into deleteme select sourceid, localid, itemid from item_identifiers where localid in (select localid from dst_tunnel where itemid is null);

mysql> delete from item_identifiers where (sourceid, localid, itemid) in (select sourceid, localid, itemid from deleteme);

mysql> delete from items where itemid in (select itemid from deleteme);

mysql> update ds_items set itemid=NULL  where (sourceid, localid, itemid) in (select sourceid, localid, itemid from deleteme);

mysql> delete from item_members where childid in (select itemid from deleteme);

mysql> delete from item_members where parentid in (select itemid from deleteme);

mysql> update ds_item_members set parentitemid=NULL where (sourceid, parentlocalid, parentitemid) in (select sourceid, localid, itemid from deleteme); 

mysql> update ds_item_members set parentitemid=NULL where (sourceid, childlocalid, childitemid) in (select sourceid, localid, itemid from deleteme);

mysql> drop table deleteme;

 

Start portal services and wait for Global Sync to run, should no longer throw an error.

systemctl start caperfcenter_sso caperfcenter_eventmanager caperfcenter_devicemanager; sleep 15; systemctl start caperfcenter_console