Upgrade to 21.2.8+ MySQL Upgrade / Migrator Error
Article ID: 235497
Updated On:
Products
Issue/Introduction
While upgrading the NFA Console or Harvester from a version prior to 21.2.8 to a version 22.2.6+ you may run into this:
The bottom of the migrator.log file would look like this:
2022-02-24 18:43:08.404 INFO [main] [com.ca.im.migrator.TheSet.migrate(Unknown Source)] - Target version='21.2.8.66' root='D:\NFA'
2022-02-24 18:43:08.420 INFO [main] [com.ca.im.migrator.TheSet.makeMovie(Unknown Source)] - Making new movie
2022-02-24 18:43:08.420 INFO [main] [com.ca.im.migrator.business.SqlPropInspector.exists(Unknown Source)] - dbConnInfo.getHost: null dbConnInfo.getName: harvester dbConnInfo.getPort: 3308 dbConnInfo.getUser: root selectExistsSql: select 1
2022-02-24 18:43:08.639 WARN [main] [com.ca.im.migrator.business.SqlPropInspector.exists(Unknown Source)] - Exception: java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES) message: Access denied for user 'root'@'localhost' (using password: YES)
2022-02-24 18:43:08.639 FATAL [main] [com.ca.im.migrator.TheSet.migrate(Unknown Source)] - Unhandled exception
com.ca.im.migrator.exceptions.MigratorException: Cannot determine whether prop exists
at com.ca.im.migrator.business.SqlPropInspector.exists(Unknown Source) ~[migrator.jar:?]
at com.ca.im.migrator.TheSet.makeMovie(Unknown Source) ~[migrator.jar:?]
at com.ca.im.migrator.TheSet.migrate(Unknown Source) [migrator.jar:?]
at com.ca.im.migrator.MigratorApp.main(Unknown Source) [migrator.jar:?]
Caused by: java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:963) ~[migrator.jar:?]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3966) ~[migrator.jar:?]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3902) ~[migrator.jar:?]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:875) ~[migrator.jar:?]
at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1712) ~[migrator.jar:?]
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1228) ~[migrator.jar:?]
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2253) ~[migrator.jar:?]
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2284) ~[migrator.jar:?]
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2083) ~[migrator.jar:?]
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:806) ~[migrator.jar:?]
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47) ~[migrator.jar:?]
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.8.0_312]
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[?:1.8.0_312]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.8.0_312]
at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[?:1.8.0_312]
at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) ~[migrator.jar:?]
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:410) ~[migrator.jar:?]
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:328) ~[migrator.jar:?]
at java.sql.DriverManager.getConnection(DriverManager.java:664) ~[?:1.8.0_312]
at java.sql.DriverManager.getConnection(DriverManager.java:208) ~[?:1.8.0_312]
at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:155) ~[migrator.jar:?]
at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:146) ~[migrator.jar:?]
at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnectionFromDriver(AbstractDriverBasedDataSource.java:205) ~[migrator.jar:?]
at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnection(AbstractDriverBasedDataSource.java:179) ~[migrator.jar:?]
at com.ca.im.migrator.data.ConnectionPool.getConnection(Unknown Source) ~[migrator.jar:?]
at com.ca.im.migrator.data.BaseDao.executeScalar(Unknown Source) ~[migrator.jar:?]
... 4 more
2022-02-24 18:43:08.639 INFO [main] [com.ca.im.migrator.MigratorApp.main(Unknown Source)] - Exiting with code 1
Environment
Release : 22.x+
Component : NFA
Cause
The MySQL upgrade and/or Migrator likely failed due to the upgrade not being ran as an administrator which didn't allow the password to be set properly for the root user inside the MySQL database. This is something new which could happen in 21.2.8+. For improved security the the root user gets a default password as opposed to no password and the anonymous user gets disabled.
If you run into issues like the above going to NFA 22.2.6 or higher, than you may be running into and issue where MySQL 8.0 now favors 'root'@'127.0.0.1' and 'root'@'::1' users over 'root'@'localhost' which is the user which should be used by the product. If these users exist, it will cause problems as the passwords won't match. The steps below have been updated to include a fix for this.
We can check whether these 'extra' root users exist by running:
mysql -uroot -p
SELECT user,host FROM mysql. user;
There should only be one single 'root' user on host 'localhost'. If we see the following extra root users, we need to get rid of them:
root@::1
Resolution
This fix this we need to manually set the default password in MySQL for the root user. Ensure the CA MySQL Service is running.
Open a CMD prompt and type:
1. mysql -uroot -p
2. ALTER USER 'root'@'localhost' IDENTIFIED BY 'root@123';
DROP USER 'root'@'127.0.0.1';
DROP USER 'root'@'::1';
3. FLUSH PRIVILEGES;
4. exit
*At this point restart CA MySQL Service*
If this is a Harvester Server then continue to step 6. If it is a Console Server jump to step 9:
5. mysql -uroot -p -P3307
6. ALTER USER 'root'@'localhost' IDENTIFIED BY 'root@123';
DROP USER 'root'@'127.0.0.1';
DROP USER 'root'@'::1';
7. FLUSH PRIVILEGES;
8. exit
*At this point restart NetQoS NQMySQL Service*
9. cmd /C cscript D:\CA\NFA\migrator\runMigrator.vbs (Substitute your own install path)
Open the "\CA\NFA\migrator\migrator.log" and make sure the migrator exits with a code of 0 and then that last frame inserted the NFA versions you are attempting to upgrade to.
If you successfully ran the MySQL upgrade and database Migrator, make sure to move the console_movie.xml or collector_movie.xml file out of the "\CA\NFA" directory into the migrator folder and rename it.
Start all remaining CA Services other than the CA NFA VnaDataCollector Service.
If you are unsure or uncomfortable with how to do this, please open a ticket with Broadcom Support.
Feedback
