Authentication problem with mysql 8 using qt

Question

I am developing a qt (5.12.2) application which can connect to a mysql server. Recently i tried to connect to a mysql 8 server. I was unable to connect, and got the following error message:

Authentication plugin 'caching_sha2_password' cannot be loaded: The specified module could not be found.

After some digging, i found out that the problem was because of the new encryption implemented in mysql 8.

One workaround to this problem was to set the server to the legacy authentication method.

Is there a solution without switching back to the legacy authentication?

Answer 1

There are 2 possible problems which can cause this problem.

1. You are using the old way and connect with QMYSQL driver using the MySQL Connector/C (libmysqlclient.dll) connector which not supports MySQL 8 and the new authentication method.
2. The second way is that you are using the QODBC driver with an older release(<8.0) which not supports the new authentication method.

You can easily solve this problem by switching or upgrading to the latest QODBC driver. You can download the latest version from the MySQL download page. With this little example and with the right connector it will work.

QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
db.setDatabaseName("Driver={MySQL ODBC 8.0 Unicode Driver};Server=example.com;Database=test;Uid=user;Port=3306;Pwd=password;WSID=.");
if (!db.open()) {
    qDebug() << db.lastError().text();
} else {
    qDebug("success");
}