Qt 5.1.0 - Cannot login to MySQL 5.5.32 server with QMYSQL to execute queries

[stingray]

Well, now im at it again...

i've got a server suppling MySQL, its version if it matters 5.5.32-0ubuntu0.13.04.1

i've got Qt 5.1.0 with MySQL driver built for Linux built from source on the ubuntu machine, as the supplied libqmysql.so or what its name is wasn't working, so i have built new one and its working... but now when i want to open the connection i get this qDebug message..

Error: "Access denied for user '<masked username>'@'localhost' (using password: YES) QMYSQL: Unable to connect"

the fun part is i copied and paste both user and pass from the phpmyadmin page, and stored it in my QSettings dialog.

i've tried password 4.1+ and password 4.0 standards in mysql (thu phpmyadmin), but it seems it doesnt like any of it.

the user host is % so it should work from localhost, without explictly saying localhost?
and port is 3306 as should be.

and in my /var/log/mysql/error.log

130815 20:24:15 [Warning] Access denied for user '<masked username>'@'localhost' (using password: YES)

and the code for this function...

void options::create_db_tables() {
 
    QSettings Settings;
    Settings.beginGroup("MySQL");
 
    QString Host = Settings.value("MySQL_Host").toString();
    int     Port = Settings.value("MySQL_Port").toInt();
    QString User = Settings.value("MySQL_User").toString();
    QString Pass = Settings.value("MySQL_Pass").toString();
    QString DB   = Settings.value("MySQL_DB").toString();
    Settings.endGroup();
 
    QSqlDatabase MySQL_DB = QSqlDatabase::addDatabase("QMYSQL");
 
    //MySQL_DB.addDatabase("QMYSQL");
    MySQL_DB.setHostName(Host);
    MySQL_DB.setPort(Port);
    MySQL_DB.setUserName(User);
    MySQL_DB.setPassword(Pass);
    MySQL_DB.setDatabaseName(DB);
    MySQL_DB.setConnectOptions();
 
 
    if (MySQL_DB.open())
    {
        QSqlQuery DB_Query;
        QString tmp_sql = "CREATE DATABASE :DB";
        DB_Query.prepare(tmp_sql);
        DB_Query.bindValue(":DB", DB);
        DB_Query.exec();
    }
    else
    {
        qDebug() << "Error: " << MySQL_DB.lastError().text();
    }
 
}

void options::create_db_tables() {

    QSettings Settings;
    Settings.beginGroup("MySQL");

    QString Host = Settings.value("MySQL_Host").toString();
    int     Port = Settings.value("MySQL_Port").toInt();
    QString User = Settings.value("MySQL_User").toString();
    QString Pass = Settings.value("MySQL_Pass").toString();
    QString DB   = Settings.value("MySQL_DB").toString();
    Settings.endGroup();

    QSqlDatabase MySQL_DB = QSqlDatabase::addDatabase("QMYSQL");

    //MySQL_DB.addDatabase("QMYSQL");
    MySQL_DB.setHostName(Host);
    MySQL_DB.setPort(Port);
    MySQL_DB.setUserName(User);
    MySQL_DB.setPassword(Pass);
    MySQL_DB.setDatabaseName(DB);
    MySQL_DB.setConnectOptions();


    if (MySQL_DB.open())
    {
        QSqlQuery DB_Query;
        QString tmp_sql = "CREATE DATABASE :DB";
        DB_Query.prepare(tmp_sql);
        DB_Query.bindValue(":DB", DB);
        DB_Query.exec();
    }
    else
    {
        qDebug() << "Error: " << MySQL_DB.lastError().text();
    }

}

To copy to clipboard, switch view to plain text mode 

and the data in those variables are the ones enterd in the setupform that saves to qsettings, so they should be alright..

the things i can think of that could be wrong, is if the QMYSQL doesnt encrypt the password right without some db.setConnectOptions(); and
that the server thinks its a pre-encrypted password and a plaintext one comes along, well sure its right that it should not let the connection in.. hehe..

but i cant find anything in the doc about how a mysql .setConnectOptions(); is supposed to look like exept for how to use mysql and ssl, but if i cant connect to begin with the ssl doesnt do much help...

or well i cant find much on how a MySQL connection setup should look like when it works, most of the mysql threads and google is about "driver not loaded" stuff..

[ChrisW67]

The granted permissions your MySQL database are incorrect. Look at the Privileges or Users tab in PHPMyAdmin. This is not a Qt issue.

[stingray]

accually when i wrote this yesterday i was so sure it wasnt a privillege error, then now when you wrote that i had a look at it again, and it accually works on the root account, so i guess its back to the drawingboard and see what permissions for the apps account is wrong, thanks for slapping me in the right direction when i refuse to see whats the most possible error..

accually im kinda releaved that the qmysql accually works, that means now its time to start accually making the application and not just trying to figure out how the f it should connect to the database..

---

Added after 1 13 minutes:

worth putting here for searching users in the future...

in mysql % = any host exept localhost...

so i thought that wildcard anyhost, was any host, but infact it isn't if connection is from localhost its denied for not being a localhost as host.
so the fix for the problem is clone the user and add localhost as the connection host..

[ChrisW67]

Sorry I was a little abrupt in my reply.

I have always found the MySql mechanism for controlling granted privileges counter-intuitive. Mangling user authentication together with server access and database privileges has always annoyed me. The mechanism for checking passwords will match a localhost anonymous account with no privs before it matches a named account on % server even when it has a name.

[stingray]

no worries, i agree on the mysql login mechanism is a pain now that i have used it, i've used DB2 more then MySQL so it was bound to be a problem even through you try to be carefull and avoid problems..