CDP Private Cloud 7.16 : HUE MYSQL 8 connection error Unexpected error. Unable to verify database connection

[#1]

Hi All,

When we use MySQL 8 for HUE DB we get the below error 

Unexpected error. Unable to verify the database connection

In Cloudera manager server logs : 

2022-11-04 09:18:56,152 INFO scm-web-5325:com.cloudera.enterprise.JavaMelodyFacade: Exiting HTTP Operation: Method:POST, Path:/dbTestConn/checkConnectionResult, Status:200
2022-11-04 09:18:56,575 INFO CommandPusher-1:com.cloudera.server.cmf.CommandPusherThread: Acquired lease lock on DbCommand:547
2022-11-04 09:18:56,578 INFO CommandPusher-1:com.cloudera.cmf.service.AbstractOneOffHostCommand: Unsuccessful 'HueTestDatabaseConnection'
2022-11-04 09:18:56,578 INFO CommandPusher-1:com.cloudera.cmf.service.AbstractDbConnectionTestCommand: Command exited with code: 1
2022-11-04 09:18:56,578 INFO CommandPusher-1:com.cloudera.cmf.service.AbstractDbConnectionTestCommand: self.ensure_connection()
File "/opt/cloudera/parcels/CDH-7.1.6-1.cdh7.1.6.p0.10506313/lib/hue/build/env/lib/python2.7/site-packages/Django-1.11.29-py2.7.egg/django/db/backends/base/base.py", line 213, in ensure_connection
self.connect()
File "/opt/cloudera/parcels/CDH-7.1.6-1.cdh7.1.6.p0.10506313/lib/hue/build/env/lib/python2.7/site-packages/Django-1.11.29-py2.7.egg/django/db/utils.py", line 94, in __exit__
six.reraise(dj_exc_type, dj_exc_value, traceback)
File "/opt/cloudera/parcels/CDH-7.1.6-1.cdh7.1.6.p0.10506313/lib/hue/build/env/lib/python2.7/site-packages/Django-1.11.29-py2.7.egg/django/db/backends/base/base.py", line 213, in ensure_connection
self.connect()
File "/opt/cloudera/parcels/CDH-7.1.6-1.cdh7.1.6.p0.10506313/lib/hue/build/env/lib/python2.7/site-packages/Django-1.11.29-py2.7.egg/django/db/backends/base/base.py", line 189, in connect
self.connection = self.get_new_connection(conn_params)
File "/opt/cloudera/parcels/CDH-7.1.6-1.cdh7.1.6.p0.10506313/lib/hue/build/env/lib/python2.7/site-packages/Django-1.11.29-py2.7.egg/django/db/backends/mysql/base.py", line 274, in get_new_connection
conn = Database.connect(**conn_params)
File "/opt/cloudera/parcels/CDH-7.1.6-1.cdh7.1.6.p0.10506313/lib/hue/build/env/lib/python2.7/site-packages/MySQL_python-1.2.5-py2.7-linux-x86_64.egg/MySQLdb/__init__.py", line 81, in Connect
return Connection(*args, **kwargs)
File "/opt/cloudera/parcels/CDH-7.1.6-1.cdh7.1.6.p0.10506313/lib/hue/build/env/lib/python2.7/site-packages/MySQL_python-1.2.5-py2.7-linux-x86_64.egg/MySQLdb/connections.py", line 193, in __init__
super(Connection, self).__init__(*args, **kwargs2)
django.db.utils.OperationalError: (2059, "Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory")

2022-11-04 09:18:56,578 ERROR CommandPusher-1:com.cloudera.cmf.model.DbCommand: Command 547(HueTestDatabaseConnection) has completed. finalstate:FINISHED, success:false, msg:Unexpected error. Unable to verify database connection.
2022-11-04 09:18:56,578 INFO CommandPusher-1:com.cloudera.cmf.command.components.CommandStorage: Invoked delete temp files for command:DbCommand{id=547, name=HueTestDatabaseConnection, host=server} at dir:/var/lib/cloudera-scm-server/temp/commands/547
2022-11-04 09:18:59,140 INFO scm-web-5335:com.cloudera.enterprise.JavaMelodyFacade: Entering HTTP Operation: Method:POST, Path:/dbTestConn/checkConnectionResult

 What causes this error?

MySQL 8 supports a new authentication method based on improved stronger SH256-based password methods. When you install MySQL 8, this is the new default authentication method (although this can be changed during MySQL server installation).

As MySQL 8 is still relatively new, depending upon your Perl environment, Perl itself (or rather the driver that Perl uses to connect to a MySQL server) may not yet be compatible with the new authentication method in MySQL 8. When this occurs, an "Authentication plugin 'caching_sha2_password' cannot be loaded" error is produced.

How to fix this error?

To resolve this error, and revert your MySQL server back to using "legacy" authentication, you'll need to login to your MySQL server and issue the following command:

ALTER USER 'yourusername'@'localhost' IDENTIFIED WITH mysql_native_password BY 'yourpassword';

(where "yourusername" is the MySQL user account you specified your HUE service to use, and "yourpassword" is the corresponding MySQL user account password)

Solution :

1) alter user 'hue'@'%' IDENTIFIED WITH mysql_native_password BY 'HUEUSERPASSWORD'; 

OR

2)  You can update your MySQL configuration to use: [mysqld] default_authentication_plugin=mysql_native_password Then alter the user to use "mysql_native_password" with the following sql statement (for the hue MySQL user you created): #ALTER USER 'user'@'host' IDENTIFIED WITH mysql_native_password BY 'password';

DISCLAIMER: This article is contributed by an external user. The steps may not be verified by Cloudera and may not be applicable for all use cases and may be very specific to a particular distribution. Please follow with caution and at your own risk. If needed, raise a support case to get confirmation.