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

Products

• Network Observability
• CA Performance Management

Issue/Introduction

| 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

Resolution

Bring down the Portal Services.  Make proper backups of the netqosportal database before proceeding.

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.