KB489365: Connection to MySQL Database is failed with error "Authentication plugin 'mysql_native_password' cannot be loaded" after upgraded to Strategy One (June 2025).

Symptom

After upgraded to Strategy One (June 2025), the MySQL ODBC and JDBC drivers were updated to version 9.3.0, then the connection to MySQL database is failed with below error:

 Error: QueryEngine encountered error: Connected failed.
Error type: Odbc error. Odbc operation attempted: SQLDriverConnect.[08004:2059: on SQLHANDLE] [MySQL][ODBC 9.3(w) Driver]Authentication plugin 'mysql_native_password' cannot be loaded: /usr/local/mysql/lib/plugin/mysql_native_password.so: cannot open shared object file: No such file or directory [IM006:0: on SQLHANDLE] [DataDirect][ODBC lib] Driver's SQLSetConnect failed [01000:364: on SQLHANDLE] The driver returned invalid (or failed to return) SQL_DRIVER_ODBC_VER: 03.80. Error in Process method of Component: QueryEngineServer, Project xxx, Job xxx, Error Code: -2147212544.

Cause

Starting from Strategy One (June 2025), MySQL ODBC and JDBC drivers have been upgraded to version 9.3.0 in MCE container environments.

However, according to MySQL Connector/ODBC Release Notes, a known limitation exists starting from MySQL Connector/ODBC 8.4.0 release and later versions, which no longer support the 'mysql_native_password' authentication plugin. This change impacts database connectivity for users relying on this deprecated authentication method. 

ACTION

To resolve this issue, follow the steps below:

1. Verify the Authentication Plugin used by the User.
   Run the following SQL query to check the authentication plugin associated with the user. It most like still using the  the 'mysql_native_password' authentication plugin.
   SELECT user, host, plugin FROM mysql.user WHERE user = '<username>';
2. Update the Authentication Plugin
   Change the user's authentication plugin from 'mysql_native_password' to 'caching_sha2_password' by running following SQL query.
   ALTER USER '<USERNAME>'@'<HOST>' IDENTIFIED WITH caching_sha2_password BY '<PASSWORD>';