QSqlDatabase with MYSQL management in the app

Cobra91151

Hi! I want to know how to proper manage QSqlDatabase in the application. For example, I should open the DB connection and do some stuff, close DB connection and remove it by removeDatabase function every time, or I should use this connection and close it before the application closes?

I get error sometimes when connecting to the database:

QSqlDatabasePrivate::database: unable to open database: "Host 'IP' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts' QMYSQL: Unable to connect"

What is the proper way to manage QSqlDatabase with MYSQL in the application? Thanks.

SGaist

Hi,

Before doing anything like that, you should determine why you are having your IP address blocked "because of many connection errors".

Cobra91151

@SGaist

The problem is that max_connect_errors on the DB server always increments, so I think it not properly closing the connection to the database or not closing it all.

SGaist

How many applications are connecting to that database ?

Cobra91151

@SGaist

3 applications connects to the database from my side, because it's a hosting, so I don't know many apps on their side are also connects.

For example, my test code:

QThread *regThread = new QThread();
MyDatabase *regDB = new MyDatabase();
regDB->moveToThread(regThread);
qRegisterMetaType<QVector<RegData>>();
connect(regDB, &MyDatabase::regSuccess, this, &Test::setRegSucess);
connect(regDB, &MyDatabase::regFailed, this, &Test::setRegFailed);
connect(regDB, &MyDatabase::finished, regThread, &QThread::quit);
connect(regDB, &MyDatabase::finished, regDB, &Test::deleteLater);
connect(regThread, &QThread::finished, regThread, &QThread::deleteLater);
QMetaObject::invokeMethod(regDB, "registerAccount", Q_ARG(QVector<RegData>, userRegDataVector));
regThread->start();

When I delete the DB object, the connection name to the database still be available, so I can use it later in the app?

jsulm

@Cobra91151 said in QSqlDatabase with MYSQL management in the app:

MyDatabase

What is this and why do you need it? http://doc.qt.io/qt-5/qsqldatabase.html can manage all your connections for you. If you access the database often then you should keep the connection open.

Cobra91151

@jsulm

MyDatabase class is where all connections/queries execute with QSqlDatabase to better manage it across all app. So I will keep the connection alive until the application closes. I will try it. Thanks.

Cobra91151

@jsulm

I have tried it, but when the object has been deleted, I no longer have access to close the database or remove connection when app closes. So there are two options:

Close the DB connection after the work is completed every time and remove the connection
Make static instance of the class (like singleton) and use it until the app closes and then close the DB connection and remove it.

What do you advice me to do? Thanks.

jsulm

@Cobra91151 You should read http://doc.qt.io/qt-5/qsqldatabase.html
And the code example there.
QSqlDatabase keeps all open connections and you can get them at any time. Whether you deleted your own MyDatabase or not doesn't matter at all. There is really no need to reinvent the wheel here.
Whether you open/close db connection everytime you need to use the DB or keep open connection until app finishes depends on your use case.

Cobra91151

@jsulm

I think I figured it out. I will reply soon. Thank you.

JonB

@Cobra91151
As @jsulm says.

When you use QSqlDatabase::addDatabase() (http://doc.qt.io/qt-5/qsqldatabase.html#addDatabase) you can give it a connectionName (or use the default), and you can always get back it at (to close etc.) via that name/default. It is QSqlDatabase that is maintaining a "pool" of connections for you, so you don't have to do it. (Not a model I was used from my background, but that's how it works from Qt, so get used to it!)

Cobra91151

@jsulm

When removing the DB connection I get the following error:

QSqlDatabasePrivate::removeDatabase: connection 'TestConnection' is still in use, all queries will cease to work.

Code:

bool Database::dbConnectionExists()
{
    if (database.contains("TestConnection")) {
        database = QSqlDatabase::database("TestConnection");
        return true;
    }

    return false;
}

void Database::closeConnection()
{
    if (dbConnectionExists() && database.isOpen()) {
        QString dbConnection = database.connectionName();
        closeDB();
        database = QSqlDatabase();
        qDebug() << dbConnection;
        database.removeDatabase(dbConnection);
        qDebug() << database.isOpen();
    }

    emit finished();
}

void Database::closeDB()
{
    database.close();
}

I think it still holds a reference to the database.
How to fix it? Thanks.

SGaist

removeDatabase is a static method.

Take a look at the its documentation to see how to avoid having that warning message.

Cobra91151

@SGaist

I have tried the static method also, the same warning message still exists.

Code (call to close db connection when app closes):

void Test2::closeDBConnection()
{
    Database *db = new Database();
    connect(db, &Database::finished, db, &Database::deleteLater);
    db->closeConnection();
}

void Test2::closeEvent(QCloseEvent *event)
{
    closeDBConnection();
    event->accept();
    QWidget::closeEvent(event);
}

SGaist

Because you have somewhere in your code an instance of QSqlDatabase stored.

From your code, I'd say you have a QSqlDatabase database member variable in your Database class. Remove that.

Cobra91151

@SGaist

What do you mean by removing QSqlDatabase database member variable? When I removed it, I will no longer open/restore or use QSqlDatabase connection.

I have changed code to:

   QSqlDatabase db = QSqlDatabase::database("TestConnection");

   if (db.isOpen()) {
       db.close();
       qDebug() << db.connectionName();
       QSqlDatabase::removeDatabase(db.connectionName());
   }

The issue still exists, I think you are right about removing QSqlDatabase member variable, I still get it by calling QSqlDatabase::database("TestConnection") and then all checks will proceed. I will try it.

Cobra91151

@SGaist

I have deleted the QSqlDatabase database member variable, but the issue still exists. But I think I figured it out. I will test it and reply.

Yes, it's finally working.

Code:

   QSqlDatabase db = QSqlDatabase::database("TestConnection");

   if (db.isOpen()) {
       QString dbConnectionName = db.connectionName();
       db.close();
       qDebug() << dbConnectionName;
       db = QSqlDatabase();
       db.removeDatabase(dbConnectionName); // or QSqlDatabase::removeDatabase(dbConnectionName);
   }

  emit finished();

And now I use the connection to the DB until app closes or close it whenever I want. Also I would like to add, that I add check in the constructor and use database connection - QSqlDatabase db = QSqlDatabase::database("TestConnection"); in the methods because different methods can execute in different situations, so it will always create or use the connection to database.

Database::Database(QObject *parent) : QObject(parent)
{
    if (!checkDBConnection()) {
         //Create db connection with the name "TestConnection"
    }
}

bool Database::checkDBConnection()
{
    if (QSqlDatabase::contains("TestConnection")) {
        return true;
    }

    return false;
}

Thank you all.

SGaist

Did you read the example code from the removeDatabase documentation I linked to earlier ?

Cobra91151

@SGaist

Yes, but for some reason I need to dereference the database object (db = QSqlDatabase();) to actually succeed with removing the DB connection even with the static method, otherwise I get error QSqlDatabasePrivate::removeDatabase: connection 'TestConnection' is still in use, all queries will cease to work..

SGaist

Please show the complete code you are currently using.

You should also check if you have other QSqlDatabase objects lying around.