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
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.
Release : 20.2
Component : IM Reporting / Admin / Configuration
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