Connection Error to MySQL 8.x from Variphy Insight

When attempting to connect Variphy Insight to a MySQL 8.x server, you may encounter errors trying to connect to the database and activate the cluster. These will generally show up as a SQL ERROR 0 in the activate cluster window. A check of the insight logs may show the following types of errors:

ERROR [com.variphy.cisco.avvid.inventory.sql.mysql.impl.Impl] java.sql.SQLException: Connections could not be acquired from the underlying database!

The database itself may be running and accessible from command line, and the connectivity issue will only affect the Variphy application. This is due to an authentication method mismatch between Insight and Mysql 8.x In order to resolve this, we will need to alter the authentication method.

1. Go to the mysql config file and edit it.
   • In linux, this will be the my.cnf file, typically located in /etc/mysql
   • In Windows, this will be the my.ini file, typically located in c:\ProgramData\Mysql\Mysql8
2. Search for the phrase default-authentication-plugin under the [mysqld] section
3. It may show a value of default_authentication_plugin=caching_sha2_password
4. The value that this needs to be changed to is: default-authentication-plugin=mysql_native_password
5. Save the file (ESC + SHIFT + :x! in linux)
6. Open Mysql again in command line (myslq -u root -p, and enter password when prompted)
7. Enter the command use mysql;
8. Enter the command: ALTER USER ‘root’@’%’ IDENTIFIED WITH mysql_native_password BY ‘password-goes-here’; Changing the ‘password-goes-here‘ to the current root password (typically V@riphy!! or variphy)
9. Restart the mysql service (on linux, sudo service mysql restart) and then try to connect to Mysql from Insight again.

If you are unable to run the ALTER USER command successfully and get an error 1936 “Operation failed for root”, try the following:

1. from the Mysql command line, run select user, host, plugin from user;
2. It will display results similar to these. Note that the root command is showing a host of “localhost” not “%”

1. In this case, you will need to modify the ALTER USER command to match the host displayed, e.g.3. ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘password-goes-here’;
2. Restart the Mysql service again and then attempt the connection again from Variphy. You should be able to connect now.