NFA 9.5 migrator failed due to "Data truncation: Truncated incorrect INTEGER value"

Document ID : KB000113677
Last Modified Date : 12/09/2018
Show Technical Document Details
Issue:
INFO  Projector::play() - Frame #152: [SqlAction database=reporter port=3308 sql='UPDATE TopHosts SET Host = inet_ntoa(Host)'] 
FATAL TheSet::play() - Error playing back movie 
com.ca.im.migrator.exceptions.MigratorException: Movie playback failed.
    at com.ca.im.migrator.business.Projector.play(Unknown Source)
    at com.ca.im.migrator.TheSet.play(Unknown Source)
    at com.ca.im.migrator.TheSet.migrate(Unknown Source)
    at com.ca.im.migrator.MigratorApp.main(Unknown Source)
Caused by: com.ca.im.migrator.exceptions.SqlActionExecutionException: SQL action failed
    at com.ca.im.migrator.business.action.SqlAction.execute(Unknown Source)
    ... 4 more
Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect INTEGER value: 'x.x.x.x'
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3964)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3902)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2526)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2673)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
    at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1192)
    at com.ca.im.migrator.data.BaseDao.executeNonQuery(Unknown Source)
    ... 5 more
Environment:
Upgrades to NFA 9.5
Resolution:
1. Open the console_movie.xml from the \CA\NFA\ directory.

2. Find the frame number in the error above.  In this case it is 152.
Find the first frame after the frame that failed on that is not calling the "<sqlFileSource>D:\CA\NFA\reporter\sql\toVersion9_5_0\EnterpriseOverviewData.sql</sqlFileSource>" source file.

In the case above, it was frame 159. In that example change the <movie nextFrame="152"> to <movie nextFrame="159"> and save the file. 

3. Open a cmd prompt window and cd to \CA\NFA\migrator and run: "RunMigrator.vbs". Wait a few minutes and check the \CA\NFA\migrator\migrator.log file to see if it finishes with exit code 0. If it exits with code 1 please send support the updated migrator.log, if it exits with code 0 move to step 4.

4.  Log in to mysql with:
mysql reporter

5. Run each command below one at a time:
ALTER TABLE TopHosts MODIFY Host varchar(40);
UPDATE TopHosts SET Host = inet_ntoa(Host);

ALTER TABLE TopHostProts MODIFY Host varchar(40);
UPDATE TopHostProts SET Host = inet_ntoa(Host);

ALTER TABLE TopHostInterfaces MODIFY Host varchar(40);
UPDATE TopHostInterfaces SET Host = inet_ntoa(Host);

ALTER TABLE TopProtHosts MODIFY Host varchar(40);
UPDATE TopProtHosts SET Host = inet_ntoa(Host);

**Note you may see warnings in the mysql output, just ensure there are no errors**
 
6. Verify that all services are running and there are no errors on the Enterprise Overview page of NFA.
Additional Information:
Note there are many different types of Migrator failure errors, it is very important to ensure the error in your migrator log matches the one above.

Also note that the frame numbers may be different depending on which version you are upgrading from, so it is key to verify you have the correct frame numbers when modifying the console_movie.xml file.  If you are unsure please contact CA Support and provide the migrator.log file the console_movie.xml file for review.