Upgrade to 21.2.8+ MySQL Upgrade / Migrator Error
search cancel

Upgrade to 21.2.8+ MySQL Upgrade / Migrator Error

book

Article ID: 235497

calendar_today

Updated On:

Products

CA Network Flow Analysis (NetQos / NFA)

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 MySQL Upgrade log would look like this:


Running mysql_upgrade utility

EXECUTE_STDOUT =


C:\Users\Administrator\AppData\Local\Temp\I1645727210\Windows>"D:\NFA\MySql\bin\mysql_upgrade" -P 3308 -u root --password=root@123 


EXECUTE_STDERR =

mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
mysql_upgrade: Got error: 2003: Can't connect to MySQL server on 'localhost' (10061) while connecting to the MySQL server
Upgrade process encountered error and will not continue.


EXECUTE_EXITCODE = 11

The bottom of the migrator log 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 : 21.2.8 - 22.2.6

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. For NFA 22.2.9 we will remove these users if they during the upgrade to prevent these types of failures.

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
2. ALTER USER 'root'@'localhost' IDENTIFIED BY 'root@123';
   
    If you are on NFA 22.2.6 - 22.2.8 you also run:
   
    DROP USER 'root'@'127.0.0.1';
    DROP USER 'root'@'::1';

3. FLUSH PRIVILEGES;
4. exit
5. If you are upgrading to NFA 22.2.5 or below you can run if not skip:
    mysql_upgrade -P 3308 -u root --password=root@123

    *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 11: 

6. mysql -uroot -P3307
7. ALTER USER 'root'@'localhost' IDENTIFIED BY 'root@123';
   
    If you are on NFA 22.2.6 - 22.2.8 you also run:
   
    DROP USER 'root'@'127.0.0.1';
    DROP USER 'root'@'::1';

8. FLUSH PRIVILEGES;
9. exit
10. If you are upgrading to NFA 22.2.5 or below you can run:
     x:\CA\NFA\Netflow\bin\mysql_upgrade -P 3307 -u root --password=root@123

    *At this point restart NetQoS NQMySQL Service*

11. cmd /C cscript D:\CA\NFA\migrator\runMigrator.vbs  (Substitute your own install path)
12. Open the D:\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.
13. 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 D:\CA\NFA directory into the migrator folder and rename it.
14. 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.

If you see this error going to a version 22.2.6 or higher, please open a ticket.

Powered by Wolken Software