dst.address missing

Products

• CA Performance Management - Usage and Administration
• DX NetOps

Issue/Introduction

Global sync is failing and we are seeing this error after a fresh install and trying to add NFA as a data source.

INFO   | jvm 1    | 2021/02/02 18:51:57 | ERROR | pool-4-thread-1          | 2021-02-02 18:51:57,029 | com.ca.im.portal.dm.productsync.DataSourcePoller                 
INFO   | jvm 1    | 2021/02/02 18:51:57 |       | Exception thrown by sync task: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [CREATE TEMPORARY TABLE temp_map_items (INDEX IDs_Index(SourceID, LocalID)) SELECT dd.SourceID, dd.LocalID FROM dst_device dd INNER JOIN dst_address da ON da.Address=dd.Address WHERE (da.UpdatedOn >= 1612271885 OR da.UpdatedOn=0) GROUP BY dd.SourceID, dd.LocalID]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'netqosportal.dst_address' doesn't exist
INFO   | jvm 1    | 2021/02/02 18:51:57 | java.util.concurrent.ExecutionException: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [CREATE TEMPORARY TABLE temp_map_items (INDEX IDs_Index(SourceID, LocalID)) SELECT dd.SourceID, dd.LocalID FROM dst_device dd INNER JOIN dst_address da ON da.Address=dd.Address WHERE (da.UpdatedOn >= 1612271885 OR da.UpdatedOn=0) GROUP BY dd.SourceID, dd.LocalID]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'netqosportal.dst_address' doesn't exist

 

Cause

NFA is mostly interface driven and if only interfaces are synced to DX NetOps Portal the dst.address which holds devices addresses will not get created. 

Environment

Release : 20.2

Component : IM Reporting / Admin / Configuration

Resolution

Need to create the dst.address table

1. Stop services 

service caperfcenter_console stop
 
service caperfcenter_devicemanager stop
 
service caperfcenter_eventmanager stop
 
service caperfcenter_sso stop
 

2. Create the table in mysql. 

mysql -unetqos -p<PASSWORD> netqosportal
mysql> CREATE TABLE `dst_address` (
      `SourceID` int(10) unsigned NOT NULL,
      `LocalID` varchar(32) NOT NULL,
      `ItemSubTypeName` varchar(32) NOT NULL,
      `ItemID` int(10) unsigned DEFAULT NULL,
      `ItemSubType` smallint(5) unsigned DEFAULT NULL,
      `UpdatedOn` int(10) unsigned NOT NULL DEFAULT '0',
      `Address` tinyblob,
      `DomainID` varchar(32) DEFAULT NULL,
      `ItemName` varchar(255) DEFAULT NULL,
      `MaskBits` int(10) unsigned DEFAULT NULL,
      `AlternateName` varchar(255) DEFAULT NULL,
      `OwnerDescription` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`SourceID`,`LocalID`),
      KEY `ItemSubTypeName_Index` (`SourceID`,`ItemSubTypeName`),
      KEY `ItemID_Index` (`ItemID`),
      KEY `Address_Index` (`Address`(16)),
      KEY `DomainID_Index` (`DomainID`),
      KEY `ItemName_Index` (`ItemName`),
      KEY `MaskBits_Index` (`MaskBits`),
      KEY `AlternateName_Index` (`AlternateName`),
      KEY `OwnerDescription_Index` (`OwnerDescription`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
mysql> quit 

3. Restart services 

service caperfcenter_sso start

service caperfcenter_eventmanager start
 
service caperfcenter_devicemanager start

service caperfcenter_console start