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

 

This Case also applies to "network_path", e.g.:

ERROR | pool-5-thread-1          | 2026-03-12 11:21:18,713 | com.ca.im.portal.dm.productsync.DataSourcePoller                 
      | Exception thrown by sync task: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [REPLACE INTO items (ItemID, ItemFlags, ItemType, ItemSubType, UpdatedOn) SELECT di.ItemID, di.ItemFlags & -25, di.ItemType, di.ItemSubType, 1773328873 FROM ds_items di WHERE di.UpdatedOn>=1771657549 AND di.ItemTypeName='network_path']; 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

Cause

Possibly the global sync was running / writing info to various tables when DM was restarted.  So this could be a backup running or possibly patching / rebooting

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

Additional Information

Defect DE181724 has addressed this with a partial fix for 25.4.9 (documented here) 

• Symptom:

   When a device is deleted in the data aggregator and synchronized to 

  NetOps Portal

  , and a tunnel, network path, or web path synchronizes a new/updated item, global synchronization can throw a SQL error about inserting a NULL into a table.

   

  Resolution:

   With this fix, the SQL calls in data source pull and global synchronization now check for NULLs when doing a insert/replace into a table that does not accept NULLs.

   

  (25.4.9, DE181724, 36772367, 36800559, 36844469, 36843740, 36881272, 36859258)

 

The full fix is addressed in 25.4.10 in DE205033 which has a rough ETA by the end of July 2026.  This will still require customers to manually clean up any bad entries via the steps in the resolution above.  The fixes will stop future occurances of the issue.