Data truncation: Data too long for column 'ItemName' at row 1

Article Id: 106922
Status: Published
Updated On: 17-07-2018 11:35
Products:
CA Infrastructure Management CA Infrastructure Management CA Performance Management - Usage and Administration CA Performance Management - Data Polling
Issue/Introduction:

The synchronization failure between CAPC an CA Spectrum.

The CAPC DMService.log file shows the following error:

CAPC encountered an error while trying to sync with data source Spectrum Infrastructure [email protected] The problem is in CAPC, not the data source.
The following stack trace shows the invalid SQL statement in CAPC: org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [INSERT INTO dst_device (SourceID, UpdatedOn, `ItemSubTypeName`, `LocalID`, `ItemName`, `ItemDesc`, `Address`, `DomainID`, `SnmpReadID`, `SnmpWriteID`) VALUES (7,1531516102,'switches','0x21052b0','ABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDAB','Huawei AR1220E Huawei Versatile Routing Platform Software VRP (R) software,Version 5.160 (AR1220E V200R007C00SPC900) Copyright (C) 2011-2016 Huawei Technologies Co., Ltd',0x00000000000000000000ffff0afdfa68,3,NULL,NULL) ON DUPLICATE KEY UPDATE UpdatedOn=1531516102, `ItemSubTypeName`=VALUES(`ItemSubTypeName`), `LocalID`=VALUES(`LocalID`), `ItemName`=VALUES(`ItemName`), `ItemDesc`=VALUES(`ItemDesc`), `Address`=VALUES(`Address`), `DomainID`=VALUES(`DomainID`), `SnmpReadID`=VALUES(`SnmpReadID`), `SnmpWriteID`=VALUES(`SnmpWriteID`)]; Data truncation: Data too long for column 'ItemName' at row 1; nested exception is com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'ItemName' at row 1 . . .

 

Cause:

This is the limitation is on CAPC mysql

Message SQL [INSERT INTO dst_device... Data too long for column 'ItemName' at row 1

--
-- Table structure for table `dst_device`
--

CREATE TABLE `dst_device` (
`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,
`Elevation` double DEFAULT NULL,
`ItemDesc` varchar(255) DEFAULT NULL,
`ItemName` varchar(100) DEFAULT NULL,
`Latitude` double DEFAULT NULL,
`LifeCycleState` varchar(32) DEFAULT 'active',
`LocationDesc` varchar(255) DEFAULT NULL,

We can see that the ItemName has varchar(100)

And the Device name on Spectrum ABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDAB
has 102 characters
 

Environment:

pm 3.x

Resolution:

Rename the Device name on Spectrum removing the last two digit like this:

From
ABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDAB

To
ABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCDABCD

Select "Perform a Full Resynchronization" on CAPC Spectrum Data Source
 

Additional Information:

If the device is from DA this behavior was fixed on PM 3.5:
https://docops.ca.com/ca-performance-management/3-5/en/release-notes/fixed-issues

Symptom: If an interface item has an ifAlias of more than 255 characters then upon on Resync All it causes CAPC to show a Synchronization Failure with the DA. In addition the DMService.log file will show an exception related to a SQL insert failure.

Resolution: For ifAlias fields we now truncate any characters beyond the 255 character limit.
(3.5.0, DE292547)