Cannot establish connection to MySQL database

[Hmmm]

I have a database created in MySQL Workbench called "mydb" and I used this code to connect with it:

void MainWindow::on_pushButton_clicked()
{
    QSqlDatabase db=QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("localhost");
    db.setPort(3306);
    db.setUserName("root");
    db.setPassword(password);
    db.setDatabaseName("mydb");

    if(db.open())
    {
        QMessageBox::information(this,"Connection","Database connection succesful");
    }
    else{
        QMessageBox::information(this,"Not connected","Database connection not succesful");
    }
}

I also run

    bool test = QSqlDatabase::isDriverAvailable("QMYSQL");
    qDebug()<<test;

to check if driver is available and it returns true. I also tested the connection on MySQL workbench to see if it was successful and it was.
But when I run my app, it says: "Database connection not succesful"

[Christian Ehrlicher]

You should check QSqlDatabase::lastError()

[Hmmm]

I did. It returns: QSqlError("", "", "")

[Cobra91151]

Hello!

I checked your code. The problem could be:

1. If you are on Windows you must copy the libmysql.dll lib to the application directory.
2. The database server is not started/working.

Check it and it will work. Happy coding!

[Hmmm]

@Cobra91151 Thank you for your answer. I checked message log on MySQL, it says: 2019-06-30 16:15:59 - MySQL server is currently running
Also I did copy libmysql.dll to the application directory but nothing helps :(

[Christian Ehrlicher]

Then please check with e.g. Dependency Walker if your qt mysql plugin really finds all necessary libraries.
And where did you check for lastError?

[Cobra91151]

@Hmmm

Ok. Try to navigate to the application directory and check if it contains: sqldrivers directory with qsqlmysql.dll lib?

[Hmmm]

@Christian-Ehrlicher I checked for lastError in the MainWindow, its not written above as I tried that later.
About Dependeny Walker, I'm not very familiar with it, is this what I'm supposed to do:

[Hmmm]

@Cobra91151 This is what my application directory contains:

[Cobra91151]

@Hmmm

It is source directory. Application directory is where your .exe file is present (Debug/Release dir)! That's why it can't locate the MySQL libs. Copy libmysql.dll into Debug/Release dir (it depends on your Qt Creator configuration) and start the program. It 100% must work :)

[Christian Ehrlicher]

@Hmmm said in Cannot establish connection to MySQL database:

I checked for lastError in the MainWindow, its not written above as I tried that later.

You have to call it after db.open()
And you should check the qt mysql plugin, not the mysql dll ... it's qsqlmysql.dll/qsqlmysqld.dll and the location is for sure not in your source tree...

[Hmmm]

@Christian-Ehrlicher Ok, about lastError, I put it after and it says:

QSqlError("2059", "QMYSQL: Unable to connect", "Authentication plugin 'caching_sha2_password' cannot be loaded: The specified module could not be found.\r\n")`

And this is the ouput of qsqlmysql.dll in Dependeny Walker:

Error: At least one required implicit or forwarded dependency was not found.
Error: At least one module has an unresolved import due to a missing export function in an implicitly dependent module.
Error: Modules with different CPU types were found.
Error: A circular dependency was detected.
Warning: At least one delay-load dependency module was not found.
Warning: At least one module has an unresolved import due to a missing export function in a delay-load dependent module.

[Hmmm]

@Cobra91151 Ok, my mistake. I did that now but still the same problem

[Christian Ehrlicher]

@Hmmm said in Cannot establish connection to MySQL database:

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

So this is a mysql problem, don't see what Qt can do against it. Maybe google can help here.

[Hmmm]

Omg, I finally did it with running this SQL query:

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

Thank you all for helping me :D