QSqlDatabase with MYSQL management in the app
-
Hi! I want to know how to proper manage
QSqlDatabase
in the application. For example, I should open theDB
connection and do some stuff, closeDB
connection and remove it byremoveDatabase
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. -
Hi,
Before doing anything like that, you should determine why you are having your IP address blocked "because of many connection errors".
-
Hi,
Before doing anything like that, you should determine why you are having your IP address blocked "because of many connection errors".
The problem is that
max_connect_errors
on theDB
server always increments, so I think it not properly closing the connection to the database or not closing it all. -
How many applications are connecting to that database ?
-
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? -
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?@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 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.
MyDatabase
class is where all connections/queries execute withQSqlDatabase
to better manage it across all app. So I will keep the connection alive until the application closes. I will try it. Thanks. -
@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.
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 theDB
connection and remove it.
What do you advice me to do? Thanks.
- Close the
-
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 theDB
connection and remove it.
What do you advice me to do? Thanks.
@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. - Close the
-
@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.I think I figured it out. I will reply soon. Thank you.
-
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 theDB
connection and remove it.
What do you advice me to do? Thanks.
@Cobra91151
As @jsulm says.When you use
QSqlDatabase::addDatabase()
(http://doc.qt.io/qt-5/qsqldatabase.html#addDatabase) you can give it aconnectionName
(or use the default), and you can always get back it at (to close etc.) via that name/default. It isQSqlDatabase
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!) - Close the
-
@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.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. -
removeDatabase
is a static method.Take a look at the its documentation to see how to avoid having that warning message.
-
removeDatabase
is a static method.Take a look at the its documentation to see how to avoid having that warning message.
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); }
-
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.
-
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.
What do you mean by removing
QSqlDatabase
database member variable? When I removed it, I will no longer open/restore or useQSqlDatabase
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 callingQSqlDatabase::database("TestConnection")
and then all checks will proceed. I will try it. -
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.
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.
-
Did you read the example code from the removeDatabase documentation I linked to earlier ?
-
Yes, but for some reason I need to dereference the database object (
db = QSqlDatabase();
) to actually succeed with removing theDB
connection even with the static method, otherwise I get errorQSqlDatabasePrivate::removeDatabase: connection 'TestConnection' is still in use, all queries will cease to work.
. -
Please show the complete code you are currently using.
You should also check if you have other QSqlDatabase objects lying around.