MySQL Upgrade Failure during NFA Console Upgrade
Article ID: 190843
Updated On:
Products
CA Network Flow Analysis (NetQos / NFA)
Issue/Introduction
While upgrading NFA Console from 9.3.X to 10.0.X we see a MySQL Upgrade failure message.
The GUI for NFA works properly but the About page says the version has not been upgraded.
The GUI for NFA works properly but the About page says the version has not been upgraded.
Environment
Release : 9.3
Component : NQRPTA - REPORTERANALYZER
Cause
This issue arises when the migrator fails to complete.
In the CA/NFA/migrator/migrator.log we see:
2020/05/08 00:12:29 INFO Projector::play() - Frame #433: [SqlAction database=reporter port=3308 sql='DROP TABLE IF EXISTS address_definitions_history']
2020/05/08 00:12:29 INFO Projector::play() - Frame #434: [SqlAction database=reporter port=3308 sql='CREATE TABLE address_definitions_history ( HostIP VARBINARY(16) NOT NULL COMMENT 'Host IP Address', HostName CHAR(64) default NULL COMMENT 'Host Name', Valid_from int(10) unsigned NOT NULL default 0 COMMENT 'Start time when Host Name is mapped for an IP', Valid_to int(10) unsigned NOT NULL default 0 COMMENT 'End time when Host Name is mapped for an IP', DomainId int(10) unsigned NOT NULL default '1', PRIMARY KEY (HostIP,Valid_from), KEY `Valid_from` (`Valid_from`), KEY `Valid_to` (`Valid_to`), KEY `HostName` (`HostName`) ) ENGINE=InnoDB']
2020/05/08 00:12:29 INFO Projector::play() - Frame #435: [SqlAction database=reporter port=3308 sql='DROP TRIGGER IF EXISTS addressDefinitions_AfterDelete_trigger']
2020/05/08 00:12:29 INFO Projector::play() - Frame #436: [SqlAction database=reporter port=3308 sql='CREATE TRIGGER addressDefinitions_AfterDelete_trigger AFTER DELETE ON address_definitions FOR EACH ROW BEGIN UPDATE address_definitions_history set Valid_To = UNIX_TIMESTAMP() where HostIP = OLD.address and DomainId = OLD.DomainId and Valid_To = 0; END ']
2020/05/08 00:12:29 INFO Projector::play() - Frame #437: [SqlAction database=reporter port=3308 sql='replace into parameter_descriptions values('TTL', 'System','string','3n', 'dns expiry time for host', 'dns expiry time', 'N')']
2020/05/08 00:12:29 INFO Projector::play() - Frame #438: [SqlAction database=reporter port=3308 sql='drop trigger if exists parameter_descriptions_check_ttl_before_update']
2020/05/08 00:12:29 INFO Projector::play() - Frame #439: [SqlAction database=reporter port=3308 sql='create trigger parameter_descriptions_check_ttl_before_update before update on parameter_descriptions for each row begin IF (NEW.DefaultValue REGEXP '(^[0-9]+)([ynwd]$)' ) = 0 AND NEW.Parameter = 'TTL' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Wrong value. Valid values are 4y, 9n, 2w, 10d etc.!!!'; END IF; end ']
2020/05/08 00:12:29 INFO Projector::play() - Frame #440: [SqlAction database=reporter port=3308 sql='INSERT IGNORE INTO `address_definitions_history` (`HostIP`, `HostName`, `Valid_from`, `DomainId`) SELECT address, Name, LastAccessed, domainId FROM address_definitions']
2020/05/08 00:12:29 INFO Projector::play() - Frame #441: [SqlAction database=reporter port=3308 sql='DROP EVENT IF EXISTS AddressDefinitionsHistoryEvent']
2020/05/08 00:12:29 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: java.sql.SQLException: Cannot proceed because system tables used by Event Scheduler were found damaged at server start
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:963)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3966)
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
2020/05/08 00:12:29 INFO Library::putMovie() - Writing movie file E:\CA\NFA\console_movie.xml
2020/05/08 00:12:29 INFO MigratorApp::main() - Exiting with code 1
In the CA/NFA/migrator/migrator.log we see:
2020/05/08 00:12:29 INFO Projector::play() - Frame #433: [SqlAction database=reporter port=3308 sql='DROP TABLE IF EXISTS address_definitions_history']
2020/05/08 00:12:29 INFO Projector::play() - Frame #434: [SqlAction database=reporter port=3308 sql='CREATE TABLE address_definitions_history ( HostIP VARBINARY(16) NOT NULL COMMENT 'Host IP Address', HostName CHAR(64) default NULL COMMENT 'Host Name', Valid_from int(10) unsigned NOT NULL default 0 COMMENT 'Start time when Host Name is mapped for an IP', Valid_to int(10) unsigned NOT NULL default 0 COMMENT 'End time when Host Name is mapped for an IP', DomainId int(10) unsigned NOT NULL default '1', PRIMARY KEY (HostIP,Valid_from), KEY `Valid_from` (`Valid_from`), KEY `Valid_to` (`Valid_to`), KEY `HostName` (`HostName`) ) ENGINE=InnoDB']
2020/05/08 00:12:29 INFO Projector::play() - Frame #435: [SqlAction database=reporter port=3308 sql='DROP TRIGGER IF EXISTS addressDefinitions_AfterDelete_trigger']
2020/05/08 00:12:29 INFO Projector::play() - Frame #436: [SqlAction database=reporter port=3308 sql='CREATE TRIGGER addressDefinitions_AfterDelete_trigger AFTER DELETE ON address_definitions FOR EACH ROW BEGIN UPDATE address_definitions_history set Valid_To = UNIX_TIMESTAMP() where HostIP = OLD.address and DomainId = OLD.DomainId and Valid_To = 0; END ']
2020/05/08 00:12:29 INFO Projector::play() - Frame #437: [SqlAction database=reporter port=3308 sql='replace into parameter_descriptions values('TTL', 'System','string','3n', 'dns expiry time for host', 'dns expiry time', 'N')']
2020/05/08 00:12:29 INFO Projector::play() - Frame #438: [SqlAction database=reporter port=3308 sql='drop trigger if exists parameter_descriptions_check_ttl_before_update']
2020/05/08 00:12:29 INFO Projector::play() - Frame #439: [SqlAction database=reporter port=3308 sql='create trigger parameter_descriptions_check_ttl_before_update before update on parameter_descriptions for each row begin IF (NEW.DefaultValue REGEXP '(^[0-9]+)([ynwd]$)' ) = 0 AND NEW.Parameter = 'TTL' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Wrong value. Valid values are 4y, 9n, 2w, 10d etc.!!!'; END IF; end ']
2020/05/08 00:12:29 INFO Projector::play() - Frame #440: [SqlAction database=reporter port=3308 sql='INSERT IGNORE INTO `address_definitions_history` (`HostIP`, `HostName`, `Valid_from`, `DomainId`) SELECT address, Name, LastAccessed, domainId FROM address_definitions']
2020/05/08 00:12:29 INFO Projector::play() - Frame #441: [SqlAction database=reporter port=3308 sql='DROP EVENT IF EXISTS AddressDefinitionsHistoryEvent']
2020/05/08 00:12:29 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: java.sql.SQLException: Cannot proceed because system tables used by Event Scheduler were found damaged at server start
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:963)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3966)
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
2020/05/08 00:12:29 INFO Library::putMovie() - Writing movie file E:\CA\NFA\console_movie.xml
2020/05/08 00:12:29 INFO MigratorApp::main() - Exiting with code 1
Resolution
Manually rerun the migrator by running the .vbs script located here (use a command prompt with admin privileges):
<install drive>:\CA\NFA\migrator\runMigrator.vbs
<install drive>:\CA\NFA\migrator\runMigrator.vbs
Feedback
Yes
No
Powered by
