Connect with MySQL Database MacOs Ventura 13.4

BushyAxis793

Hello. I recently completed the process of adding a QMySQL plugin to my project in Qt Creator. Unfortunately, a new problem arose. A database connection error.

QSqlQuery::prepare: database not open
QSqlError("", "", "")

I have installed MySQL and Xampp

When I try to access the database via phpadmin everything works fine.

Any idea why I got this error? Below is my connection function

void Database::ConnectToDatabase()
{
    db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("localhost");
    db.setUserName("root");
    db.setPassword("");
    db.setDatabaseName("atlas");

    if(db.open())
    {
        SetConnectionStatus(true);
    }
    else
    {
        SetConnectionStatus(false);
    }
}

Thanks for any tips.
Have a good day

JonB

@BushyAxis793
But have you changed your db.setPassword(""); to include it? The error message

Access denied for user 'root'@'localhost' (using password: NO)

implies you are not passing any password to logon as root.

BushyAxis793

When I try Initialize Database I got error

2023-06-05T14:43:34.457802Z 0 [System] [MY-013169] [Server] /usr/local/mysql-8.0.33-macos13-arm64/bin/mysqld (mysqld 8.0.33) initializing of server in progress as process 83787
2023-06-05T14:43:34.458703Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/mysql-8.0.33-macos13-arm64/data/ is case insensitive
2023-06-05T14:43:34.461563Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-06-05T14:43:34.525485Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-06-05T14:43:34.991617Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: >lRKX(al5yCh
2023-06-05T14:43:35.485975Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.33).
2023-06-05T14:43:36.032965Z 0 [System] [MY-010116] [Server] /usr/local/mysql-8.0.33-macos13-arm64/bin/mysqld (mysqld 8.0.33) starting as process 83795
2023-06-05T14:43:36.034822Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/mysql-8.0.33-macos13-arm64/data/ is case insensitive
2023-06-05T14:43:36.043258Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-06-05T14:43:36.101890Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-06-05T14:43:36.200615Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2023-06-05T14:43:36.200644Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2023-06-05T14:43:36.200981Z 0 [ERROR] [MY-010262] [Server] Can't start server: Bind on TCP/IP port: Address already in use
2023-06-05T14:43:36.200987Z 0 [ERROR] [MY-010257] [Server] Do you already have another mysqld server running on port: 3306 ?
2023-06-05T14:43:36.201000Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-06-05T14:43:37.238058Z 0 [System] [MY-010910] [Server] /usr/local/mysql-8.0.33-macos13-arm64/bin/mysqld: Shutdown complete (mysqld 8.0.33)  MySQL Community Server - GPL.

Any idea why I have no connection via Qt Creator?

SGaist

Hi,

Where exactly do you get these errors ?
It looks like you are trying to run several MySQL instance at the same time.

BushyAxis793

@SGaist To be honest before I installed Qt Creator I downloaded MySQL and Xampp. When I run both I can see my database via localhost / phpadmin. Now after added plugin to Qt Creator I can't connect with database via Qt app. I can see this error when I click "Initialize Database" button. I can't uninstall MySQL because I need it for my QMySQL Plugin. I think you are right. I have more than one instance. But the question is how to disable it?

https://ddgobkiprc33d.cloudfront.net/9539ae1d-8662-47cf-b3b3-6768074ee978.png

SGaist

One thing you are currently not doing is printing the QSqlDatabase error when the open call fails. That should give you a hint about what is going wrong.

Doesn't the XAMPP stack provide MySQL ? Here might be the doubling.

BushyAxis793

@SGaist I decided to uninstall XAMPP for a testing time. I added this line of code to my connections function:

qDebug()<<db.lastError();

And I got this

QSqlError("1045", "QMYSQL: Unable to connect", "Access denied for user 'root'@'localhost' (using password: NO)")

This is strange a bit...

SGaist

Are you sure the root user is configured to be accessible on localhost ? I don't think the default configuration allows that.

BushyAxis793

@SGaist Ok. So I decided to fix this issue. I follow this solution for root:

https://stackoverflow.com/questions/50177216/how-to-grant-all-privileges-to-root-user-in-mysql-8-0

I input:

mysql -u root -p

Then Input password and the result is:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.33 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Next I input:

CREATE USER 'root'@'%' IDENTIFIED BY '11111111';

Output:

Query OK, 0 rows affected (0,01 sec)

Then I input:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

Result:

Query OK, 0 rows affected (0,02 sec)

Finally I input:

FLUSH PRIVILEGES;

And the result:

Query OK, 0 rows affected (0,00 sec)

Next I run my Qt Creator project and the error still exists.

QSqlError("1045", "QMYSQL: Unable to connect", "Access denied for user 'root'@'localhost' (using password: NO)")

I also checked port 3306 via

sudo lsof -i -P | grep LISTEN | grep :3306

and I got :

mysqld    28777          _mysql   18u  IPv6 0x8a0d66b153d9f2cf      0t0    TCP *:33060 (LISTEN)
mysqld    28777          _mysql   20u  IPv6 0x8a0d66b153d9cacf      0t0    TCP *:3306 (LISTEN)

SGaist

@BushyAxis793 silly question but: did you use the correct password ?

BushyAxis793

@SGaist I'm sure the password is correct. I set it during mysql installation process.

JonB

@BushyAxis793
But have you changed your db.setPassword(""); to include it? The error message

Access denied for user 'root'@'localhost' (using password: NO)

implies you are not passing any password to logon as root.

BushyAxis793

@JonB Oh my God... I forgot about it! I was focusing on mysql privileges... I inputed password and logged in!

I'm too distracted :)

Anyway everything seems works perfect :)

Thanks for help @SGaist and @JonB !

SGaist

@BushyAxis793 one last thing: making your root account available on any and all point of access of your MySQL server is a wrong idea. You should rather create a dedicated user that has access to a specific database for that purpose.