Data truncation: Data too long for column 'ItemName' at row 1
Article ID: 106922
Updated On:
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 . . .
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
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
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)
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)
Feedback
Yes
No
Powered by
